Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

writing files from T-SQL

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
marpon
Asked:
marpon
  • 2
1 Solution
 
Gustavo Perez BuenrostroCommented:
marpon,
Have you tried using Bulk copy program utility (BCP) or Data Transformation Services (DTS)?
0
 
Gustavo Perez BuenrostroCommented:
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
 
marponAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now