我正在尝试使用 Write-SqlTableData 写入表。名称已更改,因为它是公司数据库。

环境:

  • Windows 8.1 和 Powershell 5.0,SqlServer 模块 21.0.17099

  • 命令:
    Write-SqlTableData -DatabaseName 'dbname' -ServerInstance sql1 -TableName tablename -InputData (1,'1346',$false,'CalendarInvite') -Force -SchemaName "dbo"
    错误:Write-SqlTableData : Input array is longer than the number of columns in this table.
    表格列:
  • ID:int 身份
  • 范围:int
  • ContextID: ntext
  • FeatureEnabled: 位
  • 类型:ntext

  • 我不知道为什么我收到错误。四个值应该足够了,我按列顺序给出它们。

    我也尝试过使用哈希表,但错误更令人困惑。
  • 哈希表:@{Scope=1;ContextID='1346';FeatureEnabled=$false;Type='CalendarInvite'}
  • 错误:Write-SqlTableData : The given value of type String from the data source cannot be converted to type int of the specified target column.

  • 我完全糊涂了。我的命令有什么问题?

    编辑:
    该命令适用于我的测试 SQL (13.0.1601.5) 但团队测试 SQL (11.0.3156.0)。也许版本很重要?

    最佳答案

    @Matt @DSakura:所以,我在这件事上用头撞墙了很长时间,但终于想通了......有点。我什至反编译了包含 Write-SqlTableData cmdlet (Microsoft.SqlServer.Management.PSSnapins) 的程序集。

    这是我发现的:

    这是一个错误。
    在代码内部,cmdlet 会尝试猜测您尝试向其抛出的内容,然后尝试将您发送的内容重新格式化为与 SQL 期望接收的内容一致的内容。

    它在这个过程中:

    1 将哪个 .Net 对象发送给我?

  • 是 SQL 数据集吗?
  • 是 SQL 数据表吗?
  • 是 SQL 数据行吗?
  • 是其他类型的时髦对象吗?

  • 2 如果是时髦的对象,我可以把它变成可以放入 SQL 表的东西吗?

    .Net 对象不符合 SQL 列类型([System.String] 不是有效的 SQL 类型),因此 cmdlet 使用转换表进行最佳猜测(例如 nvarchar(MAX)),然后在转换后它试图将其转储到表中的类型。

    当您使用“-force”参数创建一个全新的表时,这会更容易,因为它不必将您尝试输入的内容与现有表中已存在的列相匹配。它只是做出最好的猜测,并将数据类型更改为 SQL 能够理解并执行的内容。

    例子:

    哈希表
    Write-SQLTableData -TableName TableHash -SchemaName dbo -DatabaseName DB1 -ServerInstance localhost\SQLExpress -InputData @{col1="SomeString"} -force
    

    产生:
    --------------------------
    |    | 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
    

    产生:
    -------------------
    |    | 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 TABLESOfficial documentation here: https://docs.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"}
    

    产生:
    -----------------------------
    |    | 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
    

    产生:
    ----------------------------------
    |    | 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
    

    产生:
    --------------------------------------
    |    | 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
    

    产生:
    --------------------------------------
    |    | 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;
    

    10-07 19:08
    查看更多