Unstable Cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Unstable Cursor

Author

Topic

vdavid70
Yak Posting Veteran

67 Posts

Posted - 2006-10-09 : 06:26:44

Hello Folks,

I got this rather funny sql cursor that was written by our vendors and given to me to run on a monthly basis to delete some tables in the database. I copied and pasted this code into query analyzer and parsed to see if it was ok. It was ok.

I then went ahead to create a scheduled agent job for it, when i got to the stage where i had to paste the code, i did that and parsed it. This time it came up with errors saying

"Error 170:Line 20, Incorrect syntax near '@table_name'.

What is could be wrong here.Please find below a copy of the code.

-- remove all old Transfer Desk work tables
declare @bu_code char(3)
declare @opr_code char(3)
declare @table_name varchar(50)
declare bu_cursor cursor for select DB_CODE, OPR_CODE from DB_DEFN, OPR where DB_CODE <> ''
open bu_cursor
fetch next from bu_cursor into @bu_code, @opr_code
while @@FETCH_STATUS = 0
begin
if exists (select top 1 'OK' from sysobjects
where name like @bu_code+'[_]'+rtrim(@opr_code)+'[0-9]%[_][WE]R[RK]%')
begin
declare tab_cursor cursor for
select name from sysobjects where name like @bu_code+'[_]'+RTRIM(@opr_code)+'[0-9]%[_][WE]R[RK]%'
open tab_cursor
fetch next from tab_cursor into @table_name
while @@FETCH_STATUS = 0
begin
print 'DROP TABLE ' +@table_name
exec ('DROP TABLE ' +@table_name)
fetch next from tab_cursor into @table_name
end
close tab_cursor
deallocate tab_cursor
end
fetch next from bu_cursor into @bu_code, @opr_code
end
close bu_cursor
deallocate bu_cursor
go

-- Remove old Control desk tables
declare @bu_opr varchar(124)
declare @table_name varchar(124)
declare @del_table_name varchar(124)
declare bu_opr_cursor cursor for
select rtrim(DB_CODE+'[_]'+rtrim(OPR_CODE)+'[_]') from DB_DEFN, OPR
where DB_CODE <> ''
open bu_opr_cursor
fetch next from bu_opr_cursor into @bu_opr
while @@FETCH_STATUS >= 0
begin
if exists (select TOP 1 'OK' from sysobjects where name like @bu_opr +'%'
and name not like @bu_opr + 'CMP%' and name not like @bu_opr + 'BMP%')
begin
declare tmp_table_cursor cursor for
select @bu_opr + RTRIM(FILTER_TYPE_CODE)+'[_][0-9]%' from FILTER_TYPE
open tmp_table_cursor
fetch next from tmp_table_cursor into @table_name
while @@FETCH_STATUS >= 0
begin
declare table_cursor cursor for select name from sysobjects where name like @table_name
open table_cursor
fetch next from table_cursor into @del_table_name
while @@FETCH_STATUS >= 0
begin
print 'DROP TABLE ' + @del_table_name
exec ('DROP TABLE ' + @del_table_name)
fetch next from table_cursor into @del_table_name
end
close table_cursor
deallocate table_cursor

fetch next from tmp_table_cursor into @table_name
end
close tmp_table_cursor
deallocate tmp_table_cursor
end
fetch next from bu_opr_cursor into @bu_opr
end
close bu_opr_cursor
deallocate bu_opr_cursor
go

-- Remove more old Control desk temporary tables
declare @bu_opr varchar(124)
declare @table_name varchar(124)
declare @del_table_name varchar(124)
declare bu_opr_cursor cursor for
select rtrim(DB_CODE)+'[_]'+rtrim(OPR_CODE)+'[0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][_]' from DB_DEFN, OPR
where DB_CODE <> ''
open bu_opr_cursor
fetch next from bu_opr_cursor into @bu_opr
while @@FETCH_STATUS >= 0
begin
if exists (select TOP 1 'OK' from sysobjects where name like @bu_opr +'%')
begin
declare tmp_table_cursor cursor for
select @bu_opr + RTRIM(FILTER_TYPE_CODE)+'%' from FILTER_TYPE
open tmp_table_cursor
fetch next from tmp_table_cursor into @table_name
while @@FETCH_STATUS >= 0
begin
declare table_cursor cursor for select name from sysobjects where name like @table_name
open table_cursor
fetch next from table_cursor into @del_table_name
while @@FETCH_STATUS >= 0
begin
print 'DROP TABLE ' + @del_table_name
exec ('DROP TABLE ' + @del_table_name)
fetch next from table_cursor into @del_table_name
end
close table_cursor
deallocate table_cursor

fetch next from tmp_table_cursor into @table_name
end
close tmp_table_cursor
deallocate tmp_table_cursor
end
fetch next from bu_opr_cursor into @bu_opr
end
close bu_opr_cursor
deallocate bu_opr_cursor
go

-- Remove old Recon Manager tables
declare @recon_opr varchar(30)
declare @table_name varchar(124)
declare @del_table_name varchar(124)
declare opr_cursor cursor for
select rtrim('RECON[_]MGR[_]'+rtrim(OPR_CODE)+'[_]') from OPR where OPR_CODE <> ''
open opr_cursor
fetch next from opr_cursor into @recon_opr
while @@FETCH_STATUS >= 0
begin
if exists (select TOP 1 'OK' from sysobjects where name like @recon_opr+
'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
begin
declare table_cursor cursor for select name from sysobjects where name like @recon_opr+
'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
open table_cursor
fetch next from table_cursor into @del_table_name
while @@FETCH_STATUS >= 0
begin
print 'DROP TABLE ' + @del_table_name
exec ('DROP TABLE ' + @del_table_name)
fetch next from table_cursor into @del_table_name
end
close table_cursor
deallocate table_cursor
end
fetch next from opr_cursor into @recon_opr
end
close opr_cursor
deallocate opr_cursor
go

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-09 : 06:43:30

Looks ok (well at leastshouldn't get that error.
Suspect it's a paste problem.
Try copying what you pasted back into query analyser.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources