Sql-server – Running query against SQL Server with FreeTDS’s bsqldb returns blank output

freetdssql-server-2008

Here is the query I ran

select CAST(PageGuid as varchar(max)) + ',' + CAST(TenantGuid as varchar(max)) + '/' + CAST(OriginalFileGuid as varchar(max)) 
from Pages

This worked with tsql but when run with bsqldb I get empty lines instead of results.

I confirmed that bsqldb works by running other simple queries. So why is this one not working?

Here is the verbose output

bsqldb:163: Verbose operation enabled
bsqldb:212: Query:
    select CAST(PageGuid as varchar(max)) + ',' + CAST(TenantGuid as  varchar(max)) + '/' + CAST(OriginalFileGuid as varchar(max)) from Pages
bsqldb:186: dbsqlsend(): OK
bsqldb:194: dbsqlok(): OK
bsqldb:289: calling dbresults: OK
Result set 1
Freeing prior allocations
Allocating buffers
Allocating compute buffers
Metadata
col     name                            source                          type             size    varies
------  ------------------------------  ------------------------------  ---------------  ------  ------
     1                                                                             text   64512       1

Data


Retrieving return status... none
11084 rows affected
Retrieving output parameters... none
bsqldb:615: dbresults() returned NO_MORE_RESULTS (2):
bsqldb:212: Query:

I'm using FreeTDS 0.64RC1, FreeBSD 8, and SQL Server 2008

Best Answer

I suspect the one of the values is NULL, so the whole expression concatenation is NULL.

You'd need this:

CAST(ISNULL(PageGuid, '') as varchar(max)) + ',' + 
CAST(ISNULL(TenantGuid, '') as varchar(max)) + '/' +
CAST(ISNULL(OriginalFileGuid, '') as varchar(max))
Related Question