Execute SQL Script

**John Winterbottom** · Nov 12 '05, 10:34 PM

Re: Execute SQL Script

"Lyle Fairfield" <MissingAddress @Invalid.Com> wrote in message
news:Xns94BFB45 77F672FFDBA@130 .133.1.4...[color=blue]
> MS-SQL Server utilities Enterprise Manager and Query Analyzer will model
> almost any MS-SQL object as a simple script file with a default ".sql"
> extension.
>
> But how to "run these files? They are in a slightly modifies character set
> and contain commands only usable by the Server's SQL utilities. If one
> tries to use them on a machine which does not have the SQL utilities
> installed, one seems to have a laborious cut-and-paste task.
>
> (Of course, if Access has a simple and better way, I hope you will let me
> know.)
>[/color]

Not that I know of! We wrote a utility that does similar - but it can be
used to execute a bunch of scripts one after the other.

http://www.assaynet.com/downloads/Access/UpdateSQL.mdb if anyone wants a
copy.

We use it for updating our clients databases. AFAIK there are also some
commercial ones available that do similar.

**Tom van Stiphout** · Nov 12 '05, 10:34 PM

Re: Execute SQL Script

Check out osql.exe - it ships with SQL Server.
-Tom.

"Lyle Fairfield" <MissingAddress @Invalid.Com> wrote in message
news:Xns94BFB45 77F672FFDBA@130 .133.1.4...[color=blue]
> MS-SQL Server utilities Enterprise Manager and Query Analyzer will model
> almost any MS-SQL object as a simple script file with a default ".sql"
> extension.
>
> But how to "run these files? They are in a slightly modifies character set
> and contain commands only usable by the Server's SQL utilities. If one
> tries to use them on a machine which does not have the SQL utilities
> installed, one seems to have a laborious cut-and-paste task.
>
> (Of course, if Access has a simple and better way, I hope you will let me
> know.)
>
> I have written a tiny bit of utility code that
> allows one to select such an sql file,
> decodes and parses the script
> and allows one to Execute or not Execute its various parts.
>
> You can find it at
> http://ffdba.com/downloads
>
> but I think it is small enough to post here:
>
> 'Don't mess with this unless you are experienced and capable.
> 'Back up your database before using.
>
> Option Explicit
> Option Base 0
>
> Private Declare Function CommDlgExtended Error Lib "comdlg32.d ll" () As[/color]
Long[color=blue]
>
> Private Declare Function GetOpenFileName Lib "comdlg32.d ll" _
> Alias "GetOpenFileNam eA" _
> (pOpenfilename As OPENFILENAME) As Long
>
> Private Type OPENFILENAME
> lStructSize As Long
> hwndOwner As Long
> hInstance As Long
> lpstrFilter As String
> lpstrCustomFilt er As String
> nMaxCustFilter As Long
> nFilterIndex As Long
> lpstrFile As String
> nMaxFile As Long
> lpstrFileTitle As String
> nMaxFileTitle As Long
> lpstrInitialDir As String
> lpstrTitle As String
> flags As Long
> nFileOffset As Integer
> nFileExtension As Integer
> lpstrDefExt As String
> lCustData As Long
> lpfnHook As Long
> lpTemplateName As String
> End Type
>
> Private Const OFN_HIDEREADONL Y = &H4
> Private Const OFN_NOCHANGEDIR = &H8
> Private Const OFN_SHAREAWARE = &H4000
> Private Const MAX_PATH = 260
> Private Const DELIMITER = vbNewLine & "GO" & vbNewLine
>
> Private Sub FindAndExecuteS QLScript()
> Dim Script As String
> Dim FullPathToFile As String
> FindSQLScript Script, FullPathToFile
> Debug.Print Script
> If MsgBox( _
> "Execute " _
> & vbNewLine _
> & vbNewLine _
> & FullPathToFile & "?" _
> & vbNewLine _
> & vbNewLine _
> & "Are you SURE?" _
> & vbNewLine _
> & vbNewLine _
> & "If you're not sure, choose No," _
> & vbNewLine _
> & "and examine the immediate window" _
> & vbNewLine _
> & "where the Script has been copied.", _
> vbQuestion Or vbYesNo, _
> "This Procedure Can Cause Significant Damage To Your
> Database.") _
> = vbYes Then
> ExecuteSQLScrip t Script
> End If
> End Sub
>
> Private Sub ExecuteSQLScrip t( _
> ByVal Script As String)
> Dim aSubScripts() As String
> Dim SubScript As String
> Dim z As Long
> aSubScripts = Split(Script, DELIMITER)
> For z = 0 To UBound(aSubScri pts)
> aSubScripts(z) = Trim$(aSubScrip ts(z))
> SubScript = aSubScripts(z)
> If Len(SubScript) > 255 Then
> SubScript = Left$(aSubScrip ts(z), InStr(255, aSubScripts(z),
> vbNewLine))
> End If
> If Len(Replace(Sub Script, vbNewLine, "")) > 1 Then
> If Left$(SubScript , 3) <> "SET" Then
> If MsgBox("EXECUTE " & vbNewLine & SubScript, vbQuestion[/color]
Or[color=blue]
> vbYesNo, "FFDBA") = vbYes Then
> CurrentProject. Connection.Exec ute aSubScripts(z)
> End If
> End If
> End If
> Next z
> End Sub
>
> Private Sub FindSQLScript( _
> ByRef Script As String, _
> ByRef FullPathToFile As String, _
> Optional ByVal Owner As String, _
> Optional ByVal ChatPartner As String)
>
> Dim EntireMessage As String
> Dim FileNumber As Integer
>
> ' get full path to file
> If Len(FullPathToF ile) = 0 Then
> FullPathToFile = GetFile()
> End If
>
> ' get entire message
> FileNumber = FreeFile()
> Script = String(FileLen( FullPathToFile) , vbNullChar)
> Open FullPathToFile For Binary As #FileNumber
> Get #FileNumber, , Script
> Close #FileNumber
>
> Script = Mid$(StrConv(Sc ript, vbFromUnicode), 2)
>
> FindSQLScriptEx it:
> Close
> Exit Sub
> FindSQLScriptEr r:
> With Err
> MsgBox .Description, , "Error " & .Number
> End With
> Resume FindSQLScriptEx it
> End Sub
>
> Private Function GetFile(Optiona l InitialDir As String, _
> Optional FilterMessage As String = "MS-SQL Scrips", _
> Optional FilterSkelton As String = "*.sql", _
> Optional File As String = "*.sql", _
> Optional Title As String = "Use the Open Button to Select") As String
> GetFile = GetPath(Initial Dir, FilterMessage, FilterSkelton, File,
> Title)
> End Function
>
> Private Function GetPath( _
> Optional InitialDir As String, _
> Optional FilterMessage As String = "Choose Folder Only", _
> Optional FilterSkelton As String = "*|*", _
> Optional File As String = "Folders Only", _
> Optional Title As String = "Use the Open Button to Select") As String
> Dim CommDlgError As Long
> Dim OFN As OPENFILENAME
> If Len(InitialDir) = 0 Then InitialDir = CurDir$()
> With OFN
> .lStructSize = Len(OFN)
> .lpstrFilter = FilterMessage & vbNullChar & FilterSkelton & String
> (2, vbNullChar)
> .lpstrFile = File & String(MAX_PATH - Len(File), vbNullChar)
> .nMaxFile = MAX_PATH
> .lpstrInitialDi r = InitialDir & vbNullChar
> .lpstrTitle = Title
> .flags = OFN_HIDEREADONL Y Or OFN_NOCHANGEDIR Or OFN_SHAREAWARE
> If GetOpenFileName (OFN) <> 0 Then
> If FilterSkelton = "*|*" Then
> GetPath = Left$(.lpstrFil e, .nFileOffset)
> Else
> GetPath = .lpstrFile
> End If
> GetPath = Left$(GetPath, InStr(GetPath, vbNullChar) - 1)
> Else
> CommDlgError = CommDlgExtended Error
> ' if not just a cancel
> If CommDlgError <> 0 Then
> MsgBox "Common Dialog Error # " & CommDlgError _
> & vbCrLf _
> & vbCrLf _
> & "Consult Common Dialog Documumentation " _
> & vbCrLf _
> & "(in MSDN Library)" _
> & vbCrLf _
> & vbCrLf _
> & "for meaning.", _
> vbCritical, _
> "FFDBA"
> End If
> End If
> End With
> End Function
>
>
> --
> Lyle
> (for e-mail refer to http://ffdba.com/contacts.htm)[/color]

**Larry Linson** · Nov 12 '05, 10:34 PM

Re: Execute SQL Script

Thanks, Lyle. Although I haven't run this yet, it is impressive.

Execute SQL Script

Execute SQL Script

Comment

Comment

Comment