Msg 8152, Sev 16: String or binary data would be truncated. [SQLSTATE 22001]
-
Hi,
I have a SQL Server Agent Job which runs a stored procedure. In the past this has run fine, but then stopped running and started failing with the following error msg:
Msg 8152, Sev 16: String or binary data would be truncated. [SQLSTATE 22001]
Msg 3621, Sev 16: The statement has been terminated. [SQLSTATE 01000]
So I have tried to run the SP in Query Analyzer to get more information and it runs OK. I have repeated this process many times and have a 100% success rate running it in QA and a 100% failure rate running it via SQL Server Agent. I have checked / changed security settings and this does not appear to make a difference.
I have searched the web and found a number of people with the same problem but no solutions? Has anyone else experienced this issue and found a resolution (Other than running the job manually every day
)Thanks
-
That error message indicates that your trying to put a value into a table that exceeds the data type, and or length.
For example your trying to stuff a 60 character string into a table defined with a varchar(50).
Is the agent job calling a dts package, or doing an insert into a table that your not doing when executing the procedure manually?
-
The SP is only performing insert and update statements and is exactly the same when run in Query Analyzer.
If it was a true truncation error would I not get the same truncation result in QA?
At the minute I am running a trace to find out exactly which statement in the SP is causing the truncation error...takes an hour to run so hopefully will have more information soon. Once I have this I am going to run that statment in Query Analyser and then go and check the results. I have a feeling my updates are being truncated when run in QA but the error msg is not being generated.
-
Wierd,
if the same parameters are run from QA and when it was run in agent, I'd expect the same error.
QA should capture the same error as agent.
you still may have to go through and make sure the lenght of any data that is going to be inserted does not exceed the size of the field in the table. Its pain.
-
Query Analyser is not reporting the truncation error for this statement!
insert tblDbUsageAudit (DbName,nt_username,program_name,loginame,login_time,net_address)
select p.program_name as program_name,d.name as DbName,p.nt_username as nt_username
,p.loginame as loginame,p.login_time as login_time,p.net_address as net_address
from master..sysprocesses p,master..sysdatabases d where p.spid=@@spid and p.dbid=d.dbid
Select @@Error
-- Table definition below...
CREATE TABLE [tblDbUsageAudit] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[CreatedDate] [datetime] NULL CONSTRAINT [DF__tblDbUsag__Creat__0016B11E] DEFAULT (getdate()),
[hostname] [varchar] (64) NULL CONSTRAINT [DF__tblDbUsag__hostn__010AD557] DEFAULT (@@servername),
[DbName] [varchar] (64) NULL ,
[nt_username] [varchar] (64) NULL ,
[program_name] [varchar] (512) NULL ,
[loginame] [varchar] (64) NULL ,
[login_time] [varchar] (64) NULL ,
[net_address] [varchar] (64) NULL
) ON [PRIMARY]
GO
-- DBNAme and nt_username are too small...
Any ideas why?
-
Hi Andrew,
It looks like you have ansi_warnings turned off in query analyzer.
Check the query analyzer connection properties located in the options dialogue (Tools>Options>Connection Properties tab)
-
I'm experiencing the same problem. My ANSI NULLS parameter is checked 'on'. ANy other ideas?
-
As stated in the discussions please check all your column types to see if values declared are greater than of equal to the value in the table that you are inserting.
Example:
Common error : Column_Name is varchar(50) but you are trying to insert into that table a column whose length is 60
"-=Still Learning=-"
Lester Policarpio
-
I've checked the data and there is no discrepancy like that in the column length. And if I exec the script in Query Analyzer, it's just fine. I've even added ''where table.column_name is not null' wherever it was applicable in the WHERE statement. I wish the error message gave more info!
-
Hi,
Able to reproduce error and just performing some analysis and will update you shortly.
Thanks,
Phillip Cox
MCITP - Database Administrator
-
Hi,
When executing via QA, it will work as the program name is set to "Microsoft SQL Server Mangement Studio - Query", but when running from SQL Server Agent the program name is similar to "SQLAgent - TSQL JobStep (Job0xxxxxxxxxxxxxxxxxx), which is to big for DBname field. Looks like the select statement mapping to the insert is incorrect, as the program field is able to store this type of string data.
Cheers,
Phillip Cox
MCITP - Database Administrator
-
I have a similar issue.
I have a stored procedure which loops thru about 180 tables and inserts the data into one table used for reporting purposes. One field is a ntext field. If I execute the stored procedure from query analyzer the entire contents of the ntext field is available. When I run as scheduled task (every 1/2 hour) from sql server agent, the data in the ntext field is truncated. I do not receive an error in sql server agent - just truncated data.
-
How to tropubleshoot exactly for which column the data exceeded the defined length?
-
I had this same problem where I always get the "truncation" message when executing stored procedures in SQL Server Agent jobs but never getting the message when I run it in Management Studio.
After trying a number of desperate measures I commented out some "Print" statements I had and the error messages went away.
-
I was able to reproduce this in a SQL2000 environment. It can be fixed by changing the order of the columns selected in the first line of the select statement FROM:
select p.program_name as program_name,d.name as DbName,p.nt_username as nt_username
TO:
select d.name as DbName,p.nt_username as nt_username,p.program_name as program_name
The order of the columns in the select statement needs to match the sequence of the columns in the table.
The original select statement executes without an error in QA but the data in the audit table is not correct. The program_name is in the DbName column, the database name is in the nt_username column, etc.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply