query results to text file - SQL Server Forums

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

query results to text file

Author

Topic

pmccann1
Posting Yak Master

107 Posts

Posted - 2007-05-09 : 06:02:19

i jave the following query

select *,substring(stafflog,15,11) as test into #t1 from dbo.Customers
where stafflog like '%armagh%'
go
select left(stafflog,4) as Staff,count(left(stafflog,4)) as Total from #t1
where cast(left(test,charindex(' ', test))as smalldatetime) = cast(convert(varchar(8),getdate()-1,1) as datetime)
group by left(stafflog,4)
go
select Title,Address1,Address2,Town,County,Postcode,TelephoneDay,TelephoneWork,TelephoneEvening,
MobileTelephoneNo,Contact,Mail,Telephone,Terms,StaffLog
from #t1
where cast(left(test,charindex(' ', test))as smalldatetime) = cast(convert(varchar(8),getdate()-1,1) as datetime)
go
drop table #t1
go

i need this query to be scheduled to run at a certain time every day and the results to be put in a text file.

is there an easy way to do this or what should i be looking at doing to get it to work

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-05-09 : 06:08:45

Set up a SQL Agent job to run on an appropriate schedule. Get it to execute a BCP (Bulk Copy Program) script to do the export to a text file. (Both of these can be looked up in BOL).
I take it you're not in control of how this data is stored, as it looks like you're having to an awful lot of work to pull this out due to inapproriate data types, etc.

Mark