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

Nesting A SP Within Another SP?

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

Nesting SQL Transactions

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

Role Nesting

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

'un-nesting' Procedures

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

Nesting Parameters

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

Nesting Stored Procedure

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

Nesting Case Statements...

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

SQL Query- Confused With Nesting...

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

Strict Transaction Nesting Bug With CLR

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

SQL 2000 -&> SQL 2005 Nesting Error

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

Excel Export Error In RS200SP2 When Nesting Tables Within Matirx

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

Urgent : Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded

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

Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded (limit 32)

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

Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded (limit 32

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

Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded (limit 32)

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

Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded (limit 32).

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

Using Cursors

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

Using Cursors From Asp.net

Feb 16, 2008

can we call cursors from asp.net as we can call stored procedures from asp.net.
 

View 1 Replies View Related

Cursors

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

Cursors

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

Cursors

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

Cursors

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

Cursors?????????

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

Cursors

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

How To Use Cursors

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

Using Cursors

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

Cursors - Use Them Or Not???

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

Cursors

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

T-SQL Cursors?

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

Cursors

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

Cursors

Jan 14, 2005

nevermind forget I asked thanks anyways

View 11 Replies View Related

Cursors Are Bad

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







Copyrights 2005-15 www.BigResource.com, All rights reserved