Linked server allows operation to multiple server as one query.

learn more… | top users | synonyms

0
votes
0answers
17 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
0answers
20 views

Linked server to Availability Group Listener

I am setting up a SQL 2014 AAG (2 replicas - HASQL1, HASQL2) on Windows Server 2012R2. I created an availability group (call it HAG) on them, and added a database (hadb). I set up a listener (call ...
0
votes
1answer
962 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 ...
0
votes
2answers
50 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
17 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
20 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
261 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
25 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 ...
3
votes
1answer
157 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
159 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
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
0answers
30 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
13 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. ...
1
vote
1answer
588 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 - ...
0
votes
1answer
3k 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' ...
3
votes
1answer
17 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 ...
3
votes
2answers
613 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 ...
0
votes
0answers
25 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
12k 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
32 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'. ...
13
votes
3answers
35k 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
105 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
38 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
849 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
18 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
55 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
416 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
24 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
46 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
63 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
149 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 ...
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
55 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
71 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
23 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 ...
0
votes
2answers
72 views

Linked Server Error on SQL Agent Job

I get the below error from a SQL Server Agent Job. However, if I run the query in the job manually it works fine. Why is this? Is it just SQL Agent that requires MSDTC? I don't believe it is ...
1
vote
0answers
65 views

SQL Server Linked Server with Microsoft OLE DB Provider for Visual FoxPro?

We were having some issues with our old Microsoft Visual Fox Pro ODBC driver that we shoehorned into Windows 7 and is no longer supported by Microsoft and has not been for quite a long time... So ...
1
vote
0answers
29 views

Bypass stored procedure execution CPU consumption

We are using Microsoft SQL Server Agent Jobs to execute stored procedures on the other database systems (like MySQL or Oracle). Usually we run procedures via linked servers (over OLEDB and ODBC ...
0
votes
1answer
14 views

Catalog of LinkedServer2 from Server1 shows databases on Server1

I'm on Server1 and set up a linked server to LinkedServer2, but the catalog shows the databases on Server1! I am sure I've done something bone-headed or am overlooking something horrendously obvious, ...
1
vote
1answer
161 views

How to pass credentials to a linked server in another domain?

I am trying to link two SQL Servers (2008R2 and 2014) which are running in two separate (!) Active Directory domains. The problem is that I can login into the remote server only with a Windows ...
1
vote
1answer
67 views

SSMS connects, Linked server fails

There's a SQL Server 2012 machine which I have login and password. It's a SQL Server login, not an AD one. I'm able to login using SSMS. But when I configure a linked server in my SQL Server I get ...
0
votes
1answer
27 views

Updating linked server with temp table join

I am performing the following operation: Update A set A.col1 = B.col1 From LinkedServer.LinkedDatabase.dbo.remoteTable A JOIN #localTempTable B on B.column = A.primaryKeyColumn The performance is ...
2
votes
2answers
23 views

Most restricting permissions for querying view with linked server

I've created view to query another server via linked server(SQL Server 2014). I need to grant permissions to developers to use this view. Granting only select is not enough and they got the ...
1
vote
3answers
66 views

How do I handle multiple rows with variables defined by a query?

I am trying to write a query that uses variables to feed into a sub query. Query: DECLARE @Code VARCHAR(5) DECLARE @Sys INT DECLARE @Key INT SET @Key = 12345 SET @Code = (SELECT Code FROM ...
0
votes
0answers
87 views

Why does OpenQuery hang for hours before raising Query timeout expired when query timeout on Linked Server is set much shorter?

In production have two SQL 2012 instances on separate servers, with a Linked Server set up between them. No problems there. In development, I have the databases for both in one 2012 instance, and have ...