Linked Server and OPENQUERY with Execl Source - SQL Server by serverku

As I have written posts for the linked servers with SQL Server option in previous post, the same way we have another option is available and these are with other data sources as a linked server. With SQL Server as a linked server, we can communicate with two SQL servers. But we can also communicate other data sources like excel, csv and others as well. Let us link our SQL server with Excel and get the data from excel. For that we need to go through the same way as I did for SQL Server linked server, but here we need to choose option of other data sources.

How can we do it using TSQL?

 
USE [master]
GO

EXEC [master].[dbo].[sp_addlinkedserver] 
@server='ExcelImport',
@srvproduct='Excel', 
@provider='Microsoft.Jet.OLEDB.4.0', 
@datasrc='D:\Import\Import_1.xls',
@provstr= 'Excel 8.0'

GO

EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'EXCELIMPORT', @locallogin = NULL , @useself = N'False'
GO

It has other data source also like as following,

Now, after creating excel as linked server, we have time to communicate with it and fetch data through linked server & OPENQUERY

 
SELECT 
* 
FROM ExcelImport...[Sheet1$]

-- OR --

SELECT 
* 
FROM OPENQUERY(ExcelImport, 'SELECT * FROM [Sheet1$]')

Your comments are appreciated.

Working with Linked Servers in SQL Server by serverku

We may have a need for some logic to move the data from one server database to another server database on production environment. Also for the distributed transactions or for the cross server database queries we require it.

How to do it? With Linked servers we can perform any distributed transactions between servers. Also, we can execute remote servers stored procedure with linked server communication.

How to create it?
Herewith we have small demo which I have captured during setup it.

1. Go to the Server Objects --> Linked Servers, Right click on it and click on New Linked Servers.
2. Specify Server name which you want to linked to current server.
3. Go to Security tab and select the option for link.

The option details as msdn and book online are following,

Local login : Specify the local login that can connect to the linked server. The local login can be either a login using SQL Server Authentication or a Windows Authentication login. Use this list to restrict the connection to specific logins, or to allow some logins to connect as a different login.

Impersonate : Pass the username and password from the local login to the linked server. For SQL Server Authentication, a login with the exact same name and password must exist on the remote server. For Windows logins, the login must be a valid login on the linked server.

Remote User : Use the remote user to map users not defined in Local login. The Remote User must be a SQL Server Authentication login on the remote server.

Remote Password : Specify the password of the Remote User.

Not be made : Specify that a connection will not be made for logins not defined in the list.

Be made without using a security context : Specify that a connection will be made without using a security context for logins not defined in the list.

Be made using the logins current security context : Specify that a connection will be made using the current security context of the login for logins not defined in the list. If connected to the local server using Windows Authentication, your windows credentials will be used to connect to the remote server. If connected to the local server using SQL Server Authentication, login name and password will be used to connect to the remote server. In this case a login with the exact same name and password must exist on the remote server.

Be made using this security context : Specify that a connection will be made using the login and password specified in the Remote login and With password boxes for logins not defined in the list. The remote login must be a SQL Server Authentication login on the remote server.

We have a some server options like RPC, RPC out as follows

We have seen as how can we create linked server from SSMS. Now we will have script to create linked servers as well.

 USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'PARESH-PC1', @srvproduct=N'SQL Server'
GO

EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'use remote collation', @optvalue=N'true'
GO

USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'PARESH-PC1', @locallogin = NULL , @useself = N'False', @rmtuser = N'dba', @rmtpassword = N'dba@test'
GO

How to get list of linked servers?

SQL Server provide system stored procedure as well system tables , from we can have the details for the same.

• sp_linkedservers
• sys.servers

 
-- 1. Fetch the data using tsql

SELECT 
* 
FROM paresh-pc.demo.dbo.dempteable

-- 2. Running remote stored procedures

EXECUTE paresh-pc.demo.dbo.DemoSP 'test_param'

I hope you enjoyed linked servers. Please share your comments what you are performing with linked servers. 

Access to the remote server is denied because no login-mapping exists - SQL Server Error by serverku

Recently, when I was working with security and changed some level of access and permission of some logins\users, I received an error while accessing data through linked servers with some logins which was working earlier. An error is reported as below.

Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists.

After finding solution following, it worked. Here is a some change of linked server and below is a script to used for same. Just adding a logins to linked server which has an issue to access it.

Use master
GO

EXEC master.dbo.sp_addlinkedserver 
 @server = N'LinkedServerName', 
 @provider=N'SQLNCLI',
 @srvproduct = 'MS SQL Server', 
 @provstr=N'SERVER=ServerName\InstanceName;User ID=myUser'


EXEC master.dbo.sp_addlinkedsrvlogin 
 @rmtsrvname = N'LinkedServerName', 
 @locallogin = NULL , 
 @useself = N'False', 
 @rmtuser = N'myUser', 
 @rmtpassword = N'*****'
GO

Here is the just script and change your user name in place of ‘myUser’ and appropriate server\instance name. Please share your comments if you received such errors and workaround for same.

Synonyms in SQL Server by serverku

Before starting at depth, I just want to define Synonyms in SQL server. As per online,  it is an alternate name of any other database object and provide a layer of abstraction which refers to ad base object for local as well remote server. Which can be created for tables, stored procedures, views, linked server and for some others likes to list here. Let me give some examples as how it can be created and used,

-- Creating base table
CREATE TABLE dbo.results
(
 StudentId INT,
 Subject varchar(20),
 Scrore int
)

-- Creating a schema
CREATE SCHEMA VW
GO

-- Creating a view 
CREATE VIEW vw.results 
AS 
  SELECT StudentId,
   subject, 
         scrore 
  FROM   dbo.results 
GO 

-- Creating a Synonym for local object 
CREATE synonym dbo.snm_result 
 FOR demodb.vw.results 
GO 

-- Viewing data from Synonym 
SELECT * 
FROM   dbo.snm_result 
GO 

-- Creating a schema
CREATE SCHEMA lkd 
GO 

-- Creating a Synonym for remote object
CREATE synonym lkd.remotelinked 
 FOR RemoteServer1.demodb.dbo.Students 
GO 

-- Using in another script
SELECT rm.strundname, 
       r.subject,
    r.scrore 
FROM   vw.results r 
       INNER JOIN lkd.remotelinked rm 
               ON ( rm.studentid = r.studentid ) 
GO

This helps to use it in many places once it's created as we seen in the above example. You may have more idea of its usage. Your comments will be appreciated.

Cannot drop server 'x' because it is used as a Distributor in replication - Error when deleting linked server in SQL Server by serverku

Recently, while I was working with failover, I was needing to change data source of linked server. So linked server name remains same but the data source of SQL server point change. I tried to change data source and also tried to delete it too, but it was not worked and raised an error below,

Drop failed for LinkedServer 'x'.
Cannot drop server 'x' because it is used as a Distributor in replication.

I checked in replication and it's not used in replication. Finally, I got solution using sp_serveroption system object which sets server options for remote servers and linked servers as follows script which run against master database.

EXEC master.dbo.sp_serveroption 
 @server=N'LinkedServer', --  Put linked server name here 
 @optname=N'dist', 
 @optvalue=N'false'
GO

This script makes option value to ‘false’ for option name ‘Dist’ for passing specified server. Please read all option names here. This changed can be done from the user interface with linked server Property –> Server Options.

May be you received such relevant errors and solution too. Please share your thoughts for such kinds of error.