How to create text/csv file in stored procedure

Question

How to create text/csv file in stored procedure

Bilal

SSC Enthusiast

Points: 196
More actions
July 17, 2002 at 11:00 pm

#101201

I want the output of my stored procedure in a text or csv file. How can I do that?

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114704 More actions · Answer 1

If you are just running the stored procedure through Query Analyzer, check Tools | Options and modify the results to come back as a file, with the format desired.

If you are talking about the stored procedure itself creating the file, that's a different story.

One way is to build a dynamic query and use xp_cmdshell. For instance:



CREATE PROC dbo.usp_ShowExport

  @TableName varchar(30)

AS

  DECLARE @SQL nvarchar(255)



  SET @SQL = N'EXEC master.dbo.xp_cmdshell ''echo Viewing table '

+ @TableName  + N' >> c:\temp\test.txt '' '

  EXEC(@SQL)



  SET @SQL = N'SELECT * FROM ' + @TableName

  EXEC(@SQL)

GO

You can also create a linked server using Jet. An example from Books Online:

quote:
H. Use the Microsoft OLE DB Provider for Jet to access a text file
This example creates a linked server for directly accessing text files, without linking the files as tables in an Access .mdb file. The provider is Microsoft.Jet.OLEDB.4.0 and the provider string is 'Text'.
The data source is the full pathname of the directory that contains the text files. A schema.ini file, which describes the structure of the text files, must exist in the same directory as the text files. For more information about creating a schema.ini file, refer to Jet Database Engine documentation.
--Create a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\data\distqry',
NULL,
'Text'
GO
--Set up login mappings
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL
GO
--List the tables in the linked server
EXEC sp_tables_ex txtsrv
GO
--Query one of the tables: file1#txt
--using a 4-part name
SELECT *
FROM txtsrv...[file1#txt]

The catch here is you'll have to go into your ODBC settings and do some setup as well.

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

Edited by - bkelley on 07/17/2002 11:45:17 PM

Edited by - bkelley on 07/17/2002 11:47:05 PM

K. Brian Kelley
@kbriankelley