Capture error return by MS Sql
-
Reading excel file with OPENROWSET of sql server. Working fine and getting values. Now, if the file which i am reading is in use (opened), it gives error. How should i capture this error, I tried
Declare @error varchar(50)
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\webex\cis\CIS overview.xls',
'SELECT * FROM [CIS Overview$]')IF(@@error <> 0 )
BEGIN
Set @error = 'Error reading file'
RETURN
ENDBut its not even reaching to IF clause, terminate giving message
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.How can i capture this error ? reagards
-
Reading excel file with OPENROWSET of sql server. Working fine and getting values. Now, if the file which i am reading is in use (opened), it gives error. How should i capture this error, I tried
Declare @error varchar(50)
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\webex\cis\CIS overview.xls',
'SELECT * FROM [CIS Overview$]')IF(@@error <> 0 )
BEGIN
Set @error = 'Error reading file'
RETURN
ENDBut its not even reaching to IF clause, terminate giving message
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.How can i capture this error ? reagards
You can use try..catch in T-SQL also. Refer to: TRY...CATCH (Transact-SQL)[^]
The need to optimize rises from a bad design.My articles[^]
-
You can use try..catch in T-SQL also. Refer to: TRY...CATCH (Transact-SQL)[^]
The need to optimize rises from a bad design.My articles[^]
-
Okay, and you're sure the IF is not hit at all... One thing you could try is to call this procedure from another and check if you can handle the error properly in the calling procedure.
The need to optimize rises from a bad design.My articles[^]
-
Okay, and you're sure the IF is not hit at all... One thing you could try is to call this procedure from another and check if you can handle the error properly in the calling procedure.
The need to optimize rises from a bad design.My articles[^]
Mika Wendelius wrote:
you're sure the IF is not hit at all...
Well instead of if you can use PRINT, if control reaches to print it should print. But execution terminates on OPENROWSET itself.
Mika Wendelius wrote:
call this procedure from another and check if you can handle the error properly in the calling procedure.
No luck ! in a new query window i tried like
EXEC [spName]
IF(@@error <> 0)
BEGIN
Print 'No error'
END
ELSE
BEGIN
Print 'error'
ENDhere also not going to if clause ! Any other suggestion?
-
Mika Wendelius wrote:
you're sure the IF is not hit at all...
Well instead of if you can use PRINT, if control reaches to print it should print. But execution terminates on OPENROWSET itself.
Mika Wendelius wrote:
call this procedure from another and check if you can handle the error properly in the calling procedure.
No luck ! in a new query window i tried like
EXEC [spName]
IF(@@error <> 0)
BEGIN
Print 'No error'
END
ELSE
BEGIN
Print 'error'
ENDhere also not going to if clause ! Any other suggestion?
Hmm, what happens if you try to execute the statement dynamically. Put it into a string and then use exec to run the statement. Unfortunately I don't have SQL Server 2000 to test this issue...
The need to optimize rises from a bad design.My articles[^]