[dba-SQLServer] Very strange behavior

Arthur Fuller fuller.artful at gmail.com
Wed Sep 10 14:59:17 CDT 2008


The following code declares a cursor and then generates one SQL
statement per table in the current database, placing the statement into the
cursor. We then walk the cursor and either print or execute each statement.
Run the code as is against any database. In this example I'm using
Northwind:

<sql>
-- Build Cursor of UPDATE STATISTICS Statements
DECLARE Statement_Cursor CURSOR
    FOR SELECT
  CHAR(39) + 'UPDATE STATISTICS ' + SCHEMA_NAME() + '.' + sysobjects.NAME +
CHAR(39)
        FROM
            sysobjects
        WHERE
            TYPE = 'U'
OPEN Statement_Cursor
FETCH NEXT FROM Statement_Cursor INTO @statement

WHILE @@FETCH_STATUS = 0
    BEGIN
--  SET @statement = CHAR(39)+ at statement+CHAR(39)
--  This is the test statement to ensure that it works
       print @statement
--  This is the production statement that will take a long time
--  EXEC (@statement)
        FETCH NEXT FROM Statement_Cursor INTO @statement
    END
CLOSE Statement_Cursor
DEALLOCATE Statement_Cursor
-- example:
/*
USE NorthwindCS
DECLARE @sql VARCHAR(255)
SET @sql = 'UPDATE STATISTICS dbo.Employees'
--print @sql
EXEC (@sql)
*/
</sql>

To repeat my experiments, do this:

1. Run the code as is. It will print out lovely SQL statements.

2. Uncomment the EXEC(@statement) line and run the code again. You will get
a bunch of errors.

3. Select the code within the example and run it. The code works and you get
a nice message.

The string being passed to the EXEC in the example is identical to those
produced in the main code. Why it works in one case and not the
other baffles me.

Got any ideas?

I think this can be a cool and useful chunk of code once I figure a way out
of this problem. I have had my colleague Matthew look at it and he too is
baffled. So we need a third and maybe fourth and maybe fifth set of eyes.

TIA,

Arthur



More information about the dba-SQLServer mailing list