Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2004
    Posts
    7

    Question Unanswered: output table to a text file (was "Newbie needs help!")

    I have an assignment and need to dosomething that should be simple, basically output the contents of a table to a text file.

    I have been trying this syntax:

    bcp "dbo.items_with_constraints_tbl" out "J:\items.txt" -c

    But I keep on getting this error message:

    Server: Msg 179, Level 15, State 1, Line 1
    Cannot use the OUTPUT option when passing a constant to a stored procedure.

    I am completely lost!!

    Can anyone help me please?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Use QUERYOUT instead of OUT.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You appear to be missing a few BCP parameters, such as server name, either trusted or user and password, database name, etc! I'm thinking that BCP is very confused, so the error message is probable spurious.

    -PatP

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Check out Sql Server books online and type in bcp

    since you are a newbie, here is the path
    start>[all]programs>microsoft SQL Server>Books Online


    you will have to specify the authentication that pat suggestted and i dont see a file extension on your input file or the switch to indicate the file.

    BOOKS ONLINE {BCP}

  5. #5
    Join Date
    Aug 2004
    Posts
    7
    Thanks to all, but I still have some difficulties.

    I tried queryout, but I still had the same syntaz error.

    As for BCP paramters, from the ebook it seems that these are optional. At the moment I jusy wanted to produce a simple text file so I do not care much about formats. I am also the owner of the database and it resides on my computer. In the query analyzer I am working within the database, so again I believe that the source table pathway is not important (stand to be corrected). Same goes for the source table extension.

    On the otherhand I have tried to give the output text file a pathway buit still without any success.

    The example from the ebook is:

    bcp "Northwind.Jane Doe.Jane's Orders" out "Jane's Orders.txt" -c -q -U"Jane Doe" -P"go dba"

    and in my opinion, excluding permissions, databse pathways and optional paramters, it is not much different from my line.

    Any comments would be appreciated.

  6. #6
    Join Date
    Oct 2004
    Location
    India
    Posts
    11

    Smile

    Hi Meddoc,

    Try to use the following bcp command.

    bcp "databasename.dbo.items_with_constraints_tbl" out "J:\items.txt" -C -t"|" -S < Servername> -U < username > -P < passowrd >

    Regards,
    Sanoj Nair

  7. #7
    Join Date
    Dec 2004
    Posts
    47

    Thumbs up

    Hi Iam new to this dbForum & Iam posting a reply in this forum for the first time ...

    Ur problem over here is quite simple & straightforward. U r trying to exectue a utility command i.e 'bcp' in query analyser... U need to use 'xp_cmdshell' here... When u try it straight in query analyser it interprets as stored procedure....

    Try below code:

    ************************************************** ****
    DECLARE @FileName varchar(50),
    @bcpCommand varchar(2000)

    SET @FileName = REPLACE('c:\items_'+CONVERT(char(8),GETDATE(),1)+' .txt','/','-')

    SET @bcpCommand = 'bcp "dbo.items_with_constraints_tbl" out "'
    SET @bcpCommand = @bcpCommand + @FileName + '" -U username -P password -c'

    EXEC master..xp_cmdshell @bcpCommand

    ************************************************** ****

    U need to give ur 'username' 'password' here... If u need further help do ask..

    Thanks
    Sree

  8. #8
    Join Date
    Aug 2004
    Posts
    7

    Talking

    Sree Thanks a million. It worked fine!

  9. #9
    Join Date
    Dec 2004
    Posts
    47
    Thanks for replying when it worked... that brings encouragement and to post reply.... iam loving this now...

  10. #10
    Join Date
    Aug 2004
    Posts
    7
    sree you have a pm.

  11. #11
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    d'oh
    that's what happens when I dont read the questions in this forum carefully.

    I ASSUMED he got this error in cmd prompt not the Query analyzer?
    Server: Msg 179, Level 15, State 1, Line 1
    Cannot use the OUTPUT option when passing a constant to a stored procedure.
    i feel so seely, what a way to start the new year.

    ok robert, let's hear it.....
    Last edited by Ruprect; 01-03-05 at 13:40.

  12. #12
    Join Date
    Aug 2004
    Posts
    7
    Ruprect thanks for your help anyway. The real problem is that either there is no mention of the fact that you have to type the BCP line in the cmd prompt or else I failed to see it!

    I only realised when I discussed this issue with the lecture group and one of them remarked that one has to place such a line in the cmd prompt!!!!

    At least now I know both ways!

    Thanks again all of you.

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Ruprect
    ok robert, let's hear it.....
    Hey, on the 11th they are having some gathering at New Horizons. Are you coming?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •