Service Desk Practitioners Forum
cancel

Problem quering v_workflow on SQL 2000

Highlighted
George M. Meneg
Acclaimed Contributor.

Problem quering v_workflow on SQL 2000

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 ?
menes fhtagn
6 REPLIES
George M. Meneg
Acclaimed Contributor.

Re: Problem quering v_workflow on SQL 2000

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.
menes fhtagn
Oguz Kutlu Asi
Outstanding Contributor.

Re: Problem quering v_workflow on SQL 2000

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.
What's right is right, whether or not God exists
George M. Meneg
Acclaimed Contributor.

Re: Problem quering v_workflow on SQL 2000

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.
menes fhtagn
Jan Pavelka
Outstanding Contributor.

Re: Problem quering v_workflow on SQL 2000

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. Meneg
Acclaimed Contributor.

Re: Problem quering v_workflow on SQL 2000

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

http://openview.hp.com/ecare/getsupportdoc?docid=ITSM006598
menes fhtagn
George M. Meneg
Acclaimed Contributor.

Re: Problem quering v_workflow on SQL 2000

We have to live with this restriction if sd_userDB resides on MS SQL.

The only solution is to create a different view for reporting purposes only.
menes fhtagn