Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

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)
    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()
    Next
End Sub
Marco Bellinaso
 
Comment and Contribute

 

 

 

 

 


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

 

 

Sitemap