February 8, 2004 at 7:45 pm
I would like to create a parent .sql file that when executed goes off and executes the contents a number of other "children" .sql files.
Would anybody know how to do this?
What happens if one of the .sql files fails? Will the others keep on running?
Thanks
February 9, 2004 at 1:03 pm
The only way I know of to do this is to use xp_cmdshell and call osql. Rather than doing it this way I usually use a batch file to run my scripts. BTW: You can use a Visual Studio Database Project to create these batch files for you.
@echo off
REM: Usage: CommandFilename [Server] [Database]
if '%1' == '' goto usage
if '%2' == '' goto usage
if '%1' == '/?' goto usage
if '%1' == '-?' goto usage
if '%1' == '?' goto usage
if '%1' == '/help' goto usage
osql -S %1 -d %2 -E -b -n -i "YourScriptHere.sql"
if %ERRORLEVEL% NEQ 0 goto errors
goto finish
REM: How to use screen
:usage
echo.
echo Usage: MyScript Server Database
echo Server: the name of the target SQL Server
echo Database: the name of the target database
echo.
echo Example: MyScript.cmd MainServer MainDatabase
echo.
echo.
goto done
REM: error handler
:errors
echo.
echo WARNING! Error(s) were detected!
echo --------------------------------
echo Please evaluate the situation and, if needed,
echo restart this command file. You may need to
echo supply command parameters when executing
echo this command file.
echo.
pause
goto done
REM: finished execution
:finish
echo.
echo Script execution is complete!
:done
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This 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.
February 10, 2004 at 9:09 am
There is a way but it's not pretty. Create, or have your c++ programers create a program that uses as an input a file location and as an returns the contents of the file specifed by the input. (see "Extended Stored Procedures" in books online.) This shouldn't be a brain teaser.
Then create an extended stored procedure that calls the c++ code. Now you can reference the xp_newproc in your code and you can use dynamic sql to execute it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply