Solved

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

Posted on 2014-10-31
2
806 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article will help you understand what HashTables are and how to use them in PowerShell.
In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

627 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