Invoke sqlcmd : Execution timeout expired

Question

I m executing this Powershell scripts but getting execution timeout expired so i have added the query timeout in the Powershell scripts but still getting the same error. Does anyone have any idea?

 $InstanceList = "DB03"  
 $filepath = "D:\cc8710\script1.sql"  
  
      
 $InstanceList |  
     ForEach-Object{  
         $databases = invoke-sqlcmd -ServerInstance $_ -Database "master" -Query "select name from sys.databases where name like 'mms_c%' and name != 'lms'" -Querytimeout 0  
      
         foreach ($database in $databases) {  
             #Execute scripts   
             Invoke-Sqlcmd -ServerInstance $_ -Database $database.name -InputFIle $filepath    
                   
         }  
 }  

Answer 1

You have -Querytimeout 0 for the query against sys.databases. That is a query that should always return immediately. But then you are running your script in some databases, but here you don't have -Querytimeout 0. That is, I think you need:

   foreach ($database in $databases) {  
                 #Execute scripts   
                 Invoke-Sqlcmd -ServerInstance $_ -Database $database.name -InputFIle $filepath  -Querytimeout 0  

Answer 2

-ServerInstance $_

What's $_ here?
The error message is clear, the SQL Server, what ever is addressed with $_ isn't accessable.

Answer 3

HI @Mario2286 ,

Use $_.name, you need to pipe the list of the SQL Server to foreach and provide the instance to the -ServerInstance parameter of the Invoke -Sqlcmd cmdlet, please check the instance
https://devblogs.microsoft.com/powershell/invoke-sqlcmd-is-now-available-supporting-cross-platform/

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi,

It would appear from the error message that the SQL instance or server is not available, and therefore the request is timing out. You have set the timeout for the query to 0, which means an infinite amount of time, therefore it's more likely to be a connection issue than a DB size issue.

------------------------------------------------------------------------------------------------------------------------------------------

--If the reply is helpful, please Upvote and Accept as answer--

Answer 5

From the error message, your script is erroring on the 2nd Invoke, not the first.

Invoke-Sqlcmd -ServerInstance $_ -Database $database.name -InputFIle $filepath

You should set a long QueryTimeout, like 120, on it and see what happens. Not QueryTimeout=0