OPENQUERY | SQL with Manoj

Query Excel file source through Linked Server

November 12, 2010 11 comments

In previous post we saw how to setup a Linked Server for MySQL Database. Now lets go with other data sources. Excel files are the most important source of data and report management in a particular department.

When you need to do some query on Excel data, one way is to use Import/Export wizard, push the excel contents to SQL Server and then query on SQL Server DB. Another and easy way is to create a Linked Server to Excel file and query directly the Excel file itself.

You just need to create the Excel file and execute the following SQL Statements below:

–> For Excel 2003 format:

USE MSDB
GO
EXEC sp_addLinkedServer
	@server= 'XLS_NewSheet',
	@srvproduct = 'Jet 4.0',
	@provider = 'Microsoft.Jet.OLEDB.4.0',
	@datasrc = 'C:\Manoj_Advantage\NewSheet.xls',
	@provstr = 'Excel 5.0; HDR=Yes'

– Now, query your excel file in two ways:

SELECT * FROM OPENQUERY (XLS_NewSheet, 'Select * from [Sheet1$]')
SELECT * FROM XLS_NewSheet...[Sheet1$]

–> For Excel 2007 format:

USE MSDB
GO
EXEC sp_addLinkedServer
	@server= 'XLSX_NewSheet',
	@srvproduct = 'ACE 12.0',
	@provider = 'Microsoft.ACE.OLEDB.12.0',
	@datasrc = 'C:\Manoj_Advantage\NewSheet.xlsx',
	@provstr = 'Excel 12.0; HDR=Yes'

– Now, query your excel file in two ways:

SELECT * FROM OPENQUERY (XLSX_NewSheet, 'Select * from [Sheet1$]')
SELECT * FROM XLSX_NewSheet...[Sheet1$]

Note: If your excel file don’t have headers, then set HDR=No

You may need to execute the following SQL Statements to configure the Linked Server initially:

USE MSDB
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO

>> Check & Subscribe my [YouTube videos] on SQL Server.

Categories: Excel Tags: ACE 12.0, Excel, Jet 4.0, Linked Server, Microsoft.ACE.OLEDB.12.0, Microsoft.Jet.OLEDB.4.0, OPENQUERY, sp_addlinkedserver

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/

Categories: SQL DB Engine Tags: Create Linked Server, OPENQUERY