none
Powershell/SQL Script from .net activity failing RRS feed

  • Question

  • I have a Powershell script that is set to query a database table for an entry. It then returns a YES if present and a NO if not present.

    Script:

    Import-Module sqlps
    #Connect to SQL Database
    $sqlserver = "MYSQLSERVER"
    $approvaldatabase = "DATABASE"
    $UserSqlQuery = "DECLARE @DUP VARCHAR(MAX)
    SET @DUP = 'MYVARIABLE'
    
    SELECT TOP 1 [RequestGUID], ""DUP"" = 
    CASE 
    WHEN RequestGUID = @DUP THEN 'YES'
    --WHEN RequestGUID != @DUP THEN 'NULL'
    ELSE 'NO'
    END
      FROM [Software Approval GUID].[dbo].[RequestGUID]
      --where RequestGUID = @DUP"
    
    $conn = New-Object System.Data.SqlClient.SqlConnection("Server = $sqlserver; Database = $approvaldatabase; Integrated Security = True")
    $conn.Open()
    $sqlcmd = $conn.CreateCommand()
    
    # declaration not necessary, but good practice
    $resultsDataTable = New-Object System.Data.DataTable
    $resultsDataTable = ExecuteSqlQuery $sqlserver $approvaldatabase $UserSqlQuery 
    
    function ExecuteSqlQuery ($sqlserver, $approvaldatabase, $SQLQuery) {
        $Datatable = New-Object System.Data.DataTable
        
        $Connection = New-Object System.Data.SQLClient.SQLConnection
        $Connection.ConnectionString = "server='$sqlserver';database='$approvaldatabase';trusted_connection=true;"
        $Connection.Open()
        $Command = New-Object System.Data.SQLClient.SQLCommand
        $Command.Connection = $Connection
        $Command.CommandText = $SQLQuery
        $Reader = $Command.ExecuteReader()
        $Datatable.Load($Reader)
        $Connection.Close()
        
        return $Datatable
    }
    

    In powershell, both X86 and X64 in case that was the issue returns something like this:

    RequestGUID                                                              DUP                                                                    
    -----------                                                              ---                                                                    
    00D3B213-05E9-4D09-BD3C-549916656276                                     NO

    However when it runs in Orchestrator I get this error:

    The term 'ExecuteSqlQuery' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

    What am I missing?

    Wednesday, November 9, 2016 12:13 PM

All replies