Getting multiple values from a invoke-sql query and using them

[christianv]

I am trying to also get the delta displayed, what am I doing wrong?$processed = invoke-sql -server DBSERVER -SQL "set nocount ondeclare @xx table(dataflow varchar(50), FLOW_PROCESS_MIN int,FLOW_DELTA int)insert into @xxSELECT top 26 DATAFLOW, FLOW_PROCESS_MIN, FLOW_DELTA FROM DB.dbo.DataFlowStats (nolock)  ORDER BY AuditTimeStamp DESC   UPDATE @xx SET FLOW_DELTA = 0 WHERE dataflow IN ('MQMSGMUREX','MQSEQMUREX')SELECT SUM (FLOW_PROCESS_MIN) as Processed, SUM (FLOW_DELTA) as Delta from @xx"$obj = new-object System.objectadd-member -inputobject $obj -membertype NoteProperty -Name Processed -value $processed.Processed add-member -inputobject $obj -membertype NoteProperty -Name Delta -value $_.Delta $obj

Thanks [] 

[JuanC]

Have you tried using $processed.Delta instead of $_.Delta?

If this does not work I would recommend you do $processed | get-member, this will let you know if the results of your T-SQL are being returned as you expect to the powershell variable.

JuanC

[christianv]

It does not show the variable in the get-member it is simply blank (or non-existing). however when I do the following:

invoke-sql -server DBserver -SQL "set nocount ondeclare @xx table(dataflow varchar(50), FLOW_PROCESS_MIN int,FLOW_DELTA int)insert into @xxSELECT top 26 DATAFLOW, FLOW_PROCESS_MIN, FLOW_DELTA FROM DBtable.dbo.DataFlowStats (nolock)  ORDER BY AuditTimeStamp DESC UPDATE @xx SET FLOW_DELTA = 0 WHERE dataflow IN ('MQMSGMUREX','MQSEQMUREX')SELECT SUM (FLOW_PROCESS_MIN) as Processed,SUM (FLOW_DELTA) as Delta from @xx" | select Processed, Delta  I get all variables

 

Kind regards,

Christian 

[marco.shaw]

Can we actually see the output of the command and also what get-member says?  get-member doesn't lie! ;-)

[christianv]

This is what I get, hope this helps us all  TypeName: System.Data.DataRowName   MemberType   Definition----   ----------   ----------AcceptChanges Method   System.Void AcceptChanges()BeginEdit Method   System.Void BeginEdit()CancelEdit   Method   System.Void CancelEdit()ClearErrors Method   System.Void ClearErrors()Delete   Method   System.Void Delete()EndEdit Method   System.Void EndEdit()Equals   Method   System.Boolean Equals(Object obj)GetChildRows   Method   System.Data.DataRow[] GetChildRows(String relationName), System.Data.DataRow...GetColumnError   Method   System.String GetColumnError(Int32 columnIndex), System.String GetColumnErro...GetColumnsInError Method   System.Data.DataColumn[] GetColumnsInError()GetHashCode Method   System.Int32 GetHashCode()GetParentRow   Method   System.Data.DataRow GetParentRow(String relationName), System.Data.DataRow G...GetParentRows Method   System.Data.DataRow[] GetParentRows(String relationName), System.Data.DataRo...GetType Method   System.Type GetType()get_HasErrors Method   System.Boolean get_HasErrors()get_Item   Method   System.Object get_Item(Int32 columnIndex), System.Object get_Item(String col...get_ItemArray Method   System.Object[] get_ItemArray()get_RowError   Method   System.String get_RowError()get_RowState   Method   System.Data.DataRowState get_RowState()get_Table Method   System.Data.DataTable get_Table()HasVersion   Method   System.Boolean HasVersion(DataRowVersion version)IsNull   Method   System.Boolean IsNull(Int32 columnIndex), System.Boolean IsNull(String colum...RejectChanges Method   System.Void RejectChanges()SetAdded   Method   System.Void SetAdded()SetColumnError   Method   System.Void SetColumnError(Int32 columnIndex, String error), System.Void Set...SetModified Method   System.Void SetModified()SetParentRow   Method   System.Void SetParentRow(DataRow parentRow), System.Void SetParentRow(DataRo...set_Item   Method   System.Void set_Item(Int32 columnIndex, Object value), System.Void set_Item(...set_ItemArray Method   System.Void set_ItemArray(Object[] value)set_RowError   Method   System.Void set_RowError(String value)ToString   Method   System.String ToString()Item   ParameterizedProperty System.Object Item(Int32 columnIndex) {get;set;}, System.Object Item(String ...Delta Property   System.Int32 Delta {get;set;}Processed Property   System.Int32 Processed {get;set;}Processed : 1272Delta :

[marco.shaw]

OK, so first you are providing us with the output of "$processed|get-member", then the result of the invoke-sql cmdlet?

Perhaps I'm missing something, but didn't you previously mentioned that invoke-sql was giving you a delta value, but from what you just copied, it isn't?

 

[christianv]

Yes, if I run the following :

 

invoke-sql -server SACPANICS01 -SQL "set nocount ondeclare @xx table(dataflow varchar(50), FLOW_PROCESS_MIN int,FLOW_DELTA int)insert into @xxSELECT top 26 DATAFLOW, FLOW_PROCESS_MIN, FLOW_DELTA FROM icsadmin.dbo.DataFlowStats (nolock)  ORDER BY AuditTimeStamp DESC   UPDATE @xx SET FLOW_DELTA = 0 WHERE dataflow IN ('MQMSGMUREX','MQSEQMUREX')SELECT SUM (FLOW_PROCESS_MIN) as Processed,SUM (FLOW_DELTA) as Delta from @xx" | select Processed, Delta

 

I get:

 Processed Delta --------- ----- 946 946

 

but I want to do this in one Invoke-sql query

 

Can this be done?

 

Thanks  

 

[marco.shaw]

I think we need to start over...

Run this again:

$processed = invoke-sql -server DBSERVER -SQL "set nocount ondeclare @xx table(dataflow varchar(50), FLOW_PROCESS_MIN int,FLOW_DELTA int)insert into @xxSELECT top 26 DATAFLOW, FLOW_PROCESS_MIN, FLOW_DELTA FROM DB.dbo.DataFlowStats (nolock)  ORDER BY AuditTimeStamp DESC UPDATE @xx SET FLOW_DELTA = 0 WHERE dataflow IN ('MQMSGMUREX','MQSEQMUREX')SELECT SUM (FLOW_PROCESS_MIN) as Processed, SUM (FLOW_DELTA) as Delta from @xx"

$obj = new-object System.objectadd-member -inputobject $obj -membertype NoteProperty -Name Processed -value $processed.Processed add-member -inputobject $obj -membertype NoteProperty -Name Delta -value $processed.Delta $obj

<Show us results>

$obj|get-member

<Show us results>

What are you intending on doing the data once you have the above object properly working?