Skip to main content
20 votes

8000 character limit on OPENQUERY against a linked server

You can bypass the 8000 character limit of OPENQUERY by utilizing EXECUTE AT, as follows: DECLARE @myStatement VARCHAR(MAX) SET @myStatement = 'SELECT * FROM TABLE WHERE CHARACTERS.... ' -- Imagine ...
John Eisbrener's user avatar
18 votes
Accepted

OLE DB provider "MSOLEDBSQL" with SQL Server not supported?

You seem to have done everything right. However the link you posted has a comment on the page which states: This page is no longer maintained. Please read the details below. If you open the details ...
John K. N.'s user avatar
14 votes
Accepted

Why is this explicit cast causing problems only with a Linked Server?

So, I was able to reproduce the error after realizing that the CAST was being done locally, not on the remote instance. I had previously recommended moving up to SP3 in the hopes of fixing this (...
Solomon Rutzky's user avatar
13 votes
Accepted

What big limitations should I expect from Linked SQL Servers?

How transparent will it be to the application code? How much change should I expect? Not transparent at all. Expect major changes. You should be prepared for very substantial performance degradation....
Paul White's user avatar
  • 87k
10 votes

SQL Server - Is my database being queried over linked server?

No. A linked server is just some other SQL Server making a connection to your SQL Server. There's no way to reliably identify these connections.
David Browne - Microsoft's user avatar
10 votes

SQL Server - Is my database being queried over linked server?

There is one possible way, it is to look at sessions/connections, and guess by session's properties that this session is coming from unusual source: select * from sys.dm_exec_sessions s join sys....
Aleksey Vitsko's user avatar
9 votes
Accepted

Using INSERT INTO...EXEC AT linked server into temp table fails with Msg 7391

In my testing (to another instance on the same server, not on a separate server), this worked if I had the Linked Server option of Enable Promotion of Distributed Transactions for RPC set to "False". ...
Solomon Rutzky's user avatar
9 votes
Accepted

Door Number 1, 2, or 3?...Security change, bug, or failed upgrade with an in-place upgrade from SQL Server 2016 to SQL Server 2019?

Door Number 1, 2, or 3: 2, with a dash of 3. An ownership chain should prevent permission checks on a scalar UDF referenced from a view, so a user with SELECT permission on the view should not also ...
David Browne - Microsoft's user avatar
9 votes
Accepted

Why SQL Server scans all rows when updating bit column even for primary key via linked server

Ideally, you would call a stored procedure on the remote server to make this modification, with the UUID as a parameter. Meantime, try using a variable for the bit constant: DECLARE @true bit = 1; ...
Paul White's user avatar
  • 87k
9 votes
Accepted

SQL Server linked server's DATA ACCESS property changes to false

The only way to change the linked server properties I know of is via system procedure sp_serveroption. This procedure is called in the background even if you change the properties via GUI. So you can ...
Zikato's user avatar
  • 5,102
8 votes
Accepted

Creating Stored Procedure in local db that references tables in linked server

You can't parameterize entity names into a T-SQL statement. In order to do this you need to (a) create the #temp table first, and (b) use dynamic SQL. Here is one approach: CREATE PROCEDURE [dbo].[...
Aaron Bertrand's user avatar
7 votes

Reindexing / rebuild Indexes on Linked Server tables

Four-part-naming syntax is not supported for ALTER INDEX. Try this: EXEC ('ALTER INDEX ALL ON [dbname].[dbo].[tablename] REBUILD WITH (FILLFACTOR = 90);') AT LinkedServerName;
Scott Hodgin - Retired's user avatar
7 votes
Accepted

SQL Server --> Informix DB Linked Server

We had a nightmare setting something similar up, specifically with the issue of connecting to multiple Informix environments. I had several Informix instances that needed to be linked to a single SQL ...
coyote's user avatar
  • 106
7 votes
Accepted

Linked server returned message "Query timeout expired."

This doesn't answer your question with regards to why the query is slow on the 11th day, but hopefully it helps clarify why it fails after 10 minutes. You mention: The remote timeout on the linked ...
Josh Darnell's user avatar
  • 29.4k
7 votes
Accepted

Remote Scan when updating using functions

Am I missing anything here? Not really. You are discovering that distributed query processing has lots of tricky limitations like this. So the rule of thumb for using Linked Servers is to always use ...
David Browne - Microsoft's user avatar
6 votes
Accepted

Data read from linked server returns different data-type on production server

The observed difference in behavior is due to the difference in patch levels between the two servers. Per the resolution of KB3051993, which is included in Cumulative Update 6 For SQL Server 2012 SP2)...
mootinator's user avatar
6 votes
Accepted

Why Does SQL Server OPENQUERY To Oracle Return Different Column Types On SQL Server 2016 to 2008

This behaviour has changed in recent updates. See FIX: The value of NUMBER type is truncated when you select data from an Oracle-linked server by using OLE DB provider After you apply this fix, ...
Tom V's user avatar
  • 15.7k
6 votes
Accepted

Linked Server : How to set an exclusive lock on a remote table?

When trying to execute a transaction across multiple servers, you need to use BEGIN DISTRIBUTED TRANSACTION instead of BEGIN TRANSACTION. If my understanding is correct, BEGIN TRANSACTION creates a ...
RDFozz's user avatar
  • 11.6k
6 votes

"SQLNCLI11" for linked server "linkedserver" returned message "The transaction manager has disabled its support for remote/network transactions."

SQL Server 2014 Availability Groups does not support distributed transactions. SQL Server 2016 has limited support for distributed transactions, however, SQL Server 2017 has the best support. ...
Hannah Vernon's user avatar
  • 70.2k
6 votes
Accepted

Could not complete cursor operation because the table schema changed after the cursor was declared

Try explicitly setting your cursor to be LOCAL STATIC FORWARD_ONLY. I don't know what specific changes might be happening on your linked servers, or what that stored procedure might be touching, but ...
Aaron Bertrand's user avatar
6 votes
Accepted

how to stop using sql server login credentials in a linked server?

I don't know if you got this resolved or not, but after recently going through our own (re)discovery of the requirements to get delegation running, I figured I could at least provide an answer in the ...
John Eisbrener's user avatar
5 votes

Using OPENQUERY to execute a script

Late to the question, but nonetheless, I thought I'd add my two pennies worth. Make your first line in the procedure "SET NOCOUNT ON". This will suppress multiples result sets produced from all the ...
Gil Vicente's user avatar
5 votes

ODAC OLE DB provider "OraOLEDB.Oracle" for linked server supplied inconsistent metadata for a column

From SQL Server Management Studio (SQL Server 2012 instance), I have created a linked server to an Oracle 12c database using the 12c ODAC Provider. I have run across "The OLE DB provider "OraOLEDB....
Baodad's user avatar
  • 483
5 votes
Accepted

Run Built-In Function on linked server

You can execute Dynamic SQL on that Linked Server by executing sp_executesql on that remote server using the 4-part name. Then you just assign that to a variable marked as OUTPUT: DECLARE @RemoteName ...
Solomon Rutzky's user avatar
5 votes
Accepted

Get-MSSQLLinkPasswords Powershell script doesn't seem to work under SQL Server 2016

I've tested dbatools' Copy-SqlLinkedServer from SQL Server 2016 to SQL Server 2016 and SQL Server 2014 to SQL Server 2016. All linked servers and their respective logins were successfully migrated. ...
Chrissy LeMaire's user avatar
5 votes

Never Ending OLEDB Wait for Linked Server

For SQL Server targets: On the target server (the one that's receiving the linked server query), run sp_WhoIsActive and look at the linked server query. If the query doesn't show up, something's ...
Brent Ozar's user avatar
5 votes

What is Oracle equivalent for Linked Server and can you join with SQL Server?

Yes, you can. In Oracle, this is called "Heterogeneous Connectivity" This Oracle admin manual has details. This article walks through an example case of doing the above steps with details. ...
CaM's user avatar
  • 2,183
5 votes
Accepted

SQL Server - Linked Server error - Could not connect to server XYZ because

The linked server was also used for replication. The order of Subscribe and Create linked server was the issue. Reproduce the error: 1. Drop the subscription 2. Drop the linked server 3. ...
Santhoshkumar KB's user avatar
5 votes

Where do Linked Server Queries get executed?

1 - The OLEDB provider is initiated on Server A, Server B sees it as a normal connection and query, it doesn't know that Sever A is a SQL Server, it's just a client to it. 2 - The query is executed ...
Jonathan Fite's user avatar
5 votes

Linked Server to Azure Error: "Access to the remote server is denied because no login-mapping exists."

The other answer is wrong and I want to disprove it with repro code and pictures. The problem here is the parameter @provstr, it's a known issue and how to solve it is described here: Access to the ...
sepupic's user avatar
  • 11k

Only top scored, non community-wiki answers of a minimum length are eligible