tsql to bcp.exe format - 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 2005 Forums

Transact-SQL (2005)

tsql to bcp.exe format

Author

Topic

jtwork
Yak Posting Veteran

82 Posts

Posted - 2008-07-08 : 06:43:52

ive been using bcp.exe to output a table into a text doc and want to keep the same field lenghts as the table fields but the last field is only the length of the the len() not the whole field (char(40)). I guess i could pad the last field out with spaces but this isnt ideal way to get the outcome i want. Does anyone else have any ideas? i did look at using the format file with no joy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 06:46:26

didnt get that? what do you mean by length of the len()?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-08 : 07:20:08

Hi There.

I tried this...


CREATE TABLE test_out (
	[feild1] CHAR(10)
	, [field2] CHAR(40)
	)

INSERT INTO test_out VALUES ('asdasdasd','sdddddddddddddddddddddddddddddasd')
INSERT INTO test_out VALUES ('asdasdasd','sddddddddddddddddddddasd')

With a bcp string like...

bcp <mydb>..test_out OUT dump.doc -S<dbserver> -Usa -P<thepassword>

It asked me for the length of the columns at their defaults (10 and 40) I specified a terminator of "," and then "\n"

The output file (dump.doc) has been padded to 10 and 40 characters. example..


asdasdasd ,sdddddddddddddddddddddddddddddasd       
asdasdasd ,sddddddddddddddddddddasd

IF you select my output above you will see that bcp has padded the columns. Does this help you at all?

if I save a config file it looks like this..


8.0
2
1       SQLCHAR       0       10      ","                       1     feild1     SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       40      "\r\n"                    2     field2     SQL_Latin1_General_CP1_CI_AS

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources