store result in text file from a query run in job

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 Administration

store result in text file from a query run in job

Author

Topic

e087578
Starting Member

1 Post

Posted - 2009-12-10 : 13:04:08

Is there way to execute two queries and store results in a text file or two different text files? I am sure I can store two select statements in a stored procedure and execute that SP in a job.

sathiesh2005
Yak Posting Veteran

85 Posts

Posted - 2009-12-11 : 02:19:11

Yes you can.

the below procedure will store the results of the two select statement in two text files.

CREATE PROCEDURE PROC_TXTFile
AS
BEGIN
DECLARE @SQL varchar(2000) ,@bcpCommand varchar(5000)
-- first select
SET @sql='SELECT Query here...'
SET @bcpCommand = 'BCP "'+@SQL+' " queryout '
SET @bcpCommand = @bcpCommand + ''C:\file.txt''+ ' -w -r "" -S servername -U Login -P PWD'
EXEC master..xp_cmdshell @bcpCommand
-- second select
SET @sql='SELECT Query here...'
SET @bcpCommand = 'BCP "'+@SQL+' " queryout '
SET @bcpCommand = @bcpCommand + ''C:\file.txt''+ ' -w -r "" -S servername -U Login -P PWD'
EXEC master..xp_cmdshell @bcpCommand
END

Regards,
Sathieshkumar. R

behrman
Yak Posting Veteran

76 Posts

Posted - 2009-12-15 : 14:48:34

There isn't a direct method. Two possible workarounds are OSQL and
BCP. I tend to use BCP. BCP only lets you output from a permanent
table, though.

I would create a table called "CACHE" where one of the columns is "IP"
and the other cols match the SELECT query. Then store the output into
CACHE along with the user's IP. Then run BCP to output from CACHE.

RAQ Report: Web-based Excel-like Java reporting tool

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources