June 10, 2010 at 12:59 am
We're developing a product and I've created a .sql script that creates the database, tables, stored procedures, all the Service Broker configurations etc etc. The script itself works perfectly fine however we have a requirement to be able to configure the user name, location of the database and other parameters.
The problem is that my .sql script is scattered with GO statements that limits the scope of local variables to within each GO statement. Thus I cannot use DECLARE statements to set parameter values at the beginning of the script and then use it later in the script.
I've searched online to no avail in regards to this topic. I might be searching with the wrong keywords but if someone could perhaps push me in the right direction I'd greatly appreciate it.
Thanks in advance.
June 10, 2010 at 5:42 am
n00b (6/10/2010)
We're developing a product and I've created a .sql script that creates the database, tables, stored procedures, all the Service Broker configurations etc etc. The script itself works perfectly fine however we have a requirement to be able to configure the user name, location of the database and other parameters.The problem is that my .sql script is scattered with GO statements that limits the scope of local variables to within each GO statement. Thus I cannot use DECLARE statements to set parameter values at the beginning of the script and then use it later in the script.
I've searched online to no avail in regards to this topic. I might be searching with the wrong keywords but if someone could perhaps push me in the right direction I'd greatly appreciate it.
Thanks in advance.
If it is a program that is running the script, you could do the following.
1. get the .sql file as one big string.
2. split the string into stringCollection of strings on the "GO" & vbCrLf which appears in the script.
3. assuming all the declarations are in the FIRST string, you'd want to save that into a variable for concatenating to the rest of the strings
4. for each string in the stringCollection, append firststring + vbCrLf + CurrentString in the ForEach Loop , then .ExecuteNonQuery the contents of the string; they will remain in the correct order due to the split, and you gain the advantage of being able to carry your variables into each sql string to perform.
If you are doing this in .NET, wehter vb or c#, I could paste an example if needed; I've actually done this same thing in the past.
Lowell
June 10, 2010 at 6:10 am
I generally write a VBScript that takes in variables from user input, then generates batch scripts that execute the SQL required.
June 10, 2010 at 6:11 am
'Imports System.IO
Private Sub btnSQLSplitter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSQLSplitter.Click
Dim myConnectionString As String = "data source={0};user id={1};password={2};initial catalog={3};Trusted_Connection=False;Application Name=SSCExample.YouApplicationName.exe;"
myConnectionString = String.Format(SqlConnectionFormat, "YourServer", "SomeUserName", "NotARealPassword", "databasename")
Dim FileContents As String = GetFileContents("C:\Data\MySQLScript.sql")
Dim s() As String
Dim i As Integer = 1
s = FileContents.Split("GO" & vbCrLf)
'--assumption, only s(0) contains "global" variables to carry into each sub-script.
Dim myDeclarations As String = s(0)
For i = 1 To s.Length - 1
Dim sqlString As String = myDeclarations & vbCrLf & s(i)
'--SQLHelper Class from Microsoft Data Access Application Block
'http://www.microsoft.com/downloads/details.aspx?familyid=f63d1f0a-9877-4a7b-88ec-0426b48df275&displaylang=en
SqlHelper.ExecuteNonQuery(myConnectionString, CommandType.Text, sqlString)
Next
End Sub
Public Function GetFileContents(ByVal FullPath As String, _
Optional ByRef ErrInfo As String = "") As String
Dim strContents As String
Dim objReader As StreamReader
Try
objReader = New StreamReader(FullPath)
strContents = objReader.ReadToEnd()
objReader.Close()
Return strContents
Catch Ex As Exception
ErrInfo = Ex.Message
Return ""
End Try
End Function
Lowell
June 10, 2010 at 6:20 pm
Thanks for your responses Lowell and skcadavre.
Lowell - I've gone through your vb and can see how it works. Thanks for the suggestion!
On a general level though, does anyone know how a large corporate company would achieve what I'm trying to do? Would they create an MSI install package with the SQL commands as a large string inside the package? Although this is the first time I'm implementing this, I'd like to learn the "proper" way even if it takes me longer to learn and understand it.
Thanks
June 10, 2010 at 7:40 pm
good question actually.
at some point, the connection to the database that is going to be upgraded has to be input...
Like me, I'm sure you've noticed that you've never seen an installing application ask for that information up front as part of the install, it's always post-msi where that kind of dialog occurs, as part of some wizard/setup part of the application.
In my companies case, it's usually a script that has been proofed many times at our shop, and then finally forwarded to the clients DBA to run. if something goes wrong in the script, the dba is the most experienced to be able to give any significant feedback about any errors that occurred....things like running it in the wrong database, or a real error where a table(or a wrong schema on a table) makes the script fail.
the new upgraded .exe versions of our apps(or suite of web pages to be deployed), are deployed as msi's, which auto uninstall any previous versions of the application...but the db stuff is handled in parallel.
n00b (6/10/2010)
Thanks for your responses Lowell and skcadavre.Lowell - I've gone through your vb and can see how it works. Thanks for the suggestion!
On a general level though, does anyone know how a large corporate company would achieve what I'm trying to do? Would they create an MSI install package with the SQL commands as a large string inside the package? Although this is the first time I'm implementing this, I'd like to learn the "proper" way even if it takes me longer to learn and understand it.
Thanks
Lowell
June 11, 2010 at 5:26 pm
Andy Leonard give a pretty good overview in the following...
https://www323.livemeeting.com/cc/usergroups/view?id=W5888W
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply