Search code examples
c#sql-serverwindows-servicessqlexception

Receiving different SqlException in Windows service to expected

I'm having a very strange issue. I have windows service which is failing because of a SqlException: "String or binary data would be truncated." for an INSERT statement.

Now this is a fairly basic error to solve but it is not the true error. If I do a trace and run the query straight on the database - there is NO error. All of the data in WAY shorter than the restrictions on the database.

I eventually took out some of this required columns from the query hoping to generate a different error: "Cannot insert the value NULL into column 'Type'"

I don't however get this error! I am still getting "String or binary data would be truncated."

I DO get this error if I run the query from the trace straight on the DB.

Does anyone have any ideas on what could be happening here? Thanks!

Edit to add Here's the query that is supposed to give me the cannot insert value error. The other query is the same but with more parameters:

declare @p4 int
set @p4=60029550
exec sp_executesql N'EXECUTE ssl_GetTTDVersionCallSeq 1, @TTDVersionIDVal OUTPUT
INSERT INTO TTDVersion(Task,ID) VALUES (@P0,@TTDVersionIDVal)',N'@P0 int,@TTDVersionIDVal int output',@P0=200003762,@TTDVersionIDVal=@p4 output
select @p4
Solution

  • Found the cause of this error. It was not at all in the query posted above. There was a trigger on the table which set the LastUpdatedBy column.

    Most of the users have a 4 char user name but the user that the service was being run as didn't. The column limit was 4.

    Avoid this issue:

    • Triggers can be problematic. They aren't immediately visible - sp_help 'table' doesn't even return them. If they error you can't even trace the query.

    What I should of tried earlier:

    • Running the query as the user. (I wanted to but it's an admin user and someone else was using it at the time.)
    • Checking all columns info vs what was in them and what was in the query. Then to check how that info is getting there. In this case a default constraint probably would of given same issue.