bcp row count - 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 row count

Author

Topic

jstone
Starting Member

2 Posts

Posted - 2004-01-29 : 12:27:44

Why do I get a different row count between bcp and SQL Server when I output records from a very large table (400 million rows)?

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-29 : 13:09:52

ahhh the bcp fails?

400 million rows...how long does that take?

Do you audit the bcp?

Try something like


	DELETE FROM XLAT_BCP_Results

		Select @FilePathAndName = @FilePath + '\' + @File_Name
		SET @cmd = 'bcp ' + @db_name + '..WRK_' + SUBSTRING(@File_Name,1,CHARINDEX('.',@File_Name)-1) + ' in ' 
			+ @FilePathAndName
			+ ' -f' +   'd:\Data\Tax\'  + SUBSTRING(@File_Name,1,CHARINDEX('.',@File_Name)-1) + '.fmt '
			+ ' -S' + @@servername + ' -U[user] -P[password]'

		SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''''

SELECT @Command_String

		INSERT INTO XLAT_BCP_Results(Col1) Exec(@Command_String)


SELECT 'INSERT INTO XLAT: ' + CONVERT(varchar(15),@@ROWCOUNT)
	
SELECT * FROM XLAT_BCP_Results

		IF EXISTS (SELECT * FROM XLAT_BCP_Results WHERE Col1 LIKE '%Error%')
			BEGIN
				SELECT @Error_Loc = 7
				SELECT @Error_Message = 'Problem With bcp.  The file did not load'
				SELECT @Error_Type = 50002
	  			GOTO Load_XLAT_sp_Error
			END

Brett

8-)

jstone
Starting Member

2 Posts

Posted - 2004-01-29 : 14:12:36

Hi Brett,

Thanks for the reply.

No, the bcp didn't fail. It worked well once I figured out how to use it properly, i.e. using the -n switch on the export from SQL 2K, and -n on the import to SQL 2K. The format file I attempted to use did not work properly. Hmmm...

For approximately 460 million rows, it took almost 3 hours, but that's much faster than DTS which probably would have taken days.

Unfortunately I did not audit the bcp export/import. But thanks for the routine.

One thing I did find out is that if I do a [SELECT COUNT(*) FROM SourceTable] and compare it to [SELECT COUNT(*) DestinationTable] the table have the same rowcount. However if look at the properties of the tables in Enterprise Manager they have different row counts. Or if I do a [sp_spaceused Tablename] the row counts are different. What's up with that???

Jim :)

MuadDBA

628 Posts

Posted - 2004-01-29 : 15:42:33

Betcha dollars to donuts that the old table was the one with incorrect info. The system tables probably contain outdated information about it. If you do dbcc newalloc it could bring them up to snuff, but if you've already got the counts, it's probably not worth it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources