program save file

[beechill4]

hi,

i have a t-sql script that i want to program to output as a text file when it is run.

i know i can GO TO QUERY, RESULTS TO FILE, but i'd prefer to include the destination in the script itself.

is this possible?

Thanks

 

[NoCoolHandle]

Tools -> Options -> Results
Select "Results to File"
Click OK

 

[NoCoolHandle]

Second thoughts. (and a reread - i need to be more carefull the first time round)

Not really

BCP for options, but it doesn't have the flexability of QA

 

[beechill4]

ok.

surprising though that this cannot be programmed. is there nothing like spool in oracle to do this?

 

[NoCoolHandle]

what is oracle?

 

[vongrunt]

Matrix thing, I guess .

bcp can do that, and DTS and...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[beechill4]

i also have this script as an execute sql task.

how do i save it as a text file?

 

[MastermindSQL]

Here is one solution:

--Create a work table
CREATE TABLE Temp (Col1 varchar(200), Col2 varchar(200))

INSERT INTO Temp
(
Col1,
Col2
)
SELECT Col1, Col2 /*Your main query goes here*/
FROM MyTable

--BCP out the query result saved in Work table
EXEC Master..XP_CmdShell 'bcp "DBName.[Owner].[Temp]" out "c:\MyFile.txt" -c -U UserName -P Password'

--Cleanup
DROP TABLE Temp

After "out" in BCP command you can specify the output location.

 

[beechill4]

thanks mastermind,

the problem wit your solution is that i have about 8 pages of text in the output of my query/script