DBI MS SQL connection error

Hi all,

I have run into a problem with DBI connection to MS SQL server. I have
a script within which the below is executed:

my $DSN = 'driver={SQL
Server};Server=servername,1304;database=dbname;Network=DBMSSOCN';
my $dbh = DBI->connect("DBI Smiley Surprised

DBC:$DSN",'dbuser', 'dbpass') or die
$DBI::errstr;

my $sth = $dbh->prepare("Select categoryName, categoryID from
tblCategory Order By categoryName");

$sth->execute;

Now this script works on TS server1 (6.1) and TS server2 (6.1), but when I copy this script to New TS Server I get the following error:

DBI connect('driver={SQL Server};Server=servername,
1304;database=dbname;Network=
DBMSSOCN','dbname',...) failed: [Microsoft][ODBC SQL Server Driver]
[TCP/IP Sock
ets]General network error. Check your network documentation.
(SQL-08001)
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
(Connect()). (
SQL-01000)(DBD: db_login/SQLConnect err=-1) at E:\custom\perl
\Category.ipl line 16
Can't call method "prepare" on an undefined value at E:\custom\perl
\Category.ipl line 18.

I can ping servername (the sql server) from the NewServer. The DBI
version on all the servers is the same (1.38). Also the perl version
is the same (v5.8.2 built for MSWin32-x86-multi). The only difference between the new server and the other 2 servers is ODBC SQL server version which is 200.85.1064.00 on server1 and server1. On the NewServer the version is 200.86.1830.00.

Does anyone have any hints/clues as what might be going wrong here. Or any one else have another way to get connection to a MS SQL server using perl ?

Any help will be greatly appreciated.

regards
Saya

Find more posts tagged with

Comments

Migrateduser

From the given info,either the $DSN is wrong or username/password is wrong.

vahu

Hi snkmoorthy,

I am not sure whether you read my post correctly, but I stated that I copied the script from a server where it was working to the new server where it failed. So $DSN or the user/pass where correct but as I stated in post that ODBC SQL server versions where different! My first hunch on the issue was also what you suggest, based on the error message returned (DBD: db_login/SQLConnect err=-1), but before posting to the forum I had obviuosly checked this since it would cause quit a bit of embarassment if the problem was the user/pass (would be hard to justify the amount og hours spent :-)).

After several hours trying different stuff I got the script to work by changing the way the $DSN and connect where build.

# Build the DSN string to be used
my $sDSN = 'driver=SQL Server;Server=dbservername;Database=dbname;

# Create the connection to the SQL Server
my $sDBH = DBI->connect("dbi Smiley Surprised

DBC:$sDSN","dbusername","dbuserpass", {RaiseError => 1, AutoCommit => 1}) or die $DBI::errstr;

By building the $DSN and DBI->connect as above I was able to get connection to the DB :-).

Regards
Saya