microsoft | Brady Moritz

Access Record is too large error

Recently reverted to some old school .net winform coding, moving data into an access 2010 database using datatable and tableadapters. Funny thing, some of this still is still easier to use than entity framework or whatever flavor of data connectivity flavor of the week you might be using… but mostly, it’s not so great. Since this needed to use Access, I didn’t have much other choice.

So, one table I needed to move data into had a ton of fields… about 150 of them. I didn’t immediately recall the row data size limit, but it found me pretty quickly- apparently about 200 characters. So, without spliting the table into multiples or some other nonsense, how do you work around this?

The table is actually capable of storing 4000 bytes, but everything defaults to unicode.. and not the smaller utf-8 stuff, but the big boy utf-16. This is status quo nowdays, but is still quite a waste for many english-only applications.

I had the idea that maybe access could be tricked back into some kind of 8 bit character mode, but no such luck. However: I did find a flag you can set on text fields, called “unicode compression”. Turn this on, and apparently it uses some kind of internal compression (sounds like it emulates utf-8) to smoosh down characters that done require two bytes for storage. I turned this on for all the text fields in my db, and sure enough- now my datadata is able to store data with total width greater than 2000 chars. Maybe not the best possible solution, but works great when you just want to get the project out the door.

Info from MS: http://support.microsoft.com/kb/111304

Another item I ran across with this: if you go through and change all the columns in your table to use unicode compression, the table effectively deletes the old column and creates new ones. But internally, the column count continues to increase, and can get to be too many columns for a table definition (255 or 250 columns if I recall). The quick solution for this: after making all the changes to the table, go to the file menu and click “save as” and save it off with a different table name. This will re-gen the table with only the columns you’ve edited, and not alll the old deleted columns baggage. Change the names of the tables back, and you’re ready to roll.

Some more info on the column limit: http://support.microsoft.com/kb/128221

Bing Ads

I just started playing with Bing Ads, except that until today, it was called microsoft advertising. I think this must be th 5th or 6th renaming of this weird smashup of Yahoo and Microsoft/Bing/Live/MSN/Whatever it’s called this week.

Oh and the ads performed horribly. It might be my own fault somehow, but for whatever reason, after buying over 200 clicks, I only see about 7 clicks registered at my target website. Something isn’t working right. If it’s on my side, I’ll figure it out, but I have a weird feeling that there is some issue with bots or otherwise incentivized idiots clicking the ads and then abandoning them before my page even has time to load and register the click. So, for 50 bucks I received zero conversions. How’s that for ROI? 😉

Sql CE (compact edition)- Still slow after all these years

Many a year ago, sql server ce (sql server compact edition) was an amazing little database that microsoft released for use on Windows CE / Pocket PC devices. The coolest thing about it was that it was somewhat compatible with the big boy version of sql server, so a lot of your queries and sql knowledge from sql server could be utilized for mobile computer software.

This little database performed adequately for most situations, considering it was running on a little mobile 200mhz cpu, so the occasional slowdown was often excuseable. Except for when it came to inserting a lot of data. Keep in mind that the common technology stack at the time was to use the .Net compact framework (a stripped down, mobile-enabled version of the .Net framework) and have this .net code interact with your sql ce db. Micrsoft had envisioned a beautiful world where ado.net would work in the same disconnected fashion as the big-boy .net framework did, using datasets and datatables, and even datareaders if you were a performance maniac. These disconnected data layers work great for the full .net work, but on a mobile device this proved to be a big headache- afterall, the device is the only thing interacting with the local db, so why duplicate everything in a table into yet another structure in memory that effectively mimics.. a table? Many developers would eventually ditch datasets and move to using datareaders for populating compact framework forms, and then use direct sql for adding/updating the records in the database. This proved to be the only way to avoid waithing several seconds for your form to load a single record, in many instances.

But, most mobile projects ran into a much larger problem – the need for larger databases on the mobile device. It makes perfect sense to need larger, multi-megabyte databases on your mobile computer, since most of these mobile computers ran in disconnected mode and would sync up their (often sizeable) databases with a larger server any time they were docked and synched up. These large databases were almost impossible to create when using a sql ce database. Most devs would initially download a large chunk of data from a remote server, fill up a dataset, and then tell it to update the database. This was a disaster- even 10k records could take hours to update.

So, why dont we create a sql ce database on a server, and just download the whole thing to the device to update it? Sorry – at that time, sql ce was only supported on mobile compters, no full-pc version existed. (A company, I don’t recall the name, actually created a product that would do this, by reverse engineering the sql ce file format… yikes!)

The next step most devs would then take would be to ditch the dataset and move to using sql insert statements to add each row of data directly. This helped a lot, and with tuning (using prepared statements, etc) could speed things up a lot. But even in these cases, that same 10k records could still take 30 minutes. Possibly an order of magnitude faster, but still not even close to useable.

My company back in those days created a product specifically to address this problem – you may recall the SSCEDirect product – in which we provided a compact framework interface to a lower-level native code oledb interface to a sqlce db. This produce would typically increase performance by well over another order of magnitude, often 2. So now our 10k records might only take 15 seconds to load, or even less. This was what sql ce was meant to be.

If SSCEDirect sounds familiar, that’s probably because it is what MS later added to the Sql CE .Net interface via the SqlCeResultset objects – these effectively do exactly the same thing as what our product did in those days.

Ok, so now it is 2012 and I’m writing about all this, why?

It is just amazing to me that Sql CE has grown up a lot over the years, and has even become repurposed as the lightweight in-process database mean for use on non-mobile, full PC environments. I love the concept of it, but those old performance problems still haunt me today. Even running Sql CE 4.x on a fast desktop PC, I can try to insert 10k’s to 100k’s of records using the entity framework – and it still grinds to a halt. The full sql server seems to handle this fine, and yet the stripped down single-user in-process local database with a massive cpu and ram – and which should be able to run circles around the big multi-user database – just dies on large numbers of inserts. Sure, there’s the overhead of the entity framework, but even using ado again, it still isnt much faster. And yes, I’m using indexing correctly etc.

So what am I doing in my app to make it perform again? Yep, it’s back to using the direct-to-table hack like we had to do back in the Windows CE days. Right now I’m actually using Erik’s “Sql CE Bulk Copy” tool- at http://sqlcebulkcopy.codeplex.com – to help accomplish this.

Feels like 2003 again.

Bing shopping feed

With google beginning to charge for their google shopping feed inclusion, I was interested to hear recently about Microsoft Bing (along with Yahoo shopping) offering their Bing Shopping feed as a free place to list your products. It’s been out there a while, but we weren’t that interested until uncle google pulled this stunt. So for our paintball product brand (if you know me, you know who I’m talking about) I decided to give this a try.

So last night I dug into the spec required for creating a feed file. I was hoping I could send our google feed file directly to them and they’d crunch it, but no such luck. After signing up and reading the spec, they apparently want the feed format in… tab delimited flat file. So microsoft, who basically invented xml, is apparently going old-school on this.

I exported the google feed file and dumped it in a spreadsheet to attempt to map over all the data (My intention is to do this just once for now, until our ecommerce software gets a bing feed plugin implemented). The mapping wasnt too difficult, just rename some fields, change some number formats etc. But when I attempted to upload the file, I got a bunch of errors about unicode characters, html encoding, etc etc. Thus began a circle of hunt down the error source, fix it, resubmit and pray it works this time. I’m actually not going to admit how much time I wasted doing this, before it finally accepted the file (still with a warning about one price being suspiciously low, but hey it’s a 20 cent sticker, so I can’t “fix” that.)

Went to bed with a small feeling of accomplishment, only to be greeted this morning with a rejection notice from bing shopping.

Keep in mind, this paintball company is a well known brand in the industry, over 10 years old, and is doing nothing outside the terms of service that should get it rejected. What the heck is the reason for this?

Well, I’ll just click “chat” and we’ll figure this out. Nope, they’re busy and can’t chat (of course!), but please fill out this form and we’ll get back with you.

So, this is my first experience with bing shopping… Not a good one so far. I could possibly understand a rejection if we were some flimsy amazon reseller shop etc, but this is a very legit, established, reputable, and old company trying to apply (did I forget any adjectives?).

Undelete popular tabs in IE9

Using IE9, I like the “popular sites” list of links that shows up when a new tab is opened. Of course google is one of the first ones.. except I accidently removed it a while back, and now can’t figure out how to “un-remove” it.

Some searching online found out there is no real way to do this (wooooops!) but deleteing the keys in the registry seems to fix it: wipe anything under this path-

HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\TabbedBrowsing\NewTabPage\Exclude

bam, worked for me.

Installshield sucks. Thanks Microsoft.

Adding an installation project to my solution in vs2010, i notice an “installshield le” option for installation projects. ok, whats this? I do some research and apparently microsoft is abandoning their own installation projects, and will be moving everyone to use this “light edition” of installshield in the future.

I’ve used installshield in the past. Surely it’s not as bad as it used to be.

I add the new installshield le project, and am instructed I need to download it. this requires registration with “flexera”, who apparently now owns installshield, or whatever. create an account, give all my personal info. get a registration key in the mail. download installer and run it.

now, go back to visual studio, try to add the installer project again. Please enter serial number first problem – error, cant authorize. what? go through registration process again and try with new key. finally works.

now i finally have an install project in my solution. go through the setup steps using the weird navigation system (yes, it’s weird).

ok, we’re ready! build. what? errors?

get some kind of crazy “cant set codepage for lcid 1033”, as well as some other nonsense. click the error message, and am take to the “flexera” site, which lists a bunch of matches on this error message. no real solutions found, the matches listed date back to 2009 and 2007, so definitely not something new. I still haven’t resolved this, but do not look forward to dealing with it.

So, not much has changed. Same old cryptic BS dealing with installshield, and apparently microsoft has given up trying to provide something better. So this is what you get with a mult-thousand dollar license of visual studio 2010. Thanks Microsoft! for the opportunity to remember something i hate worse than crystal report.