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

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();

[download]

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: unixODBC Driver 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");


[download]

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?	[reply]
Re^2: Connecting to sqlserver2008 - DBD::Sybase connectivity and unixODBC issue by venu_hs (Novice) on Feb 12, 2013 at 13:26 UTC
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	[reply]
Re^3: Connecting to sqlserver2008 - DBD::Sybase connectivity and unixODBC issue by mje (Deacon) on Feb 12, 2013 at 13:33 UTC
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.	[reply]
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.	[reply]
Re^2: Connecting to sqlserver2008 - DBD::Sybase connectivity and unixODBC issue by venu_hs (Novice) on Feb 14, 2013 at 00:48 UTC
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..	[reply]
Re^3: Connecting to sqlserver2008 - DBD::Sybase connectivity and unixODBC issue by roboticus (Canon) on Feb 14, 2013 at 04:14 UTC
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.	[reply]
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` [download]	[reply] [d/l]
Re^2: Connecting to sqlserver2008 - DBD::Sybase connectivity and unixODBC issue by venu_hs (Novice) on Feb 12, 2013 at 13:46 UTC
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` [download] 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" +);` [download] 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)` [download]	[reply] [d/l] [select]
Re^3: Connecting to sqlserver2008 - DBD::Sybase connectivity and unixODBC issue by mje (Deacon) on Feb 12, 2013 at 13:55 UTC
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).	[reply]
Re^4: Connecting to sqlserver2008 - DBD::Sybase connectivity and unixODBC issue by venu_hs (Novice) on Feb 12, 2013 at 14:15 UTC
Re^5: Connecting to sqlserver2008 - DBD::Sybase connectivity and unixODBC issue by mje (Deacon) on Feb 12, 2013 at 14:53 UTC
Some notes below your chosen depth have not been shown here


We don't bite newbies here... much
	PerlMonks

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

How many continents have you visited?