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
 General SQL Server Forums
 New to SQL Server Programming
 output txt file

Author  Topic 

usafelix
Posting Yak Master

165 Posts

Posted - 2014-08-28 : 21:42:51
Anyone can help give idea how to output txt file in query by simple way ? This query have error.

Select * from invoice
into 'c:\abc.txt'

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-08-28 : 22:06:40
you will need to use low level code to move it to a text file,
EXEC master..xp_cmdshell'bcp "SELECT TOP 5 whatever FROM where-ever" queryout "c:\text.txt" -c -T -x'


Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2014-08-28 : 22:09:18
it is copy your command place into query that is workable ?
Go to Top of Page

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-08-28 : 22:15:50
also in ssms query-results to-result to file, might be the easiest way to go if xp_cmdshell isnt going

c
Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2014-08-28 : 22:17:50
Chris,
pls give a guideline use my select * from invoice , then how to do next step ?

Go to Top of Page

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-08-28 : 22:21:50
try --

EXEC master..xp_cmdshell'bcp "SELECT * FROM INVOICE" queryout "c:\abc.txt" -c -T -x'

or within sql management studio(ssms) go to query - results to - result to file, then when you execute your select statement sql does all the work

c
Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2014-08-28 : 22:26:51
Hi kelly ,
I try to run this below command and error prompt " could not find the store procedure in "master.xpcmdshell" ?

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE

EXEC master.xp_cmdshell'bcp "SELECT * FROM invoice" queryout "c:\text.txt" -c -T -x'
Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2014-08-28 : 22:30:54
i have try to use ssms go to query for result to file that is ok. but everytime that I need to give a file name to save that is not my expect. I desire to query contain my expect file name.
Go to Top of Page

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-08-28 : 22:31:01
c
Go to Top of Page
   

- Advertisement -