We help IT Professionals succeed at work.

Using Perl DBI Connecting To MS-SQL

billfinkri
billfinkri asked
on
18,695 Views
Last Modified: 2012-06-21
What's wrong with my "DBI" code? I can NOT connect to the SQL server.

If you find my mistake, PLEASE be specific as possible with the syntax, please? (Even down to if I'm using the curley brackets correctly or not.)

I've been using DBI::Oracle for 2 years now, and have never seen a problem - but I just can't seem to use DBI to connect to the SQL server.

Thanks In Advance!

# Normal Perl Intro, /perl/bin/perl stuff goes here.
#
#

# Let The Users Know There Was A Problem
my $Error_Message = "\nThere Was A Problem Connecting To The Database\n";

my $q = new CGI;
use CGI;
# The Below Line - Takes Care Of Letting The User Know There Was An Error In The Web-Page!
use CGI::Carp (fatalsToBrowser);
use DBI;
# The Below Line Is Not Needed, Correct?
# use DBD::ODBC;
#

# print $q->header;

$dbusername = 'domain\user.name';
$dbpassword = 'password_here';
$server     = 'server_name_here';
$database   = 'Northwind';


$dbh = DBI->connect("DBI:ODBC:driver={SQL Server};Server=$server;Database=$database;UID=$dbusername;PWD=$dbpassword") ||  die "$Error_Message $DBI::errstr";

die $DBI::errstr if ($DBI::errstr);

$sql_statement = ('select * from northwind.employees');

$statementHandle = $dbh->prepare($sql_statement);
$statementHandle->execute()||die $statementHandle->errstr;

while (($cat1) = $statementHandle->fetchrow_array )
{
     print $cat1."\n";
 }

$statementHandle->disconnect();


====================
Here Are My Errors Reported
====================

[Fri Oct 28 13:18:24 2005] test_mssql.cgi:
[Fri Oct 28 13:18:24 2005] test_mssql.cgi: There Was A Problem Connecting To The Database
[Fri Oct 28 13:18:24 2005] test_mssql.cgi:  [Microsoft][ODBC SQL Server Driver]Client unable to establish connection (SQL-08001)
[Fri Oct 28 13:18:24 2005] test_mssql.cgi: [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()). (SQL-01000
)(DBD: db_login/SQLConnect err=-1) at test_mssql.cgi line 54.
Comment
Watch Question

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_err_odbc_5stz.asp

01000 is "general warning" -- your connection was successful.  your problem is that you are checking for the existance of DBI->errstr rather than the defined value of $dbh.  you should never try to check for an error condition setting unless the function has failed.  since you never check the value of the DBI::connect call, your logic is flawed.
also, i'm not sure, but i think your DSN syntax is wrong.  try this:

my $dbh = DBI->connect("dbi:ODBC:driver={SQL Server};server=$server;database=$database", $dbusername, $dbpassword) or die "could not connect: " . DBI->errstr;

case DOES make a difference.  for instance, trying to use the "pg" postgres driver will fail.  but if you use the "Pg" postgres driver, it succeeds.

Author

Commented:
Soooo...what does this mean? (Please?)

I'm afraid that I'm not familiar with this sort of syntax.

Then what/where should I change the code?

Thanks!
i just showed you the code!  replace the DBI->connect line that you have with the one i just gave you.  if you "have been using DBI::Oracle for 2 years now", you shouldn't have to ask me that question.

that syntax is the de facto DBI syntax.  read the manual for DBI.

Author

Commented:
Thanks for "your code" ... I cut/pasted your line of code directly from EE here, and received the same error. (I don't think it IS connecting.)

If my syntax is (now) correct with your connect "code" - then I'm probably looking in the wrong place.

RE: Read CPAN's DBI manual?

Thanks for your troubles ... that's exactly where my other browser window is opened to as I type this response.

For what it's worth - one of the reasons to use (and pay for) EE is getting answers quickly and to have other programmers maybe shed some light on a missing comma or semicolon someplace.

Thus your suggestion to "Read The Manual For DBI", while a good suggestion, negates the purpose of EE. EE is just a quick way of getting what the boss would like done ASAP.

Truly withOUT sounding like I didn't appreciate your time - maybe a bit of patience on your tone in your responses is in line. (Then again, maybe you're having just as good a day as I am.)
well, perhaps your server isn't using named pipes for connecting.  have you tried using TCP/IP?

hrm, here's a snippet that says to do just that:

Why do I get "[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile())"

It is a resource issue when running a lot of processes (e.g. MultiProcess in the OOB Server) to the MS SQL Server ODBC driver issue. Switch to TCP/IP instead of named pipes in the MS SQL Server ODBC DSN and the problem will go away.
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
Here's a little info on your 8001 error message.

8001: The Services DLL encountered an error while allocating memory
Memory could not be allocated. Verify that your system resources are not exhausted.


Here's a short script that will show what drivers and data sources you have available.

#!usr/bin/perl -w

  use DBI;

  @driver_names = DBI->available_drivers;
  @data_sources = DBI->data_sources('ODBC');
  print "Driver names: @driver_names",$/,$/;
  print $_,$/ for @data_sources;

Author

Commented:
FishMonger,

PERFECT!

Actually, your second script, which demonstrated which resources were available for me, is what did the the trick...!

Thus, I've discovered my problem - I'm NOT connecting to a "remote" host - I've only the LOCAL host Databases, using ODBC, to connect to. At least that's what the reported "Available Connections Were For Me."

What/If there is a syntax to connect to a 'remote' SQL Server ???

Thanks for your time, now that I've discovered the problem, just need to (correctly) connect to a remote SQL Server.

Any light you might be able to shed there?

Even so, I'll give the points here - you've given me enough to look in the right places, again. (Instead of WONDERING why I'm not connecting, now I know!)

Author

Commented:
FishMonger,

(Got IT!) Thanks for all of your time!

BTW - 'bout those lines I listed above ... there were a number of them, only through trial-error did I comment a few out, move a few around, just for testing.

Thus, I realized some of the code may have looked a bit odd.

Bill

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions