January 8, 2014 at 12:26 am
Hello,
This is a bizarre situation we have started getting in some of our client environments. Basically, we are getting spurious Error 8152's / String or binary data would be truncated.
They are definitely spurious. It is somehow linked to both the PC in use plus the user account that the application is running under. So logged in as one user, it occurs, logged in as another it doesn't. And logged in as the same user on one PC is fine, but on another it isn't.
Initially I thought it might have been some sort of ado.net corruption, and was fully expecting to see bogus data in the profiler (like the strings were corrupted). but all looks fine.
Running SQL profiler shows the exception, and the physical statement being executed. However, you execute the command manually, and it works fine. For example:
exec sp_executesql N'INSERT INTO "MT_Store".."SystemProperties" ("Property","Value","SiteID","Host","UserID","LastUpdated","UpdatedByUserID") VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7)',N'@P1 varchar(17),@P2 varchar(14),@P3 varchar(2),@P4 nvarchar(30),@P5 int,@P6 datetime,@P7 int','Version:Ticketing','3.2.0.246 ','HO',N'COR01D45WCC2S ',NULL,'2014-01-08 14:59:06',0
Error: 8152, Severity: 16, State: 13
The data in the exec is fine. Nothing is longer than allowed, and more to the point, the exec will run manually fine, on the same database
This is happening randomly. Oddly it has only started occurring in the last 6 months, and since it started, it has happened at at least three customers, for single users at each of them, both AD users, and local PC user acounts.
January 8, 2014 at 4:27 am
What datatypes and lengths are defined for the fields in the [MT_Store] table? Can you post the DDL statement of this table?
What is your default setting of "SET ANSI_WARNINGS" and how are these settings defined in each connection?
January 8, 2014 at 6:48 am
If @P2 is IP Adress then you need to make it varchar(15) for IPv4 and upto varchar(39) for IPv6.
Far away is close at hand in the images of elsewhere.
Anon.
January 8, 2014 at 4:34 pm
Thanks for the suggestions, but you may be misunderstanding what I have said.
I am more than fully aware of what causes this error in normal circumstances, trying to put too long a string into too short a field. I get it. That is what that error means. That is 100% repeatable, irrespective of the user, irrespective of the machine. If this was happening, I could capture the failed statement in profiler, and run it in a query window and get _exactly_ the same error.
But, no, that is not happening. I've seen all the other threads on the entire internet on this error, and every single one of them follows the same pattern:
Newb: Why is this insert failing? Insert into Table1(ColName) Value('Long Long String.................')
Respondant: What is the DDL
Newb: Oh, is it Table1(ColName varchar(5) null)
Respondant: Look, you are trying to put a string with a (length > 5) into a field with a (length = 5). What did you expect would happen?
However, that is NOT what is happening here. This is the pattern. With the EXACT same physical SQL statement on the SAME DATABASE:
- AD User A logged into PC 1: OK
- AD User B logged into PC 1: Failed
- AD User C logged into PC 1: OK
- Local User D logged into PC 1: OK
- AD User A logged into PC 2: OK
- AD User B logged into PC 2: OK
- AD User C logged into PC 2: OK
- Local User D logged into PC 3: Failed
- Running failed query manually in query window: OK
January 8, 2014 at 4:43 pm
Are there any triggers coming into play, maybe auditing the user?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
January 8, 2014 at 5:02 pm
Thanks
Yes, there is a minor trigger on it, but that just populates the lastupdated date and DB host name fields, which will use the same values, irrespective of the user and application host PC. There are also the standard merge replication triggers on it, but they'd also have the same result irrespective of user. I'd kind of expect a different error if the issue was on replication metadata tables. And the error to repeat if run manually.
Further into: Note that the DB connection itself is just a shared SQL user, not a trusted connection. Initially I was thinking some sort of MDAC corruption, but if that was the case I was expecting the query in profiler to be corrupt. We do have the ability for trusted connections, but the clients don't generally use it as it means they have to manage the SQL rights for all the appropriate AD users on all the SQL hosts.
We have the software in several hundred machines, in around 100 different client environments and to date it has affected just 4 user accounts on 4 user machines in 4 different client environments. so pretty sparse.
January 8, 2014 at 5:47 pm
Did you notice any difference in the connection settings (e.g. SET ANSI_NULLS etc.?) when you traced the problem?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
January 8, 2014 at 6:16 pm
mister.magoo (1/8/2014)
Did you notice any difference in the connection settings (e.g. SET ANSI_NULLS etc.?) when you traced the problem?
Good idea. I'll check once I can get access to the client machine (seems to have gone into hibernation). The Audit Login event in profiler should show that.
That said, I'm not expecting anything different, given same PC, same app, same application DB connection string.
EDIT: No, they are all the same.
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
January 9, 2014 at 2:11 am
I would concur with MM it could be the trigger.
Can you post the full code for the trigger?
Depending on what the trigger does, running the code in query window may not be the same as from the app!
Far away is close at hand in the images of elsewhere.
Anon.
October 19, 2014 at 5:06 am
I'm hitting this same problem using Management Studio.
In my case the SQL is trying to add new records for reference numbers which don't already exist in the target table (I've done a left join and filter for the reference on the target table being null).
Starting with an empty table I get an error (it should add around 75,000 rows).
If I add a TOP 10000 to the select and run it 8 times all 75,000 rows are added with no error.
If it was a genuine error one of those 8 runs would have failed when it hit the over length data.
Any ideas?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply