RE: Connection to MSSql

[FARRINGTON, RYAN]

below is the code I use to access a SQL 2K box. Make sure you are running MDAC 2.7 otherwise you will run into problems. Please forgive the un-'l33t code... I'n not a PERL guru (yet) and if you want to assist in making the code 'l33t please give it a shot =)

 

 

use Win32::OLE;
use Win32::OLE::Variant;
use Win32::ADO;

 

my $Username = "uname";
my $Password = "p455w0rd";
my $Database = "database_name";
my $DBServer = "SQLserver-test";

 

if(Connect("1") eq "Good"){

    #you are connected... do your thing

} else {

    #you errored out... look at the connect sub to find how to report it.

}

 

 

 

################################################################################
##  Connect()
##
## purpose: To connect to a SQL server using ADODB.connection
##
##
## returns: success or failure
##
##
################################################################################
sub Connect{
 local ($count) = @_;
 local ($Status, $str) = "";
 $Conn = Win32::OLE->new("ADODB.Connection");
 $Conn->Open("Provider=SQLOLEDB.1;Password=$Password;Persist Security Info=True;User ID=$Username;Initial Catalog=$Database;Data Source=$DBServer");
 $str = Win32::OLE->LastError();
 if($str){
  @str = split(/\n/, $str);
  print FILE "$str[0]\n$str[1]\n$str[2]\n"; #prints to a log file... you know how to do that =)
  $Status = "Bad";
 } else {
  $Status = "Good"
 }

 

 if($Status eq "Bad"){
  if($count <= '3'){
   sleep 3;
   $Status = Connect($count+1);
  }
 }
 return $Status;
}