if you didn't pass any password for SQLCMD it will try to read it from stdin (I can do it manually and it works).
I started cmd process to which i pass the streamWriter with the password as standard input.
Process starts correctly and I can print passed stream, but i got
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'sa'
Is there any way to make sqlcmd read stdin?
C# code
ProcessStartInfo processStartInfo = new ProcessStartInfo
{
FileName = @"path_to_my_script",
UseShellExecute = false,
CreateNoWindow = false,
RedirectStandardInput = true
};
var process = Process.Start( processStartInfo );
StreamWriter myStreamWriter = process.StandardInput;
string pass = "123";
myStreamWriter.WriteLine( pass );
myStreamWriter.Close();
myScript (modified query)
SqlCmd -S DESKTOP-UR7LHEE -U sa -Q "SELECT * FROM myDb"
CodePudding user response:
The following shows how to use System.Diagnostics.Process to run a SQL script using sqlcmd. However, you may want to see if Server Management Objects (SMO) meets your needs.
The following has been tested:
Add the following using statements:
- using Microsoft.Win32;
- using System.IO;
- using System.Diagnostics;
First, we'll create a method to query the registry to find the path to sqlcmd.exe:
Note: If the directory/folder that sqlcmd.exe exists in is in your PATH environment variable, then the method below is unnecessary - one can use "sqlcmd.exe" instead of the fully-qualified filename.
GetSqlCmdPath:
private string GetSqlCmdPath()
{
//get the fully-qualified path for sqlcmd.exe
string sqlCmdPath = string.Empty;
using (RegistryKey key = RegistryKey.OpenBaseKey(Microsoft.Win32.RegistryHive.LocalMachine, RegistryView.Registry64))
{
using (RegistryKey subkey = key.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server"))
{
if (subkey != null)
{
string[] subkeyNames = subkey.GetSubKeyNames();
if (subkeyNames != null)
{
foreach (string name in subkeyNames)
{
string clientSetupSubkey = Path.Combine(name, "Tools", "ClientSetup");
using (RegistryKey subkeyClientSetup = subkey.OpenSubKey(clientSetupSubkey))
{
if (subkeyClientSetup != null)
{
string[] valueNames = subkeyClientSetup.GetValueNames();
if (valueNames != null)
{
foreach (string vName in valueNames)
{
if (vName == "Path" || vName == "ODBCToolsPath")
{
//get value
string valPath = subkeyClientSetup.GetValue(vName)?.ToString();
//check if sqlcmd.exe exists
if (File.Exists(Path.Combine(valPath, "sqlcmd.exe")))
{
sqlCmdPath = Path.Combine(valPath, "sqlcmd.exe");
}
}
}
}
}
}
}
}
}
}
}
return sqlCmdPath;
}
Next, create a method that uses an instance of Process to execute the script:
RunProcessSqlCmd:
private void RunProcessSqlCmd(string scriptFilename, string logFilename, string password)
{
string sqlCmdPath = GetSqlCmdPath();
if (String.IsNullOrEmpty(sqlCmdPath))
throw new Exception("Error: Fully-qualified path to 'sqlcmd.exe' could not be determined.");
ProcessStartInfo startInfo = new ProcessStartInfo ()
{
Arguments = String.Format(@"-S .\SQLExpress -U appAdmin -i {0} -o {1}", scriptFilename, logFilename),
//Arguments = String.Format(@"-S .\SQLExpress -U appAdmin -i {0}", scriptFilename),
CreateNoWindow = true,
FileName = sqlCmdPath,
RedirectStandardError = true,
RedirectStandardInput = true,
RedirectStandardOutput = true,
UseShellExecute = false,
WindowStyle = ProcessWindowStyle.Hidden
};
using (Process p = new Process () { StartInfo = startInfo, EnableRaisingEvents = true})
{
//subscribe to event and add event handler code
p.ErrorDataReceived = (sender, e) =>
{
if (!String.IsNullOrEmpty(e.Data))
{
//ToDo: add desired code
Debug.WriteLine("Error: " e.Data);
}
};
//subscribe to event and add event handler code
p.OutputDataReceived = (sender, e) =>
{
if (!String.IsNullOrEmpty(e.Data))
{
//ToDo: add desired code
Debug.WriteLine("Output: " e.Data);
}
};
//start
p.Start();
p.BeginErrorReadLine(); //begin async reading for standard error
p.BeginOutputReadLine(); //begin async reading for standard output
using (StreamWriter sw = p.StandardInput)
{
//provide values for each input prompt
//ToDo: add values for each input prompt - changing the for loop as necessary
//Note: Since we only have 1 prompt, using a loop is unnecessary - a single 'WriteLine' statement would suffice
for (int i = 0; i < 1; i )
{
if (i == 0)
sw.WriteLine(password); //1st prompt
else
break; //exit
}
}
//waits until the process is finished before continuing
p.WaitForExit();
}
}
Note: The code above writes the output of the script to a log file. If you'd rather write the output to StandardOutput, change the following:
From:
Arguments = String.Format(@"-S .\SQLExpress -U appAdmin -i {0} -o {1}", scriptFilename, logFilename),
To:
Arguments = String.Format(@"-S .\SQLExpress -U appAdmin -i {0}", scriptFilename),
Also, since the logFilename
parameter will no longer be used, you can remove it.
Usage:
//create test script
string logFilename = Path.Combine(Path.GetTempPath(), System.Reflection.Assembly.GetExecutingAssembly().GetName().Name "_ScriptLog.txt");
string scriptFilename = Path.Combine(Path.GetTempPath(), System.Reflection.Assembly.GetExecutingAssembly().GetName().Name "_Script.sql");
Debug.WriteLine($"scriptFilename: {scriptFilename} logFilename: {logFilename}");
//string scriptText = "use master;" System.Environment.NewLine;
//scriptText = "SELECT name, database_id from sys.databases;";
string scriptText = "SELECT name, database_id from sys.databases;";
File.WriteAllText(scriptFilename, scriptText);
RunProcessSqlCmd(scriptFilename, logFilename, "myPassword123");
Resources:
- System.Diagnostics.Process
- Download sqlcmd utility
- sqlcmd - Use the utility
- View list of databases on SQL Server
Server Management Objects (SMO) Resources: