You are currently offline, waiting for your internet to reconnect

FIX: Cannot Use Dynamic SQL Statements Within OPENQUERY

This article was previously published under Q291376
BUG #: 351729 (SHILOH_BUGS)
An Access Violation (AV) may occur if you use the OPENQUERY function to execute a stored procedure that has these properties:
  • The stored procedure has a dynamic SQL statement that uses an OPENQUERY function.

  • You execute the dynamic SQL statement by using the sp_executesql stored procedure.
The Access Violation occurs because SQL Server cannot determine the metadata correctly. SQL Server uses SET FMTONLY ON to obtain the metadata of the string inside the OPENQUERY function. With a SET FMTONLY ON statement, the remote server cannot execute the statement and it simply compiles the statement to get the metadata returned by the statements within the stored procedure. If the statements are dynamic, there is no way for the Distributed Query Processor to determine the metadata at compile time because the value of the string is not known until execution time.
To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
The scenario is still not supported; however, the Access Violation error has been fixed in SQL Server 2000 Service Pack 1 and you now receive the following error message instead:
Server: Msg 7355, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata for a column. The name was changed at execution time.
To work around the problem, do not save the dynamic SQL statement to a variable. Instead, run the dynamic SQL statement at the point of creation by using the EXECUTE function.
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.
Following is an excerpt of the SQL Server error log from the Microsoft SQL Server 2000 release version:
2001-01-10 10:17:43.64 spid51    Error: 0, Severity: 19, State: 02001-01-10 10:17:43.64 spid51    SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..********************************************************************************* BEGIN STACK DUMP:*   01/10/01 10:17:43 spid 51**   Exception Address = 0064E187 (UpdateString(unsigned short * &,unsigned long &,unsigned short const *,unsigned long) + 0000003D Line 0+00000000)*   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION*   Access Violation occurred reading address 00000000* Input Buffer 504 bytes -*  SELECT *  FROM OPENQUERY( lpfife5,  		 'EXEC pubs..repro_remote  			@pro*  vider=''MSIDXS'',  			@linked_server=''lsIndexServer'',  			@catalogue='*  'System'',  			@type=''C'',  			@search_condition=''nokia'',  			@user_i*  d=1,  			@apply_security= ''N''' )  *  ** -------------------------------------------------------------------------------Short Stack Dump0064E187 Module(sqlservr+0024E187) (UpdateString(unsigned short * &,unsigned long &,unsigned short const *,unsigned long)+0000003D)0065336D Module(sqlservr+0025336D) (COledbError::RaiseNameChange(struct COLUMNDATANODE const *,struct tagDBCOLUMNINFO const &,unsigned short const *)+00000079)0064A406 Module(sqlservr+0024A406) (COledbRange::FSingleColumnInfoEqual(struct tagDBCOLUMNINFO *,struct tagDBCOLUMNEXTRAINFO *,struct COLUMNDATANODE const * *,short,int)const +00000161)0064A0D1 Module(sqlservr+0024A0D1) (COledbRange::FColumnInfoEqual(struct COLUMNDATANODE const *,struct IUnknown *,int)const +00000233)0064AEEE Module(sqlservr+0024AEEE) (COledbRangeRowset::FPerformSchemaCheck(class CAutoRowsetSchema *)const +00000179)0064AD76 Module(sqlservr+0024AD76) (COledbRangeRowset::FCheckSchema(class CAutoRowsetSchema *)const +0000001D)00613D9D Module(sqlservr+00213D9D) (CRangeCollection::FSchemaChanged(class CAutoRowsetSchema *,int,class DES * *,unsigned long,int)const +000002E1)00441976 Module(sqlservr+00041976) (CStmtSelect::XretExecute(class CMsqlExecContext *)const +000000BC)004160DB Module(sqlservr+000160DB) (CMsqlExecContext::ExecuteStmts(class ExecutionContext *)+0000027E)00415765 Module(sqlservr+00015765) (CMsqlExecContext::Execute(class CCompPlan *,class CParamExchange *)+000001C7)00415410 Module(sqlservr+00015410) (CSQLSource::Execute(class CParamExchange *)+00000343)00459A54 Module(sqlservr+00059A54) (language_exec(struct srv_proc *)+000003C8)004175D8 Module(sqlservr+000175D8) (process_commands(struct srv_proc *)+000000E0)410735D0 Module(UMS+000035D0) (ProcessWorkRequests(class UmsWorkQueue *)+00000264)4107382C Module(UMS+0000382C) (ThreadStartRoutine(void *)+000000BC)7800BEA1 Module(MSVCRT+0000BEA1) (_beginthread+000000CE)77E837CD Module(KERNEL32+000037CD) (TlsSetValue+000000F0) 				

Microsoft has observed that this scenario works in some instances and fails with an AV in others so the behavior is not consistent. If the stored procedure has an IF...ELSE block and the IF block has an OPENQUERY with a dynamic SQL statement in a variable, we do not know which branch will execute until execution time, so SQL Server cannot possibly return the correct metadata. If the IF and ELSE blocks have different SELECT statements, the following error message may occur:
Server: Msg 7321, Level 16, State 2, Line 1
An Error occurred while preparing a query for execution against <Provider_name>.
[<Provider> returned message: Column <column_name> has not been defined. SQLSTATE=42S22 ]
If you remove the dynamic SQL statement from the ELSE block or you completely remove the ELSE block or if the IF and ELSE block have the same SELECT statement, the SQL statement works correctly. However, because this method is not supported, you should not use this method at all. The following code demonstrates the unsupported scenario mentioned previously. You need to have a computer that is running SQL Server 2000 set up as a linked server. Basically, there is a stored procedure that is created on the local server in the pubs database that runs a query against the authors table on the linked server by using the OPENQUERY command. The stored procedure has an IF..ELSE block and the IF block has the OPENQUERY command and a dynamic SQL statement in a variable, which is executed by using sp_executesql. The stored procedure is called with an OPENQUERY statement, which results in an AV.

NOTE: The query does not AV if you comment out the ELSE code block or if you run the stored procedure by using an EXEC command instead.

Create a stored procedure on the local server by using the following command:
USE pubsGOIF EXISTS (SELECT * FROM sysobjects WHERE name = 'repro' AND type = 'P')	DROP PROCEDURE reproGOCREATE PROCEDURE repro    @provider		varchar(3),    @linked_server      varchar(20),    @lname	        varchar(20)   ASBEGIN    SET NOCOUNT ON    DECLARE @sql    varchar(4000)    DECLARE @comma  varchar(1),            @crlf   varchar(2),            @tab    varchar(1),            @Q      varchar(1)  	SET @comma = ''	SET @crlf  = char(13) + char(10)	SET @tab   = char(9)	SET @Q     = ''''        SET @sql     = ''    IF @provider = 'SQL'BEGIN        SET @sql =        'SELECT * '                  +        'FROM OPENQUERY( ' + @linked_server + ',' + @crlf                 +                        @Q + 'SELECT au_lname, au_fname FROM ' + 'pubs' + '..authors' 		+ ' WHERE au_lname = ' + @Q + @Q + @lname + @Q + @Q		+ @Q 		 + @tab         + ' )'  + @crlf    END    ELSE    BEGIN	SELECT @sql        RETURN    END     DECLARE @nsql nvarchar(4000)    SET @nsql = CAST( @sql AS nvarchar(4000) )    EXEC sp_executesql @nsql    RETURNENDGO				
Run the following query and you will see the query fail with an Access Violation error message:
SELECT *FROM OPENQUERY( <local_Server>,		 'EXEC pubs..repro			@provider=''SQL'',			@linked_server=''<remote_server>'',			@lname=''White''')				
This may appear to work sometimes but results in an AV most of the time. Instead of using the dynamic SQL statement in a variable and using OPENQUERY, if you execute the statement by using the EXEC command as shown in the following code snippet, the preceding query works fine.

/* Replace the IF code block with the statements below and comment out the statements after the ELSE code:*/ BEGINEXEC ('select * from ' + @linked_server + '.pubs.dbo.authors where au_lname = ' + '''' + @lname + '''')RETURNEND				


SQL Server Books Online: topics: "Using sp_executesql"; "EXECUTE"

Article ID: 291376 - Last Review: 10/16/2003 20:39:52 - Revision: 3.2

  • Microsoft SQL Server 2000 Standard Edition
  • kbbug kbfix kbsqlserv2000sp1fix KB291376