Questions tagged [linked-server]

Linked server allows operation to multiple server as one query.

131 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
2answers
9k views

MySQL - run SELECT statement on another server without defining the table structure

In MySQL I can query information on another server using federated tables, as long as I've defined the same table structure locally. In MS SQL Server, however, I can run any SQL statement against a ...
4
votes
1answer
326 views

Stuck with self-referencing linked server - will replacing the primary server name be better?

A vendor app uses 4-part names to reference objects. I am pushing back on that, but the current version is what it is. We installed the app using a SQL Alias for all the good reasons you do that, ...
4
votes
0answers
1k views

Auditing access to a linked server

I have a linked server with SQL Server configured with the security option "For a login not defined in the above list, connections will: Be made using the login's current security context". What I ...
3
votes
1answer
1k views

SQL Server 2016 Linked Server OPENQUERY Hangs

I've been trying to figure out this linked server problem for about a week now with no luck. The set up I have is a linked server from an instance of SQL SERVER 2016 to an instance of SQL SERVER 2016 ...
3
votes
0answers
1k views

Very Sporadic Cannot initialize the data source object of OLE DB provider “SQLNCLI11” for linked server

OLE DB provider "SQLNCLI11" for linked server "001" returned message "Unable to complete login process due to delay in opening server connection". Msg 7303, Level 16, State 1, Line 19 Cannot ...
3
votes
1answer
309 views

OLAP linked server fails connection test

I am trying to create a new linked server to an Analysis Services database and I would like to use this linked server to import data into a SQL Server table. example: SELECT * into raw.example ...
3
votes
0answers
2k views

Querying Oracle via Linked Server crashes SQL Server

A bit of an odd one this. I have a couple of instances of SQL Server 2012, SP1, one being Dev Edition and the other Standard Edition and both are VMs running under VMWare. When executing a Stored ...
3
votes
0answers
2k views

Linked server error when querying one table

I am running 2 queries using the same linked server. SELECT * FROM [SERVER].[Customer].[dbo].[ccr_AxCust1] SELECT * FROM [SERVER].[Customer].[dbo].[ccr_AxCust2] For Query 1 this returns results fine,...
2
votes
0answers
30 views

Linked Server: An error occurred during decryption

We're running a SQL Server instance on AWS RDS. I believe this is a 2017 instance. We also have a couple of Linux EC2 instances running SQL Server (not sure the version, but I assume 2017) Our RDS ...
2
votes
0answers
83 views

Linked server from Azure managed instance

I have my Managed Instance (MI) setup and can connect from SSMS on-premises. There is limited functionality this way (you only see databases and security) - so I have a VM in the v-net where I can ...
2
votes
0answers
67 views

BulkAdmin Role Doesn't Work

There is a query that our BI team use. They can not execute the query because of the error below. Ad hoc access to ole db provider 'MSDASQL' has been denied. You must access this provider ...
2
votes
0answers
128 views

why is update from a join containing zero records taking a minute and 20 seconds?

SQL Server 2008R2. I have a simple join that returns zero rows in less than a second. When I try to run an update on that join, it should also take less than a second but it is taking one minute and ...
2
votes
0answers
277 views

Secondary Replica Read-Only Routing via Linked Server working from one server, but not the other

I have been given the task of setting up a read-only linked server on ServerA to Availability Group listener ListnerX. ListnerX points to ServerY or ServerZ (ServerY is usually the primary). Read-only ...
2
votes
0answers
75 views

Linked servers not ignoring case after reboot

We have a SQL Server 2014 database with collation SQL_Latin1_General_CP1_CI_AS, which is case insensitive. It is linked to another SQL server database with the same collation. The second database ...
2
votes
0answers
87 views

Linked Server ODBC statement missing delimiters on reserved identifier

INSERT INTO SOMESERVER...[SomeTable] ([ID], [Timestamp]) VALUES (746, 1504500000) This statement is missing delimiters for the identifiers in an ODBC trace. "INSERT INTO SomeTable ( ID,Timestamp) ...
2
votes
1answer
1k views

Unable to get output variable of Oracle procedure using Sql linked server

I have created a Heterogeneous connection between SQL Server and Oracle and tried to call a oracle procedure from SQL Server.I got below error when i tried to execute the procedure. OLE DB provider ...
2
votes
0answers
117 views

Permissions on views/stored procedures and the referenced base tables in linked servers

I need help setting up security division between SELECTing and EXECUTEing from views and stored procedures and access to base tables referenced by those objects on linked servers on SQL Server 2012SP3....
2
votes
0answers
71 views

TVF Poor Performance with Linked Query

I have an inline TVF that take much longer to run than should be necessary. The issue looks to be the Linked Server reference. The Linked Server is SQL Server 2012. The server with the TVF is SQL ...
2
votes
0answers
182 views

SQL Server 2008R2 Linked Server to SQL Server 2000 showing incorrect server

I have 2 servers and 4 instances Server 1 (SQL Server 2008R2) has instance a,b,c Server 2 (SQL Server 2000) has instance d I created a linked server from server 1 instance c to server 2 instance d. ...
2
votes
1answer
1k views

Security risk of Enabling MS DTC Distributed transaction?

I'm doing data exchange and Insert update operations over the linked server between two database servers. To allow distributed transaction, I've changed settings in Windows to enable MS DTC services. ...
2
votes
0answers
275 views

security concerns over enabling data access for the current server

Included in the code of the creation of some stored procedures, I have the enabling of data access as you can see below. BEGIN TRY PRINT @@SERVERNAME PRINT DB_NAME() PRINT 'enable the ...
2
votes
0answers
424 views

Performance Implications of Explicit Collation Conversions across Linked Servers

I locally host host databases from two vendors on two different SQL instances, but on the same network. These servers frequently communicate with each other (through joins and whatnot), so we have ...
2
votes
0answers
786 views

SQL Server 2008 Linked Servers Consistently Dropping Connections

I have set up a Linked Server between 2 SQL Server 2008 R2 databases (both 10.50.1600). The linked server worked normally for several weeks until I received reports that users were not able to hit ...
2
votes
1answer
351 views

SQL Server: efficient query with (double) linked servers

I am DBA of server A (SQL Server 2008R2), linked to server B (SQL Server 2012SP1), linked to server C (not sure which version). I have no control on server C, and I cannot contact its DBA. On server B,...
2
votes
0answers
489 views

Multiple login mappings for linked server

Can we assign two logins for two different databases on target server. For example, I would like to create a Linked Server between ServerA and ServerB. ServerB has two databases on it and both using ...
2
votes
0answers
615 views

Linked Server Login Timeout but SqlCmd works

I've got a SQL 2005 SP4 server that connects to a 2008 SP3 instance via linked servers using the SQL Server server type. Every once in a while, one of those linked servers will start throwing login ...
2
votes
0answers
643 views

Linked server issue

I made a linked server between two sql server instances. The remote one I have very little control over. When I try a query I get this: Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for ...
1
vote
0answers
50 views

Check linked server database status registered in the DBConfig Database

I am working with the Code below to check all the entries in the XXX Database of all the databases linked to the instance Server- XXX DB _YYY However when i run the Code, it throws an error i ...
1
vote
0answers
32 views

Linked server in Sql Server 2014 invalid path

I am trying to create a linked server to foxpro tables using vfpoledb, but it gives me the error of "Invalid path or file name" when trying to put as source \\xxx.xxx.x.x\same_names\abc (xxx.xxx.x.x ...
1
vote
2answers
322 views

How to fail over availability groups on multiple remote servers

We have many SQL Server installations with availability groups. During our maintenance window, we will need to fail over all the HA groups to secondaries, patch the primary servers, then fail back, ...
1
vote
0answers
208 views

Database not showing in sys.databases over linked server

I have a query that I can run on my server (Server1) as follows: SELECT * FROM master.sys.databases WHERE name = 'MyDB' This returns a row. I have a linked server setup on Server2 which points to ...
1
vote
0answers
177 views

SQL Linked Server Connection Success but simple query returns Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

I linked a SQL Server in Management Studio and when I test the connection, I do get "the test connection to the linked server succeeded." However, when I try to run a simple query, I get a message ...
1
vote
1answer
37 views

FILEPROPERTY not working over linked server to Azure SQL Database

FILEPROPERTY (Transact-SQL) is not working with a linked server pointing from a on-Premise SQL 2017 to a Azure SQL Database. I see the following comment in books online: Returns NULL for files that ...
1
vote
1answer
45 views

Linked Server Configuration using remote user logins

I'm trying to configure a Linked Server using remote logins reducing who has access to the linked server by taking advantage of the not be made option. For test purposes alone, I have used SA, ...
1
vote
0answers
552 views

Grant permissions over linked server

I need to grant select permissions to a sensitive table over the linked server. This table should only grant access to members of an AD group. I currently have other linked servers set up with remote ...
1
vote
0answers
32 views

Multiple queries to linked server (Access db) block Sql server

On a SQL server 2012 I've created a linked server pointing to Access database: It all works fine, unless we have more than one query to this server - queries that are not even related to the same ...
1
vote
0answers
249 views

View with OPENQUERY won't build in SSDT project

I have an existing database that I'm trying to set up as a database project. I'm not able to get the following statement to build: CREATE view [dbo].[VW_SOMEVIEW] as SELECT * FROM OPENQUERY ([...
1
vote
0answers
630 views

Linked Server transaction stopped by MSDTC

We've recently installed the newest Microsoft OLE DB Driver for SQL Server (msoledbsql) as it now supports multi-subnet failover capabilities (detailed here: - https://blogs.msdn.microsoft.com/...
1
vote
0answers
578 views

Linked server from SQL server on AWS RDS to On premises Oracle database

We are planning to migrate an on-premises Microsoft SQL server database to AWS RDS SQL server. Everything is good except one roadblock, i.e this SQL server database has a linked server to on premises ...
1
vote
0answers
57 views

Decoding timestamp number in “change in schema version between compile time and run time”

I am trying to track down a "who/why" for the error <...> reported a change in schema version between compile time ("185302088475260") and run time ("185448117384521") for table <...> I ...
1
vote
1answer
498 views

Cannot run a job to update table on a linked server

I am running SQL Server 2012 on a server (server 1) and have set up a job to run a stored procedure which will summarize data from a series of tables and update a table on a linked server (server 2) ...
1
vote
0answers
25 views

sp_start_job only running 20 times concurrently - can I adjust?

I have a procedure that has a loop that calls sp_start_job to run a query against multiple linked servers. Each run of the loop calls a job that queries a specific linked server. (An openquery that ...
1
vote
0answers
339 views

Connect ORACLE to SQLSERVER to REMOTE SQL SERVER

I have Sql server and oracle installed on same machine. So I created a heterogeneous connection between the two server. Now I need to do some transaction in my sql server and need to send the result ...
1
vote
0answers
291 views

Linked server select Openquery causing datetime column to be an hour behind?

I have a SQL 2014 server which has a linked server to an Actian Ingres database. When I run a simple linked server select openquery on any table with date/times, the results it pulls back is an hour ...
1
vote
0answers
335 views

Linked Server details cached?

I am trying to resolve a problem for a customer. They have a job which calls a stored proc on Server A, which calls a stored proc on Server B, via a linked server called RefreshDB. The linked server ...
1
vote
0answers
538 views

Occasional Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error

I know that this issue has been discussed elsewhere and I already read it. Our problem is that this occurs only once a day (roughly), the rest of time everything works fine. SERVER RS Windows Server ...
1
vote
0answers
252 views

Unable to establish linked server connection to SSAS 2008 R2 server

I'm interested in gathering some DMV data from our SSAS cubes and store it on our management box. When I create the linked server for this effort I run into an error establishing the connection. ...
1
vote
0answers
356 views

Handling exceptions with INSERT-EXEC with linked server stored proc and table results

I am trying to capture failures for a remote (via linked server) stored procedure called within a local stored procedure. If the remote procedure fails (error connecting to server, etc) I want the ...
1
vote
0answers
337 views

Connect to a SQL Server Mirror from Oracle Database

I know we can create a database link to connect to a Microsoft SQL Server from Oracle database. But the problem which I faced with is the SQL database which I want to connect to has a mirror fail-over ...
1
vote
0answers
90 views

Unable to get anything other than 'Remote scan' when selecting from linked MySql server

I have a linked server in SQL Server 2012 (SP3) that points to a MySql 5.1.61 server on another machine. Whatever I do, I don't seem to be able to get any where filtering to occur on the remote side. ...