Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

writing files from T-SQL

Posted on 2000-03-28
3
Medium Priority
?
407 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

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 ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

719 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