Solved

writing files from T-SQL

Posted on 2000-03-28
3
399 Views
Last Modified: 2012-06-22
Is it possible to write a stored procedure that generates a file in the server ?

The idea is to have a stored that generates text-files to export to an other system.

Any other ideas ?

0
Comment
Question by:marpon
  • 2
3 Comments
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2665644
marpon,
Have you tried using Bulk copy program utility (BCP) or Data Transformation Services (DTS)?
0
 
LVL 4

Accepted Solution

by:
Gustavo Perez Buenrostro earned 150 total points
ID: 2665698
Here is an example (using BCP utility):

set nocount on
create table SomeTable (Col1 int, col2 varchar(2))
GO

insert into SomeTable values (2,'A')
insert into SomeTable values (1,'B')
insert into SomeTable values (5,'CD')
insert into SomeTable values (4,'ER')
GO

create proc spExportData
as
begin
  exec master..xp_cmdshell 'bcp SomeTable out c:\temp\File.txt -c'
end
GO

 

exec spExportData

Here is the result:


output
----------------------------------------------------------------------
Password:
NULL
Starting copy...
NULL
8 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total        1 Avg        0 (8000.00 rows per sec.)

(7 row(s) affected)
0
 

Author Comment

by:marpon
ID: 2673352
I tested and it worked. Is this the only way ? I mean, it 's a pitty being inside the server (in a stored) to have to go down and reconnect as a client (that is what bcp does, doesn 't it?). More, in my case, I have to put user/password combination, but I think this is because I haven 't got trusted connections.

Isn 't there any sql extensions like "SELECT INTO FILE xxxx" ... ?

Anyway, it works and does what I asked for. Thank you !

I didn 't know about .sp_cmdshell(). Can I execute anything with it ?

Regards,



0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
need help in sql 4 65
grouping logic 6 46
Help with SQL Server Stoplist 2 16
TSQL - IF ELSE? 3 27
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now