April 19, 2011 at 11:31 pm
I'm using the following code to establish a linked list to a server:
sp_addlinkedserver 'IP_Address'
Go
sp_addlinkedsrvlogin'IP_Address',
'False',
Null,
'login',
'password'
I'm working out a reliable way to verify the connection to the server is ok and log if there are any problem.
I would very thankful if anyone has any idea.
Thank you
April 20, 2011 at 12:35 am
Hi
I think you want to confirm your linked server connected or not?
If its, just run query against a ServerB.database.table from ServerA.
If it return with success its ok other wise,you are not connected to ServerB.
Ali
MCTS SQL Server2k8
April 20, 2011 at 12:55 am
Dear Ali
thanks for participating to this discussion but the challenge is about to first identify the troubles that may arise and second to log any bad things happens to it. how can I say my server has problemA in a very fancy way??
April 20, 2011 at 8:24 am
You can use sp_testlinkedserver to test connectivity for a linked server. Something like the following would allow you to present an error message:
BEGIN TRY
EXEC master.sys.sp_testlinkedserver @servername = N'test';
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE(), ERROR_NUMBER();
END CATCH
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 20, 2011 at 9:06 am
seems like to a be a practiced way.
Thank you very much 🙂
April 20, 2011 at 9:09 am
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'TEST', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'use remote collation', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TEST', @locallogin = NULL , @useself = N'True'
GO
RPC and RPC out should be enable,that is a basic script to create a linked Server,Linked Server is also on SQL Server ? what is the version of SQL Server of both server?
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply