June 3, 2016 at 3:13 am
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.
June 3, 2016 at 3:25 am
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.
June 3, 2016 at 3:41 am
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.
June 3, 2016 at 4:04 am
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?
June 3, 2016 at 8:04 am
No I'm afraid not. I will keep digging - thanks for your assist this far.
J.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply