Logging to Database Error

Topics: Logging Application Block
Mar 2, 2011 at 9:53 PM

Hi All,

I am struggling with this particular error and was looking for some help. The error I receive follows. You can see that it tries to log it to the database and fails. Now, here's where I am struggling. What I did was use the provided .sql script to create a new Logging database and try to log to that database. That WORKS. Then I took that same script and modified it to match the table names in my database (which are basically the same as in the Logging database, except the names are capitalized), and now I am receiving this error.

I am using .NET Framework 3.5. I have attached the relevant pieces of my app.config below as well. You can see, the logdb works, the qwarndb doesn't. I have also attached the .sql script I modified and used. Any help would be appreciated.

----------------------------------------
Timestamp: 3/2/2011 5:42:53 PM

Message: Tracing to LogSource 'error' failed. Processing for other sources will continue. See summary information below for more information. Should this problem persist, stop the service and check the configuration file(s) for possible error(s) in the configuration of the categories and sinks.


Summary for Enterprise Library Distributor Service:
======================================
--> 
Message: 
Timestamp: 3/2/2011 10:42:53 PM
Message: At least one Delta Highway Sensor is reporting an operational failure or a communications failure.
Category: error
Priority: 5
EventId: 0
Severity: Error
Title:
Machine: T3400_WIN7
App Domain: NServiceBus.Host.Internal.GenericHost, NServiceBus.Host, Version=2.0.0.1145, Culture=neutral, PublicKeyToken=9fc386479f8a226c
ProcessId: 8144
Process Name: C:\Users\Sean\Documents\svn\idi\other\Oregon DOT\code\src\algorithm\DeltaHwy.Algorithm\bin\Debug\NServiceBus.Host.exe
Thread Name: DefaultQuartzScheduler_Worker-4
Win32 ThreadId:4260
Extended Properties: 
--> MachineName: T3400_WIN7
--> TimeStamp: 3/2/2011 10:42:53 PM
--> FullName: Microsoft.Practices.EnterpriseLibrary.Logging, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
--> AppDomainName: NServiceBus.Host.Internal.GenericHost, NServiceBus.Host, Version=2.0.0.1145, Culture=neutral, PublicKeyToken=9fc386479f8a226c
--> WindowsIdentity: T3400_Win7\Sean

Exception Information Details:
======================================
Exception Type: System.Data.SqlClient.SqlException
Errors: System.Data.SqlClient.SqlErrorCollection
Class: 16
LineNumber: 0
Number: 201
Procedure: WriteLog
Server: .\SQLEXPRESS
State: 4
Source: .Net SqlClient Data Provider
ErrorCode: -2146232060
Message: Procedure or function 'WriteLog' expects parameter '@EventID', which was not supplied.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void OnError(System.Data.SqlClient.SqlException, Boolean)
HelpLink: NULL

StackTrace Information Details: 
======================================
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command) in e:\Builds\EntLib\Latest\Source\Blocks\Data\Src\Data\Database.cs:line 445
   at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command, DbTransaction transaction) in e:\Builds\EntLib\Latest\Source\Blocks\Data\Src\Data\Database.cs:line 732
   at Microsoft.Practices.EnterpriseLibrary.Logging.Database.FormattedDatabaseTraceListener.ExecuteWriteLogStoredProcedure(LogEntry logEntry, Database db, DbTransaction transaction) in e:\Builds\EntLib\Latest\Source\Blocks\Logging\Src\DatabaseTraceListener\FormattedDatabaseTraceListener.cs:line 244
   at Microsoft.Practices.EnterpriseLibrary.Logging.Database.FormattedDatabaseTraceListener.ExecuteStoredProcedure(LogEntry logEntry) in e:\Builds\EntLib\Latest\Source\Blocks\Logging\Src\DatabaseTraceListener\FormattedDatabaseTraceListener.cs:line 166
   at Microsoft.Practices.EnterpriseLibrary.Logging.Database.FormattedDatabaseTraceListener.TraceData(TraceEventCache eventCache, String source, TraceEventType eventType, Int32 id, Object data) in e:\Builds\EntLib\Latest\Source\Blocks\Logging\Src\DatabaseTraceListener\FormattedDatabaseTraceListener.cs:line 89
   at Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners.TraceListenerWrapper.TraceData(TraceEventCache eventCache, String source, TraceEventType eventType, Int32 id, Object data) in e:\Builds\EntLib\Latest\Source\Blocks\Logging\Src\Logging\TraceListeners\TraceListenerWrapper.cs:line 93
   at Microsoft.Practices.EnterpriseLibrary.Logging.LogSource.TraceData(TraceEventType eventType, Int32 id, LogEntry logEntry, TraceListenerFilter traceListenerFilter, TraceEventCache traceEventCache) in e:\Builds\EntLib\Latest\Source\Blocks\Logging\Src\Logging\LogSource.cs:line 180
   at Microsoft.Practices.EnterpriseLibrary.Logging.LogWriterImpl.ProcessLog(LogEntry log, TraceEventCache traceEventCache) in e:\Builds\EntLib\Latest\Source\Blocks\Logging\Src\Logging\LogWriterImpl.cs:line 557

Category: 

Priority: -1

Severity: Error

----------------------------------------

<loggingConfiguration name="" tracingEnabled="true" defaultCategory="debug">
        <listeners>
            <add name="Rolling Flat File Trace Listener" type="Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners.RollingFlatFileTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
                listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.RollingFlatFileTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
                fileName="DeltaHwyAlgorithm.log" formatter="Text Formatter"
                rollFileExistsBehavior="Increment" rollInterval="Day" maxArchivedFiles="7" />
            <add name="Database Trace Listener" type="Microsoft.Practices.EnterpriseLibrary.Logging.Database.FormattedDatabaseTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
                listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Database.Configuration.FormattedDatabaseTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
                databaseInstanceName="qwarndb" writeLogStoredProcName="WriteLog"
                addCategoryStoredProcName="AddCategory" formatter="Text Formatter" />
        </listeners>
        <formatters>
            <add type="Microsoft.Practices.EnterpriseLibrary.Logging.Formatters.TextFormatter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
                template="Timestamp: {timestamp(local)}{newline}&#xA;Message: {message}{newline}&#xA;Category: {category}{newline}&#xA;Priority: {priority}{newline}&#xA;Severity: {severity}{newline}"
                name="Text Formatter" />
        </formatters>
        <categorySources>
            <add switchValue="Error" name="exception">
                <listeners>
                    <add name="Rolling Flat File Trace Listener" />
                    <add name="Database Trace Listener" />
                </listeners>
            </add>
            <add switchValue="Information" name="information">
                <listeners>
                    <add name="Database Trace Listener" />
                    <add name="Rolling Flat File Trace Listener" />
                </listeners>
            </add>
            <add switchValue="All" name="debug">
                <listeners>
                    <add name="Rolling Flat File Trace Listener" />
                    <add name="Database Trace Listener" />
                </listeners>
            </add>
            <add switchValue="All" name="error">
                <listeners>
                    <add name="Database Trace Listener" />
                    <add name="Rolling Flat File Trace Listener" />
                </listeners>
            </add>
            <add switchValue="All" name="critical">
                <listeners>
                    <add name="Database Trace Listener" />
                    <add name="Rolling Flat File Trace Listener" />
                </listeners>
            </add>
            <add switchValue="All" name="warning">
                <listeners>
                    <add name="Database Trace Listener" />
                    <add name="Rolling Flat File Trace Listener" />
                </listeners>
            </add>
        </categorySources>
        <specialSources>
            <allEvents switchValue="All" name="All Events" />
            <notProcessed switchValue="All" name="Unprocessed Category" />
            <errors switchValue="All" name="Logging Errors &amp; Warnings">
                <listeners>
                    <add name="Rolling Flat File Trace Listener" />
                </listeners>
            </errors>
        </specialSources>
    </loggingConfiguration>
    <dataConfiguration defaultDatabase="qwarndb" />
    <connectionStrings>
        <add name="qwarndb" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=qwarndb;Integrated Security=SSPI;Min Pool Size=2"
            providerName="System.Data.SqlClient" />
        <add name="logdb" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Logging;Integrated Security=SSPI;Min Pool Size=2"
            providerName="System.Data.SqlClient" />
    </connectionStrings>

/****** Object:  Database Logging    Script Date: 8/22/2005 ******/
USE [master]
GO

/** IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'qwarndb')
	DROP DATABASE [qwarndb]
GO **/

/** CREATE DATABASE [Logging]
 COLLATE SQL_Latin1_General_CP1_CI_AS 
GO **/

exec sp_dboption N'qwarndb', N'autoclose', N'false'
GO

exec sp_dboption N'qwarndb', N'bulkcopy', N'false'
GO

exec sp_dboption N'qwarndb', N'trunc. log', N'false'
GO

exec sp_dboption N'qwarndb', N'torn page detection', N'true'
GO

exec sp_dboption N'qwarndb', N'read only', N'false'
GO

exec sp_dboption N'qwarndb', N'dbo use', N'false'
GO

exec sp_dboption N'qwarndb', N'single', N'false'
GO

exec sp_dboption N'qwarndb', N'autoshrink', N'false'
GO

exec sp_dboption N'qwarndb', N'ANSI null default', N'false'
GO

exec sp_dboption N'qwarndb', N'recursive triggers', N'false'
GO

exec sp_dboption N'qwarndb', N'ANSI nulls', N'false'
GO

exec sp_dboption N'qwarndb', N'concat null yields null', N'false'
GO

exec sp_dboption N'qwarndb', N'cursor close on commit', N'false'
GO

exec sp_dboption N'qwarndb', N'default to local cursor', N'false'
GO

exec sp_dboption N'qwarndb', N'quoted identifier', N'false'
GO

exec sp_dboption N'qwarndb', N'ANSI warnings', N'false'
GO

exec sp_dboption N'Logging', N'auto create statistics', N'true'
GO

exec sp_dboption N'qwarndb', N'auto update statistics', N'true'
GO

