You must Sign In to post a response.
  • Category: SQL Server

    Where the data stored if we use BCP command

    Hi There,

    I have used bcp command to export data from sql server to excel file.It shows file copied.but I unable to find the excelsheet that I given in the path.

    Could you please tell me where its stored.Or did i misunderstood the concept of BCP export?

    here is my query

    DECLARE @cmd VARCHAR(255)
    SET @cmd = 'bcp "SELECT * FROM database.dbo.vw_Billwise" queryout "D:\testing.xls" -U sa -P mypwd -c'
    Exec xp_cmdshell @cmd

    Thanks in advance
  • #763377
    Hello Sharmila,

    You are nothing doing wrong.

    Just i have configured this query :

    EXEC sp_configure 'show advanced options', 1
    GO
    -- To update the currently configured value for advanced options.
    RECONFIGURE
    GO
    -- To enable the feature.
    EXEC sp_configure 'xp_cmdshell', 1
    GO
    -- To update the currently configured value for this feature.
    RECONFIGURE
    GO

    DECLARE @cmd VARCHAR(255)
    SET @cmd = 'bcp "SELECT * FROM MyDB.dbo.Customer" queryout "D:\EmployeeData.xls" -U sa -P mypwd -c'
    Exec xp_cmdshell @cmd

    See attached image. It's an output i have got using this query.

    I found my file in D drive only which path i have defined in the query.

    Try this. It will work for you.

    Hope for the best.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #763379
    Hello Sharmila,

    I am sorry i forgot to attached the output.

    See this is the output i have in EmployeeData file of excel which i have defined in the query.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

    Delete Attachment

  • #763381
    Thank you Nirav,But It doesnt have column header..Is that possible to import that too..

  • #763384
    Hello Sharmila,

    Mostly it is best to use Import Export Data Wizard for for exporting table table data to excel.

    If you are going to export table data to excel is very difficult using bcp utility.
    You can easily export table data to csv file with headers also.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"


Sign In to post your comments