when Rowsize is very large - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

when Rowsize is very large

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2005-04-28 : 05:43:35

Hi guys

i have a dynamic query building function in which the user can select any no of columns when i add order by to the query i get the following error.

Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint.

tried to search to find out on hints and robust plan not able to find any.

help me out ..........

======================================
Ask to your self before u ask someone

nr
SQLTeam MVY

12543 Posts

Posted - 2005-04-28 : 06:05:03

Have a look at the option clause.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2005-04-28 : 07:46:26

thanks nr

seen option clause and but was not able to make out as it did not had any example. if u can give a sample that will be gr8.

======================================
Ask to your self before u ask someone

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2005-04-28 : 08:47:37

was able to find it..

this is how we need to use it
select * from emp OPTION(ROBUST PLAN)

work very well in Query Analyzer but when used in ASP page it does not...

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint.

======================================
Ask to your self before u ask someone

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-04-28 : 08:51:39

Recheck the data you are returning. Are you returning the MINIMUM amount of data? If you are using SELECT *, i dont think you are returning the MINIMAL amount.

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2005-04-29 : 01:15:53

I have a simple selected list where in i have sereval joins. but i have a field of size varchar(8000)
here goes my query.

SELECT (rtrim(CCM.TICKER_ID) +'-'+ CTM.TICKET_TYPE +'/'+ CAST(CTM.TICKET_ID as varchar)) AS TICKETID,CCM.Ticker_id,CTM.USERID,CPM.Prod_NAME,CTM.SUBJECT,CTS.Date_of_report,CTS.ISSUE_DETAILS,GPM.ITEM_DESC,CSM.Status_Value,CCCTM.CFD_Issue_Type_Category,CCCTM.CFD_Phase,CCCTM.CFD_Originator_Email_Address,CCCTM.CFD_Internal_Ticket_ID FROM TICKET_SOLUTIONS CTS LEFT JOIN PICKLIST_MASTER GPM ON CTS.PRIORITY=GPM.ITEM_ID LEFT JOIN PICKLIST_MASTER GPMRM ON CTS.REPORTING_MODE=GPMRM.ITEM_ID , TICKET_MASTER CTM LEFT JOIN CFD_TICKET_MASTER CCTM ON CCTM.TICKET_ID = CTM.TICKET_ID LEFT JOIN CFD_CUSTOMER_TICKET_MASTER CCCTM ON CCCTM.TICKET_ID = CTM.TICKET_ID LEFT JOIN STATUS_MASTER CSM ON CTM.CUR_STATUS=CSM.STATUS_ID LEFT JOIN STATUS_MASTER CSSM ON CTM.CUR_SUB_STATUS=CSSM.STATUS_ID LEFT JOIN CUSTOMER_MASTER CCM ON CTM.CUSTOMER_ID = CCM.CUSTOMER_ID LEFT JOIN CFD_CUSTOMER_MASTER CCCM ON CCCM.CUSTOMER_ID = CCM.CUSTOMER_ID LEFT JOIN MKT_PROD_MASTER CPM ON CTM.PRODUCT_ID = CPM.PROD_ID LEFT JOIN CATEGORY_MASTER CATM ON CTM.CATEGORY_ID = CATM.CATEGORY_ID LEFT JOIN ISSUE_TYPE_MASTER ISSM ON CTM.ISSUE_TYPE_ID = ISSM.ISSUE_TYPE_ID LEFT JOIN CAD_USER_MASTER CUM ON CTM.CURRENT_OWNER = CUM.USERID LEFT JOIN CAD_ROLE_MASTER CRM ON CUM.ROLE_ID = CRM.ROLE_ID LEFT JOIN CUSTOMER_PRODUCT CP ON CTM.CUSTOMERPRODUCT_ID=CP.CUSTOMERPRODUCT_ID WHERE 1 = 1 AND CTM.TICKET_ID=CTS.TICKET_ID AND CTS.DATE_OF_REPORT=(SELECT MAX(DATE_OF_REPORT) FROM TICKET_SOLUTIONS WHERE TICKET_ID=CTM.TICKET_ID) AND CCM.CUSTOMER_ID IN (SELECT DISTINCT CUSTOMER_ID FROM USER_CUSTOMER_MAP ) AND CTM.CUSTOMER_ID=CCM.CUSTOMER_ID AND CTM.CUSTOMER_ID IN ( 100023) AND CTM.CUR_STATUS IN (2, 3, 5, 24) AND CTM.TICKET_TYPE IN ('ISS') Order By CTM.TICKET_ID

i cannot create a view as thigns are in live. suggest me a simple way to handle it

======================================
Ask to your self before u ask someone

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2005-05-02 : 05:18:12

Guys help me out...........

======================================
Ask to your self before u ask someone

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-03 : 10:52:16

Your field that is Varchar 8000. Check the DB and see what the actual max size used is in that field. Perhaps you could trim it down.

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2005-05-04 : 09:42:31

Hi Don

i have varchar(8000) live in production and have data of 7900 chars. no way i can trim down.

======================================
Ask to your self before u ask someone

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-04 : 09:51:38

then you either change that column to text (not really advisable)
or have another table with id and only your varchar(8000) column
that links to the original table so you don't go over the limit.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources