none
Writing table output to text file in stored procedure

    Question

  • Hi,

       how can i write table output to a text file in stored procedure..    

    Actually this i tried but still i'm getting error..

    Exec master..xp_cmdshell 'bcp "select name from sysobjects" queryout "c:\tempexportfile.txt" -c -T -SSys-7 -Usa -Psa'

    In SP, Enabled xp_cmdshell by

    EXEC sp_configure 'show advanced options', 1
    GO
    RECONFIGURE
    GO
    EXEC sp_configure 'xp_cmdshell', 1
    GO
    RECONFIGURE
    GO

     

    Now i'm getting this error, 

    SQLState = S1000, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
    NULL

    Please help me to fix this issue.

    Is any other easy way to write in text file from sp



    Thanks Ravi
    Thursday, June 17, 2010 9:08 AM

Answers

  • Do you have permission to write to C:\ drive?

    declare @sql varchar(255)
    set @sql='bcp "select * FROM northwind.dbo.orders WHERE employeeid = 1" queryout "c:\temp\1.csv" -c -T -Sservername -Usa -Ppas'
    exec master..xp_cmdshell @sql

     


    Best Regards, Uri Dimant http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Ravi_P Thursday, June 17, 2010 12:53 PM
    Thursday, June 17, 2010 9:27 AM
    Moderator
  • Ravi,

    Try this !

    Create proc ToText
    @Path nvarchar(100)
    AS
    declare @Cmd1 varchar(200)
    begin 
    set @Cmd1 = 'bcp "select name from sysobjects" queryout "' + @Path + '" -c -t -S<SQLServername> -Usa -Ppassword'
    exec master..xp_cmdshell @cmd1
    end

     Now execute the procedure as -> Exec ToText 'C:\Temp\1.csv'

    Thanks, Krishna. www.Blogs.SQLServer.in

     

    • Marked as answer by Ravi_P Thursday, June 17, 2010 12:54 PM
    Thursday, June 17, 2010 11:27 AM

All replies

  • Do you have permission to write to C:\ drive?

    declare @sql varchar(255)
    set @sql='bcp "select * FROM northwind.dbo.orders WHERE employeeid = 1" queryout "c:\temp\1.csv" -c -T -Sservername -Usa -Ppas'
    exec master..xp_cmdshell @sql

     


    Best Regards, Uri Dimant http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, June 17, 2010 9:27 AM
    Moderator
  • Do you have permission to write to C:\ drive?

    declare @sql varchar(255)
    set @sql='bcp "select * FROM northwind.dbo.orders WHERE employeeid = 1" queryout "c:\temp\1.csv" -c -T -Sservername -Usa -Ppas'
    exec master..xp_cmdshell @sql

     


    Best Regards, Uri Dimant http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Ravi_P Thursday, June 17, 2010 12:53 PM
    Thursday, June 17, 2010 9:27 AM
    Moderator
  • Uri,

     

     

       C:\Temp\1.csv is local system path,  or server system path

     


    Thanks - Ravi
    Thursday, June 17, 2010 9:54 AM
  • Ravi,

    Try this !

    Create proc ToText
    @Path nvarchar(100)
    AS
    declare @Cmd1 varchar(200)
    begin 
    set @Cmd1 = 'bcp "select name from sysobjects" queryout "' + @Path + '" -c -t -S<SQLServername> -Usa -Ppassword'
    exec master..xp_cmdshell @cmd1
    end

     Now execute the procedure as -> Exec ToText 'C:\Temp\1.csv'

    Thanks, Krishna. www.Blogs.SQLServer.in

     

    • Marked as answer by Ravi_P Thursday, June 17, 2010 12:54 PM
    Thursday, June 17, 2010 11:27 AM
  • Uri,

      u r right...i don't have permission..

        Thank you very much.. i gave rights now its working...  

     

     


    Thanks - Ravi
    Thursday, June 17, 2010 12:53 PM
  • Krishna,

     

      Thank u very much .. its working..


    Thanks - Ravi
    Thursday, June 17, 2010 12:55 PM
  • you are most welcome !

    Happy to hear that, the solution woked for you.

    Thanks, Krishna. www.Blogs.SQLServer.in

    Friday, June 18, 2010 5:08 AM