Trapping Connection Errors

Question

Trapping Connection Errors

Greg Larsen

SSC-Insane

Points: 20961
More actions
December 28, 2004 at 10:16 am

#60282

I'm trying to determine how to trap connection errors in T-SQL code. If I run the following code and for some reason I can't connect to the linkedserver the print statement is not executed, for that matter any code following the select statement is not executed. Basically the batch terminates.
select count(*) from linkedserver.master.dbo.sysobjects
if @@error <> 0 print 'connection error'
How might a trap the "SQL Server does not exist or access denied." error, and print the "connection error" message, and have the batch continue processing?

Gregory A. Larsen, MVP

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

I know one way to trap the error, but you are not going to like it. If you put your test in a dynamic SQL statement, then you can retrieve the error and your main script can continue running.

declare @result int
exec @result = sp_executesql N'Select count(*) from linkedsever.master.dbo.sysobjects'
if @result <> 0
begin
Print 'an error occured'
end
-- your script continues

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 2

Kathi - For some reason this method does not work in my environement. When I run your code I get the following error message and the print statement "an error occurred" never gets executed.

Server: Msg 17, Level 16, State 1, Line 1

SQL Server does not exist or access denied.

I'm wondering if there is something in your environement set up that allows the follow on code to be executed. Clearly in my environment the batch is terminated as soon as I get the above message.

Gregory A. Larsen, MVP

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 3

This is the result I get when I run the exact code. I didn't really try the code before posting the answer, but I had used the tequique before.

Server: Msg 7202, Level 11, State 2, Line 1

Could not find server 'linkedsever' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

an error occured

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

noeld SSC Guru Points: 96590 More actions · Answer 4

Greg,

AFAIR I was in a similar situation and I ended doing it on a job so that is asychronous and independent of your code and you could check for the Success or failure afterwards.

Some people suggested the use of DMO to be a cleaner way but that is up to you

HTH

* Noel

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 5

Ok, I have come up with at T-SQL DMO solution. There must be an easier why. Here is my solution. If this code can successfully connect to a linked server it prints a message indicating successful connection, otherwise the connection error is printed, and the code continues.

DECLARE @object int

DECLARE @hr int

DECLARE @src varchar(255), @desc varchar(255)

DECLARE @location varchar(100)

DECLARE @CMD nvarchar(1000)

DECLARE @objresults int

set @location = 'Create SQLServer Object'

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT

IF @hr <> 0 goto ERROR

-- use Windows Authentication

EXEC @hr = sp_OASetProperty @object, 'LoginSecure', 'True'

set @location = 'Connect to Server'

EXEC @hr = sp_OAMethod @object, 'Connect',Null,'serverthatcontainslinkedserverdefinitions'

IF @hr <> 0 goto ERROR

set @location = 'Execute TSQL to Linked Server'

set @cmd = 'ExecuteWithResults("select count(*) From linkedserver.master.dbo.sysobjects")'

EXEC @hr = sp_OAMethod @object,@cmd, @objResults OUT

IF @hr <> 0 goto ERROR

Print 'Successfully Connected to Linked Server'

RETURN

Error:

Print @location

EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailtoescription=@desc">Description=@desc

Print 'Connection Error'

-- place follow on code here.

RETURN

Gregory A. Larsen, MVP

Urszula Dron SSC-Addicted Points: 411 More actions · Answer 6

I tried to use your script but I have some security issues (I think) with connectivity to linked servers. All my linked server have the same remote logins:

local sa remote sa

domain\sqlserviceaccount remote sa

I run it from QA on the server all linked servers are set and I get connection error for all of them except local server. When I run the script without dmo part, it gets terminated becouse of connection error as in your original post.

I've tried to add different combination in security of linked servers and was not able to find right one. What I'm doing wrong?

How I could change the script to use sql login? don't have any programing experience and I will appreciate any help.

Here is the script I run:

DECLARE @srvname sysname

DECLARE @ExecStr varchar(300)

SELECT @srvname = ' '

WHILE @srvname IS NOT NULL

BEGIN

SELECT @srvname = MIN(srvname) FROM master.dbo.sysservers

WHERE srvname > @srvname

AND providername ='sqloledb'

DECLARE @object int

DECLARE @hr int

DECLARE @src varchar(255), @desc varchar(255)

DECLARE @location varchar(100)

DECLARE @CMD nvarchar(1000)

DECLARE @objresults int

set @location = 'Create SQLServer Object'

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT

IF @hr <> 0 goto ERROR

-- use Windows Authentication

EXEC @hr = sp_OASetProperty @object, 'LoginSecure', 'True'

set @location = 'Connect to Server'

EXEC @hr = sp_OAMethod @object, 'Connect',Null, @srvname

IF @hr <> 0 goto ERROR

set @location = 'Execute TSQL to Linked Server'

select @cmd = 'ExecuteWithResults(' + '"' + 'insert admin.dbo.tblsrv_role_members select ' + '''' + @srvname +'''' + ','

select @cmd = @cmd + 'spv.name' + ',' + 'lgn.name from ' + '[' + @srvname +']' + '.master.dbo.spt_values spv '

select @cmd = @cmd + ',' + '[' + @srvname +']' + '.master.dbo.sysxlogins lgn where spv.low = 0 and spv.type = '

select @cmd = @cmd + '''' + 'srv' + '''' + 'and lgn.srvid IS NULL and spv.number & lgn.xstatus = spv.number' + '"' + ')'

print @cmd

EXEC @hr = sp_OAMethod @object,@cmd, @objResults OUT

Error:

Print @location

EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailtoescription=@desc">Description=@desc

Print 'Connection Error'

end

-- place follow on code here.

REturn

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 7

Yes you can change this to use SQL Server authentication, but then you would have to store a password in the script. I feel this is a security risk, this is why I used windows authentication. I think your problem is related to your Linked Server security setup. Make sure your security for your linked server has a mapping for the login you connect to your server with when you run this script. Based on what you told me you would have to login to query analyzer using "domain\sqlseviceaccount". If you logged in with a different account will need to map that account to the remote sa account, or some other account on the remote server. If you have more questions please let me know.

Gregory A. Larsen, MVP

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 8

here is my original script that uses SQL Server Authentication:

DECLARE @object int

DECLARE @hr int

DECLARE @src varchar(255), @desc varchar(255)

DECLARE @location varchar(100)

DECLARE @CMD nvarchar(1000)

DECLARE @objresults int

set @location = 'Create SQLServer Object'

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT

IF @hr <> 0 goto ERROR

-- use Windows Authentication

EXEC @hr = sp_OASetProperty @object, 'LoginSecure', 'False'

set @location = 'Connect to Server'

EXEC @hr = sp_OAMethod @object, 'Connect',Null,'serverthatcontainslinkedserverdefinitions','test','test'

IF @hr <> 0 goto ERROR

set @location = 'Execute TSQL to Linked Server'

set @cmd = 'ExecuteWithResults("select count(*) From linkedserver.master.dbo.sysobjects")'

EXEC @hr = sp_OAMethod @object,@cmd, @objResults OUT

IF @hr <> 0 goto ERROR

Print 'Successfully Connected to Linked Server'

RETURN

Error:

Print @location

EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailtoescription=@desc">Description=@desc

Print 'Connection Error'

-- place follow on code here.

RETURN

Gregory A. Larsen, MVP

Urszula Dron SSC-Addicted Points: 411 More actions · Answer 9

Now, when I used NT login in QA , the script connects to linked servers but insert statement to the table on local server does not work. I've tried to qualify it with the server name, also I've replaced it with ##table but I get error Invalid object name '##table '.

In ##table I see only rows from local server.

If I created a table on linked server and insert works, so connection is o.k..

Could it be a problem with DTC?

Thanks for your answers.

Urszula

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 10

I always have problems with trying to insert records into a local table via a linked server. I'm not exactly sure why, but I know if can be resolved. I would suggest you start another tread to address questions and problems related to you "insert" statement.

Gregory A. Larsen, MVP

Urszula Dron SSC-Addicted Points: 411 More actions · Answer 11

It is not a problem with insert itself. If you run my script without dmo part insert works fine. Also from my experience, if I want to insert from linked server to temporary table, only global table will work. In my case it has to be something specific to dmo connection. Below is my script where loop is interrupted if remote login does not work or other connectivity problem exists.

DECLARE @srvname sysname

DECLARE @EexecStr varchar(300)

SELECT @srvname = ' '

-- LOOP databases in dradmin..tbldrdatabases --

WHILE @srvname IS NOT NULL

BEGIN

SELECT @srvname = MIN(srvname) FROM master.dbo.sysservers

WHERE srvname > @srvname

AND providername ='sqloledb'

SELECT @EexecStr = 'insert admin.dbo.tblsrv_role_members select ' + '''' + @srvname +'''' +','

SELECT @EexecStr = @EexecStr + 'spv.name' + ',' + 'lgn.name from ' + '[' + @srvname +']' + '.master.dbo.spt_values spv '

SELECT @EexecStr = @EexecStr + ',' + '[' + @srvname +']' + '.master.dbo.sysxlogins lgn where spv.low = 0 and spv.type = '

SELECT @EexecStr = @EexecStr + '''' + 'srv' + '''' + 'and lgn.srvid IS NULL and spv.number & lgn.xstatus = spv.number'

EXEC (@EexecStr)

END