CREATE PROCEDURE up_salary @sfile varchar(800) AS SET ANSI_NULLS ON --------- SET ANSI_WARNINGS ON------ declare @s varchar(200),@s1 varchar(200),@str varchar(200),@strsql varchar(8000)if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#tem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[#tem] set @s='Microsoft.Jet.OLEDB.4.0''' set @s1=',''' set @str='Data Source="'+@sfile+'";User ID=Admin;Password=;Extended properties=Excel 5.0' set @str=@s+@s1+@str set @strsql =' select A0103, A0109, A5519 - Z2302 a into #tem from OpenDataSource( '''+@str+''')...[Sheet1$] ' print(@strsql)exec(@strsql) update personinfo set monwage=a.a,sumwage=a.a*12 from #tem a where idencode=a.a SET ANSI_NULLS OFF----------- SET ANSI_WARNINGS OFF-------- GO
楼主是在企业管理器里做的吧?到查询分析器里执行SET ANSI_NULLS ON SET ANSI_WARNINGS ONCREATE PROCEDURE up_salary @sfile varchar(800) AS declare @s varchar(200),@s1 varchar(200),@str varchar(200),@strsql varchar(8000)if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#tem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[#tem] set @s='Microsoft.Jet.OLEDB.4.0''' set @s1=',''' set @str='Data Source="'+@sfile+'";User ID=Admin;Password=;Extended properties=Excel 5.0' set @str=@s+@s1+@str set @strsql =' select A0103, A0109, A5519 - Z2302 a into #tem from OpenDataSource( '''+@str+''')...[Sheet1$] ' print(@strsql)exec(@strsql) update personinfo set monwage=a.a,sumwage=a.a*12 from #tem a where idencode=a.a GOSET ANSI_NULLS OFF SET ANSI_WARNINGS OFF
在那里添加呢?
SET ANSI_NULLS ON ---------
SET ANSI_WARNINGS ON------
declare @s varchar(200),@s1 varchar(200),@str varchar(200),@strsql varchar(8000)if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#tem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#tem]
set @s='Microsoft.Jet.OLEDB.4.0'''
set @s1=','''
set @str='Data Source="'+@sfile+'";User ID=Admin;Password=;Extended properties=Excel 5.0'
set @str=@s+@s1+@str
set @strsql =' select A0103, A0109, A5519 - Z2302 a into #tem from OpenDataSource( '''+@str+''')...[Sheet1$] '
print(@strsql)exec(@strsql)
update personinfo set monwage=a.a,sumwage=a.a*12 from #tem a where idencode=a.a
SET ANSI_NULLS OFF-----------
SET ANSI_WARNINGS OFF--------
GO
SET ANSI_WARNINGS ONCREATE PROCEDURE up_salary @sfile varchar(800) AS
declare @s varchar(200),@s1 varchar(200),@str varchar(200),@strsql varchar(8000)if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#tem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#tem]
set @s='Microsoft.Jet.OLEDB.4.0'''
set @s1=','''
set @str='Data Source="'+@sfile+'";User ID=Admin;Password=;Extended properties=Excel 5.0'
set @str=@s+@s1+@str
set @strsql =' select A0103, A0109, A5519 - Z2302 a into #tem from OpenDataSource( '''+@str+''')...[Sheet1$] '
print(@strsql)exec(@strsql)
update personinfo set monwage=a.a,sumwage=a.a*12 from #tem a where idencode=a.a
GOSET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF