April 2, 2003 at 12:21 am
In 6.5 i could raiseerror and the message will be logged into the task history. How do i accomplished similar function?
April 2, 2003 at 12:32 am
I do this with a print statement in a stored procedure.
Jeremy
April 2, 2003 at 12:45 am
it doesn't work. I tried that. When i check the job history, details, it doesn't show anything..
April 2, 2003 at 12:46 am
In the job history, tick the box in the top right 'Show step details'. The print statements and in the detail for each step in the job.
Jeremy
April 2, 2003 at 12:53 am
no it's not there, what do you mean by print statements? I don't see any option to display print statements?
I have even checked sysjobhistory, there is nothing there
April 2, 2003 at 7:48 am
This is the script of a job which shows print statements at work:
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Print a message')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Print a message'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Print a message'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Print a message', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Print Test', @command = N'Print ''Text of your message''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
If you copy the script into Query Analyser and execute it will create a job called Print Test. If you execute the job, view the job history and expand it with Show step details, you will see the history for Step 1 as:
Executed as user: <user_name>. Text of your message [SQLSTATE 01000] (Message 0). The step succeeded.
Jeremy
April 2, 2003 at 1:57 pm
can anyone use this to pratice in query analyzer or is it just for him? does it create an action after it runs? like to you get to click something or enter something? if so i thought sql only give information.
April 2, 2003 at 6:56 pm
Hi Jeremy, Got it already. It works however i have a few issues.
Here's what is happening.
I have a job that execute a stored procedure. In this SP, i am constructing a line of IDs that i updated using the stored procedure. So i want to print out the IDS that i updated.
After seeing what you have replied last, i created another step (step 2) that will execute after successfully executed step 1 (the above). In this step 2, i put a Print 'hello'.
In the stored procedure of step 1, after successfully run my updates, i do a
sp_update_jobstep @job_name = [jobname], @step_id = 2, @command = [mystring]
in this case mystring have been constructed to say Print blah blah blah..
I started the job. It ran successfully, however it never update my step 2, it keeps printing Hello..
Any ideas? I suspect i can't update step 2 in middle of a "job transaction", because if i run the stored procedure directly without activating the job, it's updating step 2.
April 2, 2003 at 7:34 pm
Start Enterprise Manager.
Go Management -> Jobs.
Right click on the job and select properties.
Select Steps and Edit the step.
Select the advance Tab and click 'Append output to job history on'.
Click on Apply and OK.
Click on Apply and OK.
Now excute the job and view the job history with 'Show Steps Details' checked.
April 2, 2003 at 7:42 pm
Finally got it working!! Thanks all!! especially Jeremy. I found out the problem.
When i was running on query analyzer, my setting on the connection is set_concat_null_yields_null is off, And so happen i forgot to "initalize" one of the string variable. When i am running with the job, the @command = [mystring] was null therefore it didn't get updated!
now everything solved!! thanks a lot
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply