Microsoft KB Archive/823429

= FIX: You receive error message 7410 when you use a distributed query as a query parameter for the xp_sendmail stored procedure or the sp_makewebtask stored procedure in SQL Server 2000 =

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:

Server: Msg 7410, Level 16, State 1, Line 1

Remote access not allowed for Windows NT user activated by SETUSER.

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:

Executed as user: Job Owner. SQL Web Assistant: Could not execute the SQL

statement. SQLSTATE 42000 (Error 16805) Associated statement is not prepared SQLSTATE

HY007 (Error 0). The step failed.



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 [].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  </li> 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&quot;<Linked Server Query>&quot; -oc:\testmail.txt' </li> 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</li>  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. </li> Drop the SQL Server table that was created in step 2.</li></ol> </li></ul>

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.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.

<div class="moreinformation_section">

Steps to reproduce the behavior
 Connect to an instance of SQL Server 2000 SP3 as a member of the sysadmin fixed server role.</li>  Create a linked server in the current instance of SQL Server: USE master GO EXEC sp_addlinkedserver '<Linked Server Name>' </li>  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 </li></ol>

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 &quot;Symptoms&quot; section. You also receive a second error message to notify you about the consequent failure of the sp_makewebtask stored procedure.

<div class="moreinformation_section">

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

<div class="references_section">