2

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?

bymyslf
  • 23
  • 6
  • can you verify that the PS C# is launching indeed has access to SQLPS module and does load it successfully? – 4c74356b41 Jan 17 '17 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 Jan 18 '17 at 09:01

1 Answers1

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/

Community
  • 1
  • 1
Neil Bostrom
  • 2,254
  • 21
  • 24
  • 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 Jan 18 '17 at 09:02
  • Already tried to put in the app.config, but without any results. – bymyslf Jan 18 '17 at 09:35
  • 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: – Neil Bostrom Jan 18 '17 at 15:26
  • If you switch to using the Runspace and Pipeline code above, it resolved all the issues for me. – Neil Bostrom Jan 18 '17 at 15:27
  • 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. – bymyslf Jan 18 '17 at 15:45
  • I've got this running using this trick: http://reedcopsey.com/2011/09/15/setting-uselegacyv2runtimeactivationpolicy-at-runtime/ – bymyslf Jan 18 '17 at 18:46
  • Great, if it all working for you now. Welcome to accept the answer. I'll add your note about that trick. – Neil Bostrom Jan 18 '17 at 19:37
  • Thanks @Duke of Muppets. – bymyslf Jan 19 '17 at 08:56