Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Replacing BCP with SQLBulkLoad Expand / Collapse
Author
Message
Posted Saturday, June 28, 2003 12:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 1:50 AM
Points: 27, Visits: 17
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/spopovski/replacingbcpwithsqlbulkload.asp


Post #13693
Posted Monday, July 21, 2003 1:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2003 12:00 AM
Points: 1, Visits: 1
This article assumes that XML is the way of the future, which is a false assumption.

XML has some significant problems.
1. Performance. I'm not talking about file size (and the resulting poor performance over networks), but about that fact that parsing XML is an intensive process. For one thing, you must read the entire data structure into memory because you must read to the closing tag. CSV has no such problems.
2. Interoperability. XML only works with new stuff and something that can parse XML. Sure it crosses platforms, but you'll find that CSV as a format is even more accepted.

Even one of the co-authors of the XML 1.0 standard has pointed out some problems with XML.

See
http://slashdot.org/article.pl?sid=03/03/18/0712248&mode=thread&tid=95&tid=156

That is not to say that XML doesn't have its place. Its place is in small applications that don't deal with much data, and/or have complicated data structures that need to be represented with XML.

My impression is that XML is touted as the wave of the future by an increasingly small number of people these days.




Post #68648
Posted Monday, July 21, 2003 4:31 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 9:01 AM
Points: 6,705, Visits: 1,680
Im not much of an XML fan either, but in many cases the extra overhead is worth what you get out of it. Using the DOM requires you to read the entire file (an awesomely slow process with large files, I watched a demo where the memory usage ticked upward...and upward...forever), but SAX and maybe the .Net reader are designed to be lighter. CSV's are ok, but it's not very rich. My favorite data transfer format is MS Access!

Andy
http://www.sqlservercentral.com/columnists/awarren/




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #68649
Posted Monday, July 21, 2003 8:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 17, 2003 12:00 AM
Points: 80, Visits: 1
Doesn't anyone translate these articles into proper English before posting them? The grammatical errors in this article drove me nuts.


Post #68650
Posted Monday, July 21, 2003 8:51 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 06, 2014 3:47 PM
Points: 164, Visits: 368
This article could have been much better if it was edited and proof-read before posting. I gave up trying to decipher the spelling mistakes and errors, but I think the idea for the article was good.

I didn't know SQLXML had this functionality, but I wouldn't trust the code samples given in the article because of the poor English.



Edited by - cppwiz on 07/21/2003 08:53:54 AM



Post #68651
Posted Monday, July 21, 2003 9:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:01 AM
Points: 2,980, Visits: 762
I got an error trying this. I 3 column table with hr, Source, and Description as the column headings:

hr
0x800401F3

Source
ODSOLE Extended Procedure

Description
Invalid class string

Thanks,
ThomBeaux



Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
Post #68652
Posted Monday, July 21, 2003 9:34 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, April 03, 2014 10:06 PM
Points: 6,621, Visits: 1,851
quote:
Inattention using master..xp_cmdshell can seriously endanger SQL Server security


This article rightfully points out that you tend to avoid xp_cmdshell for security reasons, but then goes and uses the sp_OA* procedures, which you tend to avoid for security and stability reasons. Therefore, I don't think the author can cite security as a reason for using SQLBulkLoad over bcp.



K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1


Edited by - bkelley on 07/21/2003 09:34:06 AM


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #68653
Posted Monday, July 21, 2003 11:27 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 9:01 AM
Points: 6,705, Visits: 1,680
My fault on the English - we haven't decided what to do just yet. Clearly if can't follow the article it's of no value, yet we don't have time to rewrite content that comes in from readers who use English as a second language.

Andy
http://www.sqlservercentral.com/columnists/awarren/




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #68654
Posted Monday, July 21, 2003 11:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 20, 2007 1:25 AM
Points: 28, Visits: 1
I think the BULK INSERT statement is a good substitute to bcp. You avoid teh use of xp_cmdshell and also you avoid the overhead of having each field around tags. I personally think the best data interchange format is a pipe or comma delimited file. The question of portability doesn't arise as even with an XML file you still have to match the various tags. That said, XML does seem interesting and until now I haven't been convinced that it is the right format for transfer of large volume of data




Post #68655
Posted Monday, July 21, 2003 12:22 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, April 03, 2014 10:06 PM
Points: 6,621, Visits: 1,851
quote:
I think the BULK INSERT statement is a good substitute to bcp.


From a performance perspective, it should be slightly faster. BULK INSERT runs in the memory context of SQL Server whereas bcp does not. Both of these would be faster than DTS and the described method here.


K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #68656
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse