Creating multiple stored procedures from SQL executed by powershell issue

Question

Ok, so I've got a bit of a SQL and Powershell problem. There are 2 SQL scripts, one to setup 4 different global stored procedures. Another to execute them and manipulate data before returning it to PS to be placed in a CSV file. The reason I'm not putting them into a single file is for readability. The procs are enclosing huge chunks of sql and I cannot create permanent procs in our production environment.

The problem I'm running into is the script runs fine in SQL Mgmt Studio but when ran by PS, I get several errors around the 'go's in the script.

I'm pretty sure this is a problem with the format that PS and the .NET classes expect when executing and returning data sets but...I'm at a loss.

I'm running SQL Server 2005 btw.

Any ideas or similar experiences?

Answer 1

What errors do you get? How are you executing each file? GO is a batch separator understood only by certain tools (e.g. Management Studio); PowerShell doesn't know what GO means. Have you tried executing the separate CREATE PROCEDURE scripts without issuing a GO command between them? If they are separate commands this shouldn't be an issue.

Answer 2

"GO" is a delimiter used by SQL Management Studio. It is not a valid SQL keyword. You can configure SQL Management Studio and change "GO" to "ENGAGE" if you wanted to.

Just remove "GO" from the scripts.