Questions tagged [linked-server]

Linked server allows operation to multiple server as one query.

Filter by
Sorted by
Tagged with
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
1answer
860 views

Alter stored procedure - linked server not connected

I'm trying to change a stored procedure that is used to updates data in a local and a linked server. "UPDATE [PcName].[DatabaseName].[dbo].[TableName]" As both servers are available the stored ...
1
vote
1answer
655 views

Linked Server already exists in sys.server

I created Linked Server in ABC server and I can test the connection successfully to Destination server XYZ (Which is part of AOAG). I created a Linked server from ABC --> XYZ . I used AOAG Listener (...
2
votes
2answers
376 views

How to improve performance of SELECT query to same tables in Linked Server

I have a SELECT query to a linked server. It looks similar to this SELECT @Var1 = (SELECT COL1 FROM [LinkedServer].[dbo].[TableA] WHERE <...
1
vote
1answer
705 views

Getting error “String or binary data would be truncated” even for 'text' data type

I have created a linked server (PostgreSQL). When I try to import data from SQL Server to linked server I get the following error: String or binary data would be truncated Even though I am ...
0
votes
0answers
410 views

OLE DB provider “MSDASQL” for linked server returned message “ERROR: reserve exceeded : Out of memory ”

We have SQL Server 2012 database which pulls data using a linked server to a Netezza database. We have had a memory issue reported in an overnight batch. Error message: OLE DB provider "MSDASQL" for ...
0
votes
2answers
353 views

Adding sql users via linked servers

I need to add a windows user and set role to sysadmin for a bunch of linked servers and cannot figure out the syntax to make the current linked server active for the query to add the user and role. ...
2
votes
1answer
487 views

Deferred Name Resolution for Linked Servers

I need to be able to create stored procedures that point to a linked server where the database does not yet exist on the linked server. The thought process is that the above mentioned stored ...
1
vote
1answer
160 views

Field Conversions With Linked Servers

I have a postgres database linked to SQL Server. I have found you can use OpenQuery to query postgres from the Sql Server side, but one caveat I have found is that when creating views all fields are ...
0
votes
4answers
601 views

TRIM() Function With Linked Server

I have a linked server connection for a postgresql into sql server. I can run this in Sql Server Select * from OpenQuery(LinkedServer,'Select * from alphadawg') and this returns all results, but the ...
21
votes
5answers
4k views

Why is this explicit cast causing problems only with a Linked Server?

I am querying data from a linked server through a view on the origin server. The view has to include a couple of standardized columns, such as Created, Modified and Deleted, but in this case the table ...
-1
votes
1answer
189 views

ODBC Linked Server in SQL 2014 Express Server Data Truncated

I have been trying to collect data from ODBC Server into my SQL Server 2014 Express. I am successfully able to run the query: select * from openquery(test,'SELECT * FROM Sample/hstrh3.current') ...
1
vote
1answer
1k views

How to avoid Msg 9514, Level 16, State 1, Line 3 error for linked server?

I have a query to compare data between my local SQL Server and linked SQL Server. My problem is that the table I compare contains XML column. I get: Msg 9514, Level 16, State 1, Line 3 Xml data ...
0
votes
0answers
490 views

How to deal with linked server in SQL queries

I am creating a new database which will connect to several databases via linked servers. The problem I am trying to solve is that I have development, test and production environment with different ...
0
votes
0answers
46 views

Linked Server directly to database

Say I have a query like this: select * from openquery ([linkedservername],'select * from [databasename].dbo.person') Is there a way of omitting [databasename].dbo.? I have an enormous query I want ...
0
votes
0answers
3k views

OraOLEDB.Oracle not showing up in Providers in Linked Server

Im trying to link the Oracle database to our SQL Server database. However OraOLEDB.Oracle is not in the Providers list on the Linked Servers. I have installed Oracle Database Client (12.1.0.2.0) for ...
-1
votes
2answers
378 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 ...
1
vote
1answer
734 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.[...
0
votes
1answer
2k 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,...
2
votes
1answer
2k 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
85 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 ...
1
vote
2answers
287 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 ...
1
vote
1answer
593 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 ...
2
votes
1answer
1k 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 ...
2
votes
1answer
1k 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
3k 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
227 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 ...
1
vote
2answers
1k 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
0answers
965 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
85 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. ...
0
votes
1answer
1k 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 ...
4
votes
1answer
967 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
1answer
844 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)...
1
vote
1answer
643 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 ...
4
votes
2answers
9k 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
1answer
335 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
2answers
6k 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
473 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 ...
3
votes
1answer
5k 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
3k 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
163 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....
1
vote
0answers
1k 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
97 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
16 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, ...
0
votes
2answers
1k 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 ...
0
votes
1answer
427 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 ...
0
votes
2answers
921 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
192 views

SQL Server Partially Contained Databases and Linked Servers

What is the work around for using linked servers with partially contained DBs? Additionally, how would this work with Always on and a secondary replica?
2
votes
0answers
272 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 ...
1
vote
2answers
142 views

Linked servers - Upgrading from 2005 to 2012

Best way to find other server are linked to 2005 SQL Instance as I am migrating all to 2012 SQL Server. I will migrate all linked server that is linked to 2005 SQL Instance but what is the best way ...