July 2, 2004 at 1:12 pm
I've been building a series of stored procedures that automatically create a number of linked servers, run some queries against them and then drop the linked servers.
The problem is after running the proc to create the linked servers and add logins to them, some of them don't work. This is mainly due to a couple of servers being Windows only security (My proc creates the link with SQL Server Logins.) I don't find out that they don't work until another proc tries to run a query against them.
Unfortunately when this happens it bombs the stored procedure with a login failed error. Actually the whole process fails whether a stored procedure is nested within another or not. (Forgive my terminology, I know not what to call this type of error.) As you would expect this makes it difficult to test whether the linked server is working.
What I came up with to test this was to create a UDF that uses xp_cmdshell to make the test and it returns a 0 or a 1 to indicate whether the linked server is working.
Any ideas on a better way to perform this test?
CREATE FUNCTION udf_testlinkedserver
(@ServerID int)
RETURNS int
AS
begin
declare @rc int, @ServerName varchar(128), @cmd varchar(256)
select
@ServerName = ServerName
from
dba.dbo.Servers
where
ServerID = @ServerID
set @cmd =
'isql.exe -S '+ @ServerName +' -U <UID> -P <PWD> -Q "quit"'
exec @rc = master.dbo.xp_cmdshell @cmd, NO_OUTPUT
If @rc = 0
set @rc = 1
Else
set @rc = 0
RETURN @rc
end
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
July 5, 2004 at 8:00 am
This was removed by the editor as SPAM
July 5, 2004 at 9:27 pm
You may find sp_linkedservers (list defined linked servers) and sp_tables_ex which gives you info about tables in a linked server.. Don't wheter they'll be much help in your situation though...
Anyone else ?
DB
The systems fine with no users loggged in. Can we keep it that way ?br>
July 6, 2004 at 10:03 am
Not really because you can define a linked server and a linked server login but that's no guarantee that the login works on the server you are linking to.
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
July 6, 2004 at 3:06 pm
But testing for a result by interrogating table information might help yes ? What I'm thinking here is that you check the linked server has been defined, then try to extract table information....if you get back null or an error on a table you know exists then you could assume the login has failed. The error number may provide a clue.
What you do from there I don't know....
Best of Luck,
DB
The systems fine with no users loggged in. Can we keep it that way ?br>
July 6, 2004 at 3:21 pm
"....if you get back null or an error on a table you know exists then you could assume the login has failed. "
Sadly, no. If you query it and it fails due to a login failure it bombs the whole stored procedure or series of nested stored procedures. I can't figure out a way to capture the error other than what I tried above.
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
July 6, 2004 at 3:30 pm
That's a bugger. It's likely I will want to do the same someday soon - I'll have a go at it and get back to you.
DB
The systems fine with no users loggged in. Can we keep it that way ?br>
July 8, 2004 at 3:51 pm
No solution but very interesting reading on: http://www.sommarskog.se/error-handling-II.html. The Background article is very good too. Hope the site works for you as I just got a page not found trying to open it.
DB
The systems fine with no users loggged in. Can we keep it that way ?br>
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply