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

ID: Q132345

The information in this article applies to:

Microsoft SQL Server version 6.0

BUG# NT: 10191 (6.00)

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

The behavior can be altered by changing the variable declarations.


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.

The behavior does not change if you use a valid stored procedure name, such as sp_who; char(128)continues to cause an access violation, and char(30) continues to execute successfully. This is because char(30) is a valid length for an object identifier.

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.

Additional query words: sql6 Windows NT


Keywords          : kbother kbbug6.00 kbfix6.00.sp1 SSrvWinNT 
Version           : 6.0
Platform          : WINDOWS 
Issue type        :

Last Reviewed: April 21, 1999