SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Use powershell to execute output of a SP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

apriljuly
Starting Member

3 Posts

Posted - 12/27/2012 :  04:52:33  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/27/2012 :  06:34:02  Show Profile  Reply with Quote
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 - 12/27/2012 :  06:53:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/27/2012 :  07:20:48  Show Profile  Reply with Quote
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 - 12/27/2012 :  07:46:49  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000