|
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
modified 21-Jun-18 12:35pm.
|
|
|
|
|
Odd, as long as there are some standards setup and working.
Just use Copy-DbaAgentJob – dbatools
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 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
|
|
|
|
|
Apparently your "non-programmer" DBA can't write queries either.
What does he do? Create empty databases, and swap ownership and permissions on objects?
|
|
|
|
|
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, 2013
|
|
|
|
|
I 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.
|
|
|
|
|
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, 2013
|
|
|
|
|
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...
|
|
|
|
|
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, 2013
|
|
|
|
|
I here you. I did four years at DHS/CBP at a port of entry.
The lunacy is real!
|
|
|
|
|
It'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
|
|
|
|
|
I 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.
"Go forth into the source" - Neal Morse
|
|
|
|
|
That is a whole lot of replace statements; yikes!
|
|
|
|
|
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
|
|
|
|
|
I don't see any problem with it.
"It is easy to decipher extraterrestrial signals after deciphering Javascript and VB6 themselves.", ISanti[ ^]
|
|
|
|
|
I discovered that LNK1181 arises when the /P switch gets into the C/C++ compiler command line, preventing the compilation phase from producing an output file. This happened accidentally early in development, and for some reason remained enabled for my x86 Release build configuration. Once I removed the /P switch, the link step completed without errors.
Linker Tools Error LNK1181 | Microsoft Docs mentions other common causes, but says nothing about this one. When I discovered this, I opened an issue, Another Cause for LNK1181 · Issue #299 · MicrosoftDocs/cpp-docs · GitHub with Microsoft.
Since it might be a while before the suggestion gets into the documentation, this is a heads up. Meanwhile, at least there is a link to the issue.
David A. Gray
Delivering Solutions for the Ages, One Problem at a Time
Interpreting the Fundamental Principle of Tabular Reporting
|
|
|
|
|
Hi,
It's already documented. The /P option[^] suppresses compilation. It instructs the compiler to NOT produce an .obj file.
That /P compiler option will always cause the linker to fail. Because there was nothing produced that could be linked. The LNK1181 is telling you "object file not found" which is correct.
You should always make an attempt to fully understand each and every compiler option you are using. I'd recommend avoiding using compiler options that you do not understand.
Best Wishes,
-David Delaune
|
|
|
|
|
That's not the point, which is that the documentation would be more helpful if it mentioned that switch as something that will, obviously, elicit LNK1181. I spent a lot of time chasing down other potential causes that I wouldn't have spent if the documentation had mentioned the /P switch. Had it done so, I would immediately have checked for that, since I knew that I had enabled it for that translation unit a few hours earlier. As it is, I was unsurprised that the /P switch was enabled on the module property sheet for that translation unit, and that it overrode the project property sheet that said otherwise.
I've been writing in various flavors of the C programming language since 2005, and my reading knowledge goes back a further 20 years. I've worked almost exclusively in the Microsoft stack since the mid 1990's, and have worked in other equally complex stacks since 1978.
David A. Gray
Delivering Solutions for the Ages, One Problem at a Time
Interpreting the Fundamental Principle of Tabular Reporting
|
|
|
|
|
Well,
Technically... there are several of the compiler options[^] that suppress compilation. The /E[^] and /EP[^] and /P[^] all suppress compilation and always result in a linker failure. There may be a few more as that's all I remember off the top of my head.
I do think that it might be useful if these compiler options generated a level 3 or 4 warning so they can be caught by /WX. But if that change was made... you'd hear someone else complaining that they are unable to generate preprocessed output to a file when /W3 or /W4 was enabled.
Best Wishes,
-David Delaune
|
|
|
|
|
I agree, and you raise a good point about other switches that suppress code generation. I suspect the best compromise is to call attention to them in the documentation of LNK1181; other linkage editor errors go into such detail about possible causes. Since linkage editor errors can be difficult to solve, all the help that can be mustered would be appreciated by all concerned.
Would you be willing to comment on the issue that I opened, so that Microsoft hears from someone besides me in this regard? If so, the URL is in my original message.
David A. Gray
Delivering Solutions for the Ages, One Problem at a Time
Interpreting the Fundamental Principle of Tabular Reporting
|
|
|
|
|
David A. Gray wrote: Would you be willing to comment on the issue that I opened, so that Microsoft hears from someone besides me in this regard?
Unfortunately I cannot comment on your open issue. Back when I became a Microsoft employee I signed at least a dozen mountains of documents that limit my ability to comment and interact on public forums regarding Microsoft and certain Microsoft products. I've probably crossed or walked that line already within the last few years.
Trust me, both the Visual Studio team and c++ compiler team has probably already seen your open issue. Give him some time to respond.
Best Wishes,
-David Delaune
|
|
|
|
|
Thank you for disclosing your connection. Since my immediate problem is resolved, my objective in opening the issue is to pay it forward by suggesting an improvement that I suspect will benefit some future puzzled programmer. Hence, I have no time limit, and I suspected that they might already have seen it, or at least a report thereof.
David A. Gray
Delivering Solutions for the Ages, One Problem at a Time
Interpreting the Fundamental Principle of Tabular Reporting
|
|
|
|
|
Randor wrote: Visual Studio team and c++ compiler team
Randor wrote: him Small "team"
#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
|
|
|
|
|