Calling Procs from PowerShell with Parameters

Question

Calling Procs from PowerShell with Parameters

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16017 More actions · Answer 1

Jeff Moden wrote:

frederico_fonseca wrote:
Jeff Moden wrote:
Other that creating a parameter table the job reads from, I don't know anyone else that has been able to do it either.
out of curiosity what did you mean with this?
I'm just talking about a table in a database that you could have something populate with whatever "parameters" a job needs (even in job steps to "pass parameters" to the next step). I've also not seen people pass parameters directly from one step in a job to another. With the understanding that I'm definitely NOT an SSIS Ninja by any stretch of the imagination, I wouldn't mind learning how to do such a thing if it can be done without such a "parameter table".
Do you know of a way to pass parameters forward from one job step to another?

Without a table (or a queue) there is no way to pass "data" from 1 step to another so nothing new there - SQL Agent is quite old tech as you know.

as for using tables yes I have those - multiple step jobs that use a parameter table that both controls the flow of the steps and is updated by each step to determine what the next step(s) is/are going to do.

msbasssinger SSC Veteran Points: 263 More actions · Answer 2

Why not just write it as a C# library DLL, and call it from PowerShell?

Let PowerShell manage flow, let C# manage business logic. Clean separation of concerns.

DaveRogers Newbie Points: 5 More actions · Answer 3

Given that SQL Server is expensive and licensed by the CPU core, why would you waste CPU on running the SQL Server Agent?

I prefer to have a second computer that has a number of Windows Scheduled Tasks. A little PowerShell wrapper, or a .NET console application, can then issue the calls to SQL Server, in a manner similar to Frank's article.

DaveRogers Newbie Points: 5 More actions · Answer 4

Given that you have dbatools installed, there is an easier way to include parameters:

Invoke-DbaQuery -SqlInstance localhost -Database AdventureWorks2017 -Query "[dbo].[uspGetBillOfMaterials]"   
 -CommandType StoredProcedure -SqlParameter @{StartProductID = $productId, CheckDate = $checkDate}

eliassal Default port Points: 1467 More actions · Answer 5

I fully agree with DaveRogers as his suggestion works fine in PS 7 as well as PS standard which is not the case when using the suggested solution as it does not work in PS 7

tom_price Newbie Points: 1 More actions · Answer 6

Is there a reason NOT to use the more concise $cmd.Parameters.AddWithValue('@StartProductID',749) instead of

$p1 = $cmd.Parameters.Add('@StartProductID',[int])

$p1.ParameterDirection.Input

$p1.Value = 749

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 7

tom_price wrote:

Is there a reason NOT to use the more concise $cmd.Parameters.AddWithValue('@StartProductID',749) instead of
$p1 = $cmd.Parameters.Add('@StartProductID',[int]) $p1.ParameterDirection.Input $p1.Value = 749

Using .AddWithValue will end up costing you a lot more than it saves in concise code. There are quite a few issues - the most problematic is that it infers the data type from the value. This leads to sending nvarchar strings of different lengths where varchar should be used - which then causes full table-scans instead of index seeks due to implicit conversions.

Here are 3 articles you should review:

There are plenty more articles about the issues with using that code.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs