I have seen all the other SSL related problems, and I don't think I am
having the same issue...but I am having something similar.
I have Reporting Services SP1 installed on the same machine as SQL
Server. IIS is also on this machine. The machine is not part of a
domain and its full name is SQL2. I have an internal CA within my
domain that I used to issue a certificate to SQL2 under the name SQL2.
I have set up my reports to deply to https://SQL2/ReportServer
Reports deploy fine and with no error. I can view the report by going
to https://SQL2/ReportManager or http://SQL2/ReportManager and it takes
parameters and returns data, exactly as it does in debug mode in
vs.net.
What doesn't work:
If I go to the report properties tab (SSL or not) within the report I
get the following error:
The underlying connection was closed: Could not establish trust
relationship with remote server.
If I try to go to the data source instead of the report (SSL or not),
I get the following error:
The underlying connection was closed: Could not establish trust
relationship with remote server.
If I try to export the report to another format for download (SSL or
not), I get a file not found error.
When I search for the "The underlying connection was closed: Could not
establish trust relationship with remote server." error message, it
seems like everyone who gets that cannot deploy the report or view it.
I can deploy and view with no problems, but it seems like I can't do
anything else.
Any ideas?
Thanks in advance.
ErikI'm experiencing the exact same problems. I'm also receiving the error
when saving a new report subscription. Any help would be greatly
appreciated.
Thanks
Chad
cybermud wrote:
> I have seen all the other SSL related problems, and I don't think I am
> having the same issue...but I am having something similar.
> I have Reporting Services SP1 installed on the same machine as SQL
> Server. IIS is also on this machine. The machine is not part of a
> domain and its full name is SQL2. I have an internal CA within my
> domain that I used to issue a certificate to SQL2 under the name SQL2.
> I have set up my reports to deply to https://SQL2/ReportServer
> Reports deploy fine and with no error. I can view the report by going
> to https://SQL2/ReportManager or http://SQL2/ReportManager and it takes
> parameters and returns data, exactly as it does in debug mode in
> vs.net.
> What doesn't work:
> If I go to the report properties tab (SSL or not) within the report I
> get the following error:
> The underlying connection was closed: Could not establish trust
> relationship with remote server.
> If I try to go to the data source instead of the report (SSL or not),
> I get the following error:
> The underlying connection was closed: Could not establish trust
> relationship with remote server.
> If I try to export the report to another format for download (SSL or
> not), I get a file not found error.
> When I search for the "The underlying connection was closed: Could not
> establish trust relationship with remote server." error message, it
> seems like everyone who gets that cannot deploy the report or view it.
> I can deploy and view with no problems, but it seems like I can't do
> anything else.
> Any ideas?
> Thanks in advance.
> Erik
>|||I tried giving the site a proper DNS name, sql2.domain.com, and
generated a new SSL certificate for that name, and I am still having
all the same issues. Reports work fine, but I cannot view a data
source or go to the report properties. I still get the same "The
underlying connection was closed: Could not establish trust
relationship with remote server." error message. Additionally, like
Chad, I cannot save a subscription. I get the same "The underlying
connection was closed: Could not establish trust relationship with
remote server." error message. Has anyone else had a similar
situation?
Thanks,
Erik|||Chad:
I edited the RSReportServer.config file to tell Reporting Services not
to use SSL at all, then restarted the web site, and now I can view the
properties, view the data source, and export files. If you want to try
this, open the file in notepad, look for the following string:
<Add Key="SecureConnectionLevel" Value="0"/>
make sure value is 0. that means no SSL. I found this in the sample
chaper for Hitchhikers Guide To Reporting Services. Its somewhere out
there on the internet.
Erik|||Hi Erik,
I receive the error both on by development machine and the production
server. The production website has a fully qualified domain name with a
valid SSL certificate. My development machine has a test certificate I
created with Makecert.exe. Both of the machines have the exact same
symptoms.
I have changed the Security Level within the RSReportServer.config on my
development machine. With the option set to 0, everything seems to
work. If the Security Level is set to 1, I start receiving the
underlying connection error. I also received the security warning when
rendering reports. If the level is set to 2, the just receive the
underlying connection error. If the level is set to 3, the site stops
working all together. I wish could just set the level to 0, but I can't
on the production site.
I did some research on the error itself. It seems to be a general
problem with the SOAP protocol and SSL. Since Web Services use SOAP and
SQL Reporting uses Web Services, I'm wonder if thats the primary cause
for the underlying connection error.
Thanks
Chad
cybermud wrote:
> Chad:
> I edited the RSReportServer.config file to tell Reporting Services not
> to use SSL at all, then restarted the web site, and now I can view the
> properties, view the data source, and export files. If you want to try
> this, open the file in notepad, look for the following string:
> <Add Key="SecureConnectionLevel" Value="0"/>
> make sure value is 0. that means no SSL. I found this in the sample
> chaper for Hitchhikers Guide To Reporting Services. Its somewhere out
> there on the internet.
> Erik
>|||Chad:
No SSL will be a no-go for me as well, but its nice to see some of the
other options. I wonder if the problem has to do with my SQL Server
not being a member of the domain that the certificate authority that
issued its certificate is a member of. Anyone...?
Erik|||This is not a valid solution to this problem.
Setting SecureConnectionLevel = 0 makes the report server NOT REQUIRE SSL.
As such, any links to the report server are made over an unprotected SSL
connection.
Your more likely solution is to edit the Report Manager configuration file
to change the REPORTSERVERURL property to be https://sql2/reportserver.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"cybermud" <cybermud@.gmail.com> wrote in message
news:1107797287.951689.55530@.g14g2000cwa.googlegroups.com...
> Chad:
> I edited the RSReportServer.config file to tell Reporting Services not
> to use SSL at all, then restarted the web site, and now I can view the
> properties, view the data source, and export files. If you want to try
> this, open the file in notepad, look for the following string:
> <Add Key="SecureConnectionLevel" Value="0"/>
> make sure value is 0. that means no SSL. I found this in the sample
> chaper for Hitchhikers Guide To Reporting Services. Its somewhere out
> there on the internet.
> Erik
>|||Lukasz:
Thank you for your reply. I have tried changing this value previosly
and it has had no effect on resolving my issue. I tried it again for
good measure, setting the REPORTSERVERURL to https://sql2/reportserver,
then changed the SECURECONNECTIONLEVEL to 3. Upon restarting IIS, I
cannot even view report manager, as I get an error of "The underlying
connection was closed: Could not establish trust relationship with
remote server."
If I change the SECURECONNECTIONLEVEL to 2, I can connect and view
reports, but I cannot view daa source properties, export reports, view
report properties, or save a new subscription.
Could this problem be because the CA that I used to issue the SSL
certificate is a domain member, and SQL2 is not a domain member? Would
joining SQL2 to my domain solve the issue?
Thanks in advance for any insight you may be able to provide.|||OK, I joined SQL2 to my domain.
Everything now works great with the SECURECONNECTIONLEVEL set to 3,
except I still can't export reports to any other format unless
SECURECONNECTIONLEVEL is set to 0. Any ideas?|||Hmm... so the CA was on the domain, but your SQL2 box was not on the domain?
What URL were you using to connect to the reportserver on this box?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"cybermud" <cybermud@.gmail.com> wrote in message
news:1107893981.484348.53620@.g14g2000cwa.googlegroups.com...
> OK, I joined SQL2 to my domain.
> Everything now works great with the SECURECONNECTIONLEVEL set to 3,
> except I still can't export reports to any other format unless
> SECURECONNECTIONLEVEL is set to 0. Any ideas?
>|||Lukasz:
Yes, CA was in domain, SQL2 was not. Now SQL2 is in the domain.
I was using https://sql2/ReportServer to access the report server. Now
I am using https://sql2.domain.com/ReportServer to access report
server, and https://sql2.domain.com/ReportManager to access the report
manager. And I can only export to a file if the security parameter is
set to 0. Any other setting and it looks as if the file is never
generated...when I click on the export link, it does not show a file
name like it does when the security parameter is set at 0.|||Gang:
I was looking through my log files for Reporting Services, and I
noticed the following entries:
w3wp!library!c70!02/14/2005-12:09:03:: i INFO: Call to RenderNext(
'/Sales Reporting Project/Top 50 Market Report - Month' )
w3wp!chunks!c70!02/14/2005-12:09:03:: i INFO: ###
GetReportChunk('RenderingInfo_EXCEL', 2), chunk was not found!
this=236c9c34-01c8-4806-9d14-4c06b744d28a
w3wp!cache!c70!02/14/2005-12:09:04:: i INFO: Session live: /Sales
Reporting Project/Top 50 Market Report - Month
w3wp!webserver!c70!02/14/2005-12:09:04:: i INFO: Processed report.
Report='/Sales Reporting Project/Top 50 Market Report - Month',
Stream=''
w3wp!library!574!02/14/2005-12:09:20:: i INFO: Call to RenderNext(
'/Sales Reporting Project/Top 50 Market Report - Month' )
w3wp!cache!574!02/14/2005-12:09:20:: i INFO: Session live: /Sales
Reporting Project/Top 50 Market Report - Month
w3wp!webserver!574!02/14/2005-12:09:20:: i INFO: Processed report.
Report='/Sales Reporting Project/Top 50 Market Report - Month',
Stream=''
w3wp!library!c70!02/14/2005-12:09:34:: i INFO: Call to RenderNext(
'/Sales Reporting Project/Top 50 Market Report - Month' )
w3wp!chunks!c70!02/14/2005-12:09:34:: i INFO: ###
GetReportChunk('RenderingInfo_MHTML', 2), chunk was not found!
this=236c9c34-01c8-4806-9d14-4c06b744d28a
w3wp!cache!c70!02/14/2005-12:09:34:: i INFO: Session live: /Sales
Reporting Project/Top 50 Market Report - Month
w3wp!webserver!c70!02/14/2005-12:09:34:: i INFO: Processed report.
Report='/Sales Reporting Project/Top 50 Market Report - Month',
Stream=''
w3wp!library!1254!02/14/2005-12:09:40:: i INFO: Call to RenderNext(
'/Sales Reporting Project/Top 50 Market Report - Month' )
w3wp!chunks!1254!02/14/2005-12:09:40:: i INFO: ###
GetReportChunk('RenderingInfo_PDF', 2), chunk was not found!
this=236c9c34-01c8-4806-9d14-4c06b744d28a
w3wp!cache!1254!02/14/2005-12:09:40:: i INFO: Session live: /Sales
Reporting Project/Top 50 Market Report - Month
w3wp!webserver!1254!02/14/2005-12:09:40:: i INFO: Processed report.
Report='/Sales Reporting Project/Top 50 Market Report - Month',
Stream=''
It seems as if I get a "Chunk Not Found" error when I try to export to
any format with SSL enabled.
Has anybody else encountered this? Has anyone else encountered a "file
nto found" error when trying to export into any format from Report
Manager?
When I export in vs.net it works fine, and if I turn SSL off on the web
site it will export fine...but not with SSL on.|||Hi Erik,
Sorry the delay in check this thread. I have not been able to solve the
SSL problem. I was able to create subscriptions through SSL using the
SQL Reporting SOAP APIs. It's more work, because you have to code your
own interface, but it does work through SSL.
Thanks
Chad
cybermud wrote:
> Gang:
> I was looking through my log files for Reporting Services, and I
> noticed the following entries:
> w3wp!library!c70!02/14/2005-12:09:03:: i INFO: Call to RenderNext(
> '/Sales Reporting Project/Top 50 Market Report - Month' )
> w3wp!chunks!c70!02/14/2005-12:09:03:: i INFO: ###
> GetReportChunk('RenderingInfo_EXCEL', 2), chunk was not found!
> this=236c9c34-01c8-4806-9d14-4c06b744d28a
> w3wp!cache!c70!02/14/2005-12:09:04:: i INFO: Session live: /Sales
> Reporting Project/Top 50 Market Report - Month
> w3wp!webserver!c70!02/14/2005-12:09:04:: i INFO: Processed report.
> Report='/Sales Reporting Project/Top 50 Market Report - Month',
> Stream=''
> w3wp!library!574!02/14/2005-12:09:20:: i INFO: Call to RenderNext(
> '/Sales Reporting Project/Top 50 Market Report - Month' )
> w3wp!cache!574!02/14/2005-12:09:20:: i INFO: Session live: /Sales
> Reporting Project/Top 50 Market Report - Month
> w3wp!webserver!574!02/14/2005-12:09:20:: i INFO: Processed report.
> Report='/Sales Reporting Project/Top 50 Market Report - Month',
> Stream=''
> w3wp!library!c70!02/14/2005-12:09:34:: i INFO: Call to RenderNext(
> '/Sales Reporting Project/Top 50 Market Report - Month' )
> w3wp!chunks!c70!02/14/2005-12:09:34:: i INFO: ###
> GetReportChunk('RenderingInfo_MHTML', 2), chunk was not found!
> this=236c9c34-01c8-4806-9d14-4c06b744d28a
> w3wp!cache!c70!02/14/2005-12:09:34:: i INFO: Session live: /Sales
> Reporting Project/Top 50 Market Report - Month
> w3wp!webserver!c70!02/14/2005-12:09:34:: i INFO: Processed report.
> Report='/Sales Reporting Project/Top 50 Market Report - Month',
> Stream=''
> w3wp!library!1254!02/14/2005-12:09:40:: i INFO: Call to RenderNext(
> '/Sales Reporting Project/Top 50 Market Report - Month' )
> w3wp!chunks!1254!02/14/2005-12:09:40:: i INFO: ###
> GetReportChunk('RenderingInfo_PDF', 2), chunk was not found!
> this=236c9c34-01c8-4806-9d14-4c06b744d28a
> w3wp!cache!1254!02/14/2005-12:09:40:: i INFO: Session live: /Sales
> Reporting Project/Top 50 Market Report - Month
> w3wp!webserver!1254!02/14/2005-12:09:40:: i INFO: Processed report.
> Report='/Sales Reporting Project/Top 50 Market Report - Month',
> Stream=''
> It seems as if I get a "Chunk Not Found" error when I try to export to
> any format with SSL enabled.
> Has anybody else encountered this? Has anyone else encountered a "file
> nto found" error when trying to export into any format from Report
> Manager?
> When I export in vs.net it works fine, and if I turn SSL off on the web
> site it will export fine...but not with SSL on.
>
Showing posts with label similar. Show all posts
Showing posts with label similar. Show all posts
Monday, March 19, 2012
Another Slow Execution Plan with sp_prepare
We are having a similar problem. We have a 3rd party application that uses
sp_prepare and then executes it. The problem is the query plan from the
sp_prepare statement is different than if you run the statement using
sp_execute or sp_executesql. I run the following statements:
declare @.P1 int
exec sp_prepare @.P1 output, N'@.P1 bigint,@.P2 bigint,@.P3 bigint,@.P4
bigint,@.P5 bigint,@.P6 bigint,@.P7 bigint,@.P8 bigint', 'SELECT SHAPE
,S_.eminx,S_.eminy,S_.emaxx,S_.emaxy ,SHAPE.fid F_fid,SHAPE.numofpts
F_numofpts,SHAPE.entity F_entity,SHAPE.points F_points FROM (SELECT DISTINC
T
sp_fid,eminx,eminy,emaxx,emaxy FROM SDE.SDE.s162 SP_ WHERE SP_.gx >= @.P1 AN
D
SP_.gx <= @.P2 AND SP_.gy >= @.P3 AND SP_.gy <= @.P4 AND SP_.eminx <= @.P5 AND
SP_.eminy <= @.P6 AND SP_.emaxx >= @.P7 AND SP_.emaxy >= @.P8 ) S_
,SDE.SDE.ENTORDERLINESEGMENT, SDE.SDE.f162 SHAPE WHERE S_.sp_fid = SHAPE.fi
d
AND SDE.SDE.ENTORDERLINESEGMENT.SHAPE = S_.sp_fid AND (( ORDERID in (16320,
16825) ))', 1 select @.P1
exec sp_execute 1, 166, 169, 219, 224, 90269119, 119480870, 88777840,
117193071
This takes almost a minute to return.
Then I run this statement:
declare @.P1 int
exec sp_prepare @.P1 output, N'@.P1 bigint,@.P2 bigint,@.P3 bigint,@.P4
bigint,@.P5 bigint,@.P6 bigint,@.P7 bigint,@.P8 bigint', 'SELECT SHAPE
,S_.eminx,S_.eminy,S_.emaxx,S_.emaxy ,SHAPE.fid F_fid,SHAPE.numofpts
F_numofpts,SHAPE.entity F_entity,SHAPE.points F_points FROM (SELECT DISTINC
T
sp_fid,eminx,eminy,emaxx,emaxy FROM SDE.SDE.s162 SP_ WHERE SP_.gx >= @.P1 AN
D
SP_.gx <= @.P2 AND SP_.gy >= @.P3 AND SP_.gy <= @.P4 AND SP_.eminx <= @.P5 AND
SP_.eminy <= @.P6 AND SP_.emaxx >= @.P7 AND SP_.emaxy >= @.P8 ) S_
,SDE.SDE.ENTORDERLINESEGMENT, SDE.SDE.f162 SHAPE WHERE S_.sp_fid = SHAPE.fi
d
AND SDE.SDE.ENTORDERLINESEGMENT.SHAPE = S_.sp_fid AND (( ORDERID in (16320,
16825) ))', 1 select @.P1
DBCC Freeproccache
exec sp_execute 1, 166, 169, 219, 224, 90269119, 119480870, 88777840,
117193071
This returns in under 1 second. The only difference is the freeproccache.
The first sp_execute is using the store query plan from cache. The second i
s
forced to compile a new execution plan which is much faster.
Does anyone know why the sp_prepare query execution plan would be slower?
Shouldn't it create the same plan?
Also, how did you change sp_prepare to use varchar instead of nvarchar? I
get an error stating the sp_prepare expects @.statement of type
ntext/nchar/nvarchar.
Thanks for any help.
Doug Matney
"lorinda" wrote:
> This makes sense. Looks like I will have to find a work around. Thanks
> again for your help!
>dmatney (dmatney@.discussions.microsoft.com) writes:
> We are having a similar problem. We have a 3rd party application that
> uses sp_prepare and then executes it. The problem is the query plan
> from the sp_prepare statement is different than if you run the statement
> using sp_execute or sp_executesql. I run the following statements:
>...
> This returns in under 1 second. The only difference is the
> freeproccache. The first sp_execute is using the store query plan from
> cache. The second is forced to compile a new execution plan which is
> much faster.
> Does anyone know why the sp_prepare query execution plan would be slower?
> Shouldn't it create the same plan?
Since I have never understood the point with sp_prepare, or what it really
achieves, this had me stumped at first, but I think I know the answer.
SQL Server is fond of parameter sniffing. This means that when you
run something that has parameters, be that a stored procedure or
sp_executesql, it looks at the parameter values and take these as
guidance for the plan.
But when you run sp_prepare there are no actual parameter values. Still
SQL Server builds a plan this point, using standard assumptions. Your
query includs a lot of >=. I believe the standard assumption here is
a 30% hit-rate. This is far above the limit where a non-clustered index
is deemed to be more expensive than a table scan.
When you flush the cache, there is no plan for sp_execute to use, so
a new plan has to be created, and since the parameter values are now
available, the optimizer can "sniff" them, and build a plan that better
fits the actual values. Presumably, the input values are close to the
edges.
Since this is from a third-party app, I guess your options to address
this are limited. You could investigate if you could make any of the
involved indexes clustered, but assuming that there is a clustered index
already, this could have ramifications elsewhere in the application.
If you are on SQL 2005, you could add a plan guide, which is quite an
advanced exercise.
> Also, how did you change sp_prepare to use varchar instead of nvarchar? I
> get an error stating the sp_prepare expects @.statement of type
> ntext/nchar/nvarchar.
I guess you don't. It's the same with sp_executesql. The parameter is
ntext (nvarchar(MAX) in SQL 2005), and since this is a built-in stored
procedure, there is no implicit conversion from varchar.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks. I think you are correct about the parameter sniffing. I have tried
creating a plan guide using a Recompile hint and it works for this particula
r
sp_prepare but since the application doesn't use parameters for all values (
the OrderID in clause uses real values) I can't get it to work every time.
I'm going to do a little more research and see if we can't convince the
application developer to use a different method. Thanks again for your help
.
"Erland Sommarskog" wrote:
> dmatney (dmatney@.discussions.microsoft.com) writes:
> Since I have never understood the point with sp_prepare, or what it really
> achieves, this had me stumped at first, but I think I know the answer.
> SQL Server is fond of parameter sniffing. This means that when you
> run something that has parameters, be that a stored procedure or
> sp_executesql, it looks at the parameter values and take these as
> guidance for the plan.
> But when you run sp_prepare there are no actual parameter values. Still
> SQL Server builds a plan this point, using standard assumptions. Your
> query includs a lot of >=. I believe the standard assumption here is
> a 30% hit-rate. This is far above the limit where a non-clustered index
> is deemed to be more expensive than a table scan.
> When you flush the cache, there is no plan for sp_execute to use, so
> a new plan has to be created, and since the parameter values are now
> available, the optimizer can "sniff" them, and build a plan that better
> fits the actual values. Presumably, the input values are close to the
> edges.
> Since this is from a third-party app, I guess your options to address
> this are limited. You could investigate if you could make any of the
> involved indexes clustered, but assuming that there is a clustered index
> already, this could have ramifications elsewhere in the application.
> If you are on SQL 2005, you could add a plan guide, which is quite an
> advanced exercise.
>
> I guess you don't. It's the same with sp_executesql. The parameter is
> ntext (nvarchar(MAX) in SQL 2005), and since this is a built-in stored
> procedure, there is no implicit conversion from varchar.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>
sp_prepare and then executes it. The problem is the query plan from the
sp_prepare statement is different than if you run the statement using
sp_execute or sp_executesql. I run the following statements:
declare @.P1 int
exec sp_prepare @.P1 output, N'@.P1 bigint,@.P2 bigint,@.P3 bigint,@.P4
bigint,@.P5 bigint,@.P6 bigint,@.P7 bigint,@.P8 bigint', 'SELECT SHAPE
,S_.eminx,S_.eminy,S_.emaxx,S_.emaxy ,SHAPE.fid F_fid,SHAPE.numofpts
F_numofpts,SHAPE.entity F_entity,SHAPE.points F_points FROM (SELECT DISTINC
T
sp_fid,eminx,eminy,emaxx,emaxy FROM SDE.SDE.s162 SP_ WHERE SP_.gx >= @.P1 AN
D
SP_.gx <= @.P2 AND SP_.gy >= @.P3 AND SP_.gy <= @.P4 AND SP_.eminx <= @.P5 AND
SP_.eminy <= @.P6 AND SP_.emaxx >= @.P7 AND SP_.emaxy >= @.P8 ) S_
,SDE.SDE.ENTORDERLINESEGMENT, SDE.SDE.f162 SHAPE WHERE S_.sp_fid = SHAPE.fi
d
AND SDE.SDE.ENTORDERLINESEGMENT.SHAPE = S_.sp_fid AND (( ORDERID in (16320,
16825) ))', 1 select @.P1
exec sp_execute 1, 166, 169, 219, 224, 90269119, 119480870, 88777840,
117193071
This takes almost a minute to return.
Then I run this statement:
declare @.P1 int
exec sp_prepare @.P1 output, N'@.P1 bigint,@.P2 bigint,@.P3 bigint,@.P4
bigint,@.P5 bigint,@.P6 bigint,@.P7 bigint,@.P8 bigint', 'SELECT SHAPE
,S_.eminx,S_.eminy,S_.emaxx,S_.emaxy ,SHAPE.fid F_fid,SHAPE.numofpts
F_numofpts,SHAPE.entity F_entity,SHAPE.points F_points FROM (SELECT DISTINC
T
sp_fid,eminx,eminy,emaxx,emaxy FROM SDE.SDE.s162 SP_ WHERE SP_.gx >= @.P1 AN
D
SP_.gx <= @.P2 AND SP_.gy >= @.P3 AND SP_.gy <= @.P4 AND SP_.eminx <= @.P5 AND
SP_.eminy <= @.P6 AND SP_.emaxx >= @.P7 AND SP_.emaxy >= @.P8 ) S_
,SDE.SDE.ENTORDERLINESEGMENT, SDE.SDE.f162 SHAPE WHERE S_.sp_fid = SHAPE.fi
d
AND SDE.SDE.ENTORDERLINESEGMENT.SHAPE = S_.sp_fid AND (( ORDERID in (16320,
16825) ))', 1 select @.P1
DBCC Freeproccache
exec sp_execute 1, 166, 169, 219, 224, 90269119, 119480870, 88777840,
117193071
This returns in under 1 second. The only difference is the freeproccache.
The first sp_execute is using the store query plan from cache. The second i
s
forced to compile a new execution plan which is much faster.
Does anyone know why the sp_prepare query execution plan would be slower?
Shouldn't it create the same plan?
Also, how did you change sp_prepare to use varchar instead of nvarchar? I
get an error stating the sp_prepare expects @.statement of type
ntext/nchar/nvarchar.
Thanks for any help.
Doug Matney
"lorinda" wrote:
> This makes sense. Looks like I will have to find a work around. Thanks
> again for your help!
>dmatney (dmatney@.discussions.microsoft.com) writes:
> We are having a similar problem. We have a 3rd party application that
> uses sp_prepare and then executes it. The problem is the query plan
> from the sp_prepare statement is different than if you run the statement
> using sp_execute or sp_executesql. I run the following statements:
>...
> This returns in under 1 second. The only difference is the
> freeproccache. The first sp_execute is using the store query plan from
> cache. The second is forced to compile a new execution plan which is
> much faster.
> Does anyone know why the sp_prepare query execution plan would be slower?
> Shouldn't it create the same plan?
Since I have never understood the point with sp_prepare, or what it really
achieves, this had me stumped at first, but I think I know the answer.
SQL Server is fond of parameter sniffing. This means that when you
run something that has parameters, be that a stored procedure or
sp_executesql, it looks at the parameter values and take these as
guidance for the plan.
But when you run sp_prepare there are no actual parameter values. Still
SQL Server builds a plan this point, using standard assumptions. Your
query includs a lot of >=. I believe the standard assumption here is
a 30% hit-rate. This is far above the limit where a non-clustered index
is deemed to be more expensive than a table scan.
When you flush the cache, there is no plan for sp_execute to use, so
a new plan has to be created, and since the parameter values are now
available, the optimizer can "sniff" them, and build a plan that better
fits the actual values. Presumably, the input values are close to the
edges.
Since this is from a third-party app, I guess your options to address
this are limited. You could investigate if you could make any of the
involved indexes clustered, but assuming that there is a clustered index
already, this could have ramifications elsewhere in the application.
If you are on SQL 2005, you could add a plan guide, which is quite an
advanced exercise.
> Also, how did you change sp_prepare to use varchar instead of nvarchar? I
> get an error stating the sp_prepare expects @.statement of type
> ntext/nchar/nvarchar.
I guess you don't. It's the same with sp_executesql. The parameter is
ntext (nvarchar(MAX) in SQL 2005), and since this is a built-in stored
procedure, there is no implicit conversion from varchar.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks. I think you are correct about the parameter sniffing. I have tried
creating a plan guide using a Recompile hint and it works for this particula
r
sp_prepare but since the application doesn't use parameters for all values (
the OrderID in clause uses real values) I can't get it to work every time.
I'm going to do a little more research and see if we can't convince the
application developer to use a different method. Thanks again for your help
.
"Erland Sommarskog" wrote:
> dmatney (dmatney@.discussions.microsoft.com) writes:
> Since I have never understood the point with sp_prepare, or what it really
> achieves, this had me stumped at first, but I think I know the answer.
> SQL Server is fond of parameter sniffing. This means that when you
> run something that has parameters, be that a stored procedure or
> sp_executesql, it looks at the parameter values and take these as
> guidance for the plan.
> But when you run sp_prepare there are no actual parameter values. Still
> SQL Server builds a plan this point, using standard assumptions. Your
> query includs a lot of >=. I believe the standard assumption here is
> a 30% hit-rate. This is far above the limit where a non-clustered index
> is deemed to be more expensive than a table scan.
> When you flush the cache, there is no plan for sp_execute to use, so
> a new plan has to be created, and since the parameter values are now
> available, the optimizer can "sniff" them, and build a plan that better
> fits the actual values. Presumably, the input values are close to the
> edges.
> Since this is from a third-party app, I guess your options to address
> this are limited. You could investigate if you could make any of the
> involved indexes clustered, but assuming that there is a clustered index
> already, this could have ramifications elsewhere in the application.
> If you are on SQL 2005, you could add a plan guide, which is quite an
> advanced exercise.
>
> I guess you don't. It's the same with sp_executesql. The parameter is
> ntext (nvarchar(MAX) in SQL 2005), and since this is a built-in stored
> procedure, there is no implicit conversion from varchar.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>
Sunday, March 11, 2012
Another query help question
Similar to my other question, but the counting is different. Thank you
again for your help.
CREATE TABLE [dbo].[Tracking] (
[key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
insert into tracking VALUES ('EVENT','verbal aggression')
insert into tracking VALUES ('EVENT','peer')
insert into tracking VALUES ('EVENT','bad behavior')
insert into tracking VALUES ('EVENT','other')
insert into tracking VALUES ('PRELIM','Loud noise')
insert into tracking VALUES ('PRELIM','agitation')
insert into tracking VALUES ('PRELIM','schedule change')
insert into tracking VALUES ('PRELIM','meal time')
CREATE TABLE [dbo].[Tracking_DATA] (
[ID_] [int] IDENTITY (1, 1) NOT NULL ,
[bts_ID_] [int] NULL ,
[key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into tracking_DATA VALUES (1, 'EVENT', 'other')
insert into tracking_DATA VALUES (1, 'EVENT', 'bad behavior')
insert into tracking_DATA VALUES (1, 'PRELIM', 'Loud noise')
insert into tracking_DATA VALUES (1, 'PRELIM', 'agitation')
insert into tracking_DATA VALUES (2, 'EVENT', 'other')
insert into tracking_DATA VALUES (2, 'EVENT', 'verbal aggression')
insert into tracking_DATA VALUES (2, 'PRELIM', 'Loud noise')
event prelim count
other loud noise 2
other agitation 1
bad behavior loud noise 1
bad behavior agitation 1
verbal aggression loud noise 1
peer <BLANK> 0Hi
Can you say how the information is grouped to get these values?
John
"Jack" wrote:
> Similar to my other question, but the counting is different. Thank you
> again for your help.
> CREATE TABLE [dbo].[Tracking] (
> [key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
> insert into tracking VALUES ('EVENT','verbal aggression')
> insert into tracking VALUES ('EVENT','peer')
> insert into tracking VALUES ('EVENT','bad behavior')
> insert into tracking VALUES ('EVENT','other')
> insert into tracking VALUES ('PRELIM','Loud noise')
> insert into tracking VALUES ('PRELIM','agitation')
> insert into tracking VALUES ('PRELIM','schedule change')
> insert into tracking VALUES ('PRELIM','meal time')
>
> CREATE TABLE [dbo].[Tracking_DATA] (
> [ID_] [int] IDENTITY (1, 1) NOT NULL ,
> [bts_ID_] [int] NULL ,
> [key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into tracking_DATA VALUES (1, 'EVENT', 'other')
> insert into tracking_DATA VALUES (1, 'EVENT', 'bad behavior')
> insert into tracking_DATA VALUES (1, 'PRELIM', 'Loud noise')
> insert into tracking_DATA VALUES (1, 'PRELIM', 'agitation')
> insert into tracking_DATA VALUES (2, 'EVENT', 'other')
> insert into tracking_DATA VALUES (2, 'EVENT', 'verbal aggression')
> insert into tracking_DATA VALUES (2, 'PRELIM', 'Loud noise')
>
> event prelim count
> other loud noise 2
> other agitation 1
> bad behavior loud noise 1
> bad behavior agitation 1
> verbal aggression loud noise 1
> peer <BLANK> 0
>
>
again for your help.
CREATE TABLE [dbo].[Tracking] (
[key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
insert into tracking VALUES ('EVENT','verbal aggression')
insert into tracking VALUES ('EVENT','peer')
insert into tracking VALUES ('EVENT','bad behavior')
insert into tracking VALUES ('EVENT','other')
insert into tracking VALUES ('PRELIM','Loud noise')
insert into tracking VALUES ('PRELIM','agitation')
insert into tracking VALUES ('PRELIM','schedule change')
insert into tracking VALUES ('PRELIM','meal time')
CREATE TABLE [dbo].[Tracking_DATA] (
[ID_] [int] IDENTITY (1, 1) NOT NULL ,
[bts_ID_] [int] NULL ,
[key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into tracking_DATA VALUES (1, 'EVENT', 'other')
insert into tracking_DATA VALUES (1, 'EVENT', 'bad behavior')
insert into tracking_DATA VALUES (1, 'PRELIM', 'Loud noise')
insert into tracking_DATA VALUES (1, 'PRELIM', 'agitation')
insert into tracking_DATA VALUES (2, 'EVENT', 'other')
insert into tracking_DATA VALUES (2, 'EVENT', 'verbal aggression')
insert into tracking_DATA VALUES (2, 'PRELIM', 'Loud noise')
event prelim count
other loud noise 2
other agitation 1
bad behavior loud noise 1
bad behavior agitation 1
verbal aggression loud noise 1
peer <BLANK> 0Hi
Can you say how the information is grouped to get these values?
John
"Jack" wrote:
> Similar to my other question, but the counting is different. Thank you
> again for your help.
> CREATE TABLE [dbo].[Tracking] (
> [key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
> insert into tracking VALUES ('EVENT','verbal aggression')
> insert into tracking VALUES ('EVENT','peer')
> insert into tracking VALUES ('EVENT','bad behavior')
> insert into tracking VALUES ('EVENT','other')
> insert into tracking VALUES ('PRELIM','Loud noise')
> insert into tracking VALUES ('PRELIM','agitation')
> insert into tracking VALUES ('PRELIM','schedule change')
> insert into tracking VALUES ('PRELIM','meal time')
>
> CREATE TABLE [dbo].[Tracking_DATA] (
> [ID_] [int] IDENTITY (1, 1) NOT NULL ,
> [bts_ID_] [int] NULL ,
> [key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into tracking_DATA VALUES (1, 'EVENT', 'other')
> insert into tracking_DATA VALUES (1, 'EVENT', 'bad behavior')
> insert into tracking_DATA VALUES (1, 'PRELIM', 'Loud noise')
> insert into tracking_DATA VALUES (1, 'PRELIM', 'agitation')
> insert into tracking_DATA VALUES (2, 'EVENT', 'other')
> insert into tracking_DATA VALUES (2, 'EVENT', 'verbal aggression')
> insert into tracking_DATA VALUES (2, 'PRELIM', 'Loud noise')
>
> event prelim count
> other loud noise 2
> other agitation 1
> bad behavior loud noise 1
> bad behavior agitation 1
> verbal aggression loud noise 1
> peer <BLANK> 0
>
>
Sunday, February 19, 2012
Analyze LDF files MS SQL Server 2000
Anybody nows a tool to analyze LDF files in MS SQL Server 2000?
I mean, a tool that converts a LDF file in a set of SQL transactions?
(similar to dbtran in sybase)
I mean, a tool that converts a LDF file in a set of SQL transactions?
(similar to dbtran in sybase)
thanks!"Esteban" <ecastillo@.gmail.com> wrote in message
news:8f83567b.0406180752.1b24d5ff@.posting.google.c om...
> Anybody nows a tool to analyze LDF files in MS SQL Server 2000?
> I mean, a tool that converts a LDF file in a set of SQL transactions?
> (similar to dbtran in sybase)
> thanks!
There is nothing in MSSQL itself (except for some undocumented commands),
but there are third-party tools available, such as this one:
http://www.lumigent.com/
If you are trying to achieve something specific, you might want to post more
details of your goal, and someone may be able to suggest something.
Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<40d3fef1_2@.news.bluewin.ch>...
> "Esteban" <ecastillo@.gmail.com> wrote in message
> news:8f83567b.0406180752.1b24d5ff@.posting.google.c om...
> > Anybody nows a tool to analyze LDF files in MS SQL Server 2000?
> > I mean, a tool that converts a LDF file in a set of SQL transactions?
> > (similar to dbtran in sybase)
> > thanks!
> There is nothing in MSSQL itself (except for some undocumented commands),
> but there are third-party tools available, such as this one:
> http://www.lumigent.com/
> If you are trying to achieve something specific, you might want to post more
> details of your goal, and someone may be able to suggest something.
> Simon
Thanks Simon, I was trying this product, but I can't find an option
that shows me a list of SQL transactions made in the database ...
maybe I don't know how to use it.
For example, I want to know if a certain register was deleted from the
database.
thanks.|||Esteban (ecastillo@.gmail.com) writes:
> Thanks Simon, I was trying this product, but I can't find an option
> that shows me a list of SQL transactions made in the database ...
> maybe I don't know how to use it.
> For example, I want to know if a certain register was deleted from the
> database.
In the version I have, there is a "View DDL commands" under Browse, and it
seems to have the information of the kind you are looking for. There is
also a filter function.
Note that for Log Explorer to be really useful, you need to have the
database in FULL or BULK_LOGGED recovery mode.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Thursday, February 9, 2012
Analysis Services 2005 Processing Log
Analysis Services 2000 had a "processing log" that all processing activities could be logged to. Is there a similar capability in Analysis Services 2005? How do I enable it?
Thanks!
Keith Spitz, Software Engineer, Wall Street On Demand
I am not aware of an identical capacity in SSAS 2005, but there are a couple of options that would get you similar information.
If you want to capture errors there is an error log setting that you can use, but I know the processing log in AS 2000 used to capture a lot more information than just errors.|||You can define ErrorConfiguration object, which contains the path to the processing log or define ErrorConfiguration element if you are processing from DDL script. If you are processing from UI (Management Studio or BI Dev Studio), on processing dialog click on Change Settings and go to the Dimension(Partition) key error tab. Or in SSMS right click on the object, dimension for example, and choose properties->Select a page: Error Configuration.|||Where is the error log setting described above and in the MSDN docs?
Error Log
ErrorLog\ ErrorLogFileName
I don't see this in my Analysis Services Properties with the Advanced box checked. I'm hoping to get processing errors logged to a consistent location. I'm on SP2.
Thanks, David
Where is the error log setting described above and in the MSDN docs?
Error Log
ErrorLog\ ErrorLogFileName
I don't see this in my Analysis Services Properties with the Advanced box checked. I'm hoping to get processing errors logged to a consistent location. I'm on SP2.
Thanks, David
I have SP2 and I can't see this setting either, I can't remember if it was there previously. You can set this setting at a number of different levels and in the processing command itself. I don't know if setting it at the server level changes the default or if the server setting is used to seed new objects.
You can edit the server setting by editing the settings file, the properties window is basically showing you the settings from msmdsrv.ini which you can find at:
<Program Files>\Microsoft SQL Server\MSSQL.<x>\OLAP\Config
You can see these settings in there. This is just an xml file (inspite of it's .ini extension) - you should take a backup of this file if you do edit it, as if you make a mistake you may not be able to start the SSAS server.
|||> you should take a backup of this file if you do edit it, as if you make a mistake you may not be able to start the SSAS server
Actually, you would see that AS automatically backs up the last good known version of config file in the form of msmdsrv.bak file, so if something goes wrong with .ini, it has something to fall on. But taking backups is always a good idea - one can never trust software...
|||So what happened to all those error handling settings at server level ? Why have they gone from SQL Management Studio ? I got the same problem. Now after migrating to SP2 I see in my msdmsrv.ini that I have KeyErrors set on with fail on first error. However, my cubes don't behave that way during processing after migrating to SP2 - processing just continues after the first error, when it should not. This is a change in behaviour that seemed to happen with installation of SP2. Is anyone else experiencing this ? Any solution ?
|||The change here appears to be in the DISCOVER_XML_METADATA command which is no longer returning these properties - not sure why.
However these properties can be overridden at the object level and again in the actual processing command. So if you always want your cube/dimension etc to process with a particular error configuration you can set this up in BI Development Studio on the object(s) in question. You should also double check the object(s) and whatever is sending the processing command to make sure that they are not overriding the error configuration.
That said the default was (and rightly so in my opinion) to stop on the first error and it's a bit disturbing that anything would change this.
You are not by any chance working in a team where someone else may have altered these settings on the object(s) in question are you? I had this happen to me once and took me ages to figure out why the cube and fact table did not reconcile.
Analysis Services 2005 Processing Log
Analysis Services 2000 had a "processing log" that all processing activities could be logged to. Is there a similar capability in Analysis Services 2005? How do I enable it?
Thanks!
Keith Spitz, Software Engineer, Wall Street On Demand
I am not aware of an identical capacity in SSAS 2005, but there are a couple of options that would get you similar information.
If you want to capture errors there is an error log setting that you can use, but I know the processing log in AS 2000 used to capture a lot more information than just errors.|||You can define ErrorConfiguration object, which contains the path to the processing log or define ErrorConfiguration element if you are processing from DDL script. If you are processing from UI (Management Studio or BI Dev Studio), on processing dialog click on Change Settings and go to the Dimension(Partition) key error tab. Or in SSMS right click on the object, dimension for example, and choose properties->Select a page: Error Configuration.|||
Where is the error log setting described above and in the MSDN docs?
Error Log
ErrorLog\ ErrorLogFileName
I don't see this in my Analysis Services Properties with the Advanced box checked. I'm hoping to get processing errors logged to a consistent location. I'm on SP2.
Thanks, David
Where is the error log setting described above and in the MSDN docs?
Error Log
ErrorLog\ ErrorLogFileName
I don't see this in my Analysis Services Properties with the Advanced box checked. I'm hoping to get processing errors logged to a consistent location. I'm on SP2.
Thanks, David
I have SP2 and I can't see this setting either, I can't remember if it was there previously. You can set this setting at a number of different levels and in the processing command itself. I don't know if setting it at the server level changes the default or if the server setting is used to seed new objects.
You can edit the server setting by editing the settings file, the properties window is basically showing you the settings from msmdsrv.ini which you can find at:
<Program Files>\Microsoft SQL Server\MSSQL.<x>\OLAP\Config
You can see these settings in there. This is just an xml file (inspite of it's .ini extension) - you should take a backup of this file if you do edit it, as if you make a mistake you may not be able to start the SSAS server.
|||> you should take a backup of this file if you do edit it, as if you make a mistake you may not be able to start the SSAS server
Actually, you would see that AS automatically backs up the last good known version of config file in the form of msmdsrv.bak file, so if something goes wrong with .ini, it has something to fall on. But taking backups is always a good idea - one can never trust software...
|||So what happened to all those error handling settings at server level ? Why have they gone from SQL Management Studio ? I got the same problem. Now after migrating to SP2 I see in my msdmsrv.ini that I have KeyErrors set on with fail on first error. However, my cubes don't behave that way during processing after migrating to SP2 - processing just continues after the first error, when it should not. This is a change in behaviour that seemed to happen with installation of SP2. Is anyone else experiencing this ? Any solution ?
|||The change here appears to be in the DISCOVER_XML_METADATA command which is no longer returning these properties - not sure why.
However these properties can be overridden at the object level and again in the actual processing command. So if you always want your cube/dimension etc to process with a particular error configuration you can set this up in BI Development Studio on the object(s) in question. You should also double check the object(s) and whatever is sending the processing command to make sure that they are not overriding the error configuration.
That said the default was (and rightly so in my opinion) to stop on the first error and it's a bit disturbing that anything would change this.
You are not by any chance working in a team where someone else may have altered these settings on the object(s) in question are you? I had this happen to me once and took me ages to figure out why the cube and fact table did not reconcile.
Analysis Services 2005 Processing Log
Analysis Services 2000 had a "processing log" that all processing activities could be logged to. Is there a similar capability in Analysis Services 2005? How do I enable it?
Thanks!
Keith Spitz, Software Engineer, Wall Street On Demand
I am not aware of an identical capacity in SSAS 2005, but there are a couple of options that would get you similar information.
If you want to capture errors there is an error log setting that you can use, but I know the processing log in AS 2000 used to capture a lot more information than just errors.|||You can define ErrorConfiguration object, which contains the path to the processing log or define ErrorConfiguration element if you are processing from DDL script. If you are processing from UI (Management Studio or BI Dev Studio), on processing dialog click on Change Settings and go to the Dimension(Partition) key error tab. Or in SSMS right click on the object, dimension for example, and choose properties->Select a page: Error Configuration.|||
Where is the error log setting described above and in the MSDN docs?
Error Log
ErrorLog\ ErrorLogFileName
I don't see this in my Analysis Services Properties with the Advanced box checked. I'm hoping to get processing errors logged to a consistent location. I'm on SP2.
Thanks, David
Where is the error log setting described above and in the MSDN docs?
Error Log
ErrorLog\ ErrorLogFileName
I don't see this in my Analysis Services Properties with the Advanced box checked. I'm hoping to get processing errors logged to a consistent location. I'm on SP2.
Thanks, David
I have SP2 and I can't see this setting either, I can't remember if it was there previously. You can set this setting at a number of different levels and in the processing command itself. I don't know if setting it at the server level changes the default or if the server setting is used to seed new objects.
You can edit the server setting by editing the settings file, the properties window is basically showing you the settings from msmdsrv.ini which you can find at:
<Program Files>\Microsoft SQL Server\MSSQL.<x>\OLAP\Config
You can see these settings in there. This is just an xml file (inspite of it's .ini extension) - you should take a backup of this file if you do edit it, as if you make a mistake you may not be able to start the SSAS server.
|||> you should take a backup of this file if you do edit it, as if you make a mistake you may not be able to start the SSAS server
Actually, you would see that AS automatically backs up the last good known version of config file in the form of msmdsrv.bak file, so if something goes wrong with .ini, it has something to fall on. But taking backups is always a good idea - one can never trust software...
|||So what happened to all those error handling settings at server level ? Why have they gone from SQL Management Studio ? I got the same problem. Now after migrating to SP2 I see in my msdmsrv.ini that I have KeyErrors set on with fail on first error. However, my cubes don't behave that way during processing after migrating to SP2 - processing just continues after the first error, when it should not. This is a change in behaviour that seemed to happen with installation of SP2. Is anyone else experiencing this ? Any solution ?
|||The change here appears to be in the DISCOVER_XML_METADATA command which is no longer returning these properties - not sure why.
However these properties can be overridden at the object level and again in the actual processing command. So if you always want your cube/dimension etc to process with a particular error configuration you can set this up in BI Development Studio on the object(s) in question. You should also double check the object(s) and whatever is sending the processing command to make sure that they are not overriding the error configuration.
That said the default was (and rightly so in my opinion) to stop on the first error and it's a bit disturbing that anything would change this.
You are not by any chance working in a team where someone else may have altered these settings on the object(s) in question are you? I had this happen to me once and took me ages to figure out why the cube and fact table did not reconcile.
Subscribe to:
Posts (Atom)