How to fix Powershell Execution Timeout Expired with CommandTimeout

Question: I'm getting this Execution timeout error when executing a Powershell script using the $sql.ExecuteReader() method. How can I fix this issue? Exception calling "ExecuteReader" with "0" argument(s): "Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding." Answer: To overcome this issue - use the CommandTimeout parameter and set to a value of 0. This will make it infinite. So it would be something like : $cn = new-object System.Data.SqlClient.SqlConnection "server=myInstance;database=master;Integrated Security=sspi" $cn.Open() $cn.CreateCommand() $sql.CommandText $sql.CommandTimeout=0 $sql.CommandTimeout=0 $sql.ExecuteReader() Read More on Powerhsell and connecting to SQL Server Powershell - run... Read more →

How to get SQL Server database object count using Powershell Measure-Object

Question: How can I iterate through a list of SQL Servers , extract the count of the databases , place into a powershell data table and get the database count sum for all the servers? I'm having some trouble thinking about how to add up the counts from the individual servers into a total count , using powershell functions. Answer: This basic powershell script will give you the basic framework. This script iterates through a list of SQL Servers - listed in the file referenced in the $instancepath. The results of the command executed are added to the DataTable .... Read more →

Powershell script for unattended install of SQL Server standard patches

Question: Do you have a powershell script allowing me to dynamically create a drive - pointing to a network share , and then start an unattended install of SQL Server patching? I'll afdapt the script to drop onto a server and execute according to schedule . Answer: Here is an example of powershell script that applies SQL Server 2016 SP2 CU7 , although you can apply the same framework for other patching . Some key points to note: 1) Replace "my_path" with the network path . The drive is added by use of the PS-Drive cmdlet. If you want to... Read more →

Powershell assign a drive and mount point

Question: What are the powershell commands to create to ASSIGN a drive letter and then create a MOUNT POINT? Answer: These powershell command examples will assign a drive letter and then create a mount point. You'll need to first know the Disk ID and also decide on the drive letter you need to assign If you want to create a mount point , then use the MOUNT POINT section If the intention is to automate the assigning of drive letters to new partitions , and also dynamically identify the diskid than you'll need to wrap some extra code around these... Read more →

SQL Server Sysadmin Audit Report Powershell script

Question: I need a powershell script which reports on SQL Server logins with Sysadmin privileges. My aim is to automate the process - so if you could provide a script which provides the sql server name , login and that I can schedule. Answer: This powershell script will execute across a list of servers - as defined in the file associated with the $instancepath variable.Create a text file and place 1 or more sql server instances in the file e.g server1\instance1 server2\instance1 $isodate=Get-Date -format s $isodate=$isodate -replace(":","") $basepath=(Get-Location -PSProvider FileSystem).ProviderPath $instancepath=$basepath + "\config\instances_all.txt" $outputfile="\logs\sql_servers_sysadmin_report" + $isodate + ".html" $outputfilefull =... Read more →