September 1, 2008 at 8:07 am
In SQL2000 I am trying to check my connection to a linked server.
Do you know why I cannot connect in the folloging SQL ?:
set nocount on
set nocount on
-- ADD LinkedServer [MYLINKEDSRV]
EXEC master.dbo.sp_addlinkedserver @server = N'MYLINKEDSRV',
@srvproduct=N'MYLINKEDSRV',
@provider=N'SQLOLEDB',
@datasrc=N'T_BL_45',
@catalog=N'MYdb'
-- linked server connection be made using sa security context
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYLINKEDSRV',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='sa'
go
-- just checking my linked server
select count(*) as'test' from MYLINKEDSRV.master.dbo.sysservers -- OK
go
-- trying to test connection to this linked server (in order to monitor linked server)
DECLARE @hr INT
DECLARE @object INT
DECLARE @status INT ;SET @status = 0
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUTPUT; print '1';IF NOT @hr = 0 EXEC sp_OAGetErrorInfo @object
EXEC @hr = sp_OASetProperty @object ,'LoginSecure','True'; print '2';IF NOT @hr = 0 EXEC sp_OAGetErrorInfo @object
EXEC @hr = sp_OASetProperty @object ,'LoginTimeout',60; print '3';IF NOT @hr = 0 EXEC sp_OAGetErrorInfo @object
EXEC @hr = sp_OAMethod @object,'Connect',NULL,N'MYLINKEDSRV'; print '4';IF NOT @hr = 0 EXEC sp_OAGetErrorInfo @object
EXEC @hr = sp_OAGetProperty @object ,'Status',@status OUTPUT; print '5';IF NOT @hr = 0 EXEC sp_OAGetErrorInfo @object
EXEC @hr = sp_OAMethod @object,'DisConnect',null; print '6';IF NOT @hr = 0 EXEC sp_OAGetErrorInfo @object
EXEC @hr = sp_OADestroy @object
EXEC master.dbo.sp_dropserver @server=N'MYLINKEDSRV', @droplogins='droplogins'
===========> RESULT
test
-----------
2
1
2
3
4
Error Source Description HelpFile HelpID
0x80020009 ODSOLE Extended Procedure [Microsoft][ODBC SQL Server Driver][DBNETLIB]Ce serveur SQL n'existe pas ou son accès est refusé.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). NULL 700
5
Error Source Description HelpFile HelpID
0x800406BA Microsoft SQL-DMO [SQL-DMO]Service Control Error: Le serveur RPC n'est pas disponible.
SQLDMO80.hlp 640
6
September 1, 2008 at 8:41 am
Can you translate the error?
Does the Add linked server section work? Does this appear in SQL Server?
September 1, 2008 at 8:52 am
The add linke server is working because I can run the following with success
select count(*) as'test' from MYLINKEDSRV.master.dbo.sysservers
0x80020009 ODSOLE Extended Procedure [Microsoft][ODBC SQL Server Driver][DBNETLIB] SQL Server does not exist or refuse to visit
0x800406BA Microsoft SQL-DMO [SQL-DMO]Service Control Error: RPC server is not available.
I run all the SQL command in Query Analyzer on SQL server 2000
September 1, 2008 at 10:05 am
egpotus (9/1/2008)
EXEC @hr = sp_OAMethod @object,'Connect',NULL,N'MYLINKEDSRV'; print '4';IF NOT @hr = 0 EXEC sp_OAGetErrorInfo @object
if not using windows authentication then you would need to specify the following
EXEC @hr = sp_OAMethod @object,'Connect',NULL,N'MYLINKEDSRV', username, password;
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 2, 2008 at 1:18 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply