osql Cursor values to output file

  • guys,

    I have following stored procedure which runs osql cmd to write to a file every next value of a cursor, but the problem is the file is overwritten for each value of the cursor. Is there any way to modify the stored proc to append values to the same outputfile. Basically the outputfile contains the output only for last value of the cursor.

    ALTER PROCEDURE [dbo].[INSERTTEST2]

    AS

    BEGIN

    declare C_mf1 cursor for

    select tab_name from meta

    DECLARE @cmd varchar(2000)

    DECLARE @TAB_NAME VARCHAR(20)

    DECLARE @FETCHCOUNT INT

    set @tab_name = ''

    set @fetchcount = 0

    open C_mf1 fetch next from C_mf1 into @tab_name

    WHILE @@fetch_status<>-1

    BEGIN

    set @cmd = 'sqlcmd -E -dWatauga_Test -SNV-DEVSQL1\DAPHNE -Q"exec SP_GENERATE_INSERTS '+@TAB_NAME+'" -oc:\myTable.sql'

    select @cmd -- + '...'

    EXEC master.dbo.xp_cmdShell @cmd

    set @fetchcount=@fetchcount+1

    fetch next from C_mf1 into @tab_name

    END

    END

    Any suggestions and inputs would help

    Thanks

  • 'sqlcmd -E -dWatauga_Test -SNV-DEVSQL1\DAPHNE -Q"exec SP_GENERATE_INSERTS '+@TAB_NAME+'" >>c:\myTable.sql'

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

  • ... and, just to be on the safe side, you should enclose the server instance name in double quotes because of the special characters it contains.

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

  • Perfect Jeff Thanks

  • You bet... thank you for the feedback.

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

  • Jeff,

    One more quickie, is there any way to replace the output file with a new output file each time when I run the osql command.

    Thanks

  • Absolutely!  You want a new file name each time or do you want to reuse the same file name?

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

  • Jeff,

    I want to use the same file name but one thing though, from the stored proc definition you would see that I pass cursor values into osql commmand.

    If I use '>' it creates new file, but it loads data only for the last value of the cursor. Whereas '>>' appends the data for all values of the cursor but it works on the same output file with out creating the new file when it runs.

    Is there any solution ? can I run a windows command to delete the file from the stored procedure before it executes the osql command.

    Thanks

  • Good to see you read the post the Tara wrote on the "other" forum, but she's not entirely correct.  [ > ] will erase a file if it exists and then create a new one.  [ >> ] will append to a file if it exists and still create a new one if it does not

    So... just before you run the cursor with the append output ">>", why don't you just run another dip to the operating system that deletes a file if it exists?

    ALTER PROCEDURE [dbo].[INSERTTEST2]

    AS

    BEGIN

    declare C_mf1 cursor for

    select tab_name from meta

    DECLARE @cmd varchar(2000)

    DECLARE @TAB_NAME VARCHAR(20)

    DECLARE @FETCHCOUNT INT

    set @tab_name = ''

    set @fetchcount = 0

    set @cmd = 'DEL c:\myTable.sql /q' --/q is the "quiet" or "promptless" mode of the command

    EXEC master.dbo.xp_cmdShell @cmd

    open C_mf1 fetch next from C_mf1 into @tab_name

    WHILE @@fetch_status<>-1

    BEGIN

    set @cmd = 'sqlcmd -E -dWatauga_Test -SNV-DEVSQL1\DAPHNE -Q"exec SP_GENERATE_INSERTS '+@TAB_NAME+'" >> c:\myTable.sql'

    select @cmd -- + '...'

    EXEC master.dbo.xp_cmdShell @cmd

    set @fetchcount=@fetchcount+1

    fetch next from C_mf1 into @tab_name

    END

    END

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

  • Would it be better to have 1 file per table?

    set @cmd = 'sqlcmd -E -dWatauga_Test -SNV-DEVSQL1\DAPHNE -Q"exec SP_GENERATE_INSERTS '+@TAB_NAME+'" > "c:\' + @TAB_NAME + '.sql" '

    Separate files will allow you to order execution of inserts without editing "sql" file.

    _____________
    Code for TallyGenerator

  • Thanks again I didnt know that we can run OS commands from OSQL command.

  • You're not... the DEL command is being executed by xp_CmdShell and the >> is just part of any command line as a redirection of output from whatever command is on the same line.

    --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 12 posts - 1 through 11 (of 11 total)

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