Solved

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

Posted on 2016-07-20
5
129 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 85

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 85

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 85

Accepted Solution

by:
oBdA earned 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
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…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

707 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