Error on SQL Connection String in Powershell

Question

I'm trying to run a sql query from powershell as a scheduled job in SQL server (it's then getting emailed out by the same powershell script) and getting an error I can't sort out.

###

$SqlServer = “localhost”
$SqlCatalog = “myDatabase”
$SqlQuery = “select * from MY_VIEW″
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “server=$SqlServer;Database=$SqlCatalog; user id = myUserid; password=myPassword”
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

This is the error I receive:

A job step received an error at line 7 in a PowerShell script. The corresponding line is '$SqlConnection.ConnectionString = "server=$SqlServer;Database=$SqlCatalog; user id = myUserid; password=myPassword"'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Unexpected token 'server=$SqlServer' in expression or statement. '.

What's wrong with my script?

vonPryz · Accepted Answer · 2014-06-06 15:13:20Z

1

The code sample contains "smart" quotes. That is, instead of ordinary double quote " (0x22), you have “ (0x201c) and ” (0x201d) (look carefully to see that double quote is stright up and smart ones curve).

This shouldn't be a problem, though "smart" quotes are really stupid an idea, especially when they are automagically used to format, say, blog posts containing code samples.

When copy-pasted to an editor or directly into Powershell, the statement

$SqlQuery = “select * from MY_VIEW″

won't have a closing quote but an question mark like so,

$SqlQuery = “select * from MY_VIEW?

This creates an unbalanced string quotation, so the script breaks. The weird quote char is likely to be double prime quote ″ (0x301c).

For a solution, purge the "smart" quotes very carefully and use the ordinary " double quote. Make sure you use decent an editor like Notepad++ and not a word processor like Wordpad or Word.

answered Jun 6, 2014 at 15:13

vonPryz

24.3k10 gold badges58 silver badges69 bronze badges

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

1 Comment

That One Guy Over a year ago

That was it! Thanks. I actually did copy the code (stolen shamelessly from another site) to notepad++, but apparently that doesn't catch those.

Collectives™ on Stack Overflow

Error on SQL Connection String in Powershell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related