Invoke-SqlCmd - file with multiple select statements doesn't finish execution

We have some SQL files that contain multiple select statements, however when I execute the file using Invoke-SqlCmd and output to a csv, its as if the script doesn't want to finish.  I've even tried using datasets, however that yields the same results.

Is there a way to handle multiple select statements with PowerShell and Invoke-SqlCmd?

Here's an example of the script that I'm trying to run that stops the output after the first select statement.  I can confirm that this script runs with no syntax errors inside SSMS, as well as sqlcmd:

SELECT * FROM master.sys.databases ORDER BY database_id; SELECT DatabaseName,  
	[DirtyPageCount] * 8 / 1024 AS [Dirty PageMB],  
	[CleanPageCount] * 8 / 1024 AS [Clean PageMB]  ,
	([DirtyPageCount]+[CleanPageCount])* 8 / 1024 AS [Total BufferPool MB]
FROM 
     (SELECT 
         (CASE WHEN ([database_id] = 32767)  
             THEN N'Resource Database' 
             ELSE DB_NAME ([database_id]) END) AS [DatabaseName],  
         SUM (CASE WHEN ([is_modified] = 1)  
             THEN 1 ELSE 0 END) AS [DirtyPageCount],  
         SUM (CASE WHEN ([is_modified] = 1)  
             THEN 0 ELSE 1 END) AS [CleanPageCount]  
     FROM sys.dm_os_buffer_descriptors  WITH (NOLOCK)
     GROUP BY [database_id]) AS [buffers]  
 ORDER BY [DatabaseName] OPTION (RECOMPILE);

Open in new window

JWeb AdminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joe KlimisCommented:
Hi
Try the following , this seems to work for me .

let me know how you get on.


Joe

$sqlcmd = @'
SELECT * FROM master.sys.databases ORDER BY database_id; SELECT DatabaseName,  
	[DirtyPageCount] * 8 / 1024 AS [Dirty PageMB],  
	[CleanPageCount] * 8 / 1024 AS [Clean PageMB]  ,
	([DirtyPageCount]+[CleanPageCount])* 8 / 1024 AS [Total BufferPool MB]
FROM 
     (SELECT 
         (CASE WHEN ([database_id] = 32767)  
             THEN N'Resource Database' 
             ELSE DB_NAME ([database_id]) END) AS [DatabaseName],  
         SUM (CASE WHEN ([is_modified] = 1)  
             THEN 1 ELSE 0 END) AS [DirtyPageCount],  
         SUM (CASE WHEN ([is_modified] = 1)  
             THEN 0 ELSE 1 END) AS [CleanPageCount]  
     FROM sys.dm_os_buffer_descriptors  WITH (NOLOCK)
     GROUP BY [database_id]) AS [buffers]  
 ORDER BY [DatabaseName] OPTION (RECOMPILE);
'@
#-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 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 
}

$dataset  = get-sqLdataset $sqlcmd "Servername\instance"

$dataset.table[0] | Export-Csv -NoTypeInformation table1.csv
$dataset.table[1] | Export-Csv -NoTypeInformation table2.csv

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JWeb AdminAuthor Commented:
Thank you - looks like my code wasn't outputting another table in the dataset.  I appreciate your time!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.