devxlogo

ExecuteSqlScriptsWithOsql – Executing an array of script files with the OSQL utility

ExecuteSqlScriptsWithOsql – Executing an array of script files with the OSQL utility

' Execute an array of sql script files with batch statements,'  by using the OSQL utility'   - dbName is the name of the destination database'   - isWinAuth, login and pwd specify whether to use the integrated Windows ' authentication, or the SQL Server's login-password otherwise'' Example:'   Dim script1 As String = "C:ScriptsCreateDbTables.sql"'   Dim script2 As String = "C:ScriptsCreateDbSprocs.sql"'   Dim script3 As String = "C:ScriptsInsertData.sql"'   ' use Windows integrated security'   ExecuteSqlScriptsWithOsql("TestDB", True, "", "", script1, script2, script3)'   ' use SQL Server's security'   ExecuteSqlScriptsWithOsql("TestDB", False, "loginname", "pwd", script1,'  script2, script3)Sub ExecuteSqlScriptsWithOsql(ByVal dbName As String, _    ByVal isWinAuth As Boolean, ByVal login As String, ByVal pwd As String, _    ByVal ParamArray scriptFiles() As String)    Dim osqlParams As String = ""    ' the OSQL utility requires different parameters if using the Win or SQL     ' server security    If (isWinAuth) Then        osqlParams = String.Format("-E -d {0} -i ", dbName)    Else        osqlParams = String.Format("-U {0} -P {1} -d {2} -i ", login, pwd, _            dbName)    End If    ' execute each script file in the scripts array    Dim proc As New Process    proc.StartInfo.FileName = "osql.exe"    proc.StartInfo.CreateNoWindow = True    proc.StartInfo.WindowStyle = ProcessWindowStyle.Hidden    Dim scriptFile As String    For Each scriptFile In scriptFiles        proc.StartInfo.Arguments = osqlParams & """" & scriptFile + """"        proc.Start()        ' wait until the scrip execution ends        proc.WaitForExit()    NextEnd Sub

devx-admin

Share the Post: