???? ID: 321686 - ????? ???????: 17 ??????? 2011 - ??????: 3.0

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

???? ?????? ???????????? ?????? ???????? KB ?? ???????? ????? ?? ??? ???? ????? ????
?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
?????? ??????This article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

?? ????? ??

??? ?? ??????? ???? | ??? ?? ??????? ????

??????

?? ??? ?? ??? ???? ????????? ???? ?? ?? ???? ???? ?? ??? ???? Microsoft Excel ?????????? ?? Microsoft SQL Server ??????? ??? ??????? ??????? ?? ????? ?? ???? ?????

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

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

?? ???? ??? ????? ?? ????? ?? Excel ??? ???? ???? ????:
  • SQL Server ???? ??????????????? ???? (DTS)
  • Microsoft SQL Server 2005 ??????? ???? (SSIS)
  • SQL ????? ???? ??? ?? ?????
  • SQL ????? ?????? ????????
  • ActiveX ???? ???????? (ADO) ?? SQL ????? ?? ??? Microsoft OLE DB ???????
  • ADO ?? Jet 4.0 ?? ??? Microsoft OLE DB ???????
????? ?? ???? ????

??????????

????? ???? outlines ???????? ?????????, ??????????, ??????? ?????? ?? ?????? ??? ?? ???? ???:
  • Microsoft SQL Server 7.0 ?? Microsoft SQL Server 2000 ?? Microsoft SQL Server 2005 ?? ?????? ???????
  • Visual Basic ?? ????? ???? ???? ADO ????? ?? ??? Microsoft Visual Basic 6.0
?? ???? ?? ??? ???? ?? ?? ?? ????? ???? ?? ?????? ???:
  • ???? ??????????????? ??????
  • ???? ??? ?? ????? ?? ?????? ????????
  • Visual Basic ??? ADO ?????
????? ?? ???? ????

?????

Vs. Append ???? ????

?? ???? ??? ???????? ????? SQL ??? ?? demonstrate SELECT... ???... ?? ???????? ?? ????? ?? Excel ???? SQL ????? ???? ?? ???? ??? ???? ?????? ?????? ?????? ?? ?? ???? ??? ?? ????? ??? ?????? ???? Append ???????? ????? ?? ?????? ?????????? ?? ????? ??? ????? ??? ?????? ?????? ?????? ?? ??? ???? ???? ?? ????? ???????? ???... SELECT... ?? ???????? ?? ?????? ?????

DTS ?? SSIS ????? ????

SQL ????? ???????? ??? Excel ???? ???? ???? ?? ??? ?? SQL Server ???? ??????????????? ?????? (DTS) ???? ??????? ?? SQL Server ???? ?? ??????? ??????? ?? ????? ?? ???? ???? ?? ?? ??????? ?? stepping ??? ?? ?? Excel ???????? ??? ?? ???? ????? ($) ?? ??? ????? ???? ??? (?????? ?? ???, Sheet1 $) ???????????? ?? ??????? ???, ?? ???? ????? ?? ???? ???? ???????? ?? ??? ????????? Excel ?????? ??? ??? Excel ????? ?????? ?? ??? ?????

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

???????? ?? ??? ?????, ?? ??? ???? ??? Excel ????????????? ?? SQL ????? ??? ??? ???? ??? ?? ????? ?? ??? ??? ???????? ?? ???? ???????????? ??????? ?? ???, ???? ?? ???? ?????? ?? ????? ?? ???? ?? Microsoft ???????? ??? ?????::
306397  (http://support.microsoft.com/kb/306397/EN-US/ ) HOWTO: SQL ????? ?? ??? ????? Excel ???? ????? ?? ???????? ?? ?????? ????
????? ??? ?????? ????????????? ?? Excel ???? ??? ?? ????? ?? "EXCELLINK" SQL ????? ???? ?? ???? ??? XLImport1 ?? ??? ?? ???? ???? ???? ??:
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
?? ?? ???? ??? ?? ?????? ?? ????????? ????? ?? ??????? ??? passthrough ????? ?? ??????????? OPENQUERY ?? ????? ??:
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
    'SELECT * FROM [Customers$]')

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

??? ?? ???? ???? ??? ?? ????? ?? ??? ??? Excel ????????????? ?? ??? ??????? ??????? ?? ???????? ???? ?? ??? ???? ?????, ?? ?? ???? ?? ???? ??? ???? ???? ????? ???????? ?? ??? OPENDATASOURCE ?? OPENROWSET ?????? ?? ????? ????? ????? ??? ????? ?? ???? ???? ?????? ?? Excel ????????????? ?? ??? SQL Server ???????? ???:
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

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

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

ADO ?? SQLOLEDB ?? ????? ????

?? ?? ?????? ??? SQL ????? ?? ??? ??? ADO ????????? ??? Microsoft OLE DB ?? ??? SQL ????? (SQLOLEDB) ?? ????? ??, ?? ?? ?? "?????? ??????" ???????? ?? ????? ?? ?????????? ??????? ?? ????? ????SQL ????? ??? Excel ???? ???? ???? ?? ??? ????

????? Visual Basic 6.0 ??? ????? ????????? ?????? ActiveX ???? ???????? (ADO) ?? ????? ?? ???????? ??? ????????? ???? ?? ??? ????? ?? ?? ?? OPENDATASOURCE ?? OPENROWSET ?? ??? SQLOLEDB ??????? ?? ????? ???? ?????
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _
        "Initial Catalog=<database>;User ID=<user>;Password=<password>"

    'Import by using OPENDATASOURCE.
    strSQL = "SELECT * INTO XLImport6 FROM " & _
        "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
        "'Data Source=C:\test\xltest.xls;" & _
        "Extended Properties=Excel 8.0')...[Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and object name.
    strSQL = "SELECT * INTO XLImport7 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "[Customers$])"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and SELECT query.
    strSQL = "SELECT * INTO XLImport8 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "'SELECT * FROM [Customers$]')"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set cn = Nothing

ADO ?? ??? ??????? ?? ????? ????

????? ?????? ??? ????? ????? ADO SQLOLEDB ??????? ?? ??? ???? Excel ?? SQL ???? ???? ?? ?????? ?? ?????? ???? ?? ???? ?? Excel ????? ?? ?????? ???? ?? ??? OLE DB ??????? Jet 4.0 ?? ??? ?? ????? ?? ?????

??? ??????? ???? ?? ??? ????? ?????? ??? ??? ????? ???????? ?? ?????? ???? SQL ??? ??? ????? ??????? ?????? ?? ???? ???:
  • [Microsoft Access ??????? ?? ??? ???? ??].[?????? ???]
  • [ISAM ???;ISAM ??????? ????????].[?????? ???]
  • [ODBC;ODBC ??????? ????????].[?????? ???]
?????? SQL Server ??????? ?? ??? ??? ODBC ??????? ????? ?? ??? ?? ?????? ??? ????? ?????? ?? ????? ???? ??? ?? ???? ODBC ???? ????? ?? ??? (DSN) ?? ??? DSN ?? ??????? ???????? ?? ????? ?? ???? ???:
DSN:
    [odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]

DSN-less:
   [odbc;Driver={SQL Server};Server=<server>;Database=<database>;
       UID=<user>;PWD=<password>]
????? Visual Basic 6.0 ??? ????? ADO ???? ?? ??? ????????? ?? ?????? ?????? ?? ???????? ??? ?? ??? ????? ????????? ???? ?? ?? Excel ???? ???? ???? ?? ??? SQL ????? ??? ADO ??????? ?? Jet 4.0 ??????? ?? ????? ?? ???? ?????
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\test\xltestt.xls;" & _
        "Extended Properties=Excel 8.0"
    
    'Import by using Jet Provider.
    strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=<server>;Database=<database>;" & _
        "UID=<user>;PWD=<password>].XLImport9 " & _
        "FROM [Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
        
    cn.Close
    Set cn = Nothing
?? ?? ????????, ?? ?? ??? ??????? ??????, Excel ??? ???? ?? ???? Microsoft Access ???????, ?????????? ?????? ????? ???? (ISAM) ("????????") ???????, ?? ODBC ??????? ??? ???? ???? ?? ??? ?? ?? ????? ?? ???? ????

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

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

  • ??? ???? ?? Excel ???????? ??? ???? ????? ($) ?? ??? ????? ???? ??? ?? ???????????? (?????? ?? ???, Sheet1 $) ?? ???????????? ?? ???? ???????? ?? ??? ????????? Excel ?????? ?? ??? ???
  • ??? ???????????? ??? ??????? ?? ?? ???? ?? ?? ??? ???? ?????? ?????? ??? ?? ????? ?? Excel ????? ???? ????????? ?????? SQL Server ?????? ??? ????? ??? rearranged ??????????? ??????? ??????? ?? ??? ?? ?????? ?? ???? ??? ???????? ??????? ?? ??? Microsoft ???????? ??? ???? ????? ?? ??? ????? ???? ?????? ?? ????? ????:
    299484  (http://support.microsoft.com/kb/299484/EN-US/ ) PRB: ????? ????? ??? ???? ?? ?????????????? ?? ?? Access ?????? ?? ??????? ?? ??????? ???? ?? ??? ADOX ?? ?????
  • ??? ??????? ?? ????????? ???? ?? ?? ????-???? ??? ?? ???????? ???? Excel ?? ???? ????? ???, ?? ??? ??????? "majority" ???? ?????? ?? ??? ???? ?? ?? ??? ?? ??? ???? ??? NULLs ?? ??? ??? ???? ????? ?????? ?? ?? ???? ?? ??? ???? ???? ?? ???? ??? ???????? ??????? ?? ??? Microsoft ???????? ??? ???? ????? ?? ??? ????? ???? ?????? ?? ????? ????:
    194124  (http://support.microsoft.com/kb/194124/EN-US/ ) PRB: Excel ??? NULL DAO OpenRecordset ????? ?? ??? ??? ??? ??
????? ?? ???? ????

??????

??? ???? ????? ?? ??? ??? Excel ?? ????? ???? ???? ?? ???? ??? ???????? ??????? ?? ??? Microsoft ???????? ??? ???? ????? ?? ??? ????? ???? ?????? ?? ????? ????:
257819  (http://support.microsoft.com/kb/257819/EN-US/ ) HOWTO: ????? ADO Visual Basic ?? VBA ?? ???? ?? Excel ?? ???
For additional information about how to transfer data into Excel, click the article numbers below to view the articles in the Microsoft Knowledge Base:
295646  (http://support.microsoft.com/kb/295646/EN-US/ ) HOWTO: Transfer Data from ADO Data Source to Excel with ADO
247412  (http://support.microsoft.com/kb/247412/EN-US/ ) INFO: Methods for Transferring Data to Excel from Visual Basic
246335  (http://support.microsoft.com/kb/246335/EN-US/ ) HOWTO: Transfer Data from an ADO Recordset to Excel with Automation
319951  (http://support.microsoft.com/kb/319951/EN-US/ ) HOW TO: Transfer Data to Excel by Using SQL Server Data Transformation Services
306125  (http://support.microsoft.com/kb/306125/EN-US/ ) HOW TO: Import Data from SQL Server into Microsoft Excel
????? ?? ???? ????
???? ???? ???? ??:
  • Microsoft Excel 2000 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
????? ?? ???? ????
??????: 
kbhowtomaster kbjet kbmt KB321686 KbMthi
????? ?? ???? ????
???? ?????? ???????????? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:321686  (http://support.microsoft.com/kb/321686/en-us/ )
????? ?? ???? ????