Solved

Problem returning recordset to ADO when stored proc contains sp_start_job

Posted on 2009-07-06
4
638 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:MoonDoggie
  • 3
4 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 24788417
If you run the query from management studio, how many sets of data are returned?
0
 
LVL 1

Author Comment

by:MoonDoggie
ID: 24789265
Just one.  The last select in the procedure.
0
 
LVL 1

Author Comment

by:MoonDoggie
ID: 24789338
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
 
LVL 1

Accepted Solution

by:
MoonDoggie earned 0 total points
ID: 24789433
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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
cannot connect to sqlserver 8 25
Sql server function help 15 27
SQL Update trigger 5 16
How to construct an if else statement from existing code 6 19
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now