write-datatable - having problems getting start-job to return data in the correct format to use it

Ask a question

Thursday, December 13, 2012 7:56 PM

0

(posting here, since I figure write-datatable is better known here than on stackoverflow)

I'm trying to run code against multiple servers in parallel, using start-job, then writing the results to a table.

As of right now, I can run the code in parallel, have it returned into a variable (array?). However, I can't get write-datatable to work. I think it's just a matter of datatypes, but I'm not sure.

Here's the error:
Write-DataTable : System.Management.Automation.MethodException: Cannot convert argument "0", with value: "System.Object[]", for "WriteToServer" to type "System.Data.DataRow[]": "Cannot convert the "@{servername=MEM-PR-ITDB-02; thedate=12/13/2012 1:51:25 PM}"
value of type "Selected.System.Management.Automation.PSCustomObject" to type "System.Data.DataRow"." ---> System.Management.Automation.PSInvalidCastException: Cannot convert the "@{servername=MEM-PR-ITDB-02; thedate=12/13/2012 1:51:25 PM}" value of type "Sele
cted.System.Management.Automation.PSCustomObject" to type "System.Data.DataRow".

And here's the code:
$serverlist = invoke-sqlcmd2 -serverinstance "myrepositoryserver" -query "SELECT server FROM dba.dbo.list_of_servers WHERE active = 1"
foreach ($server in $serverlist)
{
start-job -argumentlist $server.server -scriptblock `
{
. c:\sql_tools\invoke-sqlcmd2.ps1;
invoke-sqlcmd2 -serverinstance $args[0] -database "master" `
-query "select top 1 @@servername as servername, getdate() as thedate from sys.databases"
}
}

get-job | wait-job -timeout 10 #wait 10 seconds or until all jobs are done, whichever comes first
$dt = get-job | receive-job #save details of jobs into $dt
$dt2 = New-Object system.Data.DataTable “$Datatable” #trying to create a data table
$dt2 = $dt|select-object servername, thedate #removing the -As 'Datatable' allows it to work
Write-DataTable -ServerInstance "mydatarepository" -Database dba -TableName blahtemp -Data $dt2
get-job|Remove-Job #cleanup and remove the jobs
- Reply
- Quote

All Replies

Thursday, December 13, 2012 9:09 PM

0
YOu are retriving a rowset and trying to use it like an array or collectio. WHeil thei is possible it cannot be dereferences as a PowerShell object.

This is the line I suspect is complaining.

foreach ($server in $serverlist)

Try doing this and next timepaste thee complete error including code template and line numbers.
```
$serverlist=invoke-sqlcmd2 -serverinstance "myrepositoryserver" -query "SELECT server FROM dba.dbo.list_of_servers WHERE active = 1" 
foreach ($server in $serverlist){
     $server.server
}
```
Just that piece of code then build up a line at a time. It looks like that should be the error condition. $server.server is not correct.

¯\_(ツ)_/¯
- Reply
- Quote
Thursday, December 13, 2012 9:53 PM

0

No, that part works just fine. Leave out the write-datatable line and it works just fine. If I replace the entire line with "write-datatable" on it, with "$dt" (so it returns the results, I get this:

RunspaceId : 30debc17-807e-4838-955f-e6f8ca94f707
servername : oneofmyservers
thedate : 12/13/2012 3:40:25 PM

I've changed the write-datatable line slightly, since both ways give me different errors.

CREATE TABLE blahtemp (runspaceid UNIQUEIDENTIFIER, servername sysname, thedate DATETIME)
CREATE TABLE blahtemp2 (servername sysname, thedate DATETIME)

I ran it once for each write-datatable line.

Here's the full error I get trying to invoke the script with the write-datatable line:

Write-DataTable -ServerInstance "mydatarepository" -Database dba -TableName blahtemp -Data $dt2

Write-DataTable : System.Management.Automation.MethodException: Cannot convert argument "0", with value: "System.Object[]", for "WriteToServer" to type "System.Data.DataRow[]": "Cannot convert the "@{servername=firstservernamehere; thedate=12/13/2012 3:51:32 PM}"
value of type "Selected.System.Management.Automation.PSCustomObject" to type "System.Data.DataRow"." ---> System.Management.Automation.PSInvalidCastException: Cannot convert the "@{servername=firstservernamehere; thedate=12/13/2012 3:51:32 PM}" value of type "Sele
cted.System.Management.Automation.PSCustomObject" to type "System.Data.DataRow".
at System.Management.Automation.LanguagePrimitives.ConvertTo(Object valueToConvert, Type resultType, Boolean recursion, IFormatProvider formatProvider, TypeTable backupTypeTable)
at System.Management.Automation.LanguagePrimitives.ConvertUnrelatedArrays(Object valueToConvert, Type resultType, Boolean recursion, PSObject originalValueToConvert, IFormatProvider formatProvider, TypeTable backupTable)
at System.Management.Automation.LanguagePrimitives.ConvertTo(Object valueToConvert, Type resultType, Boolean recursion, IFormatProvider formatProvider, TypeTable backupTypeTable)
at System.Management.Automation.Adapter.PropertySetAndMethodArgumentConvertTo(Object valueToConvert, Type resultType, IFormatProvider formatProvider)
at System.Management.Automation.Adapter.MethodArgumentConvertTo(Object valueToConvert, Boolean isParameterByRef, Int32 parameterIndex, Type resultType, IFormatProvider formatProvider)
at System.Management.Automation.Adapter.SetNewArgument(String methodName, Object[] arguments, Object[] newArguments, ParameterInformation parameter, Int32 index)
--- End of inner exception stack trace ---
at System.Management.Automation.Adapter.SetNewArgument(String methodName, Object[] arguments, Object[] newArguments, ParameterInformation parameter, Int32 index)
at System.Management.Automation.Adapter.GetMethodArgumentsBase(String methodName, ParameterInformation[] parameters, Object[] arguments, Boolean expandParamsOnBest)
at System.Management.Automation.Adapter.GetBestMethodAndArguments(String methodName, MethodInformation[] methods, Object[] arguments, Object[]& newArguments)
at System.Management.Automation.DotNetAdapter.MethodInvokeDotNet(String methodName, Object target, MethodInformation[] methodInformation, Object[] arguments)
at System.Management.Automation.DotNetAdapter.MethodInvoke(PSMethod method, Object[] arguments)
at System.Management.Automation.Adapter.BaseMethodInvoke(PSMethod method, Object[] arguments)
at System.Management.Automation.PSMethod.Invoke(Object[] arguments)
at System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String methodName, Object[] paramArray, Boolean callStatic, Object valueToSet)
at System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] arguments, Object value)
at System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)
at System.Management.Automation.ParseTreeNode.Execute(Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)
at System.Management.Automation.StatementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context).Message
At line:18 char:16
+ Write-DataTable <<<< -ServerInstance "myrepositoryserver" -Database eif_workspace -TableName blahtemp -Data $dt2
+ CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Write-DataTable

If I use this write statement instead:

Write-DataTable -ServerInstance "ftw-test-08" -Database eif_workspace -TableName blahtemp -Data $dt

I get

Write-DataTable : System.Management.Automation.MethodException: Cannot convert argument "0", with value: "System.Object[]", for "WriteToServer" to type "System.Data.DataRow[]": "Cannot convert the "System.Data.DataRow" value of type "Deserialized.System.Data.
DataRow" to type "System.Data.DataRow"." ---> System.Management.Automation.PSInvalidCastException: Cannot convert the "System.Data.DataRow" value of type "Deserialized.System.Data.DataRow" to type "System.Data.DataRow".
at System.Management.Automation.LanguagePrimitives.ConvertTo(Object valueToConvert, Type resultType, Boolean recursion, IFormatProvider formatProvider, TypeTable backupTypeTable)
at System.Management.Automation.LanguagePrimitives.ConvertUnrelatedArrays(Object valueToConvert, Type resultType, Boolean recursion, PSObject originalValueToConvert, IFormatProvider formatProvider, TypeTable backupTable)
at System.Management.Automation.LanguagePrimitives.ConvertTo(Object valueToConvert, Type resultType, Boolean recursion, IFormatProvider formatProvider, TypeTable backupTypeTable)
at System.Management.Automation.Adapter.PropertySetAndMethodArgumentConvertTo(Object valueToConvert, Type resultType, IFormatProvider formatProvider)
at System.Management.Automation.Adapter.MethodArgumentConvertTo(Object valueToConvert, Boolean isParameterByRef, Int32 parameterIndex, Type resultType, IFormatProvider formatProvider)
at System.Management.Automation.Adapter.SetNewArgument(String methodName, Object[] arguments, Object[] newArguments, ParameterInformation parameter, Int32 index)
--- End of inner exception stack trace ---
at System.Management.Automation.Adapter.SetNewArgument(String methodName, Object[] arguments, Object[] newArguments, ParameterInformation parameter, Int32 index)
at System.Management.Automation.Adapter.GetMethodArgumentsBase(String methodName, ParameterInformation[] parameters, Object[] arguments, Boolean expandParamsOnBest)
at System.Management.Automation.Adapter.GetBestMethodAndArguments(String methodName, MethodInformation[] methods, Object[] arguments, Object[]& newArguments)
at System.Management.Automation.DotNetAdapter.MethodInvokeDotNet(String methodName, Object target, MethodInformation[] methodInformation, Object[] arguments)
at System.Management.Automation.DotNetAdapter.MethodInvoke(PSMethod method, Object[] arguments)
at System.Management.Automation.Adapter.BaseMethodInvoke(PSMethod method, Object[] arguments)
at System.Management.Automation.PSMethod.Invoke(Object[] arguments)
at System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String methodName, Object[] paramArray, Boolean callStatic, Object valueToSet)
at System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] arguments, Object value)
at System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)
at System.Management.Automation.ParseTreeNode.Execute(Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)
at System.Management.Automation.StatementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context).Message
At line:19 char:16
+ Write-DataTable <<<< -ServerInstance "myrespositoryserver" -Database dba -TableName blahtemp -Data $dt
+ CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Write-DataTable
- Reply
- Quote
Thursday, December 13, 2012 10:39 PM

0

Run it without a job and try and look at all of the objects. The message indicates a type mismatch.

¯\_(ツ)_/¯
- Reply
- Quote
Thursday, December 13, 2012 10:56 PM

0
While I'm still really curious why it won't work, (and will happily vote up anybody who I have a workaround for now. This code works.

CREATE TABLE blahtemp2 (servername sysname, thedate DATETIME)

$serverlist = invoke-sqlcmd2 -serverinstance "myrepositoryserver" -query "SELECT server FROM dba.dbo.Servers WHERE active = 1"
foreach ($server in $serverlist)
{
start-job -argumentlist $server.server -scriptblock `
{
. c:\sql_tools\invoke-sqlcmd2.ps1;
. C:\sql_tools\write-datatable.ps1;
$quer = invoke-sqlcmd2 -serverinstance $args[0] -database "master" `
-query "select top 1 @@servername as servername, getdate() as thedate from sys.databases" -As 'DataTable'
Write-DataTable -ServerInstance "myrepositoryserver" -Database "dba" -TableName "blahtemp2" -Data $quer
}
}

get-job | wait-job -timeout 10 #wait 10 seconds or until all jobs are done, whichever comes first
get-job|Remove-Job -force #cleanup and remove the jobs
- Marked As Answer by mbourgon Monday, February 11, 2013 2:44 PM
- Reply
- Quote
Friday, December 14, 2012 2:53 AM

0

I swear it's something with the way receive-job returns data. If I try to pipe $dt to format-table, select-object, out-gridview, it's always the metadata and not the data.
- Reply
- Quote
Friday, December 14, 2012 3:43 AM

0
I swear it's something with the way receive-job returns data. If I try to pipe $dt to format-table, select-object, out-gridview, it's always the metadata and not the data.

Unfortuantely I cannot see what you are doing.

We do not have your schema or any of your setup. The error is one that usually applies to type mismatch with field caused by sending object to an argument when it expects a system value.

¯\_(ツ)_/¯
- Marked As Answer by Bill_StewartMicrosoft Community Contributor, Moderator Wednesday, January 23, 2013 4:47 PM
- Reply
- Quote
Friday, December 14, 2012 4:07 AM

0

Ah, okay. Let me come up with a stripped down case that can be run. Thanks!
- Reply
- Quote
Friday, December 14, 2012 5:12 AM

0

If you can get it simple enough I can load it and better inspect what is happening.

Look over each object carefully. Compare working case objects with failing case objects.

¯\_(ツ)_/¯
- Reply
- Quote
Friday, December 14, 2012 6:18 AM

0
Okay, new test case, stripped down as much of the code as I could, and added schema. All that you need that isn't here are the invoke-sqlcmd2 and write-datatable scripts, and a SQL Server instance.

create database dba

go

use dba

go

CREATE TABLE blahtemp (RunspaceId varchar(100), servername VARCHAR(100), thedate VARCHAR(100))

CREATE TABLE servers (id int identity, server sysname)

insert into servers (server) values ('yourserver')

powershell:

. C:\sql_tools\write-datatable.ps1;
. c:\sql_tools\invoke-sqlcmd2.ps1;

$serverlist = invoke-sqlcmd2 -serverinstance "yourserver" -query "SELECT server FROM dba.dbo.servers"
$serverlist
start-job -argumentlist $serverlist.server -scriptblock `
{
. c:\sql_tools\invoke-sqlcmd2.ps1;
invoke-sqlcmd2 -serverinstance $args[0] -database "master" `
-query "select top 1 @@servername as servername, getdate() as thedate from sys.databases"
}

get-job|wait-job
$dt = get-job | receive-job
get-job|Remove-Job #cleanup and remove the jobs
$dt
Write-DataTable -ServerInstance "yourserver" -Database dba -TableName blahtemp -Data $dt

And the error I now get:
Write-DataTable : System.Management.Automation.MethodException: Cannot find an overload for "WriteToServer" and the arg
ument count: "1".
at System.Management.Automation.Adapter.FindBestMethod(String methodName, MethodInformation[] methods, Object[] argu
ments, Boolean& expandParamsOnBest)
at System.Management.Automation.Adapter.GetBestMethodAndArguments(String methodName, MethodInformation[] methods, Ob
ject[] arguments, Object[]& newArguments)
at System.Management.Automation.DotNetAdapter.MethodInvokeDotNet(String methodName, Object target, MethodInformation
[] methodInformation, Object[] arguments)
at System.Management.Automation.DotNetAdapter.MethodInvoke(PSMethod method, Object[] arguments)
at System.Management.Automation.Adapter.BaseMethodInvoke(PSMethod method, Object[] arguments)
at System.Management.Automation.PSMethod.Invoke(Object[] arguments)
at System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String methodName, Object[] paramAr
ray, Boolean callStatic, Object valueToSet)
at System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] arguments, Object value)
at System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)
at System.Management.Automation.ParseTreeNode.Execute(Array input, Pipe outputPipe, ArrayList& resultList, Execution
Context context)
at System.Management.Automation.StatementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe output
Pipe, ArrayList& resultList, ExecutionContext context).Message
At line:1 char:16
+ Write-DataTable <<<< -ServerInstance "ftw-test-08" -Database eif_workspace -TableName blahtemp -Data $dt
+ CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Write-DataTable
- Edited by mbourgon Friday, December 14, 2012 6:19 AM added error code
- Marked As Answer by mbourgon Thursday, January 24, 2013 2:06 PM
- Unmarked As Answer by mbourgon Thursday, January 24, 2013 2:07 PM
- Reply
- Quote

write-datatable - having problems getting start-job to return data in the correct format to use it

All Replies

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support by product

Other support links

Not an IT pro?