Archive

Archive for May, 2009

Don’t trust SQL Server 2005 “Deferred Name Resolution”

May 29th, 2009

Take a look at the following stored procedure (as always, meant to be created against the AdventureWorks database):

CREATE PROCEDURE MyProcedure

AS

    IF 1 = 0

        SELECT TOP 10 * FROM Person.WrongTable

    ELSE

        SELECT TOP 10 * FROM Person.Address

As I’ve mentioned before, this stored procedure will be created by SQL Server 2005 without throwing any error message or warning. The Person.WrongTable table will be treated as a missing reference, and “Deferred Name Resolution and Compilation” will come into play. What will happen then? The names will be resolved upon execution:

When a stored procedure is executed for the first time, the query processor reads the text of the stored procedure from the sys.sql_modules catalog view and checks that the names of the objects used by the procedure are present. This process is called deferred name resolution because table objects referenced by the stored procedure need not exist when the stored procedure is created, but only when it is executed.

According to the Books Online, then, the stored procedure should fail upon execution on the resolution stage. But it doesn’t! - try it out yourself. It seems as if the query processor realizes the first statement is unreachable and doesn’t bother to resolve the names there.

Now, what happens when the condition in the IF statement depends on a parameter? Let’s see:

CREATE PROCEDURE MyProcedure2

    @flag bit

AS

    IF @flag = 1

        SELECT TOP 10 * FROM Person.WrongTable

    ELSE

        SELECT TOP 10 * FROM Person.Address

This stored procedure only fails when executed with @flag equal to 1. But this behavior is not documented.

Let’s see another interesting case: instead of a parameter, the condition will depend of a value stored in a table. We need to create a test table first:

CREATE TABLE TestTable

(

    id int IDENTITY(1,1), flag bit

)

INSERT INTO TestTable(flag) VALUES(0)

INSERT INTO TestTable(flag) VALUES(1)

Now we can create the following stored procedure:

CREATE PROCEDURE MyProcedure3

AS

    DECLARE @flag bit

    SELECT @flag = flag FROM TestTable WHERE id = 1

    IF @flag = 1

        SELECT TOP 10 * FROM Person.WrongTable

    ELSE

        SELECT TOP 10 * FROM Person.Address

We are setting @flag to a value stored on the first row of our TestTable table. The execution succeeds and returns the 10 first rows of the Person.Address table. Let’s change the procedure to read the flag from the second row instead:

SELECT @flag = flag FROM TestTable WHERE id = 2

Now the execution fails (finally!):

Msg 208, Level 16, State 1, Procedure MyProcedure3, Line 6
Invalid object name ‘Person.WrongTable’.

What’s going on here? Again, this behavior is not consistent to what the documentation says. I doubt SQL Server is fetching the data from the TestTable table in advance to determine how the execution will go, so it must be resolving the names on the fly on a per-statement basis. I’ll check my books to see if I find more information about this “feature”.

This surely looks like a valid and even desirable optimization in most cases, but again, it’s not consistent with the documentation. According to the Books Online, the optimization of the query and the creation of the execution plan occur after the resolution stage:

If procedure execution successfully passes the resolution stage, the Microsoft SQL Server query optimizer analyzes the Transact-SQL statements in the stored procedure and creates an execution plan.

The final conclusion here is that you can’t be sure your stored procedures (or functions or any executable object for that matter) are correct until you test them with values that exercise all the possible execution paths (this has been a pain in the neck at work the last couple of days). This makes a good argument for unit testing at the database level.

Happy coding!

Julián Hidalgo SQL Server

SQL Server 2005 error handling gotcha

May 17th, 2009

One of the cool features introduced in SQL Server 2005 was the improved error handling capabilities in Transact-SQL through the try/catch construct. I’m not going to talk about it here since there is plenty of material on the subject on the web, I just want to point out a gotcha I found a few days ago at work.

Take the following stored procedure:

CREATE PROCEDURE MyProcedure

AS

    SELECT x FROM Person.Address

    SELECT y FROM Person.Contact

 

If you try to create the procedure in the AdventureWorks database it will fail because the x and y columns don’t exist:

Msg 207, Level 16, State 1, Procedure MyProcedure, Line 3
Invalid column name ‘x’.
Msg 207, Level 16, State 1, Procedure MyProcedure, Line 4
Invalid column name ‘y’.

If you execute the same query as dynamic SQL (through the sp_executesql stored procedure) you get the same 2 errors:

DECLARE @Command nvarchar(2000)

SET @Command = ‘CREATE PROCEDURE MyProcedure

AS

    SELECT x FROM Person.Address

    SELECT y FROM Person.Contact’

EXEC sp_executesql @Command

 

Now let’s use a try/catch construct to handle the error:

DECLARE @Command nvarchar(2000)

SET @Command = ‘CREATE PROCEDURE MyProcedure

AS

    SELECT x FROM Person.Address

    SELECT y FROM Person.Contact’

BEGIN TRY

    EXEC sp_executesql @Command

    PRINT ‘Procedure MyProcedure created.’

END TRY

BEGIN CATCH

    PRINT ‘Procedure MyProcedure couldn”t be created: ‘ + ERROR_MESSAGE()

END CATCH

 

This time the ERROR_MESSAGE function only returns the first error message:

Procedure MyProcedure couldn’t be created:Invalid column name ‘x’.

It looks you can only get information about the first error hat occurred in a try block. I believe the reason is that control is immediately transferred to the catch block after the first error occurs because when you execute the naked sp_execute procedure it shows the 2 errors.

Most of the time this is not an issue, unless you want to execute DDL statements (since SQL Server may report several errors). This was the case for me, I was working on a script that drops all the database objects and creates them again (to check if any of them got broken by some changes in the schema), and I wanted to report all the errors, but I had to give up and just report the objects that had errors.

If I discover a way to get around this limitation I’ll post about it, but I doubt there’s one. Over and out!

Julián Hidalgo SQL Server

Dynamic SQL and square brackets in SQL Server identifiers

May 15th, 2009

All the following identifiers are valid column names (the resulting name is shown in the comments):

CREATE TABLE Table1

(

    [[MyColumn] int, – [MyColumn

    [My[Column] int, – My[Column

    [[[MyColumn] int, – [[MyColumn

    [MyColumn]]] int, – MyColumn]

    []]MyColumn] int, – ]MyColumn

    [[]]MyColumn] int – []MyColumn

)

These are called delimited identifiers are also valid for other kind of objects like tables, constraints and so on. Obviously they should be avoided, but it’s good to be aware that they may exist in case you have to work with legacy databases or dynamic SQL. SQL Server allows any character in the current code page to be used in a delimited identifier, but square brackets in particular can break that beautiful dynamic SQL script you are working on. For example, let’s write a script that drops all the columns in the Table1 table (an artificial example but it serves to illustrate the point):

DECLARE @Command nvarchar(2000), @ColumnName sysname

DECLARE ColumnsCursors CURSOR FOR

    SELECT c.name

    FROM sys.tables t

    INNER JOIN sys.columns c ON c.object_id = t.object_id

    WHERE t.name = ‘Table1′

 

OPEN ColumnsCursors

FETCH NEXT FROM ColumnsCursors INTO @ColumnName;

WHILE (@@FETCH_STATUS = 0)

BEGIN

    SET @Command = ‘ALTER TABLE Table1 DROP COLUMN ‘ + @ColumnName

    PRINT @Command

    EXEC sp_executesql @Command

    FETCH NEXT FROM ColumnsCursors INTO @ColumnName;

END;

CLOSE ColumnsCursors;

DEALLOCATE ColumnsCursors;

The script will fail with all sorts of errors:

ALTER TABLE Table1 DROP COLUMN [MyColumn
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'MyColumn'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'MyColumn'.
ALTER TABLE Table1 DROP COLUMN My[Column
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'Column'.
ALTER TABLE Table1 DROP COLUMN [[MyColumn
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string '[MyColumn'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '[MyColumn'.
ALTER TABLE Table1 DROP COLUMN MyColumn]
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘]’.

ALTER TABLE Table1 DROP COLUMN ]MyColumn
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘]’.

ALTER TABLE Table1 DROP COLUMN []MyColumn
Msg 1038, Level 15, State 4, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘MyColumn’.

Enclosing all the identifiers with square brackets (using ‘[‘ + @ColumnName + ‘]’) is not enough, the drop of the last 3 columns will still fail:

ALTER TABLE Table1 DROP COLUMN [MyColumn]]
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ‘MyColumn]’.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘MyColumn]’.
ALTER TABLE Table1 DROP COLUMN []MyColumn]
Msg 1038, Level 15, State 4, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘MyColumn’.

ALTER TABLE Table1 DROP COLUMN [[]MyColumn]
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘MyColumn’.

To be completely safe, you also need to escape any occurrence of “]” in the identifier with “]]”:

    SET @Command = ‘ALTER TABLE Table1 DROP COLUMN [' + REPLACE(@ColumnName, ']‘, ‘]]’) + ‘]’

Note that the script will still fail on the last step because you can’t drop all the columns of a table, but you get the idea. Hope it helps.

Julián Hidalgo SQL Server

Follow-up to “Strange bug in SQL Server 2005”

May 12th, 2009

My friend Michael Dorman left the following comment on my post about a bug in SQL Server 2005:

Stored Procedure creation in T-SQL doesn’t guarantee to check to see that the dependent objects exist. That’s not a bug, it’s by design.

According to the SQL Server 2005 Books Online, Deferred Name Resolution and Compilation:

Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.

Based on this the creation of the following stored procedure should fail (since the CustomerTypeX column doesn’t exist):

CREATE PROCEDURE [dbo].MyProcedure

AS

    CREATE TABLE #temp (id uniqueidentifier)

    SELECT Customer.CustomerTypeX

    FROM Sales.Customer

    WHERE EXISTS (SELECT * FROM #temp)

GO

But SQL Server 2005 creates it without any warning or error. The reference to the temporary table on the nested query seems to make it quit any effort to resolve the identifiers (either table names or column names anywhere in the query), and I see no reason of why it should.

The behavior is also not consistent to what happens when no temporary tables are involved in the nested query. If I remove the nested query and keep everything else from the example in my previous post (I could also keep the nested query and refer to another table, say Person.Contact):

CREATE PROCEDURE [dbo].MyProcedure

AS

    CREATE TABLE #temp (id uniqueidentifier)

    SELECT CustomerXXX.CustomerType

    FROM Sales.Customer

GO

I get the following error message upon creation:

Msg 4104, Level 16, State 1, Procedure MyProcedure, Line 4
The multi-part identifier "CustomerXXX.CustomerType" could not be bound.

Why is it different? For me this is a bug.

Julián Hidalgo SQL Server

Exporting SQL Server 2005 database objects to files

May 11th, 2009

Last week I wrote a Transact-SQL script for SQL Server 2005 that exports all database objects (stored procedures, triggers, views and so on) to individual files, grouped by object type, and I decided I’d blog about it because it was trickier than I thought at first. These are the things I had to deal with to make the script work:

  • Writing text to files in SQL Server
  • Getting the definition of a SQL Server database object
  • Getting around the security restrictions of SQL Server

I’ll review each of them in order (if you just want to download the script skip to the end of the post).

Writing text to files in SQL Server

I didn’t have to think too much about this since someone already wrote a script to do it. The script basically inserts the text on a global temporary table and uses bulk copy to dump it into a file. However, I did have to modify it to make it work:

  • The script was calling the bcp command with @@servername as the server name parameter. This was causing it to fail on my work machine (but not on my personal laptop), so I changed it to use ‘’localhost’ instead.
  • I modified it to use consistent case for all identifiers so it would work on case-sensitive installations of SQL Server.

I also renamed a number of variables and formatted it a little to make it easier to read (at least in my opinion).

Getting the definition of a database object

There are a number of ways to obtain the definition (that is, the Transact-SQL source text) of a database object in SQL Server. Here are the ones I know of:

  1. Call the sp_helptext stored procedure
  2. Use the sys.syscomments system table (deprecated)
  3. Call the OBJECT_DEFINITION function
  4. Use the sys.sql_modules catalog view

It turns out you can only trust the 4th option, as the others can return outdated results. I always use sys.sql_modules anyway, because it’s the way SQL Server Management Studio does it (something I discovered by spying on it).

Getting around the security restrictions

SQL Server 2005 disables the ‘xp_cmdshell’ extended stored procedure by default (SQL Server 2000 doesn’t), so the initial script was failing with many messages like the following:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure ’sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see "Surface Area Configuration" in SQL Server Books Online.

The good thing is that the message tells you everything you need to know to solve the issue. I modified the script to enable the use of xp_cmdshell using sp_configure, and to restore the setting back to what it was after it’s finished (here I’m assuming that if you are able to run the script at all then you have the permissions to change the setting).

Special considerations for case-sensitive installations of SQL Server

I wrote the initial version of this script in a SQL Server installation that was case sensitive, and something strange happened when I tested it. The output would tell you X stored procedures were found, but there were only X-1 files in the output folder. It turned out that 2 of those stored procedures had a name that differed on the case only. Since the file system in Windows is not case-sensitive the file for one of them ended up replacing the other. I thought of modifying the script to handle this case too, but I decided it was not worth the effort.

Final notes and the script

The final script is divided into 4 stored procedures, to make things easier to understand. They are dropped at the end of execution, but end up as files in the SQL_STORED_PROCEDURE folder (I figured it was easier to just remove them from there than to make the script exclude them). You should make sure the script doesn’t overwrite any stored procedure you may already have in your database before running it.

You can grab the final script here. I hope you find it useful, but please note that I make no guarantees, so use it at your own risk! As always, I welcome all suggestions and comments about it. Happy coding!

Julián Hidalgo SQL Server

Strange bug in SQL Server 2005

May 10th, 2009

Take a look to the following stored procedure (which is meant to be run against the AdventureWorks database):

CREATE PROCEDURE [dbo].MyProcedure

AS

    CREATE TABLE #temp (id uniqueidentifier)

    SELECT Customer.CustomerType

    FROM Sales.Customer

    WHERE EXISTS (SELECT * FROM #temp)

GO

Although it doesn’t make too much sense, it looks quite simple and straightforward, isn’t it? But try changing “Customer.CustomerType” in the SELECT clause to “CustomerABCDE.CustomerType” and run it (dropping it first of course). SQL Server will happily create the stored procedure without ever complaining that the identifier doesn’t exist. You can also try changing “Sales.Customer” to “Sales.CustomerWhatever” and SQL Server will not complain either.

This is something that showed up at work, in a much bigger and complex query. By removing lots of code I nailed the problem down to what you see above: using nested queries in the WHERE clause involving temporary tables seems to confuse SQL Server. I don’t see the reason of why this should happen, since the outer query has nothing to do with the inner query.

A friend suggested it could be due to the use of the “EXISTS” keyword, but it’s not (and it shouldn’t). If you change the WHERE clause to the following the stored procedure it’s still created:

CREATE PROCEDURE [dbo].MyProcedure

AS

    CREATE TABLE #temp (id uniqueidentifier)

    SELECT CustomerXXX.CustomerType

    FROM Sales.Customer

    WHERE Customer.CustomerType = (SELECT TOP 1 * FROM #temp)

GO

I searched to see if this was a known bug, but I didn’t find anything. The stored procedure will still fail when executed, but it’s a weird bug anyway.

Julián Hidalgo SQL Server