[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

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...
Measuring Server's processing rate with a simple powershell command. The differences in processing rate also was recorded in different use-cases, when a server in free and busy states.
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

873 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