Problem in reading the database collation into a variable in SQL Server

Question

I've faced this strange thing that I can't set the value of a variable to a result of a query. The query is reading the collation of a database and works fine by itself:

SELECT collation_name 
FROM sys.databases
WHERE [name] = 'my_DB';

And the result is like: SQL_Latin1_General_CP1_CI_AS

But when I want to put the result in a variable, I get no result (and no error)

declare @DB_collation varchar(100);
set @DB_collation = (SELECT 
    collation_name 
    FROM sys.databases
    WHERE name = 'my_DB')  
print @DB_collation;

I have no problem in setting the result of other SELECT queries into a varchar variable.

Works on my system (if I replace `my_DB` with a real DB name), but it can be written more succinctly as `SELECT @db_collation = collation_name ... ` anyway. Note that neither this assignment nor `PRINT` will give a *result set*, if you want that you need `select @db_collation`. — Jeroen Mostert, Sep 22 '20 at 13:31
@JeroenMostert Thank you for your reply. I just tried ```select @DB_collation = ...``` and unfortunately it doesn't work too. — Iraj, Sep 22 '20 at 13:35
"It doesn't work" gives nobody any clue as to what's wrong and how to fix it. All I can tell you is that this query is functionally equivalent to selecting it separately, and you can verify this on a separate instance with Management Studio if you like. Check basic things like "am I on the right server", "am I using the same login", "is my DB name exactly the same, containing no case difference or invisible characters". — Jeroen Mostert, Sep 22 '20 at 13:37
@JeroenMostert Not only I'm on the same server and everything is identical, but also I write both query (for getting that value of collation and for putting it in a variable and then print) in a same query and the first one works and the second doesn't give any result! — Iraj, Sep 22 '20 at 13:42
Have you put `SELECT @DB_collation;` at the bottom of your queries? (Try `SELECT LEN(@DB_collation)` as well, for fun). Remember, your query as given *does not* produce a result, it only sets the variable. Capturing `PRINT` messages is done in a separate tab (in SSMS; other clients may not capture them at all). — Jeroen Mostert, Sep 22 '20 at 13:44
@JeroenMostert I still don't understand why I the PRINT works fine in similar application but not in this case. I tries `SELECT LEN (@DB_collation)` and it works fine. The result is `28` — Iraj, Sep 22 '20 at 13:58
@Iraj Please look your results in Messages tab and not in Results tab — VBoka, Sep 22 '20 at 13:59
@VBoka Thank you. Solved. My bad that I didn't check the "Messages" tab. — Iraj, Sep 22 '20 at 14:05
@Iraj I have updated my answer. So you have found them in Messages tab ? — VBoka, Sep 22 '20 at 14:05
@VBoka Yes, it was in the message tab. Thank you for your time. — Iraj, Sep 22 '20 at 14:07

VBoka · Accepted Answer · 2020-09-22T14:05:14.917

1

This is the way to do it:

declare @DB_collation varchar(100);
SELECT  @DB_collation = 
    collation_name 
    FROM sys.databases
    WHERE name = 'tempdb' 
    
print @DB_collation;

Put your variable after the select keyword.

Here is a demo:

DEMO

If this does not work then you can select the value of the variable:

declare @DB_collation varchar(100);
SELECT  @DB_collation = 
    collation_name 
    FROM sys.databases
    WHERE name = 'tempdb' 
    
select @DB_collation;

P.S: Please look your results of the PRINT statement in Messages tab and not in Results tab

edited Sep 22 '20 at 14:05

answered Sep 22 '20 at 13:35

VBoka

8,995
3
16
24

I see this was already all written in the comment from @JeroenMostert – VBoka Sep 22 '20 at 13:42
Thank you. It works. But I still don't understand why print doesn't work. I tried this: `declare @testvar varchar(100) ; set @testvar = @DB_collation ; print @testvar` and this still doesn't print anything. I'm not sure if the content of the variable is normal varchar. That's important for me because I need it in the rest of my code/query. – Iraj Sep 22 '20 at 13:49
@Iraj what is the name of the tool you use ? – VBoka Sep 22 '20 at 13:54
@Iraj I do not know what demo you are looking at but this demo: https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=5e45d2f569893121114ca803ab04d765 works with print You have to know that I have to do it with tempdb not my_DB because I have tempdb in sys.databases as you can see in my first select in this demo – VBoka Sep 22 '20 at 14:02

Problem in reading the database collation into a variable in SQL Server

1 Answers1