USE statement is NOT supported in SQL Azure CTP1

So, now that you’ve got a token and can connect to SQL Azure, you probably want to create a database and start using it.  There’s one thing that can cause a ton of heart ache…  USE <database> is not supported!

The reason USE <database> isn’t support is because when you connect to one database you are essentially being tied to a particular server cluster via the SQL Azure TDS Gateway.  Your database aren’t all on the same physical machine, therefore you must specify the database when you connect.  Does that make sense?

For example, let’s say you are using SQLCMD or SSMS to work with SQL Azure…  Let’s look at a common use case.

First, I’ll connect to SQL Azure with my Administrative User that I created when I redeemed my token.  In this case that username is admin.  This user can be thought of like sa in on-premise SQL Server.  I’ll connect to a server named h38ssfjeiwh201 which was automatically assigned to me.  In this case I won’t specify a database name, so it will default to my virtual master database.

NOTE: In these samples I’m showing connecting via SQLCMD but you won’t get all of the same messages on success, although the errors will be the same.

sqlcmd -S h38ssfjeiwh201.ctp.database.windows.net -U admin@h38ssfjeiwh201

Now, I’ll go ahead and create a new database named sample_db with TSQL.

CREATE DATABASE sample_db;
GO

NOTE: Future versions of SQL Azure will support a MAXSIZE clause to the CREATE DATABASE statement for create 1GB or 10GB databases.

I’ll get a message saying things are looking good.

Command(s) completed successfully.

Great!  Now, I want to start using that database…  So like any novice SQL Server developer I try to use it.

USE sample_db;
GO

The problem is I get the following error…

Msg 911, Level 16, State 1, Line 1

Database ‘sample_db’ does not exist. Make sure that the name is entered correctly.

Uh…  Ok.  Let me try again…  I thought I typed it right but maybe I’m missing something.

CREATE DATABASE sample_db;
GO

Now I get the following error…

Msg 1801, Level 16, State 1, Line 1

Database ‘sample_db’ already exists. Choose a different database name
.

I know… I know… These error messages need to be improved. The problem is like I mentioned in the title of this post. SQL Azure does not currently support the USE <database> statement.

What I need to do is actually reconnect by specifying the database name as a connection parameter in SQLCMD or by specifying the Connect to database in the Options >> on the connection dialogue in SSMS.

sqlcmd -S h38ssfjeiwh201.ctp.database.windows.net -U admin@h38ssfjeiwh201 -d sample_db

Now I can go about interacting with sample_db just like I would any other SQL Server database (for the most part).

For additional information about this see the Intro to SQL Azure hands on lab and demos in the Windows Azure Platform Training Kit.

SQL Azure Invitation Codes

Updated March 2010: In February of 2010 the Windows Azure Platform reached commercial availability. The benefit of this is the platform is ready for production use. One of the downsides is that you can’t get invitation codes for free. There are some offers available for getting started with Windows Azure, SQL Azure and AppFabric. You can check these out here: http://www.microsoft.com/windowsazure/offers/.

Now that CTP1 is out the door a question that keeps coming up is…  “I registered for a token a couple months ago…  When am I going to get an email with my Invitation Code?”  We are glad that people are as excited about the SQL Azure release as we are so we love this question!

Over the next week or two everyone who has already signed up for a SQL Azure Invitation Code should be receiving an email sent to the address associated with your Live ID containing the token and a link to redeem it.  We understand that everyone would like their tokens yesterday but we need to work through the list and ramp up the service.

Once the list of current requests has been processed, new requested will be fulfilled within a day or two.

We are working on integrating the SQL Azure and Windows Azure provisioning experience.  We realize that it is very inconvenient to have to have to make requests for two different tokens from different places. 

What about customers who already have an account on the previous version of SQL Data Services/SQL Server Data Services which had an ACE model with a REST API?  When will they get tokens?  We will be providing all of those users with a token, but in the meantime I’d recommend that all of those users sign up for the CTP.

If you haven’t already done so, please sign up for the CTP today!
http://go.microsoft.com/fwlink/?LinkID=149681&clcid=0x09

Connecting to SQL Azure

So, now that SQL Azure CTP1 has been released there are a few things that most people will probably run into when trying to connect to SQL Azure with SQL Server Management Studio (SSMS), SQLCMD and OSQL.

SQL Server Management Studio

Update March 2010: With the release of the SQL Server 2008 R2 Tools November CTP working with SQL Azure from SSMS is much better.  Please read my new post if you want to use the updated tools.

One of the best parts about working with SQL Azure is that you can use the tools that you already know and love.  There are some differences though with the System Views, System Stored Procedures, System Tables and SMO that break some of those tools.  The current version of SSMS 2008 is able to connect to SQL Azure but the Object Browser doesn’t work.  There is also a trick to getting connected.

When you fire up SSMS and are presented with connection dialog box, if you enter your credentials you will get the following error.

TITLE: Connect to Server
——————————
Cannot connect to MY_SERVER_NAME.ctp.database.windows.net.
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Invalid object name ‘sys.configurations’. (Microsoft SQL Server, Error: 208)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.9006&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476

The key to getting around this is to click on the Cancel button on the initial dialogue box.

image

Then click on the New Query button.

image

You will be presented with a new connection dialogue that looks the same as the initial one.

image

Enter your credentials, if you are trying to connect to a specific database click on the Options and enter a database name because USE <database> is not supported.

Once you connect you will get a error

TITLE: Microsoft SQL Server Management Studio
——————————
Unable to apply connection settings. The detailed error message is: ‘ANSI_NULLS’ is not a recognized SET option.

You can just click OK.

Now you are good to go!  You can issue queries, visualize explain plans, etc.

 

SQLCMD and OSQL

For those of us who like to use command line tools, SQLCMD and OSQL are also supported.  The key when connecting with these tools is that you must use the <username>@<servername> in the connection string.

sqlcmd -S MY_SERVER_NAME.ctp.database.windows.net -U MY_USERNAME@MY_SERVER_NAME -d DATABASE_NAME

The key here is that you must use MY_USERNAME@MY_SERVER_NAME…  For example, if your server name is h38ssfjeiwh201, your username is admin and connecting to sample_db your connection would be.

sqlcmd -S h38ssfjeiwh201.ctp.database.windows.net -U admin@h38ssfjeiwh201 -d sample_db

Since SQL Azure doesn’t currently support the USE <database> you need to specify a database name.

 

More Information

SQL Azure CTP1 is finally here!

It’s a really exciting day for everyone involved in SQL Azure as today we’ve announced that CTP1 is available to customers.

Over the next couple of weeks everyone who has already requested an Invitation Code should be receiving one in an email. If you haven’t already requested on please do so here. Once we get through the list of developers who have already requested access, new requested will be processed in a day or two.

We’ve delivered a bunch of new SQL Azure content in the August release of the Windows Azure Platform Training Kit including:

Presentations

  • Introduction to SQL Azure
  • Building Applications using SQL Azure
  • Scaling Out with SQL Azure

Demos

  • Preparing your SQL Azure Account
  • Connecting to SQL Azure
  • Managing Logins & Security in SQL Azure
  • Creating Objects in SQL Azure
  • Migrating Database Schemas to SQL Azure
  • Moving Data into and out of SQL Azure using SSIS
  • Building a Simple SQL Azure App
  • Scaling Out SQL Azure with Database Sharding

Hands on Labs

  • Introduction to SQL Azure
  • Migrating Databases to SQL Azure
  • Building Your First SQL Azure App

The SQL Azure documentation on MSDN is also a great learning resource.

SQL Azure Documentation is Live!

As the SQL Azure CTP comes closer we’ve released the documentation which can be found here
http://msdn.microsoft.com/en-us/library/ee336279.aspx

This should help developers who are waiting on their invitation token to get started learning about the details of SQL Azure.  Make sure you’ve signed up for access here
http://go.microsoft.com/fwlink/?LinkID=149681&clcid=0x09

Enjoy!