Re: Connecting to SQL Server on Windows

[Martin J. Evans]

On 24/07/12 22:39, Shrenuj Bansal wrote:

On Thursday, June 28, 2012 10:07:47 AM UTC-6, Shrenuj Bansal wrote:

I am using a perl script to try to connect to SQL Server on Windows. I believe 
I have DBI v1.607 and DBD::ODBC v1.25. I am able to connect to the server when 
I set up a data source using the ODBC Data Source Administrator on Windows. 
However, when I try to set up the data source myself in the perl script with 
the same credentials I get an error. I was hoping someone could help me out 
with this.

Here's my code:


use DBI;

# DBD::ODBC

my $dsn = 'DBI:ODBC:Driver={SQL Server}';
my $host =
my $database =
my $user =
my $auth =

# Connect via DBD:ODBC by specifying the DSN dynamically
my $dbh = DBI->connect("$dsn;Server=$host;Database=$database",
                                                $user,
                                                $auth,
                                                { RaiseError => 1, AutoCommit 
=> 1}
                                                ) || die "Database connection 
not made: $DBI:errstr";
                                                
# Prepare a SQL statement

my $sql = "SELECT BundleVersionLocation FROM valdb.dbo.VDB_BundleVersions 
WHERE BundleVersionID = 20";
my $sth = $dbh->prepare( $sql );

# Execute the statement
$sth->execute();

my($BundleVersionID);

# Bind the results to the local variables
$sth->bind_columns( undef, \$BundleVersionID );

# Retrieve values from the result set
while( $sth->fetch() ) {
        print "$BundleVersionID\n";
}

# Close the connection
$sth->finish();
$dbh->disconnect();

Here is the error I receive when I try to run the above script:

DBI connect('Driver={SQL 
Server};Server=ecmdrvdev-dbsql;Database=valdb','shrenuj
b',...) failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed 
for
user 'shrenujb'. (SQL-28000) at test.pl line 13

Thanks for the help. I got it working on windows. I am now trying to connect 
perl from Linux to SQl Server and am having problems.
I use exactly the same connection string as I do in Windows yet it seems to be 
giving me problems

The connection string I use is:
my $dbh = DBI->connect($dsn, $user, $pwd, {RaiseError=>1, AutoCommit=>1} )|| die 
"Database connection not made: $DBI:errstr";
where
$dsn = "dbi:ODBC:DRIVER={SQL 
Server};Server=ecmdrvdev-dbsql;Database=valdb;Trusted_Connection=True";
usr and pwd are also supplied

I keep getting the error:
DBI connect('DRIVER={SQL 
Server};Server=ecmdrvdev-dbsql;Database=valdb;Trusted_Connection=True','shrenujb',...)
 failed: [unixODBC][Driver Manager]Data source name not found, and no default 
driver specified (SQL-IM002) at test.pl line 7

I was hoping you could help me out with this. Thanks.

There is a good FAQ on data source not found issues on our web site at
http://www.easysoft.com/support/kb/kb01039.html and an article all about 
unixODBC at
http://www.easysoft.com/developer/interfaces/odbc/linux.html. The second goes 
into all the details you'll need to work this out.

There is also "ODBC - The Connection Process" on my web site at 
http://www.martin-evans.me.uk/node/70 and this is all about DBD::ODBC and unixODBC.

The likelihood is that your DRIVER "SQL Server" is not defined or defined in 
the wrong place - see odbcinst -j output and then check odbinst.ini.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com