Or this...
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'TEST001')
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 ''TEST001'' 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'TEST001'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
@job_name = N'TEST001', @owner_login_name = N'sa', @description = N'Test
DB', @category_name = N'[Uncategorized (Local)]', @enabled = 1,
@notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend =
0, @notify_level_eventlog = 0, @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'TESTDB', @command = N'PUBS..sp_adduser
cgi_client
GO
PUBS..sp_adduser client_asp
GO
PUBS..sp_dropuser cgi_client
GO
PUBS..sp_dropuser client_asp
GO
northwind..sp_adduser cgi_client
GO
northwind..sp_adduser client_asp
GO
northwind..sp_dropuser cgi_client
GO
northwind..sp_dropuser client_asp
GO
', @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:
-----Original Message-----
From: [Email Address Removed] [mailto:[Email Address Removed]
Sent: Wednesday, January 29, 2003 2:59 PM
To: LazyDBA.com Discussion
Subject: RE: A true lazy question - and clarification -Correction
try this...
begin
DB1..sp_dropuser cgi_client
end
begin
DB1..sp_dropuser client_asp
end
begin
Db2..sp_dropuser cgi_client
end
begin
Db2..sp_dropuser client_asp
end
Thanks,
Lisa Greetham
Database Administrator, Corporate Internet Group
Bank One
Ken Nickels <[Email Address Removed] 03:43 PM
To
"LazyDBA.com Discussion" <[Email Address Removed] A true lazy question - and clarification -Correction
Hey now... shouldn't this sort of help be reserved for Friday
afternoons?
-----Original Message-----
From: Patrick Gallucci [mailto:[Email Address Removed] Wednesday, January 29, 2003 3:50 PM
To: LazyDBA.com Discussion
Subject: RE: A true lazy question - and clarification -Correction
Sorry
DB1..sp_dropuser cgi_client
GO
DB1..sp_dropuser client_asp
GO
Db2..sp_dropuser cgi_client
GO
Db2..sp_dropuser client_asp
GO
-----Original Message-----
From: Patrick Gallucci [mailto:[Email Address Removed] Wednesday, January 29, 2003 2:41 PM
To: LazyDBA.com Discussion
Subject: RE: A true lazy question - and clarification
DB1..sp_dropuser cgi_client
DB1..sp_dropuser client_asp
Db2..sp_dropuser cgi_client
Db2..sp_dropuser client_asp
-----Original Message-----
From: [Email Address Removed] [mailto:[Email Address Removed] Wednesday, January 29, 2003 2:23 PM
To: LazyDBA.com Discussion
Subject: RE: A true lazy question - and clarification
I don't think you can change databases in a job step. I would suggest
creating a step for each database that you need to execute code in.
Thanks,
Lisa Greetham
Database Administrator, Corporate Internet Group
Bank One
Ken Nickels <[Email Address Removed] 02:50 PM
To
"LazyDBA.com Discussion" <[Email Address Removed] A true lazy question - and clarification
Just to clariy
If I have a job step with
use DB1
go
sp_dropuser cgi_client
sp_dropuser client_asp
use db2
go
sp_dropuser cgi_client
sp_dropuser client_asp
you're saying I can replace it with...
Begin Tran
use db1
commit
sp_dropuser cgi_client
sp_dropuser client_asp
begin tran
use Db2
commit
sp_dropuser cgi_client
sp_dropuser client_asp
-----Original Message-----
From: [Email Address Removed] [mailto:[Email Address Removed] Wednesday, January 29, 2003 1:56 PM
To: Ken Nickels
Cc: LazyDBA.com Discussion
Subject: Re: A true lazy question
Just do the same thing you would have to do in a stored proc. Wrap the
statement in a begin tran and commit. instead of putting a go at the
end.
Thanks,
Lisa Greetham
Database Administrator, Corporate Internet Group
Bank One
Ken Nickels <[Email Address Removed] 01:37 PM
To
"LazyDBA.com Discussion" <[Email Address Removed] true lazy question
What do you all do when you put Isql scripts with "go" statements into
the Agent job scheduler. It usually compains about this.
Thanks
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
commands, e-mail: mssqldba-[Email Address Removed] transmission may contain information that is privileged,
confidential and/or exempt from disclosure under applicable law. If you
are not the intended recipient, you are hereby notified that any
disclosure, copying, distribution, or use of the information contained
herein (including any reliance thereon) is STRICTLY PROHIBITED. If you
received this transmission in error, please immediately contact the
sender and destroy the material in its entirety, whether in electronic
or hard copy format. Thank you.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
commands, e-mail: mssqldba-[Email Address Removed] transmission may contain information that is privileged,
confidential and/or exempt from disclosure under applicable law. If you
are not the intended recipient, you are hereby notified that any
disclosure, copying, distribution, or use of the information contained
herein (including any reliance thereon) is STRICTLY PROHIBITED. If you
received this transmission in error, please immediately contact the
sender and destroy the material in its entirety, whether in electronic
or hard copy format. Thank you.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
commands, e-mail: mssqldba-[Email Address Removed] REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
commands, e-mail: mssqldba-[Email Address Removed] REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
commands, e-mail: mssqldba-[Email Address Removed] REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
commands, e-mail: mssqldba-[Email Address Removed] transmission may contain information that is privileged,
confidential and/or exempt from disclosure under applicable law. If you
are not the intended recipient, you are hereby notified that any
disclosure, copying, distribution, or use of the information contained
herein (including any reliance thereon) is STRICTLY PROHIBITED. If you
received this transmission in error, please immediately contact the
sender and destroy the material in its entirety, whether in electronic
or hard copy format. Thank you.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page