September 26, 2003 at 8:10 am
Hi!
I've got a stored proc that calls xp_cmdshell to start OSQL and log a
database error in a transaction outside the currently executing transaction.
When run in a COM+ application written in VB.NET, the xp_cmdshell fails and
gives me a return value of 255 which isn't documented. The COM+ application
runs under a ordinary NT user account. If I start ISQL/w as the same NT user
and execute my stored proc, it works like it should, ie xp_cmdshell starts
OSQL which logs on to SQL Server as 'sa', executes a third procedure
(CM_Error_LogWrite) which logs an error.
The string sent to xp_cmdshell looks like this:
SET @aBuffer = 'osql -U sa -P MyPassword -d ' + DB_NAME() + ' -Q
"CM_Error_LogWrite '
+ '''' + @source + ''',''' + @error + ''''
+ ',''' + @description + ''',''' + CONVERT(VARCHAR(30),@errorDateTime,113)
+ ''''
+ ',''' + @otherInformation + ''','''
+ @errorStack + ''',''' + CAST(@userId AS char(36)) + ''''
+ '"'
I made the following config changes to get it to work in ISQL/w:
+ the NT user has execute rights on xp_cmdshell
+ the SQL Agent proxy account is set an administrative account
+ the SQL Server process runs with an administrative account
I've also tried to set the COM+ application to use an administrative
account, but it still does not work.
Any ideas?
TIA
Jonas
BrgdsJonas
September 29, 2003 at 6:33 am
I suggest you begin/commit another transaction for CM_Error_LogWrite or use a second connection altogether. Using OSQL will cause you more problems and introduce more points of failure.
Far away is close at hand in the images of elsewhere.
Anon.
October 1, 2003 at 1:32 am
Hi and thanks for your answer.
How would I get a new transaction from within a stored procedure that won't get rolled back together with the original transaction?
I guess my options are to either start a job which in turn logs the error, or call a COM-component which does the logging.
Brgds
Jonas Hilmersson
BrgdsJonas
October 1, 2003 at 2:42 am
Ummm, forgot about that. Either use a separate connection or check to see if your logic can be changed.
In some of my apps I do similar where I do multiple updates in a transaction but if an error occurs I still want to update another table with the error code. In this case when an error occurs I rollback the tran, create a new one, bypass any other updates but then update the final table with the error code before committing the tran.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply