Execute SQL Script using PowerShell

computerstreber
computerstreber used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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

Author

Commented:
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.

Author

Commented:
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

Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Author

Commented:
The code is pretty good, but I still run into the same problem. I can only had one SQL statement.
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
try the folloing function
just define $sqlquery to be one or more lines of SQL code, and dont use go
Good luck
joe

#-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 Function GetSqlDAtaSet 
#-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
# usage:  #
#-=-=-=-=-= 
# $myinfo   = Getsqldataset $sqlquery "servername\instance,port" database_name
# $myinfo   = Getsqldataset $sqlquery "server1\test" database1
#
 
{
 Param($sql, 
$server=".",
$SQLdb)
  $sqlConnection                              = new-object System.Data.SqlClient.SqlConnection "Server = $server;Database=$SQLdb;Integrated Security=True"
  $return_value                                 = $sqlConnection.Open()
  $sqlCommand                                                 = New-object system.data.sqlclient.SqlCommand   
  $sqlCommand.CommandTimeout         = 30 
  $sqlCommand.Connection        = $sqlConnection
  $sqlCommand.CommandText                 =  $sql
  $sqlDataAdapter                            = new-object System.Data.SqlClient.SQLDataAdapter($sqlCommand) 
  $sqlDataSet                                      = new-object System.Data.dataset 
  $sqlDataAdapter.fill($sqlDataSet)                                                         # move data into dataset
 
                $mydata              = $sqlDataSet.tables[0].select()
                $sqlconnection.close() # close connection
                return $mydata                                # return object 
}

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial