Linked server allows operation to multiple server as one query.

learn more… | top users | synonyms

3
votes
2answers
452 views

Unable to query SQL server metadata through linked server

I have a scenario where a user wants to do the following through a linked server: SELECT * FROM [DBTEST].[AdventureWorks].sys.objects WHERE object_id = OBJECT_ID(N'dbo.DatabaseLog'); The linked ...
3
votes
1answer
3k views

Insert to SQL Azure through Linked Server very slow

I have a table on SQL Asure server with no indexes except clustered. And I insert into this table small amount of data just about 1000 rows and it takes more than 4 minutes. Although, when I do it ...
3
votes
1answer
858 views

Inserting a 30 million row table from a linked server

I have a large table that needs to be updated on a nightly basis from a linked server. Initially I had simply truncated the table and inserted all of the records but this was taking too long. I'm ...
3
votes
2answers
22k views

How can I enable distributed transactions for a linked server?

I have a SQL Server 2012 instance with a linked server pointing to a db2 database on an IBM Iseries system using the System i Access ODBC Driver. I'm trying to enable distributed transactions. I ...
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 ...
3
votes
1answer
62 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
217 views

How to Replicate data between a hosted database and a local database

I have a Cloud hosted Database, maintained by a different company. Here is my scenario: Cloud Hosted Database SQL Server 2012 - Need to connect through a VPN. We setup a local Server with with ...
3
votes
1answer
676 views

Add linked server from SQL Server 11.0.5058 to SQL Server 10.50.4000 [closed]

I tried to add a linked server running on SQL Server 2008 R2 (10.50.4000) from SQL Server 2012 (11.0.5058). I keep getting this error: The linked server has been created but failed a connection ...
3
votes
2answers
10k views

SQL Server: Frequent Login timeout expired in Linked server

I am using SQL Server 2012. I have scheduled a job to run every 1 hour, with 1 step calling 1 simple Stored Procedure. The Stored Procedure migrates around 1K data from production server to our local ...
3
votes
1answer
2k views

Loopback Linked Server

Is there any benefit in use a Loopback Linked Server, maybe in a security or performance level, because I really don't get the idea ? I google it for several minutes and I find nothing about it.
3
votes
1answer
2k views

Linked Server to Mirrored Database Fails

I've setup up a mirrored database on two servers Server1 and Server 2. On Server3, I'm trying to setup a linked server that will work regardless of which server has the active database. I'm using ...
3
votes
1answer
1k 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 ...
3
votes
1answer
1k views

Update Sql Server from Mysql

We have a website that stores form data in a web hosted MySQL database. Whenever form data is entered into the MySQL server tables I need update all necessary tables on our local SQL Server database. ...
3
votes
2answers
374 views

Sudden Query Performance Degradation On A Linked Server

I have an application that uses a view on a SQL Server 2008 R2 instance. This view actually queries a linked server (SQL Server 2008) that has replicated data from our main ERP database. Yesterday, we ...
3
votes
2answers
6k views

Access Linked Server error “The provider did not give any information about the error.”

A little information. I'm running SQL SERVER 2008 R2 64bit. I have Microsoft.ACE.OLEDB.12.0 provider installed. On my production server I've setup a linked server to the access db using the provider ...
3
votes
1answer
2k views

Which timeout(s) are causing my linked server query to time out?

We have two databases on linked SQL Servers that are partially synchronized. Occasionally, one side goes down for an upgrade, then the other goes down for an upgrade, and then we re-synchronize. ...
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
477 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 ...
3
votes
1answer
89 views

Change linked server login per-query

Given the situation below, can I specify that the pre-configured linked server login should not be used for a particular remote stored procedure call? I have two SQL Server instances: [LOCAL] ...
3
votes
1answer
3k views

“Prelogin failure” when attempting to run stored procedure across linked server

I'm getting the below error when attempting to run a stored proc across a linked server that is going from Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) to Microsoft SQL Server 2008 (SP2) - 10....
3
votes
2answers
99 views

Modify linked server definition (Oracle) in SQL Server 2012

I have created a linked Oracle server in SQL Server 2012 using Oracle Provider for OLE DB and TNSNAMES.ORA (the Oracle database is clustered and, as far as I know, no other connection method works ...
3
votes
1answer
71 views

Error 7399 when the Access file has a password

I'm trying to create a linked server to access an old MS Access database (.mdb file) on SQL Server 2012, but I always get the error 7399 (authentication denied). So I tested with another mdb file that ...
3
votes
1answer
959 views

Connecting Linked Server

Hi I want to fetch data from MS SQL DB of a Remote server using Linked Servers. I have linked the servers. I am fetching data as follows. select * from [IP of server].[Database Name].[dbo].[Table ...
2
votes
2answers
4k views

32-bit ODBC driver on 64-bit SQL Server [closed]

I am unable to use an ODBC 64-bit driver with SSMS 2012 running on a SQL Server 2012 64-bit instance. However, I believe that the 32-bit driver works. My question is can I use SSMS 2005 32-bit to ...
2
votes
1answer
4k views

Why does SQL Server fail to create a procedure referencing a linked server?

Red Gate Deployment Manager (RGDM) today failed with a "login failed" error even though it had administrative control of the target server: 2013-11-04 17:13:22 +00:00 INFO Creating [dbo].[...
2
votes
2answers
4k views

Execute a stored procedure via a Linked Server from SQL Server 2008 R2 to SQL Server 2014

We are in the planning stages for an upgrade from SQL Server 2008 R2 to SQL Server 2014. Our system executes stored procedures against different databases on different servers. If we upgrade ...
2
votes
2answers
4k views

Creating a Linked Server that Points to Itself

I am attempting to create a linked server on SQL Server 2014 instance servername\instancename using the following call: EXEC master.dbo.sp_addlinkedserver @server = N'servername\instancename', ...
2
votes
2answers
7k views

Help with SQL Server Error “ad Hoc access to OLE DB Provider 'Microsoft.ACE.OLEDB.12.0'”

So we have an interesting issue where users are receiving the following error when attempting to query a view in SQL Server 2008 R2: Msg 7415, Level 16, State 1, Line 1 Ad hoc access to OLE DB ...
2
votes
2answers
3k views

SQL Server 2008 linked server to an Access 2007 DB that is in a protected directory on the Network

We have a SQL Server 2008 database that we are attempting to link with an Access 2007 DB (the maintainer works down the hall and refuses to migrate to SQL Server). We have the password to the database ...
2
votes
2answers
2k views

List Active Directory Users

My boss sent to request me that: I want to a SQL query, that lists all domain users of a specific group. I began to search query. I found several solutions using Linked Server. EXEC master.dbo....
2
votes
2answers
7k 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. ...
2
votes
2answers
285 views

Pull Access schema into a SQL Server table

We had an assembly that used to pull a list of tables and their columns from an Access database and place the listing into a SQL Server table for later use. For various reasons the assembly is no ...
2
votes
1answer
5k views

Msg 7391, Distributed Transactions (DTC) on SQL Server

This may be DBA related or developer related, I honestly don't know. I have two Database servers: A: SQL Server 2005 (Trigger that uses DBMail on Server B) B: SQL Server 2008 R2 (DBMail configured) ...
2
votes
1answer
4k views

Cannot fetch the rowset from OLE DB provider “SQLNCLI” for linked server X

I am getting this error while doing concurrent read from same table through linked server. Time out setting is alright. When this error happens, I also start getting the following error for the same ...
2
votes
2answers
3k views

Query data from multiple linked remote databases

Using SQL Server 2005 databases and SQL Server Management Studio 2008, I need to query a single value from each of n linked databases at a regular interval. The servers are physically remote and I can ...
2
votes
1answer
39 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
5k views

Run “USE” with linked server alternatives

I am using SQL server linked servers and find some difficulties while running queries. Below is the actual query. USe testdb SELECT 'Identitiy property OFF' where COLUMNPROPERTY( OBJECT_ID('Table_1')...
2
votes
2answers
35 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 ...
2
votes
1answer
96 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 ...
2
votes
1answer
313 views

Using a Linked Server to import data from Excel

I have read a lot about linked servers being bad when there are multiple hops across servers and distributed joins. What if I am just using a linked server to import data into SQL Server from an ...
2
votes
1answer
96 views

SQL Server 2012 - Disable Job On Linked Server

I have a job on server B, which is linked to server A. Is it possible to disable a job that exists on server B in the context of server A? I have tried the following command from server A and receive ...
2
votes
2answers
173 views

How can I query a remote server using a local function?

I am querying a remote server for which I only have read permissions. I set up a local server with a UDF (since I cannot create one on the remote) that I would like to utilize for the query. When I ...
2
votes
2answers
1k views

Stored Procedure against Linked Server

We have an AX server that we would like a group of users to be able to retrieve data from but limit their access to just the data needed. Normally this could be done with a stored procedure and give ...
2
votes
1answer
738 views

Which reporting method has better performance, using linked server or using lookup in SSRS?

I've been using the new lookup function in Reporting Services 2008 R2 when using different servers where I don't have the option of a linked server. However, the DBA just created the a link to that ...
2
votes
1answer
2k views

SQL Server 2000 to SQL Server 2008 linked server issue

Here's my scenario: I've got a linked server going from a SQL Server 2000 machine to a SQL Server 2008 machine. To aid in moving databases with a minimum of config changes, most of our connections ...
2
votes
1answer
46 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
173 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 ...
2
votes
1answer
197 views

Alternatives to Linked Servers (Allow Inline SQL with Parameters)

I am trying to find a solution to execute inline SQL with parameters against some linked server databases. From what I have found online the only proper way to query a linked server is to use ...
2
votes
2answers
2k views

Fix Linked Server Without Restarting SQL Server Instance

Problem: Occasionally a nightly job will fail, and when I look into it, it is the result of a query across a linked server failing. But for some reason that query seems to remain 'open' in some way ...
2
votes
1answer
9k views

Why am I getting a “login failed” when creating this linked server?

I'm creating a linked server from ServerA to ServerB, both of them are SQL Server 2008 servers. I need to create the linked server using a domain service account so that anyone will be able to utilize ...