SQL Server linked servers with collation issues

Question

This is a strange one, and it happens so infrequently that we cannot find any answers on this. First let me say yes I know we need to upgrade our server from 2012 R2 and we are in the process of this.

We have two sql servers, server a is the 2012 R2 box, server b has been upgraded to 2019 (both SQL and windows). These servers are set up as linked servers

Every now and then we run into an issue where if we run a query from server b to server a, but specify we need to use server a's collation in the results the request goes from 7 seconds to 20 minutes. This lasts for a day and then everything returns to normal. A few weeks later the issue is back.

I have looked at system resources, jobs running, network stats, disk I/O and I cannot find a single thing that could be causing this to happen so randomly.

We have had SQL teams look into this and even they are stumped, pointing to the usual troubleshooting of indexing etc which isn't the cause here.

Anyone else come across something like this before?

Answer 1

Best to re-ask here: SQL Server - Microsoft Q&A

Answer 2

Thanks, been trying to but get a puzzle that keeps bombing my posts out. When I complete the puzzle I get a 404 error, so MS and their usual difficulties, sighs

Answer 3

Aargh. Sorry to hear that.

Try with another browser perhaps?

Answer 4

Hello

Thank you for posting in Microsoft Community forum.

Based on the description, I understand your question is related to the SQL Server.

Since there are no engineers dedicated to SQL Server in this forum. in order to be able to get a quick and effective handling of your issue, I recommend that you repost your question in the Q&A forum, where there will be a dedicated engineer to give you a professional and effective reply.

Here is the link for Q&A forum.

Questions - Microsoft Q&A

Click the "Ask a Question" button in the upper right corner to post your question and select "SQL Server" tag.

Thank you for your understanding and support. If you have any question or concern, please feel free to let us know.

Have a nice day.

Best Regards,

Molly

Answer 5

Hi Molly

Every time I try submit this on the Q&A or the SQL Q&A forums, I either get a 404 error content not found, or I get content removed because it violates the code of conduct. The message I post is exactly the same as the one above, so how on earth is someone meant to ask a question if the AI keeps removing it?