Execute a T-SQL script with PowerShell

Execute a T-SQL script with PowerShell

26, Jun Klaas

In this article I show how to execute a T-SQL script with PowerShell and get objects back.
I've chosen the report subscriptions to demonstrate this.

Case

We have a large number of functions readily available to get information from sql instances or databases. For example the dbatools module. We can also construct SMO objects or navigate through the SQL Provider and look at thousands of objects in there. And yet, sometimes we want to execute a T-SQL query against a certain database. And sometimes, we prefer to do it with PowerShell instead of opening up SSMS.

Solution

This one is not very hard: Invoke-SqlCmd will deliver any T-SQL we provide and return the results as objects. That way we can consume the data as we like. We may write it to another database, convert to HTML, just show it in a grid view, or many other options.

There are other ways, someday I 'll demonstrate the .NET way for sure.

Steps

Roughly, this is what we want to do:

  1. decide on which SQL Server and in which database the wanted information resides
  2. decide where we keep the T-SQL
  3. decide what we'll do with the results

Some preparations

First of all we will import the sqlserver module. In PowerShell V3 and higher, there is auto-loading. That means a module will implicitly be loaded the moment you call a function in it. In a script we have to import explicitly, however. 

Import-Module sqlserver

Actually, we will only be using invoke-sqlcmd. There are some known issues with this command, and maybe you have an invoke-sqlcmd2, written wy Chad Miller, or another version by Warren F RamblingCookieMOnster. In that case, there's even no need to import the sqlserver module.

Choose a SQL Instance

It's always neat to assign the target SQL Instance to a variable in the beginnig of a script: 

$SQLInstance = 'SqlServer16'

Choose a T-SQL source

Next, we obviously need the T-SQL statement we want to execute:

Technically, what's important is how we will offer the statement:

  • Simply include it in our PowerShell script. This is easy, but if the script is long, it can obscure the logic in our script. In more complex scenario's we might have additional logic that executes several statements or makes a choice between several statements. Especially in that case, I like to have the T-SQL itself out of the way.
  • Keep it in a separate file. This has the disadvantage that we create dependencies and potential loss of the query. If somehow the script would be moved, deleted or altered without our knowledge, the PowerShell script may no longer work, or return results that aren't what we expect.
  • In a file with a number of T-SQL statements. This is nearly the same, except we can have a list of statements available as variables. In the PowerShellscript we can find the statement to execute using some If or switch logic.
  • Create a view, stored procedure or User Defined Function on the sql server. This would be a handy solution if we will need the T-SQL recurring on the same SQL Instance. The T-SQL that remains inside our PowerShell script will be no more than one line: Exec MyProcedure or SELECT JobName, Status, Date FROM MyView, for example.

for now, we will save the query in a .sql file. In our PowerShell script, we assign this to a variable: 

$TSQLFile = 'D:\scripts\SSRS\show-ReportSubscriptions.sql'

You notice we don't get the statement with Get-Content or something, we just pass the file path and name. That's because we will use the -InputFile parameter this time and not the -Query, which can be just as appropriate in other cases.
Use 

help Invoke-SqlCmd -ShowWindow

to learn everything about the possibilities of this cmdlet.

What are those ridiculously named Sql Agent Jobs?

As an example, I have a T-SQL query to show all there is to know about report subscriptions. It's intended to be used only on SQL Instances that have a ReportServer database.

When you create a subscription to a SSRS Report, a SQL Agent Job is automatically created for it. The Job has a beautiful name, like [3A2A741C-4B88-4D7E-856C-4EE94228FD52], and we are warned

'This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.'

by the description.

jobnames

job

The one jobstep it gets, is named 3A2A741C-4B88-4D7E-856C-4EE94228FD52_step_1 and says:
exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='64c9f002-bb63-4956-b3ec-d2fe267999cd'

jobstep

Yes, all pretty useless. I want to know what report uses what job, with what schedule, how it's delivered and so on. So I went looking for queries that can translate all this nonsense for us.

Some explanation on how to do this is brought to us by Prajapati Neha and Sorna Kumar Muthuraj.

I started with those queries and added some more columns myself.

A significant part of the information is represented by integers, like the schedule recurrence types, the months, the day of the week, day of the month, the notify level. We will make SQL Server convert all those information into readable text. We will also join the report schedules with the sysjobschedules, the sysjobs and the sysoperators.

I ended up with a 250 lines long query: 

-- based on https://social.msdn.microsoft.com/Forums/vstudio/en-US/38b42f1e-c109-4ba5-bf1c-82bad5851978/report-schedule-meta-data?forum=sqlreportingservices by Prajapati Neha

 WITH    EnhancedSched
      AS (
           SELECT
                Sched.ScheduleID ,
                Sched.Name ,
                Sched.StartDate ,
                Sched.Flags ,
                Sched.NextRunTime ,
                Sched.LastRunTime ,
                Sched.EndDate ,
                Sched.RecurrenceType ,
                Sched.MinutesInterval ,
                Sched.DaysInterval ,
                Sched.WeeksInterval ,
                Sched.DaysOfWeek ,
                Sched.DaysOfMonth ,
                Sched.Month ,
                Sched.MonthlyWeek ,
                Sched.State ,
                Sched.LastRunStatus ,
                Sched.ScheduledRunTimeout ,
                Sched.CreatedById ,
                Sched.EventType ,
                Sched.EventData ,
                Sched.Type ,
                Sched.ConsistancyCheck ,
                Sched.Path ,
            CASE WHEN DaysOfWeek & 1 <> 0 THEN 'Sun, ' ELSE '' END
            + CASE WHEN DaysOfWeek & 2 <> 0 THEN 'Mon, ' ELSE '' END
            + CASE WHEN DaysOfWeek & 4 <> 0 THEN 'Tue, ' ELSE '' END
            + CASE WHEN DaysOfWeek & 8 <> 0 THEN 'Wed, ' ELSE '' END
            + CASE WHEN DaysOfWeek & 16 <> 0 THEN 'Thu, ' ELSE '' END
            + CASE WHEN DaysOfWeek & 32 <> 0 THEN 'Fri, ' ELSE '' END
            + CASE WHEN DaysOfWeek & 64 <> 0 THEN 'Sat, ' ELSE '' END AS DaysOfWeekString ,
            CASE WHEN DaysOfMonth & 1 <> 0 THEN '1,' ELSE '' END
            + CASE WHEN DaysOfMonth & 2 <> 0 THEN '2,' ELSE '' END
            + CASE WHEN DaysOfMonth & 4 <> 0 THEN '3,' ELSE '' END
            + CASE WHEN DaysOfMonth & 8 <> 0 THEN '4,' ELSE '' END
            + CASE WHEN DaysOfMonth & 16 <> 0 THEN '5,' ELSE '' END
            + CASE WHEN DaysOfMonth & 32 <> 0 THEN '6,' ELSE '' END
            + CASE WHEN DaysOfMonth & 64 <> 0 THEN '7,' ELSE '' END
            + CASE WHEN DaysOfMonth & 128 <> 0 THEN '8,' ELSE '' END
            + CASE WHEN DaysOfMonth & 256 <> 0 THEN '9,' ELSE '' END
            + CASE WHEN DaysOfMonth & 512 <> 0 THEN '10,' ELSE '' END
            + CASE WHEN DaysOfMonth & 1024 <> 0 THEN '11,' ELSE '' END
            + CASE WHEN DaysOfMonth & 2048 <> 0 THEN '12,' ELSE '' END
            + CASE WHEN DaysOfMonth & 4096 <> 0 THEN '13,' ELSE '' END
            + CASE WHEN DaysOfMonth & 8192 <> 0 THEN '14,' ELSE '' END
            + CASE WHEN DaysOfMonth & 16384 <> 0 THEN '15,' ELSE '' END
            + CASE WHEN DaysOfMonth & 32768 <> 0 THEN '16,' ELSE '' END
            + CASE WHEN DaysOfMonth & 65536 <> 0 THEN '17,' ELSE '' END
            + CASE WHEN DaysOfMonth & 131072 <> 0 THEN '18,' ELSE '' END
            + CASE WHEN DaysOfMonth & 262144 <> 0 THEN '19,' ELSE '' END
            + CASE WHEN DaysOfMonth & 524288 <> 0 THEN '20,' ELSE '' END
            + CASE WHEN DaysOfMonth & 1048576 <> 0 THEN '21,' ELSE '' END
            + CASE WHEN DaysOfMonth & 2097152 <> 0 THEN '22,' ELSE '' END
            + CASE WHEN DaysOfMonth & 4194304 <> 0 THEN '23,' ELSE '' END
            + CASE WHEN DaysOfMonth & 8388608 <> 0 THEN '24,' ELSE '' END
            + CASE WHEN DaysOfMonth & 16777216 <> 0 THEN '25,' ELSE '' END
            + CASE WHEN DaysOfMonth & 33554432 <> 0 THEN '26,' ELSE '' END
            + CASE WHEN DaysOfMonth & 67108864 <> 0 THEN '27,' ELSE '' END
            + CASE WHEN DaysOfMonth & 134217728 <> 0 THEN '28,' ELSE '' END
            + CASE WHEN DaysOfMonth & 268435456 <> 0 THEN '29,' ELSE '' END
            + CASE WHEN DaysOfMonth & 536870912 <> 0 THEN '30,' ELSE '' END AS DaysOfMonthString ,
            CASE WHEN Month = 4095 THEN 'every month, ' ELSE
              CASE WHEN Month & 1 <> 0 THEN 'Jan, ' ELSE '' END
            + CASE WHEN Month & 2 <> 0 THEN 'Feb, ' ELSE '' END
            + CASE WHEN Month & 4 <> 0 THEN 'Mar, ' ELSE '' END
            + CASE WHEN Month & 8 <> 0 THEN 'Apr, ' ELSE '' END
            + CASE WHEN Month & 16 <> 0 THEN 'May, ' ELSE '' END
            + CASE WHEN Month & 32 <> 0 THEN 'Jun, ' ELSE '' END
            + CASE WHEN Month & 64 <> 0 THEN 'Jul, ' ELSE '' END
            + CASE WHEN Month & 128 <> 0 THEN 'Aug, ' ELSE '' END
            + CASE WHEN Month & 256 <> 0 THEN 'Sep, ' ELSE '' END
            + CASE WHEN Month & 512 <> 0 THEN 'Oct, ' ELSE '' END
            + CASE WHEN Month & 1024 <> 0 THEN 'Nov, ' ELSE '' END
            + CASE WHEN Month & 2048 <> 0 THEN 'Dec, ' ELSE '' END
            END AS MonthString ,
            CASE MonthlyWeek
              WHEN 1 THEN 'first'
              WHEN 2 THEN 'second'
              WHEN 3 THEN 'third'
              WHEN 4 THEN 'fourth'
              WHEN 5 THEN 'last'
            END AS MonthlyWeekString ,
            ' starting ' + CONVERT (VARCHAR, StartDate, 101)
            + CASE WHEN EndDate IS NOT NULL
                   THEN ' and ending ' + CONVERT (VARCHAR, EndDate, 101)
                   ELSE ''
              END AS StartEndString ,
            CONVERT(VARCHAR, DATEPART(hour, StartDate) % 12) + ':'
            + CASE WHEN DATEPART(minute, StartDate) < 10
                   THEN '0' + CONVERT(VARCHAR(2), DATEPART(minute, StartDate))
                   ELSE CONVERT(VARCHAR(2), DATEPART(minute, StartDate))
              END + CASE WHEN DATEPART(hour, StartDate) >= 12 THEN ' PM'
                         ELSE ' AM'
                    END AS StartTime
          FROM
            Reportserver..Schedule Sched
         ),
    SuperEnhancedSchedule
      AS (
           SELECT
            EnhancedSched.ScheduleID ,
            EnhancedSched.Name ,
            EnhancedSched.StartDate ,
            EnhancedSched.Flags ,
            EnhancedSched.NextRunTime ,
            EnhancedSched.LastRunTime ,
            EnhancedSched.EndDate ,
            EnhancedSched.RecurrenceType ,
            EnhancedSched.MinutesInterval ,
            EnhancedSched.DaysInterval ,
            EnhancedSched.WeeksInterval ,
            EnhancedSched.DaysOfWeek ,
            EnhancedSched.DaysOfMonth ,
            EnhancedSched.Month ,
            EnhancedSched.MonthlyWeek ,
            EnhancedSched.State ,
            EnhancedSched.LastRunStatus ,
            EnhancedSched.ScheduledRunTimeout ,
            EnhancedSched.CreatedById ,
            EnhancedSched.EventType ,
            EnhancedSched.EventData ,
            EnhancedSched.Type ,
            EnhancedSched.ConsistancyCheck ,
            EnhancedSched.Path , -- spec what you need.
            CASE WHEN RecurrenceType = 1
                 THEN 'At ' + StartTime + ' on '
                      + CONVERT(VARCHAR, StartDate, 101)
                 WHEN RecurrenceType = 2
                 THEN 'Every ' + CONVERT(VARCHAR, ( MinutesInterval / 60 ))
                      + ' hour(s) and '
                      + CONVERT(VARCHAR, ( MinutesInterval % 60 ))
                      + ' minute(s), ' + 'starting '
                      + CONVERT (VARCHAR, StartDate, 101) + ' at '
                      + SUBSTRING(CONVERT(VARCHAR, StartDate, 8), 0, 6)
                      + ' ' + SUBSTRING(CONVERT(VARCHAR, StartDate, 109),
                                        25, 2)
                      + CASE WHEN EndDate IS NOT NULL
                             THEN ' and ending '
                                  + CONVERT (VARCHAR, EndDate, 101)
                             ELSE ''
                        END
                 WHEN RecurrenceType = 3
                 THEN 'At ' + StartTime + ' every '
                      + CASE DaysInterval
                          WHEN 1 THEN 'day, '
                          ELSE CONVERT(VARCHAR, DaysInterval) + ' days, '
                        END + StartEndString
                 WHEN RecurrenceType = 4
                 THEN 'At ' + StartTime + ' every '
                      + CASE WHEN LEN(DaysOfWeekString) > 1
                             THEN LEFT(DaysOfWeekString,
                                       LEN(DaysOfWeekString) - 1)
                             ELSE ''
                        END + ' of every '
                      + CASE WHEN WeeksInterval = 1 THEN ' week,'
                             ELSE CONVERT(VARCHAR, WeeksInterval)
                                  + ' weeks,'
                        END + StartEndString
                 WHEN RecurrenceType = 5
                 THEN 'At ' + StartTime + ' on day(s) '
                      + CASE WHEN LEN(DaysOfMonthString) > 1
                             THEN LEFT(DaysOfMonthString,
                                       LEN(DaysOfMonthString) - 1)
                             ELSE ''
                        END + ' of ' + MonthString + StartEndString
                 WHEN RecurrenceType = 6
                 THEN 'At ' + StartTime + ' on the ' + MonthlyWeekString
                      + ' '
                      + CASE WHEN LEN(DaysOfWeekString) > 1
                             THEN LEFT(DaysOfWeekString,
                                       LEN(DaysOfWeekString) - 1)
                             ELSE ''
                        END + ' of ' + MonthString + StartEndString
                 ELSE 'At ' + SUBSTRING(CONVERT(VARCHAR, StartDate, 8), 0,
                                        6) + ' '
                      + SUBSTRING(CONVERT(VARCHAR, StartDate, 109), 25, 2)
                      + StartEndString
            END ScheduleTextDefinition
            FROM EnhancedSched
         )

SELECT
          c.Name AS ReportName
        , rs.ScheduleID AS JobName
        , CAST(s.LastRunTime AS DateTime2(0)) AS LastRun
        , s.LastStatus
        , NextRunTime = CASE JSc.next_run_date WHEN 0 THEN NULL
                            ELSE DATETIME2FROMPARTS(FLOOR(JSc.next_run_date/10000), FLOOR(JSc.next_run_date/100) % 100, JSc.next_run_date%100, FLOOR(JSc.next_run_time/10000), FLOOR(JSc.next_run_time/100) % 100, JSc.next_run_time%100,0 ,0)
                            END
        , J.enabled AS JobIsEnabled
        , CASE  WHEN CHARINDEX('<Name>TO</Name><Field>', ExtensionSettings) <> 0 THEN 'PARAMETER'
                WHEN CHARINDEX('<Name>TO</Name><Value>', ExtensionSettings) = 0 THEN ''
                ELSE SUBSTRING(ExtensionSettings, LEN('<Name>TO</Name><Value>') + CHARINDEX('<Name>TO</Name><Value>', ExtensionSettings),
                    CHARINDEX('</Value>', ExtensionSettings, CHARINDEX('<Name>TO</Name><Value>', ExtensionSettings) + 1) - (LEN('<Name>TO</Name><Value>')
                    + CHARINDEX('<Name>TO</Name><Value>', ExtensionSettings)))
          END
          AS 'To Email recipient List'
        , CASE CHARINDEX('<Name>CC</Name><Value>', ExtensionSettings) WHEN 0 THEN ''
            ELSE SUBSTRING(ExtensionSettings, LEN('<Name>CC</Name><Value>') + CHARINDEX('<Name>CC</Name><Value>', ExtensionSettings),
             CHARINDEX('</Value>', ExtensionSettings, CHARINDEX('<Name>CC</Name><Value>', ExtensionSettings) + 1) - (LEN('<Name>CC</Name><Value>')
              + CHARINDEX('<Name>CC</Name><Value>', ExtensionSettings)))
          END
          AS 'CC Email recipient List'
        , CASE CHARINDEX('<Name>BCC</Name><Value>', ExtensionSettings) WHEN 0 THEN ''
            ELSE SUBSTRING(ExtensionSettings, LEN('<Name>BCC</Name><Value>') + CHARINDEX('<Name>BCC</Name><Value>', ExtensionSettings),
             CHARINDEX('</Value>', ExtensionSettings, CHARINDEX('<Name>BCC</Name><Value>', ExtensionSettings) + 1) - (LEN('<Name>BCC</Name><Value>')
              + CHARINDEX('<Name>BCC</Name><Value>', ExtensionSettings)))
          END
          AS 'BCC Email recipient List'
        , J.description AS JobDescription
        , s.[Description] AS SubscriptionDescription
        , CAST(Sc.StartDate AS DateTime2(0)) AS ScheduleStartDate
        , CAST(Sc.EndDate AS DateTime2(0)) AS ScheduleEndDate
        , c.Path
        , s.DeliveryExtension
        , c.Property AS ReportProperties
        , c.Description AS ReportDescription
        --, ExtensionSettings
        , RecurrenceType = CASE RecurrenceType
             WHEN  1 THEN 'Once'
             WHEN  2 THEN 'Hourly '
             WHEN  3 THEN '3'
             WHEN  4 THEN 'Daily / Weekly'
             WHEN  5 THEN 'Monthly'
             WHEN  6 THEN 'Monthly'
             End
        , Sc.ScheduleTextDefinition
        , CASE J.notify_level_email
            WHEN 0 THEN 'Disabled'
            WHEN 1 THEN 'On Success'
            WHEN 2 THEN 'On Failure'
            WHEN 3 THEN 'On Completion'
            ELSE 'Unknown'
            END AS EmailOperatorLevel
        , O.name AS Operator
        , O.email_address AS OperatorEmail
        , CAST(J.date_created AS DateTime2(0)) AS JobCreateDate
        , CAST(J.date_modified AS DateTime2(0)) AS JobModifiedDate
        , J.originating_server_id

FROM    ReportServer..[Catalog] c 
        JOIN ReportServer..Subscriptions s ON c.ItemID = s.Report_OID 
        JOIN ReportServer..ReportSchedule rs ON c.ItemID = rs.ReportID
            AND rs.SubscriptionID = s.SubscriptionID
        JOIN SuperEnhancedSchedule Sc ON rs.ScheduleID = Sc.ScheduleID
        JOIN msdb.dbo.sysjobs J ON J.name = CAST(rs.ScheduleID AS nvarchar(128))
        JOIN msdb.[dbo].[sysjobschedules] JSc ON JSc.job_id = J.job_id
        LEFT JOIN msdb.[dbo].[sysoperators] O ON O.id = J.notify_email_operator_id

I can't remember why I chose this approach and maybe you have a less complicated way to compose this query. Please let me know.

The actual PowerShell code we need to write today is very short: 

Invoke-Sqlcmd -ServerInstance $SqlInstance -Database master -InputFile  $TSQLFile

The complete script

Import-Module sqlserver
$SQLInstance = 'SqlServer16'
$TSQLFile = 'D:\scripts\SSRS\show-ReportSubscriptions.sql'  

Invoke-Sqlcmd -ServerInstance $SqlInstance -Database master -InputFile  $TSQLFile

Surprising?
There's actually nothing more to it than send Query X to SQL server Y.

To be continued

Now you have one example of how you can send a query to SQL Server and get back an array of objects.

You can alter this in many ways.

First you may want to collect some T-SQL scripts where this method of usage makes sense.
If you have situations where you'll want to send query X when a condition is met, and query Z if not, you can write that in PowerShell.
Once we have the data, we just need to decide what we'll do with it. We can keep it very simple by just sending it to a GridView. Maybe you want to save the results in a database,
or send them in an e-mail,
like I showed with backup results.

Let the creativity flow.

Previous Post Next Post

[email protected]