????? ?? ???? ?? ?? ?? ???????? ?? ?????? SQL ????? ?? ??? Excel ?? ????? ???? ????

???? ID: 306397 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
???? ?????? ???????????? ?????? ???????? KB ?? ???????? ????? ?? ??? ???? ????? ????
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

Microsoft SQL Server ?? ??? ??????? ???? OLE DB ???? ??????? ?? ??????? ?? ??? ?? ??? ???? ?? ?????? ???? ??? ??????? ??????? ???? ???? ??? ?? ????? ?? ??? ??? ???? ???? ??, sake ?? ???? ??? ?????? ??? ?? ???? ?? ??? ??????? ?????? ?????? ?? ??? ??? ???? ???? ???

Microsoft Excel ????????????? OLE DB ???? ????? ?? ?? ?? ???? ??? ?????? SQL ????? ?? ?????? ?? ?? ??? ?? ?? ?????? ?? ???? ?? ???? ????? ?? ?? ???????? ???? Excel ???? ????? ?? ???? ???? ??? ?? ????? ?? ??? ??? ???????? ???? ?? ??? ?????? ??, ??? ?? ???????? ?????? ?????? ???? ?????? ?? ????? ???? ?? ??? ?????? ?? ?? ???? Excel ???? ??????
????? ?? ???? ???? | ??????????? ???

???? ???????

Excel ???? ????? ?? ???? ???? ??? ?? ????? ?? ??????

You can use SQL Server Management Studio or Enterprise Manager, a system stored procedure, SQL-DMO (Distributed Management Objects), or SMO (SQL Server Management Objects) to configure an Excel data source as a SQL Server linked server. (SMO are only available for Microsoft SQL Server 2005.) In all of these cases, you must always set the following four properties:
  • The???that you want to use for the linked server.
  • The OLE DBProviderthat is to be used for the connection.
  • The???? ?????or complete path and file name for the Excel workbook.
  • The??????? ????????, ?? ?????? ???? Excel ????????????? ?? ??? ??? ??????? ??? ???????? ??? ??, ??? ??????? expects Access ????????
?????? ???????? ?????????sp_addlinkedserver?? expectssrvproduct @???, ?????? ??? ???????? ??? ?? ???? ????

???:??? ?? SQL Server 2005 ?? ????? ?? ??? ???, ?? ???? ?? ??? ???? ???? ?? ?? ??? ??? ????????? ???? ?????????? ?????? SQL Server ??????? Studio ?? ?? ??? ???srvproduct @??? ??? ???????? ????????? ?? ???? Excel ???? ????? ?? ??? ???

???? Excel ???? ????? ?? ???? ???? ??? ?? ????? ?? ??? ??? ???????? ???? ?? ??? SQL Server ??????? Studio ?? ??????????? ??????? ?? ????? ????

SQL ????? ??????? Studio (SQL Server 2005)
  1. ??? SQL Server ??????? Studio, ??????? ????????? ????????????????????? Explorer.
  2. ????-????? ???????? ??? ?? ??????? ????-????? ????, ?? ???? ????? ???? ??? ?? ?????.
  3. ???? ??? ??? ?? ??? ????????????????, ?? ???? ??? ????? ????? ?? ???? ????:
    1. ????? ??? ????? ???, ???? ??? ?? ????? ?? ??? ??? ??? ??????
    2. ???????????? ???? ????????????
    3. ?????Provider????, ????? ????Microsoft Jet 4.0 OLE DB ???????.
    4. ??????????? ???????? ???,ExcelOLE DB ???? ????? ?? ??? ?? ????
    5. ????????? ?????????? ???, Excel ????? ?? ???? ?? ?? ????? ??? ???? ?????
    6. ???????????? ????????????? ???,Excel 8.0???? Excel 2002, Excel 2000 ?? Excel 97 ????????????? ?? ????
    7. ????? ????,OK?? ???? ??? ?? ????? ??????
???:??? SQL Server ??????? Studio, ?????? ????? ?????????? ?? ???? ????? ?? ??? ?? ???? ??? ?? ????? ??? ??????? ???? ???? ?? ?????
?????????? ??????? (SQL Server 2000)
  1. ?????????? ?????? ???, ??????? ???? ?? ??? ????? ??????????????????.
  2. ????-????? ???????? ??? ?? ??????? ????-????? ????, ?? ???? ????? ???? ??? ?? ?????.
  3. ????? ?????????????? ??, ????? ????? ?? ???? ????:
    1. ????? ??? ????? ???, ???? ??? ?? ????? ?? ??? ??? ??? ??????
    2. ?????????? ??????????? ???, ????? ???????? ???? ?????.
    3. ???????????? ?? ???????, ????? ????Microsoft Jet 4.0 OLE DB ???????.
    4. ????????? ?????????? ??? Excel ?? ???? ?? ?? ????? ??? ???? ????, ??????
    5. ???????????? ????????????? ???,Excel 8.0???? Excel 2002, Excel 2000 ?? Excel 97 ????????????? ?? ????
    6. ????? ????,OK?? ???? ??? ?? ????? ??????
  4. ????? ?????????? ?? ???? ??????? ???? ?? ??? ?? ???? ??? ?? ????? ??? ?? ??????? ???? ?? ??? ????? ?????
  5. ?? ???? ??? ?? ????? ??? ???, ????? ????????????. ????? ??? ?? ???? ?????????? ?? ???????? ????????? ???? ??? ??? ????? ???

???? Excel ???? ????? ?? ???? ???? ??? ?? ????? ?? ??? ??? ???????? ???? ?? ??? ??? ???????? ????????? ?? ????? ????

?? ?? ?????? ???????? ????????? ?? ????? ?? ???? ???sp_addlinkedserver???? Excel ???? ????? ?? ???? ???? ??? ?? ????? ?? ??? ??? ???????? ????:
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 parameter values
SET @server = 'XLTEST_SP'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:\book1.xls'
SET @provstr = 'Excel 8.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, 
@datasrc, @location, @provstr, @catalog
As noted above, this stored procedure requires an additional, arbitrary string value for the@srvproductargument, which appears as "Product name" in the Enterprise Manager and SQL Server Management Studio configuration. The@location, ??@catalogarguments are not used.

Using SQL-DMO to configure an Excel data source as a linked server

You can use SQL Distributed Management Objects to configure an Excel data source as a linked server programmatically from Microsoft Visual Basic or another programming language. ?? ???? ??? ???? ??? ?????????? ??????? ?? SQL Server ??????? Studio ??????????? ?? ???????? ?? ?? ?????? ???? ?????? ???
Private Sub Command1_Click()
    Dim s As SQLDMO.SQLServer
    Dim ls As SQLDMO.LinkedServer
    Set s = New SQLDMO.SQLServer
    s.Connect "(local)", "sa", "password"
    Set ls = New SQLDMO.LinkedServer
    With ls
        .Name = "XLTEST_DMO"
        .ProviderName = "Microsoft.Jet.OLEDB.4.0"
        .DataSource = "c:\book1.xls"
        .ProviderString = "Excel 8.0"
    End With
    s.LinkedServers.Add ls
    s.Close
End Sub

???? Excel ???? ????? ?? ???? ???? ??? ?? ????? ?? ??? ??? ???????? ???? ?? ??? SMO ?? ????? ????

SQL Server 2005 ??? ?? ???? Excel ???? ????? ?? ????????? ???? ???? ??? ?? ????? ?? ??? ??? ???????? ???? ?? ??? SQL Server ??????? ???????? (SMO) ?? ????? ?? ???? ???? ??? ???? ?? ???, ???? Microsoft Visual Basic .NET ?? ???? ???? ???????????? ???? ????? ?? ???? ???? ?? SQL Server Studio ????????? ??????????? ??? ?????? ???? ?????? ???? ?????? ??? SMO ???????? ???? ?? ????? ??, ?? ?????? ??????? ???????? (SQL-DMO) ???????? ???? ?? ???????????? ???? ??? SMO SQL Server ??????? 7.0, SQL Server 2000 ?? SQL Server 2005, ?? ??? ???? ?? ??????? ?? ?? ????? ?? ???? SMO SQL Server 2000 ?? ??????????? ?? ????
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim s As Server
        Dim conn As ServerConnection
        Dim ls As LinkedServer

        conn = New ServerConnection("ServerName\InstanceName", "YourUesrName", "YourPassword")
        s = New Server(conn)
        Try
            ls = New LinkedServer(s, "XLTEST_DMO")
            With ls
                .ProviderName = "Microsoft.Jet.OLEDB.4.0"
                .ProductName = "Excel"
                .DataSource = "c:\book1.xls"
                .ProviderString = "Excel 8.0"
            End With
            ls.Create()
            MessageBox.Show("New linked Server has been created.")
        Catch ex As SmoException
            MessageBox.Show(ex.Message)
        Finally
            ls = Nothing
            If s.ConnectionContext.IsOpen = True Then
                s.ConnectionContext.Disconnect()
            End If
        End Try

    End Sub
End Class

Excel ???? ????? ?? ???? ???? ??? ?? ????? ?? ??????

???????? ???? ?? ??? ???? Excel ???? ????? ?? ??? ??? ???? ???? ??? ?? ?????, ?? ???? ???? ?? ?????? ???????? ?? ???? ???? ??????? ????????? ?? ?????? ????? ?? ?? ???? ???? ?????? ?? ???, Excel ???? ?? Sheet1 ??? ???????? ???? ?? ????????? ?? ???? ???? ???, ????? ??? ?? SQL DMO ?? ????? ?? ???????? ???? ??? ???? ??? ?? ????? ????? ???? ??:
SELECT * FROM XLTEST_DMO...Sheet1$
?? ?? ??? ??? ?? "passthrough" ????? ??, Excel ???? ??? ?? ????? ?? ?????? ???? ?? ??? OPENQUERY ?? ????? ?? ?? ???? ???:
SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
OPENQUERY expects ???? ???? ??? ???? ??? ?? ????? ?? ??? ??? ??? ????? ?? ?? ??? ??? ??????? ?????????? ???, ?? ??? ?????? ????

?? ????? ?????? ?? ????? ?? Excel ???? ??? ?? ????? ?? ?????? ??? ?? ??? ???????? ?? ???? ?? ??????? ?? ???? ???:
EXECUTE SP_TABLES_EX 'XLTEST_DMO'

???? Excel ???? ????? ?? ?????? ??????? ?? ????? ???? ??? ??????

?? ????? ?? ???? SQL Server ?????? ???????? ?? OPENDATASOURCE ?? OPENROWSET ?????? ??? ?? ??? ?? ???? ?? ?????? infrequently ?????? Excel ???? ??????? ?? ????

???:??? ?? SQL Server 2005 ?? ????? ?? ??? ???, ?? ????????? ???? ?? ?? ?? ????? ???? ??? ?????? ?????????????? ?? ??? ??? ????? ?????? ??? SQL Server ????? ??????? ???????????, ?? ????? ??:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$
????? ??? ?? OPENROWSET ????? ??? ???????? ???????? ????? ?? ??? ("??????? ????????") ????:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Excel 8.0;Database=c:\book1.xls', Sheet1$)
???????? ?? ????? ("??????? ????????") ???? OPENROWSET ?? ??? ????? ?? ??? ??? ActiveX ???? ???????? (ADO) ?????? ??????? ?? ???? ??:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
?? ???????? raises ??? ??????? ?? ????? ?????? ??:
??????? ????? ISAM ???? ??? ????
???:?? ?????? ?? ???? ?? ?? ??? ?? ???? ???????? ??????? ???????? ?????. ?????? ?? ???, ????? ???? ??? ??:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
????? ?? ???? ???? | ??????????? ???

??????

SQL ????? ????? ?? ???? ?? ?? ??, ?? ?????? ???? ??????? ???????? OLE DB ???????, ??????? ????????????? ?? ????? ???? ?? ADO ?? Excel ?? ??? ????? ?? ???? ??? cautions ???? ???? ????????? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ????? ????::
257819
(http://support.microsoft.com/kb/257819/ )
Visual Basic ?? VBA ?? ???? ?? Excel ?? ??? ADO ?? ????? ???? ????
SQL ????? ???????? ??????? ?? ???? ??? ???? ??????? ?? ??? ????? Microsoft ?????? ??????? (MSDN) ??? ???? ?? ????:
(ide) http://msdn2.Microsoft.com/en-us/library/ms162169 .aspx
(http://msdn2.microsoft.com/en-us/library/ms162169(ide).aspx)
????? ???? ?? ??? ???? ???? ?? ???? ??? ???? ??????? ?? ????? ??? ?????? ?????????????? ?? ???, ????? MSDN ??? ???? ?? ????:
(ide) http://msdn2.Microsoft.com/en-us/library/ms189978 .aspx
(http://msdn2.microsoft.com/en-us/library/ms189978(ide).aspx)
????? ?? ???? ???? | ??????????? ???

???

???? ID: 306397 - ????? ???????: 04 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 7.0 Standard Edition
??????: 
kbdatabase kbhowto kbjet kbmt KB306397 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:306397
(http://support.microsoft.com/kb/306397/en-us/ )
????? ?? ???? ???? | ??????????? ???

??????????? ???

 
????? ?? ???? ????????? ?? ???? ????