August 22, 2007 at 11:22 am
I'm running Win 2k3, SQL Server 2k5 SP2a. Whenever I try to work with a maint plan or sql agent job, new or old I get a string or binary would be truncated msg from the SSMS client. Anyone know how to deal with this?
Chris.
Chris.
August 22, 2007 at 11:49 am
Usually this kind of message is generated when you try to insert a value which is wider than a column. There could many reasons for that. One of the possible reasons is if your job is trying to insert some records into a table as a part of saving the log or output.
August 22, 2007 at 11:56 am
Hey Mark,
You're right but that not applicable in this case.
The message occurs when I expand Management | Maintenace Plans and then double click on the maintenance plan OR If I expand SQL Server Agent | Jobs | and double click on a SQL Agent Job.
If memory serves there was a similar problem with SQL 20005 SP2 which is why they released 2a. I've re-applied SP2A and restarted just in case but that hasn't helped. The message also occurs if I use other SSMS clients on other machines to connect to this server.
Chris.
Chris.
August 23, 2007 at 6:28 am
More info - problem (possible bug) found.
There appears to be a bug in SSMS. I profiled clicking on the maintenance plan. Here's the code SSMS generated:
/* Snippet Start */
create table #tmp_sp_help_jobserver
(server_id int null, server_name nvarchar(30) null,
enlist_date datetime null, last_poll_date datetime null,
last_run_date int null, last_run_time int null, last_run_duration int null,
last_run_outcome tinyint null, last_outcome_message nvarchar(1024) null,
job_id uniqueidentifier null)
declare @job_id uniqueidentifier
declare crs cursor local fast_forward
for ( SELECT
sv.job_id AS [JobID]
FROM
msdb.dbo.sysjobs_view AS sv
WHERE
(sv.name=N'System DB Maint.Subplan_1' and sv.category_id=N'3') )
open crs
fetch crs into @job_id
while @@fetch_status >= 0
begin
insert into #tmp_sp_help_jobserver(server_id, server_name, enlist_date, last_poll_date, last_run_date, last_run_time, last_run_duration, last_run_outcome, last_outcome_message)
exec msdb.dbo.sp_help_jobserver @job_id = @job_id, @show_last_run_details = 1
update #tmp_sp_help_jobserver set job_id = @job_id where job_id is null
fetch crs into @job_id
end
close crs
deallocate crs
SELECT
tshj.server_name AS [ServerName],
tshj.server_id AS [ID],
tshj.enlist_date AS [EnlistDate],
tshj.last_poll_date AS [LastPollDate],
null AS [LastRunDate],
tshj.last_run_duration AS [LastRunDuration],
tshj.last_run_outcome AS [LastRunOutcome],
tshj.last_outcome_message AS [LastOutcomeMessage],
tshj.last_run_date AS [LastRunDateInt],
tshj.last_run_time AS [LastRunTimeInt]
FROM
msdb.dbo.sysjobs_view AS sv
INNER JOIN #tmp_sp_help_jobserver AS tshj ON tshj.job_id=sv.job_id
WHERE
(sv.name=N'System DB Maint.Subplan_1' and sv.category_id=N'3')
drop table #tmp_sp_help_jobserver
/* Snippet End */
If I run this code I get:
Msg 8152, Level 16, State 13, Procedure sp_help_jobserver, Line 25
String or binary data would be truncated.
(0 row(s) affected)
(0 row(s) affected)
which is what the SSMS client is picking up. It looks like #tmp_sp_help_jobserver isn't being defined properly.
Anyone know of a fix?
Chris.
Chris.
August 23, 2007 at 7:43 am
try executing the following and report back please.
select * from msdb.dbo.sysjobs_view
August 23, 2007 at 7:57 am
That query works and returns two records which are the two jobs I have listed under SQL Agent.
Do you want the records?
Chris.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply