Post reply

Using OPENROWSET and trapping errors

  • July 10, 2002 at 12:20 am

    #105014

    Hi,

    I'm trying to use OPENROWSET in order to connect to various servers and retrieve some job information from the msdb database.

    However, occasionally, a server I'm trying to connect to might be down and therefore the OPENROWSET connection will naturally fail.

    The problem I'm having is that if an OPENROWSET connection fails, the whole procedure that I am running will fail and execution will stop. What I want is, if the connection fails, to trap that error and continue executing the rest of the procedure code.

    As an example of what I mean, try running the following statement against a non-existent server. If the connection fails the PRINT 'HELLO' statement will not run unless I seperate the batches with the GO statement (Which I cannot do in a procedure).

    SELECT j.name as JobName

    FROM OPENROWSET

    ('SQLOLEDB','Trusted_Connection=yes;Server=<nonexistant servername>',

    'SELECT j.name from msdb..sysjobs j') as j

    PRINT 'HELLO'

    Ultimately what I would like to do is replace the PRINT statement with a check for an error (e.g. IF @@ERROR <> 0).

    Has anyone come across this problem before and a potential way around it?

    Karl

    Karl Grambow

    Karl
    source control for SQL Server

  • July 10, 2002 at 5:43 am

    #432326

    One method you can use is compartmentlize the procedure into component procedures.

    Ex.

    I have a database with a tables yyy

    CREATE PROC ip_test1

    AS

    SELECT * FROM xxx --Does not exist

    CREATE PROC ip_test2

    AS

    SELECT * FROM yyy --Does exist

    CREATE PROC ip_testmain

    AS

    EXEC ip_test1

    EXEC ip_test2

    I will will get error in proc test1 and output from test2 in the above example. Other than this I don't remember another way. If later pieces rely on output from previous steps it may take a bit to right to work but can be done.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • July 11, 2002 at 12:39 am

    #432415

    Thanks for your reply.

    Unfortunately I'm not going to be able use your solution. This is because the number of servers I'm connecting to is dynamic so I would not be able to create x number of procedures without knowing how many I would need to create to begin with.

    Looks like I'll have to think of another way around this problem.

    Karl Grambow

    Karl
    source control for SQL Server

  • July 11, 2002 at 6:17 am

    #432441

    Then you may want to look at using Dynamic SQL via EXECUTE or sp_executesql. See SQL BOL for more details on these processes.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • March 2, 2005 at 10:09 am

    #544931

    I have gotten around this problem by calling the stored procedure that calls OpenRowset from a parameterized DTS package (Exec usp_ProcedureName ?,?,?) I use the package's global variables as input to the stored procedure, and then execute the DTS package from another stored procedure passing the values to the global variables. If the DTS package fails it will return a trapable error. It's a bit convoluted, but it works.

    PS - As I am importing from multiple Visual FoxPro tables, my SP dynamically builds the Openrowset query, and then calls sp_ExecuteSQL to execute the OpenRowset query.

  • March 2, 2005 at 4:18 pm

    #545064

    I have the same problem, need to be able to continue executing within a stored proc after an error occurs in a linked server query or call to a remote stored proc. Does anyone know of any other ways to do this (besides the DTS method). Thanks fella's! 

Viewing 6 posts - 1 through 5 (of 5 total)

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