?
Solved

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

Posted on 2016-07-20
5
Medium Priority
?
158 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 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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 …
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

777 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