Article ID: 291376
Article Last Modified on 10/16/2003
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
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.
CAUSE
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.
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 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:
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.
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.
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:
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
REFERENCES
SQL Server Books Online: topics: "Using sp_executesql"; "EXECUTE"
Keywords: kbbug kbfix kbsqlserv2000sp1fix KB291376
