Prepping for SQL Server Update
-
I came up with this sql to discover jobs and steps in those jobs that use a SSIS package (because our DBA "isn't a programmer")...
DECLARE @serverPath = 'MyServerPath'
;WITH cte AS
(
SELECT b.job_id AS JobID,
b.name AS JobName,
b.[enabled] AS JobEnabled,
a.step_id AS StepNumber,
CASE WHEN RTRIM(LEFT(UPPER(a.command),5)) LIKE '/FILE' THEN 'File System' ELSE 'Package Store' END AS Location,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(UPPER(a.command), '/DTS ','')
,'/FILE ','')
,'/SQL ','')
, ' /CHECKPOINTING OFF', '')
,' /REPORTING E', '')
,' /X86', '')
, ' /SERVER '+@serverPath, '')
, '"', '') AS PackagePath
FROM msdb.dbo.sysjobsteps as a
INNER JOIN msdb.dbo.sysjobs as b on b.job_id = a.job_id
WHERE a.subsystem = 'SSIS' AND a.command NOT LIKE '/serv%'
)
SELECT * FROM cte
ORDER BY JobEnabled DESC, Location, JobName, StepNumber".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013Odd, as long as there are some standards setup and working. Just use [Copy-DbaAgentJob – dbatools](https://dbatools.io/functions/copy-dbaagentjob/)
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
-
Odd, as long as there are some standards setup and working. Just use [Copy-DbaAgentJob – dbatools](https://dbatools.io/functions/copy-dbaagentjob/)
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
Nothing is ever "easy" on a DoD installation... For instance, I have a MCSA for SQL Server (2012), but I'm not allowed to be a DBA on our department database server. My Active Directory account can't be used to run a job - the DBA has to change ownership to a service account, and if I need to modify the job, he has to toggle ownership back to me, and which I'm done, I have to request that he toggle it back to the service account. Initially, I could create stored procs, but didn't have permission to execute them (that took a week to iron out). Other chaos has ensued.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
I came up with this sql to discover jobs and steps in those jobs that use a SSIS package (because our DBA "isn't a programmer")...
DECLARE @serverPath = 'MyServerPath'
;WITH cte AS
(
SELECT b.job_id AS JobID,
b.name AS JobName,
b.[enabled] AS JobEnabled,
a.step_id AS StepNumber,
CASE WHEN RTRIM(LEFT(UPPER(a.command),5)) LIKE '/FILE' THEN 'File System' ELSE 'Package Store' END AS Location,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(UPPER(a.command), '/DTS ','')
,'/FILE ','')
,'/SQL ','')
, ' /CHECKPOINTING OFF', '')
,' /REPORTING E', '')
,' /X86', '')
, ' /SERVER '+@serverPath, '')
, '"', '') AS PackagePath
FROM msdb.dbo.sysjobsteps as a
INNER JOIN msdb.dbo.sysjobs as b on b.job_id = a.job_id
WHERE a.subsystem = 'SSIS' AND a.command NOT LIKE '/serv%'
)
SELECT * FROM cte
ORDER BY JobEnabled DESC, Location, JobName, StepNumber".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013Apparently your "non-programmer" DBA can't write queries either. What does he do? Create empty databases, and swap ownership and permissions on objects?
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
Apparently your "non-programmer" DBA can't write queries either. What does he do? Create empty databases, and swap ownership and permissions on objects?
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave KreskowiakDave Kreskowiak wrote:
What does he do? Create empty databases, and swap ownership and permissions on objects?
I've never seen him create a database. All he does that I can verify is that he tells me when I can't do something. The latest dust-up involves installing WinSCP on my local box so I can test data retrieval via FTP. It can only be installed on a server, evidently, so I I'm flying blind when I create a new job that performs a FTP transfer...
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
Nothing is ever "easy" on a DoD installation... For instance, I have a MCSA for SQL Server (2012), but I'm not allowed to be a DBA on our department database server. My Active Directory account can't be used to run a job - the DBA has to change ownership to a service account, and if I need to modify the job, he has to toggle ownership back to me, and which I'm done, I have to request that he toggle it back to the service account. Initially, I could create stored procs, but didn't have permission to execute them (that took a week to iron out). Other chaos has ensued.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013I will say this, someone in MS needs to be kicked for the way the Agent permissions work. Its a mess, I loathe the way they are structured. I am to to the point where I've started telling management we need need a better way to have jobs run. As far as running a stored procedure, umm thats easy. GRANT EXECUTE ON Schema::dbo TO [user or group]; GRANT VIEW DEFINITION TO [user or group];
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
-
I will say this, someone in MS needs to be kicked for the way the Agent permissions work. Its a mess, I loathe the way they are structured. I am to to the point where I've started telling management we need need a better way to have jobs run. As far as running a stored procedure, umm thats easy. GRANT EXECUTE ON Schema::dbo TO [user or group]; GRANT VIEW DEFINITION TO [user or group];
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
Take a look at this article series. I wrote an agent utility for SQL express, but it's entirely usable for SQL Server enterprise. SQLXAgent - Jobs for SQL Express - Part 1 of 6[^] Because it's written for SQL Express, there are some things that aren't supported, but it's fairly full featured.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
Dave Kreskowiak wrote:
What does he do? Create empty databases, and swap ownership and permissions on objects?
I've never seen him create a database. All he does that I can verify is that he tells me when I can't do something. The latest dust-up involves installing WinSCP on my local box so I can test data retrieval via FTP. It can only be installed on a server, evidently, so I I'm flying blind when I create a new job that performs a FTP transfer...
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013I feel your pain. When I was at CBP, we were all told "Work smarter, not harder" and in the very next breath, "You cannot write code for or script anything". A massive contradiction in a single paragraph. :wtf:
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
I came up with this sql to discover jobs and steps in those jobs that use a SSIS package (because our DBA "isn't a programmer")...
DECLARE @serverPath = 'MyServerPath'
;WITH cte AS
(
SELECT b.job_id AS JobID,
b.name AS JobName,
b.[enabled] AS JobEnabled,
a.step_id AS StepNumber,
CASE WHEN RTRIM(LEFT(UPPER(a.command),5)) LIKE '/FILE' THEN 'File System' ELSE 'Package Store' END AS Location,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(UPPER(a.command), '/DTS ','')
,'/FILE ','')
,'/SQL ','')
, ' /CHECKPOINTING OFF', '')
,' /REPORTING E', '')
,' /X86', '')
, ' /SERVER '+@serverPath, '')
, '"', '') AS PackagePath
FROM msdb.dbo.sysjobsteps as a
INNER JOIN msdb.dbo.sysjobs as b on b.job_id = a.job_id
WHERE a.subsystem = 'SSIS' AND a.command NOT LIKE '/serv%'
)
SELECT * FROM cte
ORDER BY JobEnabled DESC, Location, JobName, StepNumber".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013I had a DBA call me last week asking if we had a migration tool as they are moving almost a dozen years (single db per fiscal year) of databases from sql 2008 to 2014. I sent him a couple of well documented VBS files with a ReadMe.txt that explains how to use them. It may be old school and crude, but it works really well! One of these days, I really need to learn PS. :laugh:
"Go forth into the source" - Neal Morse
-
I came up with this sql to discover jobs and steps in those jobs that use a SSIS package (because our DBA "isn't a programmer")...
DECLARE @serverPath = 'MyServerPath'
;WITH cte AS
(
SELECT b.job_id AS JobID,
b.name AS JobName,
b.[enabled] AS JobEnabled,
a.step_id AS StepNumber,
CASE WHEN RTRIM(LEFT(UPPER(a.command),5)) LIKE '/FILE' THEN 'File System' ELSE 'Package Store' END AS Location,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(UPPER(a.command), '/DTS ','')
,'/FILE ','')
,'/SQL ','')
, ' /CHECKPOINTING OFF', '')
,' /REPORTING E', '')
,' /X86', '')
, ' /SERVER '+@serverPath, '')
, '"', '') AS PackagePath
FROM msdb.dbo.sysjobsteps as a
INNER JOIN msdb.dbo.sysjobs as b on b.job_id = a.job_id
WHERE a.subsystem = 'SSIS' AND a.command NOT LIKE '/serv%'
)
SELECT * FROM cte
ORDER BY JobEnabled DESC, Location, JobName, StepNumber".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
I came up with this sql to discover jobs and steps in those jobs that use a SSIS package (because our DBA "isn't a programmer")...
DECLARE @serverPath = 'MyServerPath'
;WITH cte AS
(
SELECT b.job_id AS JobID,
b.name AS JobName,
b.[enabled] AS JobEnabled,
a.step_id AS StepNumber,
CASE WHEN RTRIM(LEFT(UPPER(a.command),5)) LIKE '/FILE' THEN 'File System' ELSE 'Package Store' END AS Location,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(UPPER(a.command), '/DTS ','')
,'/FILE ','')
,'/SQL ','')
, ' /CHECKPOINTING OFF', '')
,' /REPORTING E', '')
,' /X86', '')
, ' /SERVER '+@serverPath, '')
, '"', '') AS PackagePath
FROM msdb.dbo.sysjobsteps as a
INNER JOIN msdb.dbo.sysjobs as b on b.job_id = a.job_id
WHERE a.subsystem = 'SSIS' AND a.command NOT LIKE '/serv%'
)
SELECT * FROM cte
ORDER BY JobEnabled DESC, Location, JobName, StepNumber".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
That is a whole lot of replace statements; yikes! :-D
That was the only way I could come up with to get just the path/filename from the command line value. We don't have so many jobs that it's burdensome to run, and I only needed to do it once.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
Apparently your "non-programmer" DBA can't write queries either. What does he do? Create empty databases, and swap ownership and permissions on objects?
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave KreskowiakI talked to him today and he said I was hammering him on the ownership toggle and he's gonna try to get me SA permissions - I guess I wore him down. :)
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
I talked to him today and he said I was hammering him on the ownership toggle and he's gonna try to get me SA permissions - I guess I wore him down. :)
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013It's amazing. "I'm sick of you making me do work so I'll just give you the permissions to do it yourself." Why didn't he just save himself the pain and do that in the first place? SMH, some people... :)
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
It's amazing. "I'm sick of you making me do work so I'll just give you the permissions to do it yourself." Why didn't he just save himself the pain and do that in the first place? SMH, some people... :)
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave KreskowiakI suggested it to him four years ago. After getting mad about it when he argued with me, I figured I'd just follow his rules. When my boss complained, I told her, "That's the way they wanna play it, so I'll be happy to oblige". He still has to ask IA (the pointless security agenda Nazis) if it's okay. What's really weird is that they hired a sql developer, and even without ANY certifications, she immediately had SA permissions, but me - with the *required MCSA certification* - couldn't get the same permissions. F*ckin lunacy...
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
I suggested it to him four years ago. After getting mad about it when he argued with me, I figured I'd just follow his rules. When my boss complained, I told her, "That's the way they wanna play it, so I'll be happy to oblige". He still has to ask IA (the pointless security agenda Nazis) if it's okay. What's really weird is that they hired a sql developer, and even without ANY certifications, she immediately had SA permissions, but me - with the *required MCSA certification* - couldn't get the same permissions. F*ckin lunacy...
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013I here you. I did four years at DHS/CBP at a port of entry. The lunacy is real! :laugh:
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
I suggested it to him four years ago. After getting mad about it when he argued with me, I figured I'd just follow his rules. When my boss complained, I told her, "That's the way they wanna play it, so I'll be happy to oblige". He still has to ask IA (the pointless security agenda Nazis) if it's okay. What's really weird is that they hired a sql developer, and even without ANY certifications, she immediately had SA permissions, but me - with the *required MCSA certification* - couldn't get the same permissions. F*ckin lunacy...
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013It's because she has chesticles and you don't
#SupportHeForShe Government can give you nothing but what it takes from somebody else. A government big enough to give you everything you want is big enough to take everything you've got, including your freedom.-Ezra Taft Benson You must accept 1 of 2 basic premises: Either we are alone in the universe or we are not alone. Either way, the implications are staggering!-Wernher von Braun
