473,320 Members | 1,863 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Execute SQL Script

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 CommDlgExtendedError Lib "comdlg32.dll" () As Long

Private Declare Function GetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" _
(pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter 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_HIDEREADONLY = &H4
Private Const OFN_NOCHANGEDIR = &H8
Private Const OFN_SHAREAWARE = &H4000
Private Const MAX_PATH = 260
Private Const DELIMITER = vbNewLine & "GO" & vbNewLine

Private Sub FindAndExecuteSQLScript()
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
ExecuteSQLScript Script
End If
End Sub

Private Sub ExecuteSQLScript( _
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(aSubScripts)
aSubScripts(z) = Trim$(aSubScripts(z))
SubScript = aSubScripts(z)
If Len(SubScript) > 255 Then
SubScript = Left$(aSubScripts(z), InStr(255, aSubScripts(z),
vbNewLine))
End If
If Len(Replace(SubScript, vbNewLine, "")) > 1 Then
If Left$(SubScript, 3) <> "SET" Then
If MsgBox("EXECUTE " & vbNewLine & SubScript, vbQuestion Or
vbYesNo, "FFDBA") = vbYes Then
CurrentProject.Connection.Execute 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(FullPathToFile) = 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(Script, vbFromUnicode), 2)

FindSQLScriptExit:
Close
Exit Sub
FindSQLScriptErr:
With Err
MsgBox .Description, , "Error " & .Number
End With
Resume FindSQLScriptExit
End Sub

Private Function GetFile(Optional 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(InitialDir, 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
.lpstrInitialDir = InitialDir & vbNullChar
.lpstrTitle = Title
.flags = OFN_HIDEREADONLY Or OFN_NOCHANGEDIR Or OFN_SHAREAWARE
If GetOpenFileName(OFN) <> 0 Then
If FilterSkelton = "*|*" Then
GetPath = Left$(.lpstrFile, .nFileOffset)
Else
GetPath = .lpstrFile
End If
GetPath = Left$(GetPath, InStr(GetPath, vbNullChar) - 1)
Else
CommDlgError = CommDlgExtendedError
' 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)
Nov 12 '05 #1
3 1876
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
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.)


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.
Nov 12 '05 #2
Check out osql.exe - it ships with SQL Server.
-Tom.

"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
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 CommDlgExtendedError Lib "comdlg32.dll" () As Long
Private Declare Function GetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" _
(pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter 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_HIDEREADONLY = &H4
Private Const OFN_NOCHANGEDIR = &H8
Private Const OFN_SHAREAWARE = &H4000
Private Const MAX_PATH = 260
Private Const DELIMITER = vbNewLine & "GO" & vbNewLine

Private Sub FindAndExecuteSQLScript()
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
ExecuteSQLScript Script
End If
End Sub

Private Sub ExecuteSQLScript( _
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(aSubScripts)
aSubScripts(z) = Trim$(aSubScripts(z))
SubScript = aSubScripts(z)
If Len(SubScript) > 255 Then
SubScript = Left$(aSubScripts(z), InStr(255, aSubScripts(z),
vbNewLine))
End If
If Len(Replace(SubScript, vbNewLine, "")) > 1 Then
If Left$(SubScript, 3) <> "SET" Then
If MsgBox("EXECUTE " & vbNewLine & SubScript, vbQuestion Or vbYesNo, "FFDBA") = vbYes Then
CurrentProject.Connection.Execute 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(FullPathToFile) = 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(Script, vbFromUnicode), 2)

FindSQLScriptExit:
Close
Exit Sub
FindSQLScriptErr:
With Err
MsgBox .Description, , "Error " & .Number
End With
Resume FindSQLScriptExit
End Sub

Private Function GetFile(Optional 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(InitialDir, 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
.lpstrInitialDir = InitialDir & vbNullChar
.lpstrTitle = Title
.flags = OFN_HIDEREADONLY Or OFN_NOCHANGEDIR Or OFN_SHAREAWARE
If GetOpenFileName(OFN) <> 0 Then
If FilterSkelton = "*|*" Then
GetPath = Left$(.lpstrFile, .nFileOffset)
Else
GetPath = .lpstrFile
End If
GetPath = Left$(GetPath, InStr(GetPath, vbNullChar) - 1)
Else
CommDlgError = CommDlgExtendedError
' 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)

Nov 12 '05 #3
Thanks, Lyle. Although I haven't run this yet, it is impressive.


Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: Larry Woods | last post by:
I need to execute a Windows script (wscript/cscript) from an ASP page for some administrative functions. Anyone doing this? If so, how? TIA, Larry Woods
3
by: Tomasz Ludwiniak | last post by:
Hi, I've little problem... I try to execute asp file ( which return XML file ) and use the result it in my asp script : set xml = Server.CreateObject("MSXML2.DOMDocument") xml.async=false...
1
by: Matt | last post by:
I used to execute a command line script in IIS 4 using Dynuexec. This is a third part server object I installed on the server (http://www.dynu.com/dynuexec.asp). I installed this object onto my...
3
by: Lyle Fairfield | last post by:
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...
5
by: batham | last post by:
Hi Gurus, How can I execute a script during runtime. Here is my code, so how do I execute the new script during runtime which should be a part of the 'topDiv'. Thanks Help is appreciated. -...
9
by: sohan | last post by:
Hi, I want to know how to connect and execute a db2 query from inside a UNIX shell script. Details: We have a unix shell script. We need to execute multiple db2 sql queries from this shell...
4
by: Chr1s | last post by:
How do I execute a crontab command using php (not CLI) as user Joe? I don't have any problem getting output from commands such as 'ls' using passthru and exec but I am stumped with crontab. ...
8
by: M.L. | last post by:
Hello. I created a form using JS validation with the form tag as follows: <form name="form1" action="dynaform.php" method="post" onsubmit="return pvg_sub();"> The js validation script sends...
7
by: tanyali | last post by:
Hi guys, I wanna to execute a php page automatically in the background, or execute it in shell script, and set this script to execute as a cron job. so who knows either of the two questions...
2
by: Bogdan | last post by:
Hi, I'd like to display (i.e. make visible) a label after a submit button is clicked but only if the client-side validation is successful. If I did not care about the validation then I'd simply...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.