run a query and the result of this query must be taken to a text file

hi experts
What I need is to run a query and the result of this query must be taken to a text file without using DTS this has to be daily (through a JOB)

please help
enrique_aeoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

netovskyCommented:
he date columns can be joined by either of the below methods,  based on the data type defined for those columns,

1. If both the columns are of DATE type, your on condition in the join clause shall be,

Date(dateCol1) = Date(dateCol2)

2. If your date columns are of PLAINTEXT type , use the below on condition in your join clause,

STR_TO_DATE(dateCol1, '%d/%m/%Y') = STR_TO_DATE(dateCol2, '%d/%m/%Y')

We believe that the above helps. If is not what you are looking for, kindly elaborate us on your need.
enrique_aeoAuthor Commented:
recommended using BCP, you think?
 but I do not know that tool
khan_webguruCommented:
Hello Bro!


The are several methods of creating text files from sql server.
osql
bcp
redirection commands
sp_MakeWebTask
Com object calls

Remember that in all cases the path of the output file will be relative to the server - not to the client on which the application is running.

osql.
This example will just output master..sysobjects to the file c:\osqloutput.txt.

declare @cmd varchar(1000)
select @cmd = 'osql -U -P -S -Q"select * from master..sysobjects" -o"c:\osqloutput.txt" -w500'
exec master..xp_cmdshell @cmd

bcp
bcp very fast and easy to use for just dumping a table out - can be used against a view too.

master..xp_cmdshell 'bcp master..sysobjects out c:\file.bcp -S -U -P -c '

redirection commands
You will need to create the data to be output as in dynamic sql statements
The first command here creates or overwrites the file - the rest append to the file.
exec master..xp_cmdshell 'echo hello > c:\file.txt'
exec master..xp_cmdshell 'echo appended data >> c:\file.txt'
exec master..xp_cmdshell 'echo more data >> c:\file.txt'

will generate a file containing
hello
appended data
more data

sp_MakeWebTask
This is for creating html code from a query

Com object calls
Using sp_oa... system stored procedures and creating com objects or existing applications you can probably do whatevr you wish - but you should probably ask whether sql server is the right place to control this.


Regards,

Asif Ahmed Khan

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
enrique_aeoAuthor Commented:
and where I execute these commands?
master..xp_cmdshell 'bcp master..sysobjects out c:\file.bcp -S -U -P -c '

d.o.s.?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.