MSSQL Access under Linux

[Shawn Tayler]

On Mon, 26 Mar 2007 12:59:33 -0700 Bill Campbell <linux-sxs at celestial.com>
exclaimed:

> On Mon, Mar 26, 2007, Matthew Carpenter wrote:
> 
> >Doesn't OOo or some other project actually have a MSAccess-equivalent
> >for MySQL?
> 
> There are a variety of database connectors for OpenOffice.org.
> Generally speaking I've found the JDBC connectors the least
> trouble and most reliable (which makes sensce given the Sun/Java
> heritage of OO.org).  Install the approprate JAR files, Add them
> to OO.org's CLASSPATH, figure out the magic incantations under
> File->New->Database to connect to the database, and things work
> well.
> 
> Using the MySQL connector the parameters are:
> 
> URL     mysql://hostname/database
> driver  com.mysql.jdbc.Driver
> 
> Being that M$SQL is a Microsoft product, I suspect that it works
> best using ODBC.


Yes, there is an ODBC connector available.  What I am trying to do, I do
this manually every few days and I'd like to automate the process, is this:

I have 5 MSSQL boxes scattered around geographically, all connected by a
Private WAN.  They are gathering event data, some several hundred
thousand records a day, others, tens of thousands.  All the records are
time stamped.  Every couple days I use the Data Import/Export application
in MSSQL on my local machine to dump the data from each machine's
log_activity table to a flat file, I use a query that's basically like
this:

select * from log_activity where event_utc_at > '2007-03-26 00:00:00'

I get a separate file from each SQL box. The data is tab delimited and I
cat it through:

fromdos | uniq | sed 's/\t/UTC\t/1' > new_file

I then pull each file into a holding table in Postgres, do another dup
check both against itself and the table its going into, there is a
tendency for duplicate records in this system.  The data is then inserted
into its appropriate table, one for each SQL Box that it came from.  I'm
up into the 28 GByte range on this so far and it grows several megs a
day.  I would simply like to automate the whole process.  I have most
everything working from bash with the exception of actually getting the
data out of the MS boxes.

As you can see it's really a simply task.  My problem is that I am not a
programmer or educated in Dbases, I have learned it as a necessity.  I
just need an app that will pass a query to a MSSQL box and dump the result
out to a file.  I can sed and awk it to death from there.

What do you think?

Shawn