拍拍尘土

author subject: Problem quering v_workflow on SQL 2000
George M. Menegakis
  May 5, 2005 09:48:55 GMT

--------------------------------------------------------------------------------
Hello,

I was trying to query v_workflow (as I understand is the view used for producing Service Today views on client) but I get this error:

"Server: Msg 1540, Level 16, State 1, Line 1
Cannot sort a row of size 8852, which is greater than the allowable maximum of 8094."

The query I used is

<<
SELECT "v_workflow"."item_type", "v_workflow"."actual_finish", "v_workflow"."actual_duration", "v_workflow"."id", "v_workflow"."actual_start", "v_workflow"."to_workgroup_name", "v_workflow"."to_person_name", "IFC_ENTITIES"."ENT_NAME"
FROM "sd_userDB"."dbo"."v_workflow" "v_workflow" INNER JOIN "sd_userDB"."dbo"."IFC_ENTITIES" "IFC_ENTITIES" ON "v_workflow"."item_type"="IFC_ENTITIES"."ENT_OID"
WHERE ("IFC_ENTITIES"."ENT_NAME"=N'Change' OR "IFC_ENTITIES"."ENT_NAME"=N'Work order' OR "IFC_ENTITIES"."ENT_NAME"=N'Service call')
>>

The same happens for:

WHERE ("IFC_ENTITIES"."ENT_NAME"=N'Service Call')

or

WHERE ("IFC_ENTITIES"."ENT_NAME"=N'Change' OR "IFC_ENTITIES"."ENT_NAME"=N'Work order')

Does anybody has any idea ?
Note: If you are the author of this question and wish to assign points to any of the answers, please login first.For more information on assigning points ,click here

 
Sort Answers By: Date or Points
 
 
George M. Menegakis May 5, 2005 11:04:51 GMT N/A: Question Author

--------------------------------------------------------------------------------
Could somebody , using SQL server, please execute query "SELECT COUNT (*) from dbo.v_workflow" and tell me if executed ok?

I got "Server: Msg 1540, Level 16, State 1, Line 1
Cannot sort a row of size 8852, which is greater than the allowable maximum of 8094."

I checked client and Service Today returns incorect nubmer of items for last month. Something is defenately wrong here.
Oguz Kutlu Asik May 6, 2005 00:51:24 GMT 7 pts

--------------------------------------------------------------------------------
Hi George,

I executed "SELECT COUNT (*) from dbo.v_workflow" on a demo data loaded system, and it returns me 111, which means executed without problem. I executed on another, and it returns me 2072, which is ok.

The query

SELECT "v_workflow"."item_type", "v_workflow"."actual_finish", "v_workflow"."actual_duration", "v_workflow"."id", "v_workflow"."actual_start", "v_workflow"."to_workgroup_name", "v_workflow"."to_person_name", "IFC_ENTITIES"."ENT_NAME"
FROM "sd_userDB"."dbo"."v_workflow" "v_workflow" INNER JOIN "sd_userDB"."dbo"."IFC_ENTITIES" "IFC_ENTITIES" ON "v_workflow"."item_type"="IFC_ENTITIES"."ENT_OID"
WHERE ("IFC_ENTITIES"."ENT_NAME"=N'Change' OR "IFC_ENTITIES"."ENT_NAME"=N'Work order' OR "IFC_ENTITIES"."ENT_NAME"=N'Service call')

runs also without problem.


I don't think it's related to your query, it's the view's query. May be you have a row(s) which size is greater than 8094, then v_workflow view generates that error.
George M. Menegakis May 6, 2005 04:32:15 GMT N/A: Question Author

--------------------------------------------------------------------------------
Well I found the problem and opened a case to HP. This view contains information and information is 4000 chars field. So, if for an item the information is long enough the above error will be generated since the row size limit for MS SQL is 8094 bytes.

Open a test service call and fill the information field. Now alter the view of "Service Today" to display as many fields as possible, including information.

Chances are that the Service Today view (as used in client) will generate an error.
Jan Pavelka May 6, 2005 07:36:16 GMT 3 pts

--------------------------------------------------------------------------------
Hi George,

have you tried to re-generate all views in Adminstrator console in System panel/reporting?

After some customizations we had to do it because of some inconsistencies in db views. I'm not sure if this is your case (informatin field is standardized).

BR
Jan
George M. Menegakis May 6, 2005 10:07:34 GMT N/A: Question Author

--------------------------------------------------------------------------------
Unfortunately it is a known issue (ITSM006598) and it is not going to be fixed.

http://openview.hp.com/ecare/getsupportdoc?docid=ITSM006598