Post reply

Linked Server requiring uppercase fields?

  • October 25, 2006 at 4:24 pm

    #174730

    In a linked server scenario, I have one user that can run queries case-insensitive, while the others have to have their fields in uppercase.

    Here's the environment:

    I have a SQL2k5 (local) server with a Linked Server connection to a SQL2k sp3 (remote) server. In that connection I have several login mappings, each with different roles on the remote server. One logs in locally through Windows Authentication and has sysadmin role on the remote box; the others don't/have lesser db roles.

    The Win-authenticated user can query case-insensitive to his heart's content, while the others have to uppercase their fields.

    What might be causing this behavior? I'd like to get the connection to be case-insensitive for everyone, if possible.

  • October 25, 2006 at 5:21 pm

    #668048

    I'm not saying that I know the answer, but something I would want to know is whether  either of the servers involved are set to a case sensitive collation? or are they both default?

  • October 25, 2006 at 5:47 pm

    #668053

    Right. Forgot to mention that. Remote is SQL_Latin1_General_CP1_CI_AS, which is case-insensitive. Local is Latin1_General_CI_AI, also case-insensitive.

    I also looked at the local default database for the sql-login guys. That db has a Latin1_General_BIN collation. The remote db being queried sticks with the remote server's collation. The windows-authenticated user defaults to the master db, which uses server default.

  • October 25, 2006 at 9:29 pm

    #668076

    I'd play with the settings on the linked server properies - there's two about collations.  One is about using the remote server's collation and the other indicates whether you want SQL to assume the collations are compatible.  I tend to NOT use the remote server's collation and flag that the servers are collation compatible.  I wrote this from memory so I might have it a bit mixed up - but in any case play with those settings and you might get what you're after.

  • October 26, 2006 at 2:01 pm

    #668351

    I'll give that a try, Ian.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply