Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [linked-server]

Linked server allows operation to multiple server as one query.

Linked server allows operation to multiple servers as one query. In a Microsoft environment, SQL Server extends the EXECUTE statement so that it can be used to send pass-through commands to linked servers. Additionally, the context in which a string or command is executed can be explicitly set.

Questions with the linked server tag regards querying multiple database servers.

631 questions
32
votes
2 answers

Which one is more efficient: select from linked server or insert into linked server?

Suppose I have to export data from one server to another (through linked servers). Which statement will be more efficient? Executing in source server: INSERT INTO [DestinationLinkedServer].[DestinationDB].[dbo].[Table] SELECT a, b, c, ... FROM…
Guillermo Gutiérrez
  • 667
  • 2
  • 7
  • 13
28
votes
4 answers

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 tables are in the same format: Cart
Der Kommissar
  • 1,523
  • 1
  • 16
  • 27
25
votes
2 answers

How can I get my linked server working using Windows authentication?

I'm trying to get a linked server to ServerA created on another server, ServerB using "Be made using the login's current security context" in a domain environment. I read that I'd need to have SPNs created for the service accounts that run SQL…
Christopher Garcia
  • 609
  • 3
  • 8
  • 12
22
votes
3 answers

Pivot rows into multiple columns

I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data: ╔══════════╦══════════╗ ║ PersonID ║ OptionID ║ ╠══════════╬══════════╣ ║ …
ChrisLively
  • 887
  • 3
  • 10
  • 19
21
votes
5 answers

Why is this explicit cast causing problems only with a Linked Server?

I am querying data from a linked server through a view on the origin server. The view has to include a couple of standardized columns, such as Created, Modified and Deleted, but in this case the table on the source server doesn't have any suitable…
krystah
  • 747
  • 9
  • 19
19
votes
5 answers

Why do linked servers have a limitation of 10 branches in a CASE expression?

Why does this CASE expression: SELECT CASE column WHEN 'a' THEN '1' WHEN 'b' THEN '2' ... c -> i WHEN 'j' THEN '10' WHEN 'k' THEN '11' END [col] FROM LinkedServer.database.dbo.table Produce this…
Andrey
  • 293
  • 1
  • 2
  • 8
16
votes
5 answers

Linked server error not caught by TRY-CATCH

I am setting up a job to loop through a list of linked servers and execute a specific query against each one. I am trying to execute the query inside a TRY-CATCH block so if there's a problem with one particular server I can log it but then carry on…
JamesLean
  • 391
  • 1
  • 4
  • 10
16
votes
5 answers

SQL Server Linked Server performance: Why are remote queries so expensive?

I have two database servers, connected via Linked Servers. Both are SQL Server 2008R2 databases, and the linked server connection is made via a regular "SQL Server" link, using the current login's security context. The linked servers are both in the…
vstrien
  • 547
  • 2
  • 6
  • 15
15
votes
3 answers

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', @srvproduct=N'SQL Server' I am getting the…
mathewb
  • 1,152
  • 1
  • 9
  • 24
14
votes
2 answers

OLE DB provider "MSOLEDBSQL" with SQL Server not supported?

I have been using linked server with the old provider (SQLNCLI) without any issue, as recommended by Microsoft, I'm planning to switch to new provider (MSOLEDBSQL). I'm able to add linked server using following T-SQL after installing the…
Shekar Kola
  • 2,477
  • 2
  • 10
  • 24
14
votes
3 answers

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 running again, the linked servers (to DB2) don't…
MelgoV
  • 241
  • 2
  • 5
12
votes
3 answers

Linked Server Risks

I'm implementing a new feature which requires data from databases on multiple servers. I just need to union data from all these servers and sort it. The two options that come to mind are: Use linked servers and write a simple query to union and…
aepheus
  • 221
  • 1
  • 2
  • 4
11
votes
1 answer

8000 character limit on OPENQUERY against a linked server

I have a query that I'm trying to run through OPENQUERY on SSRS/SQL Server 2014, but I keep getting an error of: The character string that starts with [...] is too long. Maximum length is 8000. Is there any way to work around this limitation? For…
Salmononius2
  • 441
  • 2
  • 6
  • 15
10
votes
1 answer

Using a linked server with OPENQUERY in a database project

I have a SQL Server 2008 running a database I want to throw in TFS. Therefore I used a Visual Studio 2013 database project where I imported the DB. After fixing a bunch of errors I'm stuck with only one error left: In one view the devs used…
Chake
  • 191
  • 1
  • 1
  • 8
10
votes
2 answers

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 new linked server using the following steps: …
Roi Gavish
  • 1,330
  • 1
  • 11
  • 25
1
2 3
…
42 43