How to create a text file from an SP and push the output of a query into it?

Question

Post reply

How to create a text file from an SP and push the output of a query into it?

Soorya

Ten Centuries

Points: 1314
More actions
May 19, 2009 at 2:53 am

#209789

Hi,
I need the output of a particualr query in a flat file. How do i do it from the Stored Proc?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

Using bcp or sqlcmd command utility in conjunction with xp_cmdshell procedure. For e.g.

EXECUTE master.dbo.xp_cmdshell 'sqlcmd -Q "SELECT * FROM AdventureWorks.Person.Contact" -o

MyOutput.txt'

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT FirstName, LastName FROM AdventureWorks.Person.Contact ORDER BY LastName, Firstname" queryout Contacts.txt -c -T'

Or even you can use OPENROWSET, OLE Automation Objects & Linked Servers.

--Ramesh

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

Hi Ramesh

Ramesh (5/19/2009)
...OLE Automation Objects...

:w00t:

... I really hope Active-X will die with CLR integration.

Greets

Flo

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

Florian Reischl (5/19/2009)
Hi Ramesh
Ramesh (5/19/2009)
...OLE Automation Objects...
:w00t:
... I really hope Active-X will die with CLR integration.
Greets
Flo

:hehe:And I am hoping that Active-X should die with T-SQL.

I never was a fan of CLR and I hope never would want to be one.:-D

--Ramesh

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Ramesh (5/19/2009)
Florian Reischl (5/19/2009)
Hi Ramesh
Ramesh (5/19/2009)
...OLE Automation Objects...
:w00t:
... I really hope Active-X will die with CLR integration.
Greets
Flo
:hehe:And I am hoping that Active-X should die with T-SQL.
I never was a fan of CLR and I hope never would want to be one.:-D

CLR rocks for file handling! :laugh:

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 5

Florian Reischl (5/19/2009)
CLR rocks for file handling! :laugh:

Ya, I know CLR rocks with file handling, string manipulations etc., but I never really liked those .NET languages, 'cause I never succeeded in learning those.:crazy:

--Ramesh

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

Ramesh (5/19/2009)
Florian Reischl (5/19/2009)
CLR rocks for file handling! :laugh:
Ya, I know CLR rocks with file handling, string manipulations etc., but I never really liked those .NET languages, 'cause I never succeeded in learning those.:crazy:

I guess the main problem for learning .Net are the available books. Most are huge compendiums which handle much more than really needed things for CLR in SQL.

A (still) good book is "Inside C#" which came out 2001. It doesn't cover all the cool things like LINQ, WCF, ... but it it covers the real needed things to understand .Net, nothing more.

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 7

In case you don't wanna learn C# or like me, just don't find the time to do it, there are two very good articles about reading from and writing to files on Simple Talk:

http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/

http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/

[font="Verdana"]Markus Bohse[/font]

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 8

Florian Reischl (5/19/2009)
Ramesh (5/19/2009)
Florian Reischl (5/19/2009)
CLR rocks for file handling! :laugh:
Ya, I know CLR rocks with file handling, string manipulations etc., but I never really liked those .NET languages, 'cause I never succeeded in learning those.:crazy:
I guess the main problem for learning .Net are the available books. Most are huge compendiums which handle much more than really needed things for CLR in SQL.
A (still) good book is "Inside C#" which came out 2001. It doesn't cover all the cool things like LINQ, WCF, ... but it it covers the real needed things to understand .Net, nothing more.

Thank you, Flo, I really appreciate your help. On the contrary, I hope that you won't make me that firm to start learning CLR.:w00t:

--Ramesh

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 9

MarkusB (5/19/2009)
In case you don't wanna learn C# or like me, just don't find the time to do it, there are two very good articles about reading from and writing to files on Simple Talk:
http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/
http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/

Thanks for links, Mark.

--Ramesh