Re: Can't call databasepropertyex() over DBD::ODBC for MSSQL

I traced this to the tds_version setting.

If I set "tds version" in freetds.conf to 8.0, then the DBD::Sybase
example stops working as well, while 7.0 works fine.

If I set tds_version=7.0 in my DBD::ODBC DSN, then it also works fine.

On Sun, Mar 28, 2010 at 06:42:45AM -0400, Rafael Kitover wrote:
> Hello, I've noticed that DBD::ODBC with unixODBC and FreeTDS has bizarre
> problems calling some stored procedures and functions in MSSQL.
> 
> Here's an example:
> 
> % echo $DBICTEST_MSSQL_DSN
> dbi:Sybase:server=SQL2008
> % perl -MDBI -le 'my $dbh=DBI->connect(@ENV{map "DBICTEST_MSSQL_$_", qw/DSN USER PASS/}); print for $dbh->selectrow_array("SELECT databasepropertyex(DB_NAME(), '\''Collation'\'')");'
> SQL_Latin1_General_CP1_CI_AS
> 
> Here I'm using DBD::Sybase compiled against FreeTDS to call the
> databasepropertyex() function, it works correctly.
> 
> The same query using DBD::ODBC compiled against unixODBC using the same
> version of FreeTDS produces:
> 
> % echo $DBICTEST_MSSQL_ODBC_DSN 
> dbi:ODBC:server=myhost.dyndns.info;port=5000;driver=FreeTDS;tds_version=8.0
> % perl -MDBI -le 'my $dbh=DBI->connect(@ENV{map "DBICTEST_MSSQL_ODBC_$_", qw/DSN USER PASS/}); print for $dbh->selectrow_array("SELECT databasepropertyex(DB_NAME(), '\''Collation'\'')");'                                                              
> 
> this returns no result.
> 
> This is the only reliable way to detect collation (and therefore
> case-sensitivity) in MSSQL 2000+ if for some reason access to
> sys.databases has been blocked, so would be nice if it worked or there
> was some workaround.