Nesting Cursors
May 20, 2004
Hi, i have to nest 2 cursors together and frankly i'm lost.
Here's what I have so far
CREATE PROCEDURE SeeAllColumns
AS
SET NOCOUNT ON
DECLARE @strMessageVARCHAR(100)
DECLARE @strColumnVARCHAR(100)
DECLARE @strTableVARCHAR(100)
DECLARE @strCommandVARCHAR(250)
SELECT @strMessage = 'SELECT ALL TABLES'
PRINT @strMessage
DECLARE crsTables CURSOR FOR
SELECT
name AS strTable
FROM
sysobjects
WHERE
name LIKE 'T%'
OPEN crsTables
FETCH NEXT FROM crsTables INTO @strTable
WHILE (@@FETCH_STATUS = 0) BEGIN
SELECT @strMessage = 'SELECT ALL COLUMNS'
PRINT @strMessage
SELECT @strCommand = ' SELECT ' + @strColumn + ' FROM ' + @strTable
EXECUTE (@strCommand)
FETCH NEXT FROM crsTables INTO @strTable
END
DEALLOCATE crsTables
PRINT 'DONE'
What i need to do is select all columns from all tables, but none of the actual data within the columns, just the names. I just don't know where or how to insert the 2nd cursor.
Can someone help me?
Thanks!
View 3 Replies
ADVERTISEMENT
Jul 20, 2005
I have a stored procedure that calls some UDF User Defined Functions,the purpose of which is to create row strings out of numerous columnstrings for matching uniqueIDs.The problem is I need to join that SP with some other tables.The SP I have reads something like:mySPName@myUserID intSELECT myUniqueID, dbo.fn_myFunctionName(UniqueID) As myRunningStringFROM myTEMPTableNameGROUP BY myUniqueIDWHERE myTEMPTableName.UserID = @myUserIDI need to join that result with myTableName on myUniqueID such as:Select myTableName.myField1, myTableName.myField2,mySPName.myRunningStringFrom ...-- joining myTableName.myUniqueID = mySPName.myUniqueIDCan this be done?The reason I don't just do it with a View instead of an SP is that Ihave that parameter that must be passed to filter the records inmyTEMPTableName.Any help is appreciated.lqoh...the UDF looks like:Create Function dbo.fn_myFunctionName(@myUniqueID as int) returnsnvarchar(500)ASBEGINDECLARE @ret_value nvarchar(500)SET @ret_value=''SELECT @ret_value=@ret_value + ';' + myStringFROM myTEMPTableNameWHEREmyUniqueID =@myUniqueIDRETURN RIGHT(@ret_value,Len(@ret_value)-2)END
View 1 Replies
View Related
Mar 8, 2006
Hopefully someone can point me in the right direction, I've been searching on the net for the answer to this question and can't seem to come up with anything.
First, I'm using ASP.NET 2.0 and Visual Studio 2005 with a SQL Server 2000 backend.
My SQL database is relational and is used to store names, addresses, etc of other companies.
What I need to be able to do is have 2 transactions, one nested within the other. In pseudo-code:
BEGIN TRANSACTION1
BEGIN TRANSACTION2
INSERT INTO COMPANY TABLE
COMMIT TRANSACTION2 -OR- ROLLBACK TRANSACTION2
GET COMPANYID JUST ADDED
PERFORM REMAINING INSERTS
COMMIT TRANSACTION1 -OR- ROLLBACK TRANSACTION 1 & 2
Right now I have everything grouped into one VB.NET transaction, which doesn't work because the company is not actually added until the transaction reaches commit. Therefore, I can't retrieve the companyID halfway through.
Is what I'm trying to do even possible? Thanks in advance for the help!
View 2 Replies
View Related
Dec 27, 2005
Hi,
I am developing the security in a sql database, and i am doing so in a hierarchical way. In the white paper Row and cell security it says that we must add the child role as a menber of the parent role, but when you are in the role section you can not add roles as menbers of another role, so what i did was give the parent role ownership over the child role, it seemed logical.
So i build a admin
|
boss
|
worker
|
subworker
Nested Role
Now after giving users to those roles i was good to go and try my hierarchy security, i used the view that is in the white paper cell and row security which the code is the following:
SELECT ID, Label
FROM dbo.tblUniqueLabel WITH (NOLOCK)
WHERE (ID IN
(SELECT dbo.tblUniqueLabel.ID
FROM dbo.tblUniqueLabelMarking WITH (NOLOCK)
WHERE (CategoryID = 1) AND (IS_MEMBER(MarkingRoleName) = 1)))
But when i runned this view dont matter which users in which role ist always giving me an output of every single line in the table, the problem seem that he is giving me out the IS_MEMBER(MarkingRoleName) = 1 always as true like the user was in every single role.
What am i doing wrong?
Thanks.
View 1 Replies
View Related
Apr 6, 2008
Hi!
If I have two procedures that call each other, I run out of the 32 level maximum recursion.
Is there a workaround to rewrite that without hitting the 32 level maximum?
Any help is appreciated!
-Bahman
View 9 Replies
View Related
Jun 12, 2007
I'd like to have a chart with a category "group on" expression like this:
=Fields![Parameters!MyParameter.Value].Value
The above doesn't work. Is it because it can't be done, or because I don't have the right syntax?
View 1 Replies
View Related
Jul 23, 2004
hi I have another question to ever so helpful forum
I am trying to nest stored procedure but I guess it is not the way to do it as it does not work:
CREATE PROCEDURE dbo.GetSharesTransactionsbyDates
(
@Startdate as char(10),
@Enddate as char(10)
)
AS
dbo.GetSharesTransactionsData /*tryting to nest sproc*/
WHERE TRANS_DATE BETWEEN @Startdate and @Enddate
I get complain that dbo is incorrect syntax
ST.Proc. which I try to call call is basicly a select statement with no parameters:
ALTER PROCEDURE dbo.GetSharesTransactionsData
AS
SELECT TRANS_DATE, TYPE_DESCRIPTION, SHARE_SYMBOL, SHARES_QUANTITY,
ROUND(PRICE_PER_SHARE,2) AS PRICE_PER_SHARE, COMMISSION_VALUE,
STAMP_DUTY,
dbo.GetShareTransactionTotalValue(PRICE_PER_SHARE,STAMP_DUTY,COMMISSION_VALUE,
SHARES_QUANTITY,CASH_AMOUNT, TYPE_DESCRIPTION) AS TOTAL_VALUE,/*calling function*/
ACCOUNT_NAME
FROM SHARES_TRANSACTIONS
WHERE (TYPE_DESCRIPTION = 'Sell') OR
(TYPE_DESCRIPTION = 'Buy') OR
(TYPE_DESCRIPTION = 'Cash Divident')
ORDER BY TRANS_DATE
View 4 Replies
View Related
Dec 13, 2001
I am wondering if there is a way that we could do a nesting case statement in an SQL Query?
This is what I have now... FicaWages = CASE WHEN (UPR00900.FICAMWGS_1 + UPR00900.FICAMWGS_2) >= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0' WHEN UPR00400.SBJTSSEC = 0 THEN '0' ELSE (UPR30100.GRWGPRN - (SELECT SUM(UPR30300.UPRTRXAM) FROM UPR30300 WHERE UPR30300.EMPLOYID = UPR00100.EMPLOYID AND UPR30300.PAYROLCD LIKE '3%' AND AUCTRLCD = 'UPRCC00000007')) END
What I want:
FicaWages = CASE
WHEN
('Sql Statement') = 'GRM'
THEN
CASE
WHEN (UPR00900.FICAMWGS_1+UPR00900.FICAMWGS_2)
>= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0'
WHEN UPR00400.SBJTSSEC = 0 THEN '0'
ELSE (UPR30100.GRWGPRN - ('Sql Statement'))
END
ELSE
CASE
WHEN (UPR00900.FICAMWGS_1+UPR00900.FICAMWGS_2)
>= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0'
WHEN UPR00400.SBJTSSEC = 0 THEN '0'
ELSE (UPR30100.GRWGPRN - ('Sql Statement'))
END
END
I hope this is clear enough.
View 1 Replies
View Related
Jul 20, 2005
It's me, one more time. My last request didn't quite filter all my records,I have one more field I need to evaluate in the script.DESCR TYPE SELL StartDate EndDate Number65048 04 Price A 4/21/2004 4/26/2004 3545665048 06 Price C 4/20/2004 4/27/2004 3545965048 08 Price B 4/22/2004 4/28/2004 3455965049 04 Price A 4/19/2004 4/24/2004 3859565049 06 Price B 4/22/2004 4/25/2004 3859465049 06 Price C 4/20/2004 4/29/2004 3899865050 07 Price A 4/21/2004 4/25/2004 3811265050 06 Price B 4/18/2004 4/28/2004 3855065050 07 Price C 4/17/2004 4/29/2004 38110Descr, Type, Sell and Number are CHARStartDate and EndDate are SmallDatetimeI need a simple query that would display the records with the followingcriteria:#1. "Date I Enter" >= Startdate#2. "Date I Enter" <= Enddate#3. Highest TYPE for each DESCR#4. Highest NUMBER for each TYPE/DESCR (the tie-breaker)Results for ("Date I Enter" = 4/23/2004) should be:65048 08 Price B 4/22/2004 4/28/2004 3455965049 09 Price C 4/20/2004 4/29/2004 3899865050 07 Price A 4/21/2004 4/25/2004 38112I REALLY appreciate the help!Thanks!!-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----http://www.newsfeeds.com - The #1 Newsgroup Service in the World!-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
View 1 Replies
View Related
Oct 27, 2006
Dear all,
I am constantly running into this error when calling CLR functions from triggers (even when the triggers are CLR functions themselves): The context transaction which was active before entering user defined routine, trigger or aggregate "[name of CLR sp]" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting.
However, there is no cyclic nesting going on here. In fact, the error only appears when the CLR function is writing to the database. Only executing SELECT statements inside the statements doesn't freak out the SQL server (2005 SP1 in my case). I am not ending any transaction at all. This error shows up in many places: as exceptions from ASP.NET web pages, as errors in the msmerge_conflicts_info etc. Now I saw in the thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=457818&SiteID=1
..that this was supposedly a known bug in CLR. But in my case, it appears that I can't call a single CLR inside a trigger if the CLR is supposed to write something to the database, which is a pretty severe limitation of CLR imho.
I was trying to figure out if I can avoid this error by escaping the transaction context, I tried that inside CLR (Using New TransactionScope(TransactionScopeOption.Suppress)) but it didn't work. Also, calling COMMIT TRANSACTION before calling the CLR doesn't seem to solve the problem since it's not suppose to end the ambient transaction of the trigger according to BOL, and in many cases it doesn't seem wise to do so, e.g. if the trigger is fired in merge replication as in my case.
View 13 Replies
View Related
Apr 27, 2007
I have sort of a weird error when attempting to create a database in SQL 2005 (migrating from SQL 2000 to SQL 2005).
I am able to run the CREATE PROC script just fine in SQL 2000, but I get this error in SQL 2005.
Msg 191, Level 15, State 1, Procedure udf_JCMS_ConcurrencyCheck, Line 2518
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries
I can't easily past up the whole script(it's 2655 lines), but it basically takes the following form:
CREATE FUNCTION dbo.Ugly (
@PK1 int,
@PK2 int,
@PK3 int,
@TableID int,
@UpdateBy char(30),
@LastUpdate datetime ) RETURNS BIT
DECLARE @UpdatedStatus as bit
DECLARE @RecordCount as smallint
SET @RecordCount = 0
If @TabelID = 1-- Comment about TableName
BEGIN
SELECT
@RecordCount =count(UNIQUE_ID)
FROM
dbo.MyTable
WHERE
UNIQUE_ID = @PK1
ANDPROXY_ID = @PK2
ANDLTRIM(RTRIM(ISNULL(LAST_UPDATE_BY,'')))= LTRIM(RTRIM(ISNULL(@UpdatedBy,'')))
AND
(
isnull(LAST_UPDATE_DATE,'') = isnull(@UpdatedDt,'')
or
convert(datetime,convert(varchar(10),LAST_UPDATE_D ATE,101)
+ ' ' + convert(char(8), LAST_UPDATE_DATE,8)) = @UpdatedDt
)
END
If @TableID = 2 -- Comment about TableName
BEGIN
.
.
.
END
If @TableID = 3 -- Comment about TableName
BEGIN
.
.
.
END
...
If @TableID = 156 -- Comment about TableName
BEGIN
.
.
.
END
IF @RecordCount = 0
SET @UpdatedStatus = 1
ELSE
SET @UpdatedStatus = 0
RETURN @UpdatedStatus
First, THIS IS NOT MY CODE.
Second, it appears that they are trying to create a function to test if a record has been updated (I cut out a bit of that).
I don't really get why SQL 2005 throws an error here. Yes, the code is hideous and not something that I would write myself. But it's not like the function is calling itself recursively or anything.
Can anyone shed any light?
Regards,
hmscott
View 3 Replies
View Related
Dec 5, 2006
I was hoping someone could help out with this issue....I've got a report I created in Reporting Services 2000 SP2 where I am nesting table elements within the row and detail sections of the matrix. This allows me to have multiple items within my "row" data with column headings, and works perfectly fine within the designer, and viewer, and whenever I export it to any format other than Excel. Exporting to excel results in a "Specified cast is not valid" error.
To me this seems to be a bug in the Excel rendering extension, since it seems to be supported in RDL. Has anyone else run into this? Is there a fix to the renderer or a workaround that anyone is aware of?
I'd be happy to post the RDL if anyone is interested.
Thanks,
Casey
View 5 Replies
View Related
Aug 3, 2005
Hi all,
I have writen a Function which call's the same function it self. I'm getting the error as below.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can any one give me a solution for this problem I have attached the function also.
CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float)
RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float)
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID
END
DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID)
INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID)
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports
RETURN
END
View 4 Replies
View Related
May 29, 2002
Hello,
I am running this query
"delete from ims_domains where id=61"
and got the error
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
Please let me know what should be the reason?
Thanks,
Ravi
View 7 Replies
View Related
Dec 1, 2004
Hi,
I face this error when i try to run my store procedure.
The sample of store procedure as following:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_addUserAccess
with encryption
AS
SET NOCOUNT ON
DECLARE @COUNTER INT
SET @COUNTER = 0
DECLARE @i_compId INT
BEGIN
DECLARE C1 SCROLL CURSOR FOR
SELECT i_compId
FROM ltd_cms_company WHERE (i_owner = 176 or i_owner = 268) AND ti_recStatus = 1
END
OPEN C1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO ltd_cms_userAccess ( i_loginId, i_groupId, i_compId, ti_updComp, ti_updLog, ti_updAccess, ti_owner, ti_acctMgr, ti_updContact, ti_updEvent )
VALUES ( 124, 0, @i_compId, 1, 1, 1, 1, 1, 1, 1)
SET @COUNTER = @COUNTER + 1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId
END
CLOSE C1
DEALLOCATE C1
SET NOCOUNT OFF
anyone can help me identify this error?
Thanks
Regards,
Jojomay
View 1 Replies
View Related
Jan 9, 2006
Hi all, I get this message when trying to update a tabel i have whichhas nested hierarchies.The current hierarchies beginning from root = 1 are up to the level 5.Before going into details and sample data with all the sql queries andprocedures, this limitation from Microsoft for nested levels .. isthere any way or trick to increase the level in generic?
View 1 Replies
View Related
Oct 16, 2007
I have created a delete trigger in Table1 and Table2. Once I delete a certain record in Table1 it will also delete that record in Table2 or vice versa. But once i delete certain record either in Table1 or Table2 it will create an error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).". Can you help me on this?
View 4 Replies
View Related
Jul 16, 2006
Hello,
Can anyone direct me a good article that is about why we should try avoiding using cursors and what are the alternatives?
View 1 Replies
View Related
Feb 16, 2008
can we call cursors from asp.net as we can call stored procedures from asp.net.
View 1 Replies
View Related
Jan 7, 2005
Hi,
I'm writting a stored procedure to insert new rows into a table, but I need to loop through an exsiting table and create a new record for every row in the old table. So I think what I'm needing to use is a cursor, but I've never worked with cursors before, and I just want to check that I have the right idea on what they are used for and if what I have so far looks ok.
This what I have so far, the StoreFees table only has 5 rows:
DECLARE @StoreFee as smallmoney
DECLARE @StoreLineID as int
DECLARE MyStoreFees CURSOR
FORWARD_ONLY
FOR
SELECT LineId, StoreFee FROM StoreFees ORDER BY StoreFee
OPEN MyStoreFees
FETCH NEXT FROM MyStoreFees
INTO @StoreLineId, @StoreFee
--Do my Inserts into other tables
INSERT INTO OtherTable (...,...,.., @StoreFee)
...
...
-- Done working with that row
CLOSE MyStoreFees
View 5 Replies
View Related
Feb 28, 2005
I am trying to pull the data via fetching rows into a variable.
Begin
Fetch Next Into @temp...
Select @MainVariable = @MainVariable + @temp < @temp doesn't refresh with next fetch)
Print @temp < this works fine and returns the value for each fetch
End
I can print @temp and the data returns fine, but when I try to cancantenate with the above select, it doesn't refresh @temp wit the column in the table.
Any ideas?
DotNetNow
View 5 Replies
View Related
Jun 19, 2001
I have two procedures. One works well, and the other has a small glitch I cannot figure out. I have placed >>>> at the place where the problem is occuring.
The first procedure, which is working great, is applying a stored procedure to many servers (remote procedure call), but is also polling the local server via a linked server connection. This way, all servers are polled equally.
The second procedure is actually using a SELECT statment to query a system table. This procedure works well on all servers except the local one. I get this error message:
Server: Msg 3910, Level 16, State 1, Line 1
Transaction context in use by another session.
[OLE/DB provider returned message: Unspecified error]
There seems to be a connection issue. Can someone help me work around this?
Thank you,
Neal
FIRST PROCEDURE (this one works perfectly):
truncate table dbidname
CREATE TABLE ##dbtemp ( dbname VarChar(50),
dbsize varchar (25),
dbownervarchar(50),
dbid smallint,
createdate datetime,
statusvarchar(75) )
declare@servernamevarchar(50)
declare dbupdate cursor
global
for
select servername from sqlservers where status = 'a'
open dbupdate
fetch next from dbupdate into @servername
while @@fetch_status = 0
begin
print @servername
EXEC sp_addlinkedserver @servername, 'SQL Server'
>>>INSERT INTO ##dbtemp
>>>exec (@servername + '.master..sp_helpdb')
alter table dbidname alter column sqlservers_id int null
insert into dbidname (dbsystemid, dbname)
select dbid, dbname from ##dbtemp
update dbidname set sqlservers_id = sqlservers.sqlservers_id from sqlservers where @servername = sqlservers.servername and
dbidname.sqlservers_id is null
update dbidname set whenupdate = getdate()
alter table dbidname alter column sqlservers_id int not null
exec sp_dropserver @servername
delete ##dbtemp
fetch next from dbupdate into @servername
end
close dbupdate
deallocate dbupdate
drop table ##dbtemp
exec spal_db_files_update
SECOND PROCEDURE (see >>>> to note problem area):
truncate table dbidname
CREATE TABLE ##dbtemp ( dbname VarChar(50),
dbsize varchar (25),
dbownervarchar(50),
dbid smallint,
createdate datetime,
statusvarchar(75) )
declare@servernamevarchar(50)
declare dbupdate cursor
global
for
select servername from sqlservers where status = 'a'
open dbupdate
fetch next from dbupdate into @servername
while @@fetch_status = 0
begin
print @servername
EXEC sp_addlinkedserver @servername, 'SQL Server'
>>>>INSERT INTO ##dbtemp
>>>>exec (@servername + '.master..sp_helpdb')
alter table dbidname alter column sqlservers_id int null
insert into dbidname (dbsystemid, dbname)
select dbid, dbname from ##dbtemp
update dbidname set sqlservers_id = sqlservers.sqlservers_id from sqlservers where @servername = sqlservers.servername and
dbidname.sqlservers_id is null
update dbidname set whenupdate = getdate()
alter table dbidname alter column sqlservers_id int not null
exec sp_dropserver @servername
delete ##dbtemp
fetch next from dbupdate into @servername
end
close dbupdate
deallocate dbupdate
drop table ##dbtemp
View 1 Replies
View Related
Nov 28, 2000
I am receiving this error when running my cursor:
Error Messages...
Server: Msg 16933, Level 16, State 1, Line 0
The cursor does not include the table being modified.
The statement has been terminated.
Server: Msg 16933, Level 16, State 1, Line 5 (this message repeats)...
The cursor does not include the table being modified.
The statement has been terminated.
query:
declare cursor_test CURSOR for
select emp_ssn, effective_date1 from temp_employee_benefit_load
open cursor_test
declare @ssn char(9), @process_date char(8)
fetch next from cursor_test into @ssn, @process_date
while (@@fetch_status=0)
update test_cursor
set ssn = @ssn, process_date = @process_date
where current of cursor_test
fetch next from cursor_test into @ssn, @process_date
close cursor_test
deallocate cursor_test
Any help is appreciated:
Thanks,
View 3 Replies
View Related
Sep 7, 2000
I have stored procedure in that I am using a cursor to fetch the row by row.
cursor is fetching 75000 records so that the procedure is taking long time.
Is there any way to replace the cursor to fetch the records row by row.
He needed.
Ranch
View 1 Replies
View Related
Apr 14, 2000
Please help resolve a problem (and debate!).
We have an app using VB with Access as the front end to SQL. SQL statments were built and sent
directly to the server obtaining set-oriented results. Response was fair.
We have a new app developed by an "expert" using VB & Interdev that was to be ported for use on
the internet. The app was designed using the same logic to build the SQL statements, but it is also
using cursors to retrieve all data. RESPONSE IS TERRIBLE!
The designer says that to access SQL over the Web, cursor use is a MUST!
True? Not True?
If true, and I am stuck with an app using cursors, any basic suggestions on where to look to improve
response time?
Thanks for any input.
FJ
View 1 Replies
View Related
Apr 23, 1999
Hi,
I am using SQL Server 6.5 and I have a VB routine that updates a field in a tble so it can be used in a primary key. This is run after input of data using bcp. I have noticed on several posts re: using cursors to move through a recordset and was wondering if I could use this functionality to replace my VB routine. I checked the archives and found several messages but I am not sure where to start. My VB routine is:
I first would query the data to return all rows where the value of R08SegmentValue= Null. Then I move through the resultset changing the value of the field R08Segments.
***********************Start of VB Code****************
'Set initial value for sTReportHeaderIdx
With rs1
sTReportHeaderIdx = !ReportIdx & !HeaderIdx
sPReportHeaderIdx = sTReportHeaderIdx
End With
bCount = 1
'loop through rs and when streportheaderidx changes reset bCount to 1
'otherwise increment bcount by 1 and write to field R08SegmentValue
Do While rs1.EOF = False And blnContinue = True
DoEvents
Do While sPReportHeaderIdx = sTReportHeaderIdx
With rs1
.Edit
!R08SegmentValue = bCount
.Update
bCount = bCount + 1
sPReportHeaderIdx = sTReportHeaderIdx
.MoveNext
If Not rs1.EOF Then sTReportHeaderIdx = !ReportIdx & !HeaderIdx
End With
Loop
sPReportHeaderIdx = sTReportHeaderIdx
bCount = 1
Loop
In the end my data should look like
ReportIdx HeaderIdx R08SegmentValue
1 1 1
1 1 2
2 1 1
2 1 2
2 1 3
3 1 1
3 1 2
3 1 3
1 2 1
1 2 2
1 2 3
etc..
Can this be done via an sp? How would I go about writing something like this?
Thanks in advance...any assistance you could provide would be appreciated!
Kevin
View 1 Replies
View Related
Aug 6, 2002
I hope someone can help me with this, as BOL didn't get me the answer I am looking for.
I want to write an update step that will transform the following:
1 abc
1 def
2 lmn
3 rst
4 tuv
4 xyz
4 jkl
5 pqr
into this:
1 abc,def
2 lmn
3 rst
4 tuv,xyz,jkl
5 pqr
In other words, I want to concatenate multiple instances into 1 record per row, but I am not familiar with working with cursors or loops.
Thanks in advance!
bv
View 1 Replies
View Related
Nov 16, 1998
According go textbooks and T-SQL developers experience - using cursors is not recommended, more over they say: avoid cursors where it's possible.
Could someone please recommend any other way to go through the recordset(resultset) forward and backward to perform some search or calculations, if there are a specific requirement for not using front-end tools such as VB or MS Access(please don't ask why), other words - all the work must be performed in T-SQL stored procedure.
Thanks
View 3 Replies
View Related
Feb 24, 2008
Hi
I defined a cursor and executed it...but now i exactly forgot what select statement i had run in the cursor..(forgot the columns that i am extracting )
how do I view the contents of the cursor ?
View 1 Replies
View Related
Aug 25, 2004
I have a stored proc that merges records from an undeduped table to a deduped table. It is running really slowly. Merging 70 million records against a deduped 70 million is taking 115 hours and counting on decent hardware. Very slow.
I suspect there is significant room for optimization here. Some of my ideas:
- Write/update back to the cursor rather than executing separate UPDATE statements.
- Try a dynamic cursor instead of a READ ONLY cursor.
- Dump new elements to a separate table or a file and then do a single large INSERT.
Anyone else think any of these ideas will work? Can anyone think of something better?
BTW, I've tried to replaced the procedural cursor code with set based UPDATES/INSERTS but the short version of the story is that that route just didn't pan out. I know that is very common optimization advice.
I've made minor simplifications to the code:
- Took out code to handle last line
- Took out progress logging code
- Removed some DECLARE statements. These are needed to run but it should be obvious what they were supposed to be.
View 10 Replies
View Related
Aug 27, 2004
Trying to understand cursors a little better, found this in one of the dbs I inherited. Just trying ot figure out why they put it there cause no one else knows anything about it.
DECLARE [TM #] CURSOR
FOR SELECT * FROM [2004 TERMS];
View 10 Replies
View Related
Jan 14, 2005
nevermind forget I asked thanks anyways
View 11 Replies
View Related
Feb 10, 2005
These guys I work with have some sql scripts they run over night and they bog down the server and the machine will be gummed up in the morning etc..
Well, I finally looked at this processing and the culprit is cursors. And cursors within cursors. I would like to just get some opinions about what would be more processor efficient so I can send my boss a link to this thread.
Using a cursors to pull records and update them.
vs
Create script using a scripting language that pulls the records through ADO, loops through them and performs updates as necessary using update statements and the like.
Be nice. I have to work with these guys.
View 9 Replies
View Related