[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Execute SQL Script using PowerShell

Posted on 2009-02-17
7
Medium Priority
?
10,326 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:computerstreber
  • 3
  • 2
  • 2
7 Comments
 
LVL 18

Expert Comment

by:BSonPosh
ID: 23665775
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
 
LVL 3

Author Comment

by:computerstreber
ID: 23666927
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
 
LVL 3

Author Comment

by:computerstreber
ID: 23667444
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
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

 
LVL 18

Expert Comment

by:BSonPosh
ID: 23670459
0
 
LVL 3

Author Comment

by:computerstreber
ID: 23674184
The code is pretty good, but I still run into the same problem. I can only had one SQL statement.
0
 
LVL 11

Expert Comment

by:Joe Klimis
ID: 24102866
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
 
LVL 11

Accepted Solution

by:
Joe Klimis earned 2000 total points
ID: 24105823
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Loops Section Overview

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question