Urgent : Running SQL Script files through a SqlCommand object?
-
Hi there, I have generated some SQL script-files using the SQL-Enterprise Manager (for creation of tables, stored procedures, etc.). When running those script files through the isql or osql utility, I have the possibility to capture the output to a file, which is sort of importand to me. What I'd like to do is to write a small utility (Windows app in c#) which will execute my sql-script-files on the SQL server. Again, I need to capture the output. Can this be done using an SqlCommand-object providing the contents of each individual script-file as the command text or do I have to use isql. In both ways I'd like to know how it is possible to capture the output. The output is actually only required if there have been errors during batch-execution. Any help is greatly appreceated. Thanks in advance. Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs. (Lounge/David Wulff)
-
Hi there, I have generated some SQL script-files using the SQL-Enterprise Manager (for creation of tables, stored procedures, etc.). When running those script files through the isql or osql utility, I have the possibility to capture the output to a file, which is sort of importand to me. What I'd like to do is to write a small utility (Windows app in c#) which will execute my sql-script-files on the SQL server. Again, I need to capture the output. Can this be done using an SqlCommand-object providing the contents of each individual script-file as the command text or do I have to use isql. In both ways I'd like to know how it is possible to capture the output. The output is actually only required if there have been errors during batch-execution. Any help is greatly appreceated. Thanks in advance. Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs. (Lounge/David Wulff)
isql has output parameter : /o public int Exec( string sCommand ) { int nResult = 0; m_oInstallProcess = new Process(); m_oInstallProcessInfo = new ProcessStartInfo("cmd"); m_oInstallProcessInfo.UseShellExecute = false; m_oInstallProcessInfo.RedirectStandardInput = true; m_oInstallProcessInfo.RedirectStandardOutput = true; m_oInstallProcessInfo.RedirectStandardError = true; m_oInstallProcessInfo.CreateNoWindow = true; m_oInstallProcess.StartInfo = m_oInstallProcessInfo; m_oInstallProcess.Start(); m_oCommandWriter = m_oInstallProcess.StandardInput; m_oResultReader = m_oInstallProcess.StandardOutput; m_oErrorReader = m_oInstallProcess.StandardError; m_oCommandWriter.AutoFlush = true; m_oCommandWriter.WriteLine( sCommand ); m_oCommandWriter.Close(); m_oCommandWriter = null; string s = m_oResultReader.ReadToEnd(); m_sErrorMsg = m_oErrorReader.ReadToEnd(); if( s.IndexOf("Cannot open input file") > 0 ) throw new Exception( "Command:\r\n"+sCommand+"\r\nError:\r\nCannot open input file!" ); else if( m_sErrorMsg.Trim().Length > 0 ) throw new Exception( "Command:\r\n"+sCommand+"\r\nError:\r\n"+m_sErrorMsg+"" ); else System.Threading.Thread.Sleep( 10 ); return nResult ; } D!shan
-
isql has output parameter : /o public int Exec( string sCommand ) { int nResult = 0; m_oInstallProcess = new Process(); m_oInstallProcessInfo = new ProcessStartInfo("cmd"); m_oInstallProcessInfo.UseShellExecute = false; m_oInstallProcessInfo.RedirectStandardInput = true; m_oInstallProcessInfo.RedirectStandardOutput = true; m_oInstallProcessInfo.RedirectStandardError = true; m_oInstallProcessInfo.CreateNoWindow = true; m_oInstallProcess.StartInfo = m_oInstallProcessInfo; m_oInstallProcess.Start(); m_oCommandWriter = m_oInstallProcess.StandardInput; m_oResultReader = m_oInstallProcess.StandardOutput; m_oErrorReader = m_oInstallProcess.StandardError; m_oCommandWriter.AutoFlush = true; m_oCommandWriter.WriteLine( sCommand ); m_oCommandWriter.Close(); m_oCommandWriter = null; string s = m_oResultReader.ReadToEnd(); m_sErrorMsg = m_oErrorReader.ReadToEnd(); if( s.IndexOf("Cannot open input file") > 0 ) throw new Exception( "Command:\r\n"+sCommand+"\r\nError:\r\nCannot open input file!" ); else if( m_sErrorMsg.Trim().Length > 0 ) throw new Exception( "Command:\r\n"+sCommand+"\r\nError:\r\n"+m_sErrorMsg+"" ); else System.Threading.Thread.Sleep( 10 ); return nResult ; } D!shan
Hi D!shan, thanks for your reply! Your example, which I find extremely interesting is unfortunately not, what I was looking for, because it assumes, that the person running my tool is required to have osql installed on his/her machine. So I was actually looking for a way to do this through an SqlCommand object. What I've done now is that I've written a parser for SQL script files, which creates a queue of individual commands which can actually be executed through the SqlCommand. Now I'm looking for a way to hook up the output from Sql-Server when doing ExecuteNonQuery()... Regardless of that, I find your code very useful for many other situations. Be sure of a good rating and thanks a lot. Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs. (Lounge/David Wulff)