Styjun

Speaking German abroad and feeling condescended to when people speak English back to me

Iodine tablet correct use and efficiency

What's an "add" chord?

What does the 1.-5. notation mean at the top right corner?

How does sudo handle $HOME differently since 19.10?

Would nuclear bombs be effective against clouds of nanites?

Need Good OOP Design For World and Countries Problem

Do physicists understand why time slows down the faster the velocity of an object?

At what point in time would humans notice a 21st century satellite observing them?

Musig Signature Interactivity

Dissecting the exotic bulbfish

How do the Martian rebels defeat Earth when they're grossly outnumbered and outgunned?

What do you mean by particle can be in two places at once?

Very high precision zero crossing detection

Which act of Congress authorized the Ukrainian aid which was allegedly withheld?

Why are boost/buck converters never fully integrated/all-in-one?

Unexpected Code Coverage Reduction

Is it sportsmanlike to waste opponents' time by giving check at the end of the game?

Giving a talk on a different topic than what we discussed

Decision problems for which it is unknown whether they are decidable

When does Scandinavian Mountains Airport open?

50% portfolio in single stock, JPM - appropriate for 80 year old?

Given an array A[] and a number x, check for pair in A[] with sum as x

Is policy routing bad?

Timeout Error on Powershell script querying SQL DB - Execution Timeout Expired. The timeout period

Inserting multiple rows in a single SQL query?How to avoid Sql Query TimeoutSQL update query using joinsTerminating a script in PowerShellHow to run a PowerShell scriptPowerShell says “execution of scripts is disabled on this system.”Load Dataset to SQL script (in powershell)SQL timeout expired on fast querySQL Timeout Expired When It Shouldn'tPowershell script to set dead lock priority

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty
margin-bottom:0;

0

I am receiving the message on a Powershell file that is SQLing a SQL DB. The query worked for about 1.5 years and I assume it started timing out because the data set that it is pulling as part of the query has exponentially grown over this time. The error message I am receiving is:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

However, when adding $SqlCmd.CommandTimeout = 0 to the query as shown below it is still timing out after the same amount of time. The remote query setting in SQL is the default 600 seconds. It would almost seem as if there is an issue/permission at the network-level that is not allowing the information in this query to dictate the amount of timeout. I have also added an infinite timeout to the SQL query in case there was a DB DEADLOCK, but that does not appear to be the issue. When I run the exact same query with less data being returned, it runs fine.

Why would the command below of an infinite timeout be ignored?

 `

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
 $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
 $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName;User Id=$SQLUserName;Password=$SQLDBPWD"

 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

 $SqlCmd.Connection = $SqlConnection
 $SqlCmd.CommandTimeout = 0

 $SqlConnection.Open()

 if ($SqlConnection.State -eq 'Open')
{
$message = "SQL Connection established"
$message | out-file filepath:filepathfilename -append
###SQL statements go here


$sql = "large, complex SQL query here"

$adapter = new-object system.data.sqlclient.sqldataadapter ($sql, $SqlConnection)

$adapter.Fill($table) 


$table |ConvertTo-CSV -delimiter "`t" -NoTypeInformation| %$_.Replace('"','') | out-file filepath:filepathfilename



$SqlConnection.Close();

`

sql sql-server powershell

share|improve this question

edited Apr 1 at 17:17

jason

asked Mar 28 at 21:59

jasonjason

111 bronze badge

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I cant see a query string and it looks like you have your code in the example twice. Are you having a timeout error running the exact code above? If so then it isnt query based, it is connection based.
  
  – Drew
  Mar 28 at 22:18
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  And just a matter of caution, do you have $sqlConnection.Close() ?
  
  – alexsuslin
  Mar 28 at 22:57
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Why not use Invoke-Sqlcmd? Save all this code.
  
  – m0lochwalker
  Mar 28 at 23:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi Everyone, thank you for your assistance. I have modified the post to make it more clear...not sure why it duplicated the code before. I also included the close statement. Please keep in mind that this is only a piece of a much larger PowerShell Script.
  
  – jason
  Apr 1 at 17:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It is also worth noting that we reconfigured & doubled the WSMAN limits just in case (ref devblogs.microsoft.com/powershell/configuring-wsman-limits). In reality the IdleTimeout is 180000, which is longer than the amount of time I am getting prior to receiving the PowerShell "Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding." message, but I figured it couldn't hurt. It didn't resolve the issue though. Any help as to other timeout settings at the network or permissions level would be greatly appreciated! Thanks!
  
  – jason
  Apr 1 at 18:27
  

  
  
  
  

add a comment
 | 

0

I am receiving the message on a Powershell file that is SQLing a SQL DB. The query worked for about 1.5 years and I assume it started timing out because the data set that it is pulling as part of the query has exponentially grown over this time. The error message I am receiving is:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

However, when adding $SqlCmd.CommandTimeout = 0 to the query as shown below it is still timing out after the same amount of time. The remote query setting in SQL is the default 600 seconds. It would almost seem as if there is an issue/permission at the network-level that is not allowing the information in this query to dictate the amount of timeout. I have also added an infinite timeout to the SQL query in case there was a DB DEADLOCK, but that does not appear to be the issue. When I run the exact same query with less data being returned, it runs fine.

Why would the command below of an infinite timeout be ignored?

 `

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
 $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
 $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName;User Id=$SQLUserName;Password=$SQLDBPWD"

 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

 $SqlCmd.Connection = $SqlConnection
 $SqlCmd.CommandTimeout = 0

 $SqlConnection.Open()

 if ($SqlConnection.State -eq 'Open')
{
$message = "SQL Connection established"
$message | out-file filepath:filepathfilename -append
###SQL statements go here


$sql = "large, complex SQL query here"

$adapter = new-object system.data.sqlclient.sqldataadapter ($sql, $SqlConnection)

$adapter.Fill($table) 


$table |ConvertTo-CSV -delimiter "`t" -NoTypeInformation| %$_.Replace('"','') | out-file filepath:filepathfilename



$SqlConnection.Close();

`

sql sql-server powershell

share|improve this question

edited Apr 1 at 17:17

jason

asked Mar 28 at 21:59

jasonjason

111 bronze badge

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I cant see a query string and it looks like you have your code in the example twice. Are you having a timeout error running the exact code above? If so then it isnt query based, it is connection based.
  
  – Drew
  Mar 28 at 22:18
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  And just a matter of caution, do you have $sqlConnection.Close() ?
  
  – alexsuslin
  Mar 28 at 22:57
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Why not use Invoke-Sqlcmd? Save all this code.
  
  – m0lochwalker
  Mar 28 at 23:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi Everyone, thank you for your assistance. I have modified the post to make it more clear...not sure why it duplicated the code before. I also included the close statement. Please keep in mind that this is only a piece of a much larger PowerShell Script.
  
  – jason
  Apr 1 at 17:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It is also worth noting that we reconfigured & doubled the WSMAN limits just in case (ref devblogs.microsoft.com/powershell/configuring-wsman-limits). In reality the IdleTimeout is 180000, which is longer than the amount of time I am getting prior to receiving the PowerShell "Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding." message, but I figured it couldn't hurt. It didn't resolve the issue though. Any help as to other timeout settings at the network or permissions level would be greatly appreciated! Thanks!
  
  – jason
  Apr 1 at 18:27
  

  
  
  
  

add a comment
 | 

I am receiving the message on a Powershell file that is SQLing a SQL DB. The query worked for about 1.5 years and I assume it started timing out because the data set that it is pulling as part of the query has exponentially grown over this time. The error message I am receiving is:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

However, when adding $SqlCmd.CommandTimeout = 0 to the query as shown below it is still timing out after the same amount of time. The remote query setting in SQL is the default 600 seconds. It would almost seem as if there is an issue/permission at the network-level that is not allowing the information in this query to dictate the amount of timeout. I have also added an infinite timeout to the SQL query in case there was a DB DEADLOCK, but that does not appear to be the issue. When I run the exact same query with less data being returned, it runs fine.

Why would the command below of an infinite timeout be ignored?

 `

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
 $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
 $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName;User Id=$SQLUserName;Password=$SQLDBPWD"

 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

 $SqlCmd.Connection = $SqlConnection
 $SqlCmd.CommandTimeout = 0

 $SqlConnection.Open()

 if ($SqlConnection.State -eq 'Open')
{
$message = "SQL Connection established"
$message | out-file filepath:filepathfilename -append
###SQL statements go here


$sql = "large, complex SQL query here"

$adapter = new-object system.data.sqlclient.sqldataadapter ($sql, $SqlConnection)

$adapter.Fill($table) 


$table |ConvertTo-CSV -delimiter "`t" -NoTypeInformation| %$_.Replace('"','') | out-file filepath:filepathfilename



$SqlConnection.Close();

`

sql sql-server powershell

share|improve this question

edited Apr 1 at 17:17

jason

asked Mar 28 at 21:59

jasonjason

111 bronze badge

 `

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
 $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
 $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName;User Id=$SQLUserName;Password=$SQLDBPWD"

 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

 $SqlCmd.Connection = $SqlConnection
 $SqlCmd.CommandTimeout = 0

 $SqlConnection.Open()

 if ($SqlConnection.State -eq 'Open')
{
$message = "SQL Connection established"
$message | out-file filepath:filepathfilename -append
###SQL statements go here


$sql = "large, complex SQL query here"

$adapter = new-object system.data.sqlclient.sqldataadapter ($sql, $SqlConnection)

$adapter.Fill($table) 


$table |ConvertTo-CSV -delimiter "`t" -NoTypeInformation| %$_.Replace('"','') | out-file filepath:filepathfilename



$SqlConnection.Close();

sql sql-server powershell

sql sql-server powershell

share|improve this question

edited Apr 1 at 17:17

jason

asked Mar 28 at 21:59

jasonjason

111 bronze badge

share|improve this question

edited Apr 1 at 17:17

jason

asked Mar 28 at 21:59

jasonjason

111 bronze badge

asked Mar 28 at 21:59

jasonjason

111 bronze badge

asked Mar 28 at 21:59

jasonjason

111 bronze badge