September 18, 2008 at 7:03 am
hey People,
So my issue is that im doing one script wich has 40 linked server in one temporary table, so i execute one loop just changing the linked servers and executing one SQl Query.
But when one of this linked server fails my entire script stop as well.
I want to treat this error inserting it in a log file and keep executing the loop into the linked servers.
ps. SQL SERVER 2000
Does anybody know something about it?
Regards
Rhuan Esteves
September 18, 2008 at 8:47 am
You already mention that you are looping through a temp table with the linked servers so I won't worry about that.
See if this code will get you started:
SET NOCOUNT ON;
CREATE TABLE #foo
(
dbname SYSNAME NULL
);
INSERT #foo
EXEC master..xp_cmdshell
'osql
-S
-dMaster
-U
-P
-Q"SELECT Name FROM sysdatabases"';
IF EXISTS
(
SELECT 1
FROM #foo
WHERE LTRIM(RTRIM(dbname)) = N'master'
)
BEGIN
PRINT 'Feel free to use linked server.';
END
ELSE
BEGIN
PRINT 'Linked server not available.';
END
DROP TABLE #foo;
more info here : http://sqlserver2000.databases.aspfaq.com/how-do-i-prevent-linked-server-errors.html
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
September 18, 2008 at 12:05 pm
Thanks Body,
The URL that you indicated me had this code wich helped me a lot.
The another code one that you posted works only with SQL SERVER 2005.
But anyway thanks
The code that I'm using:
CREATE TABLE #foo
(
pingResult SYSNAME NULL
);
INSERT #foo
EXEC master..xp_cmdshell
'ping linked_server_name';
IF EXISTS
(
SELECT 1
FROM #foo
WHERE pingResult LIKE '%TTL%'
)
BEGIN
PRINT 'Feel free to use linked server.';
END
ELSE
BEGIN
PRINT 'Linked server not available.';
END
DROP TABLE #foo;
September 18, 2008 at 12:07 pm
Good to know that you got it working.
Also, anytime you see 2005 code there's a possibility you could adapt it to work with 2000.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply