Solved

writing files from T-SQL

Posted on 2000-03-28
3
403 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Database ERD 4 29
Need return values from a stored procedure 8 21
Where is the Help Section? 8 27
Substring works but need to tweak it 14 15
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

829 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