Running sqlcmd or invoke-sqlcmd in powershell script in c#

Question

Been using:

            private string RunScript(string storeNumber)
    {
        //string importSQL = "Import-Module sqlserver -DisableNameChecking";
        //string executionPolicy = "Set-ExecutionPolicy -ExecutionPolicy Unrestricted";
        //string script = "ping databaseName";
        //string script = "sqlcmd - h - 1 - Q \"set nocount on;SELECT System_Date FROM Location_Codes\" - d pos - S databaseName | out-string";
        string script = "invoke-sqlcmd -Query \"use pos set nocount on; SELCECT System_date FROM location_codes\" -serverinstance databaseName| out-string";
        Runspace runspace = RunspaceFactory.CreateRunspace();
        runspace.Open();

        Pipeline pipeline = runspace.CreatePipeline();
        //pipeline.Commands.AddScript(importSQL);
        //pipeline.Commands.AddScript(executionPolicy);
        pipeline.Commands.AddScript(script);


        Collection<PSObject> results = pipeline.Invoke();

        runspace.Close();

        StringBuilder stringBuilder = new StringBuilder();
        foreach(PSObject obj in results)
        {
            stringBuilder.AppendLine(obj.ToString());
        }
        MessageBox.Show(stringBuilder.ToString());
        return stringBuilder.ToString();

    }

When trying sqlcmd I get no error but nothing gets returned (even though typing the command directly into powershell gets me the data I want)

When trying invoke-sqlcmd I get a CmdletInvocationException thrown. For that error I've added

      <startup useLegacyV2RunttimeActivationPolicy="true">
<supportedRuntime version="v4.0"/>

Added the ping command as a test. That does return back data. Any help would be appreciated!

score 0 · Answer 1 · answered May 23 '19 at 16:52

This may be helpful to you change from
string script = "invoke-sqlcmd -Query \"use pos set nocount on; SELCECT System_date FROM location_codes\" -serverinstance databaseName| out-string";

To

string script = @"invoke-sqlcmd -Query 'use pos set nocount on Go SELCECT System_date FROM location_codes' -serverinstance databaseName| out-string";

The reason I use a single colon is, For Powershell '' is used to represent string And I don't think Powershell recognize ';' for sql delimiter.

Running sqlcmd or invoke-sqlcmd in powershell script in c#

1 Answers1