MoonDoggie
asked on
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
If you run the query from management studio, how many sets of data are returned?
ASKER
Just one. The last select in the procedure.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.