none
Problem on using BCP to Export Data to a text file RRS feed

  • 問題

  • Hi
    i am having problem on creating a stored procedure on using BCP to export a table/Selected record to a text file.

    Method 1
    declare @cmdstring varchar(4000),
          @bcpstring varchar(4000)

    CREATE TABLE #test
    (
        CustomerCode VARCHAR(10),
        PhoneNumber VARCHAR(20)
    )

    INSERT INTO #test
    SELECT TOP 10 CustomerCode, PhoneNumber
    FROM Customer

    SET @bcpstring = 'exec Master.dbo.xp_Cmdshell ''bcp "Select * from #test" queryout "C:\Test2.txt" -c''' 
    print @bcpstring
    EXEC @bcpstring

    TRUNCATE TABLE  #test
    DROP TABLE #test

    Here is the error message that I have received
    exec Master.dbo.xp_Cmdshell 'bcp "Select * from #test" queryout "C:\Test2.txt" -c'
    Msg 203, Level 16, State 2, Line 22
    The name 'exec Master.dbo.xp_Cmdshell 'bcp "Select * from #test" queryout "C:\Test2.txt" -c'' is not a valid identifier.


    I have also tried to do it in the other way
    Method 2
    declare @cmdstring varchar(4000),
          @bcpstring varchar(4000)

    --
    CREATE TABLE #test
    (
        CustomerCode VARCHAR(10),
        PhoneNumber VARCHAR(20)
    )

    INSERT INTO #test
    SELECT TOP 10 CustomerCode, PhoneNumber
    FROM Customer

    set @bcpstring = 'bcp ' +  '#test' + ' queryout C:\Test2.txt -T -c' 

    print @bcpstring
    exec MASTER.DBO.xp_cmdshell @bcpstring

    TRUNCATE TABLE  #test
    DROP TABLE #test

    In this case I have receive another error message.
    -->SQL State = 37000, Native Error= 170
    --> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '#test'.
    --> NULL

    I would be grateful if someone could give me some help to make that query work. Or any other way that I could use TSQL to export a table into a text file will be appreciate.

    Many thanks

    Chi
    2008年4月11日 上午 08:47

解答

所有回覆

  • Try this:

     

    EXEC (@bcpstring)

    2008年4月11日 下午 02:50
  • Hi

    Thanks for you information.

    I think I am quite dump.

    I modified my code slightly and I received the following error.
    -----------------------------------------------------
    declare @cmdstring varchar(4000),
          @bcpstring varchar(4000)

    --


    set @bcpstring = 'bcp ' +  '"SELECT TOP 10 * FROM Customer"' + ' queryout "C:\Test2.txt" -T -c'
    SET @bcpstring = 'MASTER.DBO.xp_cmdshell ' +@bcpstring
    print @bcpstring
    --exec MASTER.DBO.xp_cmdshell @bcpstring
    EXEC (@bcpstring)

    --------------
    Here is the result

    (10 row(s) affected)
    MASTER.DBO.xp_cmdshell bcp "SELECT TOP 10 * FROM Customer" queryout "C:\Test2.txt" -T -c
    Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'SELECT TOP 10 * FROM Customer'.

    Any more suggestion?

    Many thanks

    Chi
    2008年4月14日 上午 07:57
  • What's your sql version? Top option works on sql7 and later.

    2008年4月14日 下午 03:31
  • Thanks for your information.

    It works now. I would be grateful if you could let me know whether I need to build a Format File.
    If I would like the export file look like.

    test2.txt

    "CustomerCode","CustomerName","Tel", "Age"
    "AA8888","Chi","12312312",20
    "AA8889","Test","33332312",18


    E.g. I would like to export the header and have Speech mark " surround" the String. Try to simulate the process of my previous post.
    http://forums.microsoft.com/hongkong/ShowPost.aspx?PostID=3118093&SiteID=82

    Many thanks

    Chi
    2008年4月15日 上午 07:57
  • When you run bcp to export data, it'll ask you if want to create format file.

    2008年4月15日 下午 02:28
  • Thanks very much for your information. Which mean I cannot format my the export file without a format file?

    Thanks
    2008年4月15日 下午 03:59
  • Yes, you can skip format file.

    2008年4月16日 下午 12:35
  • Thanks for your information. I will play around this function

    Thanks
    2008年4月16日 下午 01:12