BCP UTILITY and XP_CMD SHELL - SQL Server Forums

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

Import/Export (DTS) and Replication (2000)

BCP UTILITY and XP_CMD SHELL

Author

Topic

desabhatla
Starting Member

7 Posts

Posted - 2002-03-07 : 02:20:31

hello,
I have a problem in using xp_cmdshell and bcp utility while writing data into a file.I am using the above utilities to export data from a sql server table to a dat file.
My problem is while writing into the text file i require to place some header text and footer text and in between should be the data from table.Please help me out on this.
I am able to successfully export the data onto the file but need to add the header/footer to the file.
My code is
CREATE PROCEDURE testraj AS

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

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

end

Pewzner
Starting Member

2 Posts

Posted - 2002-03-07 : 03:28:19

Try use something like this:

Exec master..xp_cmdshell 'echo "Some header text" >header.txt'
EXEC master..xp_cmdshell @bcpCommand
Exec master..xp_cmdshell 'echo "Some footer text" >>test.dat'
Exec master..xp_cmdshell 'copy header.txt+test.dat test.tmp'
Exec master..xp_cmdshell 'del header.txt,test.tmp'
Exec master..xp_cmdshell 'rename text.tmp text.dat'

it is dumb, but works :)

desabhatla
Starting Member

7 Posts

Posted - 2002-03-07 : 04:31:38

Pewzner,
This din't work.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-07 : 08:16:25

I think there were some typos in his code, but Pewzner's answer is the best way to do it.

What kind of header information do you need? Is it the column names of the query, or something else? The point is that whatever the header and footer information is, you write them out to separate files and then combine them with the "copy" command:

Exec master..xp_cmdshell 'copy header.txt+test.dat+footer.txt test.tmp'

I modified Pewzner's code a little, but this is the meat of the solution. The test.tmp file now contains the entire result you need. You can rename it or copy it as needed.

desabhatla
Starting Member

7 Posts

Posted - 2002-03-08 : 04:24:52

Hi Thanks for the suggestion. It worked perfectly well.
I got one more doubt related to the same thing. If you export data with DTS Wizard. We have an option called fixed field which will align the data into columns of equal widths. Similarly I want to align the data to its specified datatype length. for example email field is defined as varchar(80) then the data should padded with blanks if it is less than 80 characters. I am using bcp to export the data to ".dat" file is there any option in bcp to specify fixed column width. or else please suggest any work arround.

Our business requirement is like this. data from diff places (diff types of databases as well) will be ftp'd to a central location in a fixed ".dat" file fromat. the ".dat" format should be like this.

headet text indicating the location
Business1Product1
Bus2 Prod2
Busin3 Product3
footer text indicating no of rows,timestamp etc..

if you observe the above first row doesn't have any column delimiters.As we know the length of the column defined. The loader will look for those many chars.. and load it into the datawarehouse. The next two rows are padded with necessary blank spaces to ensure that the second column value start at the same point as second column of first row.

waiting for suggestions.

Thanks in advance.

Edited by - desabhatla on 03/08/2002 04:50:54

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-08 : 07:50:53

You have to do some testing with bcp format files, but I'm almost certain you can use them to generate fixed-length files. Books Online has a well-detailed example on the structure and usage of a "bcp format file", search for that term and you should find it (you may have to drill down a number of links, but it's in there)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources