M$ sql server

hb21l6 · 07-28-2010, 03:09 PM

naaa then all,

Is there a way of connecting to M$ SQL server 2005 / 2008 in Linux.

Reason for this is that our reporting and management system is stored on SQL server 2005 and most of our backups run on redhat, aix, slack.
I'm looking to create a standard .sh or .ksh script to write an entry to the M$sql server once a backup has finished.

I've heard of freeTDS but everyone says its outdated and probably won't work with sql instances.. (not tried it yet, but soon will)

failing writing direct to SQL, I will have to create a windows share and write a log file on completion, then write a service to watch for entries in said folder, then write a record to the database.. which seems like a lot of work for a simple task.

any help or advice on this would be really appreciated.

many thanks

D

TB0ne · 07-28-2010, 03:44 PM

Quote:

Originally Posted by hb21l6

naaa then all,

Is there a way of connecting to M$ SQL server 2005 / 2008 in Linux.

Reason for this is that our reporting and management system is stored on SQL server 2005 and most of our backups run on redhat, aix, slack.
I'm looking to create a standard .sh or .ksh script to write an entry to the M$sql server once a backup has finished.

I've heard of freeTDS but everyone says its outdated and probably won't work with sql instances.. (not tried it yet, but soon will)

failing writing direct to SQL, I will have to create a windows share and write a log file on completion, then write a service to watch for entries in said folder, then write a record to the database.. which seems like a lot of work for a simple task.

any help or advice on this would be really appreciated.
many thanks
D

You can either do it through Perl (best) or through PHP. Both have modules to let you hook up and read/write MSSQL DB's. Give a search on CPAN.org for MSSQL, and you'll find several modules.

From what I've read, this:
http://search.cpan.org/~mjevans/DBD-ODBC/

appears most promising. I know I've done lots with MySQL, and it only take a few lines of Perl to do this.

hb21l6 · 07-28-2010, 04:26 PM

Thank you very much, I think perl will be my friend here.

D

jlinkels · 07-28-2010, 06:24 PM

I did it using ODBC driver in PHP. I can't remember all the details, but this is the example:

PHP Code:



<?php 
// Connect to MSSQL 
$link = odbc_connect('exact', 'jlinkels', 'mypasswd'); 
if(!$link) { 
    die('Unable to connect or select database!'); 
} 
 
    // Do a simple query, select the version of 
    // MSSQL and print it. 
    $version = odbc_exec($link, 'SELECT @@VERSION;'); 
    $row = odbc_fetch_array($version); 
 
    print_r($row); 
 
    odbc_free_result($version); 
    $version = odbc_exec($link, 'use [400]'); 
    $row = odbc_fetch_array($version); 
    print_r($row); 
 
    odbc_free_result($version); 
    $version = odbc_exec($link, 'select * from RNW_TECH_LOC'); 
    while ($row = odbc_fetch_array($version)) { 
      print_r($row); 
    }; 
 
 
 
 
    // Clean up 
    odbc_free_result($version); 
?> 
ms_connect.php (END)

jlinkels

TB0ne · 07-29-2010, 10:00 AM

Quote:

Originally Posted by jlinkels

I did it using ODBC driver in PHP. I can't remember all the details, but this is the example:

PHP Code:



<?php 
// Connect to MSSQL 
$link = odbc_connect('exact', 'jlinkels', 'mypasswd'); 
if(!$link) { 
    die('Unable to connect or select database!'); 
} 
 
    // Do a simple query, select the version of 
    // MSSQL and print it. 
    $version = odbc_exec($link, 'SELECT @@VERSION;'); 
    $row = odbc_fetch_array($version); 
 
    print_r($row); 
 
    odbc_free_result($version); 
    $version = odbc_exec($link, 'use [400]'); 
    $row = odbc_fetch_array($version); 
    print_r($row); 
 
    odbc_free_result($version); 
    $version = odbc_exec($link, 'select * from RNW_TECH_LOC'); 
    while ($row = odbc_fetch_array($version)) { 
      print_r($row); 
    }; 
 
 
 
 
    // Clean up 
    odbc_free_result($version); 
?> 
ms_connect.php (END)

jlinkels

Nice one, jlinkels. One thing I love about Linux...always more than one way to do things.

Also, to take this one step further, you can use this same method to read from the MSSQL database, and directly 'translate' it to MySQL, etc. Nice flexibility available to us Linux users.

nosnetrom · 08-03-2010, 09:33 AM

Definitely need to check this out--thanks jlinkels! But, would you offer some annotation or clarification for a n00b?

PHP Code:



$link = odbc_connect('exact', 'jlinkels', 'mypasswd');

Is MS SQL Server host name = 'exact' here?

PHP Code:



$version = odbc_exec($link, 'use [400]');

Not at all clear on what this does!

FWIW, the Linux server is PHP Version 5.2.6, Apache 2.2.9 on Ubuntu Server 8.10; our enterprise management DB is on SQL Server 2005, and it would be great if I could get this Linux box to see SQL Server! TIA for the clarification!

jlinkels · 08-03-2010, 02:37 PM

Uhm, well now I have to explain it, it looks a bit more complicated than it looked at first sight. It is ages ago that I installed and tested this, at least 9 months! Since then I haven't used it.

So, IIRC, you need an ODBC connector which is an interface between a database and an application program. Apparently I have used FreeTDS for the ODBC connector, and a bunch of other packages to make PHP understand how to work with ODBC. Since you are running Ubuntu, the names of the packages are likely the same. This is what I have installed:

Code:

root@jlinkels_lt:/etc# dpkg --get-selections | grep odbc
libodbcinstq1c2                                 install
odbcinst1debian1                                install
php5-odbc                                       install
tdsodbc                                         install
unixodbc                                        install
unixodbc-bin                                    install

Code:

root@jlinkels_lt:/etc# dpkg --get-selections | grep tds
freetds-common                                  install
tdsodbc                                         install

And this is the contents of the odbc.ini file:

Code:

[Exact]
Description     = Test to freeTDS
Driver          = TDS
Database        =master
Server          = eserv.rnbtech.com
Port            = 1433
Trace           = Yes
TraceFile       = /tmp/mstest.log

Now as you can see, 'Exact' is the name of the connection, aka DSN. You connect to this DSN and then you are connected to the server.

In this statement:

PHP Code:



$version = odbc_exec($link, 'use [400]');

I tell the DSN that I want to use database named '400'. (It took 3 days to find out that the database name has to be in square brackets)

There must be a command line version of TDS to test your odbc connection. I would recommend that before you start using PHP, so you can see how ODBC reacts.

Sorry that I can't be more detailed, I am for sure this worked, I abandoned the program because the database did not contain the information we needed. Browsing through the manual of FreeTDS should help you. It was the only solution which worked for us. It was MySQL2008.

jlinkels

nosnetrom · 08-04-2010, 10:01 AM

Great--you are giving me enough rope to hang myself! :-) Just a little more clarification, please.

The odbc.ini has you connecting to MS SQL Server at eserv.rnbtech.com, and you have the line "Database =master". Is "master" a named instance of SQL Server? (I want to connect to the default instance of SQL Server on the box; how would I reference that?)

Then, you say your PHP has you connecting to the DB on the server named 400 (don't forget the square brackets! <g>). This seems to contradict the line in odbc.ini, "Database = master" -- unless "master" is a reference to a named instance. Would you please explain?

Thanks again for the knowledge; I know it's hard to resurrect details from 9 months ago. :-)

jlinkels · 08-04-2010, 06:22 PM

I really would like to help you, but that would mean retracing all the steps I took a few monhts ago and I simply cannot dedicate that much time.

But maybe this is of help:
http://www.unixodbc.org/doc/FreeTDS.html

jlinkels

nosnetrom · 08-05-2010, 09:38 AM

Understood; no worries. Thanks again for your help, and kindest regards!