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

LVL 3
computerstreberAsked:
Who is Participating?
 
Joe KlimisConnect With a Mentor Commented:
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

0
 
BSonPoshCommented:
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

0
 
computerstreberAuthor 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.

0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
computerstreberAuthor 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

0
 
computerstreberAuthor Commented:
The code is pretty good, but I still run into the same problem. I can only had one SQL statement.
0
 
Joe KlimisCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.