I am writing a script aim to get some info of running complie process from sql database.
If I ran the following line as a query in sql server
SELECT Stage.Description as StageDesc,Stage.StageStatusId FROM [Build].[dbo].[WorkflowInstance_View] as Build join [Build].[dbo].[Stage_View] as Stage on Build.Id=Stage.[WorkflowInstanceId]where Stage.ParentId is null and Stage.StageStatusId <>4 and Stage.StageStatusId <>7 order by Build.Id desc
I can get a table result like this,which contents xml informations. :
Now, Here are my codes by powershell, I was aim to get the xml part:
$connection= new-object system.data.sqlclient.sqlconnection
$Connection.ConnectionString ="server=SQLShare;database=Build;trusted_connection=True"
$connection.open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlQuery = "SELECT Stage.Description as StageDesc,Stage.StageStatusId FROM [Build].[dbo].[WorkflowInstance_View] as Build join [Build].[dbo].[Stage_View] as Stage on Build.Id=Stage.[WorkflowInstanceId]where Stage.ParentId is null and Stage.StageStatusId <>4 and Stage.StageStatusId <>7 order by Build.Id desc"
$SqlCmd.CommandText = $SqlQuery
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$SqlCmd.Connection = $Connection
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$Connection.Close()
but the result only appeared '2', this is the number of current running complie process.
Since I am sooo ignorent of sql server. Could any body would like to how to do this? How can I get the xml part by using powershell?
I've done this kind of thing fairly often. Not the XML part, but pulling data out SQL via Powershell.
You now have an object of type Datatable to parse through.