April 6, 2009 at 2:57 am
I have a strange issue with a step in a SQL job, which I can't reproduce when I run the statement in a Query window. This is the situation:
1) I have a logfile in database [A], each column has a default.
2) in Database a synonym is created for the logfile in database [A]
3) in database a view is created on synonym in (2) with some where clauses
Each stored procedure in datbase starts with logging in view (3) like this:
insert into processlog(step, companycode)
vaules ('main()','%')
set @recid = @@IDENTITY
(processlog is the view)
When I execute a SP in a query window, everything works fine, but when I execute the SP in a Job, it give the "string or binary data would be truncated" and the job fails. Why? :crazy:
Column sizes are ok (double checked).
Environment: SQL Server 2005 Enterprise, 64bit, with SP3
Wilfred
The best things in life are the simple things
April 6, 2009 at 7:09 am
That warning is pretty straight forward. Somewhere in the code a string is getting placed that's just too long.
Also, on a side note, instead of using @@IDENTITY, I'd suggest you use SCOPE_IDENTITY(). It's safer and more accurate
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 6, 2009 at 8:02 am
That warning is pretty straight forward. Somewhere in the code a string is getting placed that's just too long.
Yes, I know the meaning of this error, but why is the same statement giving this error in a Job and not in a query window???
BTW thanks for your point about @@IDENTITY, I've read the documentation and I'll change the code
Wilfred
The best things in life are the simple things
April 6, 2009 at 8:31 am
Wilfred van Dijk (4/6/2009)
Yes, I know the meaning of this error, but why is the same statement giving this error in a Job and not in a query window???
Assuming the code is 100% identical, I'm not sure why that would happen. Is there nothing before or after this bit of code?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 6, 2009 at 9:14 am
Found the bug!
In my processlog, there's a column called username, default suser_name()
It was defined as varchar(30), but it's returning nvarchar(128)
My SQLservice account is quite a long name (including the domain), causing the string length overflow of 30 characters. I'm not getting this error, because my SUSER_NAME() is less than 30 characters.
Fixed the columnwidth to nvarchar(128)
Time to go home ... :hehe:
Wilfred
The best things in life are the simple things
April 6, 2009 at 11:33 am
Excellent. Glad you tracked it down. Thanks for posting the solution here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply