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

?????? ????????? ?????? ?????????
???? ???????: 306397 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

?? ??? ??????

??????

????? Microsoft SQL Server ???? ad hoc ?? ????????? ??? ????? ?????? OLE DB ??? ????????. ????? ????? ????? ??? ???? ?????; ????? ????? ???? ?? ??????? ????? ???? sake ?? ??????? ???? ???? ????? ??????? ?????.

?????? Microsoft Excel ?? ??? ???? ?? ???? ?????? OLE DB ????? ????????? ??? ???? SQL ???? ???????. ???? ??? ??????? ???? ?????? ??????? ?????? ???? ?????? Excel ??? ??? ???? ????? ???????? ??? ???? ???? ??? ??????? ??????? ??????? ???? ??? ??????????? ????? ?????? Excel.

??????? ????

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

????? ??????? Studio ????? ???? SQL ?? ????? ??????? ???? ????? ??????? SQL-DMO (?????? ????? ??????) ?? SMO (SQL Server ????? ??????) ?????? ???? ?????? Excel ?? ???? SQL Server ?????. (??? ????? SMO Microsoft SQL Server 2005.) ?? ???? ??? ??????? ??? ???? ????? ????? ??????? ??????? ???????:
  • ????? ???? ???? ??????? ???? ?????.
  • OLE DB ???? ???? ???? ???? ????????? ?? ??? ???????.
  • ???? ???????? ?? ??? ????? ??????? ?????? ????? Excel.
  • ????? ?????? ???? ????? ????? ????? Excel. ???? ???????? ????? ???? Jet ????? ?????? Access.
????? ????? ?????? ?????? sp_addlinkedserver ????? @ srvproduct ??????? ???? ???? ?? ???? ?? ???? ?????.

?????? ??? ??? ?????? SQL Server 2005, ??? ????? ???? ???? ????? ??????? "??? ??????" ?? SQL Server ????? Studio ?? @ srvproduct ??????? ?? ??????? ?????? ????? ????? ?????? Excel.

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

Studio ????? ?????? SQL (SQL Server 2005)
  1. ?? SQL Server Studio ????? ? ?? ?????? ?????? ???? ?? Explorer ????.
  2. ???? ??? ?????? ?????? ??? ?????? ???????? ??? ?? ???? ??? ???? ????? ????.
  3. ?? ????? ??????? ???? ?????? ?????? ?? ???? ??????? ???????:
    1. ?? ???? ???? ?????? ???? ?? ??? ???? ?????.
    2. ??? ???? ???? ???????? ??????.
    3. ?? ??????? ???? ???? ??? Microsoft Jet 4.0 ???? OLE DB.
    4. ?? ?????? "??? ??????" ? ???? Excel ??? ???? ?????? OLE DB.
    5. ?? ???? "???? ????????" ? ???? ?????? ?????? ???? ??? Excel.
    6. ?? ?????? ????? ???? ? ???? 8.0 Excel ???? Excel 2002 ?? Excel 2000 ?? Excel 97.
    7. ???? ??? "?????" ????? ???? ????? ????.
?????? ?? ???? ????? ??? ???? ????? ???? ???? ??????? ????????? ???? ????? ??? ?????? ?? Studio ????? ???? SQL.
????? ??????? (SQL Server 2000)
  1. ?? "????? ???????" ? ???? ??? ?????? ?????? ??????.
  2. ???? ??? ?????? ?????? ??? ?????? ???????? ??? ?? ???? ??? ???? ????? ????.
  3. ?? ??????? ??? ? ???? ??????? ???????:
    1. ?? ???? ???? ?????? ???? ?? ??? ???? ?????.
    2. ?? ?????? ??? Server ? ???? ??? ???? ???????? ??????.
    3. ?? ??????? ??? ???? ? ???? ??? Microsoft Jet 4.0 ???? OLE DB.
    4. ?? ?????? ???? ???????? ? ???? ?????? ?????? ???? ??? Excel ???.
    5. ?? ?????? ????? ???? ? ???? 8.0 Excel ???? Excel 2002 ?? Excel 2000 ?? Excel 97.
    6. ???? ??? "?????" ????? ???? ????? ????.
  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
				
??? ?? ???? ?????? ????? ??? ??????? ?????? ???? ????? ?????? ? ??????? @ srvproduct ??????? ????? ???? ?? "??? ??????" ?? ????? ????? ??????? ? Studio ????? ???? SQL. ???? @ ? @ ???????? ????? ??? ???????.

???????? SQL-DMO ?????? ???? ?????? Excel ??? ??? ???? ?????

????? ??????? ?????? ????? ??????? SQL ?????? ???? ?????? Excel ??? ??? ???? ????? ??????? ?? Microsoft Visual Basic ?? ??? ????? ????. ??? ????? ?????? ????? ??? ???????? ?? ????? ????? ??????? ? Studio ????? ???? SQL.
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
				

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

?? SQL Server 2005? ????? ??????? ?????? ????? ???? SQL (SMO) ?????? ???? ?????? Excel ??? ??? ???? ????? ???????. ?????? ????? ????? ??????? Microsoft Visual Basic .NET ?? ??? ??????? ???. ??? ????? ??????? ???????? ?? ????? Studio ????? ???? SQL. ???? ???? SMO ????? ???? ??? ???? ???? "??????? ????? ????????" (SQL-DMO). Because SMO is compatible with SQL Server version 7.0, SQL Server 2000, and SQL Server 2005, you can also use SMO for configuration of 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 ?? ???? ????? ????? ?????? ????????? ????? ?? ?????? ?? "??????? ????" ?? ????? ???? ???. ??? ???? ??????? ???????? ?????? ?? ???????? ???? ?? ??????? ?? ??????1 ??? Excel ???????? ???????? ??????? ?????? ???? ????? ??????? ?????? ??????? SQL-DMO:
SELECT * FROM XLTEST_DMO...Sheet1$
				
????? ????? ??????? OPENQUERY ????????? ?? ???? ????? Excel ?????? "????" ??? ???:
SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
				
??????? ?????? ???? ????? OPENQUERY ?? ??? ???? ?????. ???????? ?????? ?? ??? ????? ????? ????? ??? ?? ???? ?????.

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

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

????? ??????? ????????? SQL Server ????? ??????? OPENDATASOURCE ?? OPENROWSET ??? ????????? ???? ??? ????? ?????? ??? ????? ?????? 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$)
				
???? ?????? ???? ?? ????? ???? ?????? ?????? ActiveX (ADO) ????????? ?? ??????? ??????? ("???? ?????") ?? OPENROWSET:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
				
??? ???? ?????? ??? ????? ?????? ?? ???? Jet:
???? ?????? ??? ISAM ?????? ???????.
?????? ???? ??? ????? ??? ??? ?????? DataSource ????? ?? ???? ????????. ??? ???? ??????? ??????? ??????? ??? ?????:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$) 
				

?????

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

???????

???? ???????: 306397 - ????? ??? ??????: 21/????/1428 - ??????: 6.4
????? ???
  • 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
????? ??????: 
kbmt kbdatabase kbhowto kbjet KB306397 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????306397

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

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com