CREATE Procedure Import (@FilePath nvarchar(500))as
beginSET ANSI_NULLS on
SET ANSI_WARNINGS ondeclare @ImportSql nvarchar(4000)CREATE TABLE #tmp1
( f1 nvarchar (390) NOT NULL ,
f2 [numeric](19, 3) NULL ,
f3 nvarchar(20) null )set @Importsql=''
set @importSql='insert into #tmp1 (f1,f2,f3) SELECT * FROM OpenDataSource('''
set @importsql=@importsql + 'Microsoft.Jet.OLEDB.4.0'''
set @importsql=@importsql +','
set @importsql=@importsql +''' Data Source="'
set @importsql=@importsql + @FilePath +'" ;User ID=Admin;Password=;Extended properties=Excel 8.0'''
set @importsql=@importsql + ')... [sheet1$]'
execute (@importsql)
drop table #tmp1end
GO
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
beginSET ANSI_NULLS on
SET ANSI_WARNINGS ondeclare @ImportSql nvarchar(4000)CREATE TABLE #tmp1
( f1 nvarchar (390) NOT NULL ,
f2 [numeric](19, 3) NULL ,
f3 nvarchar(20) null )set @Importsql=''
set @importSql='insert into #tmp1 (f1,f2,f3) SELECT * FROM OpenDataSource('''
set @importsql=@importsql + 'Microsoft.Jet.OLEDB.4.0'''
set @importsql=@importsql +','
set @importsql=@importsql +''' Data Source="'
set @importsql=@importsql + @FilePath +'" ;User ID=Admin;Password=;Extended properties=Excel 8.0'''
set @importsql=@importsql + ')... [sheet1$]'
execute (@importsql)
drop table #tmp1end
GO
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
SET ANSI_WARNINGS on
提示不是有说是这两句的原因吗!!!试试set ansi_nulls off
beginSET ANSI_NULLS on
SET ANSI_WARNINGS on
godeclare @ImportSql nvarchar(4000)CREATE TABLE #tmp1
( f1 nvarchar (390) NOT NULL ,
f2 [numeric](19, 3) NULL ,
f3 nvarchar(20) null )set @Importsql=''
set @importSql='insert into #tmp1 (f1,f2,f3) SELECT * FROM OpenDataSource('''
set @importsql=@importsql + 'Microsoft.Jet.OLEDB.4.0'''
set @importsql=@importsql +','
set @importsql=@importsql +''' Data Source="'
set @importsql=@importsql + @FilePath +'" ;User ID=Admin;Password=;Extended properties=Excel 8.0'''
set @importsql=@importsql + ')... [sheet1$]'execute (@importsql)
drop table #tmp1goSET ANSI_NULLS off
SET ANSI_WARNINGS offend
beginSET ANSI_NULLS on
SET ANSI_WARNINGS on
declare @ImportSql nvarchar(4000)CREATE TABLE #tmp1
( f1 nvarchar (390) NOT NULL ,
f2 [numeric](19, 3) NULL ,
f3 nvarchar(20) null )set @Importsql=''
set @importSql='insert into #tmp1 (f1,f2,f3) SELECT * FROM OpenDataSource('''
set @importsql=@importsql + 'Microsoft.Jet.OLEDB.4.0'''
set @importsql=@importsql +','
set @importsql=@importsql +''' Data Source="'
set @importsql=@importsql + @FilePath +'" ;User ID=Admin;Password=;Extended properties=Excel 8.0'''
set @importsql=@importsql + ')... [sheet1$]'execute (@importsql)
drop table #tmp1SET ANSI_NULLS off --加上就没有错
SET ANSI_WARNINGS offend