CODEWITHSUNDEEP
sqlpowershellodbc
user4317867
user4317867

Reputation: 2448

Powershell ODBC error in query using IIF

I'm trying to take an existing Access Database query and get it into Powershell.

$SQLServer = "SERVERNAME" #use Server\Instance for named SQL instances!
$SQLDBName = "DBname"
[string] $SqlQuery = $("SELECT TOP 5 Item1 as COMPUTER, Item2 as Location FROM dbo.mytable WHERE (((IIf([dbo_mytable].[swMonday]=1,"Mon")) Is Not Null) AND ((dbo_mytable.swGrpResp)="SpecificGroup") AND ((dbo_mytable.swRootObjectType)="Server"))")

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

$DataSet.Tables[0] | ft -AutoSize

Powershell gives the following error:

Exception calling "Fill" with "1" argument(s): "ERROR [42000] [Microsoft][ODBC SQL Server 
Driver][SQL Server]Incorrect syntax near ')'.
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'IIf'."
$null = $da.fill <<<< ($dt)
CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
FullyQualifiedErrorId : DotNetMethodException

Upvotes: 0

Views: 659

Answers (2)

user4317867
user4317867

Reputation: 2448

After much digging around, I found what appears to be the perfect example of using CASE WHEN in Powershell in a SQL query. Posting this here should someone benefit from this.

CASE cmptlevel  
WHEN 60 THEN '60 (SQL Server 6.0)'  
WHEN 65 THEN '65 (SQL Server 6.5)'  
WHEN 70 THEN '70 (SQL Server 7.0)'  
WHEN 80 THEN '80 (SQL Server 2000)'  
WHEN 90 THEN '90 (SQL Server 2005)'  
WHEN 100 THEN '100 (SQL Server 2008)'

Taken from https://gallery.technet.microsoft.com/scriptcenter/PowerShell-Export-SQL-Data-67bcb690

Upvotes: 0

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200293

The syntax of your query string is wrong. First, get rid of all the unnecessary stuff. You don't need to put a string in a subexpression ($()), and you also don't need to cast it to a string, since it already is a string.

The actual cause of the error you're getting is that you can't have unescaped double quotes inside a double quoted string. Either escape the nested double quotes (` is the escape character in PowerShell):

$SqlQuery = "SELECT TOP 5 Item1 as COMPUTER, Item2 as Location FROM dbo.mytable WHERE (((IIf([dbo_mytable].[swMonday]=1,`"Mon`")) Is Not Null) AND ((dbo_mytable.swGrpResp)=`"SpecificGroup`") AND ((dbo_mytable.swRootObjectType)=`"Server`"))"

or make it a single quoted string (since you don't use PowerShell variables in it):

$SqlQuery = 'SELECT TOP 5 Item1 as COMPUTER, Item2 as Location FROM dbo.mytable WHERE (((IIf([dbo_mytable].[swMonday]=1,"Mon")) Is Not Null) AND ((dbo_mytable.swGrpResp)="SpecificGroup") AND ((dbo_mytable.swRootObjectType)="Server"))'

Also, according to the documentation the IIF() function expects exactly 3 arguments. You call it with only 2 arguments:

IIf([dbo_mytable].[swMonday]=1, "Mon")

so you need to add the missing third one:

IIf([dbo_mytable].[swMonday]=1, "Mon", "???")

On a more general note: I'd get rid of all the surplus parentheses in your query, as they don't improve readability. And perhaps define it as a multiline string.

$Query = @'
SELECT TOP 5
  Item1 as COMPUTER,
  Item2 as Location
FROM dbo.mytable
WHERE IIf([dbo_mytable].[swMonday]=1, "Mon") Is Not Null AND
  dbo_mytable.swGrpResp = "SpecificGroup" AND
  dbo_mytable.swRootObjectType = "Server"
'@

is quite a bit more readable than

$SqlQuery = 'SELECT TOP 5 Item1 as COMPUTER, Item2 as Location FROM dbo.mytable WHERE (((IIf([dbo_mytable].[swMonday]=1,"Mon")) Is Not Null) AND ((dbo_mytable.swGrpResp)="SpecificGroup") AND ((dbo_mytable.swRootObjectType)="Server"))'

or even

$Query = @'
SELECT TOP 5
  Item1 as COMPUTER,
  Item2 as Location
FROM dbo.mytable
WHERE (((IIf([dbo_mytable].[swMonday]=1,"Mon")) Is Not Null) AND
  ((dbo_mytable.swGrpResp)="SpecificGroup") AND
  ((dbo_mytable.swRootObjectType)="Server"))
'@

Upvotes: 1

Related Questions