5 Replies Latest reply: Nov 12, 2012 12:49 AM by Kgronau-Oracle RSS

    Problem with FreeTDS

    salvatore.gatto
      Hi,

      I have:

      Created dblink:
      CREATE DATABASE LINK "TANDOIEPS_LECCE.DBP.ONET"
      CONNECT TO "tandoieps"
      IDENTIFIED BY "tandoieps"
      USING 'TANDOIEPS_LECCE_TNS';

      In tnsnames:
      TANDOIEPS_LECCE_TNS.DBP.ONET =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = svdbp001.dbp.onet)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SID = TANDOIEPS_LECCE_SID)
      )
      (HS = OK)
      )


      in listner.ora

      (SID_DESC =
      (SID_NAME = TANDOIEPS_LECCE_SID)
      (ORACLE_HOME = /data/oracle/OracleHome10gR2)
      (PROGRAM = hsodbc)
      (ENVS = "LD_LIBRARY_PATH=/usr/lib, ODBCINI=/etc/odbc.ini")


      in hs/admin directory the file initTANDOIEPS_LECCE_SID.ora

      HS_FDS_CONNECT_INFO = TANDOIEPS_LECCE_INI
      HS_LANGUAGE="AMERICAN_AMERICA.WE8ISO8859P1"
      HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so
      set ODBCINI=/etc/odbc.ini
      set ODBCINSTINI=/etc/odbcinst.ini
      set LD_LIBRARY_PATH=/usr/lib
      HS_RPC_FETCH_SIZE = 32000
      HS_RPC_FETCH_REBLOCKING = ON
      HS_FDS_FETCH_ROWS=500
      HS_FDS_TRACE_LEVEL = 4
      HS_FDS_TRACE_FILE_NAME=TANDOIEPS_LECCE.trc

      in /etc/odbc.ini

      [TANDOIEPS_LECCE_INI]
      Driver = SQLServer
      Description = DB SQL SERVER Tandoi Lecce
      Trace = Yes
      TraceFile = /tmp/sql.log
      Servername = TANDOIEPS_LECCE_SRV
      Database = TandoiEps
      TDS_Version = 8.0

      in odbcinst.ini

      [SQLServer]
      Description     = FreeTDS unixODBC Driver
      Driver          = /usr/lib/libtdsodbc.so.0
      Setup          = /usr/lib/libtdsodbc.so.0
      UsageCount     = 1

      in freetds.conf

      [TANDOIEPS_LECCE_SRV]
      Host = 10.90.2.132
      tds version = 8.0
      instance = PEDONE_SERVER

      I check the firewall log and the riquest is allowed but I have the follow error in trc file:




      i:2104od -1 SQLDriverConnect:- hdbc=912DF20, hwnd=0, scsi=DSN=TANDOIEPS_L
      ECCE_INI;UID=tandoieps;PWD=, ccsi=-3, scso=NOT-AVAILABLE, ccso
      m=1020, pcso=BFC5E7B2, fdc=0,
      i:1094od 0 SQLError:- hnv=912B1F8,hdbc=152231712,hst=0,sss=S1000,pne=0,ce
      mm=-1077549736,pcbem=511
      sem = [unixODBC][FreeTDS][SQL Server]Unable to connect to dat
      a source
      i:1094od 100 SQLError:- hnv=912B1F8,hdbc=152231712,hst=0
      errors.h (2112): ; [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (SQL State: S1000; SQL Code: 0)
      i:2113od 0 SQLFreeConnect:- hdbc=912DF20
      DRV_InitTdp: errors.h (2112): ; [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (SQL State: S1000; SQL Code: 0)
      nvRETURN (./drv_bind.c 357): -2220
      nvRETURN (./nav_bind.c 298): -2220
      hoalgon (7): ; hoalgon Exited with retcode = 28500.
      hoaexit (6): ; hoaexit Entered.
      hoaexit (6): ; hoaexit Exited with retcode = 0.
      (0): ; Closing log file at WED NOV 7 20:14:56 2012.


      Help me please
      Rino
        • 1. Re: Problem with FreeTDS
          Kgronau-Oracle
          Rino,
          can you connect with an ODBC test utility (for example isql) to the SQL Server database?

          To get isql to work you need to make sure the ODBC environment is properly sourced (LD_LIBRARY_PATH) and the ODBCINI environment variable is set.
          then call the test utility isql like:
          isql -v <ODBC DSN> <UID> <PWD>
          for example: isql -v TANDOIEPS_LECCE_INI <UID> <PWD>

          BTW, when you do not specify the odbc driver in the odbc.ini and use odbcinst.ini, please also make sure the ODBCINITINI environment variable is set correctly.


          - Klaus
          • 2. Re: Problem with FreeTDS
            salvatore.gatto
            Klaus,

            Tnk for replay.

            I have test isql and the respons is:

            isql -v TANDOIEPS_LECCE_INI tandoieps tandoieps
            [S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
            [ISQL]ERROR: Could not SQLConnect

            I think that the problem is the SQL Server configuration on the server in question.
            I have another DB SQL Server installation, on other server, and the TDS works correctly simply changing:

            [TANDOIEPS_LECCE_SRV]
            Host = 10.90.1.180 #IP of the other server
            tds version = 8.0
            instance = MTP #instance name on the other server

            On the firewall I look that the request on the port 1434 (automatic when instance is configured) is correctly send.

            Regards
            Rino
            • 3. Re: Problem with FreeTDS
              Kgronau-Oracle
              Rino,
              as long as isql does not work dg4odbc will fail as well.

              I commonly do not like the mixture of having so many config files (odbcinst.ini, freetds.conf and odbc.ini) and prefer to have everything in one file. Let's add another alias to the odbc.ini file:
              [FreeTDS]
              Driver =/usr/lib/libtdsodbc.so.0
              Server = 10.90.2.132
              Database = AdventureWorks
              Database = TandoiEps
              Port = 1434
              TDS_Version = 8.0
              QuotedId=YES

              Are you now able to connect to the database with isql using the newly created FreeTDS ODBC DSN?

              if not, what happens when you telnet to this machine:
              telnet 10.90.2.132 1434


              BTW, I'm also wondering why the SQL Server is using the 1434 port in your config, did you cross check that with the MS SQl Server Configuration Manager => SQl Server Network Configuration => TCP/IP?

              - Klaus
              • 4. Re: Problem with FreeTDS
                salvatore.gatto
                Hi Klaus,

                The problem was on the local firewall on the server on which is installed SQL Server!!!!!

                Thanks so mutch
                • 5. Re: Problem with FreeTDS
                  Kgronau-Oracle
                  Please mark this thread as answered.
                  Thanks,
                  Klaus