|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
does Ado.Net 2.0 have bulk insert functionality?I have to read bulk data from a non RDBMS to Sql Server 2k. I tried using Ado.Net with dataAdapter.Fill, where the app reads in 500,000 records to a dataTable in memory and then applies dAdapt.Fill to fill a table in sql Server 2k. This hung the server up to the point where I had to ctrl-alt-delete/close the program. So, for my next trick, I was reading the data into a bunch of delimited text files and looping through this set of textfiles using a DTS package converted from vb6 to vb.net. This is working except that after I finish looping through all the text files (where I invoke the DTS package for each text file) I get a nasty error message that some memory couldn't be read. I have been circumventing this error message with a SendKeys{Enter} to get rid of the error message at the end of the procedure. This is micky mouse and doesn't work 100% of the time. So, does anyone know if Ado.Net 2.0 have bulk insert that can perform data transfer like DTS or BCP? If yes, is ADO.Net 2.0 contained in VS.Net2005? If not, where can I get it? Thanks, Rich Try http://msdn.microsoft.com/vs2005 (still in beta)
You could try SQLXML : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_7pv0.asp Patrice -- Show quote"Rich" <anonym***@discussions.microsoft.com> a écrit dans le message de news:09aa01c4dd46$6bcc0b00$a601280a@phx.gbl... > Hello, > > I have to read bulk data from a non RDBMS to Sql Server > 2k. I tried using Ado.Net with dataAdapter.Fill, where > the app reads in 500,000 records to a dataTable in memory > and then applies dAdapt.Fill to fill a table in sql Server > 2k. This hung the server up to the point where I had to > ctrl-alt-delete/close the program. > > So, for my next trick, I was reading the data into a bunch > of delimited text files and looping through this set of > textfiles using a DTS package converted from vb6 to > vb.net. This is working except that after I finish > looping through all the text files (where I invoke the DTS > package for each text file) I get a nasty error message > that some memory couldn't be read. I have been > circumventing this error message with a SendKeys{Enter} to > get rid of the error message at the end of the procedure. > This is micky mouse and doesn't work 100% of the time. > So, does anyone know if Ado.Net 2.0 have bulk insert that > can perform data transfer like DTS or BCP? > > If yes, is ADO.Net 2.0 contained in VS.Net2005? If not, > where can I get it? > > Thanks, > Rich Thanks for the link. I think xml might be a little too
bulky for the volume of data I need to read and write. I just hope that ADO.Net2 comes through with the DTS/SqlBulkCopy feature. Does ADO.Net2 come with VS.Net2005? Thanks, Rich >-----Original Message----- url=/library/en-us/sqlxml3/htm/bulkload_7pv0.asp>Try http://msdn.microsoft.com/vs2005 (still in beta) > >You could try SQLXML : >http://msdn.microsoft.com/library/default.asp? > le message de>Patrice > >-- > >"Rich" <anonym***@discussions.microsoft.com> a écrit dans Show quote >news:09aa01c4dd46$6bcc0b00$a601280a@phx.gbl... >> Hello, >> >> I have to read bulk data from a non RDBMS to Sql Server >> 2k. I tried using Ado.Net with dataAdapter.Fill, where >> the app reads in 500,000 records to a dataTable in memory >> and then applies dAdapt.Fill to fill a table in sql Server >> 2k. This hung the server up to the point where I had to >> ctrl-alt-delete/close the program. >> >> So, for my next trick, I was reading the data into a bunch >> of delimited text files and looping through this set of >> textfiles using a DTS package converted from vb6 to >> vb.net. This is working except that after I finish >> looping through all the text files (where I invoke the DTS >> package for each text file) I get a nasty error message >> that some memory couldn't be read. I have been >> circumventing this error message with a SendKeys{Enter} to >> get rid of the error message at the end of the procedure. >> This is micky mouse and doesn't work 100% of the time. >> So, does anyone know if Ado.Net 2.0 have bulk insert that >> can perform data transfer like DTS or BCP? >> >> If yes, is ADO.Net 2.0 contained in VS.Net2005? If not, >> where can I get it? >> >> Thanks, >> Rich > > >. > Bulk ops (one way or another are the way to go in your case). Yes VS 2005
(drop the .NET) comes with ADO.NET 2.0. You can use BCP/DTS today from code. Try SQL DMO to activate it or use it from a batch. -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ "Rich" <anonym***@discussions.microsoft.com> wrote in message Thanks for the link. I think xml might be a little toonews:0ac801c4dd6d$12c412b0$a601280a@phx.gbl... bulky for the volume of data I need to read and write. I just hope that ADO.Net2 comes through with the DTS/SqlBulkCopy feature. Does ADO.Net2 come with VS.Net2005? Thanks, Rich >-----Original Message----- url=/library/en-us/sqlxml3/htm/bulkload_7pv0.asp>Try http://msdn.microsoft.com/vs2005 (still in beta) > >You could try SQLXML : >http://msdn.microsoft.com/library/default.asp? > le message de>Patrice > >-- > >"Rich" <anonym***@discussions.microsoft.com> a écrit dans Show quote >news:09aa01c4dd46$6bcc0b00$a601280a@phx.gbl... >> Hello, >> >> I have to read bulk data from a non RDBMS to Sql Server >> 2k. I tried using Ado.Net with dataAdapter.Fill, where >> the app reads in 500,000 records to a dataTable in memory >> and then applies dAdapt.Fill to fill a table in sql Server >> 2k. This hung the server up to the point where I had to >> ctrl-alt-delete/close the program. >> >> So, for my next trick, I was reading the data into a bunch >> of delimited text files and looping through this set of >> textfiles using a DTS package converted from vb6 to >> vb.net. This is working except that after I finish >> looping through all the text files (where I invoke the DTS >> package for each text file) I get a nasty error message >> that some memory couldn't be read. I have been >> circumventing this error message with a SendKeys{Enter} to >> get rid of the error message at the end of the procedure. >> This is micky mouse and doesn't work 100% of the time. >> So, does anyone know if Ado.Net 2.0 have bulk insert that >> can perform data transfer like DTS or BCP? >> >> If yes, is ADO.Net 2.0 contained in VS.Net2005? If not, >> where can I get it? >> >> Thanks, >> Rich > > >. > Yes, I converted a vb6 dts package to vb.net using the
convert wizard. My problem is that I have 4 packages and I loop through them using an interface constuct. At the end of all the looping I am getting a nasty error message "unable to read memory at location ...". This is why I am hoping that ADO.Net 2.0 has its own sqlbulkcopy class. I think the problem I am having is that I converted unmanaged code to vb.net, and there is one (or maybe more) obscure thing that hasn't been accounted for (I had to fix quite a few things with CType(...) after the conversion and had to tweak a few lines to make the packages work in a loop - like closing the package, re- opening the same package, stuff that wasn't included in the original DTS module. >-----Original Message----- case). Yes VS 2005 >Bulk ops (one way or another are the way to go in your >(drop the .NET) comes with ADO.NET 2.0. activate it or use it >You can use BCP/DTS today from code. Try SQL DMO to >from a batch. confers no rights.> >-- >____________________________________ >William (Bill) Vaughn >Author, Mentor, Consultant >Microsoft MVP >www.betav.com >Please reply only to the newsgroup so that others can benefit. >This posting is provided "AS IS" with no warranties, and Show quote >__________________________________ > >"Rich" <anonym***@discussions.microsoft.com> wrote in message >news:0ac801c4dd6d$12c412b0$a601280a@phx.gbl... >Thanks for the link. I think xml might be a little too >bulky for the volume of data I need to read and write. I >just hope that ADO.Net2 comes through with the >DTS/SqlBulkCopy feature. Does ADO.Net2 come with >VS.Net2005? > >Thanks, >Rich > >>-----Original Message----- >>Try http://msdn.microsoft.com/vs2005 (still in beta) >> >>You could try SQLXML : >>http://msdn.microsoft.com/library/default.asp? >url=/library/en-us/sqlxml3/htm/bulkload_7pv0.asp >> >>Patrice >> >>-- >> >>"Rich" <anonym***@discussions.microsoft.com> a écrit dans >le message de >>news:09aa01c4dd46$6bcc0b00$a601280a@phx.gbl... >>> Hello, >>> >>> I have to read bulk data from a non RDBMS to Sql Server >>> 2k. I tried using Ado.Net with dataAdapter.Fill, where >>> the app reads in 500,000 records to a dataTable in >memory >>> and then applies dAdapt.Fill to fill a table in sql >Server >>> 2k. This hung the server up to the point where I had to >>> ctrl-alt-delete/close the program. >>> >>> So, for my next trick, I was reading the data into a >bunch >>> of delimited text files and looping through this set of >>> textfiles using a DTS package converted from vb6 to >>> vb.net. This is working except that after I finish >>> looping through all the text files (where I invoke the >DTS >>> package for each text file) I get a nasty error message >>> that some memory couldn't be read. I have been >>> circumventing this error message with a SendKeys{Enter} >to >>> get rid of the error message at the end of the >procedure. >>> This is micky mouse and doesn't work 100% of the time. >>> So, does anyone know if Ado.Net 2.0 have bulk insert >that >>> can perform data transfer like DTS or BCP? >>> >>> If yes, is ADO.Net 2.0 contained in VS.Net2005? If not, >>> where can I get it? >>> >>> Thanks, >>> Rich >> >> >>. >> > > >. > Rich,
The ADO.NET team at Microsoft has really done wonders in 2.0. Not only are the datatable/dataset improved a lot, so you wouldn't get or atleast get lesser of the problems you describe below - a lot of operations in general are a lot more faster. Not to mention - the usability features they have added. 1/2 a million rows in a datatable is probably not the right use for that object anyway. You might want to consider an alternate mechanism to store that much data in memory. You can pin a table in memory - inside sql server - which albeit isn't in a datatable, but might get you where you are trying to get. ADO.NET 2.0 amongst it's many many new fantastic features introduces a new class called SqlBulkCopy. Here is a comparison of DTS/SqlBulkCopy and old fashioned 1.1 style updates - http://www.thedatafarm.com/blog/PermaLink.aspx?guid=15cf7a6c-d3d9-4633-8f69-6bbf62a4d33e As you can see, ADO.NET 2.0 / SqlBulkCopy will let you do what you are trying to do. - Sahil Malik http://dotnetjunkies.com/weblog/sahilmalik Show quote "Rich" <anonym***@discussions.microsoft.com> wrote in message news:09aa01c4dd46$6bcc0b00$a601280a@phx.gbl... > Hello, > > I have to read bulk data from a non RDBMS to Sql Server > 2k. I tried using Ado.Net with dataAdapter.Fill, where > the app reads in 500,000 records to a dataTable in memory > and then applies dAdapt.Fill to fill a table in sql Server > 2k. This hung the server up to the point where I had to > ctrl-alt-delete/close the program. > > So, for my next trick, I was reading the data into a bunch > of delimited text files and looping through this set of > textfiles using a DTS package converted from vb6 to > vb.net. This is working except that after I finish > looping through all the text files (where I invoke the DTS > package for each text file) I get a nasty error message > that some memory couldn't be read. I have been > circumventing this error message with a SendKeys{Enter} to > get rid of the error message at the end of the procedure. > This is micky mouse and doesn't work 100% of the time. > So, does anyone know if Ado.Net 2.0 have bulk insert that > can perform data transfer like DTS or BCP? > > If yes, is ADO.Net 2.0 contained in VS.Net2005? If not, > where can I get it? > > Thanks, > Rich Maybe I wasn't exactly loading 500,000 recs in the data
table, I think it was like 20,000 rec and then using dAdap.Fill and looping through the source data. But Fill was too slow. So I did the same technique using the delimited text files and DTS. I am thinking that the datatable object would be the structure of choice to read 20,000 recs into so that I could use the DTS/SqlBulkCopy functionality of Ado.Net2 and loop through that. Can a datatable structure handle 20,000 recs (160 fields per rec). If not maybe I could just load 10,000 recs at a time. Or should I stick with the text files? Can DTS/SqlBulkCopy read delimited text files like regular DTS? I already have a beta version of VS.Net2005, but I have not tried it yet (or loaded it yet). My company will pay for the Enterprise version when it comes out of beta. I can't wait! Thanks for your reply Rich >-----Original Message----- 2.0. Not only are>Rich, > >The ADO.NET team at Microsoft has really done wonders in >the datatable/dataset improved a lot, so you wouldn't get or atleast get>lesser of the problems you describe below - a lot of operations in general>are a lot more faster. Not to mention - the usability features they have>added. 1/2 a million rows in a datatable is probably not the right use for>that object anyway. You might want to consider an alternate mechanism to>store that much data in memory. You can pin a table in memory - inside sql>server - which albeit isn't in a datatable, but might get you where you are>trying to get. introduces a new> >ADO.NET 2.0 amongst it's many many new fantastic features >class called SqlBulkCopy. Here is a comparison of DTS/SqlBulkCopy and oldguid=15cf7a6c-d3d9-4633-8f69-6bbf62a4d33e > what you are>As you can see, ADO.NET 2.0 / SqlBulkCopy will let you do Show quote >trying to do. > >- Sahil Malik >http://dotnetjunkies.com/weblog/sahilmalik > > > > > >"Rich" <anonym***@discussions.microsoft.com> wrote in message >news:09aa01c4dd46$6bcc0b00$a601280a@phx.gbl... >> Hello, >> >> I have to read bulk data from a non RDBMS to Sql Server >> 2k. I tried using Ado.Net with dataAdapter.Fill, where >> the app reads in 500,000 records to a dataTable in memory >> and then applies dAdapt.Fill to fill a table in sql Server >> 2k. This hung the server up to the point where I had to >> ctrl-alt-delete/close the program. >> >> So, for my next trick, I was reading the data into a bunch >> of delimited text files and looping through this set of >> textfiles using a DTS package converted from vb6 to >> vb.net. This is working except that after I finish >> looping through all the text files (where I invoke the DTS >> package for each text file) I get a nasty error message >> that some memory couldn't be read. I have been >> circumventing this error message with a SendKeys{Enter} to >> get rid of the error message at the end of the procedure. >> This is micky mouse and doesn't work 100% of the time. >> So, does anyone know if Ado.Net 2.0 have bulk insert that >> can perform data transfer like DTS or BCP? >> >> If yes, is ADO.Net 2.0 contained in VS.Net2005? If not, >> where can I get it? >> >> Thanks, >> Rich > > >. >  |
|||||||||||||||||||||||
Other interesting topics