Menu
I am trying to write to a table using Write-SqlTableData. The name is altered since it is a company database.
Environment:
Command:
Write-SqlTableData -DatabaseName 'dbname' -ServerInstance sql1 -TableName tablename -InputData (1,'1346',$false,'CalendarInvite') -Force -SchemaName "dbo"
Error: Write-SqlTableData : Input array is longer than the number of columns in this table.
Table columns:
I don't know why I get the error. Four values should be enough, and I give them in the column order.
I also tried to use a hash table, but the error is more confusing.
Hash table: @{Scope=1;ContextID='1346';FeatureEnabled=$false;Type='CalendarInvite'}
Error: Write-SqlTableData : The given value of type String from the data source cannot be converted to type int of the specified target column.
I am totally confused. What's wrong with my command?
Edit: The command works on my testing SQL (13.0.1601.5) but team testing SQL (11.0.3156.0). Maybe version matters?
220
@Matt @DSakura: So, I beat my head against the wall for a long time on this but finally figured it out... kind of. I even went as far as decompiling the assembly containing the Write-SqlTableData cmdlet (Microsoft.SqlServer.Management.PSSnapins).
Here's what I found:
It's a bug. Inside the code, the cmdlet tries to guess at what you're attempting to throw at it and then tries to reformat what you send into something that lines up with what SQL expects to receive.
It goes in this process:
.Net objects don't line up with SQL column types ([System.String] isn't a valid SQL type) so the cmdlet uses a conversion table to make a best guess (like maybe nvarchar(MAX)) then after it converts the types it tries to dump it into a table.
This is easier when you use the "-force" parameter to create a brand new table because it doesn't have to match up what you're trying to input with columns that already exist in an existing table. It just makes a best guess and changes the data type to something SQL understands and goes.
Examples:
Hash Table
Write-SQLTableData -TableName TableHash -SchemaName dbo -DatabaseName DB1 -ServerInstance localhost\SQLExpress -InputData @{col1="SomeString"} -force
Produces:
-------------------------- | | Key | Value | -------------------------- | 1 | col1 | SomeString | --------------------------
With types:
------------------------------------------- | Column Name | Data Type | Allow Nulls | ------------------------------------------- | [Key] | sql_varient | | ------------------------------------------- | Value | sql_varient | | -------------------------------------------
PSObject
$Obj = New-Object PSObject -Property @{col1="SomeString"}
Write-SQLTableData -TableName TablePSObject -SchemaName dbo -DatabaseName DB1 -ServerInstance localhost\SQLExpress -InputData $Obj -force
Produces:
------------------- | | col1 | ------------------- | 1 | SomeString | -------------------
With type:
--------------------------------------------- | Column Name | Data Type | Allow Nulls | --------------------------------------------- | col1 | nvarchar(MAX) | TRUE | ---------------------------------------------
So, here's where it gets complicated. When you have an existing table, the cmdlet does its best to line up your data with the table columns. It's not that good at it yet. The default example that the documentation provides shows how to do this by putting your values in a hash table with the column name as the hash keys. THIS DOES NOT WORK FOR EXISTING TABLES Official documentation here: https://learn.microsoft.com/en-us/powershell/module/sqlserver/write-sqltabledata?view=sqlserver-ps
Existing Table Fails Example
Existing Table:
--------------------------------------------- | Column Name | Data Type | Allow Nulls | --------------------------------------------- | col1 | varchar(MAX) | FALSE | --------------------------------------------- | col2 | Text | FALSE | ---------------------------------------------
PowerShell command:
Write-SQLTableData -TableName TableExistsAlready -SchemaName dbo -DatabaseName DB1 -ServerInstance localhost\SQLExpress -InputData @{col1="SomeText";col2="SomeMoreText"}
Write-SQLTableData -TableName TableExistsAlready -SchemaName dbo -DatabaseName DB1 -ServerInstance localhost\SQLExpress -InputData @{col1="SomeText2";col2="SomeMoreText2"}
Produces:
----------------------------- | | col1 | col2 | ----------------------------- | 1 | col2 | SomeMoreText | ----------------------------- | 2 | col1 | SomeText | ----------------------------- | 3 | col2 | SomeMoreText2 | ----------------------------- | 4 | col1 | SomeText2 | -----------------------------
Needless to say, this is not what we were expecting.
It gets even worse when you have more complicated data types you're trying to put into the table as then even if you get the right types lined up in the hash table (strings with strings, ints with ints, etc), it will never line up the columns correctly during the writing to SQL phase.
So, what's the solution?
Here it is... The code is actually written to handle the type [PSCustomObject].
If you wish to go over the detailed differences between PSObject, Hash and PSCustomObject types, refer to this: Difference between PSObject, Hashtable and PSCustomObject
So, now that we know what we need, it's also important to note that Hash tables don't set order of items (at least not by default). However, you can specify the order of PSObject type, but you can't use the quick "hash" creation method. You have to define each NoteProperty individually. See: Change order of columns in the object
However, since PowerShell V3, Microsoft added a type accelerator for [PSCustomObject] that creates objects using an ordered hash table, so the properties stay in the order they're declared. Lucky for us, that's the type we want.
So, let's do this again:
Existing Table Works Example
Existing Table (Note, I left the original data intact):
--------------------------------------------- | Column Name | Data Type | Allow Nulls | --------------------------------------------- | col1 | varchar(MAX) | FALSE | --------------------------------------------- | col2 | Text | FALSE | ---------------------------------------------
PowerShell command:
$Obj = [PSCustomObject] @{col1="SomeText";col2="SomeMoreText"}
Write-SQLTableData -TableName TableExistsAlready -SchemaName dbo -DatabaseName DB1 -ServerInstance localhost\SQLExpress -InputData $Obj
$Obj2 = [PSCustomObject] @{col1="SomeText2";col2="SomeMoreText2"}
Write-SQLTableData -TableName TableExistsAlready -SchemaName dbo -DatabaseName DB1 -ServerInstance localhost\SQLExpress -InputData $Obj2
Produces:
---------------------------------- | | col1 | col2 | ---------------------------------- | 1 | col2 | SomeMoreText | ---------------------------------- | 2 | col1 | SomeText | ---------------------------------- | 3 | col2 | SomeMoreText2 | ---------------------------------- | 4 | col1 | SomeText2 | ---------------------------------- | 5 | SomeText | SomeMoreText | ---------------------------------- | 6 | SomeText2 | SomeMoreText2 | ----------------------------------
Horray! It's actually in the right order! Now, don't forget, if you have a column that requires an int, you need to make sure your input lines up with what it's expecting. I'll put at the bottom the conversion table the cmdlet uses to compare types. I disassembled the code from the compiled dll, so no guarantees on exact accuracy.
So, now let's move to the "id" (auto incrementing identity specification) column issue. If you don't add the column in the PSCustomObject, your SQL write gets rejected. However, NO PROBLEM! Turns out we can add the id column name and just leave the value as a blank and it gets translated in a way that SQL will just put the next value in.
ID Column Example
Existing Table (Note, this is a new blank table with just columns and no row data):
--------------------------------------------- | Column Name | Data Type | Allow Nulls | --------------------------------------------- | id | int | FALSE | --------------------------------------------- | col1 | varchar(50) | FALSE | --------------------------------------------- | col2 | Text | FALSE | --------------------------------------------- Note: "id" column has Identity Specification (Is Identity) turned to "Yes" with Identity Increment set to 1 and Identity Seed set to 1.
PowerShell command:
$Obj = [PSCustomObject] @{
id=''
col1="SomeString"
col2="SomeMoreString"
}
Write-SQLTableData -TableName TableWithID -SchemaName dbo -DatabaseName DB1 -ServerInstance localhost\SQLExpress -InputData $Obj
Produces:
-------------------------------------- | | id | col1 | col2 | -------------------------------------- | 1 | 1 | SomeText | SomeMoreText | --------------------------------------
What did you say? What happens when you do the command four more times? I'm glad you asked: PowerShell command:
$Obj = [PSCustomObject] @{
id=''
col1="SomeString"
col2="SomeMoreString"
}
Write-SQLTableData -TableName TableWithID -SchemaName dbo -DatabaseName DB1 -ServerInstance localhost\SQLExpress -InputData $Obj
Write-SQLTableData -TableName TableWithID -SchemaName dbo -DatabaseName DB1 -ServerInstance localhost\SQLExpress -InputData $Obj
Write-SQLTableData -TableName TableWithID -SchemaName dbo -DatabaseName DB1 -ServerInstance localhost\SQLExpress -InputData $Obj
Write-SQLTableData -TableName TableWithID -SchemaName dbo -DatabaseName DB1 -ServerInstance localhost\SQLExpress -InputData $Obj
Produces:
-------------------------------------- | | id | col1 | col2 | -------------------------------------- | 1 | 1 | SomeText | SomeMoreText | -------------------------------------- | 2 | 2 | SomeText | SomeMoreText | -------------------------------------- | 3 | 3 | SomeText | SomeMoreText | -------------------------------------- | 4 | 4 | SomeText | SomeMoreText | -------------------------------------- | 5 | 5 | SomeText | SomeMoreText | --------------------------------------
Well... That took longer than I thought. I hope this helps others.
Here's that conversion table:
Type Conversion Table
case "System.String":
return DataType.NVarCharMax;
case "System.Int64":
return DataType.BigInt;
case "System.Byte[]":
return DataType.VarBinaryMax;
case "System.Boolean":
return DataType.Bit;
case "System.Char":
return DataType.NChar(1);
case "System.DateTime":
return DataType.DateTime2(7);
case "System.DateTimeOffset":
return DataType.DateTimeOffset(7);
case "System.Decimal":
return DataType.Decimal(14, 0x1c);
case "System.Double":
return DataType.Float;
case "System.Int32":
return DataType.Int;
case "System.Char[]":
return DataType.NVarCharMax;
case "System.Single":
return DataType.Real;
case "System.Int16":
return DataType.SmallInt;
case "System.Object":
return DataType.Variant;
case "System.TimeSpan":
return DataType.Timestamp;
case "System.Byte":
return DataType.TinyInt;
case "System.Guid":
return DataType.UniqueIdentifier;
case "System.UInt64":
return DataType.Numeric(0, 20);
case "System.UInt32":
return DataType.BigInt;
case "System.UInt16":
return DataType.Int;
case "System.SByte":
return DataType.SmallInt;
case "Microsoft.SqlServer.Types.SqlHierarchyId":
return DataType.HierarchyId;
case "Microsoft.SqlServer.Types.SqlGeography":
return DataType.Geography;
case "Microsoft.SqlServer.Types.SqlGeometry":
return DataType.Geometry;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
