Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Connecting to sqlserver2008 - DBD::Sybase connectivity and unixODBC issue

by venu_hs (Novice)
on Feb 12, 2013 at 11:57 UTC ( #1018340=perlquestion: print w/ replies, xml ) Need Help??
venu_hs has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, we are trying to connect to sqlserver 2008 from a perl script using DBD::Sybase. could you please help. This is a bit urgent... We got the below error when we try to run the script... Message String: ct_connect(): directory service layer: internal directory control layer error: Requested server name not found.

Below is the code:

#!/usr/bin/perl use strict; use DBI; use DBD::Sybase; my $user="abc"; my $auth='defv'; my $srvr = 'abc.cde.corp.org\XYZ'; # Connect to the SQL Server Database my $dbh = DBI->connect("dbi:Sybase:server=${srvr};database=TEST", $use +r, $auth,{PrintError => 0,RaiseError => 1, ShowErrorStatement => 1,}) +; my $sql = "SELECT USER"; my $sth = $dbh->prepare( $sql ); $sth->execute(); my @data; if($sth->execute) { while ( @data = $sth->fetchrow ) { print "@data\n"; } } $sth->finish(); undef $sth; $dbh->disconnect();

Then we also tried the option of using unixODBC But still could not solve the issue as we got a different error...

Here is the error: DBI connect('DRIVER={SQL Server};Server=abc.cde.corp.org\XYZ;Database=TEST;UID=abc;PWD=defv','',...) failed: unixODBCDriver ManagerCan't open lib 'SQL Server' : file not found (SQL-01000)

below is the code:

#!/usr/bin/perl -w use strict; use DBI; use DBD::ODBC; my $user = q/abc/; my $password = 'defv'; my$dbh1 = DBI->connect("dbi:ODBC:DRIVER={SQL Server};Server=abc.cde.co +rp.org\\XYZ;Database=TEST;UID=$user;PWD=$password");

Comment on Connecting to sqlserver2008 - DBD::Sybase connectivity and unixODBC issue
Select or Download Code
Re: Connecting to sqlserver2008 - DBD::Sybase connectivity and unixODBC issue
by Anonymous Monk on Feb 12, 2013 at 12:20 UTC
    What happens when you google the error message?

      i searched and found that it could be a problem with my /etc/odbc.ini file details. Actually i created a new file odbc.ini in local path and modified the environment variable $ODBCINI pointed to local file. Please tell me what i am doing wrong here...appreciate if some one could post the step by step process to implement the UnixODBC to connect to sql server... Thanks

        You've got 2 problems - which one do you want help with? the DBD::Sybase problem or the DBD::ODBC one? For the DBD::Sybase one you should check your Sybase interfaces file I think (but I'm not a Sybase guy so I could be wrong). For the ODBC issue I can help but I'll need to know what ODBC driver you are using. The odbc.ini file is irrelevant if you are using a DSN-less connection which you are if your ODBC connection string starts with DRIVER={something} instead of DSN=somethingelse. I've already shown you a valid odbcinst.ini file.

Re: Connecting to sqlserver2008 - DBD::Sybase connectivity and unixODBC issue
by roboticus (Canon) on Feb 12, 2013 at 13:17 UTC

    venu_hs:

    Do you really have an SQL Server ODBC driver on your unix box? Or are you using something like FreeTDS? You need to be sure you're specifying the correct driver name, or you can get an error like that.

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

      When i give odbcinst -j, it gives me drivers,system data resources and other details. when i give odbcinst -q -d, the o/p is SQL Server Native Client 11.0 Can you please post some links realted to this connectivity with valid info..

        venu_hs:

        I generally google for connection strings and come up with a page like: http://www.connectionstrings.com/sql-server-2008, then start trying variations on the connection strings. If you look at the error messages and/or tracing information (see http://search.cpan.org/~mjevans/DBD-ODBC-1.41/ODBC.pm#Tracing), the error messages generally guide you to the incorrect parameter.

        For example, assuming everything is set up properly, and you're getting the original message "Requested server name not found", then I'd look at the spelling of the server name you specified. If the spelling is correct and you're using a name rather than IP address, I'd try pinging the name using the same account your program runs under to see if it can access the machine.

        Unfortunately, there are so many different settings and potential connection issues, that it's rather hard to guide you through them. So start with the basics.

        I'd suggest starting with DBI turn on as much diagnostic tracing as possible, and then ask it to enumerate the drivers it knows about. Once you're sure it's listing ODBC, then ask it what ODBC data sources are available. If it lists any, try to connect to one and see what sort of diagnostics you get.

        ...roboticus

        When your only tool is a hammer, all problems look like your thumb.

Re: Connecting to sqlserver2008 - DBD::Sybase connectivity and unixODBC issue
by mje (Deacon) on Feb 12, 2013 at 13:26 UTC

    The original message "ct_connect(): directory service layer: internal directory control layer error: Requested server name not found" seems to be your main problem and it suggests it is coming from the library beneath DBD::Sybase (but you did not specify what you are using). Not that I use Sybase but it suggests to me that "abc.cde.corp.org\XYZ" is possibly wrong.

    Switching to unixODBC is not going to make this problem go away. The "Can't open lib 'SQL Server'" error is referring to what you've put in your odbcinst.ini file where you named the Driver which is supposed to be a path to the ODBC Driver shared lib you want to use i.e. DRIVER={SQL Server} makes unixODBC look in your odbcinst.ini file for a Driver named "SQL Server" then it looks for the key "driver" and attempts to dynamically load that shared object. You error suggests the driver key in the odbcinst.ini file says "SQL Server" which is not a valid shared object to load.

    e.g., a valid odbcinst.ini file looks like this:

    [ODBC] Trace=no TraceFile=/tmp/unixodbc.log [SQL Server] Description=SQL Server ODBC driver Driver=/usr/local/easysoft/sqlserver/lib/libessqlsrv.so

      First of all thankyou for replying to my query. I have checked odbcinst.ini file and i have a different driver name Please find the below details

      [SQL Server Native Client 11.0] Description=Microsoft SQL Server ODBC Driver V1.0 for Linux Driver= /.../..../libsqlncli-11.0.so.1790.0 Threading=1 UsageCount=1

      Now i have modified the code as below

      my$dbh1 = DBI->connect("dbi:ODBC:DRIVER={SQL Server Native Client 11.0 +};Server=abc.cde.corp.org\\XYZ;Database=TEST;UID=$user;PWD=$password" +);

      Now i got a different error Please find the below

      DBI connect('DRIVER={SQL Server Native Client 11.0};Server=abc.cde.cor +p.org\XYZ;Database=TEST;UID=abc;PWD=defv','',...) failed: [unixODBC][ +Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed (SQL +-IM004)

        You are better having a full path to the driver in the odbcinst.ini file as it will only work if your relative path is correct for the directory you are in.

        I don't use the ODBC driver you are using as the company I work for wrote its own SQL Server ODBC Driver. The error is saying the ODBC driver manager attempted to call SQLAllocHandle to allocate an environment and the driver returned the error IM004. This is almost always the very first call an ODBC application will make. Your driver probably needs something set in your shell environment e.g., an environment variable - but that is just a guess as I don't use that ODBC driver (what does the driver documentation say).

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1018340]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (12)
As of 2014-09-30 19:06 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (381 votes), past polls