Executing PowerShell Script with Invoke-SqlCmd cmdlet from C#

Question

I need to execute a simple PS script that contains a Invoke-Sqlcmd cmdlet from a C# app. When the script is executed through the PS window it works fine. In the C# app nothing happens.

I've tried other scripts from the C# app and got results, but with this specific script something went wrong.

using (var powerShell = PowerShell.Create())
            {
                powerShell.AddScript(psScript);
                powerShell.AddParameter("Username", "user");
                powerShell.AddParameter("Password", "password");
                powerShell.AddParameter("Server", server);
                powerShell.AddParameter("Script", script);

                var result = powerShell.Invoke();
            }

PS script:

param ([String]$Username, [String]$Password, [String]$Server, [String]$Script)

Import-Module SqlPs

Invoke-Sqlcmd -ServerInstance $Server -Username $Username -Password $Password -Query $Script -QueryTimeout 750 -ConnectionTimeout 600

Does anyone know how to solve the problem?

can you verify that the PS C# is launching indeed has access to SQLPS module and does load it successfully? — 4c74356b41
It doesn't load properly. After Duke of Muppets answer, I've inspect the Streams.Error property and got this error: "Cannot load Windows PowerShell snap-in C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SqlPs\Microsoft.SqlServer.Management.PSSnapins.dll because of the following error: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information. 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." — bymyslf

Neil Bostrom Neil Bostrom · Accepted Answer · 2017-01-17T19:36:35

After adding some some error logging code to your function:

        foreach (var error in powerShell.Streams.Error)
        {
           Console.WriteLine("Error: {0}", error);
        }

It printed an issue saying your parameters were not reaching the script. After some digging around I found this SO post on how to pass parameters to Powershell:

Execute PowerShell Script from C# with Commandline Arguments

Cut down version of that solution here:

        using (Runspace runspace = RunspaceFactory.CreateRunspace(RunspaceConfiguration.Create()))
        {
            runspace.Open();

            using (Pipeline pipeline = runspace.CreatePipeline())
            {
                Command scriptCommand = new Command(psScript);
                scriptCommand.Parameters.Add(new CommandParameter("Username", "user"));
                scriptCommand.Parameters.Add(new CommandParameter("Password", "password"));
                scriptCommand.Parameters.Add(new CommandParameter("Server", server));
                scriptCommand.Parameters.Add(new CommandParameter("Script", script));
                pipeline.Commands.Add(scriptCommand);

                var results = pipeline.Invoke();

                foreach (var item in results)
                {
                    Console.WriteLine("Output: {0}", item);
                }
            }
        }

As mentioned by @bymyself, if your running into issues with exception:

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.

Try adding this to your app.config (for a Console app):

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

If your using MSTest, try the solution at: http://reedcopsey.com/2011/09/15/setting-uselegacyv2runtimeactivationpolicy-at-runtime/

Executing PowerShell Script with Invoke-SqlCmd cmdlet from C#

1 Answers