apiVersion: batch/v1beta1
kind: CronJob
metadata:
  name: runasqlscriptforben
spec:
  schedule: "*/600 * * * *"
  jobTemplate:
    spec:
      activeDeadlineSeconds: 1200
      template:
        spec:
          containers:
            - name: dbatoolspowershellcontainerben
              image: sqldbawithabeard/pwshdbatools:v1
              env:
                - name: HOME
                  value: /tmp
              command:
              - pwsh
              - -Command
              - |
                $Date = Get-Date -Format yyyyMMdd-HHmmsss
                Write-Host "$Date - I am a script running in container"
        
                $Query = "--  Sample use: create one million rows 
                IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Example]') AND type in (N'U'))
                CREATE TABLE dbo.Example (ExampleId  int  not null)  
                
                DECLARE @RowsToCreate int
                SET @RowsToCreate = 100000
                
                --  Table of numbers data generator, as per Itzik Ben-Gan (from multiple sources)
                ;WITH
                  Pass0 as (select 1 as C union all select 1), --2 rows
                  Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
                  Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
                  Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
                  Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
                  Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows
                  Tally as (select row_number() over(order by C) as Number from Pass5)
                INSERT dbo.Example (ExampleId)
                 select Number
                  from Tally
                  where Number <= @RowsToCreate

                SELECT COUNT(*) AS 'HowMany?','Whats Happening?' AS 'What Ben Says', GetDate() AS 'When' FROM dbo.Example
                SELECT 'Finished' 
                "
                $Server = '10.244.2.13'
                $database = 'BenBenjiBenBenBen'
                $Date = Get-Date -Format yyyyMMdd-HHmmsss
                Write-Host "$Date - I am importing dbatools"
                
                Import-Module /root/.local/share/powershell/Modules/dbatools/1.1.0/dbatools.psd1

                $Date = Get-Date -Format yyyyMMdd-HHmmsss
                Write-Host "$Date - I will try to connect to the sql instance $server"
                $secStringPassword = ConvertTo-SecureString 'dbatools!12345' -AsPlainText -Force
                [pscredential]$sqllogin = New-Object System.Management.Automation.PSCredential ('benadmin', $secStringPassword)
                
                $Results = Invoke-DBaQuery -SQlINstance $server -SqlCredential $sqllogin -Query $Query -Database $database -Verbose
                $Date = Get-Date -Format yyyyMMdd-HHmmsss
                $Message =  "{0}- I got {1}" -f $date, ($Results| Out-String)
                Write-Output $Message
          restartPolicy: Never