I have a windows service written using .NET 2.0/c#, LLBLGen Pro 2.5 with adapter that is connecting to a SQL Server 2008 dB.

One of our customers has reported an issue with the service and has run a SQL Server Profiler trace which includes the following INSERT statement. Strangely this is attempting set a SQL Server datetime variable to a string value including nanoseconds (ie. '2013-06-25 15:19:31.5130000') when the data type only caters for milliseconds. Running the statement manually results in the error "Error converting data type varchar to datetime".

When I run the equivalent code in my development environment I can see that the datetime variables are set correctly using string values only including milliseconds (eg. '2013-06-25 15:19:31.513').

Does anyone have an idea why the nanoseconds are being included?

exec sp_executesql N'INSERT INTO [dbo].[ActivityParticipant] ([recID], [activityID], [typeID], [involvementID], [userName], [createUserName], [modifyDate], [modifyUserName])  VALUES (@RecId, @ActivityId, @TypeId, @InvolvementId, @UserName, @CreateUserName, @ModifyDate, @ModifyUserName)',N'@RecId uniqueidentifier,@ActivityId uniqueidentifier,@TypeId uniqueidentifier,@InvolvementId uniqueidentifier,@UserName nvarchar(50),@CreateUserName nvarchar(50),@ModifyDate datetime,@ModifyUserName nvarchar(50)',@RecId='5CD983FC-61BB-4664-84B0-7E649D0A4B8F',@ActivityId='3A004BD2-47B3-44E3-B6B6-CBC25097CC57',@TypeId='258B5EBE-406C-DC11-9FDF-0019D1462DD5',@InvolvementId='251E58C2-72D1-DB11-B91A-00134678829E',@UserName=N'DAVIDW',@CreateUserName=N'exchangesync',@ModifyDate='2013-06-25 15:19:31.5130000',@ModifyUserName=N'exchangesync'

Seems like an old .NET issue.

please check these: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14727

Thanks for the response Walaa but that post relates to the SQL 2008 "time" data type whereas my column is a "datetime" so is that relevant in my case?

Could you please post a stack trace of the exception, and the exact error text coming from the database?

Also I recommend using DATETIME2 SQL data type.

values are always stated as strings in the sql query of a trace. No idea why the nanoseconds are present, I would at least check if the latest service pack for .net 2 is installed.

I'm afraid that I can't provide a stack trace as the service is handling the error. I'm waiting on some further feedback from the customer and have also passed on the recommendation from Frans.

I'm closing as the customer's IT department have come back to say that the records associated with the inserts in question do actually exist. Thanks for your help guys.