Linked server allows operation to multiple server as one query.

learn more… | top users | synonyms

0
votes
1answer
123 views

SQL Server, firewall and linked servers

I have: SQL Server 2008R2 database server in my LAN and under my control (call it LOCALSQL) remote SQL Server, on which I have much less control (call it REMOTESQL), for which I only have IP, ...
0
votes
1answer
500 views

How to add a linked server to a sql server management studio?

I have an account in sql server management studio, which I can login to database B, which is on server X. I also have a crystal report on my local PC. Using the crystal report, I can access a ...
1
vote
1answer
1k views

SQL Server 2008 R2 - Access Failure in a query that uses Linked server

We have 2 SQL Server 2008 R2 machines. A user is running a query on Server A that joins a table in Server B. In the query he is using linked server to point Server B. User has access to Server B ...
3
votes
1answer
543 views

SQL Server: Using 4 part identifiers when database may be on the same server

In my client's setup, the database servers in lower environments are all on one server for cost saving purposes, but in higher environments, these databases are deployed on separate servers, and ...
2
votes
1answer
2k views

Linked Server and MSDTC on cluster

I'm trying to set up a linked server between two instances we have on our network. I've done this many times before without an issue, but this time I repeatedly get the following error: OLE DB ...
1
vote
1answer
566 views

Linked Server using ODBC too slow

The scenario is that developers used a linked server object on ServerM to reach ServerST. This linked server was setup as a "SQL Server" type using a domain login that exists as a login on ServerST. ...
0
votes
1answer
4k views

SQL Server - linked Oracle server: EXECUTE permission denied on 'xp_prop_oledb_provider'

Inside of our SQL Server, we have created a linked server to an Oracle server. We ave mapped several users to the Oracle server and specified the credentials. On the Linked Server 'Security' ...
0
votes
0answers
176 views

Migrate Linked Server Connections

I am getting ready to migrate a DB from one server to a new machine...but were wondering if there is an easy way to migrate those connections over as well...we have about 6-7 linked servers set up to ...
0
votes
0answers
93 views

Access Sql Server Linked Server during Logon Trigger

I am trying to log users logons to my Sql Server (A) in another Sql Server (B, different location physically), but when I try to log in I get the error Login failed ... due to trigger execution and ...
0
votes
2answers
160 views

How secure is a linked server in SQL Server

I am in a situation in which I should doubt everyone (even my developer team and database admin) so I want to Log all the logons made to my server. I have two SQL Servers, say A and B. I want to log ...
1
vote
2answers
744 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 ...
0
votes
1answer
99 views

Migration query taking time

I'm doing a migration from Oracle to SQL Server and, for one of the tables where we have 3 million records, it is taking a lot of time to complete. When I check the activity monitor, I can see the a ...
0
votes
2answers
250 views

Are these linked server queries susceptible to SQL injection?

I'm using a stored procedure to generate a create table script on the fly based on parameters passed into it. I am validating that the server exists, and then validating if the table exists in the ...
0
votes
1answer
628 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 ...
0
votes
1answer
1k views

DB2 Linked Server on SQL Server 2012 - Grant Access to Users

I have a linked server on SQL Server 2012 instance pointing to a DB2 database and I have Sys Admin access to that instance. I am unable to test connection to that DB2OLEDB database as I get below ...
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 ...
2
votes
2answers
5k views

Run “USE” with linked server alternatives

I am using SQL server linked servers and find some difficulties while running queries. Below is the actual query. USe testdb SELECT 'Identitiy property OFF' where COLUMNPROPERTY( OBJECT_ID('Table_1')...
4
votes
1answer
2k views

Security implications disabling promotion of distributed transaction for linked server

I've a a linked server and I need to run the following statement: INSERT INTO...EXEC linkedserver.sp @parameter Both servers SQL Server 2008R2 SP1. Once I run it I get this error: Msg 7391, ...
1
vote
0answers
195 views

Execute SQL at different server (with dynamic server name)

I'm using a settings table to configure on which servers a specific kind of data is stored, and I would like to do this as dynamic as possible without having to add the server as a linked server ...
0
votes
0answers
398 views

Why doesn't this transactional replication post script work? (Works in SSMS and SQLCmd.exe)

I have created a script that creates any missing indexes on replicated indexed views. It gets the index definitions from the publisher (Linkedserver) and applies them to the subscriber. The script ...
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
1answer
270 views

Error updating zoned field from SQL Server stored procedure linked server to iSeries

I'm trying to update a table on an iSeries (AS400) within a SQL Server stored procedure using linked server, but it is failing when trying to update a column defined as zoned. In my stored ...
2
votes
2answers
7k 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. ...
1
vote
0answers
207 views

Multiple login mappings for linked server

Can we assign two logins for two different databases on target server. For example, I would like to create a Linked Server between ServerA and ServerB. ServerB has two databases on it and both using ...
3
votes
1answer
2k views

Loopback Linked Server

Is there any benefit in use a Loopback Linked Server, maybe in a security or performance level, because I really don't get the idea ? I google it for several minutes and I find nothing about it.
3
votes
1answer
858 views

Inserting a 30 million row table from a linked server

I have a large table that needs to be updated on a nightly basis from a linked server. Initially I had simply truncated the table and inserted all of the records but this was taking too long. I'm ...
2
votes
2answers
7k 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 ...
0
votes
1answer
267 views

Nightly reload of tables from a linked server

I have a number of tables that need to be reloaded nightly from a linked server. My current method of doing this is to create a nightly job to pull the data using something like the following: ...
1
vote
1answer
184 views

Is it dangerous to use Linked Server? (SQL Server 2008)

I wonder what are the security risks in using Linked Server.
3
votes
2answers
22k views

How can I enable distributed transactions for a linked server?

I have a SQL Server 2012 instance with a linked server pointing to a db2 database on an IBM Iseries system using the System i Access ODBC Driver. I'm trying to enable distributed transactions. I ...
3
votes
1answer
3k 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 ...
1
vote
1answer
578 views

Linked Server - login trying to access database that is not default

Trying to create a linked server from server A (2005) to B (2008 R2). Everything worked when created. The login on server B was mapped to two databases: DataBase1 and Database2. Database1 was the ...
0
votes
1answer
445 views

linked server hanging

We've been using a linked server to an access database for close to a year now with little issue. Now when I run a select query against the linked server the query sits there and never finishes. ...
1
vote
3answers
10k 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 ...
1
vote
2answers
362 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 ...
5
votes
1answer
7k views

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 ...
1
vote
1answer
102 views

How to disable data type check while pushing data to Linked Server

I have two SQL serves one is SQL server 2005 and other SQL server 2008.I push data from SQL server 2005 to SQL server 2008 using Linked Server.I want to improve performance of data transfer. Few days ...
4
votes
2answers
963 views

SQL Server 2012 CPU spike due to LinkedServer - how to find the offending query?

As part of another question (SQL Server 2012 performance issues (maybe due to linked server)) I've identified a CPU spike to be directly related to a spike in Bytes Received, which leads me to believe ...
7
votes
0answers
1k views

SQL Server 2012 performance issues (maybe due to linked server) [closed]

Summary of the problem We've spit our one database server environment into two servers. We are now having intermittent performance issues I am hoping someone can offer clues - perhaps direction ...
0
votes
0answers
315 views

SQL Link Connection OLE DB provider Query Execution Failed

I have created linked connection in SQL and by which I am connecting Oracle server using OLE DB provider "OraOLEDB.Oracle". SQL Server = 2008 R2 Oracle = 10g Normally, When I am executing Insert or ...
1
vote
1answer
2k views

How to run stored procedure in another database on the same server using a different login?

I have 2 databases (A and B) on the same server. Database A has user ua defined, and database B has user ub defined. I have a stored procedure in A, running as ua, that needs to run a stored ...
2
votes
1answer
529 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 ...
0
votes
1answer
2k views

Linked Server Between SQL Server and iSeries is rounding decimals

We just set up a new SQL Server 2008 database, and I got the link set up between our server and an iSeries As400 server that we get some of our data from. The problem I'm having is that it is ...
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].[...
0
votes
0answers
19 views

Link server does not work in a particular terminal server [duplicate]

We set up a link server from A to B. And we added several AD accounts to the security of link server on A. Also those users have proper permissions in dbs on B. It works perfectly if I selected ...
1
vote
1answer
5k views

SQL Server 2012 Service Master Key Regenerate

I have a new SQL Server 2012 instance and when I run the following command I get an error. ALTER SERVICE MASTER KEY REGENERATE Error: Msg 15329, Level 16, State 1, Line 1 The current master ...
0
votes
3answers
242 views

SQL Server Facets: “Linked Server” exist but “Registered Server” doesn't - why?

I'd like to monitor multiple SQL Server instances. Something similar to what is described in "Monitor Your SQL Server Linked Server Configurations with Policy Based Management" but instead of using "...
1
vote
1answer
2k views

SQL Linked Server Passing Date Parameters

I'm using SQL Server 2008 R2 and have a linked SQL 2012 Express database. On the 2008 box I am creating a stored procedure to be used in a report. I am wanting to pass a date variable so the user ...
4
votes
1answer
3k views

How does one query a SQL Server Linked Server when SQL Server Authentication is specified?

I have established a linked server and am attempting to query a database to which I have a SQL Server username and password, however I can't find the syntax for specifying the userid/pw in the query ...
1
vote
0answers
365 views

Linked server query in profiler

I was profiling a DB for queries against it but have noticed that when a table in the DB is accessed through a linked server this does not show in profiler. Can anyone advise on why this is or what ...