Failed to open rowset - SQL Server - Parallel processing

[carlsd]

I have a report consisting of the base report and one subreport. The report runs fine using MS SQL Server on a single CPU machine. On a multi-CPU machine the "Failed to open a rowset" error is returned, followed by "Intra-query parallelism caused your server command (process ID ###) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1)".

The maxdop option tells the server to use only one processor, ok, but is there any way from Crystal - other than perhaps using stored procedures for the base and subreport - to "pass" the hint to the server? Configuring the server with a maxdop of 1 is not an option.

The bigger question is why would parallel processing fail to begin with. Or, are there right/wrong ways to link a subreport? (The report is "canned" and so a dynamic link isn't an option.)

Thanks for any insight.

 

[synapsevampire]

Interesting... I'm pretty sure that we have the multiple cpu SQL Server 2000 database setup here and it doesn't seem to have a problem.

What connectivity are you using?

I have used the CR ODBC for SQL Server and OLE DB.

-k

 

[carlsd]

I'm using OLE DB (ADO). I'll create an ODBC DSN - see if gives the same results.

 

[carlsd]

The problem was unrelated to the subreport as originally thought. Coincidently, the problem developed when the report enhancements were made to include the subreport.

The problem is data-related and could be replicated outside of Crystal. The report SQL uses 2 tables. Table “B” is joined twice to the parent table “A” using left outer joins. The query could be shown to either work, or fail with the parallelism error, depending upon the records selected from table “A”. What is not yet clear is how the two sets of records differ in a way to cause the query to work for one and fail with the other. Both sets contained records that warranted the need for the outer joins - such as no related record in table “B” or a null value in the joined column in table “A”.

The problem does not occur when one of the joins to table “B” is eliminated or when the outer joins are changed to inner joins. Fortunately the business process can be changed to eliminate the requirement for the outer joins so inner joins can be used.

 

[mostevil]

AFAIK you can specify MAXDOP 1 as a hint/option for the query, so you do not have to reconfigure the server. (max degree of parallelism Option)