Accessing data from a case-sensitive server to a case-insensitive server

Question

Post reply

Accessing data from a case-sensitive server to a case-insensitive server

PaulPich69

Valued Member

Points: 64
More actions
June 10, 2020 at 1:00 pm

#3761963

We had some consultants install a warehouse management software requiring a new database engine to be created on our sql server box. This software requires Latin1_General_BIN collation. As part of an integration project, we are trying to access data in our main ERP software which resides on another server, but has a case insensitive collation SQL_Latin1_General_CP1_CI_AS. We created a linked server that connects to our ERP server, using sa user & password. When we try to view tables under the catalog of this linked server, many tables are not visible (including the one we want to do a simple SELECT * FROM).
What could cause data to not be visible?

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

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

PaulPich69 wrote:

We created a linked server that connects to our ERP server, using sa user & password.

Really REALLY BAD IDEA! That also means that the "sa" user is still enabled and it should ALWAYS be disabled and should NEVER be used for something like a linked server. I'll also state that no one should actually know the sa password and that it should be written down and kept in a safe somewhere.

I know that doesn't help answer your immediate problem and sounds a bit snarky (it's not meant that way) but it will save you the embarrassment of reading in the morning news about how your company was hacked. Considering that you good folks haven't followed the first step of hardening your server, you should probably hire someone to secure your server(s) for you. Seriously.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PaulPich69 Valued Member Points: 64 More actions · Answer 2

I'm aware of this. Using sa is just temporary and this is a test server.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

Paul, there's really no difference between "just a test server" and a production server. They are both "in the domain" and should be treated equally... especially when it comes to disabling the "sa" login.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PaulPich69 Valued Member Points: 64 More actions · Answer 4

I would like to know more about the linked server issue I have. In fact what's particular is I have no problem doing the opposite: Get onto my ERP server, create a linked server to the WMS server and do a simple select * from a table there. The reason I wonder if this is a collation problem is that we are able to see all tables from the ERP server from another case-insensitive server on the domain.