Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
You are running T-SQL from bsqldb . What does this query give in SQL Server Management Studio? And also without the concatenation + '/' + ? –  gbn Aug 26 '11 at 10:56
add comment

1 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))
share|improve this answer
    
bsqldb doesn't seem to like your SQL: "Msg 102, Level 15, State 1 Server 'DEV-DB', Line 1 Incorrect syntax near 'ISNULL'. bsqldb: Msg 20018, Level 5 General SQL Server error: Check messages from the SQL Server. bsqldb:191: dbsqlok() failed" –  David Aug 29 '11 at 1:29
    
@David: this isn't standalone code... –  gbn Aug 29 '11 at 4:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.