Problem With SP_Addlinkedserver When Used In Stored Procedure

I wrote the following stored procedure. Note that SERVER_BK is an ODBC connection on the server that links to the remote server database.

--Creates the stored procedure that will be called in the Tablecounts script to send the comparison out to admins

if exists (select * from dbo.sysobjects where id = object_id(N'sp_compare_table_record_counts') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure sp_compare_table_record_counts

go

create proc sp_compare_table_record_counts as

----Creates the connection to the Remote Database. We need to make sure that the

----ODBC connections on each of the Production and Development Servers

----are named the same, so that we can use the same script and it will refer to the correct server

EXEC sp_addlinkedserver

@server = 'SERVER_BK',

@srvproduct = '',

@provider = 'MSDASQL',

@datasrc = 'SERVER_BK'

EXEC sp_addlinkedsrvlogin 'SERVER_BK', 'true'

set quoted_identifier off

select substring(remote_table.tablename,1,40) as Tablename, local_table.tablerowcount as L_SERVER,

remote_table.tablerowcount as R_SERVER,

case when remote_table.tablerowcount = local_table.tablerowcount then 'Counts Match'

else 'Counts Do Not Match'

end as Result_of_Compare

from OpenQuery([SERVER_BK], 'select * from tablecounts') remote_table, tablecounts local_table

where remote_table.tablename = local_table.tablename

order by Result_of_Compare, Tablename

-- table name on R_SERVER but not on L_SERVER

select substring(local_table.tablename,1,40) as 'Tables not on R_SERVER'

from tablecounts local_table where local_table.tablename not in (select * from OpenQuery([SERVER_BK], 'select tablename from tablecounts'))

-- table name on L_SERVER but not on R_SERVER

select substring(remote_table.tablename,1,40) as 'Tables not on L_SERVER'

from OpenQuery([SERVER_BK], 'select * from tablecounts') remote_table where remote_table.tablename not in (select tablename from tablecounts)

----Deletes the connection to the Remote Database to free up resources. This is just cleanup.

EXEC sp_dropserver 'SERVER_BK', 'droplogins'



I tested, and the stored procedure works OK when it is run as a New Query (i.e. I copy the guts and run it). When I execute the following, it fails:

exec msdb..sp_send_dbmail

@profile_name = 'DB Mail Profile',

@recipients = 'Recipient List',

@query_no_truncate = 25,

@query_result_separator = ' ',

@subject = 'Record Counts',

@query = 'exec db..sp_compare_table_record_counts', <--db is the appropriate db where the sp is

@body_format = 'text'



Here is the error I get...any ideas would be greatly appreciated. I'm kind of new at this:

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476

Query execution failed: Msg 7399, Level 16, State 1, Server L_SERVER, Procedure sp_compare_table_record_counts, Line 28

The OLE DB provider "MSDASQL" for linked server "SERVER_BK" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Server L_SERVER, Procedure sp_compare_table_record_counts, Line 28

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SERVER_BK".

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.