Linked server allows operation to multiple server as one query.
7
votes
3answers
339 views
How do I write portable SQL that refers to a linked server?
I've got a stored procedure that refers to a linked server. In several places throughout the procedure I've got something like the following:
INSERT INTO [TableName]
(...Columns...)
SELECT ...
7
votes
1answer
213 views
selecting more fields causes catastrophic failure
I have a connection to a linked server from sql server 2012 to pervasive sql.
When I do select field1, field2, field3 from mytable everything works!
However, when I do select field1, field2, field3, ...
6
votes
1answer
254 views
Is there a way to DENY CRUD operations on connections from linked server?
I have a SQL Server instance, RPT1 (2008 R2), that was set up for "power users" to run their own queries. This server is linked to our production reporting server, PRD1 (SQL Server 2008 instance), ...
5
votes
3answers
7k views
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 ...
5
votes
3answers
109 views
How does “Be made using the login's current security context” pass the users password to remote server
Say you have a SQL Server with a "Linked Server" to another server - said "Linked Server" is set to use "Be made using the login's current security context" as its authentication model.
How does the ...
5
votes
1answer
171 views
SQL Server Linked server query problem (group by)
I have been using an old SQL Server 2000 installation for a long time that is linked to several Oracle servers, and it all works very well.
I am trying to migrate to SQL Server 2012 via SQL Server ...
5
votes
5answers
1k views
Microsoft SQL server 2008 and linked servers
I'm designing a set of databases which might need to use each other's stored procedures or tables. The reason that I'm creating separate databases is that in the future I want to be able to move any ...
4
votes
3answers
270 views
SQL Server pivot query on Oracle source
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:
╔══════════╦══════════╗
║ ...
4
votes
1answer
383 views
SQL Server OPENQUERY vs Regular Query against AS400
I log into a SQL Server database which has an AS400 server as a linked server.
I've discovered that running queries against this AS400 server is MUCH faster if I run the select in an OPENQUERY...
...
4
votes
1answer
409 views
Is there a way to setup a user to run SPs, but not access the underlying data tables?
We have two database instances on two separate server boxes that are linked. The website user needs to be able to run stored procedures on DatabaseA which query DatabaseB tables, however it should not ...
4
votes
3answers
161 views
how to copy data from SQL Server table to excel Spreadsheet thru Linked Servers
I try to copy data from a table in SQL Server 2005 into a excel spreadsheet. Since that sheet is linked to some external charts, so I have to overwrite the existing data with the updated data from SQL ...
4
votes
2answers
435 views
SQL Server Linked Server to Progress is slow with an openquery view
We have a SQL Server database setup with a Linked Server setup connecting to a Progress OpenEdge database. We created a SQL Server view (for using with SSRS) of some of the OpenEdge tables using code ...
3
votes
3answers
1k views
What's the difference between Linked Server solution and SSIS solution?
When I want to move data between two databases (e.g source: Oracle, destination: SQL Server), I think I have two options: Linked Server and SQL Server Integration Services. But is there any benefit ...
3
votes
3answers
258 views
Remote queries and linked servers
I have 2 servers with SQL Server 2008 R2: INT & CRM.
I need to run an SSIS package on INT, which uses data from both an INT database and a CRM database. It's painfully slow.
I tried this to ...
3
votes
1answer
498 views
Where are queries against a linked server actually processed?
How is a query against a linked server processed? Does the linked server use its optimizer when it receives a query from a remote source? There are a few scenarios about which I'm wondering:
Scenario ...
3
votes
1answer
197 views
Slow SQL Server query using a DB2 linked server in virtual environment
I have a problem with a slow query which uses a linked server. Let me list out my environment first and I'll get to the issue:
Virtual Windows 2003 R2 server (fresh deployments on XenServer 6.1 ...
3
votes
3answers
158 views
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 ...
3
votes
2answers
137 views
Sudden Query Performance Degradation On A Linked Server
I have an application that uses a view on a SQL Server 2008 R2 instance. This view actually queries a linked server (SQL Server 2008) that has replicated data from our main ERP database. Yesterday, we ...
3
votes
2answers
1k views
Linked servers login failure: Impersonate works, Username/password fails
I'm trying to set up a linked server between 2 instances of Sql Server 2008 R2.
The login works perfectly when I select the impersonate option. However when I try to use the remote user/password ...
3
votes
1answer
852 views
Performance difference between SQL Server Linked Server and direct query
A Management Studio query pane connected directly to a remote sql server executes and displays a simple table query of (SELECT * FROM table) in just under 2 seconds.
The same Management Studio but ...
3
votes
1answer
133 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 ...
3
votes
0answers
658 views
“Prelogin failure” when attempting to run stored procedure across linked server
I'm getting the below error when attempting to run a stored proc across a linked server that is going from Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) to Microsoft SQL Server 2008 (SP2) - ...
2
votes
2answers
1k 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
1answer
578 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
2answers
796 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
5k views
Local login impersonation not working with a linked server
I have created a linked server on my server using the following:
exec sp_addlinkedserver
@server=N'MyOldDB',
@srvproduct=N'',
@provider = N'SQLNCLI',
@datasrc=N'MYSERVER',
...
2
votes
1answer
851 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
2answers
69 views
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 ...
2
votes
1answer
89 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
1answer
444 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
1answer
263 views
Communication link failure for some queries to linked server
I am seeing the following error in SSMS (server has Windows Server 2008 and Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 installed) when running some queries against the linked server, in ...
2
votes
1answer
54 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
934 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
310 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
1k 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
387 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
259 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
2answers
1k 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
0answers
29 views
Update Sql Server from Mysql
We have a website that stores form data in a web hosted MySQL database. Whenever form data is entered into the MySQL server tables I need update all necessary tables on our local SQL Server database.
...
2
votes
0answers
250 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
1k 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 ...
2
votes
3answers
97 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, ...
1
vote
2answers
411 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 ...
1
vote
1answer
171 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
83 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 ...
1
vote
3answers
248 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
1answer
529 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 ...
1
vote
1answer
117 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
438 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 ...
1
vote
1answer
302 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 ...
