Powershell and Batch files

Executing batch files throgh Powershell - is not doable by design. The cmd.exe is the runtime environment for .bat files , and it is
possible to issue a .bat call via the Powershell prompt

PS> cmd /c mybatchfile.bat

System administrator jobs

Powershell : SQLCMD and invoke-expression

There was a requirement to add a SQL Server login to a group of SQL Servers.
The SQL Server login was to be uniform across all the servers. I tend to use SQLCMD for most
DBA tasks - and as my account is Windows based and with high level of privileges , I didn't
have to worry about supplying a different username / password for every SQL Serve instance.

This powershell script is very simple , iterating through a list of SQL Server instances which are
stored in a text file . For every instance a SQLCMD is run . This is done through the "invoke-expression" call.
This allows the DBA to maintain their sql scripts separately , which can be executed against any number of instances

-----------------CODE STARTS---------------------------

foreach ($svr in get-content "C:\MyInstances.txt"){
    $svr
    invoke-expression "SQLCMD -E -S $svr -i createMyuser.sql"
   
}

-------------CODE FINISHES----------------

sqlcmd jobs

Manipulating Registry with T-SQL

I'm doing some installations at the moment for Monitoring agent and I needed a quick way of deriving
the Error Log Path and the SQL Server Home Path .

My first problem was to derive the t-sql to gain the information for 1 instance .
The second problem was to use a framework where I would be able to iterate through every instance I manage

The t-sql to derive the error log file was straightforward
----------------CODE START------------------------

select ServerProperty('ErrorLogFileName')

----------------CODE END----------------------------

The code for the SQL Server path was an undocumented method . By documented it means Microsoft do not support it.
In the master database, there is an extended stored procedure called “xp_instance_regread” that allows you to derive

values from the registry.  There is no information in BOL.  If its undocumented, this means that they are unsupported by

Microsoft and you have to google to figure out how to use them.

The code below returns the SQL Server home for a given SQL Server instance

----------------CODE START------------------------

declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot

OUTPUT

print @SmoRoot
----------------CODE END----------------------------

The second problem involved getting this information form multiple sql server instances on the same network. As I've

been getting into Powershell quote a bit recently , I thought I'd attempt it in Powershell. The results were useful.

The script reads the instances from "C:\MyInstancesPROD_2005.txt"   and writes them out to "C:\projects\test.txt"

--------------------CODE START-------------------------

foreach ($svr in get-content "C:\InstancesPROD_2005.txt"){
    $dt = new-object "System.Data.DataTable"
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=master;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot

OUTPUT
SELECT @@SERVERNAME AS ServerName, SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('ProductLevel') as

SP,SERVERPROPERTY('ErrorLogFileName') AS ErrorLog,@SmoRoot AS SQLPath
"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    $dt | Format-Table -autosize 
    $dt | out-File "C:\projects\test.txt" -append -encoding ASCII

   
}

--------------------CODE END-------------------------

t-sql jobs

SQL Server Version with Powershell

In running a large SQL Server environment , it is necessary to have as many timesaving methods as possible. For instance , I'm upgrading loads of sql servers at the moment and the following script , gives me a quick way of getting a summary of versions. Place all the relevant servers into a file , such as "C:\servers.txt" , just place them on separate lines.

An example of the lists in that document is:

SERVER1\INST1
SERVER2\INST2
etc

Keep in mind, you will need to have  Allow Remote Connections enabled

To run , copy and paste straight into a Powershell cmdlet ,which is a series of commands, usually more than one line, stored in a text file with a .ps1 extension.

-----------------------------CODE START-------------------------------------------------------------

foreach ($svr in get-content "C:\Servers.txt"){
    $dt = new-object "System.Data.DataTable"
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=master;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "SELECT @@SERVERNAME AS ServerName, SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('ProductLevel') as SP"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    $dt | Format-Table -autosize
}

-----------------------------CODE END-------------------------------------------------------------

Powershell jobs

Powershell_BAK_files

In an environment where I do not have 100% control over what files are saved in what location , I will often have issues with various application owners creating ad-hoc BAK files . This develops into a disk - space issue , causing data and log files to have no more room to grow I use the script below to search all the relevant servers and return a list of BAK files . I then send the report to the various application owners. To run , copy and paste straight into a Powershell cmdlet ,which is a series of commands, usually more than one line, stored in a text file with a .ps1 extension.

------------------CODE START-----------------------------

foreach($comp in (get-content C:\servers.txt))
{
     $comp
     # Powershell script to list the DLL files under the system32 folder
     $Dir = get-childitem "E:\Intech" -recurse
     # $Dir |get-member
     $List = $Dir | where {$_.extension -eq ".BAK"}
     $List | format-table name

}

----------------CODE END----------------------------------

Powershell_ErrorLogs

Running the code below within the Powershell interface will list designated Errors Log messages instances (SQL SERVER 2005) listed in the "C:\InstancesPROD_2005.txt" document.
An example of the lists in that document is:

SERVER1\INST1
SERVER2\INST2
etc

Currently the sql code will designated errors failed within the last 2 days. Configure to your requirements.

To run ,  copy and paste straight into a Powershell  cmdlet ,which is a series of commands, usually more than one line, stored in a text file with a .ps1 extension. 

----------------------------------CODE START-----------------------------------------

foreach ($svr in get-content "C:\InstancesPROD_2005.txt"){
    $svr
    $dt = new-object "System.Data.DataTable"
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=msdb;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "

DECLARE @sqlStatement1 VARCHAR(200)
SET @sqlStatement1 = 'master.dbo.xp_readerrorlog'
        CREATE TABLE #Errors (LogDate DATETIME,ProcessInfo NVARCHAR(50),vchMessage varchar(2000))
INSERT #Errors EXEC @sqlStatement1
SELECT LogDate, RTRIM(LTRIM(vchMessage)) FROM #Errors WHERE
([vchMessage] like '%error%'
   or  [vchMessage] like '%fail%'
   or  [vchMessage] like '%Warning%'
   or  [vchMessage] like '%The SQL Server cannot obtain a LOCK resource at this time%'
   or  [vchMessage] like '%Autogrow of file%in database%cancelled or timed out after%'
   or  [vchMessage] like '%Consider using ALTER DATABASE to set smaller FILEGROWTH%'
   or  [vchMessage] like '% is full%'
   or  [vchMessage] like '% blocking processes%'
   or  [vchMessage] like '%SQL Server has encountered%IO requests taking longer%to complete%'
)
and [vchMessage] not like '%\ERRORLOG%'
and [vchMessage] not like '%Attempting to cycle errorlog%'
and [vchMessage] not like '%Errorlog has been reinitialized.%'
and [vchMessage] not like '%found 0 errors and repaired 0 errors.%'
and [vchMessage] not like '%without errors%'
and [vchMessage] not like '%This is an informational message%'
and [vchMessage] not like '%WARNING:%Failed to reserve contiguous memory%'
and [vchMessage] not like '%The error log has been reinitialized%'
and [vchMessage] not like '%Setting database option ANSI_WARNINGS%'
and [vchMessage] not like '%Error: 15457, Severity: 0, State: 1%'
and [vchMessage] <>  'Error: 18456, Severity: 14, State: 16.'
AND Logdate > GETDATE() - 2

 
DROP TABLE #Errors
"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    $dt | Format-Table -autosize
}

----------------------------------CODE END--------------------------------------------

Powershell jobs

Powershell - List Failed SQL Server Jobs

Running the code below within the Powershell interface will list all SQL Server jobs failed
on instances (SQL SERVER 2000 & SQL SERVER 2005) listed in the "C:\Instances.txt" document.
An example of the lists in that document is:

SERVER1\INST1
SERVER2\INST2
etc

Currently the sql code will list jobs failed within the last 2 days. Configure to your requirements.

To run , wither copy and paste straight into a Powershell  cmdlet which is a series of commands, usually more than one line, stored in a text file with a .ps1 extension. 

-----CODE STARTS-----------------------------------
foreach ($svr in get-content "C:\Instances.txt"){
    $svr
    $dt = new-object "System.Data.DataTable"
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=msdb;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "SELECT sjh.server,sj.name, CONVERT(VARCHAR(30),sjh.message) as message , sjh.run_date, sjh.run_time
FROM msdb..sysjobhistory sjh
JOIN msdb..sysjobs sj on sjh.job_id = sj.job_id
JOIN (SELECT job_id, max(instance_id) maxinstanceid
FROM msdb..sysjobhistory
WHERE run_status NOT IN (1,4)
GROUP BY job_id) a ON sjh.job_id = a.job_id AND sjh.instance_id = a.maxinstanceid
WHERE    DATEDIFF(dd,CONVERT(VARCHAR(8),sjh.run_date), GETDATE()) <= 2"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    $dt | Format-Table -autosize
}
-----------------CODE ENDS--------------------------