MSSQL链接常用数据库(一)_mssql oledb-优快云博客

原文地址
使用sql访问最常用得几种文件格式，sql，access，excel，txt。把各自得方法稍微总结了下，希望能有用。
     1、访问sql：
       1)、select * from opendatasource('SQLOLEDB','data source=hero;user id=sa').xyjw.dbo.dic_depart
       2)、select * from openrowset('SQLOLEDB','hero';'sa';'','select * from xyjw.dbo.dic_folk') as a
       3)、先添加链接服务器 
            exec sp_addlinkedserver @server='hero',@provider='sqloledb',@srvproduct='',@datasrc='远程服务器名'
            exec sp_addlinkedsrvlogin  @rmtsrvname='hero',@useself='false',@locallogin='',@rmtuser='sa',@rmtpassword='密码'
            select * from hero.xyjw.dbo.dic_folk
    2、访问access
       1)、select * from opendatasource('microsoft.jet.oledb.4.0','data source=''E:\教学信息管理系统.mdb'';')...班级表
       2)、select * from openrowset('microsoft.jet.oledb.4.0','E:\教学信息管理系统.mdb';'admin';'','select * from 班级表') as a
       3)、先添加链接服务器 EXEC sp_addlinkedserver  @server = 'test', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet',        

                                       @datasrc = 'E:\教学信息管理系统.mdb'
           select * from test...班级表
    3、访问excel
       1)、SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="E:\联系方式.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...  

         [Sheet1$]
           但此时查询结果得顺序是按照列名得顺序排列的，而不是按照电子表格中原有的顺序。这是 SQL Server 2000 行集函数 OpenRowSet 和 OpenDataSource 本身的问题

，与访问接口引擎无关，也与 Excel 版本无关。SQL Server 2005 的 OpenRowSet 和 OpenDataSource 不存在这个问题。
       2)、select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=E:\联系方式.xls', 'select * from [Sheet1$]')   
           此种方法可以解决opendatasurce得问题，即查询结果列得顺序与电子表格中原有顺序一致
       3)、使用链接服务器：
           exec sp_addlinkedserver @server='xlsserver',@srvproduct='jet4.0',@provider='microsoft.jet.oledb.4.0',@datasrc='E:\联系方式.xls',@provstr='excel    

        8.0'
           select * from xlsserver...[Sheet1$]
           此时查询结果中列得顺序是按照列名得顺序排列的。
     4、访问txt
        1)、select * from openrowset('microsoft.jet.oledb.4.0','text;hdr=yes;database=E:\',a#txt)
        2)、select * from opendatasource('microsoft.jet.oledb.4.0','text;hdr=yes;database=E:\')...a#txt
        3)、使用链接服务器
            exec sp_addlinkedserver @server='txtserver',@srvproduct='jet4.0',@provider='microsoft.jet.oledb.4.0',@datasrc='E:\',@provstr='text'
            select * from txtserver...test#txt
        例子：
         Name              ID          Xtype UID
      sysrowsetcolumns     4           S     4
      sysrowsets           5           S     4
      sysallocunits        7           S     4
      sysfiles1            NULL        S     4
      syshobtcolumns        ;          S  

 上面的方法，是把所有的内容放在一列里面，如果要分成多个列的话，则需要定义schema.ini文件。schema.ini要和数据文件放在同一个目录下面

      schema.ini文件的定义方法：
       [b.txt]
   ColNameHeader=true
   format=tabdelimited
   col1=Name Text WIDTH 10
   col2=ID Text WIDTH 10
   col3=Xtype Text WIDTH 10
   col4=UID Text WIDTH 10
    第一行：如果文本文件的数据有列名，并且作为查询结果的列名的话，则ColNameHeader应该为true，否则定义为false，但是如果后面定义了列名的话，则不使用文本文件中

数据的列名，而使用后面定义的列名。但是如果数据有列名的，最好定义为true，否则的话，数据的列名将作为表中的数据。
    第二行：format定义数据的分隔符。一般使用的分隔符有空格，制表符，逗号。分别对应的是delimited( )(注意括号里应该有一个空格),tabdelimited,csvdelimited。在选

择分隔符的时候，建议使用tab，因为这样的话数据看起来比较整齐，但是不能为了保证数据的严格整齐，而在数据当中使用多个tab，这样的话读取的数据就会出现错误。其他的

逗号和空格同样。
    第三行到最后：制定列名。在这里需要制定每一列的列名，数据类型。数据类型包括：
      microsoft jet数据类型：bit,byte,short,long,currency,single,double,datetime,text,momo。
      ODBC数据类型：char,float,int,longchar,date.
    后面还应该有：指定字符集：使用ansi或者oem
    特别数据类型转换。后面两个如果没有特殊情况的话，可以不写，才用默认的即可