Create a Linked Server MySql to SQL SERVER

While working on one of the projects,i came across a scenario where i have a database that client was having and it was provided by some other provider through their application.I have to get data from that database and using that data i had to do some calculations on that and store the results in my database.There were two scenarios in this case:
1)i get all the required tables and data from that database and create my own database and dump those details into mine and then use it.
2)Create a link between my database and the existing database,so that i can fire queries directly on that database.
Using first approach will not give me updated records at any point of time but my scenario was realtime data.So i thought of using second approach.This approach gave me realtime data as i was querying the existing data and using it.But there is one drawback that it might be somewhat slow. In this article i will explain how to create a linked server in Sql Server.Here i will be using ODBC Driver to fetch data from MYSQL database.Following are the steps to connect a MYSQL database to a SQL Server: First of all we need to install appropriate MYSQL Odbc Driver based on the operating system from the below link .After the driver has been installed Go To Control Panel->Administrative Tools ->Data Sources(ODBC)->System DSN.Now Press Add Button .

Select MYSQL Driver Listed(MYSQL(ODBC) 5.3 ANSI Driver) and click finish. This will open up MYSQL Configuration Window.Fill Data Source Name as MYSQL(this can be anything).TCP/IP Server as localhost.Port as 3306(default port for mysql),User Name-root,Password -your database password and click test.This will show up a success message.Now Select database and click Ok. 
.
We are done with MYSQL System DSN,Now we will Linked Server to MYSQL in SQL Server.Open Sql Server->Server Objects->Linked Server.Right Click on Linked Servers->Add New Linked Server.

This will Open up a Linked Server Properties Dialog.Fill Linked Server as MYSQL_LINKED,Select Provider as Microsoft OLEDB Provider For ODBC Drivers.Product Name as MySQl,DataSource as MySQL_Linked(whatever name is given while creating DSN).Provider String as-
DRIVER=(MySQL ODBC 5.2 ANSI Driver);SERVER=localhost;PORT=3306;DATABASE=databasename; USER=username;PASSWORD=password;OPTION=3;
Leave location as blank and Catalog as database name (in mysql).

Drill down to Server Object → Linked Servers → Providers, right-click MSDASQL, and select “Properties”. The Provider Options for Microsoft OLE DB Provider for ODBC Drivers dialog box will open allowing you to configure several options. Ensure the following four options are checked: Nested queries
Level zero only
Allow inprocess
Supports ‘Like’ Operator
All other options should be unchecked. When done, click “OK”.
In addition to this, you can enable provider options on the SQLOLEDB, In my case I select the Dynamic Parameter and Allow inprocess.
We are done with setting up a linked server.Now we have to test it by firing some basic queries.There are 3 ways by which we can query a linked server.
1)Open Query.
2)Select using 4 part notation. 
3)Execute Function. 
Open Query function requires 2 parameters 1)Linked Server Name,2)Query

select * from openquery (MYSQL_LINKED, 'select * from test.user_details');
INSERT OPENQUERY (MYSQL_LINKED, 'select name,address from test.user_details') VALUES ('Rajeev','Bangalore');
UPDATE OPENQUERY (MYSQL_LINKED, 'select name from test.user_details WHERE user_id = 100006413534648') SET name = 'Akash';
DELETE OPENQUERY (MYSQL_LINKED, 'select name from test.user_details WHERE user_id = 100006413534648')

Note:-For Update/Delete on Linked Server we need to set RPC and RPC OUT properties of Linked Server as true(Right Click Linked Server->Properties->Server Option Tab->RPC-True,RPC OUT -True.
4 Part Notation  -We can also execute queries on linked server using 4 part notations like:
SELECT * FROM linkedserver...tablename but for this we need to change MSDASQL Provider property->Check the box that says “level zero only” in providers.

select * from MYSQL_LINKED...user_details
INSERT into MYSQL_LINKED...user_details(name,address) VALUES ('Rajeev','Bangalore');
UPDATE MYSQL_LINKED...user_details set name='Akash' where USER_ID='100006413534649';
DELETE from MYSQL_LINKED...user_details where USER_ID='100006413534649';

Execute Function can also be used for querying linked server.

EXECUTE('delete from test.user_details WHERE user_id = 100006413534647') AT MYSQL_LINKED

Triggers In Sql Server

Trigger is a special kind of stored procedure that executes in response to certain actions on the table like insertion,updation and deletion. There are 2 types of triggers:- 1)After Triggers(for triggers). 2)Instead Of Triggers. After Trigger:-After trigger are further classified into 3 types:- a)After Insert:-Fired after insert operation is performed on the table. b)After Update:Fired after update operation is performed on the table. c)After Delete:Fired when a record is deleted from a table. Now we will see the triggers in action from a small example.For this we will first create 2 tables.

CREATE TABLE [dbo].[Blogs](
 [blog_id] [int] IDENTITY(1,1) NOT NULL,
 [blog_title] [varchar](max) NOT NULL,
 [blog_date] [date] NOT NULL,
 [blog_description] [varchar](max) NOT NULL,
 [blog_tags] [varchar](max) NOT NULL,
 [status] [bit] NULL,
 [blog_url] [varchar](max) NULL,
 CONSTRAINT [PK_Blogs] PRIMARY KEY )

GO
CREATE TABLE [dbo].[Blog_tag](
 [Blog_id] [int] NOT NULL,
 [Tag_id] [int] NOT NULL
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[Tags](
 [Tags_id] [int] IDENTITY(1,1) NOT NULL,
 [Tag_name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Tags] PRIMARY KEY 
) 

GO

Now i am going to insert a record in tags table that can be used in trigger.

INSERT INTO [dbo].[Tags]
           ([Tag_name])
     VALUES
           ('Sql Server')
GO

After the new record has been inserted in the tags table .We shall create a trigger on the blogs table that will automatically insert the record in Blog_Tag table.

Create trigger [dbo].[trgAfterInsert] on [dbo].[Blogs]
After Insert
As
declare @blog_id int;
declare @tag_id int;
declare @tagname varchar(50);
select @blog_id=i.blog_id from inserted i;
select @tagname=i.blog_tags from inserted i;
select  @tag_id=Tags_id from Tags where Tag_name=@tagname;

Insert into Blog_tag(Blog_id,Tag_id) values(@blog_id,@tag_id);

Now try to insert a record in blogs table ,it will automatically insert record into Blog_tag table.

INSERT INTO [dbo].[Blogs]
           ([blog_title]
           ,[blog_date]
           ,[blog_description]
           ,[blog_tags]
           ,[status]
           ,[blog_url])
     VALUES
           ('Test'
           ,'2016-02-06'
           ,'Test Description'
           ,'Sql Server'
           ,0
           ,'test url')
GO

Similarly we can use after update and after delete trigger. Note:-For after update/delete trigger small change is there in syntax.

Create trigger [dbo].[trgAfterUpdate] on [dbo].[Blogs]
After Update
As
declare @blog_id int;
declare @tagname varchar(50);
declare @tag_id int;
select @blog_id=i.blog_id from inserted i;
select @tagname=i.blog_tags from inserted i;
select  @tag_id=Tags_id from Tags where Tag_name=@tagname;
print @tagname;
Update Blog_tag set Tag_id=@tag_id where  Blog_id=@blog_id;

Similarly we will see for delete also.

Create trigger [dbo].[trgAfterDelete] on [dbo].[Blogs]
After Delete
As
declare @blog_id int;

select @blog_id=i.blog_id from deleted i;

delete from Blog_tag where  Blog_id=@blog_id;

You can clearly view the difference in delete trigger we are using from deleted i instead of inserted i.
Instead of Trigger:-These are used when we want to check certain conditions before performing insert,update or delete on a table.These are further classified into 3 types:-
 a)Instead of Insert:-These will fire when we will insert a record in a table and will perform the specified query instead of insert.
 b)Instead of Update:-These will fire when we will insert a record in a table and will perform the specified query instead of update.
 c)Instead of delete:-These will fire when we will insert a record in a table and will perform the specified query instead of delete. Now i will show how to create instead of delete trigger.

Create trigger [dbo].[trgInsteadDelete] on [dbo].[Blogs]
instead of Delete
As
declare @blog_id int;
declare @tag_id int;
select @blog_id=i.blog_id from deleted i;
select @tag_id=Tag_id from Blog_tag where Blog_id=@blog_id
begin
if(@tag_id is null)
begin
rollback;
end
else
begin
delete from Blog_tag where  Blog_id=@blog_id;
end
end

Views in Sql Server

View are virtual tables that are compiled at runtime.Data associated with the views are not physically stored in view,they are present in base tables.In Sql Server we create view for security purpose as it restricts user to view columns actually present in the base tables.In Sql Servre we can create a view using following syntax-

CREATE VIEW [dbo].[View_Employee]
AS
SELECT     Emp_ID, Emp_name, Emp_Sal
FROM         dbo.Employee

GO

Sql Server has 2 types of views.There are 2 types of views in sql server. 
1)System Defined Views:-These views are predefined views present in master database.They are further classified into 3- 
a)Information Schema View:-In Sql Server we have around 20 Information Schema View used to display information for database like tables and columns.eg-Information_Schema.Tables,Information_Schema.columns. b)Catalog View :-These were introduced in sql server 2005 and used to show self describing information.eg-Sys.Tables. 
c)Dynamic Management View:-These were also introduced in Sql server2005.Thses view give administrative information of database current state.

2)User Defined Views:-These Views are defined by users.They are of 2 types:- a)Simple View:-When we create a view on single table then it is called simple view.eg-

CREATE VIEW [dbo].[View_Employee]
AS
SELECT     Emp_ID, Emp_name, Emp_Sal
FROM         dbo.Employee

b)Complex View:-When we create a view on more than one table then it is called a complex view.eg-

CREATE VIEW [dbo].[View_CompleteUser_Details]
AS
SELECT     dbo.tblUserLogins.sno, dbo.Employee.Emp_ID, dbo.tblUserLogins.username, dbo.tblUserLogins.password, dbo.Employee.Emp_name, dbo.Employee.Emp_Sal
FROM         dbo.tblUserLogins CROSS JOIN
                      dbo.Employee

GO

Note:-
1)We can insert ,update and delete data from simple view only when we have primary key and all not null fields are in view.
 2)We can not insert/delete data in complex view we can only update it.