problems in writing a query output to a file using xp_cmdshell

Question

Post reply

problems in writing a query output to a file using xp_cmdshell

pmiller

Ten Centuries

Points: 1190
More actions
October 10, 2007 at 9:45 am

#119032

I can run the following query but the output file doesn't get generated. Is there something wrong with me code ?
DECLARE @cmd VARCHAR(255)
set @cmd = 'OSQL -E -Q "Select filepath, filetree, filename from ccs.dbo.Work_DBA_Purge"
-O" >> T:\temp\myfile.txt"'
EXEC xp_cmdshell @cmd

Viewing 11 posts - 1 through 10 (of 10 total)

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

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 1

I would suggest using BCP for this. The syntax is in BOL

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 2

Try -

DECLARE @FileName varchar(100)

,@FileDirectory varchar(1000)

,@Cmd varchar(1000)

SET @FileDirectory = 'C:\TEMP'

SET @Cmd = 'echo OSQL -E -Q "Select filepath, filetree, filename from ccs.dbo.Work_DBA_Purge"-O>>' + @FileDirectory + '\TEST.txt'

EXEC master.dbo.xp_cmdshell @Cmd

Tommy

Follow @sqlscribe

pmiller Ten Centuries Points: 1190 More actions · Answer 3

Hi Tommy

I tried your modification and a file got created . No data juet the following

OSQL -E -Q "Select filepath, filetree, filename from ccs.dbo.Work_DBA_Purge"-O

This query will return 47 rows of data and I was hoping that's what would show up in this text file. Not the select cmd.

Any other ideas

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 4

Ah...misunderstood......I too would suggest BCP...this article might help -

http://www.sqlteam.com/article/osql-storing-result-of-a-stored-procedure-in-a-file

Tommy

Follow @sqlscribe

pmiller Ten Centuries Points: 1190 More actions · Answer 5

TommyB and Adam

I've been plugging along at trying to get the code to work and I'm at the stage were I believe I need somemore help. Here is the BCP step that I've created and I included the results.

DECLARE @FileName varchar(50),

@bcpCommand varchar(2100),

@Query varchar(2000)

SET @FileName = 't:\temp\test.txt'

SET @Query = 'bcp "Select filepath, filetree, filename from ccs.dbo.Work_DBA_Purge" queryout -c'

SET @bcpCommand = '@Query + @FileName + -t '

EXEC master..xp_cmdshell @bcpCommand

results

Invalid parameter(s)

QUERY { PROCESS | SESSION | TERMSERVER | USER }

NULL

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 6

DECLARE @FileName varchar(50),
@bcpCommand varchar(2100),
@Query varchar(2000)
SET @FileName = 't:\temp\test.txt'
SET @Query = 'bcp "Select filepath, filetree, filename from ccs.dbo.Work_DBA_Purge" queryout -c'
SET @bcpCommand = '@Query + @FileName + -t '[/u]
--I guess above statement should look like
SET @bcpCommand = @Query + @FileName + ' -t'
EXEC master..xp_cmdshell @bcpCommand

--Ramesh

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 7

How about using the -o argument to specify the output file?

DECLARE @cmd VARCHAR(255)

set @cmd = 'OSQL -E -Q "Select filepath, filetree, filename from ccs.dbo.Work_DBA_Purge" -O -o T:\temp\myfile.txt'

EXEC xp_cmdshell @cmd

pmiller Ten Centuries Points: 1190 More actions · Answer 8

I have resolved my problem. Thanks to everyone who contributed.

For those who might be interested here is the code that works.

use ccs

DECLARE @FileName varchar(150),

@bcpCommand varchar(1100),

@Query varchar(1000)

SET @FileName = 't:\temp\test.txt -c -T '

SET @Query = '"Select rtrim(filepath) + rtrim(filetree) from ccs.dbo.work_DBA_datasets_purge"'

SET @bcpCommand = 'bcp ' + @Query + ' queryout ' + @FileName

EXEC master..xp_cmdshell @bcpCommand

Thanks again

Johan Bijnens SSC Guru Points: 135368 More actions · Answer 9

Find out if your sqlserver service account has the needed privileges and the mapped drive for "T:\"

Try using UNC i.e. \\yourserver\yourshare\anotherpath\yourfile.ext

in stead of "t:\..."

With SQL2005 osql has been replaced with SQLCMD which has many enhancements.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

pmiller Ten Centuries Points: 1190 More actions · Answer 10

Good Point.

During the testing I used the T drive. Afterwards I changed the final destination on another server over the network and I made the changes as you have indicated.

Thanks for the additional info.