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.
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]);} |
|
|
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]);}
|
|
|
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. |
|
|
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.
|
|
|
|
|
|
|
|