用存储过程从Excel导入SQL Server的临时表有点行不通!

[78hgdong]

存储过程属性-新建存储过程:

CREATE PROCEDURE PROtoExcel
@EXN varchar(250)

AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

select * into #temp1 from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source="D:\Excel2005\28.xls";User ID=Admin;Password=;Extended properties=Excel 5.0;Persist Security Info=false')...[sheet1$]
select 序号 as orderNO,货号 as productNO,品名 as productName,规格 as specification,单位 as

Unit,数量 as Quantity,完成 as finished,备注 as Remark from #temp1

go

按"语法检查(C)时"提示:语法检查成功
但是按"确定"时提示:"错误7405:异类查询要求为连接设置ANSI_NULLS和ANSI_WARNINGS选项.这将确保一致的查询语义.请启用这些选项,然后重新发出查询."

请各位帮帮???TKS!

[78hgdong]

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
这两行是我见了"错误7405"提示后才加的,还是老样了,自已先顶!

[zjcxc]

删除存储过程,然后在查询分析器中执行创建存储过程的语句就行了

[78hgdong]

老大刚刚新建的啊,没有保存成功啊.

[lxzm1001]

要听话啊

[78hgdong]

zjcxc(邹建) 老大
现在想通过传递动态的EXCEL文件名进来@EXN,怎么改写下面的语句啊?TKS!

select * into #temp1 from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source="D:\Excel2005\'+@EXN+'.xls";User ID=Admin;Password=;Extended 这样写不行????

[78hgdong]

我听老大的话了,命令已成功完成。
但还有这个问题啊.:
现在想通过传递动态的EXCEL文件名进来@EXN,怎么改写下面的语句啊?TKS!
select * into #temp1 from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source="D:\Excel2005\'+@EXN+'.xls";User ID=Admin;Password=;Extended 这样写不行????

[78hgdong]

如果给出正确答案了,立马赏分了.各位加油啊.

[wbingchao]

用exec执行它

[Doncy]

给个我写的例子 一看就明白了

select @execstr = 'insert into STHold(indexTime,AREA,LOT,HOLDREASON,HOLDREASONDESC,ROUTENAME,STEPNAME,BEGINTMST,PROCESS,
RLSEUSER,PRIO ,PRODUCT,LOTTYPE,RELEASECOMMENT,MAJ_PU,EQUIPMENT,PU,HOLDUSER,Cassette)
select '''+ convert(varchar(100),@datetimeIndexDay,121) +''' , F1,F2,F3,F4,F5, F6,F7,F8,F9,F10 ,F11,F12,F13,F14,F15, F16,F17,F18 from
OPENROWSET(''' + 'MICROSOFT.JET.OLEDB.4.0''' + ',' + '''Text;HDR=NO;DATABASE=E:\mes\dat''' + ' ,' +@charFileNameM + '#csv)'
+ ' where F2 is not null and F2 <> ''' + 'LOT'''
exec (@execstr)

[78hgdong]

结粘,TKS!