Topic: Connecting to MS-SQL Server 2000 from SME 7.4

[cactus]

As ever, I'll post updates as I discover more.

That would be very nice. I think many people will value a step-by-step howto in the wiki as well if you could spare the time once you have found out how to do it. Thanks in advance.

[Per]

Are there a reason not to keep the default dir /usr/local ?
In /etc are a lot of other stuff (of course).

[Per]

I finally got connected by tsql to my SQL Server (Express edition).
The final key was to add the line

instance = db_kalle

in freetds.conf, where db_kalle is the name of the instance of SQL Server you are trying to connect to.
Before that the SQL Server must be configured to allow outside connections (disabled by default in Express edition) alongside with a user with enough permissions.
And now it's time to start wrestling with php support...

PS. Does anybody know where David Crittens howto (from 2007 I think) can be found now? DS

[mad-mooo]

The big break through
The answer is always too simple eh! In all my trawling for clues, I never found anything about tabs in the formatting of the odbc.ini
I use mcedit as my preferred editor on the SME box. As per all the examples, i typed the entries out

[TestDB]
<Tab> Driver =<Tab>/usr/lib/libtdsodbc.so


Formatting the file thus will cause odbcinst -q -s to respond correctly. However, this will upset isql!
Remove the leading <Tab>s ( In line Tabs don't cause a problem) and suddenly isql comes to life

With the above caveat, The "How to" written by Nick Critten and the one posted on http://www.calio.it/docs/doku.php/sqlserver should be good.

So where from here.
Per has started writing up a "How to"
Once I've verified the php bit, I intend to roll an RPM for FreeTDS-0.82 to remove the requirement for installing gcc and make yum friendly
If anyone has already done so, I'd bee keen for any input.

More Updates soon.

[Per]

GREAT!!

Yup. The wiki HowTo are located here: http://wiki.contribs.org/ODBC
and I've added your tip about spaces.

Feel free everyone to contribute to the HowTo. 

[Per]

Hmm. tsql works fine. isql does not. Tabs are removed. Any clues?

Solved: Sorting out FQDN and DSN fixed it, howto updated accordingly.

[mad-mooo]

Latest Update

Freetds V0.82
Using the ./configure options
 --prefix=/usr
 --with-tdsver=8.0
 --sysconfdir=/etc
 --enable-msdblib

freetds.conf - not implimented
odbcinst.ini  - not implimented
odbc.ini as follows:
[TestDB]
Driver          = /usr/lib/libtdsodbc.so
Description   = Test Database Connection
Trace          = No
Server         = 192.168.x.x
Port            = 1433
TDS_Version = 8.0
_____________________________________________
tsql works
isql works

php almost works.....

$TestDB = odbc_connect('TestDB', 'TestUsr', 'Secret');  // Good this far
if(!isset($TestDB)) die("Database connection failed");
$Query = 'select * from TABLE1';
$RecSet = odbc_exec($TestDB, $Query);                    // This is where it goes pear shaped.

From Message log
Jun 19 14:51:57 test1 httpd: PHP Warning:  odbc_exec(): SQL error: àZ   xð   8rú¿�Xú, SQL state 00000 in SQLExecDirect in /home/e-smith/files/ibays/tests/html/ticket.php on line 54

I built up separate SME box in parallel and installed FreeTDS 0.63. The above problem did not occur, all ran as smooth as a baby's b...
Installing FreeTDS 0.64 over the FreeTDS 0.82 installation clears the problem (not very scientific I know)

At this time I conclude that one of two possibilities are likely.
There is a bug in FreeTDS 0.82
I've not configured FreeTDS 0.82 correctly for it to work with PHP

Having spent 15+ days working on a the problem and arrived at a repeatable + workable solution (using v0.63),
I will return to testing when the next stable version of FreeTDS is released or when anyone can suggest
anything that stands a reasonable chance of progress with v0.82.

I'll continue to liaise with Per, who is writing up a new 'How To'

[Per]

I'll try your setup today to verify the problem and then I update the howto accordingly.
Do you remove freetds.conf and odbcinst.ini or you just ignore them?

Is it possible to reverse engineer the precompiled 0.63/0.64-version to find out used settings?

I'ts probably not neccesary to add --enable-msdblib as that is a default according to docs: "As of version 0.63, this option specifies just the default behavior".

If the problem persists, should we post something on the FreeTDS mailing list?

[Per]

I've successfully connected to SQL Server 2005, both regular and express, using the current HowTo at: http://wiki.contribs.org/ODBC.

Hence I removed the 'Under construction' -warning 

But it's neither complete or fully tested yet so, please folks, do feel free to correct, edit and stuff to make it better!

[Per]

mad-mooo

having got tsql to work, I ran in to the same problem.
using osql, the following was reported:
error: no potential directory strings in "/usr/bin/isql"
+ /lib/ld-linux.so.2

Just added instructions on how to troubleshoot with osql in the HowTo, but when I tested myself (on my working connection) I also got the same response as you.

I found som info here: http://lists.ibiblio.org/pipermail/freetds/2008q3/023579.html

When I do:

[root@server22 apps]# strace -e connect isql -v remoteserver dbuser dbpass
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
connect(3, {sa_family=AF_INET, sin_port=htons(1433), sin_addr=inet_addr("192.168.0.112")}, 16) = -1 EINPROGRESS (Operation now in progress)
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

To me it seems to work despite the error message recived with the osql script.

Should I bother with further investigation or simply accept the world as not perfect?

[mad-mooo]

Per

I think it may be fair to mention osql as tool available for testing the connection but with some considerations:
To my knowledge osql was introduced from v0.63. When I've installed 0.6x RPMs, osql has not been present or worked.
Did you get osql to work with v0.6x?

Running osql through strace does appear to show attempts to look for files, assumedly to make a remote socket / connection.
In my case, this turned out to be a red herring, the real and repeatable problem was found to be caused by the leading TABs in the odbc.ini file.

Form my experience, a strong trouble shooting section is to be reccommended but strace my be on the side of mission creep.
I would encourage readers of this forum to add details of any problems found together with their solutions to add to the troubleshooting section.

I still have to find out what is preventing odbc_exec working with v0.82 and publish the problem. I've read of others having the same problem but not yet learnt what was causing their problem.

Additional comment or corrections always welcome.

[Per]

Yes I've added that to the HowTo. I've also posted on the FreeTDS mailing list to find out more.

To be continued.