Article ID: 823429
Article Last Modified on 2/21/2007
APPLIES TO
- Microsoft SQL Server 2000 Service Pack 3
BUG #: 363990 (SQL Server 8.0)
SYMPTOMS
In Microsoft SQL Server 2000 Service Pack 3 (SP3), when you pass a distributed query to the @query parameter of the xp_sendmail stored procedure or the sp_makewebtask stored procedure, the procedure may fail and you may receive the following error message:
This problem only occurs when a SQL Server 2000 SP3 instance runs the stored procedures with a distributed query. The version of the linked server is not important.
Note The same distributed queries ran successfully in earlier versions of Microsoft SQL Server 2000.
There is more than one symptom for this problem. In the following case, the sp_makewebtask stored procedure may or may not use a distributed query. When you run a stored procedure that calls the sp_makewebtask stored procedure as a SQL Server Agent job and the SQL Server Agent is configured to run under the Local System account, the job may fail. You may also receive the following error message:
RESOLUTION
Service pack information
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 How to obtain the latest SQL Server 2000 service pack
Hotfix information
The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.
Date Time Version Size File name
---------------------------------------------------------------------
31-May-2003 18:45 2000.80.818.0 78,400 Console.exe
25-Jun-2003 01:01 2000.80.818.0 33,340 Dbmslpcn.dll
25-Apr-2003 02:12 786,432 Distmdl.ldf
25-Apr-2003 02:12 2,359,296 Distmdl.mdf
30-Jan-2003 01:55 180 Drop_repl_hotfix.sql
23-Jun-2003 22:40 2000.80.837.0 1,557,052 Dtsui.dll
23-Jun-2003 22:40 2000.80.837.0 639,552 Dtswiz.dll
24-Apr-2003 02:51 747,927 Instdist.sql
03-May-2003 01:56 1,581 Inst_repl_hotfix.sql
08-Feb-2003 06:40 2000.80.765.0 90,692 Msgprox.dll
01-Apr-2003 02:07 1,873 Odsole.sql
05-Apr-2003 01:46 2000.80.800.0 62,024 Odsole70.dll
07-May-2003 20:41 2000.80.819.0 25,144 Opends60.dll
07-May-2003 18:47 132,096 Opends60.pdb
02-Apr-2003 21:48 2000.80.796.0 57,904 Osql.exe
02-Apr-2003 23:15 2000.80.797.0 279,104 Pfutil80.dll
22-May-2003 22:57 19,195 Qfe469571.sql
12-Jun-2003 16:37 1,083,989 Replmerg.sql
04-Apr-2003 21:53 2000.80.798.0 221,768 Replprov.dll
08-Feb-2003 06:40 2000.80.765.0 307,784 Replrec.dll
05-May-2003 00:05 1,085,874 Replsys.sql
01-Jun-2003 01:01 2000.80.818.0 492,096 Semobj.dll
31-May-2003 18:27 2000.80.818.0 172,032 Semobj.rll
29-May-2003 00:29 115,944 Sp3_serv_uni.sql
01-Jun-2003 01:01 2000.80.818.0 4,215,360 Sqldmo.dll
07-Apr-2003 17:44 25,172 Sqldumper.exe
19-Mar-2003 18:20 2000.80.789.0 28,672 Sqlevn70.rll
02-Jul-2003 00:18 2000.80.834.0 180,736 Sqlmap70.dll
08-Feb-2003 06:40 2000.80.765.0 57,920 Sqlrepss.dll
23-Jun-2003 22:40 2000.80.837.0 7,553,105 Sqlservr.exe
23-Jun-2003 22:40 12,747,776 Sqlservr.pdb
08-Feb-2003 06:40 2000.80.765.0 45,644 Sqlvdi.dll
25-Jun-2003 01:01 2000.80.818.0 33,340 Ssmslpcn.dll
01-Jun-2003 01:01 2000.80.818.0 82,492 Ssnetlib.dll
01-Jun-2003 01:01 2000.80.818.0 25,148 Ssnmpn70.dll
01-Jun-2003 01:01 2000.80.818.0 158,240 Svrnetcn.dll
31-May-2003 18:59 2000.80.818.0 76,416 Svrnetcn.exe
30-Apr-2003 23:52 2000.80.816.0 45,132 Ums.dll
30-Apr-2003 23:52 132,096 Ums.pdb
02-Jul-2003 00:19 2000.80.834.0 98,816 Xpweb70.dll
Note Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.
WORKAROUND
To work around this problem, use one of the following methods.
- Use a global temporary table.
Use a global temporary table for intermediate storage, and then use the global temporary table in the sp_makewebtask stored procedure or in the xp_sendmail stored procedure instead of the linked server. To do this, follow these steps:Store the linked server query output that you want in a global temporary table:
select * into ##tmpTable from [<Linked Server Name>].pubs.dbo.authors
Use the global temporary table instead of the linked server query in the @query parameter of the sp_makewebtask stored procedure or the xp_sendmail stored procedure:
EXECUTE sp_makewebtask 'c:\test.htm', @query = 'select * from ##tmpTable', @resultstitle ='report title', @HTMLHeader=3
Drop the global temporary table:
DROP TABLE ##tmpTable
- Use a flat text file.
Use a flat text file for intermediate storage of the linked server query output. To do this, follow these steps:Store the linked server query output that you want in a flat text file:
EXEC xp_cmdshell 'osql.exe -E -Q"<Linked Server Query>" -oc:\testmail.txt'
- Use the BCP utility to copy the data from the specified flat text file to a SQL Server table.
For more information about how to copy a data file to SQL Server, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/aa173839(SQL.80).aspx Use the SQL Server table instead of the linked server in the @query parameter of the sp_makewebtask stored procedure or the xp_sendmail stored procedure:
EXECUTE sp_makewebtask 'c:\test.htm', @query = 'select * from <SQL Server Table>', @resultstitle ='report title', @HTMLHeader=3
Note You may also specify multiple SELECT statements in the @query parameter if multiple SQL Server tables are created in step 2.
- Drop the SQL Server table that was created in step 2.
Note For the xp_sendmail stored procedure, you can also work around the problem by storing the output of the linked server data in a text file, and then send the output file as an attachment by using the @attachment parameter of the xp_sendmail stored procedure.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.
MORE INFORMATION
Steps to reproduce the behavior
- Connect to an instance of SQL Server 2000 SP3 as a member of the sysadmin fixed server role.
Create a linked server in the current instance of SQL Server:
USE master GO EXEC sp_addlinkedserver '<Linked Server Name>'
Run the sp_makewebtask stored procedure, and use a distributed query for the parameter that accesses the data from the linked server:
EXECUTE sp_makewebtask 'c:\test.htm', @query = 'select * from [<Linked Server Name>].pubs.dbo.authors', @resultstitle ='report title', @HTMLHeader=3
Note The first error message that you receive after you run the code in step 3 is the error message that is shown in the "Symptoms" section. You also receive a second error message to notify you about the consequent failure of the sp_makewebtask stored procedure.
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
824684 Description of the standard terminology that is used to describe Microsoft software updates
REFERENCES
For additional information about a SQL Server Agent job that uses a linked server, click the following article number to view the article in the Microsoft Knowledge Base:
811031 SQL Server Agent job fails when the job uses a linked server and the job owner is not a system administrator
For more information about the sp_makewebtask stored procedure or the xp_sendmail stored procedure, see the following topics in SQL Server 2000 Books Online:
Keywords: kbqfe kbsqlservmail kbstoredproc kbtsql kbweb kberrmsg kbsqlserv2000presp4fix kbfix kbbug KB823429
