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

SELECT CAST(databasepropertyex(DB_NAME(), 'Collation') AS VARCHAR)

works regardless of tds version, in case anyone runs into this problem.

Not sure why I didn't realize that sooner, oh well.

On Mon, Mar 29, 2010 at 08:27:06PM -0400, Rafael Kitover wrote:
> 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.