openrowset只支持常数, 不支持变量或者表达式做参数
解决方案 »
- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~【麻烦各位了】
- 【求教】wsado15.dll与wsado28.tlb有那先不同??
- 我的服务器的数据库是sql server2000,请问在哪里查具体的sql server的版本号以及是否打了补丁?
- 已知有20000条记录,如何取第12000行?
- 新人求助,查询语句查询不到任何数据。
- 能写出这样的sql语句吗?
- 以下语句错在哪?
- 新手求一条SQL语句 50分!
- 急急急呀,关于sql行交叉的计算????
- 如何判断一个临时表是否存在
- 用Windows帐户登录SQL 2000失败
- 为了导出到excel方便,对表依次查询65535行该用什么命令?
(
@ClassName nvarchar(20),
@Company_ID int,
@Username nvarchar(50),
@FilePath nvarchar(200)
)
AS
declare @s nvarchar(4000)
set @s=N'
Insert Into Sys_Part
(ClassName,Company_ID,PartNo,MFG,DC,Qty,Res,PKG,Item,Application,Date,Class,Due,Price_SW,Curr,UP,WEB,wsPIC,Update_Date,Update_User,Insert_Date,Insert_User)
Select
@ClassName,
@Company_ID,
E.PartNo,
E.MFG,
E.DC,
E.Qty,
E.Res,
E.PKG,
E.Item,
E.Application_,
E.Date,
E.Class,
E.Due,
E.Price_SW,
E.Curr,
E.UP,
E.Web,
E.wsPIC,
E.Update_,
@Username,
Getdate(),
@Username
From OpenRowSet(''Microsoft.Jet.OLEDB.4.0'',''Excel 5.0;HDR=YES;IMEX=1;Database=' + @FilePath + ',Sheet1$) AS E'
exec sp_executesql @s, N'@ClassName nvarchar(20),
@Company_ID int,
@Username nvarchar(50),
@FilePath nvarchar(200)',
@ClassName ,
@Company_ID ,
@Username ,
@FilePath
"Select N'"&ClassName&"',"&Company_ID&",E.PartNo,E.MFG,E.DC,E.Qty,E.Res,E.PKG,E.Item,E.Application_,E.Date_,E.Class_,E.Due,E.Price_SW,E.Curr,E.UP,E.Web,E.wsPIC,E.Update_,'"&Session("Username")&"','"&Now()&"','"&Session("Username")&"'"&_
"From OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;IMEX=1;Database="&Server.MapPath("UploadFile\"&FactName)&"',Sheet1$) AS E")------------------------------
那么不用存储过程,这里总是提示Microsoft OLE DB Provider for SQL Server 错误 '80040e14' ',' 附近有语法错误。 行 81 就是上面的第一行
------------------------------------------------------------------------------
消息 105,级别 15,状态 1,第 30 行
字符串 'Excel 5.0;HDR=YES;IMEX=1;Database=E:\Sean\My Documents\!Edit\!Lan_System\Part\UploadFile\24.xls,Sheet1$) AS E' 后的引号不完整。
消息 102,级别 15,状态 1,第 30 行
'Excel 5.0;HDR=YES;IMEX=1;Database=E:\Sean\My Documents\!Edit\!Lan_System\Part\UploadFile\24.xls,Sheet1$) AS E' 附近有语法错误。
(
@ClassName nvarchar(20),
@Company_ID int,
@Username nvarchar(50),
@FilePath nvarchar(200)
)
AS
declare @s nvarchar(4000)
set @s=N'
Insert Into Sys_Part
(ClassName,Company_ID,PartNo,MFG,DC,Qty,Res,PKG,Item,Application,Date,Class,Due,Price_SW,Curr,UP,WEB,wsPIC,Update_Date,Update_User,Insert_Date,Insert_User)
Select
@ClassName,
@Company_ID,
E.PartNo,
E.MFG,
E.DC,
E.Qty,
E.Res,
E.PKG,
E.Item,
E.Application_,
E.Date,
E.Class,
E.Due,
E.Price_SW,
E.Curr,
E.UP,
E.Web,
E.wsPIC,
E.Update_,
@Username,
Getdate(),
@Username
From OpenRowSet(''Microsoft.Jet.OLEDB.4.0'',''Excel 5.0;HDR=YES;IMEX=1;Database=' + @FilePath + ''',Sheet1$) AS E'
exec sp_executesql @s, N'@ClassName nvarchar(20),
@Company_ID int,
@Username nvarchar(50),
@FilePath nvarchar(200)',
@ClassName ,
@Company_ID ,
@Username ,
@FilePath
(
@ClassName nvarchar(20),
@Company_ID int,
@Username nvarchar(50),
@FilePath nvarchar(200)
)
ASdeclare @mysql varchar(5000)
set @mysql='
declare @ClassName nvarchar(20)
declare @Company_ID int
declare @Username nvarchar(50)
declare @FilePath nvarchar(200)
set @ClassName='+@ClassName+'
set @Company_ID='+@Company_ID+'
set @Username='+@Username+'
set @FilePath='+@FilePath+'
Insert Into Sys_Part
(ClassName,Company_ID,PartNo,MFG,DC,Qty,Res,PKG,Item,Application,Date,Class,Due,Price_SW,Curr,UP,WEB,wsPIC,Update_Date,Update_User,Insert_Date,Insert_User)
Select
@ClassName,
@Company_ID,
E.PartNo,
E.MFG,
E.DC,
E.Qty,
E.Res,
E.PKG,
E.Item,
E.Application_,
E.Date,
E.Class,
E.Due,
E.Price_SW,
E.Curr,
E.UP,
E.Web,
E.wsPIC,
E.Update_,
@Username,
Getdate(),
@Username
From OpenRowSet('+'''Microsoft.Jet.OLEDB.4.0'''+','+'''Excel 5.0;HDR=YES;IMEX=1;Database=''' + @FilePath + ',Sheet1$) AS E'
exec (@mysql)