Using OPENROWSET and trapping errors

Question

Post reply

Using OPENROWSET and trapping errors

SQLZ

SSChampion

Points: 12872
More actions
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

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

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)

SQLZ SSChampion Points: 12872 More actions · Answer 2

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

Antares686 SSC Guru Points: 125444 More actions · Answer 3

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)

John McLaughlin-213204 Old Hand Points: 340 More actions · Answer 4

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.

Ben Blum Right there with Babe Points: 776 More actions · Answer 5

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!