Skip to content
September 10, 2011 / thewiseguy99

SQL Azure migration – errors & solutions

If you get this error migrating a procedure or function –> Error #: 40512 — Deprecated feature ‘String literals as column aliases’ is not supported in this version of SQL Server.
Then your statement will look like this,

SELECT 'ImageSupportDescription' = CASE 
WHEN c.description is null THEN 
 mes.name 
ELSE 
 mes.name + ' (' + c.description + ')' 
END, ...

And needs to become this,

SELECT CASE 
WHEN c.description is null THEN
 mes.name 
ELSE
 mes.name + ' (' + c.description + ')' 
END AS 'ImageSupportDescription', ...

If you get this error migrating a procedure or function –> Error #: 40516 — Global temp objects are not supported in this version of SQL Server.
Then your statement will look like this,

INSERT INTO INTO ##t_temp_property 
SELECT p.id , e.id as EntityId , o.id as OrganizationUnitId 
FROM ...

And needs to become this,

CREATE TABLE #t_temp_property(id INT NOT NULL, EntityId INT, OrganizationUnitId INT) 
-- Make sure you define this in-scope so the other statements which call it can see it.

INSERT INTO INTO #t_temp_property
 SELECT p.id
 , e.id as EntityId
 , o.id as OrganizationUnitId
 FROM ...

Since SQL Azure is a shared database among many users truly global tables are not allowed. I know the above change looks weak but if you need access to this temp table out of the statement context you will need to investigate a more specific solution based on your problem. In our case above someone in the past used a global temp table when a temp table would suffice.

Another approach you can take is to define a TABLE variable within the scope of the statement. I am not convinced this will perform as well as a temporary table but also it will not work if the statement was defined as dynamic SQL.

DECLARE @TemporaryTable TABLE ( Id int, EntityId int, OrganizationUnitId int )

INSERT INTO @TemporaryTable (Id, EntityId, OrganizationUnitId)
 SELECT p.id
 , e.id as EntityId
 , o.id as OrganizationUnitId
 FROM ...

If you get this error migrating a procedure or function –> Error #:102 — Incorrect syntax near ‘50001’.
Then your statement will look like this,

IF NOT EXISTS ( SELECT ID FROM UserDefaults WHERE FkSite= @siteId ) 
BEGIN 
 RAISERROR 50001 'User Defaults are missing' 
 SELECT 0 AS [ID] 
 RETURN 
END ...

And needs to become this,

IF NOT EXISTS ( SELECT ID FROM UserDefaults WHERE FkSite= @siteId) 
BEGIN
 RAISERROR ('User Defaults are missing', 16, 1) 
 -- Severity between 11 - 18 will actually raise the error 
 SELECT 0 AS [ID] 
 RETURN 
END ...

Since SQL Azure doesn’t support sp_addmessage so you can not add the sub 50000 errors you may have hoped –
Even more, it does not support sys.messages either –
This said you will not have the ability to define application behavior based on the error number raised in the SQL statment and propigated out into the SqlException’s Error collection within your code –

If you get this error migrating a procedure or function –> Error #:40515 — Reference to database and/or server name in ‘master.dbo.sysprocesses’ is not supported in this version of SQL Server.
Then your statement will look like this,

SELECT * FROM master.dbo.sysprocesses WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

And needs to become, well nothing since this is due to the shared nature of SQL Azure.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: