FIX: EXECUTE Command w/ a Local Variable Can Cause Thread AV

• Microsoft SQL Server, version 6.0

SYMPTOMS

Invalid syntax when you build an EXECUTE statement from a local variable can cause a thread access violation (AV).

The access violation can be generated when Microsoft SQL Server version 6.0 attempts to resolve a stored procedure name that was built in a declared variable. SQL Server generates a thread access violation and aborts the connection.

CAUSE

The error occurs when the EXECUTE statement treats an oversized buffer (more then 30 characters) as a stored procedure name instead of as a specific Transact-SQL command due to the use of invalid syntax for the EXECUTE statement itself.

For example:

   use pubs
   go

   declare @strCommand  char(128)

   select @strCommand = 'select * from authors'

   execute @strCommand
   go

DECLARATION      BEHAVIOR
-----------      ---------

varchar(xxx)     Successful completion of command, or appropriate error
                 returned:
                 'Unable to find stored procedure 'select * from authors'

char(30)         Successful completion of command, or appropriate error
                 returned:
                 'Unable to find stored procedure 'select * from authors'

char(128)        Thread access violation.

WORKAROUND

• Ensure the syntax of the EXECUTE command is correct. If the command you are attempting to execute is not a stored procedure, place parenthesis around it. See "SQL Server Books Online" for the documented syntax of the EXECUTE command. Other commands may include local variables larger than char(30).
• Use char(30) when you execute a stored procedure to guarantee object identifier length.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. This problem was corrected in Service Pack 1 for SQL Server version 6.0. For more information, contact your primary support provider.