Finished!

Well, I've finally successfully finished upgrading everything to SQL CE, with only a few other hiccups. 1 was the fact that SELECT TOP ? PERCENT doesn't work, although SELECT TOP does. In the end, that's not too big of a deal for me. The other was a little more difficult to figure out.

Our intent is to send our product with the SQL CE database included in the install. We need to be able to support 2 types of users, those who will be synchronizing with a server, and those who will not. I created the base compact database using synchronization, and delteted the subscription inside it, thinking that if the user was going to decide during installation to connect to the server, that the subscription could be re-created. This didn't seem to be the case. I kept getting 'this database is not configured for replciation' errors when i tried to create a subscription through code. Plus, a database that is synchronized to one database cannot have it's subscription changed to point to another database.

Because of that, I believe i will have to package separate databases with our installation, and let the installer choose which one to make active (getting rid of the other ones after install). Unless there's a better way out there, this seems to work well enough for me for right now.

Update to Compact

Ah, figured out my issue with "SELECT @@IDENTITY", that doesn't work with uniqueidentifier columns. For that, the best response will be probably to call a Guid.NewGUID in code, and insert that directly to the database, rather than let the database create the GUID itself.

There is talk though that Guid.NewGUID might not be the fastest option to use though.
http://www.peterfoot.net/NeedAGUIDInAHurry.aspx

moving to SQL compact

I've decided to scrap SQL 2008 express and move straight to SQL compact. I've been uncomfortble with using SQL express for a while, mostly because of how bulky it is (the install takes a while, and setting up a database server on the client machine, however little impact. Plus, I was also worried about issues with clients that might have already had a version of SQL express, if they had a different instance on and whether or not my prereq would recognize that. SQL compact seems to fix most of those issues. My main issue with SQL compact was the lack of stored procedures, but this site:
http://blogs.msdn.com/stevelasker/archive/2008/02/11/stored-procedures-and-sql-server-compact-the-great-debate.aspx
has made me more comfortable with that fact.

So far, I've found out these few issues with SQL compact that I've had to get around:

- no support for VarChar in SQL compact, all of my parameters need to be changed to nVarChar. Not a big deal.
- on connectionstrings.com, they tell me to use the Persist Security Info=False parameter in the connectionstring, but I kept gettings errors with that. Removing that parameter made the connection work fine.
- multiple queries in one command execution isn't supported. If i want to access the identity column of a row I just inserted, I have to use "select @@identity", using the same connection object that i used to make the insert. I've tried to do this however, and I keep getting a result back of dbnull . Not sure what is going on here.

Right now

This is my first post, and I thought that I'd write about what I'm working on right now. SQL 2008 just came out, and I've decided to try to test it out with our server, hoping that it might fix some virtual memory issues we were having with replication (http://forums.microsoft.com/msdn/showpost.aspx?postid=296390&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0). So far, it seems to work great, except that you need windows installer 4.5 to run it, and they don't have bootstrapper packages for either that or SQL 2008 express.

Visual studio SP1 just came out a few days ago too. Theres documentation on how to create a bootstrapper package for .Net 3.5 SP1 here http://download.microsoft.com/download/A/2/8/A2807F78-C861-4B66-9B31-9205C3F22252/VS2008SP1Readme.htm#Product%20Issues
but I guess they don't have anything similar for 4.5 or SQL 2008 yet.

The decision to use SQL express was made a long time ago, and it was pretty misguided. I'm planing on updating to SQL Compact pretty soon, and that at least should take care of that problem. The only issue I've had with SQL CE though is that I can't get web replication to work over SSL. I keep getting the error "A request to send data to the computer running IIS has failed.". When I look for solutions to that error, most seem to mention running the SQLCE client on a different domain than the server can cause problems (which they are in my case), but no real solution to fix the issue.