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.
*/
Pingback: Alexander7
I was very pleased to find this web-site.I wanted to thanks for your time for this wonderful read!! I definitely enjoying every little bit of it and I have you bookmarked to check out new stuff you blog post.
I liked your article is an interesting technology
thanks to google I found you