Stack Overflow
Stack Overflow

Questions tagged [linked-server]

The SQL Server "linked server" feature makes it possible to execute commands, such as a SELECT statement, on a remote server.

Use this tag for any questions related to SQL Server's linked server feature.

A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. Linked servers offer the following advantages:

  • Remote server access.
  • The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
  • The ability to address diverse data sources similarly.

An OLE DB provider is required for this functionality to work.

See MSDN for more information.

1493 questions
189
votes
14 answers

How to find a text inside SQL Server procedures / triggers?

I have a linkedserver that will change. Some procedures call the linked server like this: [10.10.100.50].dbo.SPROCEDURE_EXAMPLE. We have triggers also doing this kind of work. We need to find all places that uses [10.10.100.50] to change it. In SQL…
Victor Rodrigues
  • 11,353
  • 23
  • 75
  • 107
185
votes
33 answers

How do I fix the error 'Named Pipes Provider, error 40 - Could not open a connection to' SQL Server'?

I can't seem to connect to my database from a site. I get this error: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server I tried using the local IP address to connect as well as a public one. I've tried: Yes, the site can…
Damien
  • 4,093
  • 9
  • 39
  • 52
113
votes
16 answers

SQL Server Linked Server Example Query

While in Management Studio, I am trying to run a query/do a join between two linked servers. Is this a correct syntax using linked db servers: select foo.id from databaseserver1.db1.table1 foo, databaseserver2.db1.table1 bar where…
bmw0128
  • 13,470
  • 24
  • 68
  • 116
71
votes
7 answers

How to select data of a table from another database in SQL Server?

Suppose that I have a database which name is testdb in test server. I also have a database named proddb in prod server. Now I want to select data of a table of testdb database from proddb database. How can I do that in SQL Server? Also, I can do it…
user82431
  • 1,671
  • 2
  • 14
  • 7
71
votes
3 answers

Why do I get the error "Xml data type is not supported in distributed queries" when querying a linked server for non-xml data?

I have two SQL Servers (running SQL Server 2008) named DATA01 and DATA02. DATA02 has a linked server definition LINK, that points at DATA01, with suitable user mapping set up. On DATA01 there is a database MyDatabase containing these two…
AakashM
  • 62,551
  • 17
  • 151
  • 186
58
votes
9 answers

MSDTC on server 'server is unavailable'

I get this weird error on SQL Server. And I cannot find solution in older posts. I have this procedure: create proc _upJM_SyncAll_test as begin DECLARE @SQLString nvarchar(max) set @SQLString = N' DELETE FROM OPENQUERY([LOCAL_MYSQL],''SELECT…
Kiki
  • 760
  • 1
  • 7
  • 17
56
votes
2 answers

Is there an "IF EXISTS" test for a linked server?

I want to be able to programmatically (in T-SQL) check if a specific linked server already exists for my current server and database (so that if the link doesn't exist yet, I can create it). I tried stuff like this: IF OBJECT_ID('myserver\devdb_1')…
Ogre Psalm33
  • 21,366
  • 16
  • 74
  • 92
34
votes
8 answers

Cross-server SQL

I want to port data from one server's database to another server's database. The databases are both on a different mssql 2005 server. Replication is probably not an option since the destination database is generated from scratch on a [time interval]…
Boris Callens
  • 90,659
  • 85
  • 207
  • 305
31
votes
3 answers

Could not find server 'server name' in sys.servers. SQL Server 2014

I recently upgraded our SQL Server from 2005 to 2014 (linked server) and I am noticing that one of the stored procedures which calls the exec command to execute a stored procedure on the upgraded linked server is failing with the error Could not…
nobody
  • 10,892
  • 8
  • 45
  • 63
26
votes
5 answers

Sharing data between SQL databases

I'm trying to solve a problem, that for once, I didn't create. I work in an environment with many web apps backed by different databases on different servers. Each database is rather unique in its design and application but there still remains…
Matt
  • 25,943
  • 66
  • 198
  • 303
26
votes
5 answers

"Deferred prepare could not be completed" error when using local database as linked server

I created a Linked Server from my local Sql Server, as given in http://sqlserverplanet.com/dba/local-linked-server However when I try to execute a stored procedure in Linked Server, it throws: Deferred prepare could not be completed error and…
Anand B
  • 2,997
  • 11
  • 34
  • 55
25
votes
7 answers

Querying a linked sql server

I added a linked server, which is showing in the linked server list, but when I query it, it throws an error with the db server name. EXEC sp_helpserver EXEC sp_addlinkedserver 'aa-db-dev01' Select * from openquery('aa-db-dev01','Select * from…
Renju
24
votes
3 answers

SQL Server spatial and linked servers

I have a SQL Server instance that I've added a linked server to another SQL instance. The table I'm accessing on the linked server contains spatial types. When I try to query the table I receive an error: Objects exposing columns with CLR types…
BryceH
  • 2,740
  • 2
  • 21
  • 24
23
votes
7 answers

SELECT * FROM MySQL Linked Server using SQL Server without OpenQuery

I am trying to query a MySQL linked server using SQL Server. The below query runs just fine. SELECT * FROM OPENQUERY([Linked_Server], 'SELECT * FROM Table_Name') Is it possible to run the same query without using the OpenQuery call?
Kevin
  • 491
  • 1
  • 4
  • 6
22
votes
5 answers

SQL Linked Server returns error "no login-mapping exists" when non-admin account is used

I have a local SQL Server 2008R2. I have configured Linked Server to a remote database. The Linked Server works great when I login to the local server using a SQL-login account with sysadmin server role. I can query against the remote server, so…
Tony
  • 1,827
  • 1
  • 22
  • 23
1
2 3
…
99 100