Getting errors when trying to apply SQL files with PowerShell

Question

When I manually apply a SQL file, like a re-indexing job, everything runs perfectly. I want to automate applying SQL files in powershell, but I'm getting all kinds of incorrect syntax errors when using Get-Content. Is there a better way to get a SQL files contents, then apply those contents on a remote server that doesn't re-format the code to the point where I get incorrect syntax errors.

Generic error I get (any syntax might throw an error - ps seems to be mis-applying the syntax when getting the content of the file):

Incorrect syntax near [anything]

Note: all GOs have been removed, so this isn't related to those; it may throw an error due to a begin, a goto, etc. The reason is that it's not retaining how the SQL file is built.

Update

The SQL files can be anything from adding permissions to creating an index to building a table to creating a stored procedure. I have about 100 SQL files. If I manually execute them, they all work, so this isn't related to bad SQL syntax, but related to how Powershell is reading the SQL syntax and running it as a command.

Answer

The below appears to work:

Get-Content $file -Raw

It can be anything from adding permissions to creating an index to building a table to creating a stored procedure. I have about 100 SQL files. If I manually execute them, they all work, so this isn't related to bad SQL syntax, but related to how Powershell is reading the SQL syntax and running it as a command. — DoubleVu
– DoubleVu, Commented Feb 22, 2016 at 17:14
When you use Get-Content you should possibly pipe into Out-Sting.... Get-Content $file | Out-String. Does that change anything? — Matt
– Matt, Commented Feb 22, 2016 at 17:19

Matt · Accepted Answer · 2016-02-22 17:22:45Z

1

but I'm getting all kinds of incorrect syntax errors when using Get-Content

Get-Content will return an array of strings. What you need to be using is a single string. If you have at least PowerShell 3.0 then the -Raw switch is what you want.

Get-Content $file -Raw

Earlier versions you can use

Get-Content $file | Out-String

For exceptionally large files you can use .Net StreamReader's but I don't think that will be required here.

answered Feb 22, 2016 at 17:22

Matt

47.1k9 gold badges90 silver badges125 bronze badges

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

Comments

alroc · Accepted Answer · 2016-02-22 18:04:02Z

1

How are you "applying a SQL file"? If you're passing the contents of the file into Invoke-SQLCmd, you'll need to use -Raw like @Matt said.

However, you don't even really need to do that. Invoke-SQLCmd lets you pass a filename directly into it. Use the -Inputfile parameter.

answered Feb 22, 2016 at 18:04

alroc

28.3k6 gold badges55 silver badges106 bronze badges

Collectives™ on Stack Overflow

Getting errors when trying to apply SQL files with PowerShell

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