MSSQL Access under Linux

[Chong Yu Meng]

On Tue, 2007-03-27 at 05:05 -0700, Shawn Tayler wrote:
> As you can see it's really a simple task.  

Clausewitz posits that the simple tasks are typically the most difficult
to do ... ok, back to Linux. 

My opinion is that unless you really need to get some specific data from
the database, you should use the database's export utility and dump the
entire table out. For MSSQLServer, in addition to the Data Import/Export
utility, you can also use the Task Scheduler to automate the data dump
for you. I set that up once for an accounting system. Unfortunately,
after the data gets dumped, you still need to get the file from the
server to your computer and then do your data parsing. 

If you go down the SQL route, which is to have a program that connects
to each SQL server directly and extracts the information, you can skip
several steps: exporting the data, configuring the Task Scheduler and
automating the file download from the server. But this approach has a
major drawback and also a major difficulty:

The drawback is that when you establish a connection with each SQL
server, you are introducing load to the server which may or may not be
significant enough to cause a negative impact on performance (or even
affect stability -- this is SQL Server you are talking about, and the
connections with overseas locations can sometimes be quite bad). 

The major difficulty (which I had anyway), is the connection itself. I
was never able to connect from a PC running Fedora Core 1 (yes, it was
that long ago) to an MS SQL Server on the same network. I could ping
between the nodes and even access IIS running on the SQL Server, but the
connection could not be made, either by ODBC or JDBC. I then tried to
connect from a WinXP PC and still no joy. My theory was that it was
probably due to the integrated Windows firewall on the SQL Server
machine that was causing the connection problems. Unfortunately, I was
not able to further investigate this before other priorities came up. 

The problem with the integrated Windows firewall, and most personal
firewall software in general, for the Windows platform, is that there is
a lack of granularity and things aren't as intuitive as iptables. For
example, to this day I still do not know where to click to open specific
ports in the Windows firewall or why my wife's personal firewall
software blocks outgoing emails with attachments.

If you can resolve the connection issues and you have verified that the
performance hit is not significant, then you could try a direct SQL
connection. Otherwise the way you are doing it now is exactly what I
would recommend.

Regards,
pascal chong