[SQLSTATE 42S22] error 207 这个是什么错误啊

[sd722522]

写的mssql 定时任务 里面 执行存过，
存过我自己执行的时候没有问题，
在定时任务里面执行的时候，
时好时坏，
错误的时候报错
Message
Executed as user: NT AUTHORITY\SYSTEM. Invalid column name 'TaskId'. [SQLSTATE 42S22] (Error 207) Invalid column name 'Date'. [SQLSTATE 42S22] (Error 207) Invalid column name 'BusinessTaskId'. [SQLSTATE 42S22] (Error 207) Invalid column name 'ProvinceCode'. [SQLSTATE 42S22] (Error 207) Invalid column name 'TaskId'. [SQLSTATE 42S22] (Error 207) Invalid column name 'Date'. [SQLSTATE 42S22] (Error 207) Invalid column name 'BusinessTaskId'. [SQLSTATE 42S22] (Error 207) Invalid column name 'ProvinceCode'. [SQLSTATE 42S22] (Error 207). The step failed.


查了一下 207是 第 2 环堆栈已被占用。 不懂问什么呀，请大牛帮帮忙

[sd722522]

引用 4 楼 OrchidCat 的回复:

lz 这个字段TaskId 啥的，是不是建在临时表里的啊！

这个是我原有表里面的 插到临时表里的 我贴代码

[sd722522]

USP_OTSS_SgsCounts
@NeedTime varchar(20)
AS
BEGIN 
	declare @Time varchar(10)
	declare @Table varchar(100)
	declare @Num int

	if(@NeedTime is null)
		set @Time=convert(varchar(10),getdate(),120)
	else
		set @Time=@NeedTime


	select @Num=Count(*) from OTSS_SgsCounts where Time=@Time

	if(@Num=0)
	BEGIN
		set @Table='OTSS_SgsTaskDataLog'+convert(varchar(10),cast(@Time as datetime),112)
		IF NOT EXISTS(SELECT * FROM sys.objects WHERE OBJECTPROPERTY(object_id('dbo.'+@Table+''),'IsTable')=1)
			set @Table='OTSS_SgsTaskDataLog'
		--print @Time
		--print @Table
		exec USP_OTSS_SgsCountsStaticErveryDay @Time,@Table
		
	END
END

-----------------------------------------------------------------------

USP_OTSS_SgsCountsStaticErveryDay 
@Time varchar(10),
@TableName varchar(100)
AS
BEGIN
Declare @curBusinessTaskNum int 
Declare @Sql nvarchar(1000)

set @Sql =N'select Date,TaskId,BusinessTaskId,ProvinceCode into ##t from(
select '''+@Time+''' as Date,TaskId,BusinessTaskId,ProvinceCode  from dbo.OTSS_SgsTaskProvinceLog where BusinessTaskId in(
select distinct BusinessTaskId from ' + @TableName + ')
union all
select  '''+@Time+''' as Date,TaskId,BusinessTaskId,ProvinceCode from dbo.OTSS_SgsTaskProvince where BusinessTaskId in(
select distinct BusinessTaskId from ' + @TableName + ')
)a'

--print @Sql
EXEC sp_executesql @Sql

select @curBusinessTaskNum=count(*) from ##t
if @curBusinessTaskNum >0
begin
	select Date,TaskId,Glid,BusinessTaskId,ProvinceCode into ##p from(
		select Date,t.TaskId,t.Glid,BusinessTaskId,ProvinceCode from ##t as temp left join  dbo.OTSS_SgsTask as t on temp.TaskId=t.TaskId  
		union all
		select Date,t.TaskId,t.Glid,BusinessTaskId,ProvinceCode from ##t as temp left join  dbo.OTSS_SgsTaskLog as t on temp.TaskId=t.TaskId  
	)b 
	where TaskId is not null 
	
	

	set @Sql =N' insert into OTSS_SgsCounts(Time,TaskId,Theme,ProvinceCode,b.Status,Counts)
	select Date,TaskId,TaskNo,ProvinceCode,b.Status,num from ##p as a
	inner join (
	select BusinessTaskId,Status,count(*)num from '+ @TableName + '
	group by BusinessTaskId,Status	
	)b
	on a.BusinessTaskId = b.BusinessTaskId
	inner join dbo.OTSS_SgsOrders o 
	on a.Glid= o.Id '
	--print @Sql
	EXEC sp_executesql @Sql
end
drop table  ##t
drop table  ##p
END

[sd722522]

这个是作业 其实就是执行 第一个存过 传@NeedTime NULL 每天23点执行

USE [msdb]
GO
/****** Object:  Job [OTSS_Counts]    Script Date: 01/29/2013 13:15:16 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Engine Tuning Advisor]    Script Date: 01/29/2013 13:15:16 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Engine Tuning Advisor' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Engine Tuning Advisor'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'OTSS_Counts', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'远程调用脚本,完成群发汇总', 
		@category_name=N'Database Engine Tuning Advisor', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [1]    Script Date: 01/29/2013 13:15:17 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC	[dbo].[USP_OTSS_SgsCounts]
		@NeedTime = NULL', 
		@database_name=N'OTSSDB', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'everyday', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20130122, 
		@active_end_date=99991231, 
		@active_start_time=230000, 
		@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

[sd722522]

求助啊，自己顶一下

[kingtiy]

Invalid column name 'TaskId' 无效的列名啊. 是不是有临时表的创建之类的.注意查下.还是说引用的数据库不对.

[sd722522]

引用 2 楼 kingtiy 的回复:

Invalid column name 'TaskId' 无效的列名啊. 是不是有临时表的创建之类的.注意查下.还是说引用的数据库不对.

无效的列名这个我也看到了 我创建了一个临时表 然后有给删除了 最主要的是我自己执行存过的时候没有问题， 然后创建作业的时候就有的时候好用有的时候不好用

[Mr_Nice]

lz 这个字段TaskId 啥的，是不是建在临时表里的啊！