asked on

Execute SQL Script using PowerShell

I am trying to execute the PowerShell script (shown in code), but the script only runs the first line on the SQL script ("C:\script1.sql"). How can I can this to run the entire SQL script?

Here is the text of "C:\script1.sql"

use master;

create database (test);

use test;

create table mytest (
myid int
);

insert into mytest values(1);

Here is the text of "C:\serv.txt":

MyServer
MyServer2

$cmd = get-content "C:\script1.sql"
 
foreach ($svr in get-content "C:\serv.txt")
{
  $con = "server=$svr;database=master;Integrated Security=sspi"
  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
  $dt = new-object System.Data.DataTable
  $da.fill($dt) | out-null
  $svr
  $dt | format-table
}

Open in new window

BSonPosh

Isn't this the same as your other SQL problem?

I think you have to use the GO key word.

like this

use master;
create database (test);
GO
use test;
create table mytest (
      myid int
);
GO
insert into mytest values(1);
GO

Open in new window

computerstreber

ASKER

I tried changing my SQL Script to this:

use master
GO

create database test
GO

use test
GO

create table mytest (
myid int
)
GO

insert into mytest values(1)
GO

But got this error:

Exception calling "Fill" with "1" argument(s): "Incorrect syntax near 'GO'. Incorrect syntax near 'GO'. Could not locate entry in sysdatabases for database 'test'. No entry found with that name. Make sure that the name entered correctly." At C:\test.ps1:8 char:11 + $da.fill< <<<< $dt> | out-null

I also changed the SQL script to this:

create database test;

use test;

create table mytest (
myid int
);

insert into mytest values(1);

But got a similiar error to the above. I think it is because I am using the data table, but not sure.

computerstreber

ASKER

I have discovered a command to execute my SQL Script, but I don't really like it. Is there a way to do this without calling SQLCMD?

foreach ($srvr in get-content "C:\myservers.txt") {
 
  write-host "Applying to Server:" $srvr
 
  invoke-expression "SQLCMD /S $srvr /E /i myscript.sql"
}

Open in new window

BSonPosh

perhaps a better option

http://www.leeholmes.com/blog/InteractingWithSQLDatabasesInPowerShellInvokeSqlCommand.aspx

computerstreber

ASKER

The code is pretty good, but I still run into the same problem. I can only had one SQL statement.

Joe Klimis

when sending commands via powershell do NOT send the go commands as this will stop it from working, i have a working function which i will post tomorrow that will allow youto pass 1 or more commands a a time
Joe

ASKER CERTIFIED SOLUTION

Joe Klimis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial