0

OK, have a little bit of a puzzle. If I use invoke-sqlcmd by itself with -Variable I can successfully pass a variable into my .sql script

Now I need to do this against multiple instances, and thought I could use Get-DbaRegisteredServer (to use my CMS servers). However, it appears that Get-DbaRegisteredServer doesn't work with invoke-sqlcmd, SOO, I decided to try invoke-dbaquery which does work.

However, I would need the equivalent of -Variable, and it seems that -SqlParameters would do the trick, but not sure how to use this.

where with invoke-sqlcmd -variable myvar="myval", but need help to get the same behavior with invoke-dbaquery. What am I missing? thanks in advance!

2
  • 1
    Can you post an example of your code using -Variable?
    – alroc
    Jun 20, 2021 at 14:06
  • 1
    Note that SqlCmd variables are not the same as SQL Parameters. SqlCmd variables are substituted in the script before it's sent to SQL Server, so they can be used for scenarios where parameters are not allowed in TSQL, and also risk SQL injection, where parameters do not. Jun 20, 2021 at 14:15

1 Answer 1

1

Invoke-DbaQuery's -SqlParameters parameter requires a hash object and a parameterized query. So if your query is this:

$YourQuery = "select field1, field2 from table where field3=@MyVar;";

You will need this:

$Params = @{
  MyVar='Value Goes here';
};
Invoke-DbaQuery -query $YourQuery -SqlParameters @Params -SqlInstance INSTANCELIST;

You can specify the hash table inline as well but my personal preference is to make it separate, as this makes it easier to change the values based on conditionals.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.