appending to a file using xp_cmdshell??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

appending to a file using xp_cmdshell???

Author

Topic

swirl80
Starting Member

2 Posts

Posted - 2007-06-11 : 10:32:14

i'm tryin to create an extract similar to:

[RunTime]
Date=ASAP
Time=
AllowErrors=FALSE

[Task.1]
Module=Employee
Operation=AddUpdate
Server=Employee

[Data.1]
Employee,900,,1,1,,Support,,Tech,,,,,,,,,,,,,,90,90,9000,,WKSTN,
Employee,400,,1,1,,Manager,,Temp,,,,,,,,,,,,,,40,40,4000,,WKSTN,

where the data in red is static and info in green is taken from users table. i've tried using xp_cmdshell (eg. exec master..xp_cmdshell 'echo [RunTime] >> e:\testfile2.txt') to create the red info but can't get the queried results to output. I've tried using bcp but that keeps just over writing the file. Only other way i can think of is to create 2 files and append them to each other using DOS but i want to keep it all in SQL if possible.....

oh yea, 1 more thing, anyway i can create a carriage return using xp_cmdshell to???

thanks

Kristen
Test

22859 Posts

Posted - 2007-06-11 : 10:40:04

You could TYPE the files together, or COPY them:

TYPE MyHeaderFile.TXT >e:\testfile2.txt
TYPE MyData.TXT >>e:\testfile2.txt

COPY MyHeaderFile.TXT+MyData.TXT e:\testfile2.txt

watch out for EOF markers using COPY - there is a Binary Flag you can use if you have embedded binary stuff.

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-11 : 11:05:09

You can also add all the rows to a table with one string column and an identity (or sequence) then bcp the string column out ordering by the sequence.
Has the advantage that it is easy to develop as you just need to view the table and it gives you an image of the file in the database.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

swirl80
Starting Member

2 Posts

Posted - 2007-06-11 : 11:25:49

cheers for the help guys, gonna test both ways and see which will be quicker....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources