BCP and XP_CMDSHELL

[Rajesh Desabhatla]

Hi!
I have written a stored procedure using bcp utility and xp_cmdshell to pull data from a sql DB and write it onto a dat file.
My problem is
a)I want to write header and footer messages to the text file along with the writing of data onto the dat file.when i first write data onto the file and try to then write header/footer,it is replacing the data written.
Please help me out.Below is the code of the Stored Procedure.
CREATE PROCEDURE testraj AS

DECLARE @FileName varchar(200),
@bcpCommand varchar(3000)
begin

SET @FileName = 'D: est.dat'
SET @bcpCommand = 'bcp "select * from tbl_test" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -c -t -Usa -P -Ssql3server'
EXEC master..xp_cmdshell @bcpCommand

end

[ChrisH]

BCP is a highly optimised data transfer tool, so not really suited to advance formatting functions. Write the data to the text file and then use the DOS commands (executed with xp_cmdshell) to write your header, write the data into the new file and then write the footer:

ECHO ColHeader1 ColHeader2 >D:NewFile.txt
TYPE D:Test.dat >>D:NewFile.txt
ECHO ColFooter1 ColFooter2 >>D:NewFile.txt
DEL D:Test.dat

One more point: In the interests of security, I wouldn't advise posting your database sa password on the discussion board.

Regards,
ChrisH


------------
Rajesh Desabhatla at 3/7/2002 1:05:34 AM

Hi!
I have written a stored procedure using bcp utility and xp_cmdshell to pull data from a sql DB and write it onto a dat file.
My problem is
a)I want to write header and footer messages to the text file along with the writing of data onto the dat file.when i first write data onto the file and try to then write header/footer,it is replacing the data written.
Please help me out.Below is the code of the Stored Procedure.
CREATE PROCEDURE testraj AS

DECLARE @FileName varchar(200),
@bcpCommand varchar(3000)
begin

SET @FileName = 'D: est.dat'
SET @bcpCommand = 'bcp "select * from tbl_test" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -c -t -Usa -P -Ssql3server'
EXEC master..xp_cmdshell @bcpCommand

end

[ChrisH]

Doh! You didn't publish your password (unless it is the blank default) - sorry my error!


------------
ChrisH at 3/7/2002 3:58:38 AM

BCP is a highly optimised data transfer tool, so not really suited to advance formatting functions. Write the data to the text file and then use the DOS commands (executed with xp_cmdshell) to write your header, write the data into the new file and then write the footer:

ECHO ColHeader1 ColHeader2 >D:NewFile.txt
TYPE D:Test.dat >>D:NewFile.txt
ECHO ColFooter1 ColFooter2 >>D:NewFile.txt
DEL D:Test.dat

One more point: In the interests of security, I wouldn't advise posting your database sa password on the discussion board.

Regards,
ChrisH


------------
Rajesh Desabhatla at 3/7/2002 1:05:34 AM

Hi!
I have written a stored procedure using bcp utility and xp_cmdshell to pull data from a sql DB and write it onto a dat file.
My problem is
a)I want to write header and footer messages to the text file along with the writing of data onto the dat file.when i first write data onto the file and try to then write header/footer,it is replacing the data written.
Please help me out.Below is the code of the Stored Procedure.
CREATE PROCEDURE testraj AS

DECLARE @FileName varchar(200),
@bcpCommand varchar(3000)
begin

SET @FileName = 'D: est.dat'
SET @bcpCommand = 'bcp "select * from tbl_test" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -c -t -Usa -P -Ssql3server'
EXEC master..xp_cmdshell @bcpCommand

end

[Ceeba]

Hello All,
I am trying a Bulk Load/BCP from flat file (CSV) to a table.
For some reason it is putting some "garbage data". Please let me know what is the reason ?? Well the file(CSV) does have valid data.

BULK INSERT STG_Provider FROM '\MyserverMydirprovider.d'
WITH (
DATAFILETYPE = 'widenative',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '
',
FORMATFILE = '\MyserverMydirprovider.fmt',
FIRSTROW = 2,
KEEPIDENTITY,
KEEPNULLS,
TABLOCK
)

**************
bcp stg_provider in \MyserverMydirprovider.d -f \MyserverMydirprovider.fmt -F 2 -t , -r
-S Myserver -U "" -P "" -h TABLOCK

Thank you in advance for looking into it.

THX
LCV


------------
ChrisH at 3/7/2002 6:12:15 AM

Doh! You didn't publish your password (unless it is the blank default) - sorry my error!


------------
ChrisH at 3/7/2002 3:58:38 AM

BCP is a highly optimised data transfer tool, so not really suited to advance formatting functions. Write the data to the text file and then use the DOS commands (executed with xp_cmdshell) to write your header, write the data into the new file and then write the footer:

ECHO ColHeader1 ColHeader2 >D:NewFile.txt
TYPE D:Test.dat >>D:NewFile.txt
ECHO ColFooter1 ColFooter2 >>D:NewFile.txt
DEL D:Test.dat

One more point: In the interests of security, I wouldn't advise posting your database sa password on the discussion board.

Regards,
ChrisH


------------
Rajesh Desabhatla at 3/7/2002 1:05:34 AM

Hi!
I have written a stored procedure using bcp utility and xp_cmdshell to pull data from a sql DB and write it onto a dat file.
My problem is
a)I want to write header and footer messages to the text file along with the writing of data onto the dat file.when i first write data onto the file and try to then write header/footer,it is replacing the data written.
Please help me out.Below is the code of the Stored Procedure.
CREATE PROCEDURE testraj AS

DECLARE @FileName varchar(200),
@bcpCommand varchar(3000)
begin

SET @FileName = 'D: est.dat'
SET @bcpCommand = 'bcp "select * from tbl_test" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -c -t -Usa -P -Ssql3server'
EXEC master..xp_cmdshell @bcpCommand

end