Linked server allows operation to multiple server as one query.

learn more… | top users | synonyms

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
660 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
1answer
1k views

SQL Server Linked Server Permissions

On a SQL Server 7 server, I have a linked server to a Sybase database server running this command: select * from openquery(SYBASE_SERVER, 'select * from DBA.TESTTABLE') works fine if I'm logged in ...
2
votes
0answers
109 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
81 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
433 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
365 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
449 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
2answers
116 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
3answers
363 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
241 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
993 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
1answer
127 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
69 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
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
3answers
998 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
750 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
2answers
3k 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( ...
1
vote
3answers
7k 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
320 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
11k 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', ...
1
vote
1answer
633 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
3answers
36 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
49 views

Linked Server Error - Login Failed for user 'myaccount'

I have a database on serverA. I have a windows account (myAccount) that is a member of db_datareader role on this db. On serverB, I want to create a linked server to the database on server using ...
1
vote
1answer
60 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
3k 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
486 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
2k 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
429 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 ...
1
vote
1answer
603 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
2answers
8 views

Most restricting permissions for querying view with linked server

I've created view to query another server via linked server(SQL Server 2014). I need to grant permissions to developers to use this view. Granting only select is not enough and they got the following ...
1
vote
1answer
28 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
36 views

powershell problem when running on a remote server - access is denied

as a follow up from this question below: Run a PowerShell script on a different server - from inside a SQL Server job there you can see that for me tu run a powershell script from inside a sql ...
1
vote
1answer
146 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
420 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
171 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
480 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 ...
1
vote
1answer
4k 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 ...
1
vote
1answer
6k 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 ...
1
vote
2answers
6k 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
218 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: ...
1
vote
2answers
66 views

Join SQL Server table in OPENQUERY on multiple keys

I need to join an Oracle linked server table (with 1 billion rows) with an SQL Server table (with 100 rows) on multiple keys: SELECT * FROM OPENQUERY(EDW, 'SELECT * FROM ORACLE_TABLE') A INNER JOIN ...
1
vote
2answers
380 views

How can I call a remote User Defined Function?

I have SQL server which contains a link to another server(For e.g: test) I have a function in linked server(name:getvalue(@parameter)).so this function output i want to retrieve in SQL server 2012.I ...