Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Use powershell to execute output of a SP

Author  Topic 

apriljuly
Starting Member

3 Posts

Posted - 2012-12-27 : 04:52:33
I wrote a stored procedure, the stored procedure can display some command to draw a report. Now I want to use powershell to execute each row of the output of the stored procedure.

I have a code like this

`$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=T420s-JLI-W7;Database=AMSNewDataWarehouse;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "YQBreport1"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0] `

However it only list the output, instead of execute each row of the output. How can I let it execute each row of output?
An example of the output row is like

$shape1=$page.DrawRectangle(7.81250000000,6.78,8.29166666660,7.025);

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-27 : 06:34:02
After you get the data into the data table, have to do something like shown below:
....
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

foreach ($datarow in $DataSet.Tables[0].Rows)
{
$page.DrawRectangle($datarow[0],$datarow[1],$datarow[2],$datarow[3]);
}
Go to Top of Page

apriljuly
Starting Member

3 Posts

Posted - 2012-12-27 : 06:53:34
Hi Sunitabeck,
Thank you for your help.
I should've been more clearer.
Below is just small part of the result after running the store procedure. If I export the result into an excel, then copy from excel to powershell, it will be executed and draw me the report.
However, I would like powershell to execute the output automatically without the manual copy paste. Is it possible?

$application = New-Object -ComObject Visio.Application
$documents = $application.Documents
$document = $documents.Add("AMSGantt.vst")
$pages = $application.ActiveDocument.Pages
$page = $pages.Item(1)
$shape500 = $page.DrawLine(2,7.9,11,7.9)
$shape500.TextStyle = "Title"
$shape500.LineStyle = "Title"
$shape500.Text = "Assignation de Barrières - Tuesday, December 18, 2012"
$shape1 = $page.DrawRectangle(1,2.78,3.1875,3.025)
$shape1.LineStyle = "Gantt"
$shape1.TextStyle = "Gantt"
$shape1.FillStyle = "Gantt"
$shape1.Text = "C69802 / 73H / C6624"
$shape101 = $page.DrawLine(0.5,2.75,1.5,2.75)
$shape101.Text = "17:30"
$shape101.TextStyle = "Times"
$shape101.LineStyle = "Times"
$shape201 = $page.DrawLine(3.0875,2.75,3.2875,2.75)
$shape201.Text = "05:15"
$shape201.TextStyle = "Times"
$shape201.LineStyle = "Times"
$shape2 = $page.DrawRectangle(3.1875,4.78,3.8125,5.025)
$shape2.LineStyle = "Gantt"
$shape2.TextStyle = "Gantt"
$shape2.FillStyle = "Gantt"
$shape2.Text = "C69802 / 73H / C6624"
$shape102 = $page.DrawLine(2.6875,4.75,3.6875,4.75)
$shape102.Text = "05:15"
$shape102.TextStyle = "Times"
$shape102.LineStyle = "Times"
$shape202 = $page.DrawLine(3.7125,4.75,3.9125,4.75)
$shape202.Text = "06:45"
$shape202.TextStyle = "Times"

quote:
Originally posted by sunitabeck

After you get the data into the data table, have to do something like shown below:
....
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

foreach ($datarow in $DataSet.Tables[0].Rows)
{
$page.DrawRectangle($datarow[0],$datarow[1],$datarow[2],$datarow[3]);
}


Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-27 : 07:20:48
I am not familiar with Visio's COM API - however, I would think that you should be able to do it all in one Powershell script by first getting the data into a DataTable (like you have already done) and accessing the that data via the elements in each of the data rows of the data table as I had described in my previous post.

I know I am speaking in generalities - unfortunately, I don't have the skills or familiarity with your data to offer anything more concrete.
Go to Top of Page

apriljuly
Starting Member

3 Posts

Posted - 2012-12-27 : 07:46:49
Thank you all the same!

quote:
Originally posted by sunitabeck

I am not familiar with Visio's COM API - however, I would think that you should be able to do it all in one Powershell script by first getting the data into a DataTable (like you have already done) and accessing the that data via the elements in each of the data rows of the data table as I had described in my previous post.

I know I am speaking in generalities - unfortunately, I don't have the skills or familiarity with your data to offer anything more concrete.

Go to Top of Page
   

- Advertisement -