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
  • 17.6k
13 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
  • 83.7k
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
  • 83.7k
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
  • 4,468
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
Accepted

How can I tell what database/procedure is using a linked server?

Well, you could parse the procedures (as long as they aren't encrypted) and jobs on server A looking for the name of the linked server - but keep in mind these requests could be passed in through an ...
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

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

Unable to start a nested transaction for OLE DB provider "SQLNCLI11" for linked server

As the error message suggests, add the following to your stored procedure: SET XACT_ABORT ON; See Distributed Queries and Distributed Transactions
Paul White's user avatar
  • 83.7k
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

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
  • 96
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
  • 70k
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

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.2k
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

How can I tell what database/procedure is using a linked server?

I use this script that I developed to help me find linked server references, but can be used to find any text string in multiple objects. /********************************* Name: Text Searcher ...
Jonathan Fite'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
Accepted

How to Replicate data between a hosted database and a local database

There's no replication or out-of-the-box solution to your problem, particularly since you don't have that type of access to the remote machine. As other commenters here have noted, you're probably ...
Daniel Hutmacher's user avatar
5 votes

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

Even though Oracle support note 207303.1 states that the 12c client works with 11gR2 client, this error seems to be caused by a compatibility issue between the 2 different versions. To fix, I ...
BateTech's user avatar
  • 746
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

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