Tagged Questions
3
votes
3answers
83 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, ...
0
votes
2answers
57 views
Working Linked Server Query fails in sp_send_dbmail
Take the following example:
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]' ,
@query = 'SELECT TOP 10 * FROM LINKEDSERVERA.DATABASE.dbo.TABLE' ,
@attach_query_result_as_file = N'True' ...
4
votes
2answers
83 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 ...
1
vote
2answers
161 views
SQL Server update query on linked server causing remote scan
I have a SQL Server 2012 setup as a linked server on a SQL Server 2008 server.
The following queries executes in less than 1 second:
SELECT kg.IdGarment
FROM Products p
INNER JOIN ...
1
vote
1answer
49 views
How do I specify a linked server in SQL Server 2008 R2 to a remote database over tcp/ip?
I have a remote database running on a IAAS provider and I need to be able to execute joined queries from my local workstation. I am having trouble because when I attempt to create the link it is ...
1
vote
2answers
58 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 ...
0
votes
1answer
54 views
BUILTIN\Administrators allowing access via linked server but not locally
We have two servers:
LYNDB01 & LYNDB02
On LYNDB01 we have a database called db_ExampleDatabase
We have created a Linked Server on LYNDB02 to LYNDB01 using no mappings and the "Be made using the ...
7
votes
1answer
206 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, ...
1
vote
1answer
116 views
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server
I'm trying to establish a linked server from SQL Server 2008 R2 to an Access database. The Access database is not password protected. As I have seen in other posts (specifically this one), this is ...
2
votes
1answer
77 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:
...
0
votes
1answer
75 views
Linked Server to SQL Server 2000 server from SQL Server 2012
I have a new SQL Server 2012 instance, and am migrating a database from SQL Server 2000. Some of the stored procedures in the DB I am moving reference another database back on that 2000 server. What ...
1
vote
0answers
56 views
sp_testlinkedserver taking long time to time-out
I'm using the following code to check if a linked server is available before running queries on it:
declare @srvr nvarchar(128), @retval int;
set @srvr = 'my_linked_srvr';
begin try
exec @retval ...
4
votes
2answers
414 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 ...
0
votes
0answers
80 views
SQL Server link to linked server
My client has a Visual FoxPro database that needs to co-mingle with SQL Server data (SELECT only, no updates or deletes).
After quite a bit of searching I found the VFP OLEDB provider doesn't work ...
3
votes
3answers
135 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 ...
0
votes
1answer
253 views
How to fix “The OLE DB provider ”MSOLAP“ has not been registered”?
Here's the code I'm using to register a linked server to SSAS:
USE [master];
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'ServerAlias',
@srvproduct = N'',
@provider= N'MSOLAP',
...
2
votes
1answer
51 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 ...
0
votes
1answer
74 views
How can I install a provider for linked server?
I have a provider for a linked server called PervasiveOLEDB.11.0 installed on a sql server 2005 machine. It always me to have a connection to a pervasive sql database.
I would like to use this same ...
0
votes
2answers
1k views
SQL Server : Is it possible to have a linked server link to another linked server?
Hello to all and thanks ahead of time for taking the time to look at my question!
I'm trying to do a work-around to access a 32bit Advantage Database Server 8.1 database with 64bit SQL Server 2008R2.
...
1
vote
2answers
368 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 ...
3
votes
1answer
190 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 ...
1
vote
1answer
110 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 ...
4
votes
1answer
332 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...
...
3
votes
3answers
235 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 ...
4
votes
3answers
262 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:
╔══════════╦══════════╗
║ ...
2
votes
1answer
544 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)
...
1
vote
1answer
1k views
Create a linked server with SQL Server 2012 x64
How can I create a linked server to a folder having fox pro dbf files?
I can access the dbfs inside Visual Studio 2010, creating a connection with this properties
Connection string
...
0
votes
1answer
294 views
Strange Sybase / SQL Server linked server issue
I have a sybase table which has a column note varchar(1000) null.
The following T-SQL will get the following error if there is a not-null value in note. (sybase is the linked server to the Sybase ...
1
vote
4answers
1k views
Select from linked server to local server causing waits on both sides
The queries in question are here: http://pastie.org/4833351
Run individually, each of these queries runs through (albeit slow).
Run as a group, it just sits there, showing either OLEDB waits on the ...
3
votes
1answer
841 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 ...
1
vote
0answers
55 views
What permissions are required to view the contents of a catalog on a remote server?
I defined a linked server from a SQL Server box to a MySQL box. I went through a very detailed set of instructions and steps, everything works like a charm when I am logged in.
The linked server ...
0
votes
1answer
270 views
How do I connect to a database which is a linked server via ODBC?
(All the searches for this sort of thing tell me about where the linked server is connecting to the original database via ODBC, but I'm connecting to the intermediate server via ODBC.)
I have a ...
1
vote
2answers
107 views
Replacing the MS-Acccess frontend for accessing linked tables with SQL Server?
Is it possible to use SQL Server / SQL Server Management Studio to do queries across linked tables the way that you would do them in MS-Access?
The MS-Access front-end is frustrating and time ...
2
votes
0answers
241 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 ...
5
votes
0answers
159 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 ...
1
vote
2answers
428 views
SQL server moved, now can't use as linked server
So I have a SQL server database that was controlled by an outside vendor. It is hosted remotely. Then he needed to move the server to a new host. Now, I'm able to connect to the server manually ...
1
vote
2answers
123 views
Sql Server Linked Server
I have two sql server S1(2008) and S2(2005).
S2 is added to S1 as linked server.
S2 have a table Product.
When I try to run the code
select ProductID from Product
I get the error:
Location: ...
3
votes
1answer
489 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
129 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 ...
1
vote
1answer
506 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 ...
2
votes
2answers
994 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
380 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
2answers
306 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 ...
6
votes
1answer
251 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), ...
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 ...
1
vote
1answer
422 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 ...
2
votes
1answer
434 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 ...
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 ...
0
votes
2answers
3k views
How to add linked server SQL Server 2008 R2
I have two SQL Server 2008 located in different cities.
I have physical access to one of them and I can exec SQL Server Management Studio there.
For second server I have following parameters:
...
1
vote
1answer
297 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 ...
