I have this T-SQL code:
declare @DBNAAM varbinary(128) = cast((select DB_NAME() as [Current Database]) + '_SINGLES-' as varbinary(128));
set context_info @DBNAAM;
go
print cast(context_info() as varchar(128)) + 'XTRA_NAME'
go
If I run it in SSMS I get the output:
TEST_T24TSV_SINGLES-XTRA_NAME
That is what I expect, the dbname is TEST_T24TSV_SINGLES.
But if I run the same script with SQLCMD the output is:
T
So everything after the T disappears!
(If I copy the first output from SSMS to f.e. notepad, I also get only the T.)
(I want to use the construction with context_info, because I want to print a lot of this (after numerous GO's), and I want to have a one line statement for every print that I do. It is not possible to use a subquery as part of a print-statement.)
Why is SQL Server doing this? What can I change?
The problem is you're using the wrong data types.
Firstly DB_NAME returns a sysname, which is a synonym for nvarchar(128) NOT NULL. You are casting it to a varbinary(128) (so could suffer truncation) and then casting that value to a varchar, so the second byte will now be seen as a null character.
Secondly CONTEXT_INFO stores a binary value, not a varbinary, which means you get lots of null characters at the end of your string if you use a (n)varchar. Converting a binary to a varbinary doesn't remove the 0x00 bytes from the end of the value.
So you need to convert to an nchar(128), then a binary(128) (as CONTEXT_INFO allows 128 bytes) and then SET the value; this means that the bytes at the end are whitespace not null characters. Then you can convert that value back to an nchar(128), RTRIM it (which implicitly turns it into an nvarchar) and then concatenate:
DECLARE @DBNAAM binary(128) = CONVERT(binary(128),CONVERT(nchar(128),DB_NAME() +N'_SINGLES-'));
SET context_info @DBNAAM;
GO
PRINT RTRIM(CONVERT(nchar(128),CONTEXT_INFO()))+N'XTRA_NAME';
If you know that your database can only contain ASCII characters, then I suggest converting to a char(128) rather than an nchar(128) to avoid as much truncation as possible.
Damien_The_Unbeliever, however, does raise a good point about using SESSION_CONTEXT. This avoids the "hoops" and the value is stored using a sql_variant, so supports up to 8,000 bytes of data.
A solution using this would look like this:
DECLARE @DBNAAM sysname = DB_NAME() + N'_SINGLES-';
EXEC sys.sp_set_session_context N'DBNAAM', @DBNAAM;
GO
PRINT CONVERT(nvarchar(128),SESSION_CONTEXT(N'DBNAAM')) + N'XTRA_NAME';