Questions tagged [linked-server]

Linked server allows operation to multiple server as one query.

Filter by
Sorted by
Tagged with
1
vote
1answer
5k views

Using INSERT INTO…EXEC AT linked server into temp table fails with Msg 7391

I am using SQL Server 2014. I want to execute EXEC (@remotecmd) AT [server_name]; (@remotecmd is a dynamic sql not a stored procedure) into ##eapb. My code is insert into ##eapb EXEC (@remotecmd) AT [...
0
votes
2answers
63 views

Database Files through Linked Servers

Is there another way to create a script that I can see all database files with their disk space details across databases and across servers? I can do it through Registered Servers. But I want to ...
0
votes
1answer
2k views

Table-Valued Functions from Linked Server

Why does when I try to run: SELECT * FROM [servername].master.sys.dm_os_volume_stats(1, 1) AS btb (NOLOCK) I get error: Msg 195, Level 15, State 15, Line 45 'servername.master.sys....
0
votes
0answers
2k views

Date format incompatibility in linked server between SQL Server and Oracle

When I select data from Oracle to SQL Server via linked server I get exception SELECT CreationDate FROM OPENQUERY (ORCLS, 'Select CreationDate from ADMIN.CV_TMP_Subscrs_Stage4_List') Msg ...
0
votes
1answer
1k views

Can't install a OLE DB Provider for IBM DB2 ISeries on SQL Servers higher than 2008 version

I've currently had a SQL Server 2008 server, which we now need to migrate to a newer edition. We have a couple of linked servers which I have not set up personally which utilizes a provider called ...
0
votes
1answer
109 views

Linked server to Oracle doesn't use filters. Are there any performance changes in different versions of SQL Server?

My customer somehow only has SQL Server 2005 instance. It has linked server to oracle database. I've noticed that sql server doesn't send any filters to oracle side and apply them only after it ...
2
votes
2answers
5k views

Creating Linked Server - Impersonation of Windows Authentication

I am trying to set up a linked server on Server A to Server B using windows authentication. Using SSMS, I can connect to both Server A (from ServerB) and Server B (From Server A) using Windows ...
2
votes
1answer
354 views

SQL Server Linked Table in Access DB

I'm wondering if/how an SQL Server would be impacted by an Access DB that has ODBC Read-Only linked tables from that SQL Server on it. If a form event triggers a query on one of those linked tables, ...
0
votes
1answer
19 views

Linked Server Creation Error

This is a strange issue, i am unable to create linked server using provider Oracle Provider for OLE DB. But am able to connect to it using sqlplus, was able to do telnet and tnsping. when i'm trying ...
0
votes
1answer
496 views

Slow performance when using a synonym that is configured to use a linked server

Situation: I’m not sure if I should be embarrassed that we did this, or if we should be applauded. Anyways, we have a shell of a database that has synonyms pointing to another server via a linked ...
2
votes
0answers
75 views

Linked servers not ignoring case after reboot

We have a SQL Server 2014 database with collation SQL_Latin1_General_CP1_CI_AS, which is case insensitive. It is linked to another SQL server database with the same collation. The second database ...
0
votes
1answer
337 views

Can Select but not Insert from SQL Server to MySQL

I'm having a problem with inserting data from SQL Server to MySQL. I saw this syntax that helped others: EXEC master.dbo.sp_addlinkedserver @server='MYSQL', @srvproduct='MySQL', @provider='MSDASQL', @...
0
votes
2answers
2k views

SQL linked server returning error suddenly upon query

We are facing error when trying to connect to and execute queries using a linked server. The SQL Server and the Linked Server are able to ping and telnet each other. Also testing the link returns ...
7
votes
1answer
2k views

Query External LDAP Server from SQL Server

I have done extensive research on querying LDAP Server but so far has not been able to work around. Therefore, asking experts help Background: I have a client whose LDAP Server is OnPremise (though ...
2
votes
1answer
402 views

SQL Job Step timeout

If a SQL Agent job step executing a stored procedure on a linked server times out, does the agent kill the SPID on the linked server or does it continue to run on the linked server?
3
votes
1answer
1k views

Making dynamic queries for linked server in SQL Server

I want to create a dynamic query in SQL Server which will run on linked server. I am trying to do it as follows. USE [MYDB] GO DECLARE @company AS nvarchar(50); DECLARE @id nvarchar(MAX); DECLARE @...
10
votes
1answer
6k views

8000 character limit on OPENQUERY against a linked server

I have a query that I'm trying to run through OPENQUERY on SSRS/SQL Server 2014, but I keep getting an error of: The character string that starts with [...] is too long. Maximum length is 8000. Is ...
1
vote
1answer
1k views

Windows Authentication for SQL Server Linked Servers

Currently, on our project, we have a situation: there are a SQL Server 2012 instance IA in domain A and MS SQL Server 2012 instance IB in domain B. Domains A and B are trusted. IB configured as the ...
0
votes
1answer
284 views

Heterogeneous queries error with a View

I recently got my first taste of the error "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection." which I hadn't come across. Nearly every resource I ...
1
vote
1answer
355 views

Cannot insert record in remote database - Linked server

I want to insert record into a table in my remote database table.
0
votes
1answer
448 views

Login failed in Linking two servers - SQL Server

I want to connect 2 database which are on 2 different server. SV1 -> SQL Authentication SV2\SQLSERVER -> Windows Authentication LinkedServer query EXEC master.dbo.sp_addlinkedserver @...
0
votes
1answer
432 views

Same query, Same linked server. Different result

I have two instances (SQL2008R2_A and SQL2008R2_B), they have same linked server configuration to Oracle_C. I used the same query to select from Oracle_C. SQL2008R2_A returned the correct result but ...
2
votes
0answers
86 views

Linked Server ODBC statement missing delimiters on reserved identifier

INSERT INTO SOMESERVER...[SomeTable] ([ID], [Timestamp]) VALUES (746, 1504500000) This statement is missing delimiters for the identifiers in an ODBC trace. "INSERT INTO SomeTable ( ID,Timestamp) ...
0
votes
1answer
50 views

Communication between DB servers outside Active Directory

Dear SQL Server / Windows PROs: I am trying to set up a solution here (based on SQL Server 2012 Std + Windows Server 2012 R2). Currently the setup is based on a Cluster (two servers, 1 SAN) and stand ...
4
votes
2answers
7k views

Selecting and Inserting into linked server

I am having a situation with linked server which I am not able to understand. So we have a linked server from a 2008R2 server to a 2014 server. The below sample query is executing from 2008R2 server ...
1
vote
0answers
330 views

Connect ORACLE to SQLSERVER to REMOTE SQL SERVER

I have Sql server and oracle installed on same machine. So I created a heterogeneous connection between the two server. Now I need to do some transaction in my sql server and need to send the result ...
4
votes
1answer
320 views

Stuck with self-referencing linked server - will replacing the primary server name be better?

A vendor app uses 4-part names to reference objects. I am pushing back on that, but the current version is what it is. We installed the app using a SQL Alias for all the good reasons you do that, ...
5
votes
2answers
1k views

Linked Server to Azure Error: “Access to the remote server is denied because no login-mapping exists.”

I'm setting up a linked server from an on-prem SQL Server to a database on MS Azure (see below). When non-admin users query the Azure database via the linked server they get: Msg 7416, Level 16, ...
2
votes
1answer
1k views

Unable to get output variable of Oracle procedure using Sql linked server

I have created a Heterogeneous connection between SQL Server and Oracle and tried to call a oracle procedure from SQL Server.I got below error when i tried to execute the procedure. OLE DB provider ...
1
vote
1answer
4k views

Linked server to remote sql server failed

I have tried to create linked server to the remote sql server but when it failed with following error message. The test connection to the linked server failed. ------------------------------ ...
4
votes
1answer
746 views

linked server connection error in Sql server

When I tried to execute query I got the below error. SELECT * FROM OPENQUERY([TESTORACLE], 'select * from TESTTABLE') Cannot initialize the data source object of OLE DB provider "MSDASQL" ...
7
votes
1answer
1k views

Where do Linked Server Queries get executed?

I have two instances ServerA and ServerB, and I have created a linked server in ServerA for ServerB as Linksrv_B. I can execute a query on ServerA using the four part naming convention: SELECT * ...
3
votes
1answer
2k views

Remote query (openquery) to linked server fails after upgrading to SQL Server 2016

Problem started after upgrading to SQL Server 2016 Enterprise edition. Linked and local servers both were upgraded to ver.13.0.4435.0 I've tried the following: Drop/create the linked server after ...
2
votes
0answers
115 views

Permissions on views/stored procedures and the referenced base tables in linked servers

I need help setting up security division between SELECTing and EXECUTEing from views and stored procedures and access to base tables referenced by those objects on linked servers on SQL Server 2012SP3....
1
vote
1answer
615 views

Unable to connect on premise sql server from Sql Azure db [closed]

I have two SQL Server databases: On-Premise : See the image below. We have a static IP associated with this server in order to access remotely from other development machines. Machine is deployed in ...
0
votes
1answer
841 views

Linked Server ERROR while trying to insert some rows into remote table

I'm trying to insert some rows from local table, to a remote table. begin distributed tran begin try insert into [ali\servername].[dbname].[dbo].[mytable] select col1, col2 from mytable; ...
3
votes
1answer
439 views

Linked Server : How to set an exclusive lock on a remote table?

As I found, you can set a lock on your desired table within a transaction with : begin tran select 1 from myTable with (tablock /*or tablockx to block even reads from table*/, holdlock) ...
0
votes
2answers
4k views

Linked Server : Syncing two tables on two SQL Servers

Suppose these database structure : --On main server(server) TicketRequest (id, date, count, start, end/*,and some other columns*/) Ticket (id, reqId, blockedDate, usedDate, userId/*,and some other ...
3
votes
2answers
3k views

Linked Server alternative in Sql Azure database

We are migrating database from Rackspace to Azure. Our databases were deployed on Virtual Machines in rackspace. In our queries we are using Linked server to other databases. Basically cross database ...
3
votes
1answer
819 views

Linked Server Setup Error - SSL Provider: The client and server cannot communicate, because

I am trying to setup a linked server and ending up with the following error. Error: SSL Provider: The client and server cannot communicate, because they do not possess a common algorithm. Environment:...
1
vote
0answers
287 views

Linked server select Openquery causing datetime column to be an hour behind?

I have a SQL 2014 server which has a linked server to an Actian Ingres database. When I run a simple linked server select openquery on any table with date/times, the results it pulls back is an hour ...
-3
votes
1answer
518 views

Create linked server in SQL Server 2008 R2

I tried to create linked server following the syntax: SELECT * FROM Server.Database.Schema.Table My created syntax: SELECT * FROM 145.17.58.228.AHS_System.AHS_User But getting error: Msg 102, ...
0
votes
2answers
51 views

Linked Server stops responding for particular login

We have an issue where the Linked Server does not work for the app team login but tests successfully when the DBA checks the connectivity. On investigating we found that the app login had some Killed/...
3
votes
1answer
535 views

Why Does SQL Server OPENQUERY To Oracle Return Different Column Types On SQL Server 2016 to 2008

I have two SQL Server Instances on the same machine, one 2008 and one 2016. Both instances have a linked server connection to the same Oracle 12 Server. If I run the following query 2008 returns 1.23 ...
0
votes
0answers
170 views

Running a linked server query under a domain user

Is there a way to execute a sql job with a linked server query as a domain user? The linked server authentication is AD so I need to run the query with a set of windows credentials. Execute As doesn'...
1
vote
3answers
6k views

Most efficient way to insert data to MS SQL table from OpenQuery on DB2 linked server?

after much researching and experimenting, I figured I would make an attempt to get some expert advice. I am maintaining a stored procedure that is executed as a job. It creates a table with data ...
0
votes
1answer
438 views

Link Server Error - From SQL2008r2 to SQL2014

I'm getting the following error when I try to query a table using a link server. The link server is created on SQL2008r2 instance to connect to an SQL2014 instance. The OLE DB provider "SQLNCLI10" ...
1
vote
0answers
325 views

Linked Server details cached?

I am trying to resolve a problem for a customer. They have a job which calls a stored proc on Server A, which calls a stored proc on Server B, via a linked server called RefreshDB. The linked server ...
0
votes
1answer
62 views

MS SQL servers in different countries linked for ETL via SP

The title could be a bit complicated, but the question is very simple... We developed an application which uses a proprietary database (call it DB_ONE). Then, there's another application with its own ...
0
votes
0answers
679 views

SQL Server Linked Server Using ODBC DSN

I have a DSN that uses Integrated Security. I'm trying to setup a linked server that uses that DSN but I'm getting the error below. I have successfully tested my connection using another tool so I ...