Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: VB7
Expertise: Intermediate
Jul 7, 2003



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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:\Scripts\CreateDbTables.sql"
'   Dim script2 As String = "C:\Scripts\CreateDbSprocs.sql"
'   Dim script3 As String = "C:\Scripts\InsertData.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)
        osqlParams = String.Format("-U {0} -P {1} -d {2} -i ", login, pwd, _
    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 + """"
        ' wait until the scrip execution ends
End Sub
Marco Bellinaso
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date