topic Re: Data Standard 2016 - Run SQL query error in Vault Customization Forum

Data Standard 2016 - Run SQL query error

Anonymous — Wed, 07 Dec 2016 11:27:22 GMT

I want to run a SQL query to check if the value "item number" in my Vault Data Stanadard dialog and a value in a SQL table is the same. If that is true set a check box = checked. This function runs OnTabContextChanged.

With this function i want to check if a item is transferd to the ERP System and the user can see this in the datasheet of the item. It is only for troubleshooting if the interface between Vault and the ERP system doesn't work proberly.

I used the cmdlet Invoke-Sqlcmd and everything work fine. But if you start Vault and go the datasheet you get an error

For every non german reader:

"Mixed mode assembly is built against version ‘v2.0.50727′ of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information"

Than i tried something from a blogpost:

http://stackoverflow.com/questions/6425707/mixed-mode-assembly-is-built-against-version-v2-0-50727-of-the-runtime

Edit the Connectivity.VaultPro.exe.config and add the lines from the blog post.

<startup>
<useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/>
</startup></configuration>

And everything is working fine.

But am I allowed to add these lines in the config? Or is there a reason don't do this?

Re: Data Standard 2016 - Run SQL query error

Tim_Kreutzer — Sat, 24 Dec 2016 13:38:37 GMT

Sorry for going slightly off topic, but are you aware of the fact that the sqlps module is not part of the standard dot net framework? If you deploy to machines which do not have some kind of sql installation (server, management studio, etc...), your code will not work without installing sqlps manually.

You could use an instance of system.data.sqlclient.sqlconnection, which is in default .NET

It will work without tweaking any config files, too. Just google powershell + sqlconnection, it is pretty straightforward.

Since the code in the Data Standard ps1-Files is readable and changeable by the user, you may want to double check that he cannot do something nasty by altering the query. I'd perhaps go for a stored procedure which returns only the value of interest and give the users permission to use this and nothing else.

Re: Data Standard 2016 - Run SQL query error

Tim_Kreutzer — Tue, 27 Dec 2016 08:08:07 GMT

First paragraph should read: sqlps is not part auf default Powershell installation, sorry about that. (I can not edit my post anymore?)

Re: Data Standard 2016 - Run SQL query error

Anonymous — Tue, 10 Jan 2017 13:39:31 GMT

Hi Tim,

sorry for my late answer. I will check your solution and will post my result.

Re: Data Standard 2016 - Run SQL query error

Anonymous — Tue, 10 Jan 2017 15:19:16 GMT

Now i found a better solution with your hint

https://www.administrator.de/frage/powershell-sql-abfrage-266193.html

I used this function from the link above

function Get-SQLTableContents($query,$server,$database,$username,$password){
    $conn = new-object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Server=$server;Database=$database;Integrated Security=False;UID=$username;PWD=$password"
        try{
            $conn.Open()
            $cmd = $conn.CreateCommand()
            $cmd.CommandText = $query
            $cmd.Connection = $conn

            $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
            $SqlAdapter.SelectCommand = $cmd
            $dt = New-Object System.Data.DataTable
            $SqlAdapter.Fill($dt) | out-null
            $conn.close()
            return $dt
        }
        catch{
            echo "Fehler: $($_.Exception.Message)"
            $conn.close()
            return $false
        }
}

$data = Get-SQLTableContents "Select * from TestTable" "192.168.1.95" "TestDB" "sqluser" "Passw0rd"
$data