I second the comment that this should probably be done via an SSIS package which is invoked later via system(). Not only would it be easier to maintain in the long term (your SQL server DBA's are probably already familiar with it) but it will perform better, too.
SF On Tue, Feb 16, 2010 at 8:52 PM, <[email protected]> wrote: > 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 > -- Joan Crawford<http://www.brainyquote.com/quotes/authors/j/joan_crawford.html> - "I, Joan Crawford, I believe in the dollar. Everything I earn, I spend." -- SD Ruby mailing list [email protected] http://groups.google.com/group/sdruby