use [qwarndb]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CATEGORY]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[CATEGORY](
	[CATEGORYID] [int] IDENTITY(1,1) NOT NULL,
	[CATEGORYNAME] [nvarchar](64) NOT NULL,
 CONSTRAINT [PK_CATEGORY] PRIMARY KEY CLUSTERED 
(
	[CATEGORYID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CATGEGORYLOG]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[CATGEGORYLOG](
	[CATEGORYLOGID] [int] IDENTITY(1,1) NOT NULL,
	[CATEGORYID] [int] NOT NULL,
	[LOGID] [int] NOT NULL,
 CONSTRAINT [PK_CategoryLog] PRIMARY KEY CLUSTERED 
(
	[CATEGORYLOGID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[LOG]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[LOG](
	[LOGID] [int] IDENTITY(1,1) NOT NULL,
	[EVENTID] [int] NULL,
	[PRIORITY] [int] NOT NULL,
	[SEVERITY] [nvarchar](32) NOT NULL,
	[TITLE] [nvarchar](256) NOT NULL,
	[TIMESTAMP] [datetime] NOT NULL,
	[MACHINENAME] [nvarchar](32) NOT NULL,
	[APPDOMAINNAME] [nvarchar](512) NOT NULL,
	[PROCESSID] [nvarchar](256) NOT NULL,
	[PROCESSNAME] [nvarchar](512) NOT NULL,
	[THREADNAME] [nvarchar](512) NULL,
	[WIN32THREADID] [nvarchar](128) NULL,
	[MESSAGE] [nvarchar](1500) NULL,
	[FORMATTEDMESSAGE] [ntext] NULL,
 CONSTRAINT [PK_LOG] PRIMARY KEY CLUSTERED 
(
	[LOGID] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[InsertCategoryLog]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE InsertCategoryLog
	@CategoryID INT,
	@LogID INT
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @CatLogID INT
	SELECT @CatLogID FROM CATGEGORYLOG WHERE CATEGORYID=@CategoryID and LOGID = @LogID
	IF @CatLogID IS NULL
	BEGIN
		INSERT INTO CATGEGORYLOG (CATEGORYID, LOGID) VALUES(@CategoryID, @LogID)
		RETURN @@IDENTITY
	END
	ELSE RETURN @CatLogID
END
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[AddCategory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[AddCategory]
	-- Add the parameters for the function here
	@CategoryName nvarchar(64),
	@LogID int
AS
BEGIN
	SET NOCOUNT ON;
    DECLARE @CatID INT
	SELECT @CatID = CATEGORYID FROM CATEGORY WHERE CATEGORYNAME = @CategoryName
	IF @CatID IS NULL
	BEGIN
		INSERT INTO CATEGORY (CATEGORYNAME) VALUES(@CategoryName)
		SELECT @CatID = @@IDENTITY
	END

	EXEC InsertCategoryLog @CatID, @LogID 

	RETURN @CatID
END

' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[ClearLogs]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE ClearLogs
AS
BEGIN
	SET NOCOUNT ON;

	DELETE FROM CATGEGORYLOG
	DELETE FROM [LOG]
    DELETE FROM CATGEGORY
END
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[WriteLog]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



/****** Object:  Stored Procedure dbo.WriteLog    Script Date: 10/1/2004 3:16:36 PM ******/

CREATE PROCEDURE [dbo].[WriteLog]
(
	@EventID int, 
	@Priority int, 
	@Severity nvarchar(32), 
	@Title nvarchar(256), 
	@Timestamp datetime,
	@MachineName nvarchar(32), 
	@AppDomainName nvarchar(512),
	@ProcessID nvarchar(256),
	@ProcessName nvarchar(512),
	@ThreadName nvarchar(512),
	@Win32ThreadId nvarchar(128),
	@Message nvarchar(1500),
	@FormattedMessage ntext,
	@LogId int OUTPUT
)
AS 

	INSERT INTO [LOG] (
		EVENTID,
		PRIORITY,
		SEVERITY,
		TITLE,
		[TIMESTAMP],
		MACHINENAME,
		APPDOMAINNAME,
		PROCESSID,
		PROCESSNAME,
		THREADNAME,
		WIN32THREADID,
		MESSAGE,
		FORMATTEDMESSAGE
	)
	VALUES (
		@EventID, 
		@Priority, 
		@Severity, 
		@Title, 
		@Timestamp,
		@MachineName, 
		@AppDomainName,
		@ProcessID,
		@ProcessName,
		@ThreadName,
		@Win32ThreadId,
		@Message,
		@FormattedMessage)

	SET @LogID = @@IDENTITY
	RETURN @LogID



' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK_CATG_LOG__CATG') AND parent_obj = OBJECT_ID(N'[dbo].[CATGEGORYLOG]'))
ALTER TABLE [dbo].[CATGEGORYLOG]  WITH CHECK ADD  CONSTRAINT [FK_CATG_LOG__CATG] FOREIGN KEY(	[CATEGORYID])
REFERENCES [dbo].[CATEGORY] (	[CATEGORYID])
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK_CATG_LOG__LOG') AND parent_obj = OBJECT_ID(N'[dbo].[CATGEGORYLOG]'))
ALTER TABLE [dbo].[CATGEGORYLOG]  WITH CHECK ADD  CONSTRAINT [FK_CATG_LOG__LOG] FOREIGN KEY(	[LOGID])
REFERENCES [dbo].[LOG] (	[LOGID])
GO

SET QUOTED_IDENTIFIER ON 
SET ARITHABORT ON 
SET CONCAT_NULL_YIELDS_NULL ON 
SET ANSI_NULLS ON 
SET ANSI_PADDING ON 
SET ANSI_WARNINGS ON 
SET NUMERIC_ROUNDABORT OFF 
go

DECLARE @bErrors as bit

BEGIN TRANSACTION
SET @bErrors = 0

CREATE NONCLUSTERED INDEX [IXCATEGORYLOG] ON [dbo].[CATGEGORYLOG] ([LOGID] ASC, [CATEGORYID] ASC )
IF( @@error <> 0 ) SET @bErrors = 1

IF( @bErrors = 0 )
  COMMIT TRANSACTION
ELSE
  ROLLBACK TRANSACTION

Mar 3, 2011 at 1:54 AM

Hi,

I tried your config and script. Everything is working at my side. Can you post the relevant code prior to logging at "qwarn" database? BTW I just noticed that you misspelled the "CategoryLog" but I don't think this is the cause.

Mar 3, 2011 at 3:42 PM

Sure, the code is run as part of a Quartz job (every few seconds) and the calling line is :

 

LoggerUtil.LogError("At least one Delta Highway Sensor is reporting an operational failure or a communications failure.");

 

which calls a simple utility function that is:

 

        public static void LogError(string error)
        {
            Logger.Write(error, "error", 5, 0, TraceEventType.Error, "", null);
        }

 

The full code, in case you are wondering is:

       //Check if there are any failed Delta Highway Sensors, and if so, log the situation at the appropriate severity level.
        private void CheckAndLogFailedSensors()
        {
            var failedCount = NumberOfSensorsFailed();
            if (failedCount >= 3)
            {
                LoggerUtil.LogCriticalError("All Delta Highway Sensors are reporting communications failures or operational failures.");
            }
            else if (failedCount > 0)
            {
                LoggerUtil.LogError("At least one Delta Highway Sensor is reporting an operational failure or a communications failure.");
            }
        }

which is part of a call chain triggered by the Quartz job.

 

I am really thinking at this point this is the result of some strange permission issue? When I googled this issue, there was only one person with this problem, and they had a problem using an ODBC connection that I am not using, and ended up rewriting a piece of the database log writer, which I really don't want to do.

As a side note, thanks for the spelling correction. I fixed it, deleted everything, and re-ran the script and am still dealing with the same issue.

I am using SQL Server 2008 Express. I am running my program in debug mode on my VS2008 in admin mode.

 

Thoughts? Debugging strategies I can try?

Mar 4, 2011 at 3:03 AM

I'm not sure if this is related to a permission issue. Can you examine on how it manages the parameters in the FormattedDatabaseTraceListener.cs? Anyway, you can refer here for debugging.

Jun 8, 2011 at 12:16 PM

Hi,

You have probably solved your problem by now, but I post a possible solution anyway...

I ran into exactly the same problem, and the reason was a database with a Case Sensitive collation. Entlib calls the WriteLog procedure with param @eventID when the proc expects @EventID.

Either alter the database to an Insensitive collation or change your procedures to something like this:

ALTER PROCEDURE [dbo].[AddCategory]
    -- Add the parameters for the function here
    @categoryName nvarchar(64),
    @logID int
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @CatID INT
    SELECT @CatID = CategoryID FROM Category WHERE CategoryName = @categoryName
    IF @CatID IS NULL
    BEGIN
        INSERT INTO Category (CategoryName) VALUES(@CategoryName)
        SELECT @CatID = @@IDENTITY
    END

    EXEC InsertCategoryLog @CatID, @logID

    RETURN @CatID
END

______________________

ALTER PROCEDURE [dbo].[WriteLog]
    @eventID INT, @priority INT, @severity NVARCHAR (32), @title NVARCHAR (256), @timestamp DATETIME, @machineName NVARCHAR (32), @AppDomainName NVARCHAR (512), @ProcessID NVARCHAR (256), @ProcessName NVARCHAR (512), @ThreadName NVARCHAR (512), @Win32ThreadId NVARCHAR (128), @message NVARCHAR (1500), @formattedmessage NTEXT, @LogId INT OUTPUT
AS
INSERT INTO [Log] (
        EventID,
        Priority,
        Severity,
        Title,
        [Timestamp],
        MachineName,
        AppDomainName,
        ProcessID,
        ProcessName,
        ThreadName,
        Win32ThreadId,
        Message,
        FormattedMessage
    )
    VALUES (
        @eventID,
        @priority,
        @severity,
        @title,
        @timestamp,
        @machineName,
        @AppDomainName,
        @ProcessID,
        @ProcessName,
        @ThreadName,
        @Win32ThreadId,
        @message,
        @formattedmessage)

    SET @LogID = @@IDENTITY
    RETURN @LogID