November 9, 2001 at 10:00 am
I have 2 step job. 1st is to run sproc which produces several outputs:
CREATE PROCEDURE spProcName
as
SET NOCOUNT ON
declare @hr int
declare @err int
declare @totalcount int
SELECT 'This is 1st result'
SELECT ' '
select 'TimeZone'= rtrim(s.TimeZoneDesc), 'State'=rtrim(s.State), 'CoCode'= p.cocode, count(*) as Records
from States s (NOLOCK)
inner join tblProducts p (NOLOCK) on s.stateAbbr= p.costate and AsOfDate>= convert(datetime,convert(char,datepart(yyyy,getdate()))+'/'+convert(char,datepart(mm,getdate()))+'/'+convert(char,datepart(dd,getdate())))
group by s.TimeZone, s.TimeZoneDesc, s.State, p.cocode
order by s.TimeZone, s.State, p.cocode
compute Count(p.cocode)
if @@error <> 0
begin
print 'error after 1st '
select @err = 1
end
SELECT 'This is 2nd result'
SELECT ' '
select 'TimeZone'=rtrim(s.TimeZoneDesc), 'State'=u.CoState, 'CoCode'=u.cocode, count(*) as Records
from States s (NOLOCK)
inner join Results u (NOLOCK) on s.stateAbbr= u.Costate
group by s.TimeZone, s.TimeZoneDesc, u.CoState, u.cocode
having u.Costate is not null
order by s.TimeZone, u.CoState, u.cocode
compute Count(u.cocode)
if @@error <> 0
begin
PRINT 'error after 2nd '
select @err = 1
end
select @totalcount= count(*) from Results (NOLOCK)
SELECT 'Aproximate Pending time is ' + convert(varchar(30),(@totalcount*0.7)/60) + ' minutes'
if @@error <> 0
begin
PRINT 'error after 3rd '
select @err = 1
end
if @err = 1 RETURN 1 else RETURN 0
SET NOCOUNT OFF
It's set to produce TXT output file with Overwrite.
Second step is to send e-mail with TXT file produced in Step 1 using CDO from sproc.
I have two issues:
1. When I run 1st sproc in QA, it runs without problems, but when I run it as a part of job, errors out EVERY OTHER time with message:
"An exception occurred in the TSQL subsystem. The step failed."
2. Second issue: Output file is created in UNICODE, or some other code, so e-mail client sees only ÿþJ instead of 6000 characters of text file if client set to see attachments Inline.
Why I think it's UNICODE? I tried to sent other files, and UNICODE one shows as ÿþJ every time
Please help,
Michael
November 9, 2001 at 11:08 am
Did you leave out some code? I don't see anywhere that the first item produces a text file. How is it set to run?
2. Not sure. Can you post the code from the 2nd item?
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 9, 2001 at 11:31 am
I specify it on Advanced Tab, Transact-SQL script (TSQL) Command Options. This step looks looks in script like:
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Run Query', @command = N'exec spSprocName', @database_name = N'DataBase', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 4, @retry_attempts = 1, @retry_interval = 1, @output_file_name = N'E:\MSSQL7\LOG\Run_UpdateStatus.txt', @on_success_step_id = 3, @on_success_action = 4, @on_fail_step_id = 0, @on_fail_action = 2
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply