Open Files to execute SQL

  • bozo7

    Ten Centuries

    Points: 1278

    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

  • chrisleonard

    SSCertifiable

    Points: 5000

    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

  • bozo7

    Ten Centuries

    Points: 1278

    "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

  • gibsonrobert

    SSC Veteran

    Points: 237

    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.

  • bozo7

    Ten Centuries

    Points: 1278

    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

  • gljjr

    SSCrazy Eights

    Points: 9970

    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 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.

  • bozo7

    Ten Centuries

    Points: 1278

    Do you have an example of this? That is really cool!

    Thanks,

    Ross

  • gljjr

    SSCrazy Eights

    Points: 9970

    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 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.

  • Mark Shepherd

    SSC Enthusiast

    Points: 142

    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.

  • bozo7

    Ten Centuries

    Points: 1278

    Cool. Thanks for the help everyone. Much appreciated.

    Ross

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply