collation in table edit

[niall5098]

HI There,

when i select the top 200 rows for a table and then select the SQL for the select it includes the table collation in one of the columns:

SELECT TOP (200) MediaFileUploadId, MediaFileFormatID, Name COLLATE Latin1_General_CI_AS AS Expr1, ProgrammeTemplateID, UploadedDate,
CreatedBy COLLATE Latin1_General_CI_AS AS Expr2, CreatedDateTime, ModifiedBy COLLATE Latin1_General_CI_AS AS Expr3, ModifiedDateTime,
Active, RowVersion, MediaFileUploadTypeId, MediaFileStatusId, FileSize, InUTC, OutUTC, MediaFileURL COLLATE Latin1_General_CI_AS AS Expr4,
PublishId, StartDate, ProfileId, OriginalName COLLATE Latin1_General_CI_AS AS Expr5, BRINumber, Title, Description
FROM MediaFileUpload

However if i log in as a seperate user and perform the same action i receive the sql without the collation.

Would anyone know why sql server is doing this?

Thanks in advance,

Niall

 

[gmmastros]

That's weird, and I've thought about this for a while. I'm wondering... do both users have the same default database?

Run this:

Select loginname, dbname, collation_name
From   sys.syslogins L
       Inner Join sys.databases D
	      On L.dbname = D.name

I'm thinking that there may be different default databases for the two logins. So, when you one user connects, the collation of the databases they are in matches the collation of the columns in the table, so the collate clauses are not necessary.

When the other user logs in, they connect to a different database that has a different collation, so SQL Server Management Studio includes the collate clause.

This is a bit of a stab in the dark, but it is a reasonable explanation, and something easy enough for you to check. Please let me know what you find.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom