June 2, 2005 at 10:13 am
I am new to SQL Server (was an Oracle DBA), and am trying to figure out how to run another .sql script from the current one. I know I can use xp_cmdshell, but then it's a different connection, plus I have to basically re-login via the command line. In Oracle (SQL*Plus), you can just write "@other_script" or "run other_script" and it uses the SQL_PATH env variable to find the script, load it and run it in the current connection.
e.g. I have some utility scripts -- one that disables all foreign keys, and another that enables them. When I script ddl/dml changes, it's often helpful to disable fk's at the top, and re-enable them at the bottom. I don't want to copy and paste all that code, rather I'd like to just call out to the scripts.
Similarly, I have a script that creates the schema, tables and loads sample data. Instead of one huge script to do it all, I want a single controlling script that invokes several other scripts, so I can divide the work up into more logical, manageable scripts. I imagine something like:
-- create app schema
print 'creating application schema...'
print 'creating tables'
-- run createTables.sql
print 'creating dictionary data...'
-- run dictionary.sql
[...more of same...]
print 'done'
Any suggestions? Thanks in advance!
Gordon
June 2, 2005 at 11:20 am
How 'bout something like this in a BAT file
SET ChangeRecord=Maintenance
SET LOGDIR="C:\Logs\%ChangeRecord%"
IF NOT EXIST %LOGDIR% MKDIR %LOGDIR%
SET LOG=%LOGDIR%\%ChangeRecord%_runsql.log
SET PREFIX=%LOGDIR%\maint.
SET SRVNAME=MyServerName
SET DB=YourDB
ECHO. > %LOG%
osql -S%SRVNAME% -E -n -d%DB% -i "C:\Stored Procedures\Disable_FK.sql" -o %PREFIX%Disable_FK.sql.err >> %LOG%
osql -S%SRVNAME% -E -n -d%DB% -i "C:\Stored Procedures\Enable_FK.sql" -o %PREFIX%Enable_FK.sql.err >> %LOG%
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 3, 2005 at 7:43 am
That's good input. I'm really looking for the ability to run multiple sql scripts in a single database connection. I'm thinking it doesn't exist.
Thanks, though.
Gordon
June 3, 2005 at 4:50 pm
Why not call usp_Umbrella which inside would do:
EXEC usp_Maintenance1
EXEC usp_Maintenance2
etc.....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply