Connecting to SQL Server Express from a Pocket PC application

del.icio.us tags: SQl Server Express, Visual Studi o2005, .NETCF 2.0, Pocket PC Applications, Software Development Tips

I was recently trying to do a small sample application with SQL Express and Pocket PC's. Basically I wanted to try out a code to automatically synchronize with a database running on a network, when the application was cradled.

My solution was very simple. I used a dataset in the PPC application and updated to and from it to the database.

From my previous experience with PPC applications under .NETCF1, I remembered that PPC apps need the IP of the database in the connection string. Also, you have to set the SQL Server Express to accept TCP/IP connections as well as SQL Server Authentication. Instructions for all of that can be found on Daniel Chong's blog post. So I will not delve into that further here.

But the problem I faced was, no matter what I tried, I got an SqlException called "SQL Server does not exist or access denied." I searched the web high and dry and I still I was baffled. Then I tried trying out different combinations for the connection string and I realized that my emulator was not connecting to my PC at all. Delving in deeper, I realized I was running a wrong ActiveSync version. I was running version 3.8 and Visual Studio 2005 requires version 4.0 or higher. So I upgraded my ActiveSync version and enabled network connectivity on the emulator, everything worked great!

So a few pointers to remember when accessing SQL Express databases from Pocket PC (.NETCF2.0) projects:

1. Ensure that SQL Express is set to accept remote connections. By default this is disabled.
2. Ensure that your SQL Server is set to accept SQL Server Authentication as well.
3. Fix the port for the SQL Express database.
4. Create the connection string with the IP and the Port. eg: Data Source = 192.168.1.2,1433;Initial catalog=Pubs;User Id=****, Password=***;
5. SQL Server Express installs with an instance name. Never give this instance name in your connection string. eg: Data Source=192.168.1.2\SQLEXPRESS... This gave my application an error which could not be caught with exception handling within the managed code.

That's about it. Hope this helps someone out there. Any questions or clarifications, just ask.