June 13, 2012 at 8:20 am
Is there a way to view the results of a query in a stored procedure that is run by a SQL Job? I have a code that inserts records from one tabel to another and then deletes the inserted record from the original table. I want to see the number of records that was inserted and deleted to mak sure thay match
June 13, 2012 at 8:30 am
If the procedure generates an output then it will usually be stored in the job history, if not unless you have some custom logging on the tables then I dont think you can.
June 13, 2012 at 9:32 am
The job history does not record the results of the query in the SP.
Nice to hear from you again.
Thanks. 🙂
June 13, 2012 at 9:42 am
I think what I would do is alter your stored procedure so that it gives a return value of the number of rows inserted, then either insert that value into a table that you can query at your leisure, or have it e-mailed to you.
John
June 13, 2012 at 12:20 pm
ouwanogho (6/13/2012)
Is there a way to view the results of a query in a stored procedure that is run by a SQL Job? I have a code that inserts records from one tabel to another and then deletes the inserted record from the original table. I want to see the number of records that was inserted and deleted to mak sure thay match
Hi,
the following query may help you to check the status of your executed query..
The following query may help you...
Select
*
From
msdb..sysjobhistory as sysjobhistory
Join msdb..sysjobs as sysjobs on sysjobhistory.job_id=sysjobhistory.job_id
Where
Name='Your Schd. Package Name'
Order By
Run_Date Desc,
run_time Desc
Thanks
June 13, 2012 at 12:43 pm
How about deleting records from original table and inserting to another table using OUTPUT..DELETED.. INTO..
On error rollback the transaction. This approach will make sure you are deleting in one table what you are inserting in another table.
June 13, 2012 at 1:34 pm
You could trying using powershell and output results within Job step probably
http://msdn.microsoft.com/en-us/library/cc280490.aspx
Or use xp command shell .. (but that generally is turned off for security purposes-- You will have to use sp_configure to turn it on) Your SQL Server service account will need access to the folder you're trying to write to as well.
declare @Command varchar(500)
declare @sqlcount varchar(20)
select @sqlcount = COUNT(SomeID) from SomeTable
SET @Command = 'echo Results from Query count:'+@sqlcount+' > c:\temp\output.txt'
EXEC xp_cmdshell @Command
Hope this was helpful
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
June 13, 2012 at 2:26 pm
This code does not show the result of the query within the stored procedure
June 13, 2012 at 2:53 pm
What's in the job step? What code is there?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 13, 2012 at 2:58 pm
If you're trying to write out the results to a file to log... ( others may have more elegant method)
But one way is to specify an "OUTPUT" parameter in your stored sproc (that means changing your stored procedure)
Taking the Output result value... assigning it a variable - You could store the rowcount results maybe from your insertion?
Write that out using powershell or xp_cmd (though xp_cmd is not recommended method)
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
June 14, 2012 at 6:46 am
Can i get a sample powershell script that outputs the result of a stored procedure run by a sql job
June 14, 2012 at 2:03 pm
You're confused.
There is no output of the job here. You get logging from the job, or you get the output from a stored proc, but don't conflate the idea that there's something special about the job.
If you want the output of a stored proc, you have to execute it in a way that records the results, whether from SSMS or inside a job. Powershell can do that, so can SQLCMD. You can switch to a SQLCMD that stores the results, and then run that in the job.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 18, 2012 at 4:40 am
I'm not sure why Powershell is the tool of choice here.
Why don't you just alter the Proc. to log the required output to a Table?
I don't believe the Job will automatically do that for you.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply