Invoke-SQLcmd在Invoke-command中不起作用
Invoke-SQLcmd在Invoke-command中不起作用
提问于 2019-02-06 17:09:07
我目前正在Invoke-Command脚本块中运行Invoke-SQLcmd，并且不断收到连接错误，Invoke-SQLcmd脚本在独立运行时可以正常工作，而不是包装在Invoke-Command中。
Invoke-Command -ComputerName "MyserverFQDN" -Credential $Serviceaccount -ScriptBlock {
Invoke-Sqlcmd -ServerInstance "SQLDBFQDN" -Database BizTalkMgmtDb -Query "
USE BizTalkMgmtDb
GO
SELECT bts_sendport.nvcName, bts_sendport_transport.nvcAddress, nPortStatus, bts_sendport_transport.dtFromTime,
bts_sendport_transport.bIsServiceWindow, bts_sendport_transport.dtToTime,
adm_Adapter.Name As TransportTypeID, bts_application.nvcName AS Application
FROM bts_sendport With(NOLOCK) INNER JOIN
bts_sendport_transport ON bts_sendport.nID = bts_sendport_transport.nSendPortID
INNER JOIN
bts_application ON bts_sendport.nApplicationID = bts_application.nID
INNER JOIN
adm_Adapter ON bts_sendport_transport.nTransportTypeId = adm_Adapter.Id
WHERE
nTransportTypeId IS NOT NULL
"
}然后我收到以下错误：
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL
Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
+ FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
+ PSComputerName : "MyServerFQDN"
ParserError: (:) [Invoke-Sqlcmd], ParserException
+ CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], ParserException
+ FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
+ PSComputerName : "MyserverFQDN"正如我所说的，我可以作为服务帐户从我的服务器运行SQLcmd，它工作得很好，但是如果我从我的PC调用它来调用我的服务器来使用我的服务帐户来运行SQLcmd，我会得到那个错误。你知道会发生什么或者怎么解决吗？
回答 1
Stack Overflow用户
发布于 2019-02-06 17:28:10
我认为问题在于脚本块中的$SQLDBFQDN变量是在一个临时会话中定义的，与您的不同(因为它在远程计算机上的另一个PowerShell进程中运行)。
To use local variable in a remote session，尝试通过用$Using:SQLDBFQDN替换$SQLDBFQDN来更改它的作用域
Invoke-Command -ComputerName $MyserverFQDN -Credential $Serviceaccount -ScriptBlock {
Invoke-Sqlcmd -ServerInstance $Using:SQLDBFQDN -Database BizTalkMgmtDb -Query "
USE BizTalkMgmtDb
GO
SELECT bts_sendport.nvcName, bts_sendport_transport.nvcAddress, nPortStatus, bts_sendport_transport.dtFromTime,
bts_sendport_transport.bIsServiceWindow, bts_sendport_transport.dtToTime,
adm_Adapter.Name As TransportTypeID, bts_application.nvcName AS Application
FROM bts_sendport With(NOLOCK) INNER JOIN
bts_sendport_transport ON bts_sendport.nID = bts_sendport_transport.nSendPortID
INNER JOIN
bts_application ON bts_sendport.nApplicationID = bts_application.nID
INNER JOIN
adm_Adapter ON bts_sendport_transport.nTransportTypeId = adm_Adapter.Id
WHERE
nTransportTypeId IS NOT NULL
"
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/54549984
复制相关文章
相似问题
腾讯云开发者
