Powershell SQL server database connectivity and connection timeout issue

Question

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."

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?

Thank you in advance.

Answer 1

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

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

Hope this helps.

Answer 2

In addition to Zulfiqar's answer, which led me in the right direction. For me, the solution was in the SqlCommand. The error I was getting when setting the property of the adapter was as follows:

The property 'CommandTimeout' cannot be found on this object. Verify that the property exists and can be set.

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.CommandTimeout = 60;