Powershell的write-sqltabledata错误

问题描述

我正在尝试将csv文件导入到Azure数据库中的sql表中.

错误是"Write-SqlTableData:无法访问目标表[数据库].[dbo].[表名]"

我可以用read-sqltabledata读取表.

I'm trying to import a csv file into a sql table on an azure database.

The error is "Write-SqlTableData : Cannot access destination table [database].[dbo].[tablename]"

I can read the table with read-sqltabledata just fine.

如果我在write-sqltabledata命令上指定-force参数并使用新的表名，则将使用输入中的所有字段创建该表，但实际数据导入会失败，并出现与上述相同的错误.

If I specify the -force parameter on the write-sqltabledata command and use a new table name, the table is created with all the fields from the input, but the actual data import fails with the same error as above.

我可以使用invoke-sqlcmd来插入[tablename]".添加行，所以我认为这不是权限问题.

$ events = import-csv.\ mycsv.csv"； |超出数据表
Write-SqlTableData -DatabaseName'database'-SchemaName'dbo'-TableName'tablename'-Credential $ cred  -serverinstance'xxxxxx.database.windows.net'-InputData $ events

I can use invoke-sqlcmd to "Insert into [tablename]" to add rows, so I don't think it is a permissions problem.

$events = import-csv ".\mycsv.csv" | out-datatable
Write-SqlTableData -DatabaseName 'database' -SchemaName 'dbo' -TableName 'tablename' -Credential $cred  -serverinstance 'xxxxxx.database.windows.net' -InputData $events 

输出为:

这里可能出什么问题了?
下一步要测试/尝试什么?

What's possibly wrong here?
What would be the next thing to test/try?

推荐答案

示例:

Write-SqlTableData -serverInstance localhost \ sql2016-数据库Test2 -TableName TestInsert -SchemaName dbo -InputData @ {01 ='abc'; 02 ='xyz'} -Pa ssThru

写入SqlTableData

这是cmdlet链接中的PowerShell示例(示例#3):

Here is the PowerShell example (example #3) from the cmdlet link:

PS C:\> ,(Import-Csv -Path ".\a.csv" -Header "Id","Name","Amount") | Write-SqlTableData -ServerInstance "MyServer\MyInstance" -DatabaseName "MyDatabase" -SchemaName "dbo" -TableName "CSVTable" -Force
PS C:\> Read-SqlTableData -ServerInstance "MyServer\MyInstance" -DatabaseName "MyDatabase" -SchemaName "dbo" -TableName "CSVTable"

Id Name  Amount
-- ----  ------
10 AAAA  -1.2
11 BBBB   1.2
12 CCCC  -1.0

The first command imports the contents of a file by using the Import-Csv cmdlet. The file contains the following content:
    
10,AAAA,-1.2
11,BBBB,1.2
12,CCCC,-1.0
    
This example runs completely from the file prompt. It cannot use context information. Therefore, you must specify all relevant parameters.

Note the use of the "," in front of the line: this is to force PowerShell to pass the entire content of the file directly to
the **Write-SqlTableData** cmdlet, which in turn can do a bulk-insert (which is way more performant than a row by row insert)

我希望这会有所帮助！

此致

迈克

这篇关于Powershell的write-sqltabledata错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！