本地excel导入到远程sql问题...

SELECT   *   FROM     OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',       

'Data Source="e:\book2.xls";UserID=admin;password=;Extended   properties=Excel   8.0')...sheet1$ 

SELECT   *   FROM     OPENDATASOURCE(       

                                    'Microsoft.Jet.OLEDB.4.0',       

                                    'Extended   properties=Excel   8.0;Data   Source=e:\book2.xls;User   ID=admin;password='       

                                        )...sheet1$ 

我试过了,在服务器执行可以,但是在本机上出现以下错误: 

消息   7314，级别   16，状态   1，第   1   行 

The   OLE   DB   provider   "Microsoft.Jet.OLEDB.4.0"   for   linked   server   "(null)"   does   not   contain   the   table   "sheet1$".   The   table   either   does   not   exist   or   the   current   user   does   not   have   permissions   on   that   table. 





---------------------------

SELECT   *   FROM     OPENDATASOURCE(       

                                    'Microsoft.Jet.OLEDB.4.0',       

                                    'Data   Source="e:\book2.xls";

User ID=admin;password=;'Extended   properties=Excel   8.0')...sheet1$ 

--导出excel处理

declare @s nvarchar(4000)

set @s='bcp "'+

+N'select top 100 percent '

+N' case c.colid when 1 then o.name else N'''' end as 表名,'

+N' c.colid as 序号,'

+N' c.name as 字段名,'

+N' t.name 数据类型,'

+N' c.prec as 长度,'

+N' p.value as 字段说明,'

+N' m.text as 默认值'

+N' from '+quotename(@dbname)+N'.dbo.sysobjects o'

+N' inner join '+quotename(@dbname)+N'.dbo.syscolumns c on o.id=c.id'

+N' inner join '+quotename(@dbname)+N'.dbo.systypes t on c.xusertype=t.xusertype'

+N' left join '+quotename(@dbname)+N'.dbo.sysproperties p on c.id=p.id and c.colid = p.smallid'

+N' left join '+quotename(@dbname)+N'.dbo.syscomments m on c.cdefault=m.id'

+N' where o.xtype in(N''U'') and o.status>=0'

+N' order by c.id,c.colid'

+N'" queryout "'+@fname

+N'" /P"" /w'

exec master..xp_cmdshell @s,no_output

go





--查询

sqlserver中直接通过代码访问excel





SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions



甚至可以：

DELETE 

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

  'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions





你还可以：



EXEC sp_addlinkedserver 'ExcelSource',

   'Jet 4.0',

   'Microsoft.Jet.OLEDB.4.0',

   'c:\Temp\Part.xls',

   NULL,

   'Excel 5.0'

GO



EXEC sp_addlinkedsrvlogin 'excelsource', 'false', NULL, NULL, NULL



在excel中选定范围,选择菜单'插入'->'名称'->'定义'



示例



SELECT *

FROM EXCEL...SalesData  (整页使用 Sheet1$) 



如果碰到转换为NULL值请使用文本文件来做DTS转换