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:
--with this you have added the linked server
Then you can view the available tables:
And the query:
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
Then you can view the available tables:
EXECUTE SP_TABLES_EX 'MYSERVER'
SELECT * FROM XLTEST_SP...Sheet1$
Wish happines to all of you!!!
Комментариев нет:
Отправка комментария