Syntax to write to file

Question

Post reply

Syntax to write to file

Helen McCallum

Mr or Mrs. 500

Points: 583
More actions
February 9, 2004 at 1:07 pm

#107048

I know this is simple but I'm a newbie.
What should I write at the end of a query to get it to create a file? I want to schedule a query to run monthly and so can't use the output to file option in Query Analyser.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

run the scirpt from isql/oswl in the command line. /? will give the options, which to a file is one.

If you need to schedule this, SQLAgnet can run this as a CommandExec type job step.

garylzon Old Hand Points: 377 More actions · Answer 2

Additionally, you can use an ADO recordset object combined with a file system object in an ActiveX script in DTS, which you can then schedule, to write the result of your query to a file.

GVernon Valued Member Points: 64 More actions · Answer 3

Another suggestion that I have used in the past is to put your query into a Store Procedure with a BCP statement in a variable and run it with a master..xp_cmdshell (see code snipit below). Then execute the procedure with the SQLAgent.

DECLARE @FileName as varchar(10)

DECLARE @cmd nvarchar(400)

.

select @cmd = 'bcp "##temp1" out "\\'+@FileName+'\batInstall\'+@FileName+'.csv" -T -c -t,'

exec master..xp_cmdshell @cmd

drop table ##temp1

willhaney Mr or Mrs. 500 Points: 596 More actions · Answer 4

You could also use the built in ETL (Extraction, Transformation, & Loading) functionality Microsoft offers with a DTS (Data Transformation Services) package. Once the package has been built (one Microsoft OLE DB Provider for SQL Server, one Text File Destination and one Transform Data Task) you can schedule it to run in the Jobs.

achavez Old Hand Points: 359 More actions · Answer 5

I've been looking for this too...but I remember seeing a SELECT...INTO that allowed output to a text file. Hallucination?

-------
at us, very deafly, a most stares
collosal hoax of clocks and calendars

eecummings

Helen McCallum Mr or Mrs. 500 Points: 583 More actions · Answer 6

Hmmm! Me too. I'm sure we did something simple in SQL Query Manager such as OUPUT TO DISK OR INTO or something.

I'm sure the other answers work but I was looking for a simple answer

gljjr SSChampion Points: 10080 More actions · Answer 7

Since you need to do this monthly the best thing to do would be to set up a DTS package and then have a SQL Job run the package once a month or do as Steve suggested and have the SQL Job start osql in an operating system command and use the switch to use an output file.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.