monthly stored procedure to export table to text file

  • hi guys,

    what i would like to do is create a stored procedure that extracts data from 2 tables "users" and "userpoints" -certain columns preferably- and export that to a text file on a monthly basis.

    i've tried the following (without adding the monthly portion in yet):

    Create procedure dbo.IMPEXPFROMDB

    @filepath varchar(255), --filepath

    @direction varchar(5) , --direction(In/Out)

    @tablename varchar(255) --Valid tablename/viewname

    as

    begin

    set nocount on

    Declare @cmd varchar(1000)

    Declare @dbname varchar(1000)

    /* Validation for filepath */

    If (@filepath is null)

    begin

    Raiserror('Please enter the FilePath ',16,1)

    Return

    end

    /* Validation for Direction */

    If (@direction is null) or (@direction Not In('In','Out'))

    begin

    Raiserror('Please enter the Direction(In/Out)',16,1)

    Return

    end

    /* Validation for Table name */

    If (@tablename is null)

    begin

    Raiserror('Please enter the Tablename or Viewname',16,1)

    Return

    end

    else if (Object_id(@tablename)is null)

    begin

    Raiserror('Please enter a Validate Tablename or Viewname',16,1)

    Return

    end

    --Get the database name.

    set @dbname = db_name()

    --bcp command.

    set @cmd = 'bcp '+@dbname+'..'+@tablename+' '+@direction+' '+@filepath+' -c -S -U -P'

    --To run the bcp using extended stored procedure.

    exec master..xp_cmdshell @cmd

    set nocount off

    end

    Exec IMPEXPFROMDB ‘databaseOneTest’, ‘UserReminder’, ‘OUT’, ‘C:\testOne\testzz.txt’ -c -Shomefix.techsailor.com -Uxxx -Pxxx

    I received the following error:

    Incorrect syntax near ' ‘ '

    also, where should i add in the condition to automate this task on a monthly basis,

    thanks alot'-

  • If you print the command that you're building up, what do you get?

    As for monthly automation, create a SQL Agent job and set up a monthly schedule.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • well nothing gets printed out, because of that error in syntax.

    I shall add the sql agent job though. thanks.

    Is there an easier way to extract columns form tables and dump them into a single text file, without using the command prompt- ?

    when i do the SP below, i get more than 32 statements stating 13 rows affected followed by the error :- Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

    CREATE Procedure BCP_Text_File

    (

    @table varchar(100),

    @FileName varchar(100)

    )

    as

    If exists(Select * from information_Schema.tables where table_name=@table)

    Begin

    Declare @STR varchar(1000)

    set @STR='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table+'" queryout "'+@FileName+'" -c'''

    Exec(@str)

    end

    else

    Select 'The table '+@table+' does not exist in the database'

    EXEC BCP_Text_File 'userreminder','C:\test.txt'

    Your input would be greatly appreciated.

  • Hi,

    see the last 3 line. It will work. May be you have to delete one '.' :hehe:

    SET @cmd = 'bcp ' + @dbname + '.' + @tablename + ' ' + @direction + ' ' + @filepath + ' -T -c'

    PRINT @cmd

    --To run the bcp using extended stored procedure.

    EXEC master..xp_cmdshell @cmd

    ---

  • ok i'll try that and get back to you, having problems with the server now,

    should get it fixed in a bit,

    -Thanks-

  • mark.rozario (1/28/2008)


    well nothing gets printed out, because of that error in syntax.

    If you double click the error message (in management studio) it will take you to the line of the error.

    What I was suggesting was adding print @cmd just before the exec master..xp_cmdshell so that you can see exactly what you're exec-ing. Often helps to debug.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hey there, actually I am able to get the commands successfully executed not in the management studio, however, when i run the following command, I get the error saying I have passed in too many parameters:

    Exec IMPEXPFROMDB 'databaseTest' 'tableNameOne', 'OUT', 'C:\testOne\testzz.txt'

    I have tried removing one or the other but then prompts to enter data for that missing field will appear, i.e. "Please enter a tablename"- if i remove the tableNameOne from the list.

    once again this is the parameter line required in my stored procedure:

    set @cmd = 'bcp '+@dbname+'..'+@tablename+' '+@direction+' '+@filepath+' -c -T'

    Am i missing out something simple?

    Thanks alot,

  • Your stored proc is written to take 3 parameters and you're passing it 4.

    Create procedure dbo.IMPEXPFROMDB

    @filepath varchar(255), --filepath

    @direction varchar(5) , --direction(In/Out)

    @tablename varchar(255) --Valid tablename/viewname

    as

    ...

    Exec IMPEXPFROMDB 'databaseTest' 'tableNameOne', 'OUT', 'C:\testOne\testzz.txt'

    The proc takes as a 1st parameter the file path and name, as a second parameter the direction and as a third parameter the table name.

    In your call to it, you're passing first a db name (I assume), second the table name, third the direction and lastly the file name.

    Based on the proc's declaration, the call to it should be

    Exec IMPEXPFROMDB 'C:\testOne\testzz.txt', 'OUT', 'tableNameOne'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • yes it does work now however im having some remote desktop problems,

    Will connect there and check if the file has been created, but the output does suggest it has been.

    Thanks alot 🙂

  • hey yea it does work when i exec it, thanks,

    im getting a bit more greedy now and would like to export more tables, with possible a break between data from each table.

    excluding the break line of code, i've entered the following:

    Exec IMPEXPFROMDB 'Voucher','Users','Userpoints','OUT','C:\testzz.txt'

    I have changed my bcp command to:

    set @cmd = 'bcp '+@dbname+'..'+@tablename1+' '+@dbname+'..'+@tablename2+' '+@dbname+'..'+@tablename3+' '+@direction+' '+@filepath+' -c -T'

    i get the error: "Please enter the Direction(In/Out)"

    i tried to place the out and file path after each table and duly change the bcp command but i get the error : "too many parameters entered"

    any ideas, thanks

  • If you check the parameters for bcp in books online, you'll note that it takes a single table. If you want to export multiple tables, run bcp multiple times.

    Books Online

    bcp {[[database_name.][owner].]{table_name | view_name} | "query"}

    {in | out | queryout | format} data_file

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hey, how did you resolve the error message - Msg 170 , even I am facing the same prolem, Incorrect syntax near ' ' '.

    And one more thing, so for this proc the way to execute it is :

    Exec Procname 'DBName', 'Tablename','Direction','Filepath'

    please guide me

    Thanks

  • Heh... Dude! I thought you said your buddy had it whipped.... 😉

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

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