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
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy