Post reply

Read and execute SQL string from Table, output to file

  • April 29, 2011 at 12:46 am

    #235470

    Haven't worked on SQL Server since 6.5 so decided to post this question in newbie forum, if inappropriate please redirect.

    SQL Server 2005 (clustered)

    I get limited DBA access so would like to build a 1) table to record SQL txt, filename and file location 2) sp which reads each record in table and executes the SQL, outputting results to the parameterised file name and location.

    With the above I can schedule the sp to run during night, and as I need more outputs I simply add records to the table.

    I imagine this has been done before so if anyone could point me in the right direction as I can't think of the right google keywords.

    thanks a mill

    D

  • April 29, 2011 at 8:50 am

    #1317824

    Yup. Look up sp_send_dbmail in online help. Should be just what the Dr. ordered.

  • May 15, 2011 at 6:20 pm

    #1324827

    Dan, are you all set on this one or do you still need some help?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 16, 2011 at 4:01 am

    #1324917

    Jeff, yeah still looking around for help. I had a look at what Pam suggested (thanks for your input Pam) but it focuses on the getting data out which is only part of what I would like.

    Ideally I am looking for an all in one script that creates the table to hold the SQL statements, and the SP that reads the all the statements from the table, executes the statements 1 by 1, and saves the results to file based on the location which can be specified in the table as a parameter.

    I don't think I have come up with a new ideal here, and was hoping that someone could share the solution if they already have this in place in their database, or even point to another website that has this.

    thanks a mill

  • May 16, 2011 at 8:39 am

    #1325115

    In step 2, what type of sql statements are you storing in the table?

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • May 16, 2011 at 9:24 am

    #1325179

    Select statements only, no updates, insert or deletes. Basically need to get routine MI data out of a workflow system. New questions will be asked of this data, I don't have access to modify SP to output a new Select statement, therefore, I need a mechanism to add the SQL to a table, the location I want it saved to and have the SP scheduled to run everyday.

    Where clause may contain self-determining values such as getdate or getdate-1, aggregate functions, pivots and other typical Select functions but nothing complex like Olap functions

    Hope this makes sense!

    thanks a mill

    Dan

  • May 16, 2011 at 10:02 am

    #1325231

    Here is something we can work with. Start with a basic table create and some sample data.

    create table SQL_List (Stmt_ID int identity(1,1), SQL_Stmt varchar(8000), File_location varchar(8000));

    insert into SQL_List( SQL_Stmt, File_location)

    select 'select top 10 * from sys.columns','c:\temp\sql_output1.txt' union all

    select 'select top 5 object_id from sys.columns','c:\temp\sql_output2.txt'

    Now we can create a procedure to run those statements and output the results to text files:

    create procedure Execute_SQL_List as

    set nocount on;

    declare @SQL_Stmt varchar(8000);

    declare @File_location varchar(8000);

    declare @bcp varchar(8000);

    declare rs cursor for

    select SQL_Stmt, File_location from SQL_List;

    open rs

    fetch next from rs into @SQL_Stmt, @File_location

    while @@FETCH_STATUS = 0

    begin

    set @bcp = '''' + 'bcp "' + @SQL_Stmt + '" queryout '+ @File_location +' -S -T -c ' + ''''

    print @bcp

    exec master..xp_cmdshell @bcp

    fetch next from rs into @SQL_Stmt, @File_location

    end

    close rs

    deallocate rs

    You may have to turn on the permissions to use xp_cmdshell, but this can get us started.

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • May 17, 2011 at 2:58 am

    #1325647

    This looks like it ticks all the boxes, the only thing I might add is a bit of error handling to log unforseen errors (since this will run unattended). I'll give it a test on the dev box and may come back to the thread if there's something I don't understand. However, many thanks for this, I appeciate your time.

    rgds

    Dan

  • May 17, 2011 at 7:08 am

    #1325768

    dan.mcauley (5/16/2011)

    Jeff, yeah still looking around for help. I had a look at what Pam suggested (thanks for your input Pam) but it focuses on the getting data out which is only part of what I would like.

    Ideally I am looking for an all in one script that creates the table to hold the SQL statements, and the SP that reads the all the statements from the table, executes the statements 1 by 1, and saves the results to file based on the location which can be specified in the table as a parameter.

    I don't think I have come up with a new ideal here, and was hoping that someone could share the solution if they already have this in place in their database, or even point to another website that has this.

    thanks a mill

    It looks like toddasd sussed it for you. Sorry for not being able to get back sooner.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

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