Select the product you need help with

FIX: Cannot Use Dynamic SQL Statements Within OPENQUERY

Article ID: 291376 - View products that this article applies to.

This article was previously published under Q291376

BUG #: 351729 (SHILOH_BUGS)

SYMPTOMS

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.

Back to the top | Give Feedback

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.

Back to the top | Give Feedback

RESOLUTION

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 the Microsoft Knowledge Base:

290211

(http://support.microsoft.com/kb/290211/EN-US/ )

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.

Back to the top | Give Feedback

WORKAROUND

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.

Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.

Back to the top | Give Feedback

MORE INFORMATION

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: 0
2001-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 Dump
0064E187 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 pubs
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'repro' AND type = 'P')
	DROP PROCEDURE repro
GO
CREATE PROCEDURE repro
    @provider		varchar(3),
    @linked_server      varchar(20),
    @lname	        varchar(20)   
AS
BEGIN
    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
    RETURN
END
GO

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:*/ 

BEGIN
EXEC ('select * from ' + @linked_server + '.pubs.dbo.authors where au_lname = ' + '''' + @lname + '''')
RETURN
END