INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

collation in table edit

collation in table edit

(OP)
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

RE: collation in table edit

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:

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close