Powershell SQL 服务器数据库连接和连接超时问题 [英] Powershell SQL server database connectivity and connection timeout issue

问题描述

我有一个连接到 SQL Server 2012 数据库的 powershell 脚本，该脚本运行 SQL 查询并将结果集放入数据表，以向相关方发送格式化的电子邮件.以下是问题所在的代码片段:

I've a powershell script connecting to SQL server 2012 database running a SQL query and result set into data table to send formatted email to relevant parties. Below is the code snippet where issue is:

$CBA = New-Object System.Data.DataSet "CBAData"
$sqlConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=DataSource;Initial Catalog=DataCatalog;Integrated Security = False;Connection Timeout=800;User ID = user; Password =pwd;")
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($CBAData, $sqlConn)
$adapter.Fill($CBA)

我在运行脚本时遇到以下错误:

I am getting below error running the script:

Exception calling "Fill" with "1" argument(s): "Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

我尝试将 SqlConnection 字符串的超时时间从最初设置的 360 逐渐增加到 800，但仍然存在相同的问题.有没有人深入了解这里究竟是什么问题?我怎样才能克服它?

I've tried increasing timeoutin SqlConnection string from initially set up 360 gradually and now upto 800 but still having same issue. Does anyone throw insight into what exactly issue is here? and How can I overome it?

提前致谢.

推荐答案

正如 OP 所提到的 - 默认命令执行超时为 30 秒.我在下面找到了:

As mentioned by OP - default command execution timeout is 30 seconds. I found below within:

SqlDataAdapter 类

SqlDataAdapter class

这将允许您增加命令执行超时(插入、更新、删除、选择命令).所以在我的情况下，下面的技巧做到了:

that would allow you to increase command execution timeout (insert, update, delete, select command). So in my case below did the trick:

$adapter.SelectCommand.CommandTimeout=60

$adapter.SelectCommand.CommandTimeout=60

希望这会有所帮助.

这篇关于Powershell SQL 服务器数据库连接和连接超时问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！