Linked server allows operation to multiple server as one query.

learn more… | top users | synonyms

2
votes
1answer
9 views

Run Built-In Function on linked server

We have two SQL 2014 servers, and I have created a Linked Server on one of them to the other. As part of a task I'm trying to run I need to convert a SQL UserID to a username on both the local and the ...
2
votes
1answer
208 views

Running SQL job: The login is from an untrusted domain and cannot be used with Windows authentication

I have two databases in different servers (server1 , server2) server1: Login : login1 / password :123 /member of sysadmin (user user1,password:pass) server2 : Login :login1 / password :123 ...
2
votes
1answer
79 views

Four-part distributed Query issue from 2014 to 2008R2

From a SQL Server 2014 RTM in our DEV environement, I am executing this query using a SQL login. select t.noshipid, y.nofolder from ...
2
votes
1answer
2k views

DELETE from Linked Server table using OPENQUERY and dynamic criteria

I'm attempting to run a DELETE statement from my local SQL Server to delete appropriate records from a table in my remote linked server using OPENQUERY(). I'm using the following code, but it seems ...
2
votes
1answer
611 views

How to UPDATE/DELETE/INSERT with Microsoft.ACE.OLEDB.12.0 as Linked Server in SQL Server 2012?

I need to update an Access 2003 database from SQL Server 2012 with the Microsoft.ACE.OLEDB.12.0 provider. As an administrator, everything works great. I can do Select, Insert, Update and Delete ...
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 ...
2
votes
1answer
507 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 ...
2
votes
4answers
16k 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, ...
2
votes
1answer
857 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
4answers
5k 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 ...
2
votes
1answer
2k 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
2k 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

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
691 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
0answers
44 views

security concerns over enabling data access for the current server

Included in the code of the creation of some stored procedures, I have the enabling of data access as you can see below. BEGIN TRY PRINT @@SERVERNAME PRINT DB_NAME() PRINT 'enable the ...
2
votes
0answers
133 views

SSRS - ERROR NUMBER:7357 while passing multiple value parameters using comma delimited string to stored procedure

I am working on a SSRS report to display the logins permissions in a set of databases on a specific server. the server, the logins and the databases are all parameters. NULL shows them all (logins ...
2
votes
0answers
95 views

Performance Implications of Explicit Collation Conversions across Linked Servers

I locally host host databases from two vendors on two different SQL instances, but on the same network. These servers frequently communicate with each other (through joins and whatnot), so we have ...
2
votes
0answers
486 views

Auditing access to a linked server

I have a linked server with SQL Server configured with the security option "For a login not defined in the above list, connections will: Be made using the login's current security context". What I ...
2
votes
0answers
383 views

Linked Server Login Timeout but SqlCmd works

I've got a SQL 2005 SP4 server that connects to a 2008 SP3 instance via linked servers using the SQL Server server type. Every once in a while, one of those linked servers will start throwing login ...
2
votes
0answers
459 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
3k 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
430 views

Problem with EXEC MyLinkedServer…sp_executesql within transaction

I rather clueless about this so any help is appreciated. We have got a stored procedure that is running within a transaction. The stored procedure is called from a .NET program (via normal ADO.NET). ...
1
vote
1answer
244 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
1k views

sp_testlinkedserver output verbose

I've built a script/Agent job that goes and polls our linked servers every 10 minutes to make sure they are online. Now, when one goes offline, It generates an email and notifies me. See below: ...
1
vote
2answers
5k views

Linked Servers pointing to same server (localhost) causing “Transaction context in use by another session” error

We are using SQL Server 2008. Normally our app that is using different databases distributed over the network and some of the stored procedures we call therefore make use of linked servers. Now I ...
1
vote
2answers
3k views

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', ...
1
vote
1answer
210 views

Issue with FMTONLY in SQL Server 2012

I support an application that uses CodeSmith and NetTiers templates to generate C# code. CodeSmith inspects the database and uses the SET FMTONLY ON setting to determine the columns for which code ...
1
vote
1answer
97 views

Querying sys.master_files over a linked server

Querying sys.master_files through a linked server, I get no results. Why? How do I work around this issue?
1
vote
3answers
1k 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
934 views

Remote linked servers

I have a SQL Server 2008R2, and I want to connect remotely to a SQL Server 2012SP1, for which I am given: IP: 12.34.56.78 Login: john Password: pw1 Database: TESTDB Table: test_table The remote ...
1
vote
3answers
8k 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
339 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 ...
1
vote
1answer
658 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 ...
1
vote
1answer
142 views

SQL Server: How to export database with linked servers

I'm a web developer and I want to export a database to work on it in my local machine. I'm using generate script with data. the script is generated but without the linked servers used by some Views. ...
1
vote
3answers
61 views

How do I handle multiple rows with variables defined by a query?

I am trying to write a query that uses variables to feed into a sub query. Query: DECLARE @Code VARCHAR(5) DECLARE @Sys INT DECLARE @Key INT SET @Key = 12345 SET @Code = (SELECT Code FROM ...
1
vote
1answer
76 views

Calling TVFs on another Instance

We have a database on a SQL Server 2014 instance. We have another on a 2008R2 instance. Both on the same server. I have created a linked server on the 2008R2 instance to the 2014 one. However, I am ...
1
vote
1answer
4k views

SQL Server - Linked Server - Using OPENROWSET with windows integrated security

I have seen many articles regarding OPENROWSET using integrated security (Windows Authentication), but I could not make it work for me. It is working fine using SQL Server authentication: select * ...
1
vote
2answers
617 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 ...
1
vote
2answers
1k views

SQL Server 2012 linked server to SQL Server 2000

I am trying to select through an linked server from SQL Server 2012 to SQL Server 2000. The linked server has been setup and I can browser the target database through the linked server which ...
1
vote
1answer
3k views

How do I specify a linked server to a remote database over tcp/ip?

I have a remote database running on an 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
1answer
665 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
3k 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
43 views

Create Linked Server using Active Directory account

I was able to grant access in MSSQL to an Active Directory group and add a service account to that group. It's working fine, I'm able to use trusted connection to login on that MSSQL using both my ...
1
vote
1answer
41 views

Do I need different linked server remote users?

I have inherited a project that uses a local Microsoft SQL Server 2012 Express Edition and a central SQL Server 2012 as a backup. There may be multiple local "machines"/databases that all are linked ...
1
vote
1answer
130 views

How to pass credentials to a linked server in another domain?

I am trying to link two SQL Servers (2008R2 and 2014) which are running in two separate (!) Active Directory domains. The problem is that I can login into the remote server only with a Windows ...
1
vote
1answer
163 views

Alternatives to Linked Servers (Allow Inline SQL with Parameters)

I am trying to find a solution to execute inline SQL with parameters against some linked server databases. From what I have found online the only proper way to query a linked server is to use ...
1
vote
1answer
498 views

Possible to use a Trigger as failsafe for insert in two databases?

(This is for a project on MSSQL 2008R2 Server) I have been tasked with splitting a table into an internal (same db as before) and an external one (different server). Currently the idea is to read ...
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 ...
1
vote
1answer
176 views

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

I wonder what are the security risks in using Linked Server.
1
vote
1answer
536 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 ...