Adding stored procs from multiple files with one statement
-
I have a growing number of stored procedures, each in their own .tql file. As the number of stored procedures increases, my deployment work flow is becoming longer and longer.
I'd like to write a script that calls each .tql file in turn and executes the sql statements in it. What's the easiest way to do this? I've looked through a couple of sql books, and googled arround, but no joy so far.
Regards,
-jon
-
Easies way to do is write a VB Program which will scan ur database and collect and transfer it to u r required database,
use ADOX catlogs (ADO object) in VB
i had done something like that before by using this method.
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
No no no.
You misunderstand. The stored procs are each in a separate .tql file. I don't have them in a database. Some are written by me, some by other developers. In our deployment process we need to 1)add the database schemas, and then 2) add each stored proc. I'm just trying to make step 2 of the deployment be a one-step process instead of a 50-step process.
Basically, is there any way I can write a sql script that executes other sql or tsql files? That's the syntax I need.
Regards and best wishes,
Jon
-
Save the following to multiplesql.wsf.
Get syntax by multiplesql.wsf /?
<package>
<job id="multiplesql">
<runtime>
<named
name="SQLFolder"
helpstring="sql files folder"
type="string"
required="true"
/>
<named
name="S"
helpstring="server"
type="string"
required="true"
/>
<named
name="U"
helpstring="login id"
type="string"
required="true"
/>
<named
name="P"
helpstring="password"
type="string"
required="true"
/>
<example>Example: multiplesql.wsf /SQLFolder:c:\temp\sql /S
local) /U:sa /P:123654 </example></runtime>
<script language="VBScript">
dim sfolder, S, U, P
if WScript.Arguments.Named.Exists("SQLFolder") _
and WScript.Arguments.Named.Exists("S") _
and WScript.Arguments.Named.Exists("U") _
and WScript.Arguments.Named.Exists("P") then
sfolder=WScript.Arguments.Named("sqlfolder")
S=WScript.Arguments.Named("S")
U=WScript.Arguments.Named("U")
P=WScript.Arguments.Named("P")
else
WScript.Arguments.ShowUsage
WScript.Quit(1)
end if
dim fso, filepatrn
Set fso = CreateObject("Scripting.FileSystemObject")
'''''''''''''''''''''''''''''''''''''''''''''''
' Check exists
'
'''''''''''''''''''''''''''''''''''''''''''''''
If Not (fso.FolderExists(sfolder)) Then
WSCript.Echo "SQL Folder: " & sfolder & " does not exist."
WScript.Quit(1)
End If
dim fromf, fromfc, fname, i, j, cmdline
dim oShell
Set oShell = WScript.CreateObject("WSCript.shell")
'''''''''''''''''''''''''''''''''''''''''''''''
' get file name from source folder.
' execute it in turn.
'
'''''''''''''''''''''''''''''''''''''''''''''''
Set fromf = fso.GetFolder(sfolder)
Set fromfc = fromf.Files
For Each i in fromfc
fname = i.name
cmdline = "Osql -S" & S & " -U" & U & " -P" & P & " -i" & sfolder & "\" & fname
oShell.run "cmd /C " & cmdline, 7, 1
Next
Set oShell = Nothing
set fromfc = Nothing
set fromf = Nothing
Set fso = Nothing
Wscript.echo "Ok"
WScript.Quit(0)
</script>
</job>
</package>
-
That helped. What I needed to do was actually much simpler than that.
The solution to my problems was a batch file that calls osql a buch of times, a la
osql -U sa -P 123456 -d mydatabase -i myStoredProcedure1.sql
Once I looked at the script above, it was easy to figure out what I needed to do. Thanks!
-jon
-
check out http://www.dbghost.com
-
JB,
If you have Visual Studio .Net you can create a database project to hold your script files. Then you can simply right click on the folder you want to create the "Command" file for and select "Create Command File...". This will bring up a dialog box with all the files in the folder of your project so you can select the files you want to add to the batch file. When you are done you will end up with a batch file that will do precisely what you want. I typically modify it so that I can turn off the line numbering by doing a search and replace. I then have all my scripts in one place and the batch file to distribute it there as well. These all get checked into my source control directly from VS .Net.
If I have to deploy this to multiple machines I create a batch file to call the batch file I created above for each machine and then add that to my project. It works really well for me and I have been using the batch files like this for the last 2 years now.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Pity Visual Studio .NET doesn't handle the building of database objects in the correct order - it assumes there are no breakages within your code, a dangerous mind-set indeed. Remember - there may be a better way...
regards,
Mark Baekdal
PS: We have a white paper about change management (a subject I live a breathe for) which I can send any-one who is interested just send me an email to mark.baekdal@innovartis.co.uk and I'll send you a copy - no strings attached.
-
Thanks everybody for your feedback...this forum is great! My little script handles my needs just fine for now.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply