Linked server allows operation to multiple server as one query.

learn more… | top users | synonyms

3
votes
1answer
615 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
3k 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
2k 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 ...
2
votes
2answers
2k 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
1k 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 ...
2
votes
4answers
1k views

Working Linked Server Query fails in sp_send_dbmail

Take the following example: EXEC msdb.dbo.sp_send_dbmail @recipients = 'me@whatever.co.uk' , @query = 'SELECT TOP 10 * FROM LINKEDSERVERA.DATABASE.dbo.TABLE' , @attach_query_result_as_file = N'True' ...
2
votes
1answer
3k 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
3k 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
243 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
3k 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
3k 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
2k 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 ...
2
votes
2answers
68 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
1answer
187 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 ...
2
votes
2answers
820 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
1k 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 ...
2
votes
1answer
571 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
777 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
3k views

Getting error when trying to use OPENQUERY Msg 7321, Level 16, State 2, Line 1

When I run this query: SELECT * FROM OPENQUERY([XXX], 'SELECT * FROM Database.Table WHERE (MBCONO=650) AND MBCUNO LIKE a%' ) I get the error: ...
2
votes
2answers
212 views

Replacing the MS-Acccess frontend for accessing linked tables with SQL Server?

Is it possible to use SQL Server / SQL Server Management Studio to do queries across linked tables the way that you would do them in MS-Access? The MS-Access front-end is frustrating and time ...
2
votes
2answers
5k 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 ...
2
votes
1answer
911 views

ODBC Connection As Clustered Resource

First step in a long process of creating a Linked Server to an Informix database on a Sql 2008 (Win 2003 x64) cluster. I was wondering how to go about creating an ODBC connection as a clustered ...
2
votes
2answers
4k 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 ...
2
votes
1answer
288 views

How to UPDATE/DELETE/INSERT with Microsoft.ACE.OLEDB.12.0 as Linked Server in SQL Server 2012?

I need to update an Access 2003 database from SQL Server 2012 with the Microsoft.ACE.OLEDB.12.0 provider. As an administrator, everything works great. I can do Select, Insert, Update and Delete ...
2
votes
1answer
400 views

SQL Server Linked Server error and Query Problem

I have two servers running SQL Server 2000 and SQL Server 2005. There is a database A,B in SQL Server 2000. Similarly I have the same in A,B in SQL Server 2005. A,B are name of the database. I have a ...
2
votes
4answers
10k views

How can I query data from a linked server, and pass it parameters to filter by?

I have a really big query that needs to be run on multiple databases, and the results appended to a temp table and returned. The basic syntax looks something like this: INSERT INTO #tmpTable (Id, ...
2
votes
1answer
738 views

Pervasive linked server provider missing

I am having the same problem as this question. I installed Pervasive v11 (which does have 64bit ODBC drivers) and I still do not see Pervasive listed as a provider when trying to create a linked ...
2
votes
1answer
2k views

Sql Server 2008 x64 ODBC Linked Server to Oracle Not Working

I have an install of Sql Server 2008 x64, installed on two boxes. One is a Win 7 x64 workstation (Sql Server 2008 x64 SP2 developer edition), the other is a Windows Server 2003 x64 (Sql Server 2008 ...
2
votes
2answers
1k views

Query intermittently fails with “The multi-part identifier could not be bound”

I've got Schrodinger's query here; you can run it once successfully, then 5 times in a row, and it works fine. Change anything in the query, even whitespace, run it again, and it might stop working ...
2
votes
1answer
896 views

Correct SQL Server linked server connection settings to MySQL that will allow inserts

I'm in linked server hell! I've created a linked server to a remote MySQL database. I'm able to query and do updates. However when I attempt an insert I get an error: "[MySQL][ODBC 5.1 ...
2
votes
1answer
576 views

Can't create linked server SQL Server 2005

I'm new to doing linked server and I have this error. "The linked server has been created but failed a connection" On the same virtual machine I have two SQL Server instances one 2000 ...
2
votes
0answers
32 views

SSRS - ERROR NUMBER:7357 while passing multiple value parameters using comma delimited string to stored procedure

I am working on a SSRS report to display the logins permissions in a set of databases on a specific server. the server, the logins and the databases are all parameters. NULL shows them all (logins ...
2
votes
0answers
45 views

Performance Implications of Explicit Collation Conversions across Linked Servers

I locally host host databases from two vendors on two different SQL instances, but on the same network. These servers frequently communicate with each other (through joins and whatnot), so we have ...
2
votes
0answers
326 views

Linked Server Login Timeout but SqlCmd works

I've got a SQL 2005 SP4 server that connects to a 2008 SP3 instance via linked servers using the SQL Server server type. Every once in a while, one of those linked servers will start throwing login ...
2
votes
0answers
417 views

Linked server issue

I made a linked server between two sql server instances. The remote one I have very little control over. When I try a query I get this: Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for ...
2
votes
1answer
3k views

“Linked server does not contain table” error when using VFPOLEDB provider linked server

I am receiving the below error when attempting to run any queries via a linked server in SQL Server 2008 R2 using the Visual Fox Prox OLE DB Provider v9.0.0.3504. I am attempting this as I need to ...
1
vote
1answer
238 views

Is it possible in a view to reference a linked server implicitly rather than explicitly?

I'm using views and stored procedures that reference a linked server: SELECT Id, Name FROM [LinkedServer].Database.dbo.FooView I would like to hide the [LinkedServer].Database or at least the ...
1
vote
2answers
610 views

sp_testlinkedserver output verbose

I've built a script/Agent job that goes and polls our linked servers every 10 minutes to make sure they are online. Now, when one goes offline, It generates an email and notifies me. See below: ...
1
vote
3answers
803 views

Find old linked servers on a restored copy of master/msdb db

We moved our dbs to a new server and the drives on the old server are still available, but the SQL service is offline and cannot be started. I want to find the linked servers that were registered on ...
1
vote
2answers
963 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', ...
1
vote
1answer
350 views

Remote linked servers

I have a SQL Server 2008R2, and I want to connect remotely to a SQL Server 2012SP1, for which I am given: IP: 12.34.56.78 Login: john Password: pw1 Database: TESTDB Table: test_table The remote ...
1
vote
2answers
245 views

Linked Server Has Run Out Of Memory

So I have a stored procedure that calls an AS400 Linked server. Now, the stored procedure will execute successfully. However, it is used in a SQL Agent Job and each time the job runs, it gives the ...
1
vote
1answer
564 views

Get statistics of open connection to linked servers

How can I get information about connections from my MS SQL 2008 server to linked servers (actually it's an Oracle db)? Things like number of currently open connections, and history of opened and ...
1
vote
2answers
332 views

Joins between linked servers are dangerous, so can we just load the data into a local table before doing the join?

I was just reading the following article which states that it's a worst practice to do JOINs between two linked servers. The only alternative I can see is to get the necessary data from both linked ...
1
vote
2answers
989 views

SQL Server 2012 linked server to SQL Server 2000

I am trying to select through an linked server from SQL Server 2012 to SQL Server 2000. The linked server has been setup and I can browser the target database through the linked server which ...
1
vote
1answer
2k views

How do I specify a linked server to a remote database over tcp/ip?

I have a remote database running on an IAAS provider and I need to be able to execute joined queries from my local workstation. I am having trouble because when I attempt to create the link it is ...
1
vote
1answer
508 views

Replication and linked server security

I have three servers: P1, A1, and R1. P1 and A1 are production oltp servers. On each there are two databases that are being replicated down to R1 for olap (mostly SSRS reports). I am working on ...
1
vote
1answer
2k views

How do I delete rows from a table on a linked server

I need to delete rows from a table in several databases on several servers with the same schema. OPENROWSET() does not seem to be the answer. If I do this: SET @sqlStr = ' SELECT TOP 0 * FROM ...