Update-SQL should allow for -NoPSTypeName

[bryanbcook]

Not sure exactly when it is occurring, but I'm using Update-SQL to write a PSCustomObject to a table and the pstypename column is being dynamically added to my table.

My code looks something like this:

$item = @{}
$item.First = "First"
$item.Last = "Last"

$data = [pscustomobject]$item

$sqlArgs = @{
   ConnectionString = $MyConnectionString
   TableName = "MyTable"
   RowKey = "Id"
}

$data | Update-Sql @sqlArgs

What I suspect is happening is that I have a database constraint, such as a non-null, which is rejecting the insert. The try-catch logic in Update-SQL appears to attempt to alter the table and then try again.

How can I prevent the pstypename from being added?

[StartAutomating]

@bryanbcook

Your timing is good, as Update-SQL is getting some updates.

Update-SQL and Select-SQL are designed to give you a PowerShell-friendly object, so the .PSTypeName behavior is "by design".

You can work around this design in a simple enough way as is:

Without -Force, Update-SQL will not change the table you're creating.

So if you don't want a PSTypeNames column, don't create one.

Alternatively, if you wanted to create the table and then drop the PSTypeName column, you could do that and then avoid -Force in the future.

Now since neither of these solutions is ideal, I've retitled the issue to something like what I feel the solution should be:

Adding a parameter to "opt-out" of creating the PSTypeName column.

Does this reply help you understand the issue?
Does this proposal sound like it will meet your needs?
Do you have a better parameter name in mind?

[bryanbcook]

Without -Force, Update-SQL will not change the table you're creating.

I'm not using -Force and the column is being added.

[bryanbcook]

I see there's some updates based on the commit. Are these changes ready for consumption or are they still work in progress?