November 2, 2005 at 6:49 am
Hi every body
from frant-end hitting DB(MS-SQL SERVER),and profiler to view what queri hitting DB
in profiler it showing that
declare @P1 int
set @P1=180150000
declare @P2 int
set @P2=8
declare @P3 int
set @P3=1
declare @P4 int
set @P4=8
exec sp_cursoropen @P1 output, N'SELECT WORKORDER.*, DETAIL.*, LOCATION.*, CUSTOMER.*, location.zip as cust_zip, FIRSTNAME,LASTNAME, BusinessUnitDesc,OrgDesc,OrgName FROM WorkOrder, Detail, Location, Customer, mfuser, BUSINESSUNIT BU where WorkOrder.FSRUSERUID = mfuser.Useruid and WORKORDER.FSRBUID = BU.BUID AND WorkOrder.WorkOrderUID = Detail.WorkOrderUID and WorkOrder.WorkOrderUID = Location.WorkOrderUID and WorkOrder.WorkOrderUID = Customer.WorkOrderUID and WorkOrder.FSRUSERUID IS NOT NULL and WORKORDER.FSRBUID IN (''BANG'') AND SCHEDULEDATE = ''2005-11-02'' AND WorkOrder.JobStatus in (''CP'',''JC'',''XO'',''ND'') order by WorkOrder.FSRBUID, WorkOrder.fsrLOGINID, Customer.accountID, WorkOrder.ScheduleDate, WorkOrder.JobTypeDesc ', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
exec sp_cursorfetch 180150000, 16, 1, 1
declare @P1 int
set @P1=1
declare @P2 int
set @P2=8
exec sp_cursorfetch 180150000, 256, @P1 output, @P2 output
select @P1, @P2
declare @P1 int
set @P1=180150001
declare @P2 int
set @P2=1
declare @P3 int
set @P3=1
declare @P4 int
set @P4=0
exec sp_cursoropen @P1 output, N'select * from payment where WorkorderUID= 1427 Order by paymenttype ', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
exec sp_cursorfetch 180150001, 2, 0, 1
exec sp_cursorfetch 180150000, 16, 2, 1
declare @P1 int
set @P1=2
declare @P2 int
set @P2=8
exec sp_cursorfetch 180150000, 256, @P1 output, @P2 output
select @P1, @P2
declare @P1 int
set @P1=180150002
declare @P2 int
set @P2=1
declare @P3 int
set @P3=1
declare @P4 int
set @P4=1
exec sp_cursoropen @P1 output, N'select * from payment where WorkorderUID= 1426 Order by paymenttype ', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
exec sp_cursorfetch 180150002, 2, 0, 1
exec sp_cursorfetch 180150002, 2, 0, 1
exec sp_cursorclose 180150000
It is fine but iam able to view data in query analyzer
but in frant-end iam getting problem
Error Number: 3021
Error Description: Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record
November 2, 2005 at 7:07 am
1)Where exactly you have that BOF EOF is true?
2)Try to name your field instead of using .*. This prevents returning unwanted columns, being more clear of the fields requested.
2)Try specifying the owner of the object (like dbo.)
If the account VB used is different to the one you used in query analyzer it might querying the wrong table like myself.... instead of dbo....
3)For readability and small performance gain, use joins to restrict instead of the where clause
SELECT WORKORDER.* /*TODO fill in field names*/
, DETAIL.*
, LOCATION.*
, CUSTOMER.*
, location.zip as cust_zip
, FIRSTNAME,LASTNAME
, BusinessUnitDesc,OrgDesc,OrgName
FROM
dbo.WorkOrder WorkOrder
inner join dbo.mfuser mfuser
on WorkOrder.FSRUSERUID = mfuser.Useruid
AND WORKORDER.FSRBUID IN (''BANG'')
inner join dbo.BUSINESSUNIT BU
on WORKORDER.FSRBUID = BU.BUID
inner join dbo.Detail Detail
on WorkOrder.WorkOrderUID = Detail.WorkOrderUID
inner join dbo.location location
on WorkOrder.WorkOrderUID = Location.WorkOrderUID
inner join dbo.Customer Customer
on WorkOrder.WorkOrderUID = Customer.WorkOrderUID
where
/*WorkOrder.FSRUSERUID IS NOT NULL cant be NULL with = because NULL=NULL returns unknown
http://www.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp
*/
WorkOrder.JobStatus in (''CP'',''JC'',''XO'',''ND'')
AND WorkOrder.SCHEDULEDATE = ''2005-11-02''
order by WorkOrder.FSRBUID, WorkOrder.fsrLOGINID
, Customer.accountID, WorkOrder.ScheduleDate, WorkOrder.JobTypeDesc
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply