Ok, I missed the boat somewhere.
I prepared the query below to go though all the columns and tables in
the database and return the count of distinct values for each column and
label them with the table and column names
When I run the query, I receive the following error from the part
labeled #1:
Server: Msg 137, Level 15, State 2, Line 44
Must declare the variable '@.tbl_name'.
What I dont understand is why this error is occurring. I defined the
variable and populated it. I commented out part #1 and tried PRINT
@.tbl_name @.col_name which returned appropriate values.
I have a workaround of commenting out the part labeled #1 and instead
insert the following which produced a list of queries that I copied and
pasted into a new QA window and executed.
PRINT 'select count(distinct ' + @.col_name + ') ' + '"' + @.tbl_name +
'.' + @.col_name + '"' + ' from ' + @.tbl_name
I dont understand why I cannot substitute the variables in a query as I
wish to.
I am also considering building a command string and using exec
sp_executesql.
I welcome comments and suggestions on this matter.
-- -- --
DECLARE @.tbl_name varchar(255), @.col_name varchar(255)
DECLARE CURS_sys_tables_and_cols CURSOR FOR
select sysobjects.name, syscolumns.name from syscolumns, sysobjects
where sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='S')
and sysobjects.name NOT like 'SYS%'
and sysobjects.name NOT IN ( LIST OF TABLES I DONT WANT)
order by sysobjects.name
OPEN CURS_sys_tables_and_cols
FETCH NEXT FROM CURS_sys_tables_and_cols
INTO @.tbl_name, @.col_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- #1
PRINT @.tbl_name + '.' + @.col_name
Select count(distinct @.col_name) from @.tbl_name
PRINT '--'
FETCH NEXT FROM CURS_sys_tables_and_cols
INTO @.tbl_name, @.col_name
END
CLOSE CURS_sys_tables_and_cols
DEALLOCATE CURS_sys_tables_and_cols
*** Sent via Developersdex http://www.examnotes.net ***SJM,
I think you'll need to use Dynamic SQL to use a variable for the table name
in your query i.e., EXEC or sp_executesql.
Check it out in the SQL BOL and at Erland's article:
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"SJM" <nospam@.devdex.com> wrote in message
news:eIAwXiRxFHA.624@.TK2MSFTNGP11.phx.gbl...
> Ok, I missed the boat somewhere.
> I prepared the query below to go though all the columns and tables in
> the database and return the count of distinct values for each column and
> label them with the table and column names
> When I run the query, I receive the following error from the part
> labeled #1:
> Server: Msg 137, Level 15, State 2, Line 44
> Must declare the variable '@.tbl_name'.
> What I don't understand is why this error is occurring. I defined the
> variable and populated it. I commented out part #1 and tried PRINT
> @.tbl_name @.col_name which returned appropriate values.
> I have a workaround of commenting out the part labeled #1 and instead
> insert the following which produced a list of queries that I copied and
> pasted into a new QA window and executed.
> PRINT 'select count(distinct ' + @.col_name + ') ' + '"' + @.tbl_name +
> '.' + @.col_name + '"' + ' from ' + @.tbl_name
> I don't understand why I cannot substitute the variables in a query as I
> wish to.
> I am also considering building a command string and using exec
> sp_executesql.
> I welcome comments and suggestions on this matter.
> -- -- --
> DECLARE @.tbl_name varchar(255), @.col_name varchar(255)
> DECLARE CURS_sys_tables_and_cols CURSOR FOR
> select sysobjects.name, syscolumns.name from syscolumns, sysobjects
> where sysobjects.id = syscolumns.id
> and (sysobjects.xtype='U' or sysobjects.xtype='S')
> and sysobjects.name NOT like 'SYS%'
> and sysobjects.name NOT IN ( LIST OF TABLES I DON'T WANT)
> order by sysobjects.name
> OPEN CURS_sys_tables_and_cols
> FETCH NEXT FROM CURS_sys_tables_and_cols
> INTO @.tbl_name, @.col_name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
>
> -- #1
> PRINT @.tbl_name + '.' + @.col_name
> Select count(distinct @.col_name) from @.tbl_name
> PRINT '--'
>
> FETCH NEXT FROM CURS_sys_tables_and_cols
> INTO @.tbl_name, @.col_name
> END
> CLOSE CURS_sys_tables_and_cols
> DEALLOCATE CURS_sys_tables_and_cols
>
> *** Sent via Developersdex http://www.examnotes.net ***|||
Indeed, I thought I might need to build strings and use sp_executesql.
Thanks for the pointer to the article, I missed it in my google
searches.
*** Sent via Developersdex http://www.examnotes.net ***
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Thursday, March 29, 2012
Cursor to loop through all tables in db
So far, I have avoided using cursors, but I see no other way to do this.
I have a db with 285 tables. I want to add 1 varchar(10) column to all 285
tables called 'year' and I always want year to be '1999'.
I am struggling with setting up the cursor though. This is what I have so
far:
declare table_loop cursor
For select name from sysobjects where xtype = 'u'
open table_loop
Fetch table_loop
alter table [name]
add db_year varchar(10)
close table_loop
deallocate table_loop
go
Any help would be greatly appreciated.
Thanks.
ArcherIt has to be something like
declare @.name varchar(')
declare table_loop cursor for select name from sysobjects where xtype = 'u'
open table_loop
fetch next from table_loop into @.Name
while @.@.FETCH_STATUS = 0
begin
-- this migth have to be some dynamic thing, like:
-- exec 'alter table ' + @.name + ' add db_year varchar(10)'
alter table [@.name]
add db_year varchar(10)
fetch next from table_loop into @.name
end
close table_loop
deallocate table_loop
go
Kr.
Soren
"bagman3rd" <bagman3rd@.discussions.microsoft.com> skrev i en meddelelse
news:24C3F48A-4961-4285-9FDF-F790961789C5@.microsoft.com...
> So far, I have avoided using cursors, but I see no other way to do this.
> I have a db with 285 tables. I want to add 1 varchar(10) column to all
> 285
> tables called 'year' and I always want year to be '1999'.
> I am struggling with setting up the cursor though. This is what I have so
> far:
> declare table_loop cursor
> For select name from sysobjects where xtype = 'u'
> open table_loop
> Fetch table_loop
> alter table [name]
> add db_year varchar(10)
> close table_loop
> deallocate table_loop
> go
> Any help would be greatly appreciated.
> Thanks.
> Archer|||HI
There is a straight forward method for this
google for undocumented sp_Msforeachtable command
Regards
R.D
"Soeren S. Joergensen" wrote:
> It has to be something like
> declare @.name varchar(')
> declare table_loop cursor for select name from sysobjects where xtype = 'u
'
> open table_loop
> fetch next from table_loop into @.Name
> while @.@.FETCH_STATUS = 0
> begin
> -- this migth have to be some dynamic thing, like:
> -- exec 'alter table ' + @.name + ' add db_year varchar(10)'
> alter table [@.name]
> add db_year varchar(10)
> fetch next from table_loop into @.name
> end
> close table_loop
> deallocate table_loop
> go
> Kr.
> Soren
> "bagman3rd" <bagman3rd@.discussions.microsoft.com> skrev i en meddelelse
> news:24C3F48A-4961-4285-9FDF-F790961789C5@.microsoft.com...
>
>|||Wouldn't you be better off with a DDL script that you can check into source
control and deploy to test and live environments? Try this:
SELECT
'ALTER TABLE '+QUOTENAME(table_schema)+'.'+QUOTENAME(table_name)+
' ADD [year_num] VARCHAR(10) NOT NULL'+
' CONSTRAINT [df_year_num] DEFAULT (''1999'')'
FROM information_schema.tables ;
Output as text and you have your script.
Note that "YEAR" is a reserved word so not a good choice for a column name.
Why make the year number a VARCHAR anyway?
David Portas
SQL Server MVP
--|||CORRECTION:
SELECT
'ALTER TABLE '+QUOTENAME(table_schema)+'.'+QUOTENAME(table_name)+
' ADD [year_num] VARCHAR(10) NOT NULL'+
' CONSTRAINT [df_'+table_name+'_year_num] DEFAULT (''1999'')'
FROM information_schema.tables ;
David Portas
SQL Server MVP
--sql
I have a db with 285 tables. I want to add 1 varchar(10) column to all 285
tables called 'year' and I always want year to be '1999'.
I am struggling with setting up the cursor though. This is what I have so
far:
declare table_loop cursor
For select name from sysobjects where xtype = 'u'
open table_loop
Fetch table_loop
alter table [name]
add db_year varchar(10)
close table_loop
deallocate table_loop
go
Any help would be greatly appreciated.
Thanks.
ArcherIt has to be something like
declare @.name varchar(')
declare table_loop cursor for select name from sysobjects where xtype = 'u'
open table_loop
fetch next from table_loop into @.Name
while @.@.FETCH_STATUS = 0
begin
-- this migth have to be some dynamic thing, like:
-- exec 'alter table ' + @.name + ' add db_year varchar(10)'
alter table [@.name]
add db_year varchar(10)
fetch next from table_loop into @.name
end
close table_loop
deallocate table_loop
go
Kr.
Soren
"bagman3rd" <bagman3rd@.discussions.microsoft.com> skrev i en meddelelse
news:24C3F48A-4961-4285-9FDF-F790961789C5@.microsoft.com...
> So far, I have avoided using cursors, but I see no other way to do this.
> I have a db with 285 tables. I want to add 1 varchar(10) column to all
> 285
> tables called 'year' and I always want year to be '1999'.
> I am struggling with setting up the cursor though. This is what I have so
> far:
> declare table_loop cursor
> For select name from sysobjects where xtype = 'u'
> open table_loop
> Fetch table_loop
> alter table [name]
> add db_year varchar(10)
> close table_loop
> deallocate table_loop
> go
> Any help would be greatly appreciated.
> Thanks.
> Archer|||HI
There is a straight forward method for this
google for undocumented sp_Msforeachtable command
Regards
R.D
"Soeren S. Joergensen" wrote:
> It has to be something like
> declare @.name varchar(')
> declare table_loop cursor for select name from sysobjects where xtype = 'u
'
> open table_loop
> fetch next from table_loop into @.Name
> while @.@.FETCH_STATUS = 0
> begin
> -- this migth have to be some dynamic thing, like:
> -- exec 'alter table ' + @.name + ' add db_year varchar(10)'
> alter table [@.name]
> add db_year varchar(10)
> fetch next from table_loop into @.name
> end
> close table_loop
> deallocate table_loop
> go
> Kr.
> Soren
> "bagman3rd" <bagman3rd@.discussions.microsoft.com> skrev i en meddelelse
> news:24C3F48A-4961-4285-9FDF-F790961789C5@.microsoft.com...
>
>|||Wouldn't you be better off with a DDL script that you can check into source
control and deploy to test and live environments? Try this:
SELECT
'ALTER TABLE '+QUOTENAME(table_schema)+'.'+QUOTENAME(table_name)+
' ADD [year_num] VARCHAR(10) NOT NULL'+
' CONSTRAINT [df_year_num] DEFAULT (''1999'')'
FROM information_schema.tables ;
Output as text and you have your script.
Note that "YEAR" is a reserved word so not a good choice for a column name.
Why make the year number a VARCHAR anyway?
David Portas
SQL Server MVP
--|||CORRECTION:
SELECT
'ALTER TABLE '+QUOTENAME(table_schema)+'.'+QUOTENAME(table_name)+
' ADD [year_num] VARCHAR(10) NOT NULL'+
' CONSTRAINT [df_'+table_name+'_year_num] DEFAULT (''1999'')'
FROM information_schema.tables ;
David Portas
SQL Server MVP
--sql
CURSOR to delete from multiple table
I'm trying to declare a cursor that gets a list of tables in a databse and
then deletes all the data from them. My problem is that SQL will not
recognize
the variable as a table name. Is there another way to do this or perhaps
some different syntax use?
Here are my statements
declare @.table_name nvarchar(384)
DECLARE DEL_WO_DATA CURSOR FOR
select [name] As table_name from sysobjects where [name] like 'work_%'
OPEN DEL_WO_DATA
FETCH NEXT FROM DEL_WO_DATA
INTO @.table_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
delete from [@.table_name] /* <--Does not recognize the @.table_name
variable at this point
Receive this error while debugging:
Server: Msg 208, Level 16, State 1, Procedure TEST, Line 33
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'@.table_Name'. */
FETCH NEXT FROM DEL_WO_DATA
INTO @.table_name
end
close DEL_WO_DATA
deallocate DEL_WO_DATAchange delete from [@.table_name] /* <--Does not recognize the
table_name variable at this point
to
exec('delete from ' + @.table_name)
"Troy Jerkins" <tjerkins@.alltel.net> wrote in message
news:ei%23SpdQRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I'm trying to declare a cursor that gets a list of tables in a databse and
> then deletes all the data from them. My problem is that SQL will not
> recognize
> the variable as a table name. Is there another way to do this or perhaps
> some different syntax use?
> Here are my statements
> declare @.table_name nvarchar(384)
> DECLARE DEL_WO_DATA CURSOR FOR
> select [name] As table_name from sysobjects where [name] like 'work_%'
>
> OPEN DEL_WO_DATA
> FETCH NEXT FROM DEL_WO_DATA
> INTO @.table_name
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> delete from [@.table_name] /* <--Does not recognize the @.table_name
> variable at this point
> Receive this error while debugging:
> Server: Msg 208, Level 16, State 1, Procedure TEST, Line 33
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> '@.table_Name'. */
> FETCH NEXT FROM DEL_WO_DATA
> INTO @.table_name
> end
> close DEL_WO_DATA
> deallocate DEL_WO_DATA
>|||EXEC('delete from ' + @.table_name)
HH, Jens Suessmeyer.
http://sqlserver2005.de
--
"Troy Jerkins" <tjerkins@.alltel.net> schrieb im Newsbeitrag
news:ei%23SpdQRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I'm trying to declare a cursor that gets a list of tables in a databse and
> then deletes all the data from them. My problem is that SQL will not
> recognize
> the variable as a table name. Is there another way to do this or perhaps
> some different syntax use?
> Here are my statements
> declare @.table_name nvarchar(384)
> DECLARE DEL_WO_DATA CURSOR FOR
> select [name] As table_name from sysobjects where [name] like 'work_%'
>
> OPEN DEL_WO_DATA
> FETCH NEXT FROM DEL_WO_DATA
> INTO @.table_name
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> delete from [@.table_name] /* <--Does not recognize the @.table_name
> variable at this point
> Receive this error while debugging:
> Server: Msg 208, Level 16, State 1, Procedure TEST, Line 33
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> '@.table_Name'. */
> FETCH NEXT FROM DEL_WO_DATA
> INTO @.table_name
> end
> close DEL_WO_DATA
> deallocate DEL_WO_DATA
>|||Try the script below. However this will most likely fail if you have
foreign key constraints declared on tables. If this is just a one-off
then you could also try:
/* Are you SURE' EXEC sp_msforeachtable 'DELETE ?' */
Again, you may have to execute it more than once if it fails on FK
constraints. Don't use this in persistent code as it's undocumented.
You may alternatively find it more efficient just to drop the database
and then re-create it from a script.
/* DELETE FROM every table !! */
DECLARE @.TableName SYSNAME
DECLARE TableList INSENSITIVE CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE
table_type='BASE TABLE'
OPEN TableList
FETCH TableList INTO @.TableName
WHILE @.@.fetch_status=0
BEGIN
PRINT @.TableName
/* Are you SURE' EXEC ('DELETE FROM ['+@.TableName+']') */
FETCH TableList INTO @.TableName
END
CLOSE TableList
DEALLOCATE TableList
David Portas
SQL Server MVP
--|||That worked! Many thanks... to both of you.
-Troy
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uEbLVlQRFHA.2664@.TK2MSFTNGP15.phx.gbl...
> EXEC('delete from ' + @.table_name)
> HH, Jens Suessmeyer.
> --
> http://sqlserver2005.de
> --
> "Troy Jerkins" <tjerkins@.alltel.net> schrieb im Newsbeitrag
> news:ei%23SpdQRFHA.1172@.TK2MSFTNGP12.phx.gbl...
>
then deletes all the data from them. My problem is that SQL will not
recognize
the variable as a table name. Is there another way to do this or perhaps
some different syntax use?
Here are my statements
declare @.table_name nvarchar(384)
DECLARE DEL_WO_DATA CURSOR FOR
select [name] As table_name from sysobjects where [name] like 'work_%'
OPEN DEL_WO_DATA
FETCH NEXT FROM DEL_WO_DATA
INTO @.table_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
delete from [@.table_name] /* <--Does not recognize the @.table_name
variable at this point
Receive this error while debugging:
Server: Msg 208, Level 16, State 1, Procedure TEST, Line 33
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'@.table_Name'. */
FETCH NEXT FROM DEL_WO_DATA
INTO @.table_name
end
close DEL_WO_DATA
deallocate DEL_WO_DATAchange delete from [@.table_name] /* <--Does not recognize the
table_name variable at this point
to
exec('delete from ' + @.table_name)
"Troy Jerkins" <tjerkins@.alltel.net> wrote in message
news:ei%23SpdQRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I'm trying to declare a cursor that gets a list of tables in a databse and
> then deletes all the data from them. My problem is that SQL will not
> recognize
> the variable as a table name. Is there another way to do this or perhaps
> some different syntax use?
> Here are my statements
> declare @.table_name nvarchar(384)
> DECLARE DEL_WO_DATA CURSOR FOR
> select [name] As table_name from sysobjects where [name] like 'work_%'
>
> OPEN DEL_WO_DATA
> FETCH NEXT FROM DEL_WO_DATA
> INTO @.table_name
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> delete from [@.table_name] /* <--Does not recognize the @.table_name
> variable at this point
> Receive this error while debugging:
> Server: Msg 208, Level 16, State 1, Procedure TEST, Line 33
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> '@.table_Name'. */
> FETCH NEXT FROM DEL_WO_DATA
> INTO @.table_name
> end
> close DEL_WO_DATA
> deallocate DEL_WO_DATA
>|||EXEC('delete from ' + @.table_name)
HH, Jens Suessmeyer.
http://sqlserver2005.de
--
"Troy Jerkins" <tjerkins@.alltel.net> schrieb im Newsbeitrag
news:ei%23SpdQRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I'm trying to declare a cursor that gets a list of tables in a databse and
> then deletes all the data from them. My problem is that SQL will not
> recognize
> the variable as a table name. Is there another way to do this or perhaps
> some different syntax use?
> Here are my statements
> declare @.table_name nvarchar(384)
> DECLARE DEL_WO_DATA CURSOR FOR
> select [name] As table_name from sysobjects where [name] like 'work_%'
>
> OPEN DEL_WO_DATA
> FETCH NEXT FROM DEL_WO_DATA
> INTO @.table_name
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> delete from [@.table_name] /* <--Does not recognize the @.table_name
> variable at this point
> Receive this error while debugging:
> Server: Msg 208, Level 16, State 1, Procedure TEST, Line 33
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> '@.table_Name'. */
> FETCH NEXT FROM DEL_WO_DATA
> INTO @.table_name
> end
> close DEL_WO_DATA
> deallocate DEL_WO_DATA
>|||Try the script below. However this will most likely fail if you have
foreign key constraints declared on tables. If this is just a one-off
then you could also try:
/* Are you SURE' EXEC sp_msforeachtable 'DELETE ?' */
Again, you may have to execute it more than once if it fails on FK
constraints. Don't use this in persistent code as it's undocumented.
You may alternatively find it more efficient just to drop the database
and then re-create it from a script.
/* DELETE FROM every table !! */
DECLARE @.TableName SYSNAME
DECLARE TableList INSENSITIVE CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE
table_type='BASE TABLE'
OPEN TableList
FETCH TableList INTO @.TableName
WHILE @.@.fetch_status=0
BEGIN
PRINT @.TableName
/* Are you SURE' EXEC ('DELETE FROM ['+@.TableName+']') */
FETCH TableList INTO @.TableName
END
CLOSE TableList
DEALLOCATE TableList
David Portas
SQL Server MVP
--|||That worked! Many thanks... to both of you.
-Troy
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uEbLVlQRFHA.2664@.TK2MSFTNGP15.phx.gbl...
> EXEC('delete from ' + @.table_name)
> HH, Jens Suessmeyer.
> --
> http://sqlserver2005.de
> --
> "Troy Jerkins" <tjerkins@.alltel.net> schrieb im Newsbeitrag
> news:ei%23SpdQRFHA.1172@.TK2MSFTNGP12.phx.gbl...
>
cursor to compare/report on the same fields in 2 tables
I have the following cursor that I am comparing 2 tables, the
production table and a copy of the production table, I want results of
all address's that are like the address1 field...the problem is...my
results are giving me every field and if there is more than one, it is
putting it in a grid...
[snip]
If it's a copy, isn't it the same data then?
See if this points you in the right direction:
production table and a copy of the production table, I want results of
all address's that are like the address1 field...the problem is...my
results are giving me every field and if there is more than one, it is
putting it in a grid...
I only want to see results if they are 1 for the same address field
this is what I have so far...
declare @.address1 char(61),@.city char(61)
declare address_cursor CURSOR FOR
SELECT address1,city FROM test.dbo.testadd
OPEN address_cursor
fetch next from address_cursor into @.address1,@.city
while @.@.fetch_status = 0
BEGIN
select * from testadd where @.address1 like '%' + address1 + '%' and
@.city = city
Fetch next from address_cursor into @.address1,@.city
Print
END
CLOSE address_cursor
DEallocate address_cursorhttp://sqlserver-puzzles.blogspot.c...albes-real.html
--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||SQLNewbie wrote:
Quote:
Originally Posted by
I have the following cursor that I am comparing 2 tables, the
production table and a copy of the production table, I want results of
all address's that are like the address1 field...the problem is...my
results are giving me every field and if there is more than one, it is
putting it in a grid...
>
I only want to see results if they are 1 for the same address field
>
this is what I have so far...
>
[snip]
You refer to yourself as a "newbie". If you are new to SQL or to SQL
Server then do not even attempt to write cursors.
There are nearly always better alternatives to cursors. It's only when
you have a lot of experience that you can make an informed decision
about when a cursor makes sense. Meantime, if you can't think of
another way to do something it would be better to ask for help rather
than try to write a cursor. You'll learn good practices a LOT quicker
that way.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||SQLNewbie (tahnee.puckett@.bancsourceinc.com) writes:
Quote:
Originally Posted by
I have the following cursor that I am comparing 2 tables, the
production table and a copy of the production table, I want results of
all address's that are like the address1 field...the problem is...my
results are giving me every field and if there is more than one, it is
putting it in a grid...
>
I only want to see results if they are 1 for the same address field
If it's a copy, isn't it the same data then?
I was trying to understand what you really want to do, but I'm afraid I
don't.
I would suggest that you post:
o CREATE TABLE statements for your tables, preferably to show the
pertinent points.
o INSERT statements with sample data.
o The desired output given the sample.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||SQLNewbie wrote:
Quote:
Originally Posted by
I have the following cursor that I am comparing 2 tables, the
production table and a copy of the production table, I want results of
all address's that are like the address1 field...the problem is...my
results are giving me every field and if there is more than one, it is
putting it in a grid...
>
I only want to see results if they are 1 for the same address field
>
this is what I have so far...
>
>
declare @.address1 char(61),@.city char(61)
>
declare address_cursor CURSOR FOR
SELECT address1,city FROM test.dbo.testadd
>
OPEN address_cursor
>
fetch next from address_cursor into @.address1,@.city
while @.@.fetch_status = 0
BEGIN
select * from testadd where @.address1 like '%' + address1 + '%' and
@.city = city
Fetch next from address_cursor into @.address1,@.city
Print
END
CLOSE address_cursor
DEallocate address_cursor
See if this points you in the right direction:
select t.address1, t.city, count(*)
from test.dbo.testadd t
join production.dbo.testadd p on t.city = p.city
where p.address1 like '%' + t.address1 + '%'
group by t.address1, t.city
having count(*) 1
Tuesday, March 27, 2012
Cursor out of time
Hi,
First of all sorry about poor english.
I have the next problem:
I have a store procedure which declares a cursor which is a select from some tables. Im surprised because the sp takes over 10 seconds on working and if i execute the select of the cursor as separate ways, it takes no more one second. Ive changed the select with another easier select and it works 0 seconds, so it seems that the problem is in the select, but only if i use a cursor to save the regs, why????
Thanks a lot.Use the "show execution plan" option to see how the server is executing the three queries. Check to see what it different between them. My guess is that there is something causing the optimizer to make a radically different choice for one or more of the queries, and that is what is making them take longer.
-PatP|||Just don't use cursors. :) What are you trying to do with the cursor?|||Thank you for answer.
I think that I meant wrong.
If I execute the select in the cursor it takes me over ten seconds but if I copy the select and paste in the query analyzer, it takes me 0 seconds. Besides this, if i change the select by other easier one, the cursor takes 0 seconds. I know the first select could be wrong but it works 0 seconds in the analyzer!!!! and inside the cursor there is not any operations.
Thanks a lot...
First of all sorry about poor english.
I have the next problem:
I have a store procedure which declares a cursor which is a select from some tables. Im surprised because the sp takes over 10 seconds on working and if i execute the select of the cursor as separate ways, it takes no more one second. Ive changed the select with another easier select and it works 0 seconds, so it seems that the problem is in the select, but only if i use a cursor to save the regs, why????
Thanks a lot.Use the "show execution plan" option to see how the server is executing the three queries. Check to see what it different between them. My guess is that there is something causing the optimizer to make a radically different choice for one or more of the queries, and that is what is making them take longer.
-PatP|||Just don't use cursors. :) What are you trying to do with the cursor?|||Thank you for answer.
I think that I meant wrong.
If I execute the select in the cursor it takes me over ten seconds but if I copy the select and paste in the query analyzer, it takes me 0 seconds. Besides this, if i change the select by other easier one, the cursor takes 0 seconds. I know the first select could be wrong but it works 0 seconds in the analyzer!!!! and inside the cursor there is not any operations.
Thanks a lot...
Cursor or not?
Here is the scenario , I need to pull data from several tables and there wil
l
also be a sub query in one of the joins that does summing of an amount
column, then I need to perform 3 separate Inserts into diff tables with the
data from this query.
I was going to use a cursor and then perform the Inserts as one Transaction
for each row in the set. Is there a better approach or is this example a goo
d
candidate for cursor usage?Sounds like you'll just need three INSERT statements. Based on what
you've said I don't think you should use a cursor. To give you a better
solution it would help if you could post DDL and sample data.
David Portas
SQL Server MVP
--|||Info I forgot to add. I need the inserts to perform as a single transaction
with no client application involved, this proc will be run as a scheduled SQ
L
Job.
After the the three inserts are complete then an update is done to flag
column in a source table indicating the record has been processed.|||It sounds like you can accomplish this inserting the results of a group by
query: For example:
insert into MyTable select ... group by ...
"Chris Lane" <ChrisLane@.discussions.microsoft.com> wrote in message
news:996D0192-162F-40D1-814C-FEC2410EF5AD@.microsoft.com...
> Here is the scenario , I need to pull data from several tables and there
> will
> also be a sub query in one of the joins that does summing of an amount
> column, then I need to perform 3 separate Inserts into diff tables with
> the
> data from this query.
> I was going to use a cursor and then perform the Inserts as one
> Transaction
> for each row in the set. Is there a better approach or is this example a
> good
> candidate for cursor usage?
>|||also the update is part of the same transaction.|||So it sounds like you'll need three INSERT statements and an UPDATE...
David Portas
SQL Server MVP
--|||Yeah I think, I was wrong to use a cursor, I would be better of using a loca
l
temp table with the aggregated values I need then perform the 3 Inserts and
the Update as a single logical unit in a transaction.
The reason for the temp table is I need to ensure that the 3 inserts insert
an equal number of related inserts, if you get what I mean.
Thanks|||>> After the three inserts are complete then an update is done to flag column in
a source table indicating the record [sic] has been processed. <<
Rows are not records, and we don't use flags in an RDBMS. Flags are
for older file systems where we did record-at-a-time processing. You
will probably use temp tables the way we used scratch files in the
1970's. Getting rid of the cursor does not get rid of the sequential
processing mindset or design.|||What would you suggest as an alternative, give me an example of your own.
"--CELKO--" wrote:
> Rows are not records, and we don't use flags in an RDBMS. Flags are
> for older file systems where we did record-at-a-time processing. You
> will probably use temp tables the way we used scratch files in the
> 1970's. Getting rid of the cursor does not get rid of the sequential
> processing mindset or design.
>|||Cursors are almost never necessary, and even when you must use one, you
should never perform DML operations against normal tables within the fetch
loop. You should instead cache the values in a temporary table or table
variable and commit the changes using set-based operations. Flushing the
changes using a set-based operation causes triggers to fire only once, less
information is written to the transaction log, and SQL Server can optimize
the update of any indexes. I think that this cache-and-flush pattern can
also be used to solve your problem, although I don't think a cursor is
indicated in this case.
What you can do is cache the results of the subquery in a table variable and
then issue three separate set-based insert statements to flush the results.
There's something you should be aware of, however. In a concurrent
environment, it is possible for the data that was used to calculate the
subquery results to change before the final insert is executed, which,
depending on the data model, may introduce incorrect information into the
database. Note that this problem exists regardless of whether you cache the
information or not. At least by cacheing the information, you can be sure
that the data inserted into the three tables will reflect the same subquery
results.
There are two ways to deal with this problem. You can either prevent
changes until the transaction completes, or you can detect changes and if
necessary, rollback and restart. The first solution is to use pessimistic
concurrency--that is, the calculation of the subquery results occurs within
the transaction with a serializable isolation level (HOLDLOCK)--which
prevents changes to any of the data used to calculate the results until the
transaction is committed. The other solution uses optimistic concurrency.
The calculation of the subquery results occurs before the transaction
starts, and then within the transaction the source data is checked for
changes and locked for the duration of the transaction which is rolled back
if any changes are detected. Optimistic concurrency works best if you have
a rowversion (timestamp) column on each table, because you can save the
maximum rowversions from each source table for each row along with the
subquery results while you're calculating those results, and then after
initiating the transaction, lock the source rows and verify that the maximum
rowversions from each source table haven't changed before issuing the DML
statements.
You should ignore Joe Celko's comments. It appears that he finds so much
delight in bashing posters for using terms like "record" or "flag" or, God
forbid, using IDENTITY, that he doesn't take the time to understand what is
being asked, and thus issues poor, irrelevant and often incorrect advice.
As indicated above, there are very good reasons to cache results during a
long-running transaction (as used to be done in a scratch file).
"Chris Lane" <ChrisLane@.discussions.microsoft.com> wrote in message
news:996D0192-162F-40D1-814C-FEC2410EF5AD@.microsoft.com...
> Here is the scenario , I need to pull data from several tables and there
will
> also be a sub query in one of the joins that does summing of an amount
> column, then I need to perform 3 separate Inserts into diff tables with
the
> data from this query.
> I was going to use a cursor and then perform the Inserts as one
Transaction
> for each row in the set. Is there a better approach or is this example a
good
> candidate for cursor usage?
>
l
also be a sub query in one of the joins that does summing of an amount
column, then I need to perform 3 separate Inserts into diff tables with the
data from this query.
I was going to use a cursor and then perform the Inserts as one Transaction
for each row in the set. Is there a better approach or is this example a goo
d
candidate for cursor usage?Sounds like you'll just need three INSERT statements. Based on what
you've said I don't think you should use a cursor. To give you a better
solution it would help if you could post DDL and sample data.
David Portas
SQL Server MVP
--|||Info I forgot to add. I need the inserts to perform as a single transaction
with no client application involved, this proc will be run as a scheduled SQ
L
Job.
After the the three inserts are complete then an update is done to flag
column in a source table indicating the record has been processed.|||It sounds like you can accomplish this inserting the results of a group by
query: For example:
insert into MyTable select ... group by ...
"Chris Lane" <ChrisLane@.discussions.microsoft.com> wrote in message
news:996D0192-162F-40D1-814C-FEC2410EF5AD@.microsoft.com...
> Here is the scenario , I need to pull data from several tables and there
> will
> also be a sub query in one of the joins that does summing of an amount
> column, then I need to perform 3 separate Inserts into diff tables with
> the
> data from this query.
> I was going to use a cursor and then perform the Inserts as one
> Transaction
> for each row in the set. Is there a better approach or is this example a
> good
> candidate for cursor usage?
>|||also the update is part of the same transaction.|||So it sounds like you'll need three INSERT statements and an UPDATE...
David Portas
SQL Server MVP
--|||Yeah I think, I was wrong to use a cursor, I would be better of using a loca
l
temp table with the aggregated values I need then perform the 3 Inserts and
the Update as a single logical unit in a transaction.
The reason for the temp table is I need to ensure that the 3 inserts insert
an equal number of related inserts, if you get what I mean.
Thanks|||>> After the three inserts are complete then an update is done to flag column in
a source table indicating the record [sic] has been processed. <<
Rows are not records, and we don't use flags in an RDBMS. Flags are
for older file systems where we did record-at-a-time processing. You
will probably use temp tables the way we used scratch files in the
1970's. Getting rid of the cursor does not get rid of the sequential
processing mindset or design.|||What would you suggest as an alternative, give me an example of your own.
"--CELKO--" wrote:
> Rows are not records, and we don't use flags in an RDBMS. Flags are
> for older file systems where we did record-at-a-time processing. You
> will probably use temp tables the way we used scratch files in the
> 1970's. Getting rid of the cursor does not get rid of the sequential
> processing mindset or design.
>|||Cursors are almost never necessary, and even when you must use one, you
should never perform DML operations against normal tables within the fetch
loop. You should instead cache the values in a temporary table or table
variable and commit the changes using set-based operations. Flushing the
changes using a set-based operation causes triggers to fire only once, less
information is written to the transaction log, and SQL Server can optimize
the update of any indexes. I think that this cache-and-flush pattern can
also be used to solve your problem, although I don't think a cursor is
indicated in this case.
What you can do is cache the results of the subquery in a table variable and
then issue three separate set-based insert statements to flush the results.
There's something you should be aware of, however. In a concurrent
environment, it is possible for the data that was used to calculate the
subquery results to change before the final insert is executed, which,
depending on the data model, may introduce incorrect information into the
database. Note that this problem exists regardless of whether you cache the
information or not. At least by cacheing the information, you can be sure
that the data inserted into the three tables will reflect the same subquery
results.
There are two ways to deal with this problem. You can either prevent
changes until the transaction completes, or you can detect changes and if
necessary, rollback and restart. The first solution is to use pessimistic
concurrency--that is, the calculation of the subquery results occurs within
the transaction with a serializable isolation level (HOLDLOCK)--which
prevents changes to any of the data used to calculate the results until the
transaction is committed. The other solution uses optimistic concurrency.
The calculation of the subquery results occurs before the transaction
starts, and then within the transaction the source data is checked for
changes and locked for the duration of the transaction which is rolled back
if any changes are detected. Optimistic concurrency works best if you have
a rowversion (timestamp) column on each table, because you can save the
maximum rowversions from each source table for each row along with the
subquery results while you're calculating those results, and then after
initiating the transaction, lock the source rows and verify that the maximum
rowversions from each source table haven't changed before issuing the DML
statements.
You should ignore Joe Celko's comments. It appears that he finds so much
delight in bashing posters for using terms like "record" or "flag" or, God
forbid, using IDENTITY, that he doesn't take the time to understand what is
being asked, and thus issues poor, irrelevant and often incorrect advice.
As indicated above, there are very good reasons to cache results during a
long-running transaction (as used to be done in a scratch file).
"Chris Lane" <ChrisLane@.discussions.microsoft.com> wrote in message
news:996D0192-162F-40D1-814C-FEC2410EF5AD@.microsoft.com...
> Here is the scenario , I need to pull data from several tables and there
will
> also be a sub query in one of the joins that does summing of an amount
> column, then I need to perform 3 separate Inserts into diff tables with
the
> data from this query.
> I was going to use a cursor and then perform the Inserts as one
Transaction
> for each row in the set. Is there a better approach or is this example a
good
> candidate for cursor usage?
>
Cursor Loop
Hi, I wonder can someone help me. I have a cursor that will drop a number of
w
ly tables if a monthly table is being generated. I have to keep the
previous ws data but can drop the remaining tables. I have tried it as
below but it will only drop the wk214 table as calculated and does not carry
on to drop wk213,wk212 etc.
Does anybody know how I can acheive this?
Begin
DECLARE lcurTables_To_Be_Deleted CURSOR FOR
SELECT substring(NAME,28,3) FROM SNAPS_ARC..SYSOBJECTS(nolock) WHERE NAME
like @.vchArchiveTable_To_Delete and xtype = 'U' order by crdate desc
OPEN lcurTables_To_Be_Deleted
FETCH lcurTables_To_Be_Deleted into @.TabNoToDrop
SELECT @.TableNumToDrop = convert(int, @.TabNoToDrop) -- wk215
PRINT '1'
PRINT @.TableNumToDrop
SELECT @.TableNumToDrop = + convert(varchar,(@.TableNumToDrop - 1))
PRINT '2' -- wk214
PRINT @.TableNumToDrop
while @.@.fetch_status = 0
begin
EXEC('DROP TABLE SNAPS_ARC..' + @.vchArchiveTable_To_Delete1 + '_'
+ @.TableNumToDrop)
PRINT '_______________________________________
_________'
PRINT @.vchArchiveTable_To_Delete1 + '_' + @.TableNumToDrop + '
Dropped.' PRINT ''
FETCH lcurTables_To_Be_Deleted into @.TableToDrop
end
close lcurTables_To_Be_Deleted
deallocate lcurTables_To_Be_Deleted
EndMake sure you declare the cursor insensitive. Also, did you initialize all
your variables?
Seriously though, why would you want to prolong the life of such a poor
design by developing more code like this? This is so abysmal it breaks just
about every rule in the book: cursors; dynamic SQL; use of system tables;
modifying metadata. Maybe you aren't concerned, but this sort of thing
shouldn't get past even the most basic audit or compliance review.
How about:
DELETE FROM SomeTable
WHERE the_date < @.some_date
Simpler, no?
Take care.
David Portas
SQL Server MVP
--|||I have had this debate before David.
My new model design is in the pipeline with management.
Until then I am stuck with this.
The issue I have is why when the part that calculates the w number is run
it goes into the loop. But it only goes in once. Have I missed something
blatent in the code that prevents it from going into the loop for the other
tables returned from the cursor definition
"David Portas" wrote:
> Make sure you declare the cursor insensitive. Also, did you initialize all
> your variables?
> Seriously though, why would you want to prolong the life of such a poor
> design by developing more code like this? This is so abysmal it breaks jus
t
> about every rule in the book: cursors; dynamic SQL; use of system tables;
> modifying metadata. Maybe you aren't concerned, but this sort of thing
> shouldn't get past even the most basic audit or compliance review.
> How about :
> DELETE FROM SomeTable
> WHERE the_date < @.some_date
> Simpler, no?
> Take care.
> --
> David Portas
> SQL Server MVP
> --
>|||> My new model design is in the pipeline with management.
Good news :-)
I haven't tested it out but I suspect the cursor terminates because dropping
the table deletes the current row. That's why I suggested you declare the
cursor INSENSITIVE. Alternatively, select the table names into a table
variable and iterate through that.
Again, note that you really should avoid system tables where you can. For
this I would prefer to use INFORMATION_SCHEMA.TABLES.
Hope this helps.
David Portas
SQL Server MVP
--
"marcmc" wrote:
> I have had this debate before David.
> My new model design is in the pipeline with management.
> Until then I am stuck with this.
> The issue I have is why when the part that calculates the w number is r
un
> it goes into the loop. But it only goes in once. Have I missed something
> blatent in the code that prevents it from going into the loop for the othe
r
> tables returned from the cursor definition
>
> "David Portas" wrote:
>|||I execute ...
exec uspDB_Maint_Archive_Snaps 'FAT_SNAP_PO_RISK_DETAIL_mth', 216, 'DbName'
Then I declare cursor...
Begin
declare lcurTables_To_Be_Deleted cursor for
select substring(NAME,28,3) from SNAPS_ARC..SYSOBJECTS(nolock) where name
like @.vchArchiveTable_To_Delete and xtype = 'U' order by crdate desc
open lcurTables_To_Be_Deleted
fetch lcurTables_To_Be_Deleted into @.TabNoToDrop
This brings the following into the cursor
--
215
214
213
212
212
I want to keep 215 but delete the rest
I want the next few lines of to do this. But I cant seem to find a way.
Please help...
SELECT @.TableNumToDrop = convert(int, @.TabNoToDrop) -- 215
SELECT @.TableNumToDrop = + convert(varchar,(@.TableNumToDrop - 1))
SELECT @.TableToDrop = @.vchArchiveTable_To_Delete1 + '_' + @.TableNumToDrop
while @.@.fetch_status = 0
begin
PRINT @.vchArchiveTable_To_Delete1 + '_' + @.TabNoToDrop
EXEC('DROP TABLE SNAPS_ARC..' + @.vchArchiveTable_To_Delete1 + '_' +
@.TabNoToDrop)
PRINT '_______________________________________
_________'
PRINT @.vchArchiveTable_To_Delete1 + '_' + @.TabNoToDrop + ' Dropped.'
PRINT ''
FETCH lcurTables_To_Be_Deleted into @.TabNoToDrop
end
close lcurTables_To_Be_Deleted
deallocate lcurTab|||On Mon, 9 May 2005 02:54:01 -0700, marcmc wrote:
>Hi, I wonder can someone help me.
(snip)
Hi marcmc,
Since you are already aware of the downsides of this design, and
battling management to get it changed, I won't comment on that...
In your code, you use three almost but not quite equally named
variables: @.TabNoToDrop, @.TableNumToDrop and @.TableToDrop. The first and
third seem to server the same purpose (the first is used in the first
FETCH and the third in the FETCH in the WHILE loop). The second includes
only a part of the table name and is calculated from the first after the
first FETCH. Since it is not recalculated after the FETCH in the WHILE
loop, it's value never changes, and you are just attempting to drop the
same table over and over again.
>Begin
> DECLARE lcurTables_To_Be_Deleted CURSOR FOR
> SELECT substring(NAME,28,3) FROM SNAPS_ARC..SYSOBJECTS(nolock) WHERE NAME
>like @.vchArchiveTable_To_Delete and xtype = 'U' order by crdate desc
> OPEN lcurTables_To_Be_Deleted
> FETCH lcurTables_To_Be_Deleted into @.TabNoToDrop
> SELECT @.TableNumToDrop = convert(int, @.TabNoToDrop) -- wk215
> PRINT '1'
> PRINT @.TableNumToDrop
> SELECT @.TableNumToDrop = + convert(varchar,(@.TableNumToDrop - 1))
> PRINT '2' -- wk214
> PRINT @.TableNumToDrop
> while @.@.fetch_status = 0
> begin
> EXEC('DROP TABLE SNAPS_ARC..' + @.vchArchiveTable_To_Delete1 + '_'
>+ @.TableNumToDrop)
> PRINT '_______________________________________
_________'
> PRINT @.vchArchiveTable_To_Delete1 + '_' + @.TableNumToDrop + '
>Dropped.' PRINT ''
> FETCH lcurTables_To_Be_Deleted into @.TableToDrop
> end
> close lcurTables_To_Be_Deleted
> deallocate lcurTables_To_Be_Deleted
>End
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
w
ly tables if a monthly table is being generated. I have to keep theprevious w
s data but can drop the remaining tables. I have tried it asbelow but it will only drop the wk214 table as calculated and does not carry
on to drop wk213,wk212 etc.
Does anybody know how I can acheive this?
Begin
DECLARE lcurTables_To_Be_Deleted CURSOR FOR
SELECT substring(NAME,28,3) FROM SNAPS_ARC..SYSOBJECTS(nolock) WHERE NAME
like @.vchArchiveTable_To_Delete and xtype = 'U' order by crdate desc
OPEN lcurTables_To_Be_Deleted
FETCH lcurTables_To_Be_Deleted into @.TabNoToDrop
SELECT @.TableNumToDrop = convert(int, @.TabNoToDrop) -- wk215
PRINT '1'
PRINT @.TableNumToDrop
SELECT @.TableNumToDrop = + convert(varchar,(@.TableNumToDrop - 1))
PRINT '2' -- wk214
PRINT @.TableNumToDrop
while @.@.fetch_status = 0
begin
EXEC('DROP TABLE SNAPS_ARC..' + @.vchArchiveTable_To_Delete1 + '_'
+ @.TableNumToDrop)
PRINT '_______________________________________
_________'
PRINT @.vchArchiveTable_To_Delete1 + '_' + @.TableNumToDrop + '
Dropped.' PRINT ''
FETCH lcurTables_To_Be_Deleted into @.TableToDrop
end
close lcurTables_To_Be_Deleted
deallocate lcurTables_To_Be_Deleted
EndMake sure you declare the cursor insensitive. Also, did you initialize all
your variables?
Seriously though, why would you want to prolong the life of such a poor
design by developing more code like this? This is so abysmal it breaks just
about every rule in the book: cursors; dynamic SQL; use of system tables;
modifying metadata. Maybe you aren't concerned, but this sort of thing
shouldn't get past even the most basic audit or compliance review.
How about:
DELETE FROM SomeTable
WHERE the_date < @.some_date
Simpler, no?
Take care.
David Portas
SQL Server MVP
--|||I have had this debate before David.
My new model design is in the pipeline with management.
Until then I am stuck with this.
The issue I have is why when the part that calculates the w
number is runit goes into the loop. But it only goes in once. Have I missed something
blatent in the code that prevents it from going into the loop for the other
tables returned from the cursor definition
"David Portas" wrote:
> Make sure you declare the cursor insensitive. Also, did you initialize all
> your variables?
> Seriously though, why would you want to prolong the life of such a poor
> design by developing more code like this? This is so abysmal it breaks jus
t
> about every rule in the book: cursors; dynamic SQL; use of system tables;
> modifying metadata. Maybe you aren't concerned, but this sort of thing
> shouldn't get past even the most basic audit or compliance review.
> How about :
> DELETE FROM SomeTable
> WHERE the_date < @.some_date
> Simpler, no?
> Take care.
> --
> David Portas
> SQL Server MVP
> --
>|||> My new model design is in the pipeline with management.
Good news :-)
I haven't tested it out but I suspect the cursor terminates because dropping
the table deletes the current row. That's why I suggested you declare the
cursor INSENSITIVE. Alternatively, select the table names into a table
variable and iterate through that.
Again, note that you really should avoid system tables where you can. For
this I would prefer to use INFORMATION_SCHEMA.TABLES.
Hope this helps.
David Portas
SQL Server MVP
--
"marcmc" wrote:
> I have had this debate before David.
> My new model design is in the pipeline with management.
> Until then I am stuck with this.
> The issue I have is why when the part that calculates the w
number is run
> it goes into the loop. But it only goes in once. Have I missed something
> blatent in the code that prevents it from going into the loop for the othe
r
> tables returned from the cursor definition
>
> "David Portas" wrote:
>|||I execute ...
exec uspDB_Maint_Archive_Snaps 'FAT_SNAP_PO_RISK_DETAIL_mth', 216, 'DbName'
Then I declare cursor...
Begin
declare lcurTables_To_Be_Deleted cursor for
select substring(NAME,28,3) from SNAPS_ARC..SYSOBJECTS(nolock) where name
like @.vchArchiveTable_To_Delete and xtype = 'U' order by crdate desc
open lcurTables_To_Be_Deleted
fetch lcurTables_To_Be_Deleted into @.TabNoToDrop
This brings the following into the cursor
--
215
214
213
212
212
I want to keep 215 but delete the rest
I want the next few lines of to do this. But I cant seem to find a way.
Please help...
SELECT @.TableNumToDrop = convert(int, @.TabNoToDrop) -- 215
SELECT @.TableNumToDrop = + convert(varchar,(@.TableNumToDrop - 1))
SELECT @.TableToDrop = @.vchArchiveTable_To_Delete1 + '_' + @.TableNumToDrop
while @.@.fetch_status = 0
begin
PRINT @.vchArchiveTable_To_Delete1 + '_' + @.TabNoToDrop
EXEC('DROP TABLE SNAPS_ARC..' + @.vchArchiveTable_To_Delete1 + '_' +
@.TabNoToDrop)
PRINT '_______________________________________
_________'
PRINT @.vchArchiveTable_To_Delete1 + '_' + @.TabNoToDrop + ' Dropped.'
PRINT ''
FETCH lcurTables_To_Be_Deleted into @.TabNoToDrop
end
close lcurTables_To_Be_Deleted
deallocate lcurTab|||On Mon, 9 May 2005 02:54:01 -0700, marcmc wrote:
>Hi, I wonder can someone help me.
(snip)
Hi marcmc,
Since you are already aware of the downsides of this design, and
battling management to get it changed, I won't comment on that...
In your code, you use three almost but not quite equally named
variables: @.TabNoToDrop, @.TableNumToDrop and @.TableToDrop. The first and
third seem to server the same purpose (the first is used in the first
FETCH and the third in the FETCH in the WHILE loop). The second includes
only a part of the table name and is calculated from the first after the
first FETCH. Since it is not recalculated after the FETCH in the WHILE
loop, it's value never changes, and you are just attempting to drop the
same table over and over again.
>Begin
> DECLARE lcurTables_To_Be_Deleted CURSOR FOR
> SELECT substring(NAME,28,3) FROM SNAPS_ARC..SYSOBJECTS(nolock) WHERE NAME
>like @.vchArchiveTable_To_Delete and xtype = 'U' order by crdate desc
> OPEN lcurTables_To_Be_Deleted
> FETCH lcurTables_To_Be_Deleted into @.TabNoToDrop
> SELECT @.TableNumToDrop = convert(int, @.TabNoToDrop) -- wk215
> PRINT '1'
> PRINT @.TableNumToDrop
> SELECT @.TableNumToDrop = + convert(varchar,(@.TableNumToDrop - 1))
> PRINT '2' -- wk214
> PRINT @.TableNumToDrop
> while @.@.fetch_status = 0
> begin
> EXEC('DROP TABLE SNAPS_ARC..' + @.vchArchiveTable_To_Delete1 + '_'
>+ @.TableNumToDrop)
> PRINT '_______________________________________
_________'
> PRINT @.vchArchiveTable_To_Delete1 + '_' + @.TableNumToDrop + '
>Dropped.' PRINT ''
> FETCH lcurTables_To_Be_Deleted into @.TableToDrop
> end
> close lcurTables_To_Be_Deleted
> deallocate lcurTables_To_Be_Deleted
>End
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
cursor is running too slow
I have a cursor that is taking about 4:00 minutes to run on 100 records. Th
e
cursor works on 11 different tables and inserts on average around 25 records
per table. Does this time frame sound right? When I do the same thing with
a temp table it only takes about 2:00 minutes for 1000 records. I can't use
temp tables though because I want to be able to rollback each transaction
independently if there is a problem. Any suggestions on decreasing the time
my cursor takes to run?Cursors are inherently slow. You haven't posted your DDL and what you're
really trying to do.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"ASP Developer" <ASPDeveloper@.discussions.microsoft.com> wrote in message
news:7A67BFAB-A4F1-49F6-9C9B-1FC6FD988804@.microsoft.com...
I have a cursor that is taking about 4:00 minutes to run on 100 records.
The
cursor works on 11 different tables and inserts on average around 25 records
per table. Does this time frame sound right? When I do the same thing with
a temp table it only takes about 2:00 minutes for 1000 records. I can't use
temp tables though because I want to be able to rollback each transaction
independently if there is a problem. Any suggestions on decreasing the time
my cursor takes to run?|||"ASP Developer" <ASPDeveloper@.discussions.microsoft.com> wrote in message
news:7A67BFAB-A4F1-49F6-9C9B-1FC6FD988804@.microsoft.com...
>I have a cursor that is taking about 4:00 minutes to run on 100 records.
>The
> cursor works on 11 different tables and inserts on average around 25
> records
> per table. Does this time frame sound right? When I do the same thing
> with
> a temp table it only takes about 2:00 minutes for 1000 records. I can't
> use
> temp tables though because I want to be able to rollback each transaction
> independently if there is a problem. Any suggestions on decreasing the
> time
> my cursor takes to run?
Usually the best cursor optimization is to get rid of the cursor...
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||If re-writing this T-SQL to something set based is not practical at the
moment, then test if specifying the FAST_FORWARD and READ_ONLY options when
declaring the cursor help improve it's performance.
Read up on DECLARE CURSOR in SQL Server Books Online.
"ASP Developer" <ASPDeveloper@.discussions.microsoft.com> wrote in message
news:7A67BFAB-A4F1-49F6-9C9B-1FC6FD988804@.microsoft.com...
>I have a cursor that is taking about 4:00 minutes to run on 100 records.
>The
> cursor works on 11 different tables and inserts on average around 25
> records
> per table. Does this time frame sound right? When I do the same thing
> with
> a temp table it only takes about 2:00 minutes for 1000 records. I can't
> use
> temp tables though because I want to be able to rollback each transaction
> independently if there is a problem. Any suggestions on decreasing the
> time
> my cursor takes to run?
e
cursor works on 11 different tables and inserts on average around 25 records
per table. Does this time frame sound right? When I do the same thing with
a temp table it only takes about 2:00 minutes for 1000 records. I can't use
temp tables though because I want to be able to rollback each transaction
independently if there is a problem. Any suggestions on decreasing the time
my cursor takes to run?Cursors are inherently slow. You haven't posted your DDL and what you're
really trying to do.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"ASP Developer" <ASPDeveloper@.discussions.microsoft.com> wrote in message
news:7A67BFAB-A4F1-49F6-9C9B-1FC6FD988804@.microsoft.com...
I have a cursor that is taking about 4:00 minutes to run on 100 records.
The
cursor works on 11 different tables and inserts on average around 25 records
per table. Does this time frame sound right? When I do the same thing with
a temp table it only takes about 2:00 minutes for 1000 records. I can't use
temp tables though because I want to be able to rollback each transaction
independently if there is a problem. Any suggestions on decreasing the time
my cursor takes to run?|||"ASP Developer" <ASPDeveloper@.discussions.microsoft.com> wrote in message
news:7A67BFAB-A4F1-49F6-9C9B-1FC6FD988804@.microsoft.com...
>I have a cursor that is taking about 4:00 minutes to run on 100 records.
>The
> cursor works on 11 different tables and inserts on average around 25
> records
> per table. Does this time frame sound right? When I do the same thing
> with
> a temp table it only takes about 2:00 minutes for 1000 records. I can't
> use
> temp tables though because I want to be able to rollback each transaction
> independently if there is a problem. Any suggestions on decreasing the
> time
> my cursor takes to run?
Usually the best cursor optimization is to get rid of the cursor...
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||If re-writing this T-SQL to something set based is not practical at the
moment, then test if specifying the FAST_FORWARD and READ_ONLY options when
declaring the cursor help improve it's performance.
Read up on DECLARE CURSOR in SQL Server Books Online.
"ASP Developer" <ASPDeveloper@.discussions.microsoft.com> wrote in message
news:7A67BFAB-A4F1-49F6-9C9B-1FC6FD988804@.microsoft.com...
>I have a cursor that is taking about 4:00 minutes to run on 100 records.
>The
> cursor works on 11 different tables and inserts on average around 25
> records
> per table. Does this time frame sound right? When I do the same thing
> with
> a temp table it only takes about 2:00 minutes for 1000 records. I can't
> use
> temp tables though because I want to be able to rollback each transaction
> independently if there is a problem. Any suggestions on decreasing the
> time
> my cursor takes to run?
Sunday, March 25, 2012
Cursor Help Please
Hi all!
I just need some help with cursors, a topic I don't profess to be an expert in.
I've got two tables with a 1-many relationship between them. Let's say they're "tblCustomers" and "tblOrders".
tblCustomers data:
CustomerID Name
1 Fred
2 Charlie
3 Lucy
tblOrders data:
OrderID CustomerId Qty
1 1 10
2 1 5
3 1 20
4 2 8
5 3 20
6 3 6
I need to return a result set that puts all the "many" records into a single row, like:
Name Qty
Fred 10, 15, 20
Charlie 8
Lucy 20, 6
THANKS IN ADVANCE!!!!!!!!!!Create this function
CREATE Function ListOfQuantity (@.ID Varchar(100))
RETURNS Varchar(2000)
As
Begin
Declare @.List Varchar(2000)
set @.List=''
Select @.List = @.List+RTrim(Qte) + ',' From Orders Where CustomerID =RTrim(@.ID)
Return(Left(@.List,Len(@.List)-1))
End|||then you can do
select name,dbo.ListOfQuantity(id)
from customer|||What is magical is the
Select @.List = @.List + Col1 From Tbl
For a result that should be
1
2
3
it returns
123|||I'll try it Karolyn, THANKS!|||Many thanks!
I just need some help with cursors, a topic I don't profess to be an expert in.
I've got two tables with a 1-many relationship between them. Let's say they're "tblCustomers" and "tblOrders".
tblCustomers data:
CustomerID Name
1 Fred
2 Charlie
3 Lucy
tblOrders data:
OrderID CustomerId Qty
1 1 10
2 1 5
3 1 20
4 2 8
5 3 20
6 3 6
I need to return a result set that puts all the "many" records into a single row, like:
Name Qty
Fred 10, 15, 20
Charlie 8
Lucy 20, 6
THANKS IN ADVANCE!!!!!!!!!!Create this function
CREATE Function ListOfQuantity (@.ID Varchar(100))
RETURNS Varchar(2000)
As
Begin
Declare @.List Varchar(2000)
set @.List=''
Select @.List = @.List+RTrim(Qte) + ',' From Orders Where CustomerID =RTrim(@.ID)
Return(Left(@.List,Len(@.List)-1))
End|||then you can do
select name,dbo.ListOfQuantity(id)
from customer|||What is magical is the
Select @.List = @.List + Col1 From Tbl
For a result that should be
1
2
3
it returns
123|||I'll try it Karolyn, THANKS!|||Many thanks!
Cursor from diff table -challenge
I have 17 tables with names as Breaktable1, Breaktable2,... till
Breaktable17
i make a query like this
set nocount on
Declare @.sEventtable varchar(20)
Declare @.sstartzoneid varchar(20)
Declare @.ssttopzoneid varchar(20)
Select @.sstartzoneid ='1'
Select @.ssttopzoneid = '17'
select @.sstartzoneid = convert(smallint,@.sstartzoneid)
if @.sstartzoneid<> 0
DECLARE Tablecursor FOR <---> ( error here )
select status, sum(cost)
FROM "breaktable " + @.sstartzoneid
where breakdate=DATEDIFF(day,'08/12/1960','03/29/2003')
group by status
CLOSE tableCursor
DEALLOCATE tableCursor
can somebody help ??You can't use a variable that way in the DECLARE CURSOR. You may have to declare the table dynamically.
Is now a good time to ask why you've got 17 tables when it seems that 1 would do quite nicely?
-PatP|||What do you want to do with the data accessed by the cursor? You can create a view comprised of all 17 tables with an artificially created field that would identify which table the data is coming from, and then do your cursor definition based on the value of that field. And of course, I agree with Pat 100%.|||I just want to read it for each day
so that i can just change the date everyday i run the query
Trust me it needed the 17 tables
I am from oracle so a little tizzy in sql|||Sorry, I don't trust anybody. I've seen 17-table implementation here, made by Anderson Consulting, and the company was paying them $180/hour. Just by looking at the names I can tell you that the design was not done by the person who understands either business or db design. How can I trust?|||I still can't see for the life of me any reason to have either 17 tables or a cursor. If those make you more comfortable, I guess that works Ok for me. I just can't see any reason for them.
-PatP|||I agree , but i did not designed it i just have to use it
so how do i make a query that will query all 17 tables and give me sum of cost and status
cursor or no cursor ?
table name break1 to break17|||I'd use:CREATE VIEW dbo.BigBreak AS
SELECT 1 AS table_id, * FROM dbo.breaktable1
UNION ALL SELECT 2, * FROM dbo.breaktable2
UNION ALL SELECT 3, * FROM dbo.breaktable3
UNION ALL SELECT 4, * FROM dbo.breaktable4
UNION ALL SELECT 5, * FROM dbo.breaktable5
UNION ALL SELECT 6, * FROM dbo.breaktable6
UNION ALL SELECT 7, * FROM dbo.breaktable7
UNION ALL SELECT 8, * FROM dbo.breaktable8
UNION ALL SELECT 9, * FROM dbo.breaktable9
UNION ALL SELECT 10, * FROM dbo.breaktable10
UNION ALL SELECT 11, * FROM dbo.breaktable11
UNION ALL SELECT 12, * FROM dbo.breaktable12
UNION ALL SELECT 13, * FROM dbo.breaktable13
UNION ALL SELECT 14, * FROM dbo.breaktable14
UNION ALL SELECT 15, * FROM dbo.breaktable15
UNION ALL SELECT 16, * FROM dbo.breaktable16
UNION ALL SELECT 17, * FROM dbo.breaktable17
GOI'm not sure how well the optimizer will process it, but I think that it will do Ok. You'll definitely need to test it first.
-PatP|||i went like this but takes about 30 mins to run
select status, sum(cost) as zone1
FROM event1
where breakdate=DATEDIFF(day,'08/12/1960','04/15/2004')
group by status
select status, sum(cost) as zone2
FROM event2
where breakdate=DATEDIFF(day,'08/12/1960','04/15/2004')
group by status
TILL 18
But takes about 35 mins for each run and slows all other user
and then i have to do shut down restart to make it work nicely ??
and the reason for 18 tables is that an event occurs and needs to be inserted(recorded) at 18 times at that exact moment (millisecond)|||How did you jump from 17 to 18 tables? Why on earth do you need to record extremely time sensitive information 18 different times?
-PatP|||we are an adsales company that insert ads on cable
it has to insert those breaks in 18 zones at the same exact time
whether its successfull or not it does an insert in each table.|||I am sure you can have a cursor within a cursor to do that|||A cursor within a cursor ...
I swear by the Holy book (guys here know what I mean) ... I would never even think of ever doing that ...|||we are an adsales company that insert ads on cable
it has to insert those breaks in 18 zones at the same exact time
whether its successfull or not it does an insert in each table.
Pat we got a CROSS THREAD JOIN going on here...
In any case a Timezone type column is all you needed...
Anderson Consulting...scrubs....
had to babysit everyone of them...
I saw one created a 7 level nested cursor...wondered why it ran 7 hours...|||besides the time their are about 15 other fields that needs to be inserted and they are different !!!!|||Are the structure (column type and order) the same for all (1..N) of the breakpoint tables? If not, could they be made the same?
-PatP|||the structure is the same
But about 40 people may be reading or writing
beside the auto writing these zones (18) do
so i taught its quiet busy
Tell me if some one is writing to a table lets say different rows does it locks the whole table
I understood as if they write to same rows than it will
Am I correct???|||No, you can have a practical infinity of inserts all going on at the same time without a problem in SQL-2000. This was a severe problem prior to SQL 6.5, it got much better with the IRL code in SQL 7.0, and it is a non-issue with SQL 2000.
In SQL-2000, you can only have one spid doing an UPDATE or a DELETE on any given row at any given time, but that is true of almost every database. We typically have 300-500 users simultaneously updating some of our heavily used tables at period end (but that is on pretty powerful hardware).
-PatP|||thanks but yes this is 6.5 i am working on
Perhaps then the designer was right ||||
I am not a fan of microsoft
For oracle
i would do a cursor very nicely
declare
i number
for i in 1 .. 17
loop
select *
from break + "i"|||As long as you aren't running 6.0 or earlier, you should be Ok. The problems were fixed (at least for the most part) in 6.5 as long as you don't explicitly turn IRL off!
This is just a suggestion, but 6.5 is at least five years old. That would be like using Oracle 7.0 (yes, there really was something before 7.3)! You really need to encourage your management to upgrade to current versions.
-PatP|||I don't understand what's wrong with this:
select table_id, status, sum(cost) as zone1
FROM dbo.BigBreak --event1 <--WHAT IS THIS?
where breakdate=DATEDIFF(day,'08/12/1960','04/15/2004')
group by table_id, status|||Nothing wrong actually
It was just a curiosity
I am about to upgrade to sql 2000
it usually takes about 4 months to get the things passed through accounts
thanks
Breaktable17
i make a query like this
set nocount on
Declare @.sEventtable varchar(20)
Declare @.sstartzoneid varchar(20)
Declare @.ssttopzoneid varchar(20)
Select @.sstartzoneid ='1'
Select @.ssttopzoneid = '17'
select @.sstartzoneid = convert(smallint,@.sstartzoneid)
if @.sstartzoneid<> 0
DECLARE Tablecursor FOR <---> ( error here )
select status, sum(cost)
FROM "breaktable " + @.sstartzoneid
where breakdate=DATEDIFF(day,'08/12/1960','03/29/2003')
group by status
CLOSE tableCursor
DEALLOCATE tableCursor
can somebody help ??You can't use a variable that way in the DECLARE CURSOR. You may have to declare the table dynamically.
Is now a good time to ask why you've got 17 tables when it seems that 1 would do quite nicely?
-PatP|||What do you want to do with the data accessed by the cursor? You can create a view comprised of all 17 tables with an artificially created field that would identify which table the data is coming from, and then do your cursor definition based on the value of that field. And of course, I agree with Pat 100%.|||I just want to read it for each day
so that i can just change the date everyday i run the query
Trust me it needed the 17 tables
I am from oracle so a little tizzy in sql|||Sorry, I don't trust anybody. I've seen 17-table implementation here, made by Anderson Consulting, and the company was paying them $180/hour. Just by looking at the names I can tell you that the design was not done by the person who understands either business or db design. How can I trust?|||I still can't see for the life of me any reason to have either 17 tables or a cursor. If those make you more comfortable, I guess that works Ok for me. I just can't see any reason for them.
-PatP|||I agree , but i did not designed it i just have to use it
so how do i make a query that will query all 17 tables and give me sum of cost and status
cursor or no cursor ?
table name break1 to break17|||I'd use:CREATE VIEW dbo.BigBreak AS
SELECT 1 AS table_id, * FROM dbo.breaktable1
UNION ALL SELECT 2, * FROM dbo.breaktable2
UNION ALL SELECT 3, * FROM dbo.breaktable3
UNION ALL SELECT 4, * FROM dbo.breaktable4
UNION ALL SELECT 5, * FROM dbo.breaktable5
UNION ALL SELECT 6, * FROM dbo.breaktable6
UNION ALL SELECT 7, * FROM dbo.breaktable7
UNION ALL SELECT 8, * FROM dbo.breaktable8
UNION ALL SELECT 9, * FROM dbo.breaktable9
UNION ALL SELECT 10, * FROM dbo.breaktable10
UNION ALL SELECT 11, * FROM dbo.breaktable11
UNION ALL SELECT 12, * FROM dbo.breaktable12
UNION ALL SELECT 13, * FROM dbo.breaktable13
UNION ALL SELECT 14, * FROM dbo.breaktable14
UNION ALL SELECT 15, * FROM dbo.breaktable15
UNION ALL SELECT 16, * FROM dbo.breaktable16
UNION ALL SELECT 17, * FROM dbo.breaktable17
GOI'm not sure how well the optimizer will process it, but I think that it will do Ok. You'll definitely need to test it first.
-PatP|||i went like this but takes about 30 mins to run
select status, sum(cost) as zone1
FROM event1
where breakdate=DATEDIFF(day,'08/12/1960','04/15/2004')
group by status
select status, sum(cost) as zone2
FROM event2
where breakdate=DATEDIFF(day,'08/12/1960','04/15/2004')
group by status
TILL 18
But takes about 35 mins for each run and slows all other user
and then i have to do shut down restart to make it work nicely ??
and the reason for 18 tables is that an event occurs and needs to be inserted(recorded) at 18 times at that exact moment (millisecond)|||How did you jump from 17 to 18 tables? Why on earth do you need to record extremely time sensitive information 18 different times?
-PatP|||we are an adsales company that insert ads on cable
it has to insert those breaks in 18 zones at the same exact time
whether its successfull or not it does an insert in each table.|||I am sure you can have a cursor within a cursor to do that|||A cursor within a cursor ...
I swear by the Holy book (guys here know what I mean) ... I would never even think of ever doing that ...|||we are an adsales company that insert ads on cable
it has to insert those breaks in 18 zones at the same exact time
whether its successfull or not it does an insert in each table.
Pat we got a CROSS THREAD JOIN going on here...
In any case a Timezone type column is all you needed...
Anderson Consulting...scrubs....
had to babysit everyone of them...
I saw one created a 7 level nested cursor...wondered why it ran 7 hours...|||besides the time their are about 15 other fields that needs to be inserted and they are different !!!!|||Are the structure (column type and order) the same for all (1..N) of the breakpoint tables? If not, could they be made the same?
-PatP|||the structure is the same
But about 40 people may be reading or writing
beside the auto writing these zones (18) do
so i taught its quiet busy
Tell me if some one is writing to a table lets say different rows does it locks the whole table
I understood as if they write to same rows than it will
Am I correct???|||No, you can have a practical infinity of inserts all going on at the same time without a problem in SQL-2000. This was a severe problem prior to SQL 6.5, it got much better with the IRL code in SQL 7.0, and it is a non-issue with SQL 2000.
In SQL-2000, you can only have one spid doing an UPDATE or a DELETE on any given row at any given time, but that is true of almost every database. We typically have 300-500 users simultaneously updating some of our heavily used tables at period end (but that is on pretty powerful hardware).
-PatP|||thanks but yes this is 6.5 i am working on
Perhaps then the designer was right ||||
I am not a fan of microsoft
For oracle
i would do a cursor very nicely
declare
i number
for i in 1 .. 17
loop
select *
from break + "i"|||As long as you aren't running 6.0 or earlier, you should be Ok. The problems were fixed (at least for the most part) in 6.5 as long as you don't explicitly turn IRL off!
This is just a suggestion, but 6.5 is at least five years old. That would be like using Oracle 7.0 (yes, there really was something before 7.3)! You really need to encourage your management to upgrade to current versions.
-PatP|||I don't understand what's wrong with this:
select table_id, status, sum(cost) as zone1
FROM dbo.BigBreak --event1 <--WHAT IS THIS?
where breakdate=DATEDIFF(day,'08/12/1960','04/15/2004')
group by table_id, status|||Nothing wrong actually
It was just a curiosity
I am about to upgrade to sql 2000
it usually takes about 4 months to get the things passed through accounts
thanks
CURSOR FETCH STATEMENT IS HANGING
Hi All,
I have a stored proc that uses a cursor to iterate over the join resultset
of 5 tables. After fetching roughly 12,000 records and running for about 30
minutes, the next FETCH statement inside the WHILE loop hangs. This happens
consisently. I tried to do a commit / checkpoint after every 10,000 records,
but the problem persists.
Can any one please provide some thoughts about why this could be happening?
ps: The tempdb size is 955 MB and the disk size is 33 GB. This is our
development database.
Thanks,
RajeshWhat are you trying to accomplish?
Why are you using cursors?
What kind of cursor are you using?
Any research about a set-based solution?
Can we see some code?
AMB
"rajeshlh" wrote:
> Hi All,
> I have a stored proc that uses a cursor to iterate over the join resultse
t
> of 5 tables. After fetching roughly 12,000 records and running for about 3
0
> minutes, the next FETCH statement inside the WHILE loop hangs. This happen
s
> consisently. I tried to do a commit / checkpoint after every 10,000 record
s,
> but the problem persists.
> Can any one please provide some thoughts about why this could be happening
?
> ps: The tempdb size is 955 MB and the disk size is 33 GB. This is our
> development database.
> Thanks,
> Rajesh
>|||I have to process a set of records obtained from the join of 5 tables. For
each record in the resultset, i need to insert/update into 5-6 tables, plus
i
need to write to a log table the key for each inserted row or updated row.
Following is the code.
The FETCH FROM Subscription_Cursor fails inside the While Loop after some
11,000 records. Since iam writing debug statements to another table, i can
say that after processing 11000 records inside the cursor, the FETCH
statement freezes.
I did think about SET-based approach, but the processing logic forces me to
use a cursor. The Job is run nightly and processes about 100,000+ records.
Following is the SP Code.
CREATE PROCEDURE TEST_SP
AS
--Variables to store Name record values
DECLARE @.Account_Number varchar(20),@.Publisher_Code varchar(5),@.Mag_Code
varchar(5),@.Postal_Code varchar(6),@.Common_Name varchar(30),
@.Job_Title varchar(50),@.Company_Name varchar(30),@.Address_Line_1
varchar(30),@.Address_Line_2 varchar(30),@.City varchar(30),
@.State_Prov varchar(2),@.Country_Code varchar(2),@.Telephone
varchar(11),@.Fax_Number varchar(11)
--Variables to store Product record values
DECLARE @.Service_Status varchar(1),@.Start_Issue datetime,@.Expire_Issue
datetime,@.Num_Copies integer,@.Email_User_Name varchar(50),
@.Current_Email_Address varchar(50),@.Email_Password varchar(50)
--Variables to store Order record values
DECLARE @.Order_Number varchar(20),@.Order_Status varchar(1),@.Order_Term
integer,@.Order_Net_Value money,@.Source_Code varchar(2),@.Medium_Code
varchar(2),
@.Document_Key varchar(10),@.Setcode varchar(1),@.Orig_Start_Issue
datetime,@.Order_Entry_Type varchar(5)
--Variables to store Demographic record values
DECLARE @.Version_Number varchar(10),@.Segment_Number integer ,@.Demo_Data
varchar(1024)
--variales to store newly created reg_visitor_id,account_id and order_id -
for subscriptions not existing in Elogic Reg DB
DECLARE @.Reg_Visitor_Id integer ,@.Account_Id integer ,@.Order_Id
integer,@.Address_Id integer
--variables to store values present in staging tables - For subscriptions
already existing in Elogic Reg DB
DECLARE @.eLogic_Reg_Visitor_Id integer ,@.eLogic_Account_Id integer
,@.eLogic_Order_Id integer,@.eLogic_Publication_Id integer
--variables used for logging
DECLARE @.Publication_Id integer,@.Pub_Code varchar(5),@.Target_Type
varchar(100),@.Process_Type varchar(100),@.Summary_Count integer,
@.Log_Summary_Id integer,@.Source_File_Name varchar(100)
--Variables to store Donor's data
DECLARE @.Donor_Company_Name varchar(30),@.Donor_Address_Line_1
varchar(30),@.Donor_Address_Line_2 varchar(30),@.Donor_City varchar(30),
@.Donor_State_Prov varchar(2),@.Donor_Country_Code
varchar(2),@.Donor_Telephone varchar(11),@.Donor_Fax_Number varchar(11),
@.Donor_Account_number varchar(20), @.Donor_Postal_Code varchar(6)
--Variables to store CDS to Elogic Converted values
DECLARE @.eLogic_Account_Status varchar(5),@.eLogic_Pay_Type
varchar(5),@.eLogic_Auto_Renew bit,@.eLogic_Account_Type varchar(20)
--Misc variables
DECLARE @.SP_NAME varchar(50),@.Ret integer
DECLARE @.Reg_Visitor_Product varchar(100)
DECLARE @.Exec_Start_Time datetime
DECLARE @.Promo_Code_Pos2 varchar(1)
DECLARE @.Delivery_Type varchar(1)
DECLARE @.Cnt int
--Cursor to subscriptions stored in the staging tables
DECLARE Subscriptions_Cursor CURSOR FOR
SELECT nr.eLogic_reg_visitor_id,nr.eLogic_publication_id,nr.source_file_name
,nr.account_number,nr.publisher_code,nr.mag_code,nr.postal_code,nr.common_na
me,nr.job_title,nr.company_name,
nr.address_line_1,nr.address_line_2,nr.city,nr.state_prov,nr.country_code,nr
.telephone,nr.fax_number,
pr.eLogic_account_id,pr.service_status,pr.start_issue,pr.expire_issue,pr.num
_copies,pr.email_user_name,pr.email_password,pr.current_email_address,
ord.eLogic_order_id,ord.order_number,ord.order_status,ord.order_term,ord.ord
er_net_value,ord.source_code,ord.medium_code,ord.document_key,
ord.setcode,ord.orig_start_issue,ord.order_entry_type,
dr.version_number,dr.segment_number,dr.demo_data,
delivery_type
FROM cds_name_record nr
JOIN cds_product_record pr
ON nr.account_number=pr.account_number
AND nr.publisher_code=pr.publisher_code
AND nr.mag_code=pr.mag_code
JOIN cds_order_record ord
ON ord.account_number=pr.account_number
AND ord.publisher_code=pr.publisher_code
AND ord.mag_code=pr.mag_code
JOIN cds_demographic_record dr
ON dr.account_number=pr.account_number
AND dr.publisher_code=pr.publisher_code
AND dr.mag_code = pr.mag_code
JOIN publication_subscription ps
ON ps.external_multi_mag_code = pr.publisher_code
AND ps.code = pr.mag_code
WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
Get Only current address
AND (ord.setcode='A' OR ord.setcode='C' OR ord.setcode='E')--Get only
Non-Gift and Donee Orders
AND ord.order_status='B' --Get only Base Orders
AND dr.demo_status = 'B' --Get only Base Demo Records
--AND nr.account_number <>'0010115103'
ORDER BY CAST(nr.account_number AS int)
--cursor to the reg_feed_detail_log table , used for populating the
reg_feed_summary_log table
DECLARE Summary_Cursor CURSOR FOR
SELECT
publication_id,pub_code,target_type,proc
ess_type,source_file_name,count(*) a
s
summary_count
FROM reg_feed_detail_log
GROUP BY publication_id,pub_code,target_type,proc
ess_type,source_file_name
SET @.SP_NAME = OBJECT_NAME(@.@.PROCID)
SET @.Exec_Start_Time = CURRENT_TIMESTAMP
OPEN Subscriptions_Cursor
FETCH NEXT FROM Subscriptions_Cursor
INTO
@.eLogic_Reg_Visitor_Id,@.eLogic_Publicati
on_Id,@.Source_File_Name,@.Account_Num
ber ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
@.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
@.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
@.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
@.Current_Email_Address,
@.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
@.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
@.Version_Number,@.Segment_Number,@.Demo_Da
ta,@.Delivery_Type
SET @.Cnt =1
PRINT 'start'
WHILE @.@.FETCH_STATUS = 0
BEGIN --B1
INSERT INTO Demo_data values ('inside while loop',null,null)
INSERT INTO debug_table
(eLogic_reg_visitor_id,eLogic_publicatio
n_id,account_number,publisher_code,m
ag_code,order_status,set_code,delivery_t
ype,seq)
VALUES (@.eLogic_Reg_Visitor_Id,@.eLogic_p
ublication_id,@.Account_Number
,@.Publisher_Code ,@.Mag_Code,@.Order_Status,@.SetCode,@.Deliv
ery_Type,@.Cnt)
SELECT @.Reg_Visitor_Id = NULL,@.Account_Id = NULL,@.Order_Id =
NULL,@.Address_Id = NULL,@.Reg_Visitor_Product = NULL
SELECT @.Donor_Company_Name = NULL,@.Donor_Address_Line_1 =
NULL,@.Donor_Address_Line_2 = NULL,@.Donor_City = NULL,
@.Donor_State_Prov = NULL,@.Donor_Country_Code = NULL,@.Donor_Telephone =
NULL,@.Donor_Fax_Number = NULL,
@.Donor_Account_number = NULL
SELECT @.eLogic_Account_Status = NULL,@.eLogic_Pay_Type =
NULL,@.eLogic_Auto_Renew = NULL,@.eLogic_Account_Type = NULL
SELECT @.Promo_Code_Pos2 = NULL
IF ( @.SetCode = 'C' OR @.SetCode ='E') --SetCode 'C' and 'E' denote donee
BEGIN --B2
SELECT @.Donor_Account_number = nr.account_number,@.Donor_Postal_Code =
nr.postal_code,@.Donor_Company_Name = nr.company_name,
@.Donor_Address_Line_1 = nr.address_line_1,@.Donor_Address_Line_2 =
nr.address_line_2,@.Donor_City = nr.city,
@.Donor_State_Prov = nr.state_prov,@.Donor_Country_Code =
nr.country_code,@.Donor_Telephone = nr.telephone,@.Donor_Fax_Number =
nr.fax_number
FROM cds_name_record nr
JOIN cds_order_record ord
ON ord.account_number=nr.account_number
AND ord.publisher_code=nr.publisher_code
AND ord.mag_code=nr.mag_code
WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
Get Only current address Name record
AND (ord.setcode='B' OR ord.setcode='D')--Get only Donor Orders
AND (ord.order_status='B' OR ord.order_status='D') --Get only Base Order
or Non-Subscibing Donor Order
AND nr.publisher_code = @.Publisher_Code
AND nr.mag_code = @.Mag_Code
AND ord.order_number = @.Order_Number
-- Note : Donor and Donee will have different account numbers, but same
publisher code, mag code and order number
END --END B2
SET @.eLogic_Account_Status = CASE
WHEN @.Service_Status = 'A' THEN 'A'
WHEN @.Service_Status IN ( 'B','H','I') THEN 'C'
WHEN @.Service_Status = 'C' THEN 'X'
WHEN @.Service_Status IN ('D','E','F','G') THEN 'O'
END
SET @.eLogic_Pay_Type = CASE
WHEN @.Order_Entry_Type IN ('A','B','C','D') THEN 'P'
WHEN @.Order_Entry_Type IN ('L','M','U') THEN 'F'
ELSE ''
END
SET @.eLogic_Auto_Renew= CASE
WHEN ( SUBSTRING(@.Document_Key,1,1)= '#' OR @.Medium_Code = 'E') THEN 1
ELSE 0
END
SET @.Promo_Code_Pos2 = SUBSTRING(@.Document_Key,2,1)
SET @.eLogic_Account_Type=CASE
WHEN @.Source_Code = 'CC' THEN
CASE
WHEN @.Promo_Code_Pos2 = 'T' THEN 'FREETRIAL'
WHEN @.Promo_Code_Pos2 = 'E' THEN 'EMAILONLY'
ELSE 'CONTROLLED'
END
WHEN @.Source_Code = 'CA' THEN 'COMP'
ELSE 'PAID'
END
SELECT top 1 @.Reg_Visitor_Id = A.reg_visitor_id
FROM account A
JOIN publication_subscription PS
ON A.pub_code = PS.code
WHERE A.account_number = @.Account_Number
AND PS.external_multi_mag_code = @.Publisher_Code
--If a reg_visitor is not determined in the Staging tables or by looking up
the Account table , a new reg_visitor
--is created
IF ( @.eLogic_Reg_Visitor_Id IS NULL AND @.Reg_Visitor_Id IS NULL)
BEGIN --B3
--No Reg_Visitor corresponding to the mag subscription - mag subscription
generated at CDS
--Create a new reg_visitor and associated rows in Account,Account_Order
and visitor_demographic
SELECT @.Reg_Visitor_Product = master_brand
FROM publication
WHERE external_multi_mag_code=@.Publisher_Code
--insert into reg_visior table
EXEC @.Ret= dbo.upd_reg_visitor @.p_reg_visitor_id = @.Reg_Visitor_Id OUTPUT,
@.p_common_name = @.Common_Name,
@.p_company_name = @.Company_Name,
@.p_email = @.Current_Email_Address,
@.p_encrypted_password = @.Email_Password,
@.p_given_name = NULL,
@.p_login_id = @.Email_User_Name,
@.p_merged_visitor_id = NULL,
@.p_middle_initial = NULL,
@.p_middle_name = NULL,
@.p_name_suffix = NULL,
@.p_password = @.Email_Password,
@.p_product = @.Reg_Visitor_Product,
@.p_subproduct = NULL,
@.p_professional_title = @.Job_Title,
@.p_record_status = 1,
@.p_registration_level = 1,
@.p_salutation = NULL,
@.p_sur_name = NULL,
@.p_zip = NULL,
@.p_country = 'TESTDTS'
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting into Reg_Visitor table!', 18, 2, @.SP_NAME)
--ROLLBACK TRAN T1
RETURN -2
END
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id = SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'reg_visitor',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = NULL,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
--insert into address table
EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
@.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_company_name = @.Company_Name,
@.p_address_line_1 = @.Address_Line_1,
@.p_address_line_2 = @.Address_Line_2,
@.p_city = @.City,
@.p_postal_code = @.Postal_Code,
@.p_state_prov = @.State_Prov,
@.p_country_code = @.Country_Code,
@.p_phone = @.Telephone,
@.p_fax = @.Fax_Number,
@.p_address_type = 0 --shipping
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'address',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Address_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
IF (@.SETCODE = 'C' OR @.SETCODE = 'E') -- Donee subscription
BEGIN
--Insert the corresponding Donor Address
EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
@.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_company_name = @.Donor_Company_Name,
@.p_address_line_1 = @.Donor_Address_Line_1,
@.p_address_line_2 = @.Donor_Address_Line_2,
@.p_city = @.Donor_City,
@.p_postal_code = @.Donor_Postal_Code,
@.p_state_prov = @.Donor_State_Prov,
@.p_country_code = @.Donor_Country_Code,
@.p_phone = @.Donor_Telephone,
@.p_fax = @.Donor_Fax_Number,
@.p_address_type = 1 --Billing
--IF @.Account_Number = '0010115103' OR @.Cnt =11214
--INSERT INTO demo_data values ('inserted into address for donor')
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'address',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Address_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
--IF @.Account_Number = '0010115103' OR @.Cnt =11214
--INSERT INTO demo_data values ('inserted into detail log for donor
address')
END
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting into Address table!', 18, 3, @.sp_name)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -3
END
--Insert into Account table
EXEC @.Ret = dbo.set_account @.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_account_id = @.Account_Id OUTPUT,
@.p_account_number = @.Account_Number,
@.p_pub_code = @.Mag_Code,
@.p_account_type = @.eLogic_Account_type ,
@.p_status = @.eLogic_Account_Status,
@.p_supp_account_number = @.Donor_Account_Number -- If its a Non-Gift
Order, NULL will be inserted for Supp_Account_Number
IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Account_Id IS NULL)
BEGIN
RAISERROR('%s: Error inserting into Account table!', 18, 4, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -4
END
INSERT INTO Demo_data values ('completed inserting into account
table',@.Reg_Visitor_id,@.Account_Number)
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'account',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Account_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
INSERT INTO Demo_data values ('completed inserting into detail log for
account table',@.Reg_Visitor_id,@.Account_Number)
--insert into account_order table
EXEC @.Ret = dbo.set_account_order @.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_account_id = @.Account_Id,
@.p_order_id = @.Order_Id OUTPUT,
@.p_term = @.Order_Term,
@.p_term_unit = NULL,
@.p_pay_type = @.eLogic_Pay_Type,
@.p_net_amt = @.Order_Net_Value,
@.p_quantity = @.Num_Copies,
@.p_vendor_order_number = @.Order_Number,
@.p_promo_response_key = @.Document_Key,
@.p_number_of_installments=1,
@.p_auto_renew =@.eLogic_Auto_Renew
IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Order_Id IS NULL)
BEGIN
RAISERROR('%s: Error inserting into Account_Order table!', 18, 5, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -5
END
INSERT INTO Demo_data values ('completed inserting into account_order
table',@.Reg_Visitor_id,@.Account_Number)
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'account_order',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Account_Id ,
@.P_Target_Key_3 = @.Order_Id,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
INSERT INTO Demo_data values ('completed logging to detail log table for
account_order',@.Reg_Visitor_id,@.Account_
Number)
--insert into visitor_demographics table only for Online Magazines ;
Delivery Type W - Online, P- Print
IF(@.Delivery_Type = 'W')
BEGIN
INSERT INTO Demo_data values ('calling demographcis
sp',@.Reg_Visitor_id,@.Account_Number)
EXEC @.Ret = Process_CDS_Demographics @.Reg_Visitor_Id = @.Reg_Visitor_Id,
@.Mag_Code = @.Mag_Code,
@.Demo_Data = @.Demo_Data,
@.Segment_Number = @.Segment_Number,
@.Version_Number = @.Version_Number
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting/updating into Visitor_Demographics
table!', 18, 6, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -6
END
END
INSERT INTO Demo_data values ('DEBUG LINE
HIT',@.Reg_Visitor_id,@.Account_Number)
END --END B3
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('About to fetch next row- current row details
-->',@.Reg_visitor_id,@.Account_number)
FETCH NEXT FROM Subscriptions_Cursor
INTO
@.eLogic_Reg_Visitor_Id,@.eLogic_Publicati
on_Id,@.Source_File_Name,@.Account_Num
ber ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
@.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
@.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
@.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
@.Current_Email_Address,
@.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
@.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
@.Version_Number,@.Segment_Number,@.Demo_Da
ta,@.Delivery_Type
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('fetched next row',@.eLogic_Reg_Visitor_Id,@.Account_num
ber)
SET @.Cnt = @.Cnt + 1
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('value of @.@.FETCHSTATUS =',@.@.Fetch_Status,@.Account_number)
END --END B1
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
"Alejandro Mesa" wrote:
> What are you trying to accomplish?
> Why are you using cursors?
> What kind of cursor are you using?
> Any research about a set-based solution?
> Can we see some code?
>
> AMB
> "rajeshlh" wrote:
>|||- Declare the cursor LOCAL FAST_FORWARD.
- In the WHERE clause, change:
GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date
by:
(nr.address_start_date <= GETDATE() and nr.address_end_date >= GETDATE())
- Do you need the ORDER BY clause in the select associated?
- Are the rows, of the result, processed in group or one by one?. For
example, Do you need to process multiple rows
per nr.account_number as a group?
- Can you do it by chuncks?
declare @.min int
declare @.max int
select @.min = min(nr.account_number), @.max = max(nr.account_number)
from ...
where ...
while @.min <= @.max
begin
DECLARE Subscriptions_Cursor CURSOR local fast_forward
for
select ...
from ...
where ...
and nr.account_number between @.min and case when (@.min + 1000) > @.max
then @.max else (@.min + 1000) end
open cursor ...
while 1 = 1
begin
fetch ...
if @.@.error != 0 or @.@.fetch_status != 0 break
..
end
close cursor ...
deallocate cursor ...
set @.min = @.min + 1000
end
...
AMB
"rajeshlh" wrote:
> I have to process a set of records obtained from the join of 5 tables. For
> each record in the resultset, i need to insert/update into 5-6 tables, plu
s i
> need to write to a log table the key for each inserted row or updated row
.
> Following is the code.
> The FETCH FROM Subscription_Cursor fails inside the While Loop after some
> 11,000 records. Since iam writing debug statements to another table, i can
> say that after processing 11000 records inside the cursor, the FETCH
> statement freezes.
> I did think about SET-based approach, but the processing logic forces me t
o
> use a cursor. The Job is run nightly and processes about 100,000+ records.
> Following is the SP Code.
> CREATE PROCEDURE TEST_SP
> AS
> --Variables to store Name record values
> DECLARE @.Account_Number varchar(20),@.Publisher_Code varchar(5),@.Mag_Code
> varchar(5),@.Postal_Code varchar(6),@.Common_Name varchar(30),
> @.Job_Title varchar(50),@.Company_Name varchar(30),@.Address_Line_1
> varchar(30),@.Address_Line_2 varchar(30),@.City varchar(30),
> @.State_Prov varchar(2),@.Country_Code varchar(2),@.Telephone
> varchar(11),@.Fax_Number varchar(11)
> --Variables to store Product record values
> DECLARE @.Service_Status varchar(1),@.Start_Issue datetime,@.Expire_Issue
> datetime,@.Num_Copies integer,@.Email_User_Name varchar(50),
> @.Current_Email_Address varchar(50),@.Email_Password varchar(50)
> --Variables to store Order record values
> DECLARE @.Order_Number varchar(20),@.Order_Status varchar(1),@.Order_Term
> integer,@.Order_Net_Value money,@.Source_Code varchar(2),@.Medium_Code
> varchar(2),
> @.Document_Key varchar(10),@.Setcode varchar(1),@.Orig_Start_Issue
> datetime,@.Order_Entry_Type varchar(5)
> --Variables to store Demographic record values
> DECLARE @.Version_Number varchar(10),@.Segment_Number integer ,@.Demo_Data
> varchar(1024)
> --variales to store newly created reg_visitor_id,account_id and order_id -
> for subscriptions not existing in Elogic Reg DB
> DECLARE @.Reg_Visitor_Id integer ,@.Account_Id integer ,@.Order_Id
> integer,@.Address_Id integer
> --variables to store values present in staging tables - For subscriptions
> already existing in Elogic Reg DB
> DECLARE @.eLogic_Reg_Visitor_Id integer ,@.eLogic_Account_Id integer
> ,@.eLogic_Order_Id integer,@.eLogic_Publication_Id integer
> --variables used for logging
> DECLARE @.Publication_Id integer,@.Pub_Code varchar(5),@.Target_Type
> varchar(100),@.Process_Type varchar(100),@.Summary_Count integer,
> @.Log_Summary_Id integer,@.Source_File_Name varchar(100)
> --Variables to store Donor's data
> DECLARE @.Donor_Company_Name varchar(30),@.Donor_Address_Line_1
> varchar(30),@.Donor_Address_Line_2 varchar(30),@.Donor_City varchar(30),
> @.Donor_State_Prov varchar(2),@.Donor_Country_Code
> varchar(2),@.Donor_Telephone varchar(11),@.Donor_Fax_Number varchar(11),
> @.Donor_Account_number varchar(20), @.Donor_Postal_Code varchar(6)
> --Variables to store CDS to Elogic Converted values
> DECLARE @.eLogic_Account_Status varchar(5),@.eLogic_Pay_Type
> varchar(5),@.eLogic_Auto_Renew bit,@.eLogic_Account_Type varchar(20)
> --Misc variables
> DECLARE @.SP_NAME varchar(50),@.Ret integer
> DECLARE @.Reg_Visitor_Product varchar(100)
> DECLARE @.Exec_Start_Time datetime
> DECLARE @.Promo_Code_Pos2 varchar(1)
> DECLARE @.Delivery_Type varchar(1)
> DECLARE @.Cnt int
>
> --Cursor to subscriptions stored in the staging tables
> DECLARE Subscriptions_Cursor CURSOR FOR
> SELECT nr.eLogic_reg_visitor_id,nr.eLogic_publication_id,nr.source_file_na
me,nr.account_number,nr.publisher_code,nr.mag_code,nr.postal_code,nr.common_
name,nr.job_title,nr.company_name,
> nr.address_line_1,nr.address_line_2,nr.city,nr.state_prov,nr.country_code
,nr.telephone,nr.fax_number,
> pr.eLogic_account_id,pr.service_status,pr.start_issue,pr.expire_issue,pr.
num_copies,pr.email_user_name,pr.email_password,pr.current_email_address,
> ord.eLogic_order_id,ord.order_number,ord.order_status,ord.order_term,ord.
order_net_value,ord.source_code,ord.medium_code,ord.document_key,
> ord.setcode,ord.orig_start_issue,ord.order_entry_type,
> dr.version_number,dr.segment_number,dr.demo_data,
> delivery_type
> FROM cds_name_record nr
> JOIN cds_product_record pr
> ON nr.account_number=pr.account_number
> AND nr.publisher_code=pr.publisher_code
> AND nr.mag_code=pr.mag_code
> JOIN cds_order_record ord
> ON ord.account_number=pr.account_number
> AND ord.publisher_code=pr.publisher_code
> AND ord.mag_code=pr.mag_code
> JOIN cds_demographic_record dr
> ON dr.account_number=pr.account_number
> AND dr.publisher_code=pr.publisher_code
> AND dr.mag_code = pr.mag_code
> JOIN publication_subscription ps
> ON ps.external_multi_mag_code = pr.publisher_code
> AND ps.code = pr.mag_code
> WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
> Get Only current address
> AND (ord.setcode='A' OR ord.setcode='C' OR ord.setcode='E')--Get only
> Non-Gift and Donee Orders
> AND ord.order_status='B' --Get only Base Orders
> AND dr.demo_status = 'B' --Get only Base Demo Records
> --AND nr.account_number <>'0010115103'
> ORDER BY CAST(nr.account_number AS int)
>
> --cursor to the reg_feed_detail_log table , used for populating the
> reg_feed_summary_log table
> DECLARE Summary_Cursor CURSOR FOR
> SELECT
> publication_id,pub_code,target_type,proc
ess_type,source_file_name,count(*)
as
> summary_count
> FROM reg_feed_detail_log
> GROUP BY publication_id,pub_code,target_type,proc
ess_type,source_file_name
> SET @.SP_NAME = OBJECT_NAME(@.@.PROCID)
> SET @.Exec_Start_Time = CURRENT_TIMESTAMP
> OPEN Subscriptions_Cursor
> FETCH NEXT FROM Subscriptions_Cursor
> INTO
> @.eLogic_Reg_Visitor_Id,@.eLogic_Publicat
ion_Id,@.Source_File_Name,@.Account_
Number ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
> @.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
> @.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
> @.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
> ,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
> @.Current_Email_Address,
> @.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
> ,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
> @.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
> @.Version_Number,@.Segment_Number,@.Demo_D
ata,@.Delivery_Type
>
> SET @.Cnt =1
> PRINT 'start'
> WHILE @.@.FETCH_STATUS = 0
> BEGIN --B1
> INSERT INTO Demo_data values ('inside while loop',null,null)
> INSERT INTO debug_table
> (eLogic_reg_visitor_id,eLogic_publicatio
n_id,account_number,publisher_code
,mag_code,order_status,set_code,delivery
_type,seq)
> VALUES (@.eLogic_Reg_Visitor_Id,@.eLogic_
publication_id,@.Account_Number
> ,@.Publisher_Code ,@.Mag_Code,@.Order_Status,@.SetCode,@.Deliv
ery_Type,@.Cnt)
> SELECT @.Reg_Visitor_Id = NULL,@.Account_Id = NULL,@.Order_Id =
> NULL,@.Address_Id = NULL,@.Reg_Visitor_Product = NULL
> SELECT @.Donor_Company_Name = NULL,@.Donor_Address_Line_1 =
> NULL,@.Donor_Address_Line_2 = NULL,@.Donor_City = NULL,
> @.Donor_State_Prov = NULL,@.Donor_Country_Code = NULL,@.Donor_Telephone =
> NULL,@.Donor_Fax_Number = NULL,
> @.Donor_Account_number = NULL
> SELECT @.eLogic_Account_Status = NULL,@.eLogic_Pay_Type =
> NULL,@.eLogic_Auto_Renew = NULL,@.eLogic_Account_Type = NULL
> SELECT @.Promo_Code_Pos2 = NULL
> IF ( @.SetCode = 'C' OR @.SetCode ='E') --SetCode 'C' and 'E' denote donee
> BEGIN --B2
> SELECT @.Donor_Account_number = nr.account_number,@.Donor_Postal_Code =
> nr.postal_code,@.Donor_Company_Name = nr.company_name,
> @.Donor_Address_Line_1 = nr.address_line_1,@.Donor_Address_Line_2 =
> nr.address_line_2,@.Donor_City = nr.city,
> @.Donor_State_Prov = nr.state_prov,@.Donor_Country_Code =
> nr.country_code,@.Donor_Telephone = nr.telephone,@.Donor_Fax_Number =
> nr.fax_number
> FROM cds_name_record nr
> JOIN cds_order_record ord
> ON ord.account_number=nr.account_number
> AND ord.publisher_code=nr.publisher_code
> AND ord.mag_code=nr.mag_code
> WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date -
-
> Get Only current address Name record
> AND (ord.setcode='B' OR ord.setcode='D')--Get only Donor Orders
> AND (ord.order_status='B' OR ord.order_status='D') --Get only Base Orde
r
> or Non-Subscibing Donor Order
> AND nr.publisher_code = @.Publisher_Code
> AND nr.mag_code = @.Mag_Code
> AND ord.order_number = @.Order_Number
> -- Note : Donor and Donee will have different account numbers, but same
> publisher code, mag code and order number
> END --END B2
>
> SET @.eLogic_Account_Status = CASE
> WHEN @.Service_Status = 'A' THEN 'A'
> WHEN @.Service_Status IN ( 'B','H','I') THEN 'C'
> WHEN @.Service_Status = 'C' THEN 'X'
> WHEN @.Service_Status IN ('D','E','F','G') THEN 'O'
> END
> SET @.eLogic_Pay_Type = CASE
> WHEN @.Order_Entry_Type IN ('A','B','C','D') THEN 'P'
> WHEN @.Order_Entry_Type IN ('L','M','U') THEN 'F'
> ELSE ''
> END
> SET @.eLogic_Auto_Renew= CASE
> WHEN ( SUBSTRING(@.Document_Key,1,1)= '#' OR @.Medium_Code = 'E') THEN
1
> ELSE 0
> END
> SET @.Promo_Code_Pos2 = SUBSTRING(@.Document_Key,2,1)
> SET @.eLogic_Account_Type=CASE
> WHEN @.Source_Code = 'CC' THEN
> CASE
> WHEN @.Promo_Code_Pos2 = 'T' THEN 'FREETRIAL'
> WHEN @.Promo_Code_Pos2 = 'E' THEN 'EMAILONLY'
> ELSE 'CONTROLLED'
> END
> WHEN @.Source_Code = 'CA' THEN 'COMP'
> ELSE 'PAID'
> END
>
> SELECT top 1 @.Reg_Visitor_Id = A.reg_visitor_id
> FROM account A
> JOIN publication_subscription PS
> ON A.pub_code = PS.code
> WHERE A.account_number = @.Account_Number
> AND PS.external_multi_mag_code = @.Publisher_Code
> --If a reg_visitor is not determined in the Staging tables or by looking
up
> the Account table , a new reg_visitor
> --is created
> IF ( @.eLogic_Reg_Visitor_Id IS NULL AND @.Reg_Visitor_Id IS NULL)
> BEGIN --B3
> --No Reg_Visitor corresponding to the mag subscription - mag subscriptio
n
> generated at CDS
> --Create a new reg_visitor and associated rows in Account,Account_Order
> and visitor_demographic
> SELECT @.Reg_Visitor_Product = master_brand
> FROM publication
> WHERE external_multi_mag_code=@.Publisher_Code
> --insert into reg_visior table
> EXEC @.Ret= dbo.upd_reg_visitor @.p_reg_visitor_id = @.Reg_Visitor_Id OUTP
UT,
> @.p_common_name = @.Common_Name,
> @.p_company_name = @.Company_Name,
> @.p_email = @.Current_Email_Address,
> @.p_encrypted_password = @.Email_Password,
> @.p_given_name = NULL,
> @.p_login_id = @.Email_User_Name,
> @.p_merged_visitor_id = NULL,
> @.p_middle_initial = NULL,
> @.p_middle_name = NULL,
> @.p_name_suffix = NULL,
> @.p_password = @.Email_Password,
> @.p_product = @.Reg_Visitor_Product,
> @.p_subproduct = NULL,
> @.p_professional_title = @.Job_Title,
> @.p_record_status = 1,
> @.p_registration_level = 1,
> @.p_salutation = NULL,
> @.p_sur_name = NULL,
> @.p_zip = NULL,
> @.p_country = 'TESTDTS'
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting into Reg_Visitor table!', 18, 2, @.SP_NAM
E)
> --ROLLBACK TRAN T1
> RETURN -2
> END
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id = SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'reg_visitor',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = NULL,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> --insert into address table
> EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
> @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_company_name = @.Company_Name,
> @.p_address_line_1 = @.Address_Line_1,
> @.p_address_line_2 = @.Address_Line_2,
> @.p_city = @.City,
> @.p_postal_code = @.Postal_Code,
> @.p_state_prov = @.State_Prov,
> @.p_country_code = @.Country_Code,
> @.p_phone = @.Telephone,
> @.p_fax = @.Fax_Number,
> @.p_address_type = 0 --shipping
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'address',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Address_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> IF (@.SETCODE = 'C' OR @.SETCODE = 'E') -- Donee subscription
> BEGIN
> --Insert the corresponding Donor Address
> EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
> @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_company_name = @.Donor_Company_Name,
> @.p_address_line_1 = @.Donor_Address_Line_1,
> @.p_address_line_2 = @.Donor_Address_Line_2,
> @.p_city = @.Donor_City,
> @.p_postal_code = @.Donor_Postal_Code,
> @.p_state_prov = @.Donor_State_Prov,
> @.p_country_code = @.Donor_Country_Code,
> @.p_phone = @.Donor_Telephone,
> @.p_fax = @.Donor_Fax_Number,
> @.p_address_type = 1 --Billing
> --IF @.Account_Number = '0010115103' OR @.Cnt =11214
> --INSERT INTO demo_data values ('inserted into address for donor')
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'address',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Address_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> --IF @.Account_Number = '0010115103' OR @.Cnt =11214
> --INSERT INTO demo_data values ('inserted into detail log for donor
> address')
> END
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting into Address table!', 18, 3, @.sp_name)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -3
> END
> --Insert into Account table
> EXEC @.Ret = dbo.set_account @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_account_id = @.Account_Id OUTPUT,
> @.p_account_number = @.Account_Number,
> @.p_pub_code = @.Mag_Code,
> @.p_account_type = @.eLogic_Account_type ,
> @.p_status = @.eLogic_Account_Status,
> @.p_supp_account_number = @.Donor_Account_Number -- If its a Non-Gift
> Order, NULL will be inserted for Supp_Account_Number
>
> IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Account_Id IS NULL)
> BEGIN
> RAISERROR('%s: Error inserting into Account table!', 18, 4, @.SP_NAME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -4
> END
> INSERT INTO Demo_data values ('completed inserting into account
> table',@.Reg_Visitor_id,@.Account_Number)
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'account',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Account_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> INSERT INTO Demo_data values ('completed inserting into detail log for
> account table',@.Reg_Visitor_id,@.Account_Number)
> --insert into account_order table
> EXEC @.Ret = dbo.set_account_order @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_account_id = @.Account_Id,
> @.p_order_id = @.Order_Id OUTPUT,
> @.p_term = @.Order_Term,
> @.p_term_unit = NULL,
> @.p_pay_type = @.eLogic_Pay_Type,
> @.p_net_amt = @.Order_Net_Value,
> @.p_quantity = @.Num_Copies,
> @.p_vendor_order_number = @.Order_Number,
> @.p_promo_response_key = @.Document_Key,
> @.p_number_of_installments=1,
> @.p_auto_renew =@.eLogic_Auto_Renew
> IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Order_Id IS NULL)
> BEGIN
> RAISERROR('%s: Error inserting into Account_Order table!', 18, 5, @.SP_N
AME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -5
> END
> INSERT INTO Demo_data values ('completed inserting into account_order
> table',@.Reg_Visitor_id,@.Account_Number)
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'account_order',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Account_Id ,
> @.P_Target_Key_3 = @.Order_Id,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> INSERT INTO Demo_data values ('completed logging to detail log table for
> account_order',@.Reg_Visitor_id,@.Account_
Number)
> --insert into visitor_demographics table only for Online Magazines ;
> Delivery Type W - Online, P- Print
> IF(@.Delivery_Type = 'W')
> BEGIN
> INSERT INTO Demo_data values ('calling demographcis
> sp',@.Reg_Visitor_id,@.Account_Number)
> EXEC @.Ret = Process_CDS_Demographics @.Reg_Visitor_Id = @.Reg_Visitor_Id
,
> @.Mag_Code = @.Mag_Code,
> @.Demo_Data = @.Demo_Data,
> @.Segment_Number = @.Segment_Number,
> @.Version_Number = @.Version_Number
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting/updating into Visitor_Demographics
> table!', 18, 6, @.SP_NAME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -6
> END
> END
> INSERT INTO Demo_data values ('DEBUG LINE
> HIT',@.Reg_Visitor_id,@.Account_Number)
>
> END --END B3
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('About to fetch next row- current row details
> -->',@.Reg_visitor_id,@.Account_number)
> FETCH NEXT FROM Subscriptions_Cursor
> INTO
> @.eLogic_Reg_Visitor_Id,@.eLogic_Publicat
ion_Id,@.Source_File_Name,@.Account_
Number ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
> @.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
> @.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
> @.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
> ,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
> @.Current_Email_Address,
> @.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
> ,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
> @.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
> @.Version_Number,@.Segment_Number,@.Demo_
Data,@.Delivery_Type
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('fetched next row',@.eLogic_Reg_Visitor_Id,@.Account_num
ber)
> SET @.Cnt = @.Cnt + 1
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('value of @.@.FETCHSTATUS =',@.@.Fetch_Status,@.Account_number)
> END --END B1
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
>
> "Alejandro Mesa" wrote:
>
I have a stored proc that uses a cursor to iterate over the join resultset
of 5 tables. After fetching roughly 12,000 records and running for about 30
minutes, the next FETCH statement inside the WHILE loop hangs. This happens
consisently. I tried to do a commit / checkpoint after every 10,000 records,
but the problem persists.
Can any one please provide some thoughts about why this could be happening?
ps: The tempdb size is 955 MB and the disk size is 33 GB. This is our
development database.
Thanks,
RajeshWhat are you trying to accomplish?
Why are you using cursors?
What kind of cursor are you using?
Any research about a set-based solution?
Can we see some code?
AMB
"rajeshlh" wrote:
> Hi All,
> I have a stored proc that uses a cursor to iterate over the join resultse
t
> of 5 tables. After fetching roughly 12,000 records and running for about 3
0
> minutes, the next FETCH statement inside the WHILE loop hangs. This happen
s
> consisently. I tried to do a commit / checkpoint after every 10,000 record
s,
> but the problem persists.
> Can any one please provide some thoughts about why this could be happening
?
> ps: The tempdb size is 955 MB and the disk size is 33 GB. This is our
> development database.
> Thanks,
> Rajesh
>|||I have to process a set of records obtained from the join of 5 tables. For
each record in the resultset, i need to insert/update into 5-6 tables, plus
i
need to write to a log table the key for each inserted row or updated row.
Following is the code.
The FETCH FROM Subscription_Cursor fails inside the While Loop after some
11,000 records. Since iam writing debug statements to another table, i can
say that after processing 11000 records inside the cursor, the FETCH
statement freezes.
I did think about SET-based approach, but the processing logic forces me to
use a cursor. The Job is run nightly and processes about 100,000+ records.
Following is the SP Code.
CREATE PROCEDURE TEST_SP
AS
--Variables to store Name record values
DECLARE @.Account_Number varchar(20),@.Publisher_Code varchar(5),@.Mag_Code
varchar(5),@.Postal_Code varchar(6),@.Common_Name varchar(30),
@.Job_Title varchar(50),@.Company_Name varchar(30),@.Address_Line_1
varchar(30),@.Address_Line_2 varchar(30),@.City varchar(30),
@.State_Prov varchar(2),@.Country_Code varchar(2),@.Telephone
varchar(11),@.Fax_Number varchar(11)
--Variables to store Product record values
DECLARE @.Service_Status varchar(1),@.Start_Issue datetime,@.Expire_Issue
datetime,@.Num_Copies integer,@.Email_User_Name varchar(50),
@.Current_Email_Address varchar(50),@.Email_Password varchar(50)
--Variables to store Order record values
DECLARE @.Order_Number varchar(20),@.Order_Status varchar(1),@.Order_Term
integer,@.Order_Net_Value money,@.Source_Code varchar(2),@.Medium_Code
varchar(2),
@.Document_Key varchar(10),@.Setcode varchar(1),@.Orig_Start_Issue
datetime,@.Order_Entry_Type varchar(5)
--Variables to store Demographic record values
DECLARE @.Version_Number varchar(10),@.Segment_Number integer ,@.Demo_Data
varchar(1024)
--variales to store newly created reg_visitor_id,account_id and order_id -
for subscriptions not existing in Elogic Reg DB
DECLARE @.Reg_Visitor_Id integer ,@.Account_Id integer ,@.Order_Id
integer,@.Address_Id integer
--variables to store values present in staging tables - For subscriptions
already existing in Elogic Reg DB
DECLARE @.eLogic_Reg_Visitor_Id integer ,@.eLogic_Account_Id integer
,@.eLogic_Order_Id integer,@.eLogic_Publication_Id integer
--variables used for logging
DECLARE @.Publication_Id integer,@.Pub_Code varchar(5),@.Target_Type
varchar(100),@.Process_Type varchar(100),@.Summary_Count integer,
@.Log_Summary_Id integer,@.Source_File_Name varchar(100)
--Variables to store Donor's data
DECLARE @.Donor_Company_Name varchar(30),@.Donor_Address_Line_1
varchar(30),@.Donor_Address_Line_2 varchar(30),@.Donor_City varchar(30),
@.Donor_State_Prov varchar(2),@.Donor_Country_Code
varchar(2),@.Donor_Telephone varchar(11),@.Donor_Fax_Number varchar(11),
@.Donor_Account_number varchar(20), @.Donor_Postal_Code varchar(6)
--Variables to store CDS to Elogic Converted values
DECLARE @.eLogic_Account_Status varchar(5),@.eLogic_Pay_Type
varchar(5),@.eLogic_Auto_Renew bit,@.eLogic_Account_Type varchar(20)
--Misc variables
DECLARE @.SP_NAME varchar(50),@.Ret integer
DECLARE @.Reg_Visitor_Product varchar(100)
DECLARE @.Exec_Start_Time datetime
DECLARE @.Promo_Code_Pos2 varchar(1)
DECLARE @.Delivery_Type varchar(1)
DECLARE @.Cnt int
--Cursor to subscriptions stored in the staging tables
DECLARE Subscriptions_Cursor CURSOR FOR
SELECT nr.eLogic_reg_visitor_id,nr.eLogic_publication_id,nr.source_file_name
,nr.account_number,nr.publisher_code,nr.mag_code,nr.postal_code,nr.common_na
me,nr.job_title,nr.company_name,
nr.address_line_1,nr.address_line_2,nr.city,nr.state_prov,nr.country_code,nr
.telephone,nr.fax_number,
pr.eLogic_account_id,pr.service_status,pr.start_issue,pr.expire_issue,pr.num
_copies,pr.email_user_name,pr.email_password,pr.current_email_address,
ord.eLogic_order_id,ord.order_number,ord.order_status,ord.order_term,ord.ord
er_net_value,ord.source_code,ord.medium_code,ord.document_key,
ord.setcode,ord.orig_start_issue,ord.order_entry_type,
dr.version_number,dr.segment_number,dr.demo_data,
delivery_type
FROM cds_name_record nr
JOIN cds_product_record pr
ON nr.account_number=pr.account_number
AND nr.publisher_code=pr.publisher_code
AND nr.mag_code=pr.mag_code
JOIN cds_order_record ord
ON ord.account_number=pr.account_number
AND ord.publisher_code=pr.publisher_code
AND ord.mag_code=pr.mag_code
JOIN cds_demographic_record dr
ON dr.account_number=pr.account_number
AND dr.publisher_code=pr.publisher_code
AND dr.mag_code = pr.mag_code
JOIN publication_subscription ps
ON ps.external_multi_mag_code = pr.publisher_code
AND ps.code = pr.mag_code
WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
Get Only current address
AND (ord.setcode='A' OR ord.setcode='C' OR ord.setcode='E')--Get only
Non-Gift and Donee Orders
AND ord.order_status='B' --Get only Base Orders
AND dr.demo_status = 'B' --Get only Base Demo Records
--AND nr.account_number <>'0010115103'
ORDER BY CAST(nr.account_number AS int)
--cursor to the reg_feed_detail_log table , used for populating the
reg_feed_summary_log table
DECLARE Summary_Cursor CURSOR FOR
SELECT
publication_id,pub_code,target_type,proc
ess_type,source_file_name,count(*) a
s
summary_count
FROM reg_feed_detail_log
GROUP BY publication_id,pub_code,target_type,proc
ess_type,source_file_name
SET @.SP_NAME = OBJECT_NAME(@.@.PROCID)
SET @.Exec_Start_Time = CURRENT_TIMESTAMP
OPEN Subscriptions_Cursor
FETCH NEXT FROM Subscriptions_Cursor
INTO
@.eLogic_Reg_Visitor_Id,@.eLogic_Publicati
on_Id,@.Source_File_Name,@.Account_Num
ber ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
@.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
@.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
@.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
@.Current_Email_Address,
@.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
@.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
@.Version_Number,@.Segment_Number,@.Demo_Da
ta,@.Delivery_Type
SET @.Cnt =1
PRINT 'start'
WHILE @.@.FETCH_STATUS = 0
BEGIN --B1
INSERT INTO Demo_data values ('inside while loop',null,null)
INSERT INTO debug_table
(eLogic_reg_visitor_id,eLogic_publicatio
n_id,account_number,publisher_code,m
ag_code,order_status,set_code,delivery_t
ype,seq)
VALUES (@.eLogic_Reg_Visitor_Id,@.eLogic_p
ublication_id,@.Account_Number
,@.Publisher_Code ,@.Mag_Code,@.Order_Status,@.SetCode,@.Deliv
ery_Type,@.Cnt)
SELECT @.Reg_Visitor_Id = NULL,@.Account_Id = NULL,@.Order_Id =
NULL,@.Address_Id = NULL,@.Reg_Visitor_Product = NULL
SELECT @.Donor_Company_Name = NULL,@.Donor_Address_Line_1 =
NULL,@.Donor_Address_Line_2 = NULL,@.Donor_City = NULL,
@.Donor_State_Prov = NULL,@.Donor_Country_Code = NULL,@.Donor_Telephone =
NULL,@.Donor_Fax_Number = NULL,
@.Donor_Account_number = NULL
SELECT @.eLogic_Account_Status = NULL,@.eLogic_Pay_Type =
NULL,@.eLogic_Auto_Renew = NULL,@.eLogic_Account_Type = NULL
SELECT @.Promo_Code_Pos2 = NULL
IF ( @.SetCode = 'C' OR @.SetCode ='E') --SetCode 'C' and 'E' denote donee
BEGIN --B2
SELECT @.Donor_Account_number = nr.account_number,@.Donor_Postal_Code =
nr.postal_code,@.Donor_Company_Name = nr.company_name,
@.Donor_Address_Line_1 = nr.address_line_1,@.Donor_Address_Line_2 =
nr.address_line_2,@.Donor_City = nr.city,
@.Donor_State_Prov = nr.state_prov,@.Donor_Country_Code =
nr.country_code,@.Donor_Telephone = nr.telephone,@.Donor_Fax_Number =
nr.fax_number
FROM cds_name_record nr
JOIN cds_order_record ord
ON ord.account_number=nr.account_number
AND ord.publisher_code=nr.publisher_code
AND ord.mag_code=nr.mag_code
WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
Get Only current address Name record
AND (ord.setcode='B' OR ord.setcode='D')--Get only Donor Orders
AND (ord.order_status='B' OR ord.order_status='D') --Get only Base Order
or Non-Subscibing Donor Order
AND nr.publisher_code = @.Publisher_Code
AND nr.mag_code = @.Mag_Code
AND ord.order_number = @.Order_Number
-- Note : Donor and Donee will have different account numbers, but same
publisher code, mag code and order number
END --END B2
SET @.eLogic_Account_Status = CASE
WHEN @.Service_Status = 'A' THEN 'A'
WHEN @.Service_Status IN ( 'B','H','I') THEN 'C'
WHEN @.Service_Status = 'C' THEN 'X'
WHEN @.Service_Status IN ('D','E','F','G') THEN 'O'
END
SET @.eLogic_Pay_Type = CASE
WHEN @.Order_Entry_Type IN ('A','B','C','D') THEN 'P'
WHEN @.Order_Entry_Type IN ('L','M','U') THEN 'F'
ELSE ''
END
SET @.eLogic_Auto_Renew= CASE
WHEN ( SUBSTRING(@.Document_Key,1,1)= '#' OR @.Medium_Code = 'E') THEN 1
ELSE 0
END
SET @.Promo_Code_Pos2 = SUBSTRING(@.Document_Key,2,1)
SET @.eLogic_Account_Type=CASE
WHEN @.Source_Code = 'CC' THEN
CASE
WHEN @.Promo_Code_Pos2 = 'T' THEN 'FREETRIAL'
WHEN @.Promo_Code_Pos2 = 'E' THEN 'EMAILONLY'
ELSE 'CONTROLLED'
END
WHEN @.Source_Code = 'CA' THEN 'COMP'
ELSE 'PAID'
END
SELECT top 1 @.Reg_Visitor_Id = A.reg_visitor_id
FROM account A
JOIN publication_subscription PS
ON A.pub_code = PS.code
WHERE A.account_number = @.Account_Number
AND PS.external_multi_mag_code = @.Publisher_Code
--If a reg_visitor is not determined in the Staging tables or by looking up
the Account table , a new reg_visitor
--is created
IF ( @.eLogic_Reg_Visitor_Id IS NULL AND @.Reg_Visitor_Id IS NULL)
BEGIN --B3
--No Reg_Visitor corresponding to the mag subscription - mag subscription
generated at CDS
--Create a new reg_visitor and associated rows in Account,Account_Order
and visitor_demographic
SELECT @.Reg_Visitor_Product = master_brand
FROM publication
WHERE external_multi_mag_code=@.Publisher_Code
--insert into reg_visior table
EXEC @.Ret= dbo.upd_reg_visitor @.p_reg_visitor_id = @.Reg_Visitor_Id OUTPUT,
@.p_common_name = @.Common_Name,
@.p_company_name = @.Company_Name,
@.p_email = @.Current_Email_Address,
@.p_encrypted_password = @.Email_Password,
@.p_given_name = NULL,
@.p_login_id = @.Email_User_Name,
@.p_merged_visitor_id = NULL,
@.p_middle_initial = NULL,
@.p_middle_name = NULL,
@.p_name_suffix = NULL,
@.p_password = @.Email_Password,
@.p_product = @.Reg_Visitor_Product,
@.p_subproduct = NULL,
@.p_professional_title = @.Job_Title,
@.p_record_status = 1,
@.p_registration_level = 1,
@.p_salutation = NULL,
@.p_sur_name = NULL,
@.p_zip = NULL,
@.p_country = 'TESTDTS'
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting into Reg_Visitor table!', 18, 2, @.SP_NAME)
--ROLLBACK TRAN T1
RETURN -2
END
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id = SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'reg_visitor',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = NULL,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
--insert into address table
EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
@.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_company_name = @.Company_Name,
@.p_address_line_1 = @.Address_Line_1,
@.p_address_line_2 = @.Address_Line_2,
@.p_city = @.City,
@.p_postal_code = @.Postal_Code,
@.p_state_prov = @.State_Prov,
@.p_country_code = @.Country_Code,
@.p_phone = @.Telephone,
@.p_fax = @.Fax_Number,
@.p_address_type = 0 --shipping
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'address',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Address_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
IF (@.SETCODE = 'C' OR @.SETCODE = 'E') -- Donee subscription
BEGIN
--Insert the corresponding Donor Address
EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
@.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_company_name = @.Donor_Company_Name,
@.p_address_line_1 = @.Donor_Address_Line_1,
@.p_address_line_2 = @.Donor_Address_Line_2,
@.p_city = @.Donor_City,
@.p_postal_code = @.Donor_Postal_Code,
@.p_state_prov = @.Donor_State_Prov,
@.p_country_code = @.Donor_Country_Code,
@.p_phone = @.Donor_Telephone,
@.p_fax = @.Donor_Fax_Number,
@.p_address_type = 1 --Billing
--IF @.Account_Number = '0010115103' OR @.Cnt =11214
--INSERT INTO demo_data values ('inserted into address for donor')
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'address',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Address_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
--IF @.Account_Number = '0010115103' OR @.Cnt =11214
--INSERT INTO demo_data values ('inserted into detail log for donor
address')
END
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting into Address table!', 18, 3, @.sp_name)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -3
END
--Insert into Account table
EXEC @.Ret = dbo.set_account @.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_account_id = @.Account_Id OUTPUT,
@.p_account_number = @.Account_Number,
@.p_pub_code = @.Mag_Code,
@.p_account_type = @.eLogic_Account_type ,
@.p_status = @.eLogic_Account_Status,
@.p_supp_account_number = @.Donor_Account_Number -- If its a Non-Gift
Order, NULL will be inserted for Supp_Account_Number
IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Account_Id IS NULL)
BEGIN
RAISERROR('%s: Error inserting into Account table!', 18, 4, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -4
END
INSERT INTO Demo_data values ('completed inserting into account
table',@.Reg_Visitor_id,@.Account_Number)
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'account',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Account_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
INSERT INTO Demo_data values ('completed inserting into detail log for
account table',@.Reg_Visitor_id,@.Account_Number)
--insert into account_order table
EXEC @.Ret = dbo.set_account_order @.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_account_id = @.Account_Id,
@.p_order_id = @.Order_Id OUTPUT,
@.p_term = @.Order_Term,
@.p_term_unit = NULL,
@.p_pay_type = @.eLogic_Pay_Type,
@.p_net_amt = @.Order_Net_Value,
@.p_quantity = @.Num_Copies,
@.p_vendor_order_number = @.Order_Number,
@.p_promo_response_key = @.Document_Key,
@.p_number_of_installments=1,
@.p_auto_renew =@.eLogic_Auto_Renew
IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Order_Id IS NULL)
BEGIN
RAISERROR('%s: Error inserting into Account_Order table!', 18, 5, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -5
END
INSERT INTO Demo_data values ('completed inserting into account_order
table',@.Reg_Visitor_id,@.Account_Number)
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'account_order',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Account_Id ,
@.P_Target_Key_3 = @.Order_Id,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
INSERT INTO Demo_data values ('completed logging to detail log table for
account_order',@.Reg_Visitor_id,@.Account_
Number)
--insert into visitor_demographics table only for Online Magazines ;
Delivery Type W - Online, P- Print
IF(@.Delivery_Type = 'W')
BEGIN
INSERT INTO Demo_data values ('calling demographcis
sp',@.Reg_Visitor_id,@.Account_Number)
EXEC @.Ret = Process_CDS_Demographics @.Reg_Visitor_Id = @.Reg_Visitor_Id,
@.Mag_Code = @.Mag_Code,
@.Demo_Data = @.Demo_Data,
@.Segment_Number = @.Segment_Number,
@.Version_Number = @.Version_Number
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting/updating into Visitor_Demographics
table!', 18, 6, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -6
END
END
INSERT INTO Demo_data values ('DEBUG LINE
HIT',@.Reg_Visitor_id,@.Account_Number)
END --END B3
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('About to fetch next row- current row details
-->',@.Reg_visitor_id,@.Account_number)
FETCH NEXT FROM Subscriptions_Cursor
INTO
@.eLogic_Reg_Visitor_Id,@.eLogic_Publicati
on_Id,@.Source_File_Name,@.Account_Num
ber ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
@.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
@.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
@.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
@.Current_Email_Address,
@.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
@.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
@.Version_Number,@.Segment_Number,@.Demo_Da
ta,@.Delivery_Type
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('fetched next row',@.eLogic_Reg_Visitor_Id,@.Account_num
ber)
SET @.Cnt = @.Cnt + 1
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('value of @.@.FETCHSTATUS =',@.@.Fetch_Status,@.Account_number)
END --END B1
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
"Alejandro Mesa" wrote:
> What are you trying to accomplish?
> Why are you using cursors?
> What kind of cursor are you using?
> Any research about a set-based solution?
> Can we see some code?
>
> AMB
> "rajeshlh" wrote:
>|||- Declare the cursor LOCAL FAST_FORWARD.
- In the WHERE clause, change:
GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date
by:
(nr.address_start_date <= GETDATE() and nr.address_end_date >= GETDATE())
- Do you need the ORDER BY clause in the select associated?
- Are the rows, of the result, processed in group or one by one?. For
example, Do you need to process multiple rows
per nr.account_number as a group?
- Can you do it by chuncks?
declare @.min int
declare @.max int
select @.min = min(nr.account_number), @.max = max(nr.account_number)
from ...
where ...
while @.min <= @.max
begin
DECLARE Subscriptions_Cursor CURSOR local fast_forward
for
select ...
from ...
where ...
and nr.account_number between @.min and case when (@.min + 1000) > @.max
then @.max else (@.min + 1000) end
open cursor ...
while 1 = 1
begin
fetch ...
if @.@.error != 0 or @.@.fetch_status != 0 break
..
end
close cursor ...
deallocate cursor ...
set @.min = @.min + 1000
end
...
AMB
"rajeshlh" wrote:
> I have to process a set of records obtained from the join of 5 tables. For
> each record in the resultset, i need to insert/update into 5-6 tables, plu
s i
> need to write to a log table the key for each inserted row or updated row
.
> Following is the code.
> The FETCH FROM Subscription_Cursor fails inside the While Loop after some
> 11,000 records. Since iam writing debug statements to another table, i can
> say that after processing 11000 records inside the cursor, the FETCH
> statement freezes.
> I did think about SET-based approach, but the processing logic forces me t
o
> use a cursor. The Job is run nightly and processes about 100,000+ records.
> Following is the SP Code.
> CREATE PROCEDURE TEST_SP
> AS
> --Variables to store Name record values
> DECLARE @.Account_Number varchar(20),@.Publisher_Code varchar(5),@.Mag_Code
> varchar(5),@.Postal_Code varchar(6),@.Common_Name varchar(30),
> @.Job_Title varchar(50),@.Company_Name varchar(30),@.Address_Line_1
> varchar(30),@.Address_Line_2 varchar(30),@.City varchar(30),
> @.State_Prov varchar(2),@.Country_Code varchar(2),@.Telephone
> varchar(11),@.Fax_Number varchar(11)
> --Variables to store Product record values
> DECLARE @.Service_Status varchar(1),@.Start_Issue datetime,@.Expire_Issue
> datetime,@.Num_Copies integer,@.Email_User_Name varchar(50),
> @.Current_Email_Address varchar(50),@.Email_Password varchar(50)
> --Variables to store Order record values
> DECLARE @.Order_Number varchar(20),@.Order_Status varchar(1),@.Order_Term
> integer,@.Order_Net_Value money,@.Source_Code varchar(2),@.Medium_Code
> varchar(2),
> @.Document_Key varchar(10),@.Setcode varchar(1),@.Orig_Start_Issue
> datetime,@.Order_Entry_Type varchar(5)
> --Variables to store Demographic record values
> DECLARE @.Version_Number varchar(10),@.Segment_Number integer ,@.Demo_Data
> varchar(1024)
> --variales to store newly created reg_visitor_id,account_id and order_id -
> for subscriptions not existing in Elogic Reg DB
> DECLARE @.Reg_Visitor_Id integer ,@.Account_Id integer ,@.Order_Id
> integer,@.Address_Id integer
> --variables to store values present in staging tables - For subscriptions
> already existing in Elogic Reg DB
> DECLARE @.eLogic_Reg_Visitor_Id integer ,@.eLogic_Account_Id integer
> ,@.eLogic_Order_Id integer,@.eLogic_Publication_Id integer
> --variables used for logging
> DECLARE @.Publication_Id integer,@.Pub_Code varchar(5),@.Target_Type
> varchar(100),@.Process_Type varchar(100),@.Summary_Count integer,
> @.Log_Summary_Id integer,@.Source_File_Name varchar(100)
> --Variables to store Donor's data
> DECLARE @.Donor_Company_Name varchar(30),@.Donor_Address_Line_1
> varchar(30),@.Donor_Address_Line_2 varchar(30),@.Donor_City varchar(30),
> @.Donor_State_Prov varchar(2),@.Donor_Country_Code
> varchar(2),@.Donor_Telephone varchar(11),@.Donor_Fax_Number varchar(11),
> @.Donor_Account_number varchar(20), @.Donor_Postal_Code varchar(6)
> --Variables to store CDS to Elogic Converted values
> DECLARE @.eLogic_Account_Status varchar(5),@.eLogic_Pay_Type
> varchar(5),@.eLogic_Auto_Renew bit,@.eLogic_Account_Type varchar(20)
> --Misc variables
> DECLARE @.SP_NAME varchar(50),@.Ret integer
> DECLARE @.Reg_Visitor_Product varchar(100)
> DECLARE @.Exec_Start_Time datetime
> DECLARE @.Promo_Code_Pos2 varchar(1)
> DECLARE @.Delivery_Type varchar(1)
> DECLARE @.Cnt int
>
> --Cursor to subscriptions stored in the staging tables
> DECLARE Subscriptions_Cursor CURSOR FOR
> SELECT nr.eLogic_reg_visitor_id,nr.eLogic_publication_id,nr.source_file_na
me,nr.account_number,nr.publisher_code,nr.mag_code,nr.postal_code,nr.common_
name,nr.job_title,nr.company_name,
> nr.address_line_1,nr.address_line_2,nr.city,nr.state_prov,nr.country_code
,nr.telephone,nr.fax_number,
> pr.eLogic_account_id,pr.service_status,pr.start_issue,pr.expire_issue,pr.
num_copies,pr.email_user_name,pr.email_password,pr.current_email_address,
> ord.eLogic_order_id,ord.order_number,ord.order_status,ord.order_term,ord.
order_net_value,ord.source_code,ord.medium_code,ord.document_key,
> ord.setcode,ord.orig_start_issue,ord.order_entry_type,
> dr.version_number,dr.segment_number,dr.demo_data,
> delivery_type
> FROM cds_name_record nr
> JOIN cds_product_record pr
> ON nr.account_number=pr.account_number
> AND nr.publisher_code=pr.publisher_code
> AND nr.mag_code=pr.mag_code
> JOIN cds_order_record ord
> ON ord.account_number=pr.account_number
> AND ord.publisher_code=pr.publisher_code
> AND ord.mag_code=pr.mag_code
> JOIN cds_demographic_record dr
> ON dr.account_number=pr.account_number
> AND dr.publisher_code=pr.publisher_code
> AND dr.mag_code = pr.mag_code
> JOIN publication_subscription ps
> ON ps.external_multi_mag_code = pr.publisher_code
> AND ps.code = pr.mag_code
> WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
> Get Only current address
> AND (ord.setcode='A' OR ord.setcode='C' OR ord.setcode='E')--Get only
> Non-Gift and Donee Orders
> AND ord.order_status='B' --Get only Base Orders
> AND dr.demo_status = 'B' --Get only Base Demo Records
> --AND nr.account_number <>'0010115103'
> ORDER BY CAST(nr.account_number AS int)
>
> --cursor to the reg_feed_detail_log table , used for populating the
> reg_feed_summary_log table
> DECLARE Summary_Cursor CURSOR FOR
> SELECT
> publication_id,pub_code,target_type,proc
ess_type,source_file_name,count(*)
as
> summary_count
> FROM reg_feed_detail_log
> GROUP BY publication_id,pub_code,target_type,proc
ess_type,source_file_name
> SET @.SP_NAME = OBJECT_NAME(@.@.PROCID)
> SET @.Exec_Start_Time = CURRENT_TIMESTAMP
> OPEN Subscriptions_Cursor
> FETCH NEXT FROM Subscriptions_Cursor
> INTO
> @.eLogic_Reg_Visitor_Id,@.eLogic_Publicat
ion_Id,@.Source_File_Name,@.Account_
Number ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
> @.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
> @.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
> @.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
> ,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
> @.Current_Email_Address,
> @.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
> ,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
> @.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
> @.Version_Number,@.Segment_Number,@.Demo_D
ata,@.Delivery_Type
>
> SET @.Cnt =1
> PRINT 'start'
> WHILE @.@.FETCH_STATUS = 0
> BEGIN --B1
> INSERT INTO Demo_data values ('inside while loop',null,null)
> INSERT INTO debug_table
> (eLogic_reg_visitor_id,eLogic_publicatio
n_id,account_number,publisher_code
,mag_code,order_status,set_code,delivery
_type,seq)
> VALUES (@.eLogic_Reg_Visitor_Id,@.eLogic_
publication_id,@.Account_Number
> ,@.Publisher_Code ,@.Mag_Code,@.Order_Status,@.SetCode,@.Deliv
ery_Type,@.Cnt)
> SELECT @.Reg_Visitor_Id = NULL,@.Account_Id = NULL,@.Order_Id =
> NULL,@.Address_Id = NULL,@.Reg_Visitor_Product = NULL
> SELECT @.Donor_Company_Name = NULL,@.Donor_Address_Line_1 =
> NULL,@.Donor_Address_Line_2 = NULL,@.Donor_City = NULL,
> @.Donor_State_Prov = NULL,@.Donor_Country_Code = NULL,@.Donor_Telephone =
> NULL,@.Donor_Fax_Number = NULL,
> @.Donor_Account_number = NULL
> SELECT @.eLogic_Account_Status = NULL,@.eLogic_Pay_Type =
> NULL,@.eLogic_Auto_Renew = NULL,@.eLogic_Account_Type = NULL
> SELECT @.Promo_Code_Pos2 = NULL
> IF ( @.SetCode = 'C' OR @.SetCode ='E') --SetCode 'C' and 'E' denote donee
> BEGIN --B2
> SELECT @.Donor_Account_number = nr.account_number,@.Donor_Postal_Code =
> nr.postal_code,@.Donor_Company_Name = nr.company_name,
> @.Donor_Address_Line_1 = nr.address_line_1,@.Donor_Address_Line_2 =
> nr.address_line_2,@.Donor_City = nr.city,
> @.Donor_State_Prov = nr.state_prov,@.Donor_Country_Code =
> nr.country_code,@.Donor_Telephone = nr.telephone,@.Donor_Fax_Number =
> nr.fax_number
> FROM cds_name_record nr
> JOIN cds_order_record ord
> ON ord.account_number=nr.account_number
> AND ord.publisher_code=nr.publisher_code
> AND ord.mag_code=nr.mag_code
> WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date -
-
> Get Only current address Name record
> AND (ord.setcode='B' OR ord.setcode='D')--Get only Donor Orders
> AND (ord.order_status='B' OR ord.order_status='D') --Get only Base Orde
r
> or Non-Subscibing Donor Order
> AND nr.publisher_code = @.Publisher_Code
> AND nr.mag_code = @.Mag_Code
> AND ord.order_number = @.Order_Number
> -- Note : Donor and Donee will have different account numbers, but same
> publisher code, mag code and order number
> END --END B2
>
> SET @.eLogic_Account_Status = CASE
> WHEN @.Service_Status = 'A' THEN 'A'
> WHEN @.Service_Status IN ( 'B','H','I') THEN 'C'
> WHEN @.Service_Status = 'C' THEN 'X'
> WHEN @.Service_Status IN ('D','E','F','G') THEN 'O'
> END
> SET @.eLogic_Pay_Type = CASE
> WHEN @.Order_Entry_Type IN ('A','B','C','D') THEN 'P'
> WHEN @.Order_Entry_Type IN ('L','M','U') THEN 'F'
> ELSE ''
> END
> SET @.eLogic_Auto_Renew= CASE
> WHEN ( SUBSTRING(@.Document_Key,1,1)= '#' OR @.Medium_Code = 'E') THEN
1
> ELSE 0
> END
> SET @.Promo_Code_Pos2 = SUBSTRING(@.Document_Key,2,1)
> SET @.eLogic_Account_Type=CASE
> WHEN @.Source_Code = 'CC' THEN
> CASE
> WHEN @.Promo_Code_Pos2 = 'T' THEN 'FREETRIAL'
> WHEN @.Promo_Code_Pos2 = 'E' THEN 'EMAILONLY'
> ELSE 'CONTROLLED'
> END
> WHEN @.Source_Code = 'CA' THEN 'COMP'
> ELSE 'PAID'
> END
>
> SELECT top 1 @.Reg_Visitor_Id = A.reg_visitor_id
> FROM account A
> JOIN publication_subscription PS
> ON A.pub_code = PS.code
> WHERE A.account_number = @.Account_Number
> AND PS.external_multi_mag_code = @.Publisher_Code
> --If a reg_visitor is not determined in the Staging tables or by looking
up
> the Account table , a new reg_visitor
> --is created
> IF ( @.eLogic_Reg_Visitor_Id IS NULL AND @.Reg_Visitor_Id IS NULL)
> BEGIN --B3
> --No Reg_Visitor corresponding to the mag subscription - mag subscriptio
n
> generated at CDS
> --Create a new reg_visitor and associated rows in Account,Account_Order
> and visitor_demographic
> SELECT @.Reg_Visitor_Product = master_brand
> FROM publication
> WHERE external_multi_mag_code=@.Publisher_Code
> --insert into reg_visior table
> EXEC @.Ret= dbo.upd_reg_visitor @.p_reg_visitor_id = @.Reg_Visitor_Id OUTP
UT,
> @.p_common_name = @.Common_Name,
> @.p_company_name = @.Company_Name,
> @.p_email = @.Current_Email_Address,
> @.p_encrypted_password = @.Email_Password,
> @.p_given_name = NULL,
> @.p_login_id = @.Email_User_Name,
> @.p_merged_visitor_id = NULL,
> @.p_middle_initial = NULL,
> @.p_middle_name = NULL,
> @.p_name_suffix = NULL,
> @.p_password = @.Email_Password,
> @.p_product = @.Reg_Visitor_Product,
> @.p_subproduct = NULL,
> @.p_professional_title = @.Job_Title,
> @.p_record_status = 1,
> @.p_registration_level = 1,
> @.p_salutation = NULL,
> @.p_sur_name = NULL,
> @.p_zip = NULL,
> @.p_country = 'TESTDTS'
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting into Reg_Visitor table!', 18, 2, @.SP_NAM
E)
> --ROLLBACK TRAN T1
> RETURN -2
> END
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id = SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'reg_visitor',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = NULL,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> --insert into address table
> EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
> @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_company_name = @.Company_Name,
> @.p_address_line_1 = @.Address_Line_1,
> @.p_address_line_2 = @.Address_Line_2,
> @.p_city = @.City,
> @.p_postal_code = @.Postal_Code,
> @.p_state_prov = @.State_Prov,
> @.p_country_code = @.Country_Code,
> @.p_phone = @.Telephone,
> @.p_fax = @.Fax_Number,
> @.p_address_type = 0 --shipping
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'address',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Address_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> IF (@.SETCODE = 'C' OR @.SETCODE = 'E') -- Donee subscription
> BEGIN
> --Insert the corresponding Donor Address
> EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
> @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_company_name = @.Donor_Company_Name,
> @.p_address_line_1 = @.Donor_Address_Line_1,
> @.p_address_line_2 = @.Donor_Address_Line_2,
> @.p_city = @.Donor_City,
> @.p_postal_code = @.Donor_Postal_Code,
> @.p_state_prov = @.Donor_State_Prov,
> @.p_country_code = @.Donor_Country_Code,
> @.p_phone = @.Donor_Telephone,
> @.p_fax = @.Donor_Fax_Number,
> @.p_address_type = 1 --Billing
> --IF @.Account_Number = '0010115103' OR @.Cnt =11214
> --INSERT INTO demo_data values ('inserted into address for donor')
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'address',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Address_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> --IF @.Account_Number = '0010115103' OR @.Cnt =11214
> --INSERT INTO demo_data values ('inserted into detail log for donor
> address')
> END
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting into Address table!', 18, 3, @.sp_name)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -3
> END
> --Insert into Account table
> EXEC @.Ret = dbo.set_account @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_account_id = @.Account_Id OUTPUT,
> @.p_account_number = @.Account_Number,
> @.p_pub_code = @.Mag_Code,
> @.p_account_type = @.eLogic_Account_type ,
> @.p_status = @.eLogic_Account_Status,
> @.p_supp_account_number = @.Donor_Account_Number -- If its a Non-Gift
> Order, NULL will be inserted for Supp_Account_Number
>
> IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Account_Id IS NULL)
> BEGIN
> RAISERROR('%s: Error inserting into Account table!', 18, 4, @.SP_NAME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -4
> END
> INSERT INTO Demo_data values ('completed inserting into account
> table',@.Reg_Visitor_id,@.Account_Number)
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'account',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Account_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> INSERT INTO Demo_data values ('completed inserting into detail log for
> account table',@.Reg_Visitor_id,@.Account_Number)
> --insert into account_order table
> EXEC @.Ret = dbo.set_account_order @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_account_id = @.Account_Id,
> @.p_order_id = @.Order_Id OUTPUT,
> @.p_term = @.Order_Term,
> @.p_term_unit = NULL,
> @.p_pay_type = @.eLogic_Pay_Type,
> @.p_net_amt = @.Order_Net_Value,
> @.p_quantity = @.Num_Copies,
> @.p_vendor_order_number = @.Order_Number,
> @.p_promo_response_key = @.Document_Key,
> @.p_number_of_installments=1,
> @.p_auto_renew =@.eLogic_Auto_Renew
> IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Order_Id IS NULL)
> BEGIN
> RAISERROR('%s: Error inserting into Account_Order table!', 18, 5, @.SP_N
AME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -5
> END
> INSERT INTO Demo_data values ('completed inserting into account_order
> table',@.Reg_Visitor_id,@.Account_Number)
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'account_order',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Account_Id ,
> @.P_Target_Key_3 = @.Order_Id,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> INSERT INTO Demo_data values ('completed logging to detail log table for
> account_order',@.Reg_Visitor_id,@.Account_
Number)
> --insert into visitor_demographics table only for Online Magazines ;
> Delivery Type W - Online, P- Print
> IF(@.Delivery_Type = 'W')
> BEGIN
> INSERT INTO Demo_data values ('calling demographcis
> sp',@.Reg_Visitor_id,@.Account_Number)
> EXEC @.Ret = Process_CDS_Demographics @.Reg_Visitor_Id = @.Reg_Visitor_Id
,
> @.Mag_Code = @.Mag_Code,
> @.Demo_Data = @.Demo_Data,
> @.Segment_Number = @.Segment_Number,
> @.Version_Number = @.Version_Number
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting/updating into Visitor_Demographics
> table!', 18, 6, @.SP_NAME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -6
> END
> END
> INSERT INTO Demo_data values ('DEBUG LINE
> HIT',@.Reg_Visitor_id,@.Account_Number)
>
> END --END B3
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('About to fetch next row- current row details
> -->',@.Reg_visitor_id,@.Account_number)
> FETCH NEXT FROM Subscriptions_Cursor
> INTO
> @.eLogic_Reg_Visitor_Id,@.eLogic_Publicat
ion_Id,@.Source_File_Name,@.Account_
Number ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
> @.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
> @.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
> @.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
> ,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
> @.Current_Email_Address,
> @.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
> ,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
> @.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
> @.Version_Number,@.Segment_Number,@.Demo_
Data,@.Delivery_Type
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('fetched next row',@.eLogic_Reg_Visitor_Id,@.Account_num
ber)
> SET @.Cnt = @.Cnt + 1
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('value of @.@.FETCHSTATUS =',@.@.Fetch_Status,@.Account_number)
> END --END B1
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
>
> "Alejandro Mesa" wrote:
>
Thursday, March 22, 2012
cursor and update
Hi,
I have a table t1 (part_id int, gen_code int) part_id is a primary key.
I have a cursor on t1 for part_id , I take part_id
go thro different tables , do calculations and update gen_code for that part_id in t1 . This process is very slow and I see waittype 'LATCH_EX' and 'CXPACKET' all the time in process info .
do I have to declare cursor for update? how does it diff from read_only cursor.
please reply how do I make this process faster.
thanks,
RamIt would be helpful if you woul dexplain your process in more detail. Otherwise, it's even for a guru difficult to look into a crystal ball.|||Update cursor may even slow down the process by locking rows
I have a table t1 (part_id int, gen_code int) part_id is a primary key.
I have a cursor on t1 for part_id , I take part_id
go thro different tables , do calculations and update gen_code for that part_id in t1 . This process is very slow and I see waittype 'LATCH_EX' and 'CXPACKET' all the time in process info .
do I have to declare cursor for update? how does it diff from read_only cursor.
please reply how do I make this process faster.
thanks,
RamIt would be helpful if you woul dexplain your process in more detail. Otherwise, it's even for a guru difficult to look into a crystal ball.|||Update cursor may even slow down the process by locking rows
Subscribe to:
Posts (Atom)