sql2005 使用OPENROWSET时报错 报错:链接服务器"(null)"的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 返回了消息 "未指定的错误"。高手救命啊。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 /*************导出到Access********************/ insert into openrowset('Microsoft.Jet.OLEDB.4.0', 'x:\A.mdb';'admin';'',A表) select * from 数据库名..B表 /*************导入Access********************/ insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0', 'x:\A.mdb';'admin';'',A表) 文件名为参数 declare @fname varchar(20) set @fname = 'd:\test.mdb' exec('SELECT a.* FROM opendatasource(''Microsoft.Jet.OLEDB.4.0'', '''+@fname+''';''admin'';'''', topics) as a ') SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="f:\northwind.mdb";Jet OLEDB:Database Password=123;User ID=Admin;Password=;')...产品 ??? 參照操作Excel方法1、在SQL SERVER里查询Excel数据:-- ======================================================SELECT *FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]下面是个查询的示例,它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。SELECT *FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions-------------------------------------------------------------------------------------------------2、将Excel的数据导入SQL server :-- ======================================================SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]实例:SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions-------------------------------------------------------------------------------------------------3、将SQL SERVER中查询到的数据导成一个Excel文件-- ======================================================T-SQL代码:EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'参数:S 是SQL服务器名;U是用户;P是密码说明:还可以导出文本文件等多种格式实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'在VB6中应用ADO导出EXCEL文件代码:Dim cn As New ADODB.Connectioncn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"------------------------------------------------------------------------------------------------4、在SQL SERVER里往Excel插入数据:-- ======================================================insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)T-SQL代码:INSERT INTO OPENDATASOURCE('Microsoft.JET.OLEDB.4.0', 'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$] (bestand, produkt) VALUES (20, 'Test') insert into openrowset('Microsoft.Jet.OLEDB.4.0','EXCEL 8.0;HDR=YES;User id=admin;Password=;IMEX=1;DATABASE=D:\Roy.xls', sheet1$)--(ID,Name)select 2,'b'insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=D:\Roy.xls',sheet1$)(ID,Name)select 2,'b' ' 测试制单日期是否有误 sql = "IF EXISTS (SELECT * " & _ "FROM (SELECT isdate(制单日期) AS dt " & _ "FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _ "'Excel 8.0;hdr=yes;Database=" & Trim$(txtPath.Text) & "', [Sheet1$]) Rowset_3) out1 " & _ "WHERE (dt = 0)) " & _ "SELECT 0 as flag ELSE " & _ "SELECT 1 as flag " Set ado = ExecuteSQL(sql, False) If ado!Flag = 0 Then MsgBox "Excel表中“制单日期”有错误项!", vbInformation GoTo Finish End If我用VB将Excel导入sql2005 openrowsethttp://blog.csdn.net/xys_777/archive/2010/06/16/5673992.aspx 要配置SQL 2005允許遠程連接默認只允許本地連接 除了允许openrowset opendatasource还要在哪儿配置? 连接串如果无错,一般不会有问题,可将连接串直接在ado中试,连通后再用于openrowset 求sql预计 存储过程如何传递IN后的参数 如何查看所建立的表约束如:主键约束,Check约束等等 如何用触发器实现这个问题 给存储过程传参数中单引号的问题。 多列创建聚簇索引,在列上有先后顺序吗? 中文乱码 分布式系统结构讨论 SQL1 运行就出现错误,SQL2运行就不出现错误,但在表记录中 testColumn 有些值是包含有 'xx检测xxx' 内容的! 求助,同一张表查询问题 关于SELECT语句的问题 问行列转换的SQL高级查询写法。
insert into openrowset('Microsoft.Jet.OLEDB.4.0',
'x:\A.mdb';'admin';'',A表) select * from 数据库名..B表 /*************导入Access********************/
insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0',
'x:\A.mdb';'admin';'',A表) 文件名为参数
declare @fname varchar(20)
set @fname = 'd:\test.mdb'
exec('SELECT a.* FROM opendatasource(''Microsoft.Jet.OLEDB.4.0'',
'''+@fname+''';''admin'';'''', topics) as a ') SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="f:\northwind.mdb";Jet OLEDB:Database Password=123;User ID=Admin;Password=;')...产品
???
-- ======================================================
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
下面是个查询的示例,它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
SELECT *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------2、将Excel的数据导入SQL server :
-- ======================================================
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
实例:
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------3、将SQL SERVER中查询到的数据导成一个Excel文件
-- ======================================================
T-SQL代码:
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
参数:S 是SQL服务器名;U是用户;P是密码
说明:还可以导出文本文件等多种格式
实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
在VB6中应用ADO导出EXCEL文件代码:
Dim cn As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"
------------------------------------------------------------------------------------------------4、在SQL SERVER里往Excel插入数据:
-- ======================================================
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)T-SQL代码:
INSERT INTO
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]
(bestand, produkt) VALUES (20, 'Test') insert into openrowset('Microsoft.Jet.OLEDB.4.0','EXCEL 8.0;HDR=YES;User id=admin;Password=;IMEX=1;
DATABASE=D:\Roy.xls', sheet1$)--(ID,Name)
select 2,'b'insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=D:\Roy.xls',sheet1$)(ID,Name)
select 2,'b'
' 测试制单日期是否有误
sql = "IF EXISTS (SELECT * " & _
"FROM (SELECT isdate(制单日期) AS dt " & _
"FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
"'Excel 8.0;hdr=yes;Database=" & Trim$(txtPath.Text) & "', [Sheet1$]) Rowset_3) out1 " & _
"WHERE (dt = 0)) " & _
"SELECT 0 as flag ELSE " & _
"SELECT 1 as flag "
Set ado = ExecuteSQL(sql, False)
If ado!Flag = 0 Then
MsgBox "Excel表中“制单日期”有错误项!", vbInformation
GoTo Finish
End If我用VB将Excel导入sql2005
http://blog.csdn.net/xys_777/archive/2010/06/16/5673992.aspx
默認只允許本地連接
除了允许openrowset opendatasource还要在哪儿配置?