Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-10-31
2
Medium Priority
?
862 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 2000 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

The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
In the absence of a fully-fledged GPO Management product like AGPM, the script in this article will provide you with a simple way to watch the domain (or a select OU) for GPOs changes and automatically take backups when policies are added, removed o…
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 video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

722 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