SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 output txt file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

usafelix
Posting Yak Master

Hong Kong
103 Posts

Posted - 08/28/2014 :  21:42:51  Show Profile  Reply with Quote
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 - 08/28/2014 :  22:06:40  Show Profile  Reply with Quote
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'



Edited by - Chris_Kelley on 08/28/2014 22:13:53
Go to Top of Page

usafelix
Posting Yak Master

Hong Kong
103 Posts

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

Chris_Kelley
Posting Yak Master

114 Posts

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

c

Edited by - Chris_Kelley on 11/11/2014 19:29:25
Go to Top of Page

usafelix
Posting Yak Master

Hong Kong
103 Posts

Posted - 08/28/2014 :  22:17:50  Show Profile  Reply with Quote
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 - 08/28/2014 :  22:21:50  Show Profile  Reply with Quote
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

Edited by - Chris_Kelley on 11/11/2014 19:29:38
Go to Top of Page

usafelix
Posting Yak Master

Hong Kong
103 Posts

Posted - 08/28/2014 :  22:26:51  Show Profile  Reply with Quote
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

Hong Kong
103 Posts

Posted - 08/28/2014 :  22:30:54  Show Profile  Reply with Quote
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 - 08/28/2014 :  22:31:01  Show Profile  Reply with Quote
c

Edited by - Chris_Kelley on 11/11/2014 19:29:48
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000