Archive

Archive for the ‘SQL DB Engine’ Category

Creating Linked Server in SQL Server

November 10, 2010 1 comment

Linked Servers provides access to external datasources be it another databases like Oracle, MySQL, or Excel files.

Advantages:
– Remote server access.
– The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
– The ability to address diverse data sources similarly.

MSDN Links on Linked Servers:
Info: http://msdn.microsoft.com/en-us/library/ms188279(v=SQL.90).aspx
Configure: http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx

My idea here is to query MySQL tables in MS SQL Server as I’m more comfortable with MS SQL queries and semantics. I don’t know if or how I can use Ranking functions, case statements, etc in MySQL DB. Plus I’m also not comfortable in writing queries in DOS like editor or any other freeware tool.

Linked Server to MySQL:

Before creating a Linked Server for MySQL you need to install the MySQL ODBC connector.
Download MySQL ODBC Client: http://dev.mysql.com/downloads/connector/odbc/

Now we need a DSN that will act as a bridge between for creating the Linked Server:
Create a System DSN:
– On Control Pannel -> Admin Tools -> Data Sources (ODBC), Select System DSN tab, click ADD, Selct “MySQL ODBC 3.51 Driver”, Click Finish.
– A new pop-up will come up, “Connector/ODBC 3.51.27 – Configure Data Source Name”.
– On Login Tab: Set fields, Click Test.
– On Advanced Tab, go to following tabs and check the options:
– Flag1: Return Matching Rows, Allow Big Results, Use Compressed Protocol, Change BIGINT columns to Int, Safe
– Flag2: Don”t Prompt Upon Connect, Ignore # In Table Name
– Flag3: Return Table Names for SQLDescribeCol, Disable Transactions
– Click “OK”

Create a New Linked Server:
– On SSMS under Object Explorer go to “Server Objects” -> “Linked Servers”, Richt Click and select “New Linked Server”
– Set an appropriate name on “Linked Server”, like: MYSQL_LINK
– Server Type = Select “Other Data Source” radio button.
– Set Provider = Microsoft OLE DB Provider for ODBC Drivers
– Set the “Product Name” & “Data Source” field you set on configuring the DSN.

This can also be setup by following SQL statements:

-- Create New Linked Server
EXEC sp_addlinkedserver
   @server = 'MYSQL_LINK',
   @srvproduct = 'MySQLDatabase',
   @provider = 'MSDASQL',
   @datasrc = 'MySQLKayako'

-- Pull list of all Servers
select * from sys.servers
EXEC sp_linkedservers

-- Drop the Linked Server
EXEC sp_dropserver 'MYSQL_LINK'

Now you can query the tables and other objects of MySQL database by using OPENQUERY function as shown below:

-- Select a table or view
SELECT * FROM OPENQUERY(MYSQL_LINK, 'select * from MySQL_Table')
-- Execute a function
SELECT * FROM OPENQUERY(MYSQL_LINK, 'EXEC MySQL_Proc param1, param2')

OPENQUERY() also helps in selecting a Stored Procedure result just like a table.

MSDN Ref: http://social.msdn.microsoft.com/Forums/en-US/databasedesign/thread/8a18fdc1-4738-4029-ba40-d4eae483720a/

Physical Join vs Logical Join in SQL Server

October 6, 2010 5 comments

 
Most of us know about JOINS in SQL Server and their types. But do we really know how they are interpreted in SQL Server internally. Today I found lot of informative, interesting and important sources regarding Logical and Physical joins in SQL Server (links below).
 

–> Classifying JOINS mainly into 2 types:

1. Logical Joins: These joins are simple joins that we apply in our SQL queries, like INNER JOIN, RIGHT/LEFT OUTER JOIN, CROSS JOIN, OUTER APPLY, etc.

2. Physical Joins: These are the joins that users don’t use/write in their SQL queries. Instead these are implemented inside SQL Server engine as operators or algorithms to implement the Logical Joins. Their types are Nested Loop, Merge and Hash.

For a particular SQL query when you try to view an Estimated Execution Plan or execute a query by selecting Actual Execution Plan, you can clearly see these Physical Joins under the Execution Plan tab in SSMS.
 

Reference for Logical Joins from one of my old posts: https://sqlwithmanoj.wordpress.com/2009/03/12/sql-server-joins-and-types
 

–> Logical Joins:

– Inner/Outer/Cross: https://blogs.msdn.microsoft.com/craigfr/2006/07/19/introduction-to-joins/
 

–> Physical Joins:

– Nested Loop Joins: https://blogs.msdn.microsoft.com/craigfr/2006/07/26/nested-loops-join/

– Merge Joins: https://blogs.msdn.microsoft.com/craigfr/2006/08/03/merge-join/

– Hash Joins: https://blogs.msdn.microsoft.com/craigfr/2006/08/10/hash-join/


SQL DBA – Collation Conflict in SQL Server

February 23, 2010 6 comments

Cannot resolve the collation conflict between “SQL_Latin1_General_CP437_BIN” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

Just few days back I came across this error when I tried to join two tables from 2 different databases. Didn’t get any clue for a few minutes so I googled up this error (Thanks google baba).

Collation is MS SQL Server is used for specifying the ordering of characters when you create or alter a table or create a domain. Collation settings, which include character set, sort order, and other locale-specific settings, are fundamental to the structure and function of Microsoft SQL Server databases. SQL Server uses them implicitly in SQL statements. To use them explicitly you need to override the default collation or the collation you specified when creating or altering the table or creating the domain. Collation can be applied for char, varchar, text, nchar, nvarchar, and ntext data types.

For example:

SELECT T1.EmployeeName, T2.DeptName
FROM ServerA.dbo.EmpTab T1
JOIN ServerB.dbo.DeptTab T2
ON T1.DeptCode = T2.DeptCode

There could be a possibility that both the servers use different Collations. If yes then you would get an error similar to then one I mentioned at the top of this topic. What you should do in this case?
1. You can alter the default Collation of either of the table columns/fields, but this could have a ripple effect for other tables currently in use with the altered table.
2. Use COLLATE DATABASE_DEFAULT keyword while matching the columns/fields, like:

SELECT T1.EmployeeName, T2.DeptName
FROM ServerA.dbo.EmpTab T1
JOIN ServerB.dbo.DeptTab T2
ON T1.DeptCode COLLATE DATABASE_DEFAULT
= T2.DeptCode COLLATE DATABASE_DEFAULT

More Info:
Collation Types: http://msdn.microsoft.com/en-us/library/ms144250%28SQL.90%29.aspx
Alter Table: http://msdn.microsoft.com/en-us/library/aa275462%28SQL.80%29.aspx
Alter Database: http://msdn.microsoft.com/en-us/library/aa275464%28SQL.80%29.aspx
Other: http://developer.mimer.com/documentation/html_92/Mimer_SQL_Engine_DocSet/Collations3.html