March 25, 2009 at 4:05 pm
I have a script for a database conversion. In addition, there are several scripts for changes to existing stored procedures and functions (SP/FN) as well as new ones. I would like to specify in the conversion script the specific execution order for the CREATE/ALTER statements for the SP/FN.
I know that you cannot directly execute a .SQL file from within another .SQL file and some have suggested to edit my file to include all the content from the various scripts. However, since these SP/FN files are saved as they have been tested (of course, to individual files), I really would not want to include the contents of all SP/FN files in one huge .SQL file that would have to be specially maintained as the SP/FN files are updated during testing.
Any suggestions would be greatly appreciated. Oh, and before it's suggested, I would really rather avoid having to create an SSIS package to do all of this if I can possible avoid it. Executing a single SQL file that controls everything would be really handy.
TIA,
Aaron
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
March 25, 2009 at 4:27 pm
Hi,
you could use the osql tool (command prompt)
osql -S 192.168.1.13 -E -i sqlfile.sql
That's the command for one sql file (sqlfile.sql) with a trusted connection to 192.168.1.13. Then you just have to create a batch file for all of your sql files.
Hope that helps 🙂
Edit:
To get all of your files in a batch file use this in a batch file (CreateCommands.bat)
@echo off
for %%a in (dir c:\*.*) do echo osql -S 192.168.1.13 -E %%a
and write it's output to another batch file...
CreateCommands.bat >> myOSqlBatch.bat
-----------------------
SQL Server Database Copy Tool at Codeplex
March 25, 2009 at 4:39 pm
That allows one to execute a SQL file from a .BAT file (requiring a connection to the database for each file), but what about within an existing SQL file that is currently being executed?
For example, UpgradeDB.sql has DDL commands and some DML statements as well. Once the conversion has been done, then it needs to load the UDFs and SPs from their respective files:
fnUserFuncA.sql
fnUserFuncB.sql
spStoredProcA.sql
spStoredProcB.sql
Due to dependencies, they need to be executed in a specific order, so I can't just run them at random.
Does that help?
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
March 25, 2009 at 5:03 pm
Hmm,
I think I would write a small tool which does the following.
1. read a list of the sql files with filename (assuming the filename is the name of the function/procedure)
2. search for the name of the function in all the other files
3. if you can't find it => it's ok => execute sql and remove file from list
4. if you can find it => move to the next file and goto 2
5. until the list is empty 🙂
I know that's maybe not the easiest solution but I think this could work. And it's no SSIS 😉
How many functions/procedures do you want to alter/create?
-----------------------
SQL Server Database Copy Tool at Codeplex
March 25, 2009 at 7:02 pm
It's about 25 UDF/SP files and the dependencies are not always direct. While I really wanted to have a "click it and forget it" approach, I just may have to do it manually.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply