SQL Job Truncation Error
-
Updating my local [Event] table with the data from a source SQL box [Event] table. Just need to insert missing records. The table schema is identical in the source and destination tables. The following SQL works fine in SSMS. The job fails with a string or binary data truncation error, only when executed via SQL job. sa owns the job, it should be run under the system context. Wrapped it up in a stored procedure, still no love.
insert into event (event_id, event_headliner_id, venue_id, event_name, event_date, event_time, active, exchange_modified_dt, merged_event_id, update_count)
select
e.event_id, e.event_headliner_id, e.venue_id, e.event_name, e.event_date, e.event_time, e.active, e.exchange_modified_dt, e.merged_event_id, e.update_count
from
(select * from openquery(sql1, 'select * from sourceDB..event')) e
join Venue v on e.Venue_ID = v.Venue_ID
join Event_Headliner h on e.Event_Headliner_ID = h.Event_Headliner_ID
join Category c on h.Cat_ID = c.Cat_ID
left join Event q on e.event_id = q.event_id
where q.event_id is null
In a job this yields Error 8152 String or binary data would be truncated while in SSMS this works fine.
Thanks in advance
John
-
A trigger that did an insert into a logging table was the cause of the problem. Anyone know why the job would have a problem with a trigger when SSMS did not?
-
different security context...possibly the trigger behaves differently for different users?
-
Deuce (8/7/2014)
A trigger that did an insert into a logging table was the cause of the problem. Anyone know why the job would have a problem with a trigger when SSMS did not?Can I hazard a guess:
Are you inserting APP_NAME() into the logging table? Is the application name defined as 50 characters in your logging table?
APP_NAME() in SSMS returns "Microsoft SQL Server Management Studio - Query" (46 characters)
APP_NAME() in Agent returns "SQLAgent - TSQL JobStep (Job 0x4C5CB2CA2CF2C943B072197BED50C058 : Step 1)" (73 characters)
-
Nice!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply