Solved

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

Posted on 2014-10-31
2
620 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
2 Comments
 
LVL 10

Accepted Solution

by:
JoeKlimis earned 500 total points
Comment Utility
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
Comment Utility
Thank you - looks like my code wasn't outputting another table in the dataset.  I appreciate your time!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now