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
– 4c74356b41, Commented Jan 17, 2017 at 18:54
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
– bymyslf, Commented Jan 18, 2017 at 9:01

Community · Accepted Answer · 2017-05-23 12:08:58Z

0

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/

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Jan 17, 2017 at 19:36

Neil Bostrom

2,35921 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

bymyslf Over a year ago

Has pointed above, after your 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.SqlServe‌r.Management.PSSnapi‌ns.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 Over a year ago

Already tried to put <startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" /> </startup> in the app.config, but without any results.

Neil Bostrom Over a year ago

Are you running it in a console app? I had the same issue with that error message in my test console app. I added to my app.config: <?xml version="1.0" encoding="utf-8" ?> <configuration> <startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" /> </startup> </configuration>

Neil Bostrom Over a year ago

If you switch to using the Runspace and Pipeline code above, it resolved all the issues for me.

bymyslf Over a year ago

Using the Runsapce and Pipeline code I've got a System.Management.Automation.CmdletInvocationException with the same error as running with the prior code. If it matters, this a MSTest project.

|

Collectives™ on Stack Overflow

Executing PowerShell Script with Invoke-SqlCmd cmdlet from C#

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related