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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply