In the Feb. 2010 blog post, “Using SQL Server Client APIs with SQL Azure Version 1.0”, Steve Hale provided details regarding similarities and differences to watch out for when developing an application with SQL Azure. In this post, the focus is on tips of resolving issues once the application encounters an error condition. Please note that the exact error messages may differ slightly, but this should provide a good starting point.
Now, let’s jump right into it. Starting with the initial setup of the database table:
Msg 40054, Level 16, State 1, Server server19, Line 1
Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.
As the error message stated, you’ll need to create a cluster index on the table. The following sample demonstrates how you can create the cluster index:
Create table employees(EmployeeID int, EmployeeName varchar(30))
Create clustered index empIndex on employees(EmployeeID)
Please note that in order to insert data into a SQL Azure database table, you will need to have a cluster index anyways, so it is definitely in your interest to create the cluster index from the start.
Now that your database table is created, time to connect to the database. During connection, if you encounter:
Msg 40531, Level 11, State 1, Server servername.mscds.com, Line 1
Server name cannot be determined. It must appear as the first segment of the server's dns name (servername.mscds.com). Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername). In addition, if both formats are used, the server names must match.
Verify that the “servername” is specified as part of the user name in the format the error message stated (uid = username@servername).
Another error you may encounter is:
HResult 0x35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server .
Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Microsoft SQL Native Client : Login timeout expired.
In this scenario, there can be a couple of different configuration issues. Double check the following:
Now that you can establish a connection to your SQL Azure database, the following are a few things to watch out for in your application:
If you do use any of these above syntax in your application, you will most likely encounter one of the following error messages:
SQLState=HY024: Message = "[Microsoft][SQL Server Native Client 10.0]Database is invalid or cannot be accessed“
SQLState=25S12: Message=[Microsoft][SQL Server Native Client 10.0]The partner transaction manager has disabled its support for remote/network transactions.
There are many similarities between programming for SQL Server and for SQL Azure. But, there are also a few things to watch out for. Hopefully these troubleshooting tips help you to recognize the differences quickly and resolve the problems.
Steve Hale and Jimmy WuMicrosoft SQL Server