April 29, 2016 at 5:32 am
I hope someone can assist me. I'd like to know where I can find the code behind the SQL engine's "Script AS" choice in the right-click GUI menu. The thing you get when you right-click a job, proc, etc. and go to Script X As and get to choose DROP, CREATE, or DROP and CREATE.
I'd like to build my own version of the DROP scripts for the jobs that use @job_name instead of @job_id. Because job_ids vary from environment to environment, I don't want to use that in the sp_delete_job statement. There are a lot of jobs to move from SQL 2k8 to SQL 2012 and it is taking me too much time to go through each one and update the job_id with the job name. Cut-n-paste is easy, yeah, but there are so many other tasks on my plate that I don't have the time for it.
Plus my hands are starting to hurt.
So any thoughts on how I can turn a
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'MyJobName')
EXEC msdb.dbo.sp_delete_job @job_id=N'67a26c58-8341-42a7-8773-b09ee0aef10f', @delete_unused_schedule=1
into a
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'MyJobName')
EXEC msdb.dbo.sp_delete_job @job_name=N'MyJobName', @delete_unused_schedule=1
for lots of jobs at the same time?
April 29, 2016 at 5:56 am
Brandie Tarvin (4/29/2016)
I hope someone can assist me. I'd like to know where I can find the code behind the SQL engine's "Script AS" choice in the right-click GUI menu. The thing you get when you right-click a job, proc, etc. and go to Script X As and get to choose DROP, CREATE, or DROP and CREATE.I'd like to build my own version of the DROP scripts for the jobs that use @job_name instead of @job_id. Because job_ids vary from environment to environment, I don't want to use that in the sp_delete_job statement. There are a lot of jobs to move from SQL 2k8 to SQL 2012 and it is taking me too much time to go through each one and update the job_id with the job name. Cut-n-paste is easy, yeah, but there are so many other tasks on my plate that I don't have the time for it.
Plus my hands are starting to hurt.
So any thoughts on how I can turn a
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'MyJobName')
EXEC msdb.dbo.sp_delete_job @job_id=N'67a26c58-8341-42a7-8773-b09ee0aef10f', @delete_unused_schedule=1
into a
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'MyJobName')
EXEC msdb.dbo.sp_delete_job @job_name=N'MyJobName', @delete_unused_schedule=1
for lots of jobs at the same time?
One idea is to create the scripts as usual and then do a REPLACE using the job name in msdb.dbo.sysjobs.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 29, 2016 at 6:34 am
Quick thought, you can pass null as job_id and the job name, sp_delete_job uses sp_verify_job_identifiers to find the job_id if it isn't passed to the proc. Behind the scene it queries msdb.dbo.sysjobs_view to get the job_id.
😎
April 29, 2016 at 6:38 am
just the drops scripts are pretty easy, you can just build the statements from sysjobs;
select
'IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N''' + name + ''')
EXEC msdb.dbo.sp_delete_job @job_name=N''' + name + ''', @delete_unused_schedule=1' As cmd,
*
from msdb.dbo.sysjobs
there's lots of examples in the script center for scripting out jobs, without reinventing the wheel; i would start with an example there and bullet proof them with if exists()/drop/create and stuff, and use one of those as the base script/skeleton.
http://www.sqlservercentral.com/Search/?q=script+jobs&s=30&t=s&sort=relevance
this one specifically scripts as @job_name:
http://www.sqlservercentral.com/scripts/generate+jobs+scipts/68105/
Lowell
April 29, 2016 at 6:55 am
Lowell (4/29/2016)
just the drops scripts are pretty easy, you can just build the statements from sysjobs;
select
'IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N''' + name + ''')
EXEC msdb.dbo.sp_delete_job @job_name=N''' + name + ''', @delete_unused_schedule=1' As cmd,
*
from msdb.dbo.sysjobs
there's lots of examples in the script center for scripting out jobs, without reinventing the wheel; i would start with an example there and bullet proof them with if exists()/drop/create and stuff, and use one of those as the base script/skeleton.
http://www.sqlservercentral.com/Search/?q=script+jobs&s=30&t=s&sort=relevance
this one specifically scripts as @job_name:
http://www.sqlservercentral.com/scripts/generate+jobs+scipts/68105/
Thank you, Lowell. I'll take a look at those links.
April 29, 2016 at 7:07 am
Just to be sure, I like to use the quotename function.
SELECT 'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs_view WHERE name = N' + QUOTENAME( name, '''') + ')
EXEC msdb.dbo.sp_delete_job @job_name=N' + QUOTENAME( name, '''') + ', @delete_unused_schedule=1;'
FROM msdb.dbo.sysjobs_view
And I spent too much time before posting this. 😀
April 29, 2016 at 7:08 am
Lowell's code in a slightly different flavour
😎
DECLARE @DROP_JOP_TEMPLATE NVARCHAR(MAX) = '
-- {{@JOB_NAME}}
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N''{{@JOB_NAME}}'')
EXEC msdb.dbo.sp_delete_job @job_name=N''{{@JOB_NAME}}'', @delete_unused_schedule=1;
';
SELECT
REPLACE(@DROP_JOP_TEMPLATE ,N'{{@JOB_NAME}}',MSSJV.name)
from msdb.dbo.sysjobs_view MSSJV
FOR XML PATH(''),TYPE;
April 29, 2016 at 7:15 am
HA! Even SSMS agrees I have too many jobs. Using just a part of the code from Lowell's second link (to create the drop job stuff), I got
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
I'll see if "results to text" works. Thank you everyone for what you've posted and I'll try variations on the theme until I can get SSMS to cooperate with me. Really don't want to have to send to a file, but will if I have to.
April 29, 2016 at 7:37 am
Brandie Tarvin (4/29/2016)
HA! Even SSMS agrees I have too many jobs. Using just a part of the code from Lowell's second link (to create the drop job stuff), I gotAn error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
I'll see if "results to text" works. Thank you everyone for what you've posted and I'll try variations on the theme until I can get SSMS to cooperate with me. Really don't want to have to send to a file, but will if I have to.
Convert the query to a view and use bcp. Or use the import/export wizard or SSIS. Or install more memory.
April 29, 2016 at 7:39 am
Luis Cazares (4/29/2016)
Just to be sure, I like to use the quotename function.
SELECT 'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs_view WHERE name = N' + QUOTENAME( name, '''') + ')
EXEC msdb.dbo.sp_delete_job @job_name=N' + QUOTENAME( name, '''') + ', @delete_unused_schedule=1;'
FROM msdb.dbo.sysjobs_view
And I spent too much time before posting this. 😀
And yet, this is the one I got to work without SSMS deciding to fail out of memory or shutdown and restart.
Thanks. @=)
EDIT: GAH. Except it didn't. The exception error is at the end of the file too. This is getting frustrating. And now I just realized that your code repeats the same job name over and over again... Whoops.
April 29, 2016 at 7:44 am
Out of curiosity, how many jobs do you have?
😎
April 29, 2016 at 7:50 am
Luis Cazares (4/29/2016)
Or install more memory.
Umm, that won't work. SSMS has the memory limitation. Not my desktop.
Per Microsoft:
Note SSMS is a 32-bit process. Therefore, it is limited to 2 GB of memory. SSMS imposes an artificial limit on how much text that can be displayed per database field in the results window. This limit is 64 KB in "Grid" mode and 8 KB in "Text" mode. If the result set is too large, the memory that is required to display the query results may surpass the 2 GB limit of the SSMS process. Therefore, a large result set can cause the error that is mentioned in the "Symptoms" section.
Sigh. The problem with all of these solutions (I just realized) is that it doesn't allow me to pick around the jobs I don't want to script out. That's kind of why I wanted to use the right-click version.
Now I have to spend time hunting through the code (when I get it working) to remove the DROPs that I don't want to drop. I suppose that's less time then the copy-n-paste stuff, but I wanted to make this faster.
April 29, 2016 at 7:51 am
Eirikur Eiriksson (4/29/2016)
Out of curiosity, how many jobs do you have?😎
I have not counted. I have to pull them from several servers down to one or two scripts.
April 29, 2016 at 7:59 am
Okay. This is what I ended up with in order to make everything readable and easier to find the jobs I need to delete from the script. Also, I ran results to text so I could just do a giant copy-n-paste to my SSMS screen instead of copying the individual query strings from a grid (which I usually use for my results).
SELECT
'IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N''' + name
+ ''') EXEC msdb.dbo.sp_delete_job @job_name=N''' + name + ''',
@delete_unused_schedule=1;'
+ CHAR(13) + 'GO' + CHAR(13)
FROM msdb.dbo.sysjobs
WHERE enabled = 1
ORDER BY name;
Thank you all for your assistance. This will cut a good chunk of time off my scripting process and allow me to get on to solving other issues that are less cosmetic and more of a "I need a DBA NOW" flavor.
EDIT: It appears the system memory issue was due to a loop for pulling the same job name over and over and over again. I don't know how that happened.
April 29, 2016 at 9:40 am
Brandie Tarvin (4/29/2016)
Luis Cazares (4/29/2016)
Or install more memory.Umm, that won't work. SSMS has the memory limitation. Not my desktop.
Per Microsoft:
Note SSMS is a 32-bit process. Therefore, it is limited to 2 GB of memory. SSMS imposes an artificial limit on how much text that can be displayed per database field in the results window. This limit is 64 KB in "Grid" mode and 8 KB in "Text" mode. If the result set is too large, the memory that is required to display the query results may surpass the 2 GB limit of the SSMS process. Therefore, a large result set can cause the error that is mentioned in the "Symptoms" section.
Sigh. The problem with all of these solutions (I just realized) is that it doesn't allow me to pick around the jobs I don't want to script out. That's kind of why I wanted to use the right-click version.
Now I have to spend time hunting through the code (when I get it working) to remove the DROPs that I don't want to drop. I suppose that's less time then the copy-n-paste stuff, but I wanted to make this faster.
SQLCMD is the alternative at that point. I have had massively long scripts that I could read and edit in SSMS but would not run. Running them using SQLCMD, however, works.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply