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
22
votes
4 answers

I need to add a linked server to a MS Azure SQL Server

I have tried and tried, and can not get linked. I can connect to the server using SSMS, but can not link to it from a local server. Here is my script (replacing things in brackets with pertainent information): EXEC master.dbo.sp_addlinkedserver …
user3241483
  • 237
  • 1
  • 2
  • 6
21
votes
8 answers

"Cannot create an instance of OLE DB provider" error as Windows Authentication user

I am trying to run openrowset from MS SQL Server on an Oracle server. When i execute the following command: select * from OPENROWSET('OraOLEDB.Oracle','srv';'user';'pass', 'select * from table') the following error occurs Msg 7302, Level 16, State…
th1rdey3
  • 4,176
  • 7
  • 30
  • 66
19
votes
3 answers

Linked SQL Server database giving "inconsistent metadata" error

I am currently running a third-party software suite, which uses SQL Server as its database. I have a second instance of SQL Server running in a different location, and some apps that I am building in that instance SQL Server needs to access some…
dpmattingly
  • 1,301
  • 1
  • 7
  • 11
18
votes
8 answers

Transaction context in use by another session

I have a table called MyTable on which I have defined a trigger, like so: CREATE TRIGGER dbo.trg_Ins_MyTable ON dbo.MyTable FOR INSERT AS BEGIN SET NOCOUNT ON; insert SomeLinkedSrv.Catalog.dbo.OtherTable (MyTableId,…
Cristian Lupascu
  • 39,078
  • 16
  • 100
  • 137
17
votes
5 answers

error when insert into linked server

I want to insert some data on the local server into a remote server, and used the following sql: select * into linkservername.mydbname.dbo.test from localdbname.dbo.test But it throws the following error The object name…
Daniel Wu
  • 5,853
  • 12
  • 42
  • 93
17
votes
5 answers

SQL Server: How to call a user-defined function (UDF) on linked server?

i'm trying to call a User-Defined Function (UDF) on a linked server: CREATE FUNCTION [dbo].[UserGroupMembershipNames](@UserGUID uniqueidentifier) RETURNS VARCHAR(8000) AS BEGIN RETURN…
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
17
votes
3 answers

How to reference a sql server with a backslash (\) in its name?

Givens: One SQL Server is named: DevServerA Another is named: DevServerB\2K5 Problem: From DevServerA, how can I write a query that references DevServerB\2K5? I tried a sample, dummy query (running it from DevServerA): SELECT TOP 1 * FROM…
Bill Paetzke
  • 13,332
  • 6
  • 44
  • 46
16
votes
7 answers

Create View using Linked Server db in SQL Server

How can I create View on Linked Server db. For Example I have a linked server [1.2.3.4] on [5.6.7.8]. Both db servers are SQL Sserver 2005. I want to create View on [5.6.7.8] using table on linked server. EDIT: On creating using full name,…
Kashif
  • 14,071
  • 18
  • 66
  • 98
15
votes
5 answers

There is insufficient system memory in resource pool 'internal'

SQL Server 2008 Linked Server and ad-hoc INSERTs cause a rapid memory leak which eventually causes the server to become non-responsive and ends with the following error: Msg 701, Level 17, State 123, Server BRECK-PC\SQLEXPRESS, Line 2 There is…
Breck Carter
  • 351
  • 2
  • 5
  • 18
15
votes
2 answers

Workaround for calling table-valued function remotely in SQL Server has even more issues

I had a query with a set of parameters that needed to be run multiple times with different parameters, so I wrapped it in a table-valued function. That table valued function needed called from a remote server. Unfortunately, the call fails on the…
Triynko
  • 18,766
  • 21
  • 107
  • 173
14
votes
4 answers

Best way to get identity of inserted row in Linked server?

I am inserting record in a remote Sql Server using Linked server, Now I wanna get the id of inserted record. something like scope_identity() in local server. My remote sql server is 2000 version. I have seen this post but I can't add any stored…
Raymond Morphy
  • 2,464
  • 9
  • 51
  • 93
14
votes
3 answers

How does OPENQUERY differ for SELECT and INSERT?

I'm aware that the following query will pull down the result set from a linked server: SELECT * FROM openquery(DEVMYSQL, 'SELECT event_id, people_id, role_id, rank, last_updated FROM event_cast') However, is this the same case when it comes to…
Abs
  • 56,052
  • 101
  • 275
  • 409
14
votes
2 answers

Calling Oracle stored procedure with output parameter from SQL Server

I have an Oracle linked server in SQL Server 2008 R2. I need to execute Oracle stored procedures (with output parameter in first, and input parameter in second procedure): CREATE OR REPLACE PROCEDURE my1.spGetDate(CurrentDate OUT…
David Levin
  • 6,573
  • 5
  • 48
  • 80
14
votes
5 answers

How to insert a row into a linked server table?

I have a server SourceServer I am connected to which has a linked server TargetServer. How should an insert statement looks like (I need to reference Linked server, database, namespace, table): //Connected to [SourceServer] USE…
BanditoBunny
  • 3,658
  • 5
  • 32
  • 40
12
votes
3 answers

What are the drawbacks of using linked servers in SQL Server?

Are there any huge performance issues or security concerns? Using SQL Server 2005 and higher
Gabe
  • 5,113
  • 11
  • 55
  • 88
1
2
3
…
99 100