How do I do this in ruby?

[kishor.gurtu]

Hi,

I am trying to write a script that downloads my web server statistics
that is stored in an online MS SQL Database to a local database for
further processing and then deletes the downloaded records (to save
space online). How do I do this safely so that I don't lose any records
due to network error - is there some kind of a distibuted transaction
coordinator that I can use in ruby?

 

[Robert Klemme]

Hi,

I am trying to write a script that downloads my web server statistics
that is stored in an online MS SQL Database to a local database for
further processing and then deletes the downloaded records (to save
space online). How do I do this safely so that I don't lose any
records due to network error - is there some kind of a distibuted
transaction coordinator that I can use in ruby?

That question is probably better answered in a SQL Server related news
group. This sounds like a typical application of replication / staging.
SQL Server does provide mechanisms for that. I'm sure you can even set a
trigger that deletes records after successful replication. TX
coordination can also be done with SQL Server. I can't help with the
details but I'm sure you'll find info in BOL (SQL Server Books Online).

Kind regards

robert

 

[Jeff Wood]

--------------070204060309020601040808
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I am trying to write a script that downloads my web server statistics
that is stored in an online MS SQL Database to a local database for
further processing and then deletes the downloaded records (to save
space online). How do I do this safely so that I don't lose any records
due to network error - is there some kind of a distibuted transaction
coordinator that I can use in ruby?

actually, the methodology really depends on whether you are using the
Windows version of Ruby or the *nix version ...

One option that will work either way is to build a simple DTS script
that MSSQL should run on a routine basis to export listings to a file (
CSV or XML ) ...

Then you can access that via any means you have to access the filesystem
... ( could be a samba mount of the filesystem, or ssh, or ftp, or
whatever ... ) then your script can parse the given file and dump into
the database ... of course if you're following that methodology,
there's really nothing that keeps you from building a DTS script that
would dump the data directly into a remote database ( anything you can
access via an ODBC driver ).

... Anyways, the other option would be to use Ruby under windows with
the compiled in ODBC drivers ... then access the MSSQL database and do
whatever you want ... This option is possible from *nix, but it's a bit
wierd since you need to access the MSSQL database using Sybase drivers (
since that's all MSSQL really uses for it's protocol, just a heavily
modified version of the Sybase protocol since MSSQL started life as just
a modified version of the Sybase server... )

Hope those options help you.

Me, I'd just write a DTS to do whatever you want ... you can schedule it
within MSSQL and then not have to worry about it. And it keeps you from
having to play in M$ land *too* much

j.

--------------070204060309020601040808--

 

[Craig Demyanovich]

... Anyways, the other option would be to use Ruby under windows
with the compiled in ODBC drivers ... then access the MSSQL
database and do whatever you want ... This option is possible from
*nix, but it's a bit wierd since you need to access the MSSQL
database using Sybase drivers ( since that's all MSSQL really uses
for it's protocol, just a heavily modified version of the Sybase
protocol since MSSQL started life as just a modified version of the
Sybase server... )

My team has just begun writing a few data manipulation scripts in
Ruby using ActiveRecord. We use it in ADO mode. To do so, we

* installed Ruby using the one-click Windows installer (http://
rubyinstaller.rubyforge.org/wiki/wiki.pl)
* updated RubyGems ( c:\> gem update --system )
* installed ActiveRecord ( c:\> gem install activerecord )
* followed the directions here (http://wiki.rubyonrails.com/rails/
pages/HowtoConnectToMicrosoftSQLServer) to set up ADO

The great thing about it is that we can issue raw SQL when needed,
and we can use the ORM features of ActiveRecord when it's beneficial
to do so.

Good luck,
Craig

 

[kishor.gurtu]

Thanks guys. This was a great help. Guess I will try using DTS - it was
an option I had ignored.

Kishor.