Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

writing files from T-SQL

Posted on 2000-03-28
3
Medium Priority
?
408 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 450 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

916 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