Linked server allows operation to multiple server as one query.

learn more… | top users | synonyms

0
votes
1answer
24 views

INSERT INTO SQL Server : Linked Server without specifying database in semi-fully qualified name

I have a server called DEV, a database called Nus12345, a linked server called SnapshotDev, a schema called Snapshot, and a table called SnapshotPersonEmployee. On the DEV server, there are many ...
4
votes
1answer
172 views

SQL Server - monitor changes on linked server

We have a local SQL Server database which we have full control over, and in this local server we are linking a remote SQL Server database, to which we have only read access (no schema changes, no ...
1
vote
1answer
35 views

Create a Linked-Server from SQL Server 2008 R2 to AWS RDS SQL Server

How do I correctly create a linked-server entry, from an on-prem SQL Server, to an AWS RDS SQL Server instance? On-prem is 2008 R2, AWS-RDS is running 2012. This is the error I get when attempting ...
0
votes
1answer
30 views

Linkserver update not working in Transaction

I am trying to do operation on my linked server. I have created one linked server and everything is ok. I just observed one thing which I am not able to understand. if I run: update SERVER.testdb.[...
12
votes
5answers
8k views

Why do linked servers have a limitation of 10 branches in a CASE expression?

Why does this CASE expression: SELECT CASE column WHEN 'a' THEN '1' WHEN 'b' THEN '2' ... c -> i WHEN 'j' THEN '10' WHEN 'k' THEN '11' END [col] ...
1
vote
1answer
168 views

Best way to transfer a few tables from reserve server to production

We have two MS SQL Servers (2012 Standard Edition on Windows 2012 Server) which are located in the same subnet. One is production server. Another is reserve server. Currently there is a ...
0
votes
1answer
43 views

When and Who created linked server?

I came across a linked server that is pointing to production from test environment with full access. Is there a way to find out who and when this linked server was created? Based on last modified date,...
0
votes
1answer
1k views

DB2 Linked Server on SQL Server 2012 - Grant Access to Users

I have a linked server on SQL Server 2012 instance pointing to a DB2 database and I have Sys Admin access to that instance. I am unable to test connection to that DB2OLEDB database as I get below ...
0
votes
1answer
1k views

How to query a server linked via ODBC

I've looked at multiple other questions about querying linked servers, but nothing has worked for my setup. Notes: I'm not using localdb at all. I just wanted to use SQLSMS for a remote cache ...
1
vote
1answer
622 views

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

I have two linked servers and want to create a store procedure that simply SELECT data from one table from the first server and INSERT the data in other table from the second server. The procedure ...
0
votes
1answer
4k views

Cannot create an instance of OLE DB provider “OraOLEDB.Oracle” for linked server

i am having a bit of a linked server problem . I created a sql server linked server and it's been working fine for last 2 years and suddenly not working . SQL Server Microsoft SQL Server 2005 - 9....
1
vote
2answers
38 views

Linked Server works on SSMS and isn't found on SSDT

My Microsoft SQL Server has a linked server to another SQL Server. I have a simple query that runs on mine, deleting a remote table (truncate doesn't work) and inserting data to it. This query works ...
0
votes
1answer
4k views

SQL Server - linked Oracle server: EXECUTE permission denied on 'xp_prop_oledb_provider'

Inside of our SQL Server, we have created a linked server to an Oracle server. We ave mapped several users to the Oracle server and specified the credentials. On the Linked Server 'Security' ...
4
votes
2answers
627 views

Security for Oracle linked server from SQL Server

I'm trying to make the linked server from SQL Server 2005 to Oracle more secure by not having user/pass on "Be made using this security context:" fields so only a specific user can access the data ...
1
vote
1answer
83 views

Linked server to Availability Group Listener - is anyone successfully using one?

I edited the question hoping to check the basic stuff first. Does anyone have linked servers that successfully work with Availability Group listener? Have anyone heard of problems with linked servers?...
0
votes
1answer
31 views

Accessing a Linked server [closed]

I am fairly new to the whole DBA scene. With that said, i was able to get an ODBC connection established within Microsoft SQL Server 2012 to be able to access a database for a library. Now what I am ...
2
votes
1answer
40 views

Query Exceeding Query Timeout on Linked Server and Working

Currently we have a SQL Server Agent job that executes a stored procedure that reads data over a linked server. Initially on our test server this was timing out at 10m:17s (617 seconds). We changed ...
0
votes
0answers
16 views

MS SQL, MySQL linked server distributed transactions

I have a linked mysql server object that I am accessing through MS SQL. I'm unable to execute any 'OpenQuery' queries to the MYSQL inside a trigger because I get the following error message: Msg ...
0
votes
1answer
39 views

How to connect to a password protected Access 2010 database from SQL Server 2014 x64

I have the following situation: An Access 2010 database (.accdb) on a shared folder of a remote server. A domain account that can access the shared folder and files. A SQL Server 2014 x64 running on ...
0
votes
1answer
38 views

OLE DB provider “MSDASQL” for linked server “[name]” was unable to begin a distributed transaction

I have a linked server setup from SQL Server Ent 2012 to PostgreSQL 9.3 and a stored procedure pulling 3 filtered tables from it and storing each in a temporary table. There is a final query at the ...
1
vote
1answer
40 views

Best solution to keep a big table synched in 2 DB

I manage a few tables with more than 1 billion records. Daily, their ETLs run, reading data from flat files and inserting a few thousand records on each of them. Now I need to keep these same tables ...
0
votes
1answer
64 views

Extracting data from SQL Server linked server

I am using MS SQL Server 2012, and I am trying to extract data from a Linked Server database. In SQL Server management studio, I can see the linked server. I can browse the database on the server. I ...
1
vote
1answer
22 views

The call to LOADLIBRARY for the XA resource manager DLL failed (PostgreSQL & SQL Server)

I am attempting to setup a Linked Server from MS SQL Server 2012 to PostgreSQL 9.3 via Linked Servers & ODBC driver from PostgreSQL. Everything works, until a given query invokes MSDTC, at which ...
4
votes
1answer
2k 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 ...
0
votes
0answers
29 views

How to set up repl_distributor for data access

I need to run queries on the distributor server from the publisher during deployment in a transactional replication setup. I see repl_distributor is set up as a linked server, but am having trouble ...
2
votes
1answer
267 views

Error updating zoned field from SQL Server stored procedure linked server to iSeries

I'm trying to update a table on an iSeries (AS400) within a SQL Server stored procedure using linked server, but it is failing when trying to update a column defined as zoned. In my stored ...
0
votes
0answers
34 views

Distributed transaction debug

I have the classic error: Msg 7391, Level 16, State 2 When I try to execute a transaction over a linked server. I have the same identical configuration in 2 environment, production and test. It ...
1
vote
0answers
14 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. ...
3
votes
1answer
20 views

Run Built-In Function on linked server

We have two SQL 2014 servers, and I have created a Linked Server on one of them to the other. As part of a task I'm trying to run I need to convert a SQL UserID to a username on both the local and the ...
0
votes
0answers
29 views

Connecting 2 databases using sp_addlinkedserver

I have 2 servers and I want to connect to them using this: exec sp_addlinkedserver 'myserver1.dyndns.biz'; exec sp_addlinkedsrvlogin 'myserver1.dyndns.biz' , 'FALSE', NULL, 'remoteuser', '...
2
votes
2answers
13k views

How to fix “The OLE DB provider ”MSOLAP“ has not been registered”?

Here's the code I'm using to register a linked server to SSAS: USE [master]; GO EXEC master.dbo.sp_addlinkedserver @server = N'ServerAlias', @srvproduct = N'', @provider= N'MSOLAP', @...
0
votes
1answer
43 views

OLEDB provider for linked server reported change of schema version error

I am trying to run a report but its giving this message: An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'ParamYear'. (rsErrorExecutingCommand)...
14
votes
3answers
37k views

Pivot rows into multiple columns

I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data: ╔══════════╦══════════╗ ║ ...
0
votes
1answer
213 views

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

I am using SQL Server 2014 linked server to connect to an Oracle 11gR2 (11.2.0.3) database, using the ODAC 12.1.0.2.4 Oracle OLE DB Provider. I have a stored procedure that lives in a SQL 2014 ...
0
votes
0answers
45 views

SSDT - Unresolved Reference to temporary table in linked server

Here's the scenario. I am extracting data from a table in an Oracle database via a linked server as part of an ETL process. I am developing the solution in Visual Studio 2013 with SQL Server Data ...
3
votes
1answer
965 views

SSDT project with working linked server references gets SQL71562 warning

I have a VS 2013 solution with two database projects. One of them, let's call it the "worker" is supposed to get deployed to about 10 different production servers. The other one, that is supposed ...
4
votes
4answers
15k views

Linked Server to SQL Server 2000 server from SQL Server 2012

I have a new SQL Server 2012 instance, and am migrating a database from SQL Server 2000. Some of the stored procedures in the DB I am moving reference another database back on that 2000 server. What ...
0
votes
1answer
27 views

Can't list columns or query in MS Access 2003 linked server in SQL Server 2008 R2 using Microsoft Jet 4.0 OLE DB Provider?

I can however list the tables... If I run Exec sp_tables_ex 'LSTest' I get a result with a list of tables. But if I run Exec sp_columns_ex 'LSTest', '<one of those table names>' I get an ...
2
votes
1answer
105 views

How do I guarantee using Microsoft ODBC Driver 13 for SQL Server in a linked server?

On SQL Server 2016, how do I guarantee using Microsoft ODBC Driver 13 for SQL Server in a linked server? I don't mind there being another layer in there, such as the MSDASQL provider, but I do want ...
3
votes
1answer
450 views

OPENQUERY and server permissions

when I run my stored procedure it is a complete success, even if I run it through exec. Running these: sp_GetServerLogins GO exec ( 'sp_GetServerLogins ') GO I get this: Needless ...
0
votes
0answers
27 views

Troubleshooting Case Sensitive linked server identification

Good afternoon, A few of our 2008 SQL servers were recently migrated to fresh 2014 installs, and I'm having trouble identifying where a breakdown is occurring in the linked server setup because of ...
3
votes
1answer
1k views

Create Linked Server to AlwaysOn Availability Group Listener

I have an alwayson availability group setup using SQL 2014 and Windows Server 2012 R2. There are 3 nodes in the group. The SQL Service is running as a Managed Service account on each node (1 MSA for ...
3
votes
1answer
50 views

What sort of SPN / AD Computer Delegation do you need for delegation of credentials to a linked server that connects to a file share?

The idea here is to perform a double hop and delegate the credentials of the client (leeand00@example.org), that have been sent to the SQL2.example.org server to SQL3.example.org (admittedly this is ...
1
vote
1answer
84 views

Create Linked Server using Active Directory account

I was able to grant access in MSSQL to an Active Directory group and add a service account to that group. It's working fine, I'm able to use trusted connection to login on that MSSQL using both my ...
1
vote
1answer
164 views

SQL Server: How to export database with linked servers

I'm a web developer and I want to export a database to work on it in my local machine. I'm using generate script with data. the script is generated but without the linked servers used by some Views. ...
2
votes
1answer
37 views

querying sys.database_principals accross a linked server

I'm attempting to get a listing of the users and roles they are associated with on a remote server to be input into a database on the local server. But when I use the linked server sys....
2
votes
2answers
6k views

Using OPENQUERY to execute a script

I have a SQL Server 2008 instance which I want to use to import data from an Oracle server. I have set up a linked server that works correctly when running simple queries like SELECT * FROM table. ...
1
vote
0answers
58 views

How can I reduce OLEDB wait type in a SSAS linked server connection?

I have recently created a linked server to one of my SSAS servers. when I go ahead and open the catalogs, to see which ssas databases I have there, I use the following procedure: create procedure ...
2
votes
1answer
82 views

powershell problem when running on a remote server - access is denied

as a follow up from this question below: Run a PowerShell script on a different server - from inside a SQL Server job there you can see that for me tu run a powershell script from inside a sql ...
1
vote
0answers
24 views

How to identify a process that has been cancelled?

How can you identify a process that has been cancelled? for example: I was running this dynamic sql - via a linked server: declare @sql nvarchar(max) declare @i int declare @z int DECLARE @vCrlf ...