Skip to content

eConnect Procedure Fails – String or Binary Data Would Be Truncated

December 6, 2012

Recently I was working on some customized GP integrations, specifically, around contracts (SVC tables). When I created the procedure in T-SQL, it worked great, once I created a job to do some work on a daily basis, it started failing with error “String or binary data would be truncated”. Well I know what that means, but the question is, why? Turns out I had run into this once before when executing from within the query window in SQL server. As it turns out, there is a column called CRUSERID in Great Plains that is defined as a char(15). My login name was over that. GP was using SUSER_NAME to get the logged in user to pass that information to a table I wasn’t even directly accessing. In this case it was SVC00604.

SQL profiler was no help as the proc was called from a trigger and is encrypted within Great Plains. I ended up stepping through every line of the create contract code and finally realized it was the user id that was causing problems. Mine happened to be domainname\jthompson and it exceeded the 15 char limit.

In the case of the SQL job, the SQL Server Agent account was being used. At this client, it was NT AUTHORITY\SYSTEM, which was also too long. We changed the sql agent to login with a domain account that was <15 chars total, best practice anyway, and it solved the problem! To be clear, the entire total login length (domain\login) has to be < 15 chars.

So if your code is working fine from your dev environment and starts failing with this error once it’s scheduled as a job in SQL server, you’ll know where to look! I hope this helps and saves you all the time I spent trying to debug this curious error.

Advertisements

From → SQL Server

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: