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

???? ?????? ???? ??????
???? ID: 321686 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

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

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

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

??????????

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

?????

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

?? ???? ??? ????? ??? ?? ????? SQL ??? ?? ??? ???? ?? ????? ?? ?? ??? SQL Server ?????? ??? Excel ???? ???? ?????? ?????? ????? ??...???...?? ????????? ?? ???????? ???? ??? ?? ????? ???? ????? ?????? ???? ?? ??? ?? ??? ????????? ?? ???? ???...?? ??? ????...????? ?? ?????? ?????????? ?? ????? ??? ????? ??? ?????? ?????? ?????? ?? ??? ?? ??? ???????? ?? ???? ?????

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

??? ?? SQL ????? ???????? ??? Excel ???? ???? ???? ?? ??? SQL ????? ???? ???????????????? ?????? (DTS) ???? ???????? ?? SQL Server ???? ?? ??????? ???????? ????? ?? ???? ???? ?? ?? ???????? ?? ?????? ?? ??????????? ?? ??? ??? ?? ?? Excel ???????? ??? ???? ????? ($) ?? ??? ????? ???? ??? ?? ???????????? (?????? ?? ???, ?????1$) ?? ???????????? ???? ???, ?? ???? ???????? ??? ???? ????? ?? ???? Excel ?? ???????????? ????? ????????? Excel ????? ?????? ?? ??? ????, ??? ???

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

???????? ?? ??? ????? ???? ?? ???, ??? ?? ??? Excel ????????????? ???? ???? ??? ?? ????? SQL Server ??? ?? ??? ??? ????????? ?? ???? ???????????? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ???? ????? ???? ?????? ?? ????? ????:
306397 ????: SQL ????? ?? ??? ????? Excel ???? ????? ?? ?????? ????????
????? ??? ?????? ????????????? ?? Excel ???? ??? ?? ????? ?? "XLImport1 ???? EXCELLINK" SQL ????? ???? ?? ???? ??? ?? ???? ????:
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$]')
				

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

?? ?? SQL ????? ?? ??? ??? ADO ????????? ??? SQL ????? (SQLOLEDB ?? ???) Microsoft OLE DB ?? ????? ?? ?????? ???, ?? ?? ?? ?? "?????? ??????" ???????? ?? ????? ?? ???? ??? ?????? ???????? ?? ????? ?? 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 ?? ??? ?? ????? ?? ???? ????

Jet ??????? ????? ??? ????? ?????? ??? ???? ????? ???????? ?? ?????? ???? 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
				
?? ?? ????????, ?? ?? ??? ???????, ???? Microsoft Access ???????, ?????????? ?????? ????? ???? (ISAM) ("????????") ???????, ?? ODBC ??????? ??? Excel ???? ???? ???? ?? ??? ??????, ????? ?? ???? ????

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

  • ??? ???? ?? Excel ???????? ??? ???? ????? ($) ?? ??? ????? ???? ??? ?? ???????????? (?????? ?? ???, ?????1$) ?? ???????????? ???? ??? ?? ?? ???? ???????? ??? Excel ???????? ?????? ?? ???????????? ???? ????
  • ????? ??? ?? ?? Excel ????? ???? ?? ??? ???? ?????? ?? ????, ?????? ??? ?? ????? ???? ????????? ???? ??? ??? ???????????? ???, ?????? SQL Server ?????? ??? ????? ??????????? ??? rearranged ??? ??????? ??????? ?? ??? ?? ?????? ?? ???? ??? ???????? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ?? ????? ????:
    299484 PRB: Access ?????? ?? ????? ?? ???????????? ???? ?? ??? ADOX ?? ????? ???? ??? ????? ?????????? ????? ???? ???
  • ??? ??????? ?? ???? Excel ????? ??? ????-???? ??? ?? ???????? ???? ???? ????????? ???? ??, ?? ??? ??????? "???????" ???? ?????? ?? ??? ???? ?? ?? ??? ?? ??? ???? ??? ?? ?? ??? ??? ???? ????? ?????? ?? ?? ???? ?? ????? ?? ???? ??? ???????? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ?? ????? ????:
    194124 PRB: Excel ??? ?? ????? ???? ??? DAO OpenRecordset ?? ?? ??? ??? ??????

??????

???? ???? ????? ?? ??? ??? Excel ?? ????? ???? ???? ?? ???? ??? ???????? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ?? ????? ????:
257819 ???? ????: Visual Basic ?? VBA ?? Excel ???? ?? ??? ADO ?? ????? ????
Excel ??? ???? ??????????? ???? ?? ????? ?? ???? ??? ???????? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ???????? ?? ????? ????:
295646 ???? ????: ?????????? ADO ???? ????? ?? ???? Excel ?? ??? ADO ???? ?? ???
247412 ?????: Visual Basic ?? Excel ??? ???? ??????????? ???? ?? ??? ???????
246335 ???? ????: ?????????? ???? ?? ??? ADO ?????? ??? ???? ?? ??? Excel ??????? ?? ???
319951 ???? ?? ???: ?????????? ???? ???? ?? ??? Excel ?? ????? ???? ??? SQL Server ???? ???????????????? ??????
306125 ?? ??? ????: Microsoft Excel ??? ???? ???? ?? SQL ?????

???

???? ID: 321686 - ????? ???????: 08 ????? 2014 - ??????: 7.0
???? ???? ???? ??:
  • 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

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

 

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