my $ConnStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Se
+curity Info=False;Data Source=hostname;Initial Catalog=Database;";

my $AdoConn = new Win32::OLE("ADODB.Connection");
   $AdoConn->Open($ConnStr);
[download]

If you need to specify a user, here is the syntax:

my $ConnStr = "Provider=SQLOLEDB.1;User Id=user; Password=password; Da
+ta Source=hostname; Initial Catalog=Database;";
[download]

Tired this...

my $connect_str = "Provider=SQLOLEDB.1;" .
                  "User Id=$uid;Password=$pwd;" .
                  "Data Source=$svr;" .
                  "Initial Catalog=$db";

my $conn = new Win32::OLE('ADODB.Connection')
   or die "Cannot make ADODB connection : " . Win32::OLE->LastError;

$conn->Open($connect_str) 
   or die "Cannot connect to $db on $svr : " . Win32::OLE->LastError;

$conn->Close;
[download]

...but I'm still getting the same problem...

Cannot connect to $db on $svr : 0 at adodb.pl line 22
[download]

I tried using Win32::ODBC to connect, which I got working ok, but this method doesn't support stored procedures with output parameters, which is what I really need to do. I'm looking into DBI too.

Anyone got any ideas how to do this?

Thanks for your help, but I got it working a different way - using DBI.

For anyone who's interested - it seems difficult to find info on accessing MS SQL Server from Perl. I used DBI and DBD::ODBC - I was able to get a connection going with Win32::ODBC and RDBAL but wasn't sure about running stored procedures with these methods.

Here's the code...note you need to install DBI, DBD::ODBC and set up a system DNS to connect to database from your ODBC data sources control panel - there may be a way to get a DSN-less connection though. Also, you must initialise any parameter being bound to a placeholder and used as an output parameter to the stored procedure to some valid value of the expected datatype. If you're expecting a string then initalise to ' ' or other string value. If you're getting some datatype conversion error ("error converting data type ... to ..."), then this could be the problem.

use DBI;
use DBI qw(:sql_types);

my $MSSQL_uid = 'myuid';
my $MSSQL_pwd = 'mypwd';
my $MSSQL_svr = 'mysqlsvr';
my $MSSQL_db  = 'mydb';
my $MSSQL_dsn = "dbi:ODBC:$MSSQL_db";
my $sth; # statement handle

# connect to DB
my $dbh = DBI->connect($MSSQL_dsn, $MSSQL_uid, $MSSQL_pwd)
   or die "Couldn't connect to $MSSQL_svr : $DBI::errstr\n";
print "\n\nConnected to $MSSQL_dsn DS\n\n";

# execute stored procedures and get output parameter value
my $param1_int = 0; # note you must initalise this to something 
my $exe_string = "EXECUTE my_stored_procedure" .
                 "?, " .
                 "'$param2_str', " .
                 "'$param3_str', " .
                 "$param4_int";
$sth = $dbh->prepare($exe_string) 
    or die "\nCouldn't prepare statement : $DBI::errstr\n";
$sth->bind_param_inout(1, \$param1_int, 50) 
    or die "\nCouldn't bind param inout : $DBI::errstr\n";
$sth->execute
    or die "\nCouldn't execute stored procedure : $DBI::errstr\n";
print "Output parameter value is now $param1_int\n";

# disconnect from DB
$dbh->disconnect
   or die "\nError disconnecting : $DBI::errstr\n";
[download]

Andrew

I got the same error again with your code.

Andrew