ODBC ERROR 7410

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

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. 

To resolve this problem:
1.obtain the latest service pack for Microsoft SQL Server 2000.

2.Use a global temporary table.
1.>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:
select * into ##tmpTable from [<Linked Server Name>].pubs.dbo.authors

2.>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

3.>Drop the global temporary table:
DROP TABLE ##tmpTable

3.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:
1>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'
2>Use the BCP utility to copy the data from the specified flat text file to a SQL Server table.
3>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.
4>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.