Invoke-sqlcmd with scripting variables and function parameters

Question

I have several tables that need to be periodically cleaned (de-duplicated). I want to create a function I can call and pass in a table name as a parameter. I'm stuck on the appropriate use of back tics (`), quotes (") and parentheses to reference the inline scripting variable ($) I'm trying to pass in as a function parameter.

I have poured over "get-help invoke-sqlcmd -detailed" and tried various combinations with the -Variable options, but with no success.

Can someone help me with the correct syntax?

My stripped down script looks like this -

# Define Server
$server = "AHDC389"  
# Define Database
$dbname = "VBP" 
# Define Table
$dbtab = "[FacilityBaseline]"   
# Import SQL svr handler
Import-Module “sqlps” -DisableNameChecking  
# Set env for SQL svr handler
Set-Location SQLSERVER:\SQL\$server\DEFAULT\Databases\$dbname
# Define my SQL query
$query = "SELECT DISTINCT * INTO #Temp FROM `$dbtab; TRUNCATE TABLE `$dbtab; 
INSERT INTO `$dbtab SELECT DISTINCT * FROM  #Temp;"
# Put Query in a Function for reuse
Function DeDup ([string] $dbtab) {
    Invoke-Sqlcmd -Query $query
}
# Call my Function and pass the Table name I want to dedup.
DeDup $dbtab

The errors I'm getting are " Incorrect syntax near '$dbtab' " or " 'dbtab' scripting variable not defined '

ravikanth · Accepted Answer · 2013-05-01 15:31:09Z

5

You should not use the backtick. With a backtick, the variable values don't get included in the query string. It will look like:

SELECT DISTINCT * INTO #Temp FROM $dbtab; TRUNCATE TABLE $dbtab; 
INSERT INTO $dbtab SELECT DISTINCT * FROM  #Temp;

Without a backtick, the variable values get expanded and the query will look like:

SELECT DISTINCT * INTO #Temp FROM [FacilityBaseline]; TRUNCATE TABLE [FacilityBaseline]; 
INSERT INTO [FacilityBaseline] SELECT DISTINCT * FROM  #Temp;

So, the right $query statement will be:

$query = "SELECT DISTINCT * INTO #Temp FROM $dbtab; TRUNCATE TABLE $dbtab; 
INSERT INTO $dbtab SELECT DISTINCT * FROM  #Temp;"

answered May 1, 2013 at 15:31

ravikanth

26k4 gold badges62 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Shepard · Accepted Answer · 2013-05-01 15:31:06Z

0

The problem is that the backtick before $dbtab is forcing the double-quotes to treat the dollar sign as a literal rather than introducing a variable name. Drop the backtick and that will work (and substitute the value of $dbtab).

answered May 1, 2013 at 15:31

Mike Shepard

18.4k7 gold badges59 silver badges72 bronze badges

Collectives™ on Stack Overflow

Invoke-sqlcmd with scripting variables and function parameters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related