select * from Exceptions$

Question

select * from Exceptions$

jellybean

SSCommitted

Points: 1954
More actions
June 3, 2016 at 3:13 am

#319239

Hi,
I have inherited a sproc that writes out error messages to an excel file. It build up a dynamic sql command and then executes it. Inside the command is the line
"SELECT * FROM [Exceptions$]"
To be specific the relevent segment of Sql code is:
SET @varSql2 = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0;Database=' + @varTemplateConnectionString + ''',
''SELECT * FROM [Exceptions$]'')'
+ @varSql
BEGIN TRY
EXEC (@varSql2)
Can anyone please tell me what Exception$ is please (or where is it?)? I have googled for it and got nowhere. There are no other references to it in the sproc. BTW, the main problem that I am working on is that sometimes the error messages are written to file and sometimes not and I feel that it *may* be related to this particular LOC.
Any information would be most appreiated,
Thanks,
J.

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

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

joeroshan SSChampion Points: 10377 More actions · Answer 1

It is the name of the sheet in the excel, example if you have multiple sheets, each one of them can be accessed as a table individually.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

jellybean SSCommitted Points: 1954 More actions · Answer 2

Yes, of course - your right joeroshan. Good spot I should have seen it myself. Well, that's a dead end then. It does not really explain why sometimes the error message gets written to the sheet and sometimes not.

Hmmm. There is another part of the sproc calling a function called fnGetErrorInfo. Here is the function

ALTER FUNCTION [dbo].[fnGetErrorInfo]

()

RETURNS @retVals TABLE

(

-- Columns returned by the function

ErrorNumberINT,

ErrorSeverityINT,

ErrorStateINT,

ErrorProcedureNVARCHAR(126),

ErrorLineINT,

ErrorMessageNVARCHAR(max)

)

AS

BEGIN

INSERT @retVals

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage;

RETURN

END

I'm just wondering do you see anything unusual here? Any comments/suggestions you like to offer?

Cheers,

J.

joeroshan SSChampion Points: 10377 More actions · Answer 3

I cant find anything wrong in the function assuming you are using this in your catch block. How are you handling the catch for this insert. Are you getting any error there?

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

jellybean SSCommitted Points: 1954 More actions · Answer 4

No I'm afraid not. I will keep digging - thanks for your assist this far.

J.