Problem returning recordset to ADO when stored proc contains sp_start_job

I have a stored proc that returns a recordset to my front end ADO code.  It worked fine, then I added sp_start_job to import some data from another server.  Now no recordset is returned.  I've tried using NoCount but didnt help.  The job execute the DTS just fine.  Any ideas?  Thanks, Mike
ALTER      proc pr_Login( 
	@UserName varchar(20),
	@Password varchar(10),
	@rtnUser nchar(20) = null
	--@Result tinyint
 
) as
 
set nocount on
 
/*
Checks for correct login and returns data about the user
	and the current pay period if login successful.
*/
 
declare	@PPNo int
declare	@PPEnd datetime
declare @Cams bit
declare @strCams varchar(1)
 
select @rtnUser = UserName, @Cams = CamsUser
	from tblTCUsers
	where Username = @UserName
	and Password = @password
	and startdate <= getdate()
	and (enddate is null
	or enddate >= getdate())
--------------------------------------------------------------------------
declare @rnt int
if @Cams = 1 
	exec @rnt = msdb.dbo.sp_start_job @job_name = 'CAMSImportToDOTTime'
---------------------------------------------------------------------------
if @rtnUser is null 
	begin
	raiserror ('User Name and/or Password incorrect.',16,1)
	end
 
--Get pay period data based on today's date
select @PPNo = PPNo, @PPEnd = PPEnd 
	from tblPayPeriods 
	where PPBegin <= cast(convert(varchar(8),getdate()-1,1) as datetime) 
	and PPend >= cast(convert(varchar(8),getdate()-1,1) as datetime)
 
set nocount off
 
--Return the pay period info and employee info 
select @PPNo, @PPEnd, EmpID, EmpNo, UserTypeID, RDOShortWeek, EmpTypeID
	from tblTCUsers
	where Username = @UserName
	and Password = @password

Open in new window

LVL 1
MoonDoggieAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UnifiedISCommented:
If you run the query from management studio, how many sets of data are returned?
0
MoonDoggieAuthor Commented:
Just one.  The last select in the procedure.
0
MoonDoggieAuthor Commented:
When I run it in Query Analyzer (which is where I ran it from to answer you question), I see on the message tab there is  "Job 'CAMSImportToDOTTime' started successfully."  I wonder if that's blowing out the recordset for ADO.  
0
MoonDoggieAuthor Commented:
Added @output_flag = 0 to the end of the command to make it:

exec @rnt = msdb.dbo.sp_start_job @job_name = 'CAMSImportToDOTTime', @output_flag = 0

No more message and ADO got the recordset.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.