SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 How to protect errorlogs to tables from rollbacks
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Daniel_Buchholz
Starting Member

Germany
28 Posts

Posted - 04/05/2002 :  02:38:59  Show Profile  Reply with Quote
Hey!

This post contains the code for this thread: http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14475

It deals with the problem how to prevent log actions in long running batch jobs from being rolled back. It was heavily inspired by Andy Pope´s approach to error handling (http://www.sqlteam.com/item.asp?ItemID=2290) and in fact you will see much of his code here.

The code:

This procedure dynamically opens a second connection in parallel to the existing connection of the calling procedure using SQL-DMO. So the second connection runs without the scope of transaction of the calling procedure. So no action you take here is rolled back in case the calling proc fails. So be careful! Keeping data integrity is your job here and you could do many weird things to your database.
The procedure dynamically adds a user function that if called just would return the object token of the new DMO connection. So any piece of code in the same batch could reuse the exisiting connection.

LogConstructor

CREATE PROCEDURE LogConstructor AS

if exists (select * from sysobjects where id = object_id (N'dbo.MFF_GetLogObject')
and OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
drop function dbo.MFF_GetLogObject

DECLARE @Error INT
DECLARE @ErrorMsg VARCHAR(255)
DECLARE @oSQLServer INTEGER
DECLARE @Source VARCHAR(255)
DECLARE @Return INTEGER
declare @dynsql nvarchar(3000)

-- Create the SQLServer object
EXEC @Error = sp_OACreate 'SQLDMO.SQLServer', @oSQLServer OUT
IF @Error <> 0
GOTO OA_Error

-- Set the login process to use NT Authentication
EXEC @Error = sp_OASetProperty @oSQLServer, 'LoginSecure', -1
IF @Error <> 0
GOTO OA_Error

-- Connect to server using NT Authentication
EXEC @Error = sp_OAMethod @oSQLServer, 'Connect', NULL, @@SERVERNAME
IF @Error <> 0
GOTO OA_Error

-- Verify the connection
EXEC @Error = sp_OAMethod @oSQLServer, 'VerifyConnection', @Return OUTPUT
IF @Error <> 0
GOTO OA_Error
IF @Return = 0
GOTO OA_Error

-- Create Function with server object
select @dynsql = N'CREATE Function MFF_GetLogObject () RETURNS INT AS BEGIN RETURN ' + cast(@oSQLServer as varchar) + N' END'
EXEC sp_executesql @dynsql

return


OA_Error:
-- Get the error text
EXEC sp_OAGetErrorInfo @oSQLServer, @Source OUT, @ErrorMsg OUT
SELECT
@ErrorMsg = CONVERT(CHAR(16), @Error) + ': ' + @ErrorMsg + ' (Source: ' + @Source + ')'
print @ErrorMsg
return
GO

The next procedure just drops the DMO connection and also drops the user function as the token is invalid by now. This proc should be called within the same batch as the constructor to clean things up properly.

LogDestructor

CREATE PROCEDURE MFP_LogDestructor AS

declare @lo int
select @lo = dbo.MFF_GetLogObject()
exec sp_OADestroy @lo

if exists (select * from sysobjects where id = object_id(N'dbo.MFF_GetLogObject')
and OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
drop function dbo.MFF_GetLogObject
GO

Daniel_Buchholz
Starting Member

Germany
28 Posts

Posted - 04/05/2002 :  02:41:22  Show Profile  Reply with Quote
Part 2 ;-)

The last part is the log procedure itself. And it is using the DMO connection and the user function so make sure LogConstructur is called before!

LogAction


CREATE PROCEDURE LogAction

@iIDExportLog int,
@sMessage varchar(500),
@sLogCategory char(1),
@sSource varchar(50),
@errorcheck int=0 OUTPUT
AS

declare @LogObject int
declare @oDatabase int
declare @oQueryResults int
declare @Length int
declare @ErrorMsg varchar(255)
declare @Source varchar(255)
declare @Error int
declare @CurrentDB varchar(255)
declare @SQL varchar(8000)

-- Get current objecttoken for log connection
select @LogObject = dbo.MFF_GetLogObject()

-- Set the database object to be the current database
-- (We do this so we don't need to execute the SQL against the
-- SQLServer object, which would require explicit DB prefixing)
SELECT @CurrentDB = DB_NAME()
SELECT @CurrentDB = 'Databases("' + RTRIM(@CurrentDB) + '")'
EXEC @Error = sp_OAGetProperty @LogObject, @CurrentDB, @oDatabase OUT
IF @Error <> 0
GOTO OA_Error

-- Build the SQL string
-- This is my version. Add your code. Change the insert or call
-- another procedure. Do whatever you like but KEEP YOUR
-- DATABASE CLEAN!
SET @SQL = 'insert into LogDetail
(IDExportLog,
Message,
LogCategory,
Source,
Zeit)
values
(' + cast(@iIDExportLog as varchar) + ',
''' + @sMessage + ''',
''' + @sLogCategory + ''',
''' + @sSource + ''',
getdate())'

-- Execute the SQL (this will NOT be rolled back)
-- We could use ExecuteImmediate here, but it doesn't return a result
-- and we found while debugging that you really NEED that
SET @Length = LEN(@SQL)
SET @ErrorMsg = '' -- Must initialize the @ErrorMsg string
EXEC @Error = sp_OAMethod @oDatabase, 'ExecuteWithResultsAndMessages',
@oQueryResults OUTPUT, @Length = @Length,
@Command = @SQL , @Messages = @ErrorMsg OUTPUT
IF @Error <> 0
GOTO OA_Error

EXEC @Error = sp_OADestroy @oDatabase
EXEC @Error = sp_OADestroy @oQueryResults

return

OA_Error:
-- Get the error text
EXEC sp_OAGetErrorInfo @LogObject, @Source OUT, @ErrorMsg OUT
SELECT @ErrorMsg = CONVERT(CHAR(16), @Error) + ': ' + @ErrorMsg + ' (Source: ' + @Source + ')'
select @errorcheck = 1
print @ErrorMsg
return
GO


That´s it. Have fun!

Daniel

Edited by - Daniel_Buchholz on 04/05/2002 02:42:22
Go to Top of Page

Jay99
Constraint Violating Yak Guru

USA
468 Posts

Posted - 04/05/2002 :  10:05:15  Show Profile  Reply with Quote
I like this. The constructor and destructor . . . if two batchs from the same user schema need to do logging at the same time, is it possible for the CREATE FUNCTION DDL to fail because the function already exists? How would you handle this? Allow both batches to use the same connection? How do you know when to clean up?

Jay
<O>
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000