November 12, 2010 at 7:54 am
Is it possible to use a try catch with Openrowset. WhenI run this code it failed to run the error catching stored procedure.
BEGIN TRY
SELECT a.* FROM OPENROWSET('SQLOLEDB', 'TEST';'CC';'TTT','SELECT @@SERVERNAME AS ServerName, SUBSTRING(s.name, 1, 40) AS Expr1, DATEDIFF(dd, b.backup_start_date, GETDATE())
AS Expr3, GETDATE() AS RD, b.backup_start_date, b.type
FROM master.dbo.sysdatabases s LEFT OUTER JOIN
msdb.dbo.backupset b ON s.name = b.database_name')AS a;
END TRY
BEGIN CATCH
EXEC USP_ERRORHANDLE
END CATCH;
November 12, 2010 at 8:23 am
it appears not.
login, statement errors aren't trapped.
Cursors never.
DTS - only when needed and never to control.
November 12, 2010 at 8:28 am
It's because it tries to get the resultset format when compiling.
The error is a compile error not a run time error so is not trapped.
Cursors never.
DTS - only when needed and never to control.
November 12, 2010 at 8:32 am
If you make it a run time error (i.e. stop it being parsed) then it is trapped.
I tend to do most openrowsets like this so haven't noticed it before.
BEGIN TRY
declare @sql varchar(1000)
select @sql = '
SELECT a.* FROM OPENROWSET(''SQLOLEDB'', ''server'';''user'';''password'',''SELECT @@SERVERNAME AS ServerName, SUBSTRING(s.name, 1, 40) AS Expr1, DATEDIFF(dd, b.backup_start_date, GETDATE())
AS Expr3, GETDATE() AS RD, b.backup_start_date, b.type
FROM master.dbo.sysdatabases s LEFT OUTER JOIN
msdb.dbo.backupset b ON s.name = b.database_name'')AS a'
exec (@sql)
END TRY
BEGIN CATCH
select 'failed'
END CATCH;
Cursors never.
DTS - only when needed and never to control.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply