是不是因为你对表statelaw_temp权限不够?
解决方案 »
- 请教触发器问题
- SQL SERVER2005 连接失败: SQLState:'01000' SQL Server 错误:14 [Microsoft][ODBC SQL Server Driver][DBNETLIB]无效的连接
- 格式转换问题。清帮忙
- 请问SQL2000 中不可以使用自定义数据类型吗?
- 修改主键,保存时,提示有错误怎么回事啊,
- 菜鸟问题,关于读取另一台服务器上的数据库的表
- insert到指定的行,怎么搞?
- 关于位运算
- 触发器编写 急救
- 一个SQL语句的问题!
- 求救!为什么我的ACESS数据库文件会莫名其妙的达到25M,我还没输任何数据呢?而生成MDE后只有500K?
- 自学SQL Server第一天:一台普通的个人电脑,装上Windows2000 server+SQL Server2000拨号上网,别人怎样从Internet上访问我的SQL Server数据
into 数据库.用户.statelaw_temp
看看这样行不行?我也奇怪:
CREATE PROCEDURE yl_query (
@adate1 varchar(10),@adate2 varchar(10),@acustt varchar(36),@awldh1 varchar(36),
@awldh2 varchar(36),@adnno1 varchar(60),@adnno2 varchar(60),@ahsort varchar(100)
) WITH RECOMPILE AS
declare @sqlquery varchar(900)select @sqlquery = 'select distinct dnss,wl_dh,custwldh,custmc,fsrq,clbz,bzver,res,lastuser,lastdate,t_bz from yl_mi where fsrq >= '
+ '''' + @adate1 +'''' + ' and fsrq <= ' + '''' + @adate2 +''''
if @acustt <>'%'
select @sqlquery = @sqlquery + ' and custmc = @acustt '
if len(@awldh1) <10
select @sqlquery = @sqlquery + ' and wl_dh like ' + '''%' + ltrim(rtrim(@awldh1)) + '%'''
else
select @sqlquery = @sqlquery + ' and wl_dh >= ' + '''' + @awldh1 + '''' + 'and wl_dh <=' + '''' + @awldh2 + '''' select @sqlquery = @sqlquery + ' and custwldh >=' + '''' + @adnno1 + '''' + 'and custwldh <='
+ '''' + @adnno2 + '''' + ' order by ' + @ahsort create table #tempaa( [dnss] [float] NULL , [wl_dh] [varchar] (36) NULL , [custwldh] [varchar] (60) NULL ,
[custmc] [varchar] (36) NULL , [fsrq] [varchar] (10) NULL , [clbz] [varchar] (2) NULL ,[bzver] [varchar] (2) NULL,
[res] [varchar] (100) NULL , [lastuser] [varchar] (8) NULL , [lastdate] [datetime] NULL ,[t_bz] [varchar] (1) NULL )
create table #temaaaa( [dnss] [float] NULL , [wl_dh] [varchar] (36) NULL , [custwldh] [varchar] (60) NULL ,
[custmc] [varchar] (36) NULL , [fsrq] [varchar] (10) NULL , [clbz] [varchar] (2) NULL ,[bzver] [varchar] (2) NULL,
[res] [varchar] (100) NULL , [lastuser] [varchar] (8) NULL , [lastdate] [datetime] NULL )insert into #tempaa
EXECUTE( @sqlquery)
以上代码在SQL65中可以直接INTO到一个表中,但SQL2000不知道为什么不成,也报错,后面我只好改成上面这样。
sp_dboption 'yourdbname','select into/bulkcopy'一般情况下,你的select into/bulkcopy为off值。
把它改成on即可
语句如下
sp_dboption 'yourdbname','select into/bulkcopy',‘TRUE’
set @sYear=2002
EXEC sp_dboption 'yourdatabase', 'select into/bulkcopy', 'true'
select
@sYear as 年份,
count(*) as 总数,
sum(case when month(startusedate)=1 then 1 else 0 end ) as 一月,
sum(case when month(startusedate)=2 then 1 else 0 end ) as 二月,
sum(case when month(startusedate)=3 then 1 else 0 end ) as 三月,
sum(case when month(startusedate)=4 then 1 else 0 end ) as 四月,
sum(case when month(startusedate)=5 then 1 else 0 end ) as 五月,
sum(case when month(startusedate)=6 then 1 else 0 end ) as 六月,
sum(case when month(startusedate)=7 then 1 else 0 end ) as 七月,
sum(case when month(startusedate)=8 then 1 else 0 end ) as 八月,
sum(case when month(startusedate)=9 then 1 else 0 end ) as 九月,
sum(case when month(startusedate)=10 then 1 else 0 end ) as 十月,
sum(case when month(startusedate)=11 then 1 else 0 end ) as 十一月,
sum(case when month(startusedate)=12 then 1 else 0 end ) as 十二月
into statelaw_temp--不要这句就没问题,如果有这句就会提示以下错误:
from law where year(startusedate)=@sYear
EXEC sp_dboption 'yourdatabase', 'select into/bulkcopy', 'false'
set @sYear=2002
EXEC sp_dboption 'yourdatabase', 'select into/bulkcopy', 'true'
select
@sYear as 年份,
count(*) as 总数,
sum(case when month(startusedate)=1 then 1 else 0 end ) as 一月,
sum(case when month(startusedate)=2 then 1 else 0 end ) as 二月,
sum(case when month(startusedate)=3 then 1 else 0 end ) as 三月,
sum(case when month(startusedate)=4 then 1 else 0 end ) as 四月,
sum(case when month(startusedate)=5 then 1 else 0 end ) as 五月,
sum(case when month(startusedate)=6 then 1 else 0 end ) as 六月,
sum(case when month(startusedate)=7 then 1 else 0 end ) as 七月,
sum(case when month(startusedate)=8 then 1 else 0 end ) as 八月,
sum(case when month(startusedate)=9 then 1 else 0 end ) as 九月,
sum(case when month(startusedate)=10 then 1 else 0 end ) as 十月,
sum(case when month(startusedate)=11 then 1 else 0 end ) as 十一月,
sum(case when month(startusedate)=12 then 1 else 0 end ) as 十二月
into statelaw_temp--不要这句就没问题,如果有这句就会提示以下错误:
from law where year(startusedate)=@sYear
EXEC sp_dboption 'yourdatabase', 'select into/bulkcopy', 'false'