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.
hope this link may help u :http://sqlserver2000.databases.aspfaq.com/how-do-i-prevent-linked-server-errors.html|||
Whoops, I forgot to mention that this is SQL 2005, SP1. I will check out the link though.
Any other ideas?
The other thing is that this only stops working when I turn this into a SP, when I run the first part (between but not including the create proc...as and the sp_send_dbmail) it seems to work fine.
I just can't turn this into a SP so that I can have DBMAIL execute it and mail the output.
|||OK, I am going to change the problem a little bit, as I have made some progress. I decided to NOT make this a stored procedure, instead it is just one script now that does all of the work.
However, I still use this part of the code :
EXEC sp_addlinkedserver
@.server = 'SERVER_BK',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'SERVER_BK'
EXEC sp_addlinkedsrvlogin 'SERVER_BK', 'true'
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'))
This works when I run it as myself, who happens to be an administrator in both locations, and the ODBC connection exists. The problem is when I schedule this as a job. I have a job that runs a tsql command, the job succeeds, but I get this in the log:
Line 59 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "IFS_BK". OLE DB provider "MSDASQL" for linked server "IFS_BK" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).". OLE DB provider "MSDASQL" for linked server "IFS_BK" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.".
I know that this runs as the SQL Agent account, but I set the account as a db_datareader for that particular database on the other server. The SQL Agent account also is in the local administrators group of the OS on the remote system. Any ideas why this is failing?
|||EXEC sp_addlinkedsrvlogin 'SERVER_BK', 'true'
when u give this statement the current users credentials are used to connect to the remote server also... check remote server have this user and same password
FROM BOL :
Determines the name of the login used to connect to the remote server. useself is varchar(8), with a default of TRUE.
A value of true specifies that logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. false specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server
If remote server is having different user and password then u should use the below statement
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'Server2',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'SomeLoginInServer2',@.rmtpassword='Password'
Madhu
|||Madhu,
Thank you, I think i'm on the right track now. I think I may have confused myself a bit though. The first lines of my script is this:
EXEC master.dbo.sp_addlinkedserver
@.server = 'SERVER2',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'SERVER2' --This is an ODBC connection that is setup in the system named SERVER. I was trying to do this to avoid having a username and password in a script.
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'SERVER2',
@.useself=N'False',
@.locallogin=NULL,
@.rmtuser=N'USERNAME',
@.rmtpassword='PASSWORD'
My expectation is that I can now use a command like this further down in the same script:
OpenQuery([SERVER2], 'select * from TABLENAME')
and it would return everything in the TABLENAME table on the Other Server (server2). When I run the script, instead I get an error:
Msg 7202, Level 11, State 2, Line 67
Could not find server 'SERVER2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
But it's not listing any errors creating the connection. What's the key i'm missing?
|||Madhu,
Thank you, I think i'm on the right track now. I think I may have confused myself a bit though. The first lines of my script is this:
EXEC master.dbo.sp_addlinkedserver
@.server = 'SERVER2',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'SERVER2' --This is an ODBC connection that is setup in the system named SERVER. I was trying to do this to avoid having a username and password in a script.
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'SERVER2',
@.useself=N'False',
@.locallogin=NULL,
@.rmtuser=N'USERNAME',
@.rmtpassword='PASSWORD'
My expectation is that I can now use a command like this further down in the same script:
OpenQuery([SERVER2], 'select * from TABLENAME')
and it would return everything in the TABLENAME table on the Other Server (server2). When I run the script, instead I get an error:
Msg 7202, Level 11, State 2, Line 67
Could not find server 'SERVER2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
But it's not listing any errors creating the connection. What's the key i'm missing?
|||Madhu,
Thank you, I think i'm on the right track now. I think I may have confused myself a bit though. The first lines of my script is this:
EXEC master.dbo.sp_addlinkedserver
@.server = 'SERVER2',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'SERVER2' --This is an ODBC connection that is setup in the system named SERVER. I was trying to do this to avoid having a username and password in a script.
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'SERVER2',
@.useself=N'False',
@.locallogin=NULL,
@.rmtuser=N'USERNAME',
@.rmtpassword='PASSWORD'
My expectation is that I can now use a command like this further down in the same script:
OpenQuery([SERVER2], 'select * from TABLENAME')
and it would return everything in the TABLENAME table on the Other Server (server2). When I run the script, instead I get an error:
Msg 7202, Level 11, State 2, Line 67
Could not find server 'SERVER2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
But it's not listing any errors creating the connection. What's the key i'm missing?
|||I did as you suggested, so this makes up the beginning of my script:EXEC master.dbo.sp_addlinkedserver
@.server = 'SERVER2', --This is an ODBC connection that is setup on the machine.
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'SERVER2'
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'SERVER2',
@.useself=N'False',
@.locallogin=NULL,
@.rmtuser=N'USERNAME',
@.rmtpassword='PASSWORD'
Later in the script, I run this:
OpenQuery([SERVER2], 'select * from TABLENAME')
My expectation is that I would get all of the information from the TABLENAME table on SERVER2. Instead, I get this error message when the script runs:
Msg 7202, Level 11, State 2, Line 67
Could not find server 'SERVER2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
I think i'm missing one important thing, but I can't figure out what it is.
My thought process is to have 1 script that:
1. Creates the connection to the other server, preferable through the System DSN so that I don't have to store a username/password in the code.
2. Access a table in the remote server that contains a bunch of rowcounts for that server
3. Compare the table from the remote rowcounts to the local rowcounts to make sure they match.
Maybe i'm just doing it the wrong way.
|||
At least you got me thinking... 
I found the problem. I created the connection, but forgot to put "GO" to create the connection before I tried using it.
Now, it's working.
Thank you for your help.
No comments:
Post a Comment