Exception occurred in the TSQL subsystem.

Question

Post reply

Exception occurred in the TSQL subsystem.

RAD

SSChasing Mays

Points: 646

November 9, 2001 at 10:00 am

#151634

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

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

steve@dkranch.net

RAD SSChasing Mays Points: 646 More actions · Answer 2

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