SQL Select results to a Text File

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Select results to a Text File

Author

Topic

joriveek
Starting Member

33 Posts

Posted - 2006-05-12 : 07:28:41

Hi,

I was trying to do this but did not work.

Select employee_name From employees
into outfile 'c:/test.txt'

I want to send the Select Result set to a text file programmatically
from within my stored procedure,

Any ideas/

Thanks
J.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-12 : 07:34:26

use BCP QueryOut

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-12 : 07:46:23

Exec Master..xp_Cmdshell 'bcp "Select * from DBname..tableName" queryout "FilePath" -c'

Madhivanan

Failing to plan is Planning to fail

joriveek
Starting Member

33 Posts

Posted - 2006-05-12 : 10:41:13

Thanks but if I use scope_idetity() function like below

Exec Master..xp_Cmdshell'bcp "Select Content From ISPProviderTable Where ExportId = SCOPE_IDENTITY()" queryout "c:\rao.xml" -c -T -x'

It is not picking up SCOPE_IDENTITY, probably is running background of database without having knowledge from the command prompt.
But if I give a calue to ExportId = 25 or any value, it works fine...

Any ideas?

Thanks

LeoC
Starting Member

37 Posts

Posted - 2006-05-17 : 14:07:50

I have a similar need; I just need to be able to name the file differently (i.e. date based) and preferably an XLS or CSV.
Any body done this before? it seems to be a simple thing to do however I just can find a way to say have a week of result set files.
Thanks in advance for any help!

lec.

Maux Maux
Starting Member

10 Posts

Posted - 2006-05-17 : 14:30:00

There is no identity to pass in your query. Doing a select does not generate an insert. If you did something like


Exec Master..xp_Cmdshell'bcp "Insert into ISPProviderTable [some values here] ;Select Content From ISPProviderTable Where ExportId = SCOPE_IDENTITY()" queryout "c:\rao.xml" -c -T -x'

i would expect you to get something.

LeoC
Starting Member

37 Posts

Posted - 2006-05-17 : 15:07:33

Thanks for the rapid info.
I'm not quite sure I get the point on having different file names for every run of the xp_cmdshell.... (is 'ISPProviderTable' a key word for this procedure to work?)
Also I just doing a simple select with no SCOPE_IDENTITY need; but I would prefer to use .XLS not '.xml' type of files. This should not be an issue, right?

lec.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-18 : 02:33:56

>> Where ExportId = SCOPE_IDENTITY()"

If you want to have only the last record, then try
Select Top 1 * From ISPProviderTable Order by IdCol DESC

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources