Open Files to execute SQL
-
We need to have a script that will open files in a directory and all sub-directories and execute the sql in each file. I have a start on such a script but I was wondering if anyone has a solution that I am unaware of, or if a script exists on this site that I have not found.
Here is the SQL I have started. It reads the files from a directory called Test and executes them in a database called Test:
create table #Files (Files varchar(255))
create table #OutputTable (CommandOutput varchar(255))
declare @FileName varchar(255), @Command varchar(3000)
insert into #Files
exec master..xp_cmdshell 'dir /b C:\Test\'
declare Files_CURSOR cursor for
select files from #Files
open Files_CURSOR
fetch next from Files_CURSOR into @FileName
while @@FETCH_STATUS = 0
begin
set @Command = 'osql -E -d Test -i c:\Test\' + @FileName
insert into #OutputTable
exec master..xp_cmdshell @Command
fetch next from Files_CURSOR into @FileName
end
select * from #OutputTable
close Files_CURSOR
Deallocate Files_CURSOR
drop table #Files
drop table #Outputtable
Thanks your help is appreciated
Ross
-
I don't have what you're looking for, but another approach would be to drive this from a batch file. You could CD to the directory, and then do something like this:
for /R %f in (*.*) do osql -E -S (local) -i %f
Then you could use xp_cmdshell to call that batch. An obvious issue here that needs to be addressed would be how to check (and respond to) %ERRORLEVEL% after each osql call, as well as how to handle the authentication of different logins (if required). But I thought I would punt this your way, just in case this would be useful to you.
Cheers,
Chris
-
"An obvious issue here that needs to be addressed would be how to check (and respond to) %ERRORLEVEL% after each osql call"
I am hoping this is where the OutputTable will come in handy. The output will be the outcome of each osql call.
It is a work in progress so we will see how it comes out.
Ross
-
Just a heads up, but this looks like a hackers dream come true
I hope there is some design time put into this potential security hole.
-
Yeah I have been thinking about that.
I do not think this code would end up on any of the actual servers. I was thinking on my local machine only. Set up a linked server to the destination box and let it execute them on the production box.
Or we could drop the procs used for this when they are not in use. Only create them for the short period of time that they are in use.
Or we could deny execute rights to this but a few select users.
It is a concern that I am thinking about.
Ross
-
If you use a database project in Visual Studio .Net and save all your scripts in the project you can have Visual Studio make a dos command file for you to call each script in the folder. This works really well. I use it all the time to roll out SP changes to my replicated machines.
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.
-
Do you have an example of this? That is really cool!
Thanks,
Ross
-
Open Visual Studio .Net and create a new database project. Open Windows Explorer and drag in the folder and drop it on your project in the solution explorer pane. Then right click on the folder and add the existing scripts to the project. Once you have done that right click on the folder and select "Create Command File". You will then get a dialog box that allows you to select the files you want to add. The resulting file will look something like the following....
@echo off
REM: Command File Created by Microsoft Visual Database Tools
REM: Date Generated: 2/26/2004
REM: Authentication type: Windows NT
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 -i "myScript1.SQL"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "myScript2.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
@echo on
I then took this file and made a batch file that calls this file with all my machine names and logins. So when I need to rollout my SPs I just call one batch file and it processes all the new SPs for each machine. It's a real time saver! Plus I feel much more confidant that each machine actually has the SP it is supposed to have!
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.
-
Create a batch file with these commands in it.
Type c:\Test\*.sql> c:\Test\ExecuteAllFiles.sql
Save it as c:\Test\ExecuteAllFiles.bat
Execute the batch file like this
master..xp_cmdshell 'c:\Test\ExecuteAllFiles.bat'
Run the output file.
master..xp_cmdshell 'osql -E -d Test -i c:\Test\ExecuteAllFiles.sql'
Essentially the batch file combines all the files in the current folder into one file. You can now execute the one file using xp_cmdshell and osql. This works well if you have a number of sp's that are constantly being updated and they need to pushed out to a number of servers.
-
Cool. Thanks for the help everyone. Much appreciated.
Ross
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply