How to produce an csv output file from stored procedure in SQL Server

Question

Is it possible to generate a csv file from a stored procedure in SQL Server  I created my stored procedure and I want to stored some result as csv  does someone know how to achieve this

User · Answer

I also used bcp and found a couple other helpful posts that would benefit others if finding this thread

Don't use VARCHAR(MAX) as your @sql or @cmd variable for xp_cmdshell; you will get and error

Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1 Procedure expects parameter 'command_string' of type 'varchar'.

http://www.sqlservercentral.com/Forums/Topic1071530-338-1.aspx

Use NULLIF to get blanks for the csv file instead of a NUL (viewable in hex editor, or notepad++). I used that in the SELECT statement for bcp

How to make Microsoft BCP export empty string instead of a NUL char?

User · Answer

Found a really helpful link for that  Using SQLCMD for this is really easier than solving this with a stored procedure  http   www excel-sql-server com sql-server-export-to-excel-using-bcp-sqlcmd-csv htm

User · Answer

I have tried this and it is working fine for me   sqlcmd -S servername -E -s  -W -k1 -Q   sql query here   gt     file path file name csv

User · Answer

This script exports rows from specified tables to the CSV format in the output window for any tables structure  Hope  the script will be helpful for you -  DECLARE         TableName SYSNAME        ObjectID INT  DECLARE  tables  CURSOR READ ONLY FAST FORWARD LOCAL FOR      SELECT                  s name           t name                 t  object id      FROM           SELECT DISTINCT               t  schema id                t  object id                t name         FROM sys objects t WITH  NOWAIT          JOIN sys partitions p WITH  NOWAIT  ON p  object id    t  object id          WHERE p  rows   gt  0             AND t  type     U        t     JOIN sys schemas s WITH  NOWAIT  ON t  schema id    s  schema id      WHERE t name IN    lt your table name gt     OPEN  tables   FETCH NEXT FROM  tables  INTO         TableName        ObjectID  DECLARE         SQLInsert NVARCHAR MAX         SQLColumns NVARCHAR MAX         SQLTinyColumns NVARCHAR MAX   WHILE   FETCH STATUS   0 BEGIN      SELECT             SQLInsert                 SQLColumns                 SQLTinyColumns            WITH cols AS                SELECT                c name               datetype   t name               c column id         FROM sys columns c WITH  NOWAIT          JOIN sys types t WITH  NOWAIT  ON c system type id   t system type id AND c user type id   t user type id         WHERE c  object id     ObjectID             AND c is computed   0             AND t name NOT IN   xml    geography    geometry    hierarchyid             SELECT             SQLTinyColumns   STUFF               SELECT         c name                   FROM cols c             ORDER BY c column id             FOR XML PATH  TYPE  ROOT  value       NVARCHAR MAX     1  2                 SQLColumns   STUFF  SELECT CHAR 13                CASE                  WHEN c datetype    uniqueidentifier                       THEN             ISNULL        CAST      c name      AS VARCHAR MAX             NULL                      WHEN c datetype IN   nvarchar    varchar    nchar    char    varbinary    binary                        THEN             ISNULL        CAST REPLACE      c name                       AS NVARCHAR MAX             NULL                      WHEN c datetype    datetime                      THEN             ISNULL        CONVERT VARCHAR       c name       120            NULL                      ELSE                              ISNULL CAST      c name      AS NVARCHAR MAX      NULL                 END             FROM cols c             ORDER BY c column id             FOR XML PATH  TYPE  ROOT  value       NVARCHAR MAX     1  10   CHAR 13                  DECLARE  SQL NVARCHAR MAX              SET NOCOUNT ON      DECLARE             SQL NVARCHAR MAX                    x INT   1            count INT    SELECT COUNT 1  FROM      TableName           IF EXISTS          SELECT 1         FROM tempdb dbo sysobjects         WHERE ID   OBJECT ID   tempdb   import                  DROP TABLE  import       SELECT      SQLTinyColumns        RowNumber     ROW NUMBER   OVER  ORDER BY      SQLTinyColumns          INTO  import     FROM      TableName           WHILE  x  lt   count BEGIN          SELECT  SQL   STUFF           SELECT      SQLColumns                         FROM  import          WHERE RowNumber BETWEEN  x AND  x   9         FOR XML PATH  TYPE  ROOT  value          NVARCHAR MAX      1  1                 PRINT  SQL           SELECT  x    x   10      END       EXEC sys sp executesql  SQL      FETCH NEXT FROM  tables  INTO             TableName            ObjectID  END  CLOSE  tables  DEALLOCATE  tables    In the output window you ll get something like this  AdventureWorks Person Person    1 EM 0 NULL Ken J S  nchez NULL 0 92C4279F-1207-48A3-8448-4636514EB7E2 2003-02-08 00 00 00 2 EM 0 NULL Terri Lee Duffy NULL 1 D8763459-8AA8-47CC-AFF7-C9079AF79033 2002-02-24 00 00 00 3 EM 0 NULL Roberto NULL Tamburello NULL 0 E1A2555E-0828-434B-A33B-6F38136A37DE 2001-12-05 00 00 00 4 EM 0 NULL Rob NULL Walters NULL 0 F2D7CE06-38B3-4357-805B-F4B6B71C01FF 2001-12-29 00 00 00 5 EM 0 Ms  Gail A Erickson NULL 0 F3A3F6B4-AE3B-430C-A754-9F2231BA6FEF 2002-01-30 00 00 00 6 EM 0 Mr  Jossef H Goldberg NULL 0 0DEA28FD-EFFE-482A-AFD3-B7E8F199D56F 2002-02-17 00 00 00

User · Answer

You can do this using OPENROWSET as suggested in this answer  Reposting Slogmeister Extrarodinare answer   Use T-SQL  INSERT INTO OPENROWSET   Microsoft ACE OLEDB 12 0   Text Database D   HDR YES FMT Delimited   SELECT   FROM  FileName csv    SELECT Field1  Field2  Field3 FROM DatabaseName   But  there are couple of caveats    You need to have the Microsoft ACE OLEDB 12 0 provider available  The Jet 4 0 provider will work  too  but it s ancient  so I used this one instead  The  CSV file will have to exist already  If you re using headers  HDR YES   make sure the first line of the  CSV file is a delimited list of all the fields

User · Answer

Just to answer a native way to do this that finally worked  everything had to be casted as a varchar ALTER PROCEDURE  clickpay   sp GetDocuments     Submid bigint  AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements  SET NOCOUNT ON     DECLARE  raw sql varchar max   DECLARE  columnHeader VARCHAR max  SELECT  columnHeader   COALESCE  columnHeader                column name       FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    Documents   DECLARE  ColumnList VARCHAR max  SELECT  ColumnList   COALESCE  ColumnList            CAST   column name    AS VARCHAR   FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    Documents   SELECT  raw sql    SELECT     columnHeader    UNION ALL SELECT      ColumnList     FROM      Documents where Submid        CAST  Submid AS VARCHAR   -- Insert statements for procedure here exec  raw sql  END

User · Answer

I think it is possible to use bcp command  I am also new to this command but I followed this link and it worked for me

[sql] How to produce an csv output file from stored procedure in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to stored-procedures