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.

One thought on “USE statement is NOT supported in SQL Azure CTP1

  1. Pingback: - David Yack's Blog! - Kicking the tires on SQL Azure

Leave a Reply

Your email address will not be published. Required fields are marked *