?
Solved

Cannot convert the "Microsoft.PowerShell.Commands.Internal.Format.FormatStartData" value of type

Posted on 2016-07-20
5
Medium Priority
?
352 Views
Last Modified: 2016-07-21
Hello

The following error is generated if I try to execute  :

$servername="TEST\TEST"
[Microsoft.SqlServer.Management.Smo.Server]$server = New-Object "Microsoft.SqlServer.Management.Smo.Server" $servername
[Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$found_tables = @()
$found_tables = $db.ExecuteWithResults("select object_name from [DBA].[dbo].[ddl_configuration] where new_fg_name <> 'NULL' and  new_fg_name <> ''");

Open in new window


Cannot convert the "Microsoft.PowerShell.Commands.Internal.Format.FormatStartData" value of type
"Microsoft.PowerShell.Commands.Internal.Format.FormatStartData" to type
"Microsoft.SqlServer.Management.Smo.SqlSmoObject".
At D:\MSSQLSERVER\scripts\DBA\SqlScriptGenerationHelperScripts\ScriptGenerationMethods.ps1:105 char:9
+         $found_tables = $tables.Tables[0] | Format-Table
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [], RuntimeException
    + FullyQualifiedErrorId : ConvertToFinalInvalidCastException

How can I resolve?

Thanks

Regards
0
Comment
Question by:bibi92
  • 3
  • 2
5 Comments
 
LVL 86

Expert Comment

by:oBdA
ID: 41721381
The line were the error occurred is not included in your code block.
Anyway, use a different variable to assign the values retrieved from the DB to; you've  explicitly defined $found_tables as [Microsoft.SqlServer.Management.Smo.SqlSmoObject[].
And shouldn't that be "... = $found_tables.Tables[0] ..." instead of "... = $tables.Tables[0] ...", or is that just the missing part between the block you posted and the actual error location?
$formatted_table = $tables.Tables[0] | Format-Table

Open in new window

Try this to reproduce the error you're getting:
[byte[]]$b = 1, 2, 3
$b = 'Hello World'

Open in new window


And you're aware that you can't do a whole lot with this $formatted_table? The Format-cmdlets should basically only be used to generate console or text file output. The results are not usable anymore in that you can't access the properties they had before the Format-.
0
 

Author Comment

by:bibi92
ID: 41721417
Yes I have forgotten to copy it but the type is different
0
 
LVL 86

Expert Comment

by:oBdA
ID: 41721432
What do you mean with "but the type is different"?
Whatever is missing, simple fact is that you can't assign the output of Format-Table (or anything other than an SqlSmoObject) to a variable you explicitly defined as [Microsoft.SqlServer.Management.Smo.SqlSmoObject[], which is what you are trying to do.
0
 

Author Comment

by:bibi92
ID: 41721437
Cannot convert the "Microsoft.PowerShell.Commands.Internal.Format.FormatStartData" value of type
 "Microsoft.PowerShell.Commands.Internal.Format.FormatStartData" to type
 "Microsoft.SqlServer.Management.Smo.SqlSmoObject".
$found_tables = SMO but $db.ExecuteWithResults is StartData
0
 
LVL 86

Accepted Solution

by:
oBdA earned 2000 total points
ID: 41721454
Again:
You explicitly defined $found_tables as an array of [Microsoft.SqlServer.Management.Smo.SqlSmoObject].
The output of Format-Table is an array of Microsoft.PowerShell.Commands.Internal.Format, so you can not assign this output to $found_tables.
Use a different variable like I suggested in my first comment.
And copy and paste the second code block in said comment into a PS console to see what's happening.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
You have missed a phone call. The number looks like it belongs to the bunch of numbers which your company uses. How to find out who has just called you?
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Loops Section Overview
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question