SQL statements not supported for SQL Azure

SQL statements not supported for SQL Azure

When working with SQL Azure, you primarily have to work with Transact SQL (TSQL) instead of a graphic user interface. There is limited functionality that is available with “Project Houston” that will give you somewhat of a basic user interface for working with SQL Azure. As such you need to have a good grasp on Transact SQL.

Having said that, you will find that quite a few SQL statements are just not supported for SQL Azure Cloud databases. Some of these make sense as they interact with the operating system or the underlying hardware. Remember SQL Azure is in the cloud environment so an end-user will have limited need for Hardware/OS related issues. Microsoft is supposed to take care of those! Regardless we have compiled a few SQL commands that you may have used in your on-premise SQL Server 2008, however they will simply not work in SQL Azure.

Here we go:

1. BACKUP DATABASE:

This command is used to make a copy of the database. Typically you make a copy to your local disk or a network drive. When working with SQL Azure, you do not have access to underlying discs or drives. As such that this command will not function in SQL Azure. Let’s go ahead and pass this on a local SQL Server first and then on in clouds SQL Server.

–1. BACKUP DATABASE

BACKUP DATABASE [Northwind]
TO  DISK = N’C:\SQL Backups\Northwind.bak’
GO

/* LOCAL SERVER

Processed 168 pages for database ‘Northwind’, file ‘Northwind’ on file 2.
Processed 5 pages for database ‘Northwind’, file ‘Northwind_log’ on file 2.
BACKUP DATABASE successfully processed 173 pages in 1.317 seconds (1.026 MB/sec).

*/

/* CLOUD SERVER

Msg 40510, Level 15, State 2, Line 1
Statement ‘BACKUP DATABASE’ is not supported in this version of SQL Server.

*/

2. USE command:

After working with SQL Azure for months, I am still struggling with this one! In an on-premise database sql server, you can easily switch between databases on the same server. However this does not happen on SQL Azure, you first have to browse to the database and then open up a query from there. Somewhat annoying if you ask me!

–2. USE [DATABASE]

USE Northwind
GO

USE MASTER
GO

/* LOCAL SERVER

Command(s) completed successfully.

*/

/* CLOUD SERVER

Msg 40508, Level 16, State 1, Line 1
USE statement is not supported to switch between databases. Use a new connection to connect to a different Database.

*/

3. Adding a SQL login to a SQL Server role:

As you are unable to manage a SQL Azure server, the functionality to add a Server role has been removed. Here’s an example of this in action:

–3. Assign a login to SYSADMIN server role

EXEC SP_ADDSRVROLEMEMBER ‘HR_OWNER2′, ‘SYSADMIN’

/* LOCAL SERVER

Command(s) completed successfully.

*/

/* CLOUD SERVER

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘sp_addsrvrolemember’.

*/

4. RESTORE DATABASE:

Since you are unable to backup a database, it makes sense that you will not be able to restore a database in SQL Azure. We have included the TSQL syntax along with the execution information in local SQL Server and on a cloud SQL Azure environment.

–4. RESTORE DATABASE

RESTORE DATABASE [Northwind] FROM
DISK = N’C:\SQL Backups\northwind.bak’ WITH  FILE = 1,
MOVE N’Northwind’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Northwind.mdf’,
MOVE N’Northwind_Log’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Northwind_log.ldf’,
NOUNLOAD,  REPLACE,  STATS = 10

/* LOCAL SERVER

11 percent processed.
21 percent processed.
31 percent processed.
41 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
91 percent processed.
100 percent processed.
Processed 408 pages for database ‘Northwind’, file ‘Northwind’ on file 1.
Processed 2 pages for database ‘Northwind’, file ‘Northwind_log’ on file 1.
Converting database ‘Northwind’ from version 655 to the current version 661.
Database ‘Northwind’ running the upgrade step from version 655 to version 660.
Database ‘Northwind’ running the upgrade step from version 660 to version 661.
RESTORE DATABASE successfully processed 410 pages in 0.808 seconds (3.958 MB/sec).

*/

/* CLOUD SERVER

Msg 40510, Level 15, State 2, Line 1
Statement ‘RESTORE DATABASE’ is not supported in this version of SQL Server.

*/

The next three examples include the following commands:

5. XP_CMDSHELL:

This is a powerful SQL command that can let you intrract with the operating system.

6. XP_FIXEDDRIVES:

In a similar fashion this SQL Server stored procedure will give you vital information on the underlying drives on your database server.

7. XP_READERRORLOG:

When troubleshooting SQL Server, this one is handy as it can give you the same information as the error log from SSMS.

–5. XP_CMDSHELL – very useful but potential dangerous extended stored proc

EXEC MASTER..XP_CMDSHELL ‘DIR C:\’

/* LOCAL SERVER (contents of C drive)

Volume in drive C has no label.
Volume Serial Number is A80F-F48C
NULL
Directory of C:\
NULL
02/20/2010  04:58 PM    <DIR>          Ascii
02/16/2010  08:02 PM            12,170 AsciiFilename.txt
09/18/2006  04:43 PM                24 autoexec.bat
09/16/2008  06:23 AM       552,324,608 AW_alldata.bak
03/12/2011  05:17 PM    <DIR>          Azure
10/05/2008  07:29 AM           101,880 Baclup_job.log
09/18/2006  04:43 PM                10 config.sys

*/

/* CLOUD SERVER

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘MASTER..XP_CMDSHELL’.

*/

–6. The next extended stored procedure is useful in returning information on physical drives

EXEC MASTER..XP_FIXEDDRIVES

/* LOCAL SERVER

C    67561
D    6310
K    87485

*/

/* CLOUD SERVER

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘MASTER..XP_FIXEDDRIVES’.

*/
–7. This next extended stored procedure returns info from the error Error log file

EXEC MASTER..XP_READERRORLOG
/* LOCAL SERVER

2011-04-20 06:53:58.490    Server    Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (Intel X86)    Apr  2 2010 15:53:02    Copyright (c) Microsoft Corporation   Enterprise Evaluation Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)
2011-04-20 06:53:58.540    Server    (c) Microsoft Corporation.
2011-04-20 06:53:58.540    Server    All rights reserved.
2011-04-20 06:53:58.540    Server    Server process ID is 2336.
2011-04-20 06:53:58.540    Server    System Manufacturer: ‘Dell Inc.’, System Model: ‘Inspiron 530′.
2011-04-20 06:53:58.560    Server    Authentication mode is MIXED.
2011-04-20 06:53:58.570    Server    Logging SQL Server messages in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG’.
2011-04-20 06:53:58.680    Server    This instance of SQL Server last reported using a process ID of 2356 at 4/19/2011 10:02:53 PM (local) 4/20/2011 3:02:53 AM (UTC). This is an informational message only; no user action is required.
2011-04-20 06:53:58.680    Server    Registry startup parameters:     -d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf    -e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG    -l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2011-04-20 06:53:58.960    Server    SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2011-04-20 06:53:58.960    Server    Detected 2 CPUs. This is an informational message; no user action is required.
2011-04-20 06:54:00.450    Server    Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2011-04-20 06:54:01.990    Server    Node configuration: node 0: CPU mask: 0×00000003:0 Active CPU mask: 0×00000003:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2011-04-20 06:54:03.010    spid7s    Starting up database ‘master’.
2011-04-20 06:54:03.940    spid7s    Recovery is writing a checkpoint in database ‘master’ (1). This is an informational message only. No user action is required.
2011-04-20 06:54:05.340    spid7s    Resource governor reconfiguration succeeded.

*/

/* CLOUD SERVER

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘MASTER..XP_READERRORLOG’.

*/

8. Global temporary tables:

We do have support for temporary tables in SQL Azure, however not Global temporary tables. This does make sense as one should not be able to get information across different connections on the same SQL Azure Cloud server.

Here are some example from the local SQL Server and Cloud Azure SQL.

–8. GLOBAL TEMP TABLES

CREATE TABLE ##GLBL_ACCT1
(
ACCT_ID INT,
ACCT_NAME VARCHAR(50)
)

/* LOCAL SERVER

Command(s) completed successfully.

*/

/* CLOUD SERVER

Msg 40516, Level 15, State 1, Line 1
Global temp objects are not supported in this version of SQL Server.

*/