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

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

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