We've moved to Azure SQl and I am trying to run a SQL query via powershell my codes is not failing but it is not running anything.
我们已经转到Azure SQl,我正在尝试通过powershell运行SQl查询,我的代码没有失败,但它没有运行任何东西。
Set-DbatoolsInsecureConnection -SessionOnly
#$destcred = Get-Credential XXX
$User = "XXXX"
$PWord = ConvertTo-SecureString -String "XXXXX" -AsPlainText -Force
$destcred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $PWord
Connect-DbaInstance 'XXXX' -SqlCredential $destcred
$params = @{
Destination = 'XXXX'
DestinationDatabase = 'X'
DestinationSqlCredential = $destcred
KeepIdentity = $true
KeepNulls = $true
}
$Destination = 'XXXX'
$DestinationDatabase = 'XX'
$DestinationSqlCredential = $destcred
$ds = (Invoke-DbaQuery -SqlInstance $SqlInstance -Database $Database -SqlCredential $destcred -Query "Select count(*) as T from customer"|Select -Expand T -Verbose
)
Write-host $ds
更多回答
It is failing isn't a helpful problem statement. Are you getting an error? Unexpected results? Undesired behaviour? A server crash? Your fishtank is losing water as a result? Explain the problem.
“它失败了”并不是一个有用的问题陈述。你有错吗?意外结果?不受欢迎的行为?服务器崩溃?你的鱼缸因此而缺水?解释问题。
Make sure your destination and database are correct. Your query is not returning any data which usually means you are connecting to the wrong database.
请确保您的目的地和数据库是正确的。您的查询没有返回任何数据,这通常意味着您连接到了错误的数据库。
Jdweng - thanks I check and that was exactly the issue
Jdweng-谢谢,我检查了一下,这正是问题所在
优秀答案推荐
To query Azure SQL using PowerShell Invoke-dbaquery
使用PowerShell查询Azure SQL调用dbaquery
Try below sample code:
请尝试以下示例代码:
Set-DbatoolsInsecureConnection -SessionOnly
$User = "username"
$PWord = ConvertTo-SecureString -String "password" -AsPlainText -Force
$destcred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $PWord
$server = Connect-DbaInstance 'Servername.database.windows.net' -Database pd1 -SqlCredential $destcred
$params = @{
Destination = 'servername.database.windows.net'
DestinationDatabase = 'Databasename'
DestinationSqlCredential = $destcred
KeepIdentity = $true
KeepNulls = $true
}
$Destination = 'servername.database.windows.net'
$DestinationDatabase = 'databasename'
$DestinationSqlCredential = $destcred
$ds = (Invoke-DbaQuery -SqlInstance $server -Query "select * from tablename" -Verbose)
$ds
OUTPUT:
输出:
THanks for the helpful feedback in my panic I had simply not passed in the correct connection string
感谢我在恐慌中得到的有用反馈,我只是没有输入正确的连接字符串
Set-DbatoolsInsecureConnection -SessionOnly
#$destcred = Get-Credential XXXX
$User = "XXXX"
$PWord = ConvertTo-SecureString -String "XXX -AsPlainText -Force
$destcred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $PWord
Connect-DbaInstance 'XXXXX' -SqlCredential $destcred
$params = @{
Destination = 'XXXX'
DestinationDatabase = 'XXXX'
DestinationSqlCredential = $destcred
KeepIdentity = $true
KeepNulls = $true
}
$Destination = 'XXXX'
$DestinationDatabase = 'XXXX'
$DestinationSqlCredential = $destcred
$1820 = (Invoke-DbaQuery -SqlInstance $Destination -Database $DestinationDatabase -SqlCredential $destcred -Query "
THe SQLinstance destination and database in set in the @{ Parameter section of the code
在代码的@{Parameter部分中设置SQLinstance目标和数据库
更多回答
我是一名优秀的程序员,十分优秀!