Executing a SQL query using Powershell

I hope this is the place to ask "super basic" newbie questions because I have one which may have to do with script placement of the following sql query:

"USE DATABASENAME

GO

 

IF OBJECT_ID('tempdb..#mytemp') IS NOT NULL DROP TABLE #mytemp

GO

 

DECLARE @row_id int;

DECLARE @tableHTML  NVARCHAR(MAX) ;

DECLARE @Subject NVARCHAR(MAX);

DECLARE @AppID int; 

DECLARE @EmailTo NVARCHAR(MAX);

DECLARE @PassFail NVARCHAR(20);

 

set rowcount 0

SELECT 

ROW_NUMBER() OVER(ORDER BY AL.AppID) AS Row, AL.AppID, AL.AppName, AL.AppOwner, AL.AppAlertEmail, TD.TestCaseName, TD.TestCaseDescription, TR.TestResult, 

TR.TestStartTime, TR.TestEndTime, TR.TestResultDescription, TR.TestEnvironment, TR.TestBroswer

 into #mytemp from TestResults TR INNER JOIN

TestDetails TD ON TR.TestID = TD.TestID INNER JOIN

AppList AL ON TD.AppID = AL.AppID

WHERE

(TR.BatchID = (SELECT max(batchid) as MaxBatchID

FROM TestBatch where BatchEndTime is not null))

ORDER BY AL.AppID

 

set rowcount 1

 

select @row_id = Row from #mytemp

 

while @@rowcount <> 0

begin

    set rowcount 0

    select * from #mytemp where Row = @row_id;

 

    SET @AppID = (select AppID from #mytemp where Row = @row_id);

 

    SET @EmailTo = (select AppAlertEmail from #mytemp where Row = @row_id);

    --SET @EmailTo = 'me@mydomain.com';

 

    SET @PassFail = (select top 1 UPPER(TestResult) as TestResult from #mytemp where AppID = @AppID order by TestResult);

 

    SET @Subject = '***' + @PassFail + '*** ' + (select AppName from #mytemp where Row = @row_id) + ' Test Results ***' + @PassFail + '***';

 

    SET @tableHTML = 

    N'<H1>Results</H1>' +

    N'<table border="1">' +

    N'<tr bgcolor="#BDD8DA"><th>Name</th><th>Description</th>' +

    N'<th>Result</th><th>Start Time</th><th>End Time</th>' +

    N'<th>Result Description</th><th>Environment</th><th>Browser</th></tr>' +

    CAST ( ( SELECT TestCaseName as [td], '',

                    TestCaseDescription as [td], '',

                    (case when TestResult = 'Pass' then

                    '#00CC00' else '#FF0000' end)  AS [td/@bgcolor],

                    TestResult as [td], '',

                    TestStartTime as [td], '',

                    TestEndTime as [td], '',

                    TestResultDescription as [td], '',  

                    TestEnvironment as [td], '',

                    TestBroswer as [td]

              from #mytemp where AppID = @AppID

              ORDER BY TestResult

              FOR XML PATH('tr'), TYPE 

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

 

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'SQL Mail',

    @recipients = @EmailTo,

    @building = @tableHTML,

    @subject = @Subject,

    @body_format = 'HTML' ;

 

 

    delete #mytemp where AppID = @AppID

 

    set rowcount 1

    select @row_id = Row from #mytemp

end

set rowcount 0

 

GO"

This sql query runs against a database and generates an email notification on the status of a set of applications as being up or down. 

 

I finally have a structure built but can't figure out where to place the sql query. Here's what I have as a reference:

$ServerInstance = "SQLSERVERNAME\INSTANCENAME "

$Database = "SQLDATABASENAME "

$ConnectionTimeout = 30

$Query = "CAN I PASTE SQL QUERY HERE"

$QueryTimeout = 120

 

#Establish database connection and execute query

$conn=new-object System.Data.SqlClient.SQLConnection

$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout

$conn.ConnectionString=$ConnectionString

$conn.Open()

$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)

$cmd.CommandTimeout=$QueryTimeout

$ds=New-Object system.Data.DataSet

$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)

[void]$da.fill($ds)

$conn.Close()

$ds.Tables

 

Attempting to run this in PowerGui generates "unexpected token" errors so I figure I must be doing something wrong and could use some direction. 

Any responses appreciated.

Parents
No Data
Reply Children
No Data