I have a number of generated .sql files that I want to run in succession. I'd like to run them from a SQL statement in a query (i.e. Query Analyzer/Server Management Studio).
Is it possible to do something like this and if so what is the syntax for doing this?
I'm hoping for something like:
exec 'c:\temp\file01.sql'
exec 'c:\temp\file02.sql'
I am using SQL Server 2005 and running queries in management studio.
use xp_cmdshell and sqlcmd
EXEC xp_cmdshell 'sqlcmd -S ' + @DBServerName + ' -d ' + @DBName + ' -i ' + @FilePathName
I wouldn't recommended doing this, but if you really have to then the extended stored procedure xp_cmdshell is what you want. You will have to first read the contents of the file into a variable and then use something like this:
DECLARE @cmd sysname, @var sysname
SET @var = 'Hello world'
SET @cmd = 'echo ' + @var + ' > var_out.txt'
EXEC master..xp_cmdshell @cmd
Note: xp_cmdshell runs commands in the background, because of this, it must not be used to run programs that require user input.
Take a look at OSQL. This utility lets you run SQL from the command prompt. It's easy to get installed on a system, I think it comes with the free SQL Server Express.
A qick search of "OSQL" on stack overflow shows a lot of stuff is available.
The main thing to handle properly is the user and password account parameters that get passed in on the command line. I have seen batch files that use NT file access permissions to control the file with the password and then using this file's contents to get the script started. You could also write a quick C# or VB program to run it using the Process class.
what's the solution for this ? I have multiple .sql files and i want to create a single .sql file that should execute all the .sql files
EXEC a.sql
EXEC b.sql
EXEC c.sql
do i need to have a semicolon (;) at each end of the exec statement? something like this?
EXEC a.sql;
EXEC b.sql;
EXEC c.sql;
which one will work? Please let me know..
Thanks in advance.
Mohan Gajula http://iMohanG.blogspot.com