September 9, 2004 at 11:26 am
Use linked server, I can access a remote server in T-SQL grogram. However, if the server is down, the program command return message :
"SQL Server does not exist or access denied."
and the program terminates. Is there any way I can let the program continue, say by first test the server's availability. If not, don't access the server but process further.
The other thought is that there are some errors in SQL Server which do not terminate the program, like when drop a non existing table. Is it possible to configure the server so that let a particular executing error being ignored?
September 9, 2004 at 1:18 pm
You could use a nested batch (dynamic SQL or a Stored Procedure) within your T-SQL batch to test the server. Assess the @@ERROR_CODE and return its value within an Output parameter or return value (you can use sp_executesql for Dynamic SQL with Output). Then branch based on that Output parameter. If a nested branch errors that error isn't acknoledged within the parent batch unless you explicitely code for it.
September 9, 2004 at 1:26 pm
and if you're worried about any raiserrors going to the client you can use the old fashioned ugly DOS means:
declare @err int
exec @err = master..xp_cmdshell 'osql -Smyserver -Usa -Pgod -Q"set nocount on"'
select @err
@err will be 0 on success and 1 on fail: a fail to connect will set the DOS error to 1
(This is in the major quick and dirty/kluge category of solutions)
September 10, 2004 at 4:10 am
mmm putting passwords in stored procedures is such a bad idea.
What's wrong with doing a little error handling?
IF EXISTS(SELECT top 1 * FROM [mylinkedserver].[master].[dbo].[sysdatabases])
BEGIN
-- mylinkedserver is up and can be connected to
END
-- maybe there was an error?
DECLARE @MyError INT
SET @MyError = @@ERROR
IF @MyError <> 0
BEGIN
-- ERROR Handling Routines
END
Julian Kuiters
juliankuiters.id.au
September 13, 2004 at 6:06 pm
Thank you for your responses. I tried all the 3 solutions, and like to share with you the results
1) using sp_executesql, I did the following
execute sp_executesql
N'SELECT top 1 * FROM [goodserver].[master].[dbo].[sysdatabases]'
select @@error
execute sp_executesql
N'SELECT top 1 * FROM [badserver].[master].[dbo].[sysdatabases]'
select @@error
SELECT top 1 * FROM [goodserver].[master].[dbo].[sysdatabases] '
The script stop at the 2nd sp_executesql, the last select statement is not executed. Similar to the scenario I see in my code.
2) using oslq, I did
declare @err int
exec @err = master..xp_cmdshell 'osql -S[bad_server] -Uglu -Panna -Q"set nocount on"'
select @err
SELECT top 1 * FROM [stage].[master].[dbo].[sysdatabases]
The command return 1 and the last statement executed. this seems solve my problem.
3) error handling, I did
IF EXISTS(SELECT top 1 * FROM [bad_server].[master].[dbo].[sysdatabases])
BEGIN
print 'server exist'
END
-- maybe there was an error?
DECLARE @MyError INT
SET @MyError = @@ERROR
IF @MyError <> 0
BEGIN
print 'server not exist'
END
When bad_server not exists, the first statement failed and stop execution. Thus 2nd half of code is never executed.
Any more comments?
September 14, 2004 at 5:47 am
The following is what I meant. As John pointed out, the error message is printed to the client, but odds are your client doesn't care and will not acknoledge that they happened.
declare @strParams nvarchar(1024)
declare @strQuery nvarchar(1024)
declare @errorcode int
set @strParams = N'@ErrorCode int OUTPUT'
set @strQuery = N'SELECT top 1 * FROM [badserver].[master].[dbo].[sysdatabases]
SELECT @errorcode = @@ERROR'
EXEC sp_executesql @strQuery, @strParams, @errorcode = @errorcode OUTPUT
if @errorcode = 0
begin
print 'Good'
end
else
begin
print 'Bad'
end
The above results in the following when I run it:
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'badserver' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
Bad
September 14, 2004 at 5:32 pm
This works perfectly. Thanks
October 14, 2004 at 11:19 am
My apologies for resurrecting this thread, but it pertains to a question that I have in the same vein. This post was very helpful but when I tried to use it for my purposes, it still errored out with the same error that the original poster.
I tried this:
declare @strParams nvarchar(1024)
declare @strQuery nvarchar(1024)
declare @errorcode int
set @strParams = N'@ErrorCode int OUTPUT'
set @strQuery = N'SELECT top 1 * FROM [badserver].[master].[dbo].[sysdatabases]
SELECT @errorcode = @@ERROR'
EXEC sp_executesql @strQuery, @strParams, @errorcode = @errorcode OUTPUT
if @errorcode = 0
begin
print 'Good'
end
else
begin
print 'Bad'
end
The above results in the following when I run it:
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'badserver' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
Bad
And indeed, it does report back that error message if there is no linked server named badserver in sysservers.
BUT if there IS a server named badserver (but is currently down), it would fail with the "SQL Server does not exist or access is denied." message.
My goal is: I am trying to do error handling to continue processing the list of linked servers in sysservers even if some particular ones are down but this error always stops me in my tracks. That particular error message is not recorded in sysmessages so there is no @@ERROR recorded.
Any ideas?
Thanks!
Dan
October 14, 2004 at 11:26 am
I see I'm not the only one that has tried to do this... Answered my own question with more searching: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=109509
That's a bummer. If anyone can think of a work-around, that'd still be much appreciated! Thanks.
October 14, 2004 at 11:46 am
I'm going to guess that the batch stops because the prepare process crashed due to the remote server being responsible for helping to prepare the SQL. But the reason ultimately doesn't matter.
A workaround would be to use sp_cmdShell to run osql to obtain the knowledge. Since this would establish a seperate connection the T-SQL batch will be completely isolated and protected. I don't know if sp_cmdShell runs synchronously or not and so capturing the result might become a little tricky but it ultimately could be done. Granted it's ugly, but an option is better than no options. As an example, you could actually run a DOS command script that runs osql twice. The first would attempt the connection and update a table reporting success if successful and the second would update the table saying the process finished. The core SP would then loop with a WAITFOR DELAY until the table reports the check has finished. If this is done thousands of times a day it might be a problem, but if done rarely might be an acceptable resolution. Good luck. Please let me know if you actually prove that this approach works. I'd like to know for future reference.
October 14, 2004 at 11:51 am
Yes I also thought about trying it through osql as was mentioned earlier. It doesn't look like fun but it's worth a shot. I'll let you know if it works.
Thanks for the reply.
Dan
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply