artful at rogers.com
artful at rogers.com
Wed Jul 19 08:12:57 CDT 2006
I'm following this thread a bit late, and I'm not an Informix user, but now I'm curious. Doesn't Informix support the LIKE predicate? Or are your search requirements more complicated than things such as "LIKE %Mark Matte%"? Arthur ----- Original Message ---- From: Robert L. Stewart <rl_stewart at highstream.net> To: dba-sqlserver at databaseadvisors.com Cc: markamatte at hotmail.com Sent: Tuesday, July 18, 2006 1:27:35 PM Subject: Re: [dba-SQLServer] Get my data Informix-SQLServer Mark, In your DTS package, go to the last tab, Options, of your transform. Set the insert batch size to 100 and set the always commit last batch. This will allow you to commit each 100 records. It runs a little slower, but you will loose less records that way. If you have an alpha field like customer name, you could also set up a package for each letter of the alphabet and run all 27 of them at once based on the first letter of the company name. The 27th one being any that did not start with a letter. Robert L. Stewart The Dyson Group International Software for the Non-profit Enterprise Expanding your Sphere of Knowledge Quoting dba-sqlserver-request at databaseadvisors.com: > Date: Mon, 17 Jul 2006 20:28:28 +0000 > From: "Mark A Matte" <markamatte at hotmail.com> > Subject: [dba-SQLServer] Get my data Informix-SQLServer > To: dba-sqlserver at databaseadvisors.com > Message-ID: <BAY121-F733473F1B811CE16485A8D2620 at phx.gbl> > Content-Type: text/plain; format=flowed > > Hello All, > > I have SQLServer 7 and I have a link to an Informix db via ODBC. There are > over 6 million rows. I don't have a 24hour connection( and what I do have > is slower than dirt). The only reason to move it to SQL is to do wildcards > searches on a text field. If I use the DTS wizard to pull this in...I'm not > sure it would run in the 10 hour window I have...and second...if I cancel > half way through...I don't get to keep the first half. > > When I was pulling this same information into access...I used a counter, ran > my SQL for ID 1 to 100,000, looped back to beginning, added 100,000 to my > criteria, and ran my SQL again. At anytime I lost my connection I only lost > the current 100K group it was pulling. > > I have a fairly strong background in SQL, Access, and VBA...but SQL Server > is new to me. Any suggestions would be great...so is solution above used in > Access a likely candidate for the scenario, and if so...where is SQLServer > would I create/execute it from (Stored Proc)?...and if not what should I do. > > Summary, I need to bring 6mil records from Informix to SQL to do wild card > searches I have an ODBC connection to Informix...and SQLServer is local. > > Many thanks for any ideas/direction. > > Thanks, > > Mark A. Matte _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com