Outer Join Issues in Sql 2005

Hi,

The following query works fine in Sql 2000 database, but it throws an error ('Query contains an outer-join request that is not permitted.') while executing the query on Sql 2005 database. We have already set the compatibility level of the Sql 2005 database to 'Sql Server 2000(80)'. But still we are getting the error.

select top 2 *
from table1 t1, table2 t2
where t2.col8 =* t1.col1
and t2.col9 =* ( select max(t21.col9) from table2 t21
where t21.col8 =* t2.col8 )

We are planning to convert all our sql 2000 databases to sql 2005 databases. We have used this kind of outer join(=* or *=) in many places in our application. It is also working fine many places in 2005 database, but throw errors in some places only.

Please let me know if any one knows the reason for the error in some places alone. So that we can give priority to those kind of query and change the outer join to 'SQL-92 compliant'. We know that this kind of outer joins are NOT going to be supported in future version of Sql and we are going to change all such outer join. But now we do not have much time to change all such outer join. It will be a great help if any one have answer for this.

Thanks in advance,

Sankar N

Is this error related only to the statements including an outer join on a subquery?

Regards

|||

You will have to modify the query to use the OUTER JOIN syntax. There is no way to make it work in SQL2005 for all cases. The compatibility level doesn't help in this case due to changes in query plan generation in SQL2005 (there are lot of changes, some of the syntax evaluation is more strict, new optimizations etc). The old proprietary *= and =* syntax has lot of ambiguities and it can produce incorrect results. It is best to convert all statements with old syntax or do it on a case-by-case basis which might be problematic.

sql

orphaned request in the RSLog

we are trying to track down an error we get in RS where the ssl connection
is forcibly closed by the remote server. Going through the RS Logs I noticed
these info statements. Can anyone explain in better detail what these mean?
w3wp!runningjobs!11b4!9/16/2005-16:06:35:: w WARN: Thread pool pressure:
turning off threads
w3wp!runningjobs!11b4!09/16/2005-16:06:37:: w WARN: Thread pool pressure:
turning off threads
w3wp!library!11b4!09/16/2005-16:06:37:: i INFO: Initializing
EnableExecutionLogging to 'True' as specified in Server system properties.
w3wp!runningjobs!11b4!9/16/2005-16:07:15:: i INFO: Adding: 5 running jobs to
the database
w3wp!runningjobs!1d5c!9/16/2005-16:07:15:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!1d5c!9/16/2005-16:07:15:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!1d5c!9/16/2005-16:07:15:: i INFO:FYI,,, We are running RS SP2 on clustered Windows Server 2003
"Mark" wrote:
> we are trying to track down an error we get in RS where the ssl connection
> is forcibly closed by the remote server. Going through the RS Logs I noticed
> these info statements. Can anyone explain in better detail what these mean?
>
> w3wp!runningjobs!11b4!9/16/2005-16:06:35:: w WARN: Thread pool pressure:
> turning off threads
> w3wp!runningjobs!11b4!09/16/2005-16:06:37:: w WARN: Thread pool pressure:
> turning off threads
> w3wp!library!11b4!09/16/2005-16:06:37:: i INFO: Initializing
> EnableExecutionLogging to 'True' as specified in Server system properties.
> w3wp!runningjobs!11b4!9/16/2005-16:07:15:: i INFO: Adding: 5 running jobs to
> the database
> w3wp!runningjobs!1d5c!9/16/2005-16:07:15:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!1d5c!9/16/2005-16:07:15:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!1d5c!9/16/2005-16:07:15:: i INFO:|||Hi Mark,
Thanks for your posting!
From your descriptions, I understood when disconnected forcibly from remote
server, your Report Server will report the error message "found orphaned
request". If I have misunderstood your concern, please feel free to point
it out.
I believe due to the none response from client (it was disconnected
forcibly), some existing request become orphaned. Does this error message
make any business impact on your server?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||So are you saying then that its' the client that is actually disconnecting
from the server?
This issue actually goes back to an early post I had with long running
reports
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?&query=long+running+reports&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=80219d15-436f-4b8e-ad0d-0c8c604c6b9f&mid=80219d15-436f-4b8e-ad0d-0c8c604c6b9f
It seems we constantly get this message when we have a batch job running
on a client machine doing a soap call to the RS server. Everything runs fine
until it hits 15 minutes and then we get the SSL connection closed error. We
have tried a variety of things from upgrading to sp2 applying serveral hot
fixes, and changing the defualt IIS timeout settings from 900 seconds to 600
seconds. All to no avail. At exaclty 15 minutes we get disconnected.
I was looking then through the RSlogs and saw the orphaned requests coming
up at about the same time we get the ssl connection error and decided to post
again to see if that might trigger a new approach.
Frustrated at 15!!!
"Michael Cheng [MSFT]" wrote:
> Hi Mark,
> Thanks for your posting!
> From your descriptions, I understood when disconnected forcibly from remote
> server, your Report Server will report the error message "found orphaned
> request". If I have misunderstood your concern, please feel free to point
> it out.
> I believe due to the none response from client (it was disconnected
> forcibly), some existing request become orphaned. Does this error message
> make any business impact on your server?
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Hi Mark,
From the error, I belive it seems to be caused by data connection to
datasource for the report. You may want to check if the following error is
also in RS log:
e ERROR: Reporting Services error
Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has
occurred during report processing. -->
Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An
error
has occurred during report processing. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Query
execution failed for data set 'MSFixlets'. -->
System.Data.SqlClient.SqlException:
Timeout expired. The timeout period elapsed prior to completion of the
operation
or the server is not responding.
You may try the following steps:
Open up the report project in VS.NET IDE, go to the Data tab and edit the
dataset which brings back those images. In the Query tab, set the timeout
property to more than 900 (15 mins).
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.