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.

  • Yes, you should be able to just paste in your query between the quotes of your $query variable.

    Or you can go $query = get-content c:\myquery.txt

    Just be wary, when quoting it, of double or single quotes within the query conflicting with the outer quotes.  If you use the get-content method, I seem to remember having blank lines at the end of the file causing me problems.

  • Here's what I have after pasting in the $query: 

     

    $ServerInstance = "SQLSERVERNAME\SQLINSTANCENAME"

    $Database = "SQLDBNAME"

    $ConnectionTimeout = 30

    $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"

    $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

     

    I save and when attempting to run it from the server using the powershell command console or from within the PowerGui console I get the following error: 

    At C:\Temp\Scripts\myquery.ps1:49 char:22

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

    +                      ~~~~~~

    Unexpected token '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"' in expression or statement.

     

     

        + CategoryInfo          : ParserError: (:) [], ParseException

        + FullyQualifiedErrorId : UnexpectedToken

     

    Gonna try option #2 and call it from a .txt file.
  • $ServerInstance = "SQLSRVNAME\SQLSRVINSTANCE"

    $Database = "SQLDB"

    $ConnectionTimeout = 30

    $Query = "get-content C:\Temp\Scripts\tsql.txt"

    $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

     

    Returns the following error: 

    Exception calling "Fill" with "1" argument(s): "Incorrect syntax near '-'."

    At C:\Temp\MS\SCOM\Scripts\tsql.ps1:17 char:1

    + [void]$da.fill($ds)

    + ~~~~~~~~~~~~~~~~~~~

        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

        + FullyQualifiedErrorId : SqlException

    Checking the file at that location and I see this to be the culprit: [void]$da.fill($ds)

    A little stumped!!

  • Hm, I'm not still learning Powershell, and I'm not familiar with you [void]$da.fill($ds) statement - specifically how [void] is being used.  This is the code I use for querying our database:

     

    # Connect to SQL and query data, extract data to SQL Adapter
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "server=$Server;database=$Database;integrated security=true;"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $nRecs = $SqlAdapter.Fill($DataSet)

    $nRecs | Out-Null

    #Populate Hash Table
    $objTable = $DataSet.Tables[0]

     

     

    From there I can just do:

    $objTable | export-csv c:\myfile.txt

     

    Sorry it's taken so long to respond, hope this works for you.

  • Void here means essentially generate no output.  In other words ...

    [void]$da.fill($ds)

    ... is equivalent to ...

    $da.fill($ds) | out-null

  • @Bob

    Ah, ok, thanks.  I was wondering if it might be something like that.

     

    @nusolutions

    If you're still around...

    The error you're receiving means, I think, that the query failed or your connection to the database was rejected in some way.  Since you're using Integrated Security in $connectionstring, have you verified that you have the correct permissions and or user account set up in the database?  Can you check the database connection logs and see what it looks like from the server-side?

  • Still here and trying to make this work. My powershell is hit or miss so bear with me. 

    I've confirmed my access to the SQL database using my account and from a random server with sql studio installed I am able to connect to that instance. My code:

     

    $ServerInstance = "dbserver\instancename"

    $Database = "database"

    $ConnectionTimeout = 30

    $Query = "C:\Temp\MS\SCOM\Scripts\mysqlqueryqa.txt"

    $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

     

    A second run produces this similar error: C:\Temp\MS\SCOM\Scripts> .\mysqlqueryqa.ps1

    Exception calling "Fill" with "1" argument(s): "Incorrect syntax near '\'."

    At C:\Temp\MS\SCOM\Scripts\mysqlqueryqa.ps1:16 char:1

    + [void]$da.fill($ds)

    + ~~~~~~~~~~~~~~~~~~~

        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

        + FullyQualifiedErrorId : SqlException

     

    Error log shows the following repeated error: 

    Error 1000002, severity 16, state 1 was raised, but no message with that error number was found in sys.messages...

     

    Anyone see where I'm going wrong here??

  • It looks like you're still not completing the query.

    Looking here:

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand(v=vs.110).aspx

    it says that sqlcommand takes a string and a connection, but your $query is just the path to your query file.  It's a string, but not a sql query.  You need to assign the content of your file to $query.

    Try replacing $query = "C:\Temp\MS\SCOM\Scripts\mysqlqueryqa.txt"

    with:  $query = get-content "C:\Temp\MS\SCOM\Scripts\mysqlqueryqa.txt"