473,558 Members | 3,023 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Cursor problem

GAS
I have tried to figure this out for a couple of days now and I'm not
making much headway. I have a cursor that I can't get to work
properly. There are two cursors in the stored procedure. The first
accesses data in the current database and works fine. The second
accesses data in another database on the same server. Whenever I
fetch rows into the second cursor (other database) the variables I
fetch into remain NULL.

I have included excerpts from the stored procedure. The whole
procedure is about 300 lines long so I only included a little snippet.

Thanks for any help you can provide.

DECLARE @v_salesOrderLi ne char(3),
@v_endItemNumbe r char(25),
@v_endItemRev char(3),
@v_dueDate datetime,
@v_orderQty decimal(15,5),
@v_endItemPrice decimal(17,5)

DECLARE c_salesOrderLin eItems CURSOR
FOR SELECT fenumber,
fpartno,
fpartrev,
fduedate,
forderqty,
funetprice
FROM m2mdata05.dbo.s orels
WHERE fsono = @v_salesOrder

BEGIN

OPEN c_salesOrderLin eItems

FETCH NEXT FROM c_salesOrderLin eItems INTO @v_salesOrderLi ne,
@v_endItemNumbe r,
@v_endItemRev,
@v_dueDate,
@v_orderQty,
@v_endItemPrice
CLOSE c_salesOrderLin eItems

END
Jul 20 '05 #1
1 1544
GAS (gs*******@trin ityforge.com) writes:
I have tried to figure this out for a couple of days now and I'm not
making much headway. I have a cursor that I can't get to work
properly. There are two cursors in the stored procedure. The first
accesses data in the current database and works fine. The second
accesses data in another database on the same server. Whenever I
fetch rows into the second cursor (other database) the variables I
fetch into remain NULL.

I have included excerpts from the stored procedure. The whole
procedure is about 300 lines long so I only included a little snippet.


Which unfortunately does not tell me much. I can't even tell whether
you actually need the cursor. (Often I see examples on the newsgroups
where people use cursors where you easily can find a set-based statement
that solves the problem with not only less lines of code, but also
a dramatic reduce in execution time.) So it's better to post all
the code.

The only I can say here, is that this is my recommendation on how to
write a cursor loop:

DECLARE cur INSENSITIVE CURSOR FOR
SELECT col1 ...
FROM ...
IF @@error <> 0 BEGIN DEALLOCATE cur RETURN END

OPEN cur

WHILE 1 = 1
BEGIN
FETCH cur INTO @par1, ...
IF @@fetch_status <> 0
BREAK

-- Do stuff
END

DEALLOCATE cur

1) Use INSENSITIVE cursors. Default is keyset and they may work well,
or disastrously. INSENSIIVE copies the current rows into a worktable
and you are safe from surprises.

2) Check that cursors are not already open, because cursors are created
on process level by default.

3) Only one FETCH statement makes it easier to change if you add another
column to the SELECT statement. It's easy to forget that second
FETCH which may be 100 lines down.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
6093
by: DarthMacgyver | last post by:
Hello, I recently wrote a survey application. Each question is very similar. The first questions gives me a problem when there are multiple people taking the survey (The Database connection Timed out) I am using the Data Access Application Blocks as ASP.NET (using VB.NET) and SQL 2000. In there first question there can be up to 27...
4
4069
by: Oliver Stratmann | last post by:
Hello All! We have a cursor in an application which fetches some data out of a DB2-DB. Normally it all works kind of well. We inserted a routine at certain places in order to do some perfomance-monitoring . Now we got the problem, that the cursor closes at some point although not all the data has been fetched and then next fetch of the...
0
6231
by: Prashanth | last post by:
I am getting this error from BEA DB2 Driver in weblogic console. java.sql.SQLException: CURSOR C02 NOT IN A PREPARED STATE java.sql.SQLException: CURSOR C02 NOT IN A PREPARED STATE We keep running into this problem, frequently almost everyday. We figured what exactly to do when the problem happens but not the reason or the solution to...
1
2252
by: ilPostino | last post by:
I'm using this function to load a custom cursor from a .cur file; public static extern IntPtr LoadCursorFromFile(String str); It works great but if I popup a ContextMenu or Control like a ListView, the cursor resets to the system arrow. Even if I set the cursor property of each control to the one loaded it just ignores it. The...
5
14423
by: Vlad Simionescu | last post by:
Hello I'm trying to let my Windows Form application perform a lengthy operation while displaying a progress bar in a modal dialog window; the dialog has a cancel button. The dialog is displayed in the main thread while the work is done in a background thread. I managed to do it except for one minor problem: I want the cursor to be hourglass...
2
7859
by: Craig | last post by:
I'm trying to do this within a control I've created: Cursor.Current = Cursors.WaitCursor; During the initialization of the parent form, my control gets added to the form, and while the control is being intialized, I call a method within the control that sets the cursor to Cursors.WaitCursor, then processing continues. I want the Cursor to...
10
17313
by: Just Me | last post by:
Does Me.Cursor.Current=Cursors.WaitCursor set the current property of Me.Cursor to Cursors.WaitCursor And Me.Cursor.Current=Cursors.Default set the Me.Current property to something (default) stored in Me.Cursor. Or is Cursors.Default some process wide cursor shape? What is a correct statement?
12
30988
by: Lucky | last post by:
Hi guys! i want to create one cursor in the t-sql. the problem is i want to use stored procedure instead of select command in cursor. can anyone tell me how can i use stored procedure's o/p to create cursor? i'm using sql 2000 and .net 2.0 thanks,
6
11552
by: Tomek | last post by:
In my page I reload JavaScript. When it is reloading the cursor change to "wait" (hourglass). After javascript loaded it does not change back to the "hand" if cursor stay on hyperlinks. How can I turn off changing cursor to "wait" (hourglass) is javascript is reloading.
4
6954
by: mike | last post by:
I have the opportunity to rescue a project that uses a mouse to sense the relative position of a machine. The hardware is built...just needs to be programmed. Stop snickering!!! I didn't do it...I just gotta fix it. I need to make some calculations on the measurements and VB6 is my language. Yes, the system mouse will corrupt the...
0
7629
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7550
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8061
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7915
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6184
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3579
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2046
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1165
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
872
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.