September 9, 2010 at 11:05 am
I am using SQL Server 2008 Enterprise edition SP2 trial version.
I have a table that have 5 columns all varchar having a total length upto around 40 characters.
Now I am trying to execute an t-sql script that has 3-4 insert statements and its failing with 'String or Binary data would be truncated' and execution is failed.
If I execute these statements one by one they all execute successfully with no error.
I found that if the length of t-sql batch is less than 256 (approximate) character its executing but if more than 256 its failing.
I also found that same batch (even more than 256 charcahter long) is successfully executing in SQL Server 2005.
I tried using the SET ANSI_WARNINGS OFF also but that didn't help.
I haven't found any setting to change the maximum size limit of batch.
Thanks in advance.
Subhash Chandra
September 9, 2010 at 11:22 am
This typically occurs when you have a string that overflows the storage size.
Can you post some code?
September 9, 2010 at 12:20 pm
How are you executing the batch? It wouldn't, by any chance, be dynamic sql? If it is, you might want to make sure that the variable that holds the string you're building is longer than 256 characters.
September 10, 2010 at 5:29 am
The exact code you are running would certainly help. Also have you checked for any triggers on the table - if you are inserting multiple rows in one statement and the triggers are not correctly handling that then the error may be in the trigger.
Wild guesses really without the exact code you are running...
Mike
September 10, 2010 at 5:56 am
table's script is:
CREATE TABLE [dbo].[EQ_Groups](
[EQ_GroupID] [int] NOT NULL,
[GroupType] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[GroupCode] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SortOrder] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_EQ_Groups] PRIMARY KEY CLUSTERED
(
[EQ_GroupID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Insert statements are:
INSERT INTO dbo.EQ_Groups (EQ_GroupID, GroupType, GroupCode, [Description], SortOrder) Values (30,'Type3', 'Code3', 'Desc3', 'Ord3')
INSERT INTO dbo.EQ_Groups (EQ_GroupID, GroupType, GroupCode, [Description], SortOrder) Values (31,'Type4', 'Code4', 'Desc4', 'Ord4')
If I execute these two insert statements as one batch then I get the error 'String or biinary data would be truncated' but if I execute them one by one they execute successfully.
September 10, 2010 at 10:02 am
Very odd behaviour.
When you say as one batch I assume you are highlighting both insert statements and submitting that way.
Can you put a GO between them and then run the two again? Just to see what yuou get
It almost looks like the first statement is not terminating a string in some way and the second being added to it - but it does not look obvious what is wrong...
MIke
September 10, 2010 at 10:38 am
subash: Can you please do a quick double check if you have triggers on the table? Something hiding underneath could be part of the problem.
If you change that table name to a different table name and run just that create table, and then run your two inserts, do you get the same error in the same database?
If so, we've got a really funky issue somehow. If not, we're looking at something we can't see yet.
And, if this isn't a production database (otherwise wait for a large maintenance window) DBCC CHECKDB couldn't hurt over the long run, except waste some time. If you're pressed for time DBCC CHECKTABLE can be run for just that table.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 10, 2010 at 11:21 am
My first thought on this was triggers not handling multi row, but it is clear that there are two separate inserts here so I am starting to think probably not trigger. BUT we clearly need to know if there are triggers there as there could be some weird logic under the covers.
I like the idea of changing the table name and recreating - eliminates all sorts of oddities like the wrong db, wrong schema etc. Please post the outcome and also aoutput of a select * after each insert.
Are we absolutely certain the table was created as per the create table shown? Not saying you are wrong just want to be certain so maybe posty output of sp_help on that table.
Confused but intrigued!
Mike
September 10, 2010 at 12:04 pm
Thank you for reply.
I checked and not found trigger on this table.
Table schema is exactly the same as scripted in previous mail.
The script runs successfully if I add GO between these two statements.
I suspect the maximum length limit of statement in one batch to 256 character. Because when the length of complete batch (without GO inbetween) is exceeding 256 its failing but when it becomes less than 256, its running. Is there any SSMS setting to set the maximum length of statement?
Regards,
Subhash
September 10, 2010 at 12:42 pm
Considering many of my scripts run 2k characters or more, I'd have thought I'd have ran into it if it did. I just took a breeze through tools-options and didn't see anything like this.
A truncate error usually comes from the table level on an insert. I'm boggled.
Can you remote into the server itself, open up SSMS there, and test to see if the script fails from another installation of SSMS?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 10, 2010 at 12:50 pm
I copied and pasted your code into SQL 2005. It worked fine.
Try this. Add the square brackets [] around all of your field names. See what happens
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 10, 2010 at 12:54 pm
There is definitely nothing wrong with the code...it works fine on my end without a GO in between.
This might sound silly, but are you sure you're executing this in the right database and didn't accidentally create a non-trigger version of this table? I just don't see any reason for it to fail.
September 10, 2010 at 1:14 pm
Are you executing this in SSMS or some other tool? Can you run a profiler trace when you execute this so you can see what the server is actually getting?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply