Trouble run UD under IIS and use SQL SMO

Universal Dashboard Universal Dashboard Help
1

Hey all,

I am having trouble loading assemblies for SMO then UD is running under IIS.
This code works without issue if I just run it under pwsh.exe

the udlog give me this:
12:27:41 [Warn] ExecutionService Error executing endpoint script. Could not load file or assembly ‘System.Data.SqlClient, Version=4.2.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. The system cannot find the file specified.

I use the following code:

- Help find and save the location of the SMO dll’s in a PowerShell variable:

$smopath = Join-Path ((Get-Package Microsoft.SqlServer.SqlManagementObjects).Source `

| Split-Path) (Join-Path lib netstandard2.0)

- Help find and save the location of the SMO dll’s in a PowerShell variable:

$sqlclientpath = Join-Path ((Get-Package System.Data.SqlClient).Source `

| Split-Path) (Join-Path lib netstandard2.0)

Add types to load SMO Assemblies only:

Add-Type -Path (Join-Path $sqlclientpath System.Data.SqlClient.dll)

Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.Smo.dll)

Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.ConnectionInfo.dll)

$SQLSrvConn = new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($Server, $UserID, $Password);

$SQLSrvConn.UseIntegratedSecurity = $false;

$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn)

$database = $SQLSrvObj.Databases |where name -eq $DB

$return = ($database.ExecuteWithResults($query))

2

The code inside Universal Dashboard runs in another .net runspace, so it doesn’t recognize outside dll and function.

I’m using SMO also and my dashboard is running perfectly under IIS. You need to do something like this:

Function MyFunction {

        [parameter(Mandatory = $true)]
        [string]$Database,
        [parameter(Mandatory = $false)]
        [string]$ServerInstance = "SQLServer"

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

    $Query = "Select * from test"
    $SqlConnectionString = "Data Source=$($ServerInstance);Initial Catalog=$($Database);Integrated Security=SSPI"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection($SqlConnectionString)
    $SqlCommand = $SqlConnection.CreateCommand()
    $SqlCommand.CommandText = $Query
    $SqlConnection.Open()
    $SqlDataReader = $SqlCommand.ExecuteReader()


    while ($SqlDataReader.Read()) {
                                       

             [...]
	}
}

Then you need to do this :

$FunctionInit = New-UDEndpointInitialization -Function @(“MyFunction”)`

New-UDDashboard […] -EndpointInitialization $FunctionInit