Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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 '

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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;"
share|improve this answer
    
Than you. I understand now. –  Colin A. White May 1 '13 at 16:35
add comment

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).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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