Dear All,
I've been trying to deploy the security extension example from
Microsoft for SSRS2005 and have encountered the following errors when
I access the reports virtual directory (although the pages/
UILogin.aspx page comes up)
- Report Server Web App reports that the report server is not
responding.
- Report Server reports that it cannot access (access denied)
accessing the dll file.
Can anyone help me with this?
Config File Segments:
________________________________________________________________________
rsmreportserver.config (file exists when I do a dir command on the
path in the type attribute)
<Security>
<Extension Name="Forms"
Type="Microsoft.Samples.ReportingServices.CustomSecurity.Authorization,
Microsoft.Samples.ReportingServices.CustomSecurity" >
<Configuration>
<AdminConfiguration>
<UserName>prime</UserName>
</AdminConfiguration>
</Configuration>
</Extension>
</Security>
<Authentication>
<Extension Name="Forms"
Type="Microsoft.Samples.ReportingServices.CustomSecurity.AuthenticationExtension,
Microsoft.Samples.ReportingServices.CustomSecurity" />
</Authentication>
_____________________________________________________________________
rssrvpolicy.config
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="$CodeGen$/*"
/>
</CodeGroup>
<CodeGroup
class="UnionCodeGroup"
version="1"
Name="SecurityExtensionCodeGroup"
Description="Code group for the sample security extension"
PermissionSetName="FullTrust">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\bin
\Microsoft.Samples.ReportingServices.CustomSecurity.dll"
/>
</CodeGroup>
_____________________________________________________________________
rsswebapplication.config (note xxx=actual hostname)
<UI>
<CustomAuthenticationUI>
<loginUrl>/Pages/UILogon.aspx</loginUrl>
<UseSSL>False</UseSSL>
</CustomAuthenticationUI>
<ReportServerUrl>http://xxx/reportserver</ReportServerUrl>
</UI>
_____________________________________________________________________
_____________________________________________________________________
ReportServer WebApp Log
w3wp!library!1!12/3/2007-18:28:01:: i INFO: Initializing WatsonFlags
to default value of '1064' because it was not specified in
Configuration file.
w3wp!library!1!12/3/2007-18:28:01:: i INFO: Initializing
WatsonDumpOnExceptions to default value of
'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException'
because it was not specified in Configuration file.
w3wp!library!1!12/3/2007-18:28:01:: i INFO: Initializing
WatsonDumpExcludeIfContainsExceptions to default value of
'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException'
because it was not specified in Configuration file.
w3wp!library!1!12/3/2007-18:28:01:: i INFO: Initializing
SecureConnectionLevel to default value of '1' because it was not
specified in Configuration file.
w3wp!library!1!12/3/2007-18:28:01:: i INFO: Initializing
DisplayErrorLink to 'True' as specified in Configuration file.
w3wp!library!1!12/3/2007-18:28:01:: i INFO: Initializing
WebServiceUseFileShareStorage to default value of 'False' because it
was not specified in Configuration file.
w3wp!ui!1!12/3/2007-18:28:05:: e ERROR: The report server is not
responding. Verify that the report server is running and can be
accessed from this computer.
w3wp!ui!1!12/3/2007-18:28:06:: e ERROR: HTTP status code --> 500
--Details--
Microsoft.ReportingServices.UI.Global+RSWebServiceWrapper
+CantCommunicateWithReportServerException: The report server is not
responding. Verify that the report server is running and can be
accessed from this computer.
at
Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods()
at
Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String
methodname)
at
Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()
at
Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel
level)
at
Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object
sender, EventArgs args)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Control.OnInit(EventArgs e)
at System.Web.UI.Page.OnInit(EventArgs e)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
w3wp!ui!1!12/3/2007-18:28:07:: e ERROR: Exception in ShowErrorPage:
System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean
preserveForm)
at
Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String
errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean
preserveForm)
at
Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String
errMsg)
_________________________________________________________________________
ReportServer Log
w3wp!runningjobs!7!12/3/2007-18:28:04:: i INFO: Database Cleanup (Web
Service) timer enabled: Next Event: 600 seconds. Cycle: 600 seconds
w3wp!runningjobs!7!12/3/2007-18:28:04:: i INFO: Running Requests
Scavenger timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
w3wp!runningjobs!7!12/3/2007-18:28:04:: i INFO: Running Requests DB
timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
w3wp!runningjobs!7!12/3/2007-18:28:04:: i INFO: Memory stats update
timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
w3wp!extensionfactory!7!12/03/2007-18:28:04:: e ERROR: Exception
caught instantiating Forms report server extension:
System.IO.FileLoadException: Could not load file or assembly
'Microsoft.Samples.ReportingServices.CustomSecurity' or one of its
dependencies. Access is denied.
File name: 'Microsoft.Samples.ReportingServices.CustomSecurity'
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String
codeBase, Evidence assemblySecurity, Assembly locationHint,
StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean
forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName
assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark,
Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString,
Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean
forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
at
Microsoft.ReportingServices.Diagnostics.ExtensionClassFactory.LoadAssembly(String
name)
at
Microsoft.ReportingServices.Diagnostics.ExtensionClassFactory.CreateExtensionObject(Extension
extConfig)
WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM
\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind
failure logging.
To turn this feature off, remove the registry value [HKLM\Software
\Microsoft\Fusion!EnableLog].
.
w3wp!library!7!12/03/2007-18:28:04:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The report server has encountered a configuration error. See the
report server log files for more information., Could not load
Authentication extension;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The report server has encountered a configuration error. See the
report server log files for more information.
w3wp!library!7!12/03/2007-18:28:06:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The report server has encountered a configuration error. See the
report server log files for more information., Could not load
Authentication extension;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The report server has encountered a configuration error. See the
report server log files for more information.
w3wp!library!8!12/3/2007-18:29:04:: i INFO: Catalog SQL Server Edition
= Developer
w3wp!library!7!12/3/2007-18:38:04:: i INFO: Cleaned 0 batch records, 0
policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0
running jobs, 0 persisted streams
__________________________________________________________
Thanks, HerbHi
Ive got the same problem, but only on windows server 2003 using ssl. It runs
fine untill i install a ssl certificate, then it gives me a similar error. *.
ReportingServices.CustomSecurity* file not found.
If i reverse the ssl certificate, the problem remains. The same stuff works
fine on win xp with ssl though, and on win server 2003 without ssl. How did
you resolve this?
thanks
Leon
url:http://www.ureader.com/msg/115713209.aspx
Showing posts with label encountered. Show all posts
Showing posts with label encountered. Show all posts
Monday, March 26, 2012
Saturday, February 25, 2012
Problem with View migrated from SQL Server 2000 into 2005
We are in the process of migrating from SQL Server 2000 to 2005. We encountered a problem with one of our web applications (ASP) when attached to the new 2005 database. We do not get this error when the application is attached to the 2000 database.
During execution of the following code:
--
sub OpenRS_TicketDetails(iTicketID)
strSQL="SELECT * from vwexTicketDetails WHERE TicketID =" & iTicketID
rs.open strSQL, cnReadWrite, adOpenStatic, adLockOptimistic
end sub
We encountered the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e23'
Row handle referred to a deleted row or a row marked for deletion.
The following is the select statement related to the view:
SELECT T.TicketID, T.TicketDate, T.Problem, T.Technician_Assigned, T.Closed_Date, T.PersonID, T.SiteId, T.ProgramId, T.StatusId, T.PriorityId, T.CategoryId,
CMT.Comment, RTRIM(P.LastName) + ', ' + RTRIM(P.FirstName) AS FullName, P.Phone, P.WorkLocation, CT.CategoryName AS Category,
PR.priorityDesc AS Priority, ST.statusDesc AS Status
FROM dbo.dtTickets AS T LEFT OUTER JOIN
dbo.dtComments AS CMT ON T.TicketID = CMT.TicketID LEFT OUTER JOIN
DIVCommon.dbo.dtPersonnel AS P ON T.PersonID = P.PersonID INNER JOIN
dbo.vtCategory AS CT ON T.CategoryId = CT.CategoryID INNER JOIN
dbo.vtPriority AS PR ON T.PriorityId = PR.priorityId INNER JOIN
dbo.vtStatus AS ST ON T.StatusId = ST.statusId
We tracked the problem to the dtComments table and were able to come up with a workaround to our problem. When we added a primary key to the dtComments table, the application ran fine.
CREATE TABLE [dbo].[dtComments](
[CommentId] [int] IDENTITY(1,1) NOT NULL,
[TicketID] [int] NULL,
[Comment] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastModUser] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastModDate] [datetime] NULL)
Can someone explain to me why we are experiencing this problem in the 2005 environment and if there is a better solution. Please let me know if you need additional information about this situation.
Thanks, Doug
I can't say why it is a problem in 2005 rather than 2000. But I can explain why the problem, its not simple and to do with some inner workings of ADO, metadata and optimistic locking
You are specified to have a optimistic locked recordset based on a view. Firstly you shouldn't do this with views because you can end up with orphaned rows if you try and insert data.
How ADO handles the optimistic locking is it needs to know how to identify the row that is being updated and also how to identify what determines the record has changed so that it can verify the update. However your dtComments table doesn't have a PK thus the problem. ADO does try and obtain the information needed in the absence of a PK but this is what is causing the problem.
Putting a PK should be your answer, all tables should have one unless a specific reason not to.
You could also try changing it to a readonly recordset.
Problem with View
Hi,
Today I encountered with a view that references other views and each of them
reference some other view. The problem began when I started querying this
view.
When I used condition(WHERE), the view produced about 67,000 rows. When I
excluded the condition, I expected to have more rows but the result was
about 50,000 rows. I tried different ways but the condition produced more
rows!
Whereas the views were nested, it was very difficult to trace the problem.
It may help if I say some views used UNION ALL to concatenate some result.
I must find the source of problem. Does anybody have any idea/experience
like that?
Any help would be greatly appreciated.
Leila
I've had problems with nested views where the server has taken hours to get a
query plan (but that developer nested to quite a few levels) and heard of the
sort of thing you are seeing.
have you rebooted the server and updated statistics?
Apart from that I would suggest getting rid of the views and putting the
logic in stored procedures. You'll probably find it faster and certainly
easier to maintain and troubleshoot.
"Leila" wrote:
> Hi,
> Today I encountered with a view that references other views and each of them
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>
>
|||In addition to Nigel's commends: Are you using any old-style outer joins in any of the view
definitions? (*=)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <leilas@.hotpop.com> wrote in message news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of them reference some other
> view. The problem began when I started querying this view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I excluded the condition,
> I expected to have more rows but the result was about 50,000 rows. I tried different ways but the
> condition produced more rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience like that?
> Any help would be greatly appreciated.
> Leila
>
|||Thanks Tibor,
I'm not sure if I have undertood your meaning.
Do you mean old style joins like:
select t1.*,t2.* from t1,t2 where t1.id=t2.id
?
Leila
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> In addition to Nigel's commends: Are you using any old-style outer joins
in any of the view
> definitions? (*=)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <leilas@.hotpop.com> wrote in message
news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
them reference some other[vbcol=seagreen]
I excluded the condition,[vbcol=seagreen]
tried different ways but the[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]
like that?
>
|||Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the[vbcol=seagreen]
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
them[vbcol=seagreen]
this[vbcol=seagreen]
I[vbcol=seagreen]
more[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]
|||Leila,
I'm referring to old style *outer* joins, such as:
SELECT t1.col1, t2.colZ
FROM t1, t2
WHERE t1.c1 *= t2.c1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <Leilas@.hotpop.com> wrote in message news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Thanks Tibor,
> I'm not sure if I have undertood your meaning.
> Do you mean old style joins like:
> select t1.*,t2.* from t1,t2 where t1.id=t2.id
> ?
> Leila
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> in any of the view
> news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> them reference some other
> I excluded the condition,
> tried different ways but the
> problem.
> result.
> like that?
>
|||No, but there're ansi style outer joins.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Leila,
> I'm referring to old style *outer* joins, such as:
> SELECT t1.col1, t2.colZ
> FROM t1, t2
> WHERE t1.c1 *= t2.c1
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
in[vbcol=seagreen]
joins[vbcol=seagreen]
of[vbcol=seagreen]
When[vbcol=seagreen]
idea/experience
>
|||Leila,
can you post a concise DDL statements to replicate the issue, if possible..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Leila" <Leilas@.hotpop.com> wrote in message
news:#u0J0dP8EHA.2572@.tk2msftngp13.phx.gbl...
> No, but there're ansi style outer joins.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
wrote[vbcol=seagreen]
> in
> joins
each[vbcol=seagreen]
> of
> When
I
> idea/experience
>
|||Sounds like someone coded that ID to be VARCHAR instead of INT. Chances are
WHERE CustomerID = 100 is NULL. Try it with WHERE CustomerID LIKE '100%'
Even though I'd like to, I will refrain from beating up on you for using
nested VIEWS; hwoever, if you must nest, then you might consider
materializing the lower level ones. SQL Server calls these INDEXED VIEWS
but the effect is the same.
Sincerely,
Anthony Thomas
"Leila" <Leilas@.hotpop.com> wrote in message
news:ORQCBiO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the[vbcol=seagreen]
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
them[vbcol=seagreen]
this[vbcol=seagreen]
I[vbcol=seagreen]
more[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]
|||I did see couple of issues on my server, where in I do a select * with and
without a where clause. The result was different. But, I did this on a
table. The problem was found to be index corruption. You might want to
check into that.
-Nags
"Leila" <leilas@.hotpop.com> wrote in message
news:#w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of
them
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>
Today I encountered with a view that references other views and each of them
reference some other view. The problem began when I started querying this
view.
When I used condition(WHERE), the view produced about 67,000 rows. When I
excluded the condition, I expected to have more rows but the result was
about 50,000 rows. I tried different ways but the condition produced more
rows!
Whereas the views were nested, it was very difficult to trace the problem.
It may help if I say some views used UNION ALL to concatenate some result.
I must find the source of problem. Does anybody have any idea/experience
like that?
Any help would be greatly appreciated.
Leila
I've had problems with nested views where the server has taken hours to get a
query plan (but that developer nested to quite a few levels) and heard of the
sort of thing you are seeing.
have you rebooted the server and updated statistics?
Apart from that I would suggest getting rid of the views and putting the
logic in stored procedures. You'll probably find it faster and certainly
easier to maintain and troubleshoot.
"Leila" wrote:
> Hi,
> Today I encountered with a view that references other views and each of them
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>
>
|||In addition to Nigel's commends: Are you using any old-style outer joins in any of the view
definitions? (*=)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <leilas@.hotpop.com> wrote in message news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of them reference some other
> view. The problem began when I started querying this view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I excluded the condition,
> I expected to have more rows but the result was about 50,000 rows. I tried different ways but the
> condition produced more rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience like that?
> Any help would be greatly appreciated.
> Leila
>
|||Thanks Tibor,
I'm not sure if I have undertood your meaning.
Do you mean old style joins like:
select t1.*,t2.* from t1,t2 where t1.id=t2.id
?
Leila
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> In addition to Nigel's commends: Are you using any old-style outer joins
in any of the view
> definitions? (*=)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <leilas@.hotpop.com> wrote in message
news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
them reference some other[vbcol=seagreen]
I excluded the condition,[vbcol=seagreen]
tried different ways but the[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]
like that?
>
|||Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the[vbcol=seagreen]
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
them[vbcol=seagreen]
this[vbcol=seagreen]
I[vbcol=seagreen]
more[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]
|||Leila,
I'm referring to old style *outer* joins, such as:
SELECT t1.col1, t2.colZ
FROM t1, t2
WHERE t1.c1 *= t2.c1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <Leilas@.hotpop.com> wrote in message news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Thanks Tibor,
> I'm not sure if I have undertood your meaning.
> Do you mean old style joins like:
> select t1.*,t2.* from t1,t2 where t1.id=t2.id
> ?
> Leila
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> in any of the view
> news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> them reference some other
> I excluded the condition,
> tried different ways but the
> problem.
> result.
> like that?
>
|||No, but there're ansi style outer joins.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Leila,
> I'm referring to old style *outer* joins, such as:
> SELECT t1.col1, t2.colZ
> FROM t1, t2
> WHERE t1.c1 *= t2.c1
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
in[vbcol=seagreen]
joins[vbcol=seagreen]
of[vbcol=seagreen]
When[vbcol=seagreen]
idea/experience
>
|||Leila,
can you post a concise DDL statements to replicate the issue, if possible..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Leila" <Leilas@.hotpop.com> wrote in message
news:#u0J0dP8EHA.2572@.tk2msftngp13.phx.gbl...
> No, but there're ansi style outer joins.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
wrote[vbcol=seagreen]
> in
> joins
each[vbcol=seagreen]
> of
> When
I
> idea/experience
>
|||Sounds like someone coded that ID to be VARCHAR instead of INT. Chances are
WHERE CustomerID = 100 is NULL. Try it with WHERE CustomerID LIKE '100%'
Even though I'd like to, I will refrain from beating up on you for using
nested VIEWS; hwoever, if you must nest, then you might consider
materializing the lower level ones. SQL Server calls these INDEXED VIEWS
but the effect is the same.
Sincerely,
Anthony Thomas
"Leila" <Leilas@.hotpop.com> wrote in message
news:ORQCBiO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the[vbcol=seagreen]
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
them[vbcol=seagreen]
this[vbcol=seagreen]
I[vbcol=seagreen]
more[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]
|||I did see couple of issues on my server, where in I do a select * with and
without a where clause. The result was different. But, I did this on a
table. The problem was found to be index corruption. You might want to
check into that.
-Nags
"Leila" <leilas@.hotpop.com> wrote in message
news:#w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of
them
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>
Problem with View
Hi,
Today I encountered with a view that references other views and each of them
reference some other view. The problem began when I started querying this
view.
When I used condition(WHERE), the view produced about 67,000 rows. When I
excluded the condition, I expected to have more rows but the result was
about 50,000 rows. I tried different ways but the condition produced more
rows!
Whereas the views were nested, it was very difficult to trace the problem.
It may help if I say some views used UNION ALL to concatenate some result.
I must find the source of problem. Does anybody have any idea/experience
like that?
Any help would be greatly appreciated.
LeilaI've had problems with nested views where the server has taken hours to get
a
query plan (but that developer nested to quite a few levels) and heard of th
e
sort of thing you are seeing.
have you rebooted the server and updated statistics?
Apart from that I would suggest getting rid of the views and putting the
logic in stored procedures. You'll probably find it faster and certainly
easier to maintain and troubleshoot.
"Leila" wrote:
> Hi,
> Today I encountered with a view that references other views and each of th
em
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>
>|||In addition to Nigel's commends: Are you using any old-style outer joins in
any of the view
definitions? (*=)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <leilas@.hotpop.com> wrote in message news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of th
em reference some other
> view. The problem began when I started querying this view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
excluded the condition,
> I expected to have more rows but the result was about 50,000 rows. I tried
different ways but the
> condition produced more rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience l
ike that?
> Any help would be greatly appreciated.
> Leila
>|||Thanks Tibor,
I'm not sure if I have undertood your meaning.
Do you mean old style joins like:
select t1.*,t2.* from t1,t2 where t1.id=t2.id
?
Leila
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> In addition to Nigel's commends: Are you using any old-style outer joins
in any of the view
> definitions? (*=)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <leilas@.hotpop.com> wrote in message
news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
them reference some other[vbcol=seagreen]
I excluded the condition,[vbcol=seagreen]
tried different ways but the[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]
like that?[vbcol=seagreen]
>|||Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the[vbcol=seagreen]
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
>
them[vbcol=seagreen]
this[vbcol=seagreen]
I[vbcol=seagreen]
more[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]|||Leila,
I'm referring to old style *outer* joins, such as:
SELECT t1.col1, t2.colZ
FROM t1, t2
WHERE t1.c1 *= t2.c1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <Leilas@.hotpop.com> wrote in message news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...[v
bcol=seagreen]
> Thanks Tibor,
> I'm not sure if I have undertood your meaning.
> Do you mean old style joins like:
> select t1.*,t2.* from t1,t2 where t1.id=t2.id
> ?
> Leila
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> in any of the view
> news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> them reference some other
> I excluded the condition,
> tried different ways but the
> problem.
> result.
> like that?
>[/vbcol]|||No, but there're ansi style outer joins.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Leila,
> I'm referring to old style *outer* joins, such as:
> SELECT t1.col1, t2.colZ
> FROM t1, t2
> WHERE t1.c1 *= t2.c1
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
in[vbcol=seagreen]
joins[vbcol=seagreen]
of[vbcol=seagreen]
When[vbcol=seagreen]
idea/experience[vbcol=seagreen]
>|||Leila,
can you post a concise DDL statements to replicate the issue, if possible..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Leila" <Leilas@.hotpop.com> wrote in message
news:#u0J0dP8EHA.2572@.tk2msftngp13.phx.gbl...
> No, but there're ansi style outer joins.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
wrote[vbcol=seagreen]
> in
> joins
each[vbcol=seagreen]
> of
> When
I[vbcol=seagreen]
> idea/experience
>|||Sounds like someone coded that ID to be VARCHAR instead of INT. Chances are
WHERE CustomerID = 100 is NULL. Try it with WHERE CustomerID LIKE '100%'
Even though I'd like to, I will refrain from beating up on you for using
nested VIEWS; hwoever, if you must nest, then you might consider
materializing the lower level ones. SQL Server calls these INDEXED VIEWS
but the effect is the same.
Sincerely,
Anthony Thomas
"Leila" <Leilas@.hotpop.com> wrote in message
news:ORQCBiO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the[vbcol=seagreen]
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
>
them[vbcol=seagreen]
this[vbcol=seagreen]
I[vbcol=seagreen]
more[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]|||I did see couple of issues on my server, where in I do a select * with and
without a where clause. The result was different. But, I did this on a
table. The problem was found to be index corruption. You might want to
check into that.
-Nags
"Leila" <leilas@.hotpop.com> wrote in message
news:#w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of
them
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>
Today I encountered with a view that references other views and each of them
reference some other view. The problem began when I started querying this
view.
When I used condition(WHERE), the view produced about 67,000 rows. When I
excluded the condition, I expected to have more rows but the result was
about 50,000 rows. I tried different ways but the condition produced more
rows!
Whereas the views were nested, it was very difficult to trace the problem.
It may help if I say some views used UNION ALL to concatenate some result.
I must find the source of problem. Does anybody have any idea/experience
like that?
Any help would be greatly appreciated.
LeilaI've had problems with nested views where the server has taken hours to get
a
query plan (but that developer nested to quite a few levels) and heard of th
e
sort of thing you are seeing.
have you rebooted the server and updated statistics?
Apart from that I would suggest getting rid of the views and putting the
logic in stored procedures. You'll probably find it faster and certainly
easier to maintain and troubleshoot.
"Leila" wrote:
> Hi,
> Today I encountered with a view that references other views and each of th
em
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>
>|||In addition to Nigel's commends: Are you using any old-style outer joins in
any of the view
definitions? (*=)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <leilas@.hotpop.com> wrote in message news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of th
em reference some other
> view. The problem began when I started querying this view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
excluded the condition,
> I expected to have more rows but the result was about 50,000 rows. I tried
different ways but the
> condition produced more rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience l
ike that?
> Any help would be greatly appreciated.
> Leila
>|||Thanks Tibor,
I'm not sure if I have undertood your meaning.
Do you mean old style joins like:
select t1.*,t2.* from t1,t2 where t1.id=t2.id
?
Leila
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> In addition to Nigel's commends: Are you using any old-style outer joins
in any of the view
> definitions? (*=)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <leilas@.hotpop.com> wrote in message
news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
them reference some other[vbcol=seagreen]
I excluded the condition,[vbcol=seagreen]
tried different ways but the[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]
like that?[vbcol=seagreen]
>|||Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the[vbcol=seagreen]
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
>
them[vbcol=seagreen]
this[vbcol=seagreen]
I[vbcol=seagreen]
more[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]|||Leila,
I'm referring to old style *outer* joins, such as:
SELECT t1.col1, t2.colZ
FROM t1, t2
WHERE t1.c1 *= t2.c1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <Leilas@.hotpop.com> wrote in message news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...[v
bcol=seagreen]
> Thanks Tibor,
> I'm not sure if I have undertood your meaning.
> Do you mean old style joins like:
> select t1.*,t2.* from t1,t2 where t1.id=t2.id
> ?
> Leila
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> in any of the view
> news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> them reference some other
> I excluded the condition,
> tried different ways but the
> problem.
> result.
> like that?
>[/vbcol]|||No, but there're ansi style outer joins.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Leila,
> I'm referring to old style *outer* joins, such as:
> SELECT t1.col1, t2.colZ
> FROM t1, t2
> WHERE t1.c1 *= t2.c1
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
in[vbcol=seagreen]
joins[vbcol=seagreen]
of[vbcol=seagreen]
When[vbcol=seagreen]
idea/experience[vbcol=seagreen]
>|||Leila,
can you post a concise DDL statements to replicate the issue, if possible..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Leila" <Leilas@.hotpop.com> wrote in message
news:#u0J0dP8EHA.2572@.tk2msftngp13.phx.gbl...
> No, but there're ansi style outer joins.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
wrote[vbcol=seagreen]
> in
> joins
each[vbcol=seagreen]
> of
> When
I[vbcol=seagreen]
> idea/experience
>|||Sounds like someone coded that ID to be VARCHAR instead of INT. Chances are
WHERE CustomerID = 100 is NULL. Try it with WHERE CustomerID LIKE '100%'
Even though I'd like to, I will refrain from beating up on you for using
nested VIEWS; hwoever, if you must nest, then you might consider
materializing the lower level ones. SQL Server calls these INDEXED VIEWS
but the effect is the same.
Sincerely,
Anthony Thomas
"Leila" <Leilas@.hotpop.com> wrote in message
news:ORQCBiO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the[vbcol=seagreen]
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
>
them[vbcol=seagreen]
this[vbcol=seagreen]
I[vbcol=seagreen]
more[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]|||I did see couple of issues on my server, where in I do a select * with and
without a where clause. The result was different. But, I did this on a
table. The problem was found to be index corruption. You might want to
check into that.
-Nags
"Leila" <leilas@.hotpop.com> wrote in message
news:#w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of
them
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>
Problem with View
Hi,
Today I encountered with a view that references other views and each of them
reference some other view. The problem began when I started querying this
view.
When I used condition(WHERE), the view produced about 67,000 rows. When I
excluded the condition, I expected to have more rows but the result was
about 50,000 rows. I tried different ways but the condition produced more
rows!
Whereas the views were nested, it was very difficult to trace the problem.
It may help if I say some views used UNION ALL to concatenate some result.
I must find the source of problem. Does anybody have any idea/experience
like that?
Any help would be greatly appreciated.
LeilaI've had problems with nested views where the server has taken hours to get a
query plan (but that developer nested to quite a few levels) and heard of the
sort of thing you are seeing.
have you rebooted the server and updated statistics?
Apart from that I would suggest getting rid of the views and putting the
logic in stored procedures. You'll probably find it faster and certainly
easier to maintain and troubleshoot.
"Leila" wrote:
> Hi,
> Today I encountered with a view that references other views and each of them
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>
>|||In addition to Nigel's commends: Are you using any old-style outer joins in any of the view
definitions? (*=)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <leilas@.hotpop.com> wrote in message news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of them reference some other
> view. The problem began when I started querying this view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I excluded the condition,
> I expected to have more rows but the result was about 50,000 rows. I tried different ways but the
> condition produced more rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience like that?
> Any help would be greatly appreciated.
> Leila
>|||Thanks Tibor,
I'm not sure if I have undertood your meaning.
Do you mean old style joins like:
select t1.*,t2.* from t1,t2 where t1.id=t2.id
?
Leila
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> In addition to Nigel's commends: Are you using any old-style outer joins
in any of the view
> definitions? (*=)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <leilas@.hotpop.com> wrote in message
news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> > Today I encountered with a view that references other views and each of
them reference some other
> > view. The problem began when I started querying this view.
> > When I used condition(WHERE), the view produced about 67,000 rows. When
I excluded the condition,
> > I expected to have more rows but the result was about 50,000 rows. I
tried different ways but the
> > condition produced more rows!
> > Whereas the views were nested, it was very difficult to trace the
problem.
> > It may help if I say some views used UNION ALL to concatenate some
result.
> > I must find the source of problem. Does anybody have any idea/experience
like that?
> > Any help would be greatly appreciated.
> > Leila
> >
>|||Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
> > Hi,
> > Today I encountered with a view that references other views and each of
them
> > reference some other view. The problem began when I started querying
this
> > view.
> > When I used condition(WHERE), the view produced about 67,000 rows. When
I
> > excluded the condition, I expected to have more rows but the result was
> > about 50,000 rows. I tried different ways but the condition produced
more
> > rows!
> > Whereas the views were nested, it was very difficult to trace the
problem.
> > It may help if I say some views used UNION ALL to concatenate some
result.
> > I must find the source of problem. Does anybody have any idea/experience
> > like that?
> > Any help would be greatly appreciated.
> > Leila
> >
> >
> >|||Leila,
I'm referring to old style *outer* joins, such as:
SELECT t1.col1, t2.colZ
FROM t1, t2
WHERE t1.c1 *= t2.c1
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <Leilas@.hotpop.com> wrote in message news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Thanks Tibor,
> I'm not sure if I have undertood your meaning.
> Do you mean old style joins like:
> select t1.*,t2.* from t1,t2 where t1.id=t2.id
> ?
> Leila
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
>> In addition to Nigel's commends: Are you using any old-style outer joins
> in any of the view
>> definitions? (*=)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> > Today I encountered with a view that references other views and each of
> them reference some other
>> > view. The problem began when I started querying this view.
>> > When I used condition(WHERE), the view produced about 67,000 rows. When
> I excluded the condition,
>> > I expected to have more rows but the result was about 50,000 rows. I
> tried different ways but the
>> > condition produced more rows!
>> > Whereas the views were nested, it was very difficult to trace the
> problem.
>> > It may help if I say some views used UNION ALL to concatenate some
> result.
>> > I must find the source of problem. Does anybody have any idea/experience
> like that?
>> > Any help would be greatly appreciated.
>> > Leila
>> >
>>
>|||No, but there're ansi style outer joins.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Leila,
> I'm referring to old style *outer* joins, such as:
> SELECT t1.col1, t2.colZ
> FROM t1, t2
> WHERE t1.c1 *= t2.c1
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
> > Thanks Tibor,
> > I'm not sure if I have undertood your meaning.
> > Do you mean old style joins like:
> > select t1.*,t2.* from t1,t2 where t1.id=t2.id
> > ?
> >
> > Leila
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> >> In addition to Nigel's commends: Are you using any old-style outer
joins
> > in any of the view
> >> definitions? (*=)
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> > news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> >> > Hi,
> >> > Today I encountered with a view that references other views and each
of
> > them reference some other
> >> > view. The problem began when I started querying this view.
> >> > When I used condition(WHERE), the view produced about 67,000 rows.
When
> > I excluded the condition,
> >> > I expected to have more rows but the result was about 50,000 rows. I
> > tried different ways but the
> >> > condition produced more rows!
> >> > Whereas the views were nested, it was very difficult to trace the
> > problem.
> >> > It may help if I say some views used UNION ALL to concatenate some
> > result.
> >> > I must find the source of problem. Does anybody have any
idea/experience
> > like that?
> >> > Any help would be greatly appreciated.
> >> > Leila
> >> >
> >>
> >>
> >
> >
>|||Leila,
can you post a concise DDL statements to replicate the issue, if possible..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Leila" <Leilas@.hotpop.com> wrote in message
news:#u0J0dP8EHA.2572@.tk2msftngp13.phx.gbl...
> No, but there're ansi style outer joins.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> > Leila,
> >
> > I'm referring to old style *outer* joins, such as:
> >
> > SELECT t1.col1, t2.colZ
> > FROM t1, t2
> > WHERE t1.c1 *= t2.c1
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > http://www.sqlug.se/
> >
> >
> > "Leila" <Leilas@.hotpop.com> wrote in message
> news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
> > > Thanks Tibor,
> > > I'm not sure if I have undertood your meaning.
> > > Do you mean old style joins like:
> > > select t1.*,t2.* from t1,t2 where t1.id=t2.id
> > > ?
> > >
> > > Leila
> > >
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> in
> > > message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> > >> In addition to Nigel's commends: Are you using any old-style outer
> joins
> > > in any of the view
> > >> definitions? (*=)
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >> http://www.sqlug.se/
> > >>
> > >>
> > >> "Leila" <leilas@.hotpop.com> wrote in message
> > > news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> > >> > Hi,
> > >> > Today I encountered with a view that references other views and
each
> of
> > > them reference some other
> > >> > view. The problem began when I started querying this view.
> > >> > When I used condition(WHERE), the view produced about 67,000 rows.
> When
> > > I excluded the condition,
> > >> > I expected to have more rows but the result was about 50,000 rows.
I
> > > tried different ways but the
> > >> > condition produced more rows!
> > >> > Whereas the views were nested, it was very difficult to trace the
> > > problem.
> > >> > It may help if I say some views used UNION ALL to concatenate some
> > > result.
> > >> > I must find the source of problem. Does anybody have any
> idea/experience
> > > like that?
> > >> > Any help would be greatly appreciated.
> > >> > Leila
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>|||Sounds like someone coded that ID to be VARCHAR instead of INT. Chances are
WHERE CustomerID = 100 is NULL. Try it with WHERE CustomerID LIKE '100%'
Even though I'd like to, I will refrain from beating up on you for using
nested VIEWS; hwoever, if you must nest, then you might consider
materializing the lower level ones. SQL Server calls these INDEXED VIEWS
but the effect is the same.
Sincerely,
Anthony Thomas
"Leila" <Leilas@.hotpop.com> wrote in message
news:ORQCBiO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
> > Hi,
> > Today I encountered with a view that references other views and each of
them
> > reference some other view. The problem began when I started querying
this
> > view.
> > When I used condition(WHERE), the view produced about 67,000 rows. When
I
> > excluded the condition, I expected to have more rows but the result was
> > about 50,000 rows. I tried different ways but the condition produced
more
> > rows!
> > Whereas the views were nested, it was very difficult to trace the
problem.
> > It may help if I say some views used UNION ALL to concatenate some
result.
> > I must find the source of problem. Does anybody have any idea/experience
> > like that?
> > Any help would be greatly appreciated.
> > Leila
> >
> >
> >|||I did see couple of issues on my server, where in I do a select * with and
without a where clause. The result was different. But, I did this on a
table. The problem was found to be index corruption. You might want to
check into that.
-Nags
"Leila" <leilas@.hotpop.com> wrote in message
news:#w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of
them
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>
Today I encountered with a view that references other views and each of them
reference some other view. The problem began when I started querying this
view.
When I used condition(WHERE), the view produced about 67,000 rows. When I
excluded the condition, I expected to have more rows but the result was
about 50,000 rows. I tried different ways but the condition produced more
rows!
Whereas the views were nested, it was very difficult to trace the problem.
It may help if I say some views used UNION ALL to concatenate some result.
I must find the source of problem. Does anybody have any idea/experience
like that?
Any help would be greatly appreciated.
LeilaI've had problems with nested views where the server has taken hours to get a
query plan (but that developer nested to quite a few levels) and heard of the
sort of thing you are seeing.
have you rebooted the server and updated statistics?
Apart from that I would suggest getting rid of the views and putting the
logic in stored procedures. You'll probably find it faster and certainly
easier to maintain and troubleshoot.
"Leila" wrote:
> Hi,
> Today I encountered with a view that references other views and each of them
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>
>|||In addition to Nigel's commends: Are you using any old-style outer joins in any of the view
definitions? (*=)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <leilas@.hotpop.com> wrote in message news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of them reference some other
> view. The problem began when I started querying this view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I excluded the condition,
> I expected to have more rows but the result was about 50,000 rows. I tried different ways but the
> condition produced more rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience like that?
> Any help would be greatly appreciated.
> Leila
>|||Thanks Tibor,
I'm not sure if I have undertood your meaning.
Do you mean old style joins like:
select t1.*,t2.* from t1,t2 where t1.id=t2.id
?
Leila
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> In addition to Nigel's commends: Are you using any old-style outer joins
in any of the view
> definitions? (*=)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <leilas@.hotpop.com> wrote in message
news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> > Today I encountered with a view that references other views and each of
them reference some other
> > view. The problem began when I started querying this view.
> > When I used condition(WHERE), the view produced about 67,000 rows. When
I excluded the condition,
> > I expected to have more rows but the result was about 50,000 rows. I
tried different ways but the
> > condition produced more rows!
> > Whereas the views were nested, it was very difficult to trace the
problem.
> > It may help if I say some views used UNION ALL to concatenate some
result.
> > I must find the source of problem. Does anybody have any idea/experience
like that?
> > Any help would be greatly appreciated.
> > Leila
> >
>|||Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
> > Hi,
> > Today I encountered with a view that references other views and each of
them
> > reference some other view. The problem began when I started querying
this
> > view.
> > When I used condition(WHERE), the view produced about 67,000 rows. When
I
> > excluded the condition, I expected to have more rows but the result was
> > about 50,000 rows. I tried different ways but the condition produced
more
> > rows!
> > Whereas the views were nested, it was very difficult to trace the
problem.
> > It may help if I say some views used UNION ALL to concatenate some
result.
> > I must find the source of problem. Does anybody have any idea/experience
> > like that?
> > Any help would be greatly appreciated.
> > Leila
> >
> >
> >|||Leila,
I'm referring to old style *outer* joins, such as:
SELECT t1.col1, t2.colZ
FROM t1, t2
WHERE t1.c1 *= t2.c1
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <Leilas@.hotpop.com> wrote in message news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Thanks Tibor,
> I'm not sure if I have undertood your meaning.
> Do you mean old style joins like:
> select t1.*,t2.* from t1,t2 where t1.id=t2.id
> ?
> Leila
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
>> In addition to Nigel's commends: Are you using any old-style outer joins
> in any of the view
>> definitions? (*=)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> > Today I encountered with a view that references other views and each of
> them reference some other
>> > view. The problem began when I started querying this view.
>> > When I used condition(WHERE), the view produced about 67,000 rows. When
> I excluded the condition,
>> > I expected to have more rows but the result was about 50,000 rows. I
> tried different ways but the
>> > condition produced more rows!
>> > Whereas the views were nested, it was very difficult to trace the
> problem.
>> > It may help if I say some views used UNION ALL to concatenate some
> result.
>> > I must find the source of problem. Does anybody have any idea/experience
> like that?
>> > Any help would be greatly appreciated.
>> > Leila
>> >
>>
>|||No, but there're ansi style outer joins.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Leila,
> I'm referring to old style *outer* joins, such as:
> SELECT t1.col1, t2.colZ
> FROM t1, t2
> WHERE t1.c1 *= t2.c1
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
> > Thanks Tibor,
> > I'm not sure if I have undertood your meaning.
> > Do you mean old style joins like:
> > select t1.*,t2.* from t1,t2 where t1.id=t2.id
> > ?
> >
> > Leila
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> >> In addition to Nigel's commends: Are you using any old-style outer
joins
> > in any of the view
> >> definitions? (*=)
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> > news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> >> > Hi,
> >> > Today I encountered with a view that references other views and each
of
> > them reference some other
> >> > view. The problem began when I started querying this view.
> >> > When I used condition(WHERE), the view produced about 67,000 rows.
When
> > I excluded the condition,
> >> > I expected to have more rows but the result was about 50,000 rows. I
> > tried different ways but the
> >> > condition produced more rows!
> >> > Whereas the views were nested, it was very difficult to trace the
> > problem.
> >> > It may help if I say some views used UNION ALL to concatenate some
> > result.
> >> > I must find the source of problem. Does anybody have any
idea/experience
> > like that?
> >> > Any help would be greatly appreciated.
> >> > Leila
> >> >
> >>
> >>
> >
> >
>|||Leila,
can you post a concise DDL statements to replicate the issue, if possible..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Leila" <Leilas@.hotpop.com> wrote in message
news:#u0J0dP8EHA.2572@.tk2msftngp13.phx.gbl...
> No, but there're ansi style outer joins.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> > Leila,
> >
> > I'm referring to old style *outer* joins, such as:
> >
> > SELECT t1.col1, t2.colZ
> > FROM t1, t2
> > WHERE t1.c1 *= t2.c1
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > http://www.sqlug.se/
> >
> >
> > "Leila" <Leilas@.hotpop.com> wrote in message
> news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
> > > Thanks Tibor,
> > > I'm not sure if I have undertood your meaning.
> > > Do you mean old style joins like:
> > > select t1.*,t2.* from t1,t2 where t1.id=t2.id
> > > ?
> > >
> > > Leila
> > >
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> in
> > > message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> > >> In addition to Nigel's commends: Are you using any old-style outer
> joins
> > > in any of the view
> > >> definitions? (*=)
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >> http://www.sqlug.se/
> > >>
> > >>
> > >> "Leila" <leilas@.hotpop.com> wrote in message
> > > news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> > >> > Hi,
> > >> > Today I encountered with a view that references other views and
each
> of
> > > them reference some other
> > >> > view. The problem began when I started querying this view.
> > >> > When I used condition(WHERE), the view produced about 67,000 rows.
> When
> > > I excluded the condition,
> > >> > I expected to have more rows but the result was about 50,000 rows.
I
> > > tried different ways but the
> > >> > condition produced more rows!
> > >> > Whereas the views were nested, it was very difficult to trace the
> > > problem.
> > >> > It may help if I say some views used UNION ALL to concatenate some
> > > result.
> > >> > I must find the source of problem. Does anybody have any
> idea/experience
> > > like that?
> > >> > Any help would be greatly appreciated.
> > >> > Leila
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>|||Sounds like someone coded that ID to be VARCHAR instead of INT. Chances are
WHERE CustomerID = 100 is NULL. Try it with WHERE CustomerID LIKE '100%'
Even though I'd like to, I will refrain from beating up on you for using
nested VIEWS; hwoever, if you must nest, then you might consider
materializing the lower level ones. SQL Server calls these INDEXED VIEWS
but the effect is the same.
Sincerely,
Anthony Thomas
"Leila" <Leilas@.hotpop.com> wrote in message
news:ORQCBiO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
> > Hi,
> > Today I encountered with a view that references other views and each of
them
> > reference some other view. The problem began when I started querying
this
> > view.
> > When I used condition(WHERE), the view produced about 67,000 rows. When
I
> > excluded the condition, I expected to have more rows but the result was
> > about 50,000 rows. I tried different ways but the condition produced
more
> > rows!
> > Whereas the views were nested, it was very difficult to trace the
problem.
> > It may help if I say some views used UNION ALL to concatenate some
result.
> > I must find the source of problem. Does anybody have any idea/experience
> > like that?
> > Any help would be greatly appreciated.
> > Leila
> >
> >
> >|||I did see couple of issues on my server, where in I do a select * with and
without a where clause. The result was different. But, I did this on a
table. The problem was found to be index corruption. You might want to
check into that.
-Nags
"Leila" <leilas@.hotpop.com> wrote in message
news:#w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of
them
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>
Subscribe to:
Posts (Atom)