August 10, 2005 at 12:58 pm
I have a stored procedure that is called from a scheduled job. It looks for a specific table based on the week number and if exists, drop, if not report that it does not exists in the job log. The problem is that I am unable to get it to function correctly.
Would anyone have any insight to my dilema? Any assistance would be greatly appreciated.
Below is the portion of the stored proc that's causing me pain.
declare @week int,
@tblnm char(50),
@suffix char(5),
@drop_oldtbl char(25),
@srctbl char(50),
@SQLString NVARCHAR(4000)
select @tblnm='Current_Cost_Model'
select @week=DATENAME(ww,getdate())
if isnull(@week,1)=1
select @suffix='_'+DATENAME(ww,getdate())
else
select @suffix='_'+ltrim(str(@week))
select @tblnm=rtrim(@tblnm)+ltrim(@suffix)
if exists (select name from cost_model.dbo.sysobjects where name = @tblnm and xtype = 'U')
begin
select @drop_oldtbl = stuff(@tblnm , 17, 2, ltrim(str(DATENAME(ww,getdate()) - 6)))
select @sqlstring = N'drop table Cost_Model.dbo.' + @drop_oldtbl
select 'Table being deleted: ' + @sqlstring
exec sp_executesql @sqlstring
end
else
begin
select @tblnm = stuff(@tblnm , 17, 2, ltrim(str(DATENAME(ww,getdate()) - 6)))
select @sqlstring = 'Current table Current_Cost_model' +@tblnm + 'does not exist'
exec sp_executesql @sqlstring
end
August 10, 2005 at 1:39 pm
You are checking for the existance of @tblnm..
and dropping something else. I'm not sure thats what you're trying to do but...
probably your error is here:
select @drop_oldtbl = stuff(@tblnm , 17, 2, ltrim(str(DATENAME(ww,getdate()) - 6)))
try
select @drop_oldtbl = stuff(@tblnm , 20, 2, ltrim(str(DATENAME(ww,getdate()) - 6)))
HTH
Mathew J Kulangara
sqladventures.blogspot.com
August 10, 2005 at 1:49 pm
sorry about that, the name of the table is 'curr_cost_model' not current_cost_model'.
so the this section of the code: select @drop_oldtbl = stuff(@tblnm , 17, 2, ltrim(str(DATENAME(ww,getdate()) - 6))), is actually correct on starting on the 17th character.
i really appreciate your input. actually, maybe i wasn't clear earlier, the problem is once the table is dropped, and the stored procedure is called again, this step looks for the table and since it no longer esists, i get a failue.
i tried using a true\false scenario, so if the table does not exist, report so and end successfully...but yet to no fruition.
August 10, 2005 at 7:03 pm
cspangler...go look at this other post....the problem seems to be similar except i don't have the time to follow through and verify...
**ASCII stupid question, get a stupid ANSI !!!**
August 11, 2005 at 3:49 am
I think the problem is in the final two statements
select @sqlstring = 'Current table ...'
exec sp_executesql @sqlstring
you're trying to run invalid SQL ie The statement 'Current table ...'.
I'm pretty sure this is not what you intended. I think you meant to just return the message eg:
select 'Current table ... ' + @tblnum + 'does not exist'
HTH
Nigel
How to post questions
August 11, 2005 at 8:13 am
You can also try a print statement:
print @sqlstring
The advantage of this is that you don't get the dashed line above the output.
-n
August 11, 2005 at 10:40 am
thanks everyone for your insight. what i did was to take this portion out, tweak it a little, and place it into a separate job that runs once a week to drop the specific table.
i ran it through a couple of tests and it works fine.
thanks again.
declare @week int,
@tblnm char(50),
@suffix char(5),
@drop_oldtbl char(25),
@SQLString NVARCHAR(4000)
select @tblnm='Current_Cost_Model'
select @week=DATENAME(ww,getdate())
if isnull(@week,1)=1
select @suffix='_'+DATENAME(ww,getdate())
else
select @suffix='_'+ltrim(str(@week))
select @tblnm=rtrim(@tblnm)+ltrim(@suffix)
if exists (select name from cost_model.dbo.sysobjects where name = @tblnm and xtype = 'U')
begin
select @drop_oldtbl = stuff(@tblnm , 17, 2, ltrim(str(DATENAME(ww,getdate()) - 6)))
select @sqlstring = N'drop table Cost_Model.dbo.' + @drop_oldtbl
select 'Table being deleted: ' + @sqlstring
exec sp_executesql @sqlstring
end
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply