Solved

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

Posted on 2014-10-31
2
766 Views
Last Modified: 2014-11-03
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

0
Comment
Question by:JWeb Admin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 11

Accepted Solution

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

Author Closing Comment

by:JWeb Admin
ID: 40419356
Thank you - looks like my code wasn't outputting another table in the dataset.  I appreciate your time!
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
A brief introduction to what I consider to be the best editor for PowerShell.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
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…

734 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