bcp problem - 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

SSIS and Import/Export (2005)

bcp problem

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-25 : 14:22:08

I'm not having a syntax problem, I don't think, but the following is telling 0 rows copied, but I do know the file contains data...anyone have any ideas?

bcp "Privacy_OHM.[dbo].[Dat_up_log]" in D:\DATA\OHM\T0000001.log -T -S<servername> -fD:\DATA\OHM\Dat_log_In.fmt

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-25 : 15:42:42

Could you post the format file? Are you able to simplify the issue for us, meaning recreate the problem with say 2 rows of data and just a few columns?

I've never used double quotes around the DatabaseName..ObjectName.

Does it work if you use the first row, last row option of bcp (to load a couple of specific rows)?

Tara Kizer

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-25 : 15:47:01

it was the format file

The redirected out put is essentially 1 column

The table has 3 columns and I forgot to "zero out" the columns in the format file (date and tablename)

Thanks

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-26 : 09:47:22

EDIT: NEVER MIND...good lord..forest for the trees

It's funny, bbut getting pulled in 9 directions at once can cause you to los your mind...in any case, this is the format file...



7.0 
3
1	SQLCHAR	0	4000	"\r\n"	1	log_message
2	SQLCHAR	0	300	"\r\n"	0	TableName
3	SQLCHAR	0	8	"\r\n"	0	Date_Time

And this is the ftp redirected output

quote:

ftp> Connected to ISOF1.prudential.com.

open <mainframe>

220-FTPD1 IBM FTP CS V1R7 at <mainframe>, 15:50:44 on 2007-01-25.
220 Connection will close if idle for more than 5 minutes.
User (ISOF1.prudential.com:(none)):
331 Send password please.

230 X002548 is logged on. Working directory is "BX87BB.".
ftp> delete 'BXRL94.OHM.D070125.T0000001'
250 BXRL94.OHM.D070125.T0000001 deleted.
ftp> quote site cyl pri=1 sec=1 lrecl=5000 blksize=0 recfm=fb retpd=365
200 SITE command was accepted
ftp> put D:\DATA\OHM\DATA\T0000001.dat 'BXRL94.OHM.D070125.T0000001'
200 Port request OK.
125 Storing data set BXRL94.OHM.D070125.T0000001
250 Transfer completed successfully.
ftp: 12735 bytes sent in 0.00Seconds 12735000.00Kbytes/sec.

ftp> quit
221 Quit command received. Goodbye.

And this is what get's loaded

quote:

log_message C:\WINDOWS\system32>bcp "SELECT * FROM OHM_Prod.[OHMAdmin].[ABSENTEE]" queryout D:\Data\OHM\DATA\T0000001.dat
-T -SPAERSCBVD0014 -fD:\Data\OHM\FORMAT\OHMAdmin_ABSENTEE.fmt
Starting copy...
5 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 15

I'm baffled as to why the entire redirected output didn't get loaded?

I think I went this route because the EXECUTEE of xp_cmdshell won't insert the rows doing a INSERT INTO Log EXEC xp_cmdsehll @cmd because you can't capture the bcp output

In any case, what does get's loaded is enough...so if I ddon't see successfuly, then I stop processing

Any ideas why to doesn't fully load?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-26 : 11:41:43

Because it's SQL Server 7.0!

I think the problem is with your format file. I don't think your column delimiter is correct. Isn't \r\n your row terminator?

Tara Kizer

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-26 : 11:55:15

Yes and yes

But you can use a 7.0 card with 2000

And it's easier to generate the format files with code if it's 7.

Do you know how to get the collations for every column from a view or the catalog?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-26 : 12:06:51

I'm not sure about that. I rarely use a format file as my files are typically simple, such as tab or comma delimited with no text qualifiers.

Tara Kizer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-26 : 12:24:45

quote:
Originally posted by X002548

Yes and yes

But you can use a 7.0 card with 2000

And it's easier to generate the format files with code if it's 7.

Do you know how to get the collations for every column from a view or the catalog?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Is this what you are looking for?


select
	COLUMN_NAME,
	COLLATION_NAME
from
	PUBS.INFORMATION_SCHEMA.COLUMNS
where
	TABLE_NAME = 'AUTHORS'
order by
	ORDINAL_POSITION

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources