Hi Greg, I am out of own and don't have the connection string right in front of me for using ActiveRecord without Rails but can get it to you tomorrow. I also have code for DBI and that is closer to the metal - there are plenty of examples listed for using DBI, many for Perl but the syntax is close enough.
But I suspect with that volume of data you should look into creating an SSIS package if you have the tools available and call the SSIS package from using something like: system(start dtsx.exe pathnamepacakagename) This solution can be a coupe order of magnitudes faster than ActiveRecord or DBI. I don't know who is crafting the SQL but thinking in sets and using the new EXCEPT clause and PIVOT clause can help a lot. Good Luck, Carl ---- Greg Willits <[email protected]> wrote: > Thx. Had a look at Sequel. Pretty much any ORM is going to be too much > extra bulk (but I think I might like it for when a light ORM would be > handy). > > My code needs to do a raw SQL query (it will have been hand crafed by > people other than me to pull data out of complex schemas) upon which > the records (commonly hundreds of thousands up to a million of them, > sometimes more) will be immediately converted to CSV/tab file exports. > And each system will have to do that about 8-10 times per job. > > So the more bare-metal the solution the better. > > I suspect my troubles are mostly not understanding the correct > connectivity & authentication through the MS layers. > > -- > def gw > lives_at 'www.gregwillits.ws' > end > > > On Feb 15, 2010, at 9:27 PM, Matt Aimonetti wrote: > > > I'm not on Windows and I don't use SQL server, but ActiveRecord has > > an adapter for MS SQL Server, try something like: > > > > gem install activerecord activerecord-sqlserver-adapter > > > > irb --simple-prompt > > require 'active_record' > > ActiveRecord::Base.establish_connection( > > :adapter => "sqlserver", > > :host => ".\SQLEXPRESS", > > :database => "YourDB", > > :username => "me", > > :password => "topSekrit" > > ) > > > > ActiveRecord::Base.pluralize_table_names = false # if your tables > > are not following AR's conventions > > > > class Purchase < ActiveRecord::Base > > set_table_name 'msft_purchases' # only if your table names have > > nothing to do with the class name > > end > > > > Purchase.first > > > > > > You can also use http://sequel.rubyforge.org/ which is awesome > > (lower level) ORM supporting MS SQL Server. > > > > - Matt > > > > > > On Mon, Feb 15, 2010 at 8:48 PM, Greg Willits <[email protected]> wrote: > > I'm a Mac guy, but I need to write some Ruby (not Rails) that will > > run on a variety of Windows systems connected to a variety of SQL > > Server versions (probably just 2005 and 2008). > > > > I have VMWare Fusion, Windows XP (and Vista), Ruby 1.9.1, and SQL > > Server 2008 Express all installed. After a few hours of hair > > pulling, I managed to get SQL Server Express installed and a simple > > db/table created with a couple dummy records. > > > > Now, I need to figure out how to get at that data using bare Ruby so > > I can create a generic SQLServer adaptor (MySQL one works just fine) > > for my code. > > > > I'm surprised (sort of) at how little info there is for this. > > > > I have fiddled with these two techniques for some time with no joy. > > http://snippets.dzone.com/posts/show/3906 * > > http://waysysweb.com/qa/odbc.html > > > > * this would be my preferred way as it is much simpler, and I have > > to help dozens & dozens of half-skilled school district IT employees > > to implement this. > > > > Compared to MySQL, SQL Server seems really convoluted to me, and I'm > > drowning in MS's circular online help systems (not to mention the > > eyestrain-inducing layouts). > > > > Is there anyone here that has a similar dev setup (WinXP, Ruby 1.9, > > SQLServer Express) that can be my Q&A email buddy to get this > > figured out. If it turns out to be lengthy, I'll gladly pay for your > > time. > > > > > -- > def gw > lives_at 'www.gregwillits.ws' > end > > > > > -- > SD Ruby mailing list > [email protected] > http://groups.google.com/group/sdruby -- SD Ruby mailing list [email protected] http://groups.google.com/group/sdruby
