Powershell, SQL just stopped working on PS version change.

rated by 0 users
This post has 4 Replies | 1 Follower

Top 500 Contributor
Posts 9
Lydia626 Posted: 01-10-2012 5:43 PM

on SQL server 2008 and powershell the following was working fine for me.

$con = "server=$hostname;database=Log;Integrated Security=sspi"

 

$cmd = "SELECT top 1 replace(convert(varchar, LogDate , 110), '-', '') from $Argument1`_$day with (nolock)"

$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

$dt = new-object System.Data.DataTable

$da.fill($dt) | out-null

$dt | Format-Table -autosize

 

 

But when I switched to SQL server 2005 with powershell version 1.0 I started getting errors like below. Anyone have any idea how I can fix it?

 

Exception calling "Fill" with "1" argument(s): "Incorrect syntax near '_'.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a

change tracking context clause, the previous statement must be terminated with a semicolon."

At c:\TestLogs.ps1:36 char:9

+ $da.fill( <<<< $dt) | out-null

 

 

Top 500 Contributor
Posts 9

The version on the box I am having problems with is Microsoft SQL Server 2005 - 9.00.3042.00 (X64)   Feb 10 2007 00:59:02   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) 


Another thing to note is that on sql 2008 with powershell 2.0 both of these work

$con = "server=$hostname;database=Log;Integrated Security=sspi"
$cmd = "SELECT top 1 replace(convert(varchar, LogDate , 110), '-', '') from $Argument1`_$day with (nolock)"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
$dt | Format-Table -autosize


$con = "server=$hostname;database=Log;Integrated Security=sspi"
$cmd = "select * from log_1"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
$dt | Format-Table -autosize


but on 2005 with powershell 1.0  it is this query that suddenly breaks via powershell

SELECT top 1 replace(convert(varchar, LogDate , 110), '-', '') from $Argument1`_$day with (nolock)

Top 10 Contributor
Posts 658
Idera Employee

Just a silly question, but did you turn on tracing on the SQL Server to see if the query is failing on the server side? This would tell you whether or not the issue is PowerShell or SQL Server 2005.

Another thing I would try is to debug the script at run time and take that actual $cmd query text and run it in SQL Server Query tool to again see if it is PowerShell or SQL Server.

If the error is occurring at the .Fill line then there is something going on with the execution of the query. If my memory serves me correctly, calling SqlDataAdapter just shapes the DataSet definition. The .Fill actually runs the query and returns rows.

Top 500 Contributor
Posts 9

It’s not making it out of powershell. I think the root of the problem might be that powershell 1.0 seems to like underbar even less than PS 2.0.   in 2.0 to my understanding you can tell powershell that underbar is not a tab by putting a backtick in front of it `_  but in 1.0 this does not seem to be the case. 

Also from in the error  "Incorrect syntax near the keyword 'with'

I am guessing that PS 1.0 does not allow the way the query is formatted and that ps 2.0 is okay with it.

If I pass the query on in with no variables and just hard code the table it works fine in PS 1.0

Also if I just pass the one variable in it works $Argument1

$con = "server=$hostname;database=Log;Integrated Security=sspi"

$cmd = "SELECT top 1 replace(convert(varchar, LogDate , 110), '-', '') from testlog"

$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

$dtt = new-object System.Data.DataTable

$da.fill($dtt) | out-null

$dtt | Format-Table -autosize

Top 500 Contributor
Posts 9

I have figured out my problem.

In powershell 2.0 you can do $VariableA`_$variableB but not in PS 1.0 

Here is how I got around this limitation in PS 1.0

$underscore = "_"

$VariableA$underscore$variableB

Page 1 of 1 (5 items) | RSS
Copyright 2012 PowerShell.com. All rights reserved.