Re: Connecting to SQL Server on Windows

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