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 [53].

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:

  • Verify the local firewall is not blocking outbound network traffic on TCP 1433.
  • Verify the Forefront Threat Management Gateway (TMG) or Internet Security Acceleration (ISA) Firewall client, or equivalent is installed, if the company firewall blocks non-authenticated traffic.
  • Verify using the SQL Azure portal that the client IP address is in the set of SQL Azure allowed IP ranges.

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:

  • Do not execute any “USE <database>” statements.  Always connect directly to the database the application requires.
  • Use at most 2 part names in your query statements.  Even though 3 part names are supported, the database portion of the 3 part name must be the name of the current database.  Linked servers (4 part names) are not supported in SQL Azure.
  • Do not call SQLSetConnectAttr() to set SQL_ATTR_CURRENT_CATALOG or SQL_ATTR_ENLIST_IN_DTC
  • Do not call SQLSetConnectOption() to set SQL_CURRENT_CATALOG

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 Wu
Microsoft SQL Server