Questions tagged [linked-server]

Linked server allows operation to multiple server as one query.

Filter by
Sorted by
Tagged with
1 vote
1 answer
12 views

SQL Server Linked Server PAGEIOLATCH_SH / CXPACKET

I have an agent job that runs a very simple "select max(date) from table" against a linked sql server. The linked server is part of an AG and the link from remote uses a listener to get to ...
user avatar
1 vote
0 answers
44 views

SQL script to run from batch jobs

I have a sql script that truncates the table and insert into database from a linked server.Since the server is SQL express I will need to use batch jobs to schedule the jobs. Below is a snippet of the ...
SQL_NoExpert's user avatar
  • 1,049
0 votes
1 answer
91 views

Communicating from a SQL Server 2022 to a SQL Server 2008 over linked server

I'm doing a proof of concept to assess whether we can upgrade some our servers piecemeal to SQL Server 2022. Unfortunately we still have a SQL Server 2008 which holds an old version of Microsoft ...
Jamie's user avatar
  • 101
2 votes
4 answers
571 views

SQL Server linked server query to Postgres not filtering rows

When I run this query on a SQL Server 2022 server with a linked server connection to PostgreSQL: SELECT TOP 1 * FROM PGSTACK.stackoverflow.[public].users WHERE Id = 1; SQL Server fetches the entire ...
Brent Ozar's user avatar
  • 42.9k
0 votes
1 answer
91 views

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

I have the task of migrating our databases from a Windows 2012 R2 server with SQL Server 11.0.7507 (2012) to a Windows 2019 server with SQL Server 16.0.1110.1 (2022). I am having issues with opening ...
Chris's user avatar
  • 1
2 votes
1 answer
49 views

Problems getting SQL Server linked server to work in web-based admin utility on Windows Server 2022

I have an ASP classic web application based on an SQL Server database, with an admin component. The database has a linked server with Microsoft.ACE.OLEDB.12.0 provider to allow bulk import of records ...
user1207313's user avatar
1 vote
1 answer
38 views

Can a server outside the Windows domain connect to a 2nd SQL instance via a linked server?

I have the infrastructure shown in the diagram below. Web Server 1 is outside the Windows Domain. Web Server 2 is inside the Windows Domain. SQL Server 1 and 2 are both inside the domain. There is a ...
ktakmn's user avatar
  • 25
0 votes
1 answer
75 views

How to use transaction in different server SQL Server

I have more than one server with SQL Server database. I want to do a begin transaction on both databases so that when there is an error it will automatically roll back. I have tried linked servers ...
stevanus billy's user avatar
0 votes
0 answers
25 views

Distributor_Admin Changing Linked Server "Data Access" Property to False

Why is the system changing the "Data Access" property on my linked server? How can I change that? See SQL Server linked server's DATA ACCESS property changes to false. I have the same ...
Abraham's user avatar
  • 101
0 votes
1 answer
782 views

unable to begin a distributed transaction database linked server

I have a problem with my Linked Server. I have a Linked Server set up and when I select or insert it works great. The problem starts when I run a transaction to overwrite simple data in tables. begin ...
Jan Kůst's user avatar
1 vote
0 answers
132 views

There was a recoverable, provider-specific error, such as an RPC failure. SQL to MySQL linked server no longer working

I have been searching all day and trying every manner of solution that I come across, yet nothing has resolved this issue. Here is my was working code: delete RemoteTable from OPENQUERY (LIGHTSAIL, '...
Yourguide's user avatar
1 vote
1 answer
121 views

SQL Server Jobs - fail on connection timeout

We have a LIVE set of 3 SQL Server instances on one network and a DEV set of 3 SQL Server instances on another. Being on different networks; LIVE cannot talk to DEV however the instances can talk to ...
Drees's user avatar
  • 11
0 votes
0 answers
530 views

Problems with DB linked server

My db link on DEV environment is working fine. But for the test I have windows without a GUI environment. With the help of my colleagues, I managed to set it up precisely like on DEV. The DB link ...
Jan Kůst's user avatar
0 votes
0 answers
43 views

Linked server view Select query doesn't execute on production server

I've build one view. I can run sys.databases from the linked server. However, when I tried to execute the following view. It is not retrieving any data from the linked server. USE [SSISDB] GO /****** ...
James's user avatar
  • 1
3 votes
2 answers
295 views

Using Kerberos Constrained Delegation with an ADSI Linked Server

I am attempting to utilize Kerberos delegation in order to allow Active Directory queries using a Linked Server configured for ADSI to be filtered according to the end-user's Active Directory security ...
DamonDCD's user avatar
0 votes
0 answers
40 views

Linked server failed

I'm trying to set up a linked server, but it still fails and gives an error: Error: Microsoft ODBC Driver 17 for SQL Server : SQL Server Network Interfaces: Error Locating Server/Instance Specified [...
Jan Kůst's user avatar
0 votes
0 answers
98 views

Linked service: SQL Server-2019 to Cosmos DB

I have created linked service for importing data from Azure cosmos DB to on-prem MS-SQL-2019. Services created from SSMS and test connection is successful, but query is getting error. Query: select * ...
maverick_1's user avatar
0 votes
2 answers
162 views

Linked Servers and Application Intent

I'm attempting to improve performance of a set of views that we have on a local server, pulling data from another set of views on a remote Azure database. Our system provider advised us to use ...
Paul's user avatar
  • 188
5 votes
1 answer
263 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 ...
pvallero's user avatar
1 vote
0 answers
433 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 ...
gobsek2 Altsgamer's user avatar
1 vote
0 answers
633 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 ...
Mike's user avatar
  • 658
0 votes
0 answers
138 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 ...
Dave.Gugg's user avatar
  • 294
0 votes
0 answers
77 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 ...
Dorian's user avatar
  • 103
0 votes
0 answers
165 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 ...
Dorian's user avatar
  • 103
0 votes
1 answer
461 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 ...
Khanjan Shah's user avatar
0 votes
0 answers
101 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....
RobMartin's user avatar
  • 149
1 vote
1 answer
203 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 ...
RobMartin's user avatar
  • 149
0 votes
1 answer
307 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 ...
Depth of Field's user avatar
0 votes
0 answers
96 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 ...
Mr.Kal's user avatar
  • 1
5 votes
1 answer
1k 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>].[<...
Sergey Rybalkin's user avatar
0 votes
0 answers
141 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....
DBASurvivor's user avatar
2 votes
2 answers
555 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 ...
user45867's user avatar
  • 1,683
0 votes
1 answer
54 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 ...
Sam Axe's user avatar
  • 183
0 votes
0 answers
64 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 ...
tjking45's user avatar
0 votes
0 answers
91 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 ...
user avatar
1 vote
1 answer
356 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 ...
xhr489's user avatar
  • 805
0 votes
1 answer
173 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 ...
chrispykill's user avatar
9 votes
2 answers
883 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 ...
supervisor's user avatar
0 votes
1 answer
981 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 ...
Marcello Miorelli's user avatar
0 votes
0 answers
210 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 ...
Drees's user avatar
  • 11
0 votes
1 answer
172 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 ...
SAKA UK's user avatar
  • 90
1 vote
1 answer
754 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 ...
ktakmn's user avatar
  • 25
0 votes
0 answers
517 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 ...
David Squires's user avatar
1 vote
2 answers
3k 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 ...
Pedro Fernandes's user avatar
0 votes
1 answer
967 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 ...
sada's user avatar
  • 111
3 votes
0 answers
1k 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 ...
folow's user avatar
  • 339
0 votes
0 answers
1k 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 ...
pojanisu's user avatar
0 votes
1 answer
64 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'...
variable's user avatar
  • 3,541
0 votes
1 answer
482 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 ...
variable's user avatar
  • 3,541
0 votes
1 answer
985 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....
Metaphor's user avatar
  • 900

1
2 3 4 5
13