Query Problems - Server Configuration?

Hi All,
I was running the following Query
SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id, c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_folder_path FROM dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_sysobject_r sr2 WHERE (f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/contact/%') AND sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id = s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Active' AND (s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_folder_path = f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_format = 'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and ( language_code='en_US' or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id from dm_relation r where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF' and r.child_id in (select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where sys1.language_code in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label = 'Active' ) ))) ORDER BY s.i_chronicle_id,s.r_object_id
And the Error: "A database error has occurred during the creation of a cursor (' STATE=01000, CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length is 900 bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail.
Can Any one Help to fix this SQL Enterprise Manager?You could try adding the ROBUST PLAN hint to your query. Read more about it in Books Online.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...
> Hi All,
> I was running the following Query
> SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id,
c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_folder_path FROM
dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_sysobject_r sr2 WHERE
(f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/contact/%') AND
sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id =s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Active' AND
(s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT
r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND
w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_folder_path =f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND
w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_format ='zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and ( language_code='en_US'
or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id from dm_relation r
where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF' and r.child_id in
(select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where sys1.language_code
in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label = 'Active' ) )))
ORDER BY s.i_chronicle_id,s.r_object_id
> And the Error: "A database error has occurred during the creation of a cursor (' STATE=01000,
CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length is 900
bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combination of large
values, the insert/update operation will fail.
> Can Any one Help to fix this SQL Enterprise Manager?
>|||Perhaps the index indeed is larger that 900 bytes for some rows. This is not configurable. Work with
the vendor of the app and see what can be done to handle this. One possible thing is, of course to
remove the index...
I still suggest you read about what the ROBUST PLAN does. It is likely that the intermediate steps
of the query processing creates some work table for which the row length exceeds the max for SQL
Server.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:C6E27085-F620-4B28-8F43-0AD14BD9714B@.microsoft.com...
> Hi,
> The Query is generated by Documentum. As you know Documentum is a middleware that generates a
DQL Query (SQL like Query) on the underlying database. As per the error description, it looks like,
I have some Server configuration problem like
> 1) Maximum length of the index RowSetWorkTableSS = 1020 bytes
> 2) Maximum key length = 900 bytes
> what do these signify? How to increase these from EM?
> -- Tibor Karaszi wrote: --
> You could try adding the ROBUST PLAN hint to your query. Read more about it in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Raj" <anonymous@.discussions.microsoft.com> wrote in message
> news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...
> > Hi All,
> >> I was running the following Query
> >> SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id,
> c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_folder_path
FROM
> dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_sysobject_r sr2 WHERE
> (f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/contact/%')
AND
> sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id => s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Active' AND
> (s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT
> r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND
> w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_folder_path => f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND
> w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_format => 'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and (
language_code='en_US'
> or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id from
dm_relation r
> where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF' and r.child_id in
> (select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where
sys1.language_code
> in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label ='Active' ) )))
> ORDER BY s.i_chronicle_id,s.r_object_id
> >> And the Error: "A database error has occurred during the creation of a cursor ('
STATE=01000,
> CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length
is 900
> bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combination
of large
> values, the insert/update operation will fail.
> >> Can Any one Help to fix this SQL Enterprise Manager?
> >