Questions tagged [linked-server]
Linked server allows operation to multiple server as one query.
599
questions
4
votes
1
answer
132
views
SQL Server linked server's DATA ACCESS property changes to false
I have a SQL Server instance that includes an agent job that accesses a second instance via a linked server. Occasionally, and seemingly randomly, the DATA ACCESS property of the linked server ...
- 53
1
vote
0
answers
84
views
Linked server and promotion of distributed transaction
Background - Main server is Microsoft SQL Server 2019 and linked server is PostgreSQL 15.
Main reason for the linking is the duplication of records.
All other DML operations are good from SQL Server ...
1
vote
0
answers
77
views
Linked Server query to Oracle fails on very wide table - adjusting FetchSize doesn't resolve the problem
We're using a Linked Server connection to Oracle 19c, using the 21.3 Oracle Client (64 bit). We're running on SQL 2019.
This connection works fine for all the tables we want to query except one, which ...
- 498
0
votes
0
answers
55
views
Linked server query occasionally returns results slowly
I have an unfortunate situation where I need to pull a next-number from a table from both a local and a linked server. To do so, I've created a stored procedure to return the result. When I execute ...
- 294
0
votes
0
answers
45
views
two servers have linked to the same server - one connection is encrypted and second not - why?
I have some old SQL Server 2008r2 instances that do not have client certificate set up and force encryption is set to FALSE.
It is on Windows 2008 r2 and really hard to upgrade - for now it must stay ...
- 101
0
votes
0
answers
47
views
what permissions does user need to have to use linked server and its default remote user mapping
i have some linkedserver and for it created default maping that mapping list is empty
and all goes thrue 'Be made using this seciurity context'
and this works fine - i can querry this linked from any ...
- 101
0
votes
1
answer
72
views
Unable to create linked server to Power BI XMLA endpoint in SSMS
Unable to create linked server to Power BI XMLA endpoint in SSMS and getting below error:
The test connection to the linked server failed. Additional information: An exception occurred while ...
0
votes
0
answers
33
views
Join remote tables with local tables in SQL Server 2012
If I have to join a local table to a remote table then I usually use the 4-part identifier. For example:
select t.id, r.region_id, r.cost
from localdb.dbo.product p
join linked_server.remotedb.dbo....
- 149
1
vote
1
answer
53
views
Filtering in Queries using Linked Servers in SQL Server 2012
My colleague told me that any query that uses a linked server first transfers the data from the remote server to the server the query is running on and then applies filters. For example, if I have the ...
- 149
0
votes
1
answer
86
views
SQL not connecting to Linked Oracle Server
We have SQL Server 2014, with the latest CU, running with Oracle client version 11.2.0.1.0 installed.
We have about 25 linked Oracle servers connected on it. The servers are running of mix of Oracle ...
- 135
0
votes
0
answers
74
views
SQL Server Linked Server Impersonate error
I'm trying to configure LinkedServer to use Impersonate.
All Servers and PC are connected to the same Domain.
What I've configured already:
SQLServer1 - First/Middle server:
Configured SPNs for ...
- 1
5
votes
1
answer
493
views
Update fails on linked server after upgrading to SQL Server 2022
After upgrading SQL Server from 2019 to 2022 the following query with linked server has started to fail (I've omitted some column names and details for simplicity):
UPDATE [<remote server>].[<...
- 165
0
votes
0
answers
50
views
Linked Server using domain accounts suddenly stopped working (…for a while)
**Issue
Last week (Monday, afternoon), we ran an Alter Procedure to change dbo.mySpToRead and it worked.
Last week (Tuesday, late morning), we ran an Alter Procedure again to change the same dbo....
- 13
2
votes
2
answers
288
views
SQL Server: How to query data between linkedservers, with index visibility to both?
I'm green with linked servers so forgive some of my nomenclature ignorance.
But my understanding & experience is that it's far more efficient to query a table on the server itself vs. a remote ...
- 1,613
0
votes
1
answer
52
views
Why does this seemingly equivalent SQL have such vastly different run times? [closed]
A little set up:
I have a SQL Server, and that SQL Server has a linked server connection to an Oracle database. All the queries presented here are being run in SSMS against the SQL Server (and from ...
- 183
0
votes
0
answers
46
views
Append Data from 1 Table to Another with Image Column Across Linked Server
MSSQL - I am trying to append data from Table 1 that doesn't exist in Table 2. The tables have the same columns. One of the columns is image format and is not 'comparable'. Table 2 is across a ...
- 1
0
votes
0
answers
57
views
My linked server from as400 disconnects every time SQL Server is restarted
Every time I restart SQL Server, my linked server which is linked with AS400 gets disconnected.
Every time it gets disconnected, I simply refresh the provider and I'm good to go. But, sometimes if I'm ...
siddharth rao
1
vote
1
answer
143
views
ETL table from one SQL Server to another SQL Server
I would like to ETL a table from one instance of SQL Server to another instance of SQL Server. I do not want to create an SSIS package.
I have tried
to extract the data using an External Table on the ...
- 765
0
votes
1
answer
92
views
Unable to remove local login for linked server
I have added a linked server that was working fine. After this I added a local login to within properties > security on the linked server. After doing this I get an error when trying to run a query ...
9
votes
2
answers
706
views
Why SQL Server scans all rows when updating bit column even for primary key via linked server
I am using simple update statement for specific primary key for SQL linked server as follow
UPDATE t
SET
processed = 1,
processed_on = GETDATE()
FROM [LINKED\SERVER].DATABASE.dbo.FileQueue t
...
- 93
0
votes
1
answer
436
views
Server 'repl_distributor' is not configured for DATA ACCESS
I have enabled my repl_distributor linked server for data access in both publisher_server and distributor_server.
Both servers are also configured to allow remote connections
On both servers I have ...
- 15.6k
0
votes
0
answers
147
views
T-SQL: Slow performance on Stored Procedure Sending Data to LinkedServers
I am new to T-SQL and I am facing an issue with a slow processing stored procedure. The purpose of this procedure is to transfer table data from child databases up to their corresponding parents.
The ...
- 101
0
votes
1
answer
123
views
Allow external users to access my DB through Linked server
What do i need to configure to allow external users from different domain to access a few tables in my SQL DB?
I've created in the past a connection to external linked server by one of our clients who ...
- 90
1
vote
1
answer
377
views
Cannot connect to linked server using name in hosts file
I am trying to set up a linked server in SQL Server 2017 using the local Windows hosts file on the server where the linked server is being set up. The reason for this is to allow me to hard-code the ...
- 13
0
votes
0
answers
349
views
SQL Linked Server "IBM DB2 for i IBMDASQL OLE DB Provider" / IBMDASQL Stopped Working - No Errors
We have a linked server to pull data from an AS400 that worked every day for years, but randomly stopped working last night.
When we do test connection / run a test open query against the linked ...
1
vote
2
answers
2k
views
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGIN'
I'm facing a very specific issue on my SQL Server instance. Recently, I had to create a Linked Server (referencing server B) in my server A, to connect to a database in B through A. Everything worked ...
0
votes
1
answer
561
views
Login failed at creating Linked Server
I want to create a linked server on database server B to database server A in a development server where just the SQL Management Studio is installed.
All three servers are in the same domain.
The ...
- 111
3
votes
0
answers
683
views
Lined Server - Cannot create an instance of OLE DB provider "IBMDADB2.DB2COPY2" for linked server "MYDB2"
I would like to create Linked Server from SQL Server 2016 on Windows to Db2 v11.5.7 on Linux/x86.
I created linked Server using Microsoft OLE DB Provider for DB2 and it works fine.
Now I would like to ...
- 183
0
votes
0
answers
655
views
How to use "with execute as" while querying a linked server from SQL Server
I have a strange permission issue with SQL Server 2019 (same thing happened also with 2016 version).
I have a database ReportDB, where reportuser (login: reportlogin) can run several reports.
I also ...
- 11
0
votes
1
answer
57
views
How to test whether current sql engine service account can decrypt the SMK?
Is it true that when the sql service account is changed via the config manager then the new account can decrypt the SMK.
However if the change was made via the services.msc, then the new account doesn'...
- 3,225
0
votes
1
answer
191
views
Do linked servers stop working after migrating the system dbs to a new sever?
Link: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-service-master-key-transact-sql?view=sql-server-ver15
The service master key is automatically generated the first time it is
needed ...
- 3,225
0
votes
1
answer
543
views
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for all but Windows Administrator login
I have a few dozen linked servers, MSSQL, Teradata and Oracle. These are on SQL Server 2019. Oracle servers are using OraOLEDB.Oracle 19c client. They are all created with the same parametrized script....
- 880
1
vote
2
answers
1k
views
How to save result from a Stored Procedure in a Linked Server to a variable or table?
I need to read the results of a stored procedure in a linked server, and store them in a variable, or a temporary table if not possible.
I can do this, and I get the correct result:
EXEC ('EXEC DBName....
0
votes
1
answer
234
views
Openquery through Oledb driver for DB2 slows down after a while - reboot only solution?
We have several sql servers that read from a db2 database on an as400 via Openquery to a linked server using the Microsoft oledb driver for db2 v5. One sql instance had a problem where its selects ...
- 71
0
votes
0
answers
710
views
Linked Server Error Msg 7321, Level 16, State 2, Line 1
I am getting the following error when I execute a simple linked server query.
OLE DB provider "MSDASQL" for linked server "name" returned message "HTTP request returned status ...
- 73
0
votes
0
answers
4k
views
Linked server error- The table either does not exist or the current user does not have permissions on that table
There is a view that is being called from Server A (SQL 2016) and this view needs to read data from Server B (SQL 2014). There is a linked server set up between these two servers and the connection ...
- 73
5
votes
1
answer
213
views
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?
What I Did:
Recently I tested doing an in-place upgrade on ServerA, a development server that was running SQL Server 2016 (SP 2 CU17) Standard Edition to SQL Server 2019 Standard Edition. (I know this ...
- 32k
1
vote
1
answer
248
views
How to create a SQL Server linked Server with Local SQL Server Authentication User to a remote Windows Authentication
I need to create a Linked Server from Server A that uses a SQL Server Authentication to a Server B that uses a Windows Authentication.
Is the "impersonation" possible?
If "YES" ...
- 13
0
votes
0
answers
329
views
Delay in prelogin response when connecting to Azure SQL
I have set up a linked server to an Azure SQL 2014 managed instance. The vendor gave me a SQL login and password to use. I can open a direct connection in SSMS and run queries. But when I try to run ...
- 129
-2
votes
1
answer
455
views
Need to create a SQL Server Linked Server to an Oracle data source [closed]
Can someone kindly point me to a good documentation where I can get all the information I need to be able to pull data out of an oracle database from a SQL Server database please? For example, what ...
- 287
0
votes
0
answers
219
views
MS SQL Linked Server Insert Error
As part of data migration, I have created linked server for Greenplum database in MS SQL 2019. I am able to query using linked server. Select & insert commands are working fine for most of the ...
- 1
0
votes
0
answers
639
views
Linked Server Connection Issue to SQL Server AlwaysOn Listener
I have SQL Server 2008R2 Standard Edition. I am making a Linked Server connection to a Listener of an AlwaysOn Availability Group.
However, when I run a query from SSMS I intermittently get this error:...
- 1,394
4
votes
2
answers
857
views
SQL Server - Is my database being queried over linked server?
Is there a way to know if a specific database on my SQL Server instance is being queried over linked server?
- 217
0
votes
1
answer
82
views
Merge 2 SQL Server Instances?
I have 2 SQL Server instances on a single server, inst1 and inst2. These are tightly coupled instance, with inst1 being linked to inst2 and vice-versa.
Because they are already running on the same ...
- 217
-1
votes
2
answers
666
views
Linked server - When is it recommended to use SQL Server or the 4 Other data sources for SQL Server?
To configure linked server pointing to a SQL server, there are 4 possible options.
Server Type: SQL Server
Server Type: Other data source; Microsoft OLE DB Provider for SQL Server
Server Type: ...
- 3,225
1
vote
1
answer
157
views
Should the remote user value be configured to map users defined in Local login or map users not defined in Login login?
Link: https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver15#to-create-a-linked-server-to-another-instance-of-...
- 3,225
-2
votes
1
answer
545
views
Does linked server local login value support a windows AD group based sql login?
Link: https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver15#to-create-a-linked-server-to-another-instance-of-...
- 3,225
1
vote
0
answers
596
views
Problem creating a link between SQL Server Standard edition and SQL Express edition
I am trying to establish a connection between two SQL severs located in different location and trying to read/fetch the tables. When I connect the remote Standard SQL Server from SQL Server Express ...
- 13
0
votes
0
answers
338
views
Sql Job Failing with Timeout Error
Recently I have noticed that a job is failing on our Production 2014 SQL Server with this error text:
Timeout error [258]. [SQLSTATE 42000] (Error 258) OLE DB provider "SQLNCLI11" for ...
- 73
2
votes
1
answer
223
views
Updating varchar column over linked server with parameterized query causes remote scan and cursorfetch [closed]
I'm issuing a fairly simple update of single varchar column against a remote linked server. E.g.:
UPDATE Hydrogen.CRM.dbo.Customers
SET EyeColor = 'Blue'
WHERE CustomerID = 619
And that works fine ...
- 946