Post reply

Job fails because remote server insert fails. Need testing method.

  • January 14, 2004 at 4:01 pm

    #107699

    I have a Job that periodically calls an SP....

    SP tries to insert records to a remote SQL Server using

      Insert Into OpenRows et('SQLOLEDB', 'Server=[an IP #]....') .....

    All is well when at the IP # the SQL Server exists and is up & running.

    When not, the Job History shows ....

     

      SQLSTATE 01000 Message 22001  SQL Server does not exist or access denied. 

      SQLSTATE 42000 Error 17.  The step failed.

    , the Job "fails", and a "Warning" Application log entry goes into the Win2K Event log.

    Is there a way to test 1st or lower the status of the error so that all

    indicates ok, even when the INSERT fails?

    Note: It is not "critical" to me that the INSERT fails or not.

    Thanks

    P.S. This issue is fairly time critical to me.



    Once you understand the BITs, all the pieces come together

  • January 14, 2004 at 5:01 pm

    #490100

    No way I am aware of that can guarantee the remote/linked server is up when you perform operation to it.

    What do you try to achieve by lower the status of the error?

  • January 14, 2004 at 5:07 pm

    #490101

    ....What do you try to achieve by lower the status of the error?

    status = severity

    No Server Event Log entry or Job Failed status for the customer to "complain" about.

    Any SP Statements after the failed INSERT to continue to execute.

     

    I would like to error to be "non severe".



    Once you understand the BITs, all the pieces come together

  • January 14, 2004 at 5:19 pm

    #490105

    Have a look at system table "sysmessages". Maybe you can do something from there.

  • January 14, 2004 at 5:30 pm

    #490108

    Have your sp check for that specific error using @@error and if that error occurs, have it return 0 (success).

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • January 14, 2004 at 6:15 pm

    #490111

    Thanks for input folks...

    Rawhide, as soon as the error occurs, any subsequent SQL statements do not get executed

    so as is, I can not "trap".

    Try this little script in Q/A to show what I mean... I need to get to the second Print.

    Exec ('select * from OpenRowset(''SQLOLEDB'',

        ''Server=(local);Trusted_Connection=yes;Timeout=5;'',

        ''Select top 1 * from sysfiles'') ')

    Print 'got this far! Yeah!'

    -- 192.123.123.123 must NOT be a good SQL Srv. for this test

    Exec ('select * from OpenRowset(''SQLOLEDB'', 

        ''Server=192.123.123.123;Trusted_Connection=yes;Timeout=5;'',  

        ''Select top 1 * from sysfiles'') ')

    Print 'never get here boo'



    Once you understand the BITs, all the pieces come together

  • January 15, 2004 at 7:57 am

    #490203

    Any other ideas folks?

    Thanks in advance.



    Once you understand the BITs, all the pieces come together

  • January 16, 2004 at 12:08 pm

    #490515

    Have you tried creating a linked server instead of using the openrowset? It may handle this situation a little differently. I don't currently have a machine to test this with. Sorry.




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • January 16, 2004 at 12:24 pm

    #490519

    Funny, I was just on anther thread about linked servers... I do not want to make a "permanent" linked server, for various reasons... but maybe the "failer" of being able to make the linked server entry may not be as "severe" as the INSERT, and I can use this "make linked server entry" just as a test, then if it does create it, just drop the entry.

    Thanks I'll try it...



    Once you understand the BITs, all the pieces come together

  • January 16, 2004 at 3:50 pm

    #490562

    You could throw something like this in your script and then drop it at the end....

     

    DECLARE @LinkedServerName     sysname

        , @provstr                nvarchar(4000)

        , @ServerName             sysname

    -------------------------------------------------------------------------------

    -- Change the following to the remote servername!

    SET @ServerName = 'servername'

    -------------------------------------------------------------------------------

    SELECT @LinkedServerName =  'ReplicationServer_' + @ServerName

        , @provstr = 'DRIVER={SQL Server};SERVER=' + @ServerName + ';UID=User;PWD=userpwd;'

    -- Drop the server if it exists

    IF EXISTS(SELECT * FROM master.dbo.sysservers WHERE srvname = @LinkedServerName)

        EXEC sp_dropserver @LinkedServerName

    -- If we don't have the login for the LexMirrorUser create it.

    IF NOT EXISTS(SELECT * FROM master.dbo.syslogins WHERE name = 'User')

     EXEC sp_addlogin 'User', 'userpwd', 'defaultdb'

    -- Now Add the Linked Server

    EXEC sp_addlinkedserver

       @server = @LinkedServerName

       , @provider = 'MSDASQL'

       , @provstr = @provstr

       , @srvproduct = 'any'

     

     




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • January 23, 2004 at 8:18 am

    #491385

    Tried your code Gary, and plain old

    sp_addlinkedserver ..., @srvproduct = '', @provider = 'SQLOLEDB',

    sp_serveroption(s)...,

    sp_addlinkedsrvlogin

    Both ways produce no errors even if remote server does not exist.

    Once I try to access something from the remote server I get either:

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

    SQL Server does not exist or access denied.

    or, Using your code I get:

    Server: Msg 7399, Level 16, State 1, Procedure Test, Line 1

    OLE DB provider 'MSDASQL' reported an error. 

    [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).]

    [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.]

    OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:   ].

    Both are "severe" errors, and do not allow further code to execute

    Even tried "2 level" approach...

    select * from OpenRowset('SQLOLEDB', 'Server=(local);Trusted_Connection=yes',

     'select count(1) as c from RemoteSrv.Master.DBO.SysServers')

    Got error:

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

    SQL Server does not exist or access denied.

    [OLE/DB provider returned message: Deferred prepare could not be completed.]

    Any other ideas??



    Once you understand the BITs, all the pieces come together

  • February 27, 2004 at 9:02 am

    #496231

    Well I ended up using an SP I wrote a while back that consists of creating a Job, executing it,  monitoring its success or failure, and returning any error. Since this is a once in a while task only, it did not matter to me if it takes an extra second or two to perform the operation (it takes longer on failure because it needs to "timeout").

    Since the Job is a separate process, it does NOT make the current process fail.

    The "temp" Job that gets created, does not "system log" any of its actions or errors, and is deleted upon completion. This work just fine for me.

    Thanks all for your input.



    Once you understand the BITs, all the pieces come together

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

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