Post reply

Trapping Connection Errors

  • 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

  • December 28, 2004 at 12:10 pm

    #535168

    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

  • December 28, 2004 at 12:29 pm

    #535174

    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

  • December 28, 2004 at 12:41 pm

    #535177

    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

  • December 28, 2004 at 12:47 pm

    #535179

    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

  • December 28, 2004 at 1:49 pm

    #535191

    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

  • January 21, 2005 at 10:13 am

    #538547

    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

  • January 21, 2005 at 11:28 am

    #538550

    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

  • January 21, 2005 at 11:57 am

    #538555

    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

  • January 21, 2005 at 2:22 pm

    #538597

    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

  • January 21, 2005 at 5:41 pm

    #538630

    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

  • January 21, 2005 at 6:03 pm

    #538633

    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

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

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