January 20, 2005 at 5:56 am
Could anyone please throw some light on a problem I have.
I am not a programmer, I do not understand the code I am seeing, but I am in Tech support trying to figure out what is happening.
We have a bespoke product that stores information into a Microsoft SLQ 2000 Database.
We believe the problem is with our product but we need clarification on how the code is working.
We have been using SQL profiler to watch activity through the database and the question I has is:-
The code below represents, as I believe the way a cursor opens and closes.
CursorOpen 180150089
RPC:Completed declare @P1 int
set @P1=180150089
declare @P2 int
set @P2=2
declare @P3 int
set @P3=4
declare @P4 int
set @P4=-1
exec sp_cursoropen @P1 output, N'SELECT ActionedDate,ActionedTime FROM TempAdjust WHERE InternalID=''1933018566'' AND ActionedDate = ''20050119'' AND USED = ''1'' AND NewTotalHours > ''0'' ORDER BY ActionedDate DESC', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
RPC:Starting exec sp_cursorfetch 180150089, 2, 0, 1
RPC:Completed exec sp_cursorfetch 180150089, 2, 0, 1
RPC:Starting exec sp_cursorclose 180150089
CursorClose 180150089
RPC:Completed exec sp_cursorclose 180150089
The code below we think is where the problem is, in the bottom line is says "exec sp_cursorfetch" after the "CursorClose", where the above code says "exec sp_cursorclose" after the "CursorClose"
CursorOpen 180150022
RPC:Completed declare @P1 int
set @P1=180150022
declare @P2 int
set @P2=8
declare @P3 int
set @P3=1
declare @P4 int
set @P4=1
exec sp_cursoropen @P1 output, N'SELECT @@IDENTITY', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
RPC:Starting exec sp_cursorfetch 180150022, 2, 0, 1
RPC:Completed exec sp_cursorfetch 180150022, 2, 0, 1
RPC:Starting exec sp_cursorfetch 180150022, 2, 0, 1
CursorClose 180150022
RPC:Completed exec sp_cursorfetch 180150022, 2, 0, 1
We believe that the above code is leaving a User Connection open to the SQL database, we can have up to 11000 user connections open at the same time, this uses up memory and the PC the application is running on.
can anyone confirm the above code to be correct or incorrect.
January 24, 2005 at 8:00 am
This was removed by the editor as SPAM
January 26, 2005 at 2:23 pm
January 28, 2005 at 1:57 am
OK, can anyone verify this.
We have now set up the same database on a different PC with 2 hard drives (Databases installed on the second drive.)
Although our test has been over 48 hours, SQL seems to be handling the avalable memory on the server better than when only 1 hard drive was being used.
Does having two physical hard drives affect memory usage ????
February 10, 2005 at 1:09 pm
The code above involves (only) setting up, executing, and then returning records from a query. It does not indicate the user logged out...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply