Using XLS as Database from MS SQL Server 2000

Hi,
many people uses Excel to store some information and you can face with the problem of using this data from your database. I will show you how you can easily attach Excel file as linked server to the MS SQL Server 2000 and make queries :) .There are several ways to do that, i will show you 2 ways i liked much :)



1st) easiest way:


SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\test\test.xls',
                'SELECT * FROM [Sheet1$]')


2nd)
firstly run this:

DECLARE @RC int

DECLARE @server nvarchar(128)

DECLARE @srvproduct nvarchar(128)

DECLARE @provider nvarchar(128)

DECLARE @datasrc nvarchar(4000)

DECLARE @location nvarchar(4000)

DECLARE @provstr nvarchar(4000)

DECLARE @catalog nvarchar(128)

SET @server = 'MYSERVER'

SET @srvproduct = 'Excel'

SET @provider = 'Microsoft.Jet.OLEDB.4.0'

SET @datasrc = 'C:\test\test.xls'

SET @provstr = 'Excel 8.0'

EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, 

@datasrc, @location, @provstr, @catalog

--with this you have added the linked server

Then you can view the available tables:

EXECUTE SP_TABLES_EX 'MYSERVER'

And the query:

SELECT * FROM XLTEST_SP...Sheet1$

Wish happines to all of you!!!