none
generate csv file from store procedure

    Question

  • Hi Exceperts,

    any one suggest how generate csv file from storeprocedure,

    please provide any examples

    Thanks

    Monday, July 01, 2013 7:49 AM

Answers

All replies

  • Please refer

    https://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/ 

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, July 01, 2013 7:53 AM
  • Thanks for quirk response,

    declare @sql varchar(8000)select @sql = 'bcp master..sysobjects out 
    c:\bcp\sysobjects.txt -c -t, -T -S'
    + @@servername
    exec master..xp_cmdshell@sql

    where i have t write this code in sp, how apply above code to my sp?

    Thanks

    Monday, July 01, 2013 7:54 AM
  • Hi 

    You can use openrowset function. For example

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Text;Database=F:\Test\;HDR=Yes;', 'SELECT * FROM Test.csv')
    SELECT * FROM Table

    Monday, July 01, 2013 7:58 AM
  • Hi 

    You can use openrowset function. For example

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Text;Database=F:\Test\;HDR=Yes;', 'SELECT * FROM Test.csv')
    SELECT * FROM Table

    Openrowset is reserved for Excel files, and it's a horrid function at best, if you're going down that route then you're much better off using SSIS. But for the purposes of this question a Bulk Export is sufficient for the OP's needs. 

    See this code:

    DECLARE @SQL NVARCHAR(4000)
    
    SET @SQL = '
    bcp "SELECT STATEMENT HERE" queryout "FILENAME.csv" -t, -c -T'
    
    EXEC MASTER..XP_CMDSHELL @SQL

    Thanks.

    Monday, July 01, 2013 8:16 AM
  • thanks for all,

    shall i change this or not 

    EXEC MASTER..XP_CMDSHELL @SQL

    thanks

    Monday, July 01, 2013 8:43 AM
  • thanks for all,

    shall i change this or not 

    EXEC MASTER..XP_CMDSHELL @SQL

    thanks

    ...Why would you want to change it? It's correct...
    Monday, July 01, 2013 8:44 AM
  • what is master..XP_CMDSHELL@SQL

    thanks

    Monday, July 01, 2013 8:51 AM
  • what is master..XP_CMDSHELL@SQL

    thanks

    I think you need to do a bit of exploration and study of SQL Server before you start going any further. Judging on your questions and replies here, you do not seem to grasp concepts of SQL Syntax and the way dynamic SQL behaves.

    Here's a BOL article on xp_cmdshell:

    http://msdn.microsoft.com/en-us/library/ms175046.aspx

    The quick answer for you is that it is a function used in SQL to execute/pass various tasks from the command line to the stack in Windows. 

    Thanks.

    Monday, July 01, 2013 9:05 AM
  • Really thanks Johny,

    i will come with my sp, please keep in this way?

    Monday, July 01, 2013 9:12 AM
  • Hi , 

    Please once see my below sp , modify as per needs , very thanks, anything wrong in my sp ?

    Alter procedure spInvoice
    @Invoiceno varchar(16),
    @department int=2
    AS
    BEGIN
    select D value from Invoice where Svalue=@Invoiceno
    union
    select D value from Department where Svalue=@department
    exce spInvoice

    here i want generate as csv file , where write this below code in sp

    declare @sql varchar(8000)select @sql = 'bcp master..sysobjects out 
    c:\bcp\sysobjects.txt -c -t, -T -S'+ @@servernameexec master..xp_cmdshell@sql

    Monday, July 01, 2013 9:20 AM
  • Hi , 

    Please once see my below sp , modify as per needs , very thanks, anything wrong in my sp ?

    Alter procedure spInvoice
    @Invoiceno varchar(16),
    @department int=2
    AS
    BEGIN
    select D value from Invoice where Svalue=@Invoiceno
    union
    select D value from Department where Svalue=@department
    exce spInvoice

    here i want generate as csv file , where write this below code in sp

    declare @sql varchar(8000)select @sql = 'bcp master..sysobjects out 
    c:\bcp\sysobjects.txt -c -t, -T -S'+ @@servernameexec master..xp_cmdshell@sql

    This won't work unfortunately. This is not dynamic SQL. You need to concatenate your variables to a statement as a string to get it to work:

    ALTER PROCEDURE DBO.SPINVOICE
    @INVOICE_NO VARCHAR(16)
    , @DEPARTMENT_NO INT = 2
    
    AS
    
    BEGIN
    
    DECLARE @SQL NVARCHAR(4000)
    
    SET @SQL = 'BCP "SELECT [D VALUE] FROM DBO.INVOICE WHERE [SVALUE] = ' + @INVOICE_NO + 'UNION ALL SELECT [D VALUE] FROM DBO.DEPARTMENT WHERE [SVALUE] = ' + CAST( @DEPARTMENT AS VARCHAR)+'" QUERYOUT "FILENAME.csv" -t, -c -T'
    EXEC MASTER..XP_CMDSHELL @SQL
    

    But as I said earlier, if this is for a production environment I would urge you to learn some more basics before moving onto this.

    Monday, July 01, 2013 10:32 AM
  • Thanks John,

    Please suggest any goodsites for basics in dynamic sql ..

    thanks

    Monday, July 01, 2013 12:40 PM
  • Hi John, 

    as per as its copy and pastes , shall run the code , before run code any prerequstties?

    Thanks

    Monday, July 01, 2013 1:07 PM
  • You still need to put your own values in there.

    This is the route I took to having a good basic understand of SQL:

    Firstly I read the SQL Standard basics on W3Schools - http://www.w3schools.com/sql/

    I've referred back to this site many times (although not really anymore) to brush up on the basics and it gave me a good understanding of how select, update, insert and delete all behave and also how to format dates and use basic functions for arithmetic.

    Secondly I read the MCTS SQL Server 2008 book on Database Development as part of the training kit. You can find most if not all of this information for free in BOL (Books Online) - http://msdn.microsoft.com/en-us/library/ms130214(v=sql.105).aspx

    The third and probably most important step in my learning was implementing what I'd learnt in a practical environment. So having a job using SQL Server is a key factor in my learning experience.

    The fourth was joining this forum and joining in discussions/answering questions. You find you pick up alot of tips while attempting to find solutions for someone else that will become useful in the future.

    Thanks.

    Monday, July 01, 2013 2:58 PM