Linked server allows operation to multiple server as one query.

learn more… | top users | synonyms

1
vote
0answers
78 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
30 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
216 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
75 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
28 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
27 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
69 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 LOOKUP....
0
votes
0answers
101 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 ...
2
votes
0answers
57 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 ...
0
votes
1answer
23 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?
0
votes
1answer
601 views

Best Practice For Data Views with Linked Server in SQL Server

In SQL Server, I have to design a View that I can map on a DBML file in order to dynamically retrieve information from the database with multiple conditions to filter. The problem is that I have to ...
7
votes
0answers
199 views

How to reload a linked server?

I am using Microsoft SQL Server 2014 Enterprise Edition. A problem occurs with the linked servers where it is necessary to restart the server, or to stop the MSSQLSERVER service. When the server is ...
0
votes
3answers
16k views

How to add linked server SQL Server 2008 R2

I have two SQL Server 2008 located in different cities. I have physical access to one of them and I can exec SQL Server Management Studio there. For second server I have following parameters: ...
0
votes
0answers
81 views

Linked server calling “select * from table” query (ODBC Connection)

Every time I use any command using my Linked Server connection on any table it call first a "select * from table" If I use a simple query like that: INSERT INTO LINKEDSERVER...MYSQLTABLE SELECT * ...
1
vote
2answers
76 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 ...
1
vote
2answers
116 views

How to get MS Access to reach a Linked server on MSSQL

We have an MS Access DB that has been in place for 16 years in a Front End/BackEnd configuration. It is now running under the latest version of MS Access using ACCDB files. Since its inception, we ...
3
votes
1answer
192 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 ...
2
votes
1answer
307 views

Running SQL job: The login is from an untrusted domain and cannot be used with Windows authentication

I have two databases in different servers (server1 , server2) server1: Login : login1 / password :123 /member of sysadmin (user user1,password:pass) server2 : Login :login1 / password :123 /...
2
votes
3answers
8k views

Linked servers: Transaction context in use by another session

I'm using linked servers to perform some queries in a distributed transaction. It doesn't work in a distributed transaction. I get the following error: Transaction context in use by another ...
3
votes
2answers
91 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 ...
1
vote
2answers
198 views

Join SQL Server table in OPENQUERY on multiple keys

I need to join an Oracle linked server table (with 1 billion rows) with an SQL Server table (with 100 rows) on multiple keys: SELECT * FROM OPENQUERY(EDW, 'SELECT * FROM ORACLE_TABLE') A INNER JOIN ...
1
vote
1answer
45 views

Do I need different linked server remote users?

I have inherited a project that uses a local Microsoft SQL Server 2012 Express Edition and a central SQL Server 2012 as a backup. There may be multiple local "machines"/databases that all are linked ...
7
votes
2answers
20k views

Linked server available to users without permissions

I have users seeing a linked server which they are not supposed to see. The linked server is defined so that only I will have access to it, but everybody can see and use it. I have created the ...
4
votes
2answers
377 views

How can I tell what database/procedure is using a linked server?

I have 3 servers, one of which has a linked server configured that points to the other - I'll call this Server A. Server A has over 100 user databases for various purposes. Server B is running SQL ...
22
votes
4answers
2k views

Copying (hundreds of) tables from one server to another (with SSMS)

I have several hundred (currently 466, but ever growing) tables I have to copy from one server to another. I have never had to do this before, so I'm not sure at all on how to approach it. All the ...
0
votes
2answers
5k views

Running a Job from a Stored Procedure in another server?

How can I run a job from another server using a stored procedure? Let's say server 1 : db1 server 2 : db2 username : testssis password : testssispass sqljob found in db2: job1 Here is the part ...
0
votes
0answers
75 views

Linked Server to Oracle seems to be slow

we have a linked server between a SQL Server 2008 R2 instance and an Oracle 11.1.0.7 instance using OraOLEDB.Oracle Provider. The enabled options for provider are: Dynamic parameter Nested queries ...
1
vote
1answer
1k views

SQL Server Linked Server Permissions

On a SQL Server 7 server, I have a linked server to a Sybase database server running this command: select * from openquery(SYBASE_SERVER, 'select * from DBA.TESTTABLE') works fine if I'm logged in ...
0
votes
1answer
59 views

Linked Server Error - Login Failed for user 'myaccount'

I have a database on serverA. I have a windows account (myAccount) that is a member of db_datareader role on this db. On serverB, I want to create a linked server to the database on server using ...
1
vote
0answers
202 views

Permission Error when creating Linked Server from SQL Server to Access DB

I created a linked server from my SQL Server 2008 R2 64 bit Windows Server 2008 machine to an Access 2000 Database that resides on another server. The Linked server was created with no trouble using ...
2
votes
1answer
189 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
3answers
456 views

Problem with EXEC MyLinkedServer…sp_executesql within transaction

I rather clueless about this so any help is appreciated. We have got a stored procedure that is running within a transaction. The stored procedure is called from a .NET program (via normal ADO.NET). ...
0
votes
0answers
201 views

Microsoft.ACE.OLEDB.12.0 Error when querying Excel

I am running the following query: SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=D:\GSS.xlsx;HDR=YES', '...
1
vote
1answer
2k views

Using ODBC Connection to Access Firebird Database

I have an ODBC connection to a Firebird database that tests successfully. Is there a way to use this ODBC connection within SQL Server 2008 R2 to access the tables? I haven't used Linked Servers ...
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 ...
8
votes
2answers
3k views

How do I write portable SQL that refers to a linked server?

I've got a stored procedure that refers to a linked server. In several places throughout the procedure I've got something like the following: INSERT INTO [TableName] (...Columns...) SELECT ...Columns....
6
votes
1answer
4k views

Kerberos authentication not working with Linked Servers in SQL Server 2012

I am setting up a DEV/TEST environment using 2 SQL Servers running SQL Server 2012 on Windows Server 2012. We are moving from SQL Server 2005 on Windows Server 2008, where we already have this up-and-...
1
vote
0answers
73 views

Can we Link sql windows server with oracle unix server?

while inserting data through linked server it is happening but when i execute the stored procedure in getting error like OLE DB provider "OraOLEDB.Oracle" for linked server "server ip" returned ...
6
votes
2answers
1k views

Is there a way to setup a user to run SPs, but not access the underlying data tables?

We have two database instances on two separate server boxes that are linked. The website user needs to be able to run stored procedures on DatabaseA which query DatabaseB tables, however it should not ...
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].[...
1
vote
3answers
9k views

Missing DB on linked server

So there is a linked server. However, I am not currently able to view all databases associated with that linked server from within SSMS after expanding the catalog dropdown. It is my understanding ...
0
votes
0answers
32 views

SQL Server: Update linked table times out

I have 4 linked SQL Server 2008 R2 servers in the same network, and distributed selection queries works fine. However when I try to run something like that: UPDATE [LINKED SERVER NAME].[DB NAME].[...
1
vote
0answers
297 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 ...
0
votes
1answer
51 views

Retrieve linked servers out of system database files?

I have the data and log files of a SQL-Server that has crashed. It's user databases have been restored. I am looking for the jobs and linked servers that have been there before the crash. I attached ...
1
vote
0answers
2k views

EXECUTE AS over Linked Servers

I have a front end that logs into the DB as "logInA", and only has permission to "schemaA" stored procs, which use EXECUTE AS "logInB", (I have also tried EXECUTE AS 'DBO'), to handle all the business ...
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 ...
0
votes
1answer
412 views

Using Identity Insert on a linked server

We have a database hosted in Microsoft Azure and using this database connection details I have created a linked server object in SSMS on our on-premise SQL box and I can insert into it as following ...
5
votes
1answer
2k views

Performance difference between SQL Server Linked Server and direct query

A Management Studio query pane connected directly to a remote sql server executes and displays a simple table query of (SELECT * FROM table) in just under 2 seconds. The same Management Studio but ...
2
votes
1answer
108 views

Four-part distributed Query issue from 2014 to 2008R2

From a SQL Server 2014 RTM in our DEV environement, I am executing this query using a SQL login. select t.noshipid, y.nofolder from distantserver.distantdatabase.dbo....