odd stored procedure behavior

We have a stored procedure that we've tried with two slightly
different designs. It needs to take a 30 day date range and return a
result set.

Design 1 takes one date as a parameter. The other date is calculated
in a local variable to be 30 days before the one that was passed in.
Both data types are datetime and are in the where clause.

Design 2 takes two dates as parameters with the 30 days being
calculated outside the stored procedure, both in the where clause.

There's some joins, but the main table has maybe 20 million rows.

This is sql server 2000.

Design 1 takes maybe 30 mintues to run. Design 2 runs 15 times
faster.

The plan says that Design 1 is doing a table scan on the 20 million
row table. For Design 2, the plan says it's doing a bookmark lookup
on the date in question.

Why?

brianbrianlanning (brianlanning@.gmail.com) writes:

Quote:

Originally Posted by

We have a stored procedure that we've tried with two slightly
different designs. It needs to take a 30 day date range and return a
result set.
>
Design 1 takes one date as a parameter. The other date is calculated
in a local variable to be 30 days before the one that was passed in.
Both data types are datetime and are in the where clause.
>
Design 2 takes two dates as parameters with the 30 days being
calculated outside the stored procedure, both in the where clause.
>...
Design 1 takes maybe 30 mintues to run. Design 2 runs 15 times
faster.
>
The plan says that Design 1 is doing a table scan on the 20 million
row table. For Design 2, the plan says it's doing a bookmark lookup
on the date in question.
>
Why?

When the optimizer compiles the query plan, it works the stored procedure
as a whole. Thus it has no knowledge of the values of local variables.
Instead it applies standarad assumption which for BETWEEN is 20%, if
memory serves.

However, the optimizer does look at the parameter values, and use
these as guidance. When it knwos both end of the period, it can
estimate more exactly how many rows you will select. Note however
that this plan is cached, and if you the next time call the procedure
with an interval of, say, two years, the same plan will be used,
although that plan may not be good for this longer interval.

What you also could try is:

datecol BETWEEN @.startdate AND dateadd(DAY, 30, @.startday)

Hopefully, the optimizer understands the condition and can act
accordingly.

--
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.mspxsql

Odd SQLTrigger behavior

I have three tables I am using, aspnet_Users, Stories,CustomizedStory. Stories andCustomizedStory are related via a foreign key StoryID. I've setup the tables so that when I delete aStory row it cascade deletes the corresponding row from CustomizedStory. Each CustomizedStory row has a reference to UserID from aspnet_Users.Since, I didn't want to mess with the table definition by adding a cascadedelete option on aspnet_Users, I decide to use a trigger, essentially delete all customized stories and associated stories if a user is deleted:

ALTERTRIGGER[dbo].[DeleteCustomizedStories]

ON[dbo].[aspnet_Users]
FOR DELETE
AS
BEGIN
DELETE FROMdbo.Story
WHEREStoryID=
(SELECTStoryIDFROMdbo.CustomizedStoryWHEREUserID =
(SELECTUserIDFROMdeleted))
END

The problem I am having is that it deletes all of theCustomizedStory rows as specified by the cascading option, but doesn't deletethe Story rows. I can't seem to understandwhy this is happening, especially when Iexplicitly told it to delete story rows.

The behavior is a bit weird, I must admit. Your DELETE-query is wrong though, which may cause problems.

DELETE FROMdbo.Story
WHEREStoryIDIN
(SELECTStoryIDFROMdbo.CustomizedStoryWHEREUserIDIN
(SELECTUserIDFROMdeleted))

or

DELETE s FROMdbo.Storys
INNER JOINdbo.CustomizedStory c ON s.StoryID=c.StoryID
INNER JOINdeleted d ON c.UserID=d.UserID

Odd sqldatasource insert behavior

I have a sqldatasource (code listed below) whose insert Paramaters are control parameters. My aspx page has a textbox and a submit button. the button onclick runs the sqdatasource1.insert.

What I get is every other insert inserts the text in textbox2 and every other insert enters nothing for the namecust value. I have a required field validator which correctly prevents submission if textbox2 is empty.

How do I fix this?

:<code>

<asp:PanelID="Panel1"runat="server"Height="50px"Width="548px">

<asp:ButtonID="Button1"runat="server"Text="New Prospect"ValidationGroup="insertCust"/>

<asp:RequiredFieldValidatorID="RequiredFieldValidator1"runat="server"ControlToValidate="TextBox2"

ErrorMessage="Prospect Name can not be blank"ValidationGroup="insertCust"></asp:RequiredFieldValidator>

<asp:TextBoxID="TextBox2"runat="server"Width="330px"ValidationGroup="insertCust"></asp:TextBox></asp:Panel>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:AccPac2ConnectionString %>"

SelectCommand="SELECT DISTINCT CODETERR FROM dbo.F_arcus() AS F_arcus_1 WHERE (DATEINAC = 0) AND (rtrim(CODETERR) <>'')">

</asp:SqlDataSource>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:AccPac2ConnectionString %>"

InsertCommand="INSERT INTO dbo.BudgetProspects(NameCust, CodeTerr) VALUES (@.Namecust, @.codeterr)"

SelectCommand="SELECT CustomerID, NameCust FROM dbo.BudgetProspects WHERE (CodeTerr = @.codeterr)"

UpdateCommand="UPDATE dbo.BudgetProspects SET NameCust = @.namecust">

<UpdateParameters>

<asp:ParameterName="namecust"/>

</UpdateParameters>

<SelectParameters>

<asp:ControlParameterControlID="RadioButtonList1"Name="codeterr"PropertyName="SelectedValue"/>

</SelectParameters>

<InsertParameters>

<asp:ControlParameterControlID="textbox2"Name="Namecust"PropertyName="text"/>

<asp:ControlParameterControlID="RadioButtonList1"Name="codeterr"PropertyName="SelectedValue"/>

</InsertParameters>

</asp:SqlDataSource>

</code>

codebehind button_click:

<code>

ProtectedSub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles Button1.Click

IfNot TextBox2.TextIsNothingThen

SqlDataSource1.Insert()

TextBox2.Text =""

EndIf

EndSub

</code>

Never mind, complete stupidity on my part the altenate row style had white background and white text.

Odd SQL Server behavior

Hello everyone,
It seems like I've got a runaway SQL Server process.
Almost without fail, when I'm connected to the Internet
via my cable-modem, the SQL Server process starts to
consume 97-100% of the CPU resources on my machine after
about 45 minutes to 1 hour from the time the process is
started. Not only does it consume almost all my CPU's
power, but the number of packets sent out through the
Ethernet cable grows exponentially to gigantic proportions:
prior to SQL Server going nuts, I see the number of
packages SENT and RECEIVED in the order of a few thousand,
but afterwards the number of SENT packages skyrockets to
several MILLION, and keeps going and going.
When this happens, I cannot stop the SQL Server service,
nor can I kill the process. Only solution I have found up
until now is to reboot.
Any ideas? Could this be some sort of a SQL Server virus?
About my system:
Compaq Evo N600c
Tons of free space; 768MB RAM
Windows 2000 Server SP3
SQL Server 2000 SP3
Please send replies to:
jgiosone AT sofwerks.comThis is symptomatic of the Slammer worm but you are not vulnerable if you
have SP3 installed. You might double-check that SP3 is actually installed
with the following:
SELECT SERVERPROPERTY('ProductLevel')
Also, check to see if you have other unpatched instances on your machine.
See <http://www.microsoft.com/sql/downloads/securitytools.asp> for
information on the SQL Critical Update tool.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jose P. Gisone" <jgisone@.sofwerks.com> wrote in message
news:448d01c3e45b$38ee6410$a301280a@.phx.gbl...
> Hello everyone,
> It seems like I've got a runaway SQL Server process.
> Almost without fail, when I'm connected to the Internet
> via my cable-modem, the SQL Server process starts to
> consume 97-100% of the CPU resources on my machine after
> about 45 minutes to 1 hour from the time the process is
> started. Not only does it consume almost all my CPU's
> power, but the number of packets sent out through the
> Ethernet cable grows exponentially to gigantic proportions:
> prior to SQL Server going nuts, I see the number of
> packages SENT and RECEIVED in the order of a few thousand,
> but afterwards the number of SENT packages skyrockets to
> several MILLION, and keeps going and going.
> When this happens, I cannot stop the SQL Server service,
> nor can I kill the process. Only solution I have found up
> until now is to reboot.
> Any ideas? Could this be some sort of a SQL Server virus?
> About my system:
> Compaq Evo N600c
> Tons of free space; 768MB RAM
> Windows 2000 Server SP3
> SQL Server 2000 SP3
>
> Please send replies to:
> jgiosone AT sofwerks.com

Odd SQL Server behavior

Hello everyone,
It seems like I've got a runaway SQL Server process.
Almost without fail, when I'm connected to the Internet
via my cable-modem, the SQL Server process starts to
consume 97-100% of the CPU resources on my machine after
about 45 minutes to 1 hour from the time the process is
started. Not only does it consume almost all my CPU's
power, but the number of packets sent out through the
Ethernet cable grows exponentially to gigantic proportions:
prior to SQL Server going nuts, I see the number of
packages SENT and RECEIVED in the order of a few thousand,
but afterwards the number of SENT packages skyrockets to
several MILLION, and keeps going and going.
When this happens, I cannot stop the SQL Server service,
nor can I kill the process. Only solution I have found up
until now is to reboot.
Any ideas? Could this be some sort of a SQL Server virus?
About my system:
Compaq Evo N600c
Tons of free space; 768MB RAM
Windows 2000 Server SP3
SQL Server 2000 SP3
Please send replies to:
jgiosone AT sofwerks.comThis is symptomatic of the Slammer worm but you are not vulnerable if you
have SP3 installed. You might double-check that SP3 is actually installed
with the following:
SELECT SERVERPROPERTY('ProductLevel')
Also, check to see if you have other unpatched instances on your machine.
See <http://www.microsoft.com/sql/downlo...curitytools.asp> for
information on the SQL Critical Update tool.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jose P. Gisone" <jgisone@.sofwerks.com> wrote in message
news:448d01c3e45b$38ee6410$a301280a@.phx.gbl...

quote:

---

> Hello everyone,
> It seems like I've got a runaway SQL Server process.
> Almost without fail, when I'm connected to the Internet
> via my cable-modem, the SQL Server process starts to
> consume 97-100% of the CPU resources on my machine after
> about 45 minutes to 1 hour from the time the process is
> started. Not only does it consume almost all my CPU's
> power, but the number of packets sent out through the
> Ethernet cable grows exponentially to gigantic proportions:
> prior to SQL Server going nuts, I see the number of
> packages SENT and RECEIVED in the order of a few thousand,
> but afterwards the number of SENT packages skyrockets to
> several MILLION, and keeps going and going.
> When this happens, I cannot stop the SQL Server service,
> nor can I kill the process. Only solution I have found up
> until now is to reboot.
> Any ideas? Could this be some sort of a SQL Server virus?
> About my system:
> Compaq Evo N600c
> Tons of free space; 768MB RAM
> Windows 2000 Server SP3
> SQL Server 2000 SP3
>
> Please send replies to:
> jgiosone AT sofwerks.com

---

sql

Odd Snapshot Behavior

I'm encountering some behavior in a Snapshot publication I just can't figure
out.
I have a publication consisting of 124 tables. These tables are all lookup
tables so the data isstatic and so are the respcetive table schemas.
I dropped all subscriptions to this publication, dropped the publication,
and disabled replication. I then configured the server for replication. I
recreated the publication with the required articles. I created a new
subscriotion and chose to create the snapshot immediately.
The snapshot gets created just fine, but when the distribution agent
attempts to apply the snapshot, I get the following error:
Cannot drop the table 'dbo.ReportTypeLookup' because it is being used for
replication
The odd thing is that I'm not trying to drop that table at all!! As soon as
I remove that particular command from MSrepl_commands, another error occurs,
only this time it's trying to drop another table.
I am 100% sure no one is trying to drop any tables in production...
If anyone has any insight into this I would truly appreciate it.
Thank You!!
It sounds like the subscriber has been previously set up as a publisher of
these tables. Try running sp_removedbreplication on the subscriber which
should reset the replication flags at the article level and then
synchronize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

odd server behavior

Hello everyone,
One of our applications was timing out while executing a certain
stored procedure that inserts information in a group of tables.
When I checked the situation via Profiler, that particular SP has
indeed an almost consistent duration of roughly 30 seconds (which was
the timeout setting at the frontend).
I copied one of the textdata entry captured in Profiler and executed
this in QA. This executed for almost 2 minutes after which all
subsequent executions of this stored procedure via the application
returned to normal with a duration of about 5 to 10ms.
Has anyone had a similar experience? I am at a loss at what happened,
and I don't know where to begin looking for the cause as it has
resolved itself when I was just attempting to look into it.
Thanks.
AramidIt could be that the auto update stats kicked in, or that the data was
readily available in cache. Do check in Profiler to see if there are any
recompilations. Also capture the execution plan in Profiler, and compare
with the one you are getting in Query Analyzer. It would also help if you
had posted the stored procedure code, so that we can point out any issues.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"aramid" <aramid@.hotmail.com> wrote in message
news:v5s371p46il11k82jn9tene1701fmtad2j@.
4ax.com...
Hello everyone,
One of our applications was timing out while executing a certain
stored procedure that inserts information in a group of tables.
When I checked the situation via Profiler, that particular SP has
indeed an almost consistent duration of roughly 30 seconds (which was
the timeout setting at the frontend).
I copied one of the textdata entry captured in Profiler and executed
this in QA. This executed for almost 2 minutes after which all
subsequent executions of this stored procedure via the application
returned to normal with a duration of about 5 to 10ms.
Has anyone had a similar experience? I am at a loss at what happened,
and I don't know where to begin looking for the cause as it has
resolved itself when I was just attempting to look into it.
Thanks.
Aramidsql

Odd Server Behavior

I have a new SQL Server installation on a remote dedicated web server server
hosting several websites.
If I use Remote Desktop, I can log in on the server and perform any DB task,
using any of the created logins without any problem. I can use Enterprise
Manager & Query Analyzer without any problems.
But if I connect with Query Analyzer or Enterprise Manager remotely (from my
home or office PC), things start getting really strange.
First, I don't see all the databases when I connect as sa in Query Analyzer
or Enterprise Manager. Now, I don't like using sa remotely in the first
place, so I have an account that has complete admin access to the database
server. And that account works fine on the server. But no other account on
the server will connect to the database remotely except sa. When I do
connect as sa, I don't see all objects on the server. I don't see 1
database. The other 2 databases I do see? I don't see all the objects using
either QA or EM. I can't run select statements on them. If I run SELECT *
FROM SYSOBJECTS remotely, I don't see all the objects. If I run SELECT *
FROM SYSOBJECTS on the server, I see everything. And yes, the accounts are
set up correctly, they all work as configured on the server. And when I
thought it couldn't behave more wierd, it did. I didn't see a table named
ClubCalendar' remotely. So I created it remotely to see what would happen.
And it created the new table. And if I log on the server, I don't see that
table. I know I'm connecting to the correct server because I doubt anyone
else would use the password I'm using for sa and that account does connect.
Anyone have any ideas about this? This is starting to drive me batty.
Dan
Problem solved. I had to shut down the local instance and not start it and
reset the TCP port. Even though it was set to 1433, I had to set it again.
Odd, since this was working yesterday morning.
Dan
"Dan" wrote:

> I have a new SQL Server installation on a remote dedicated web server server
> hosting several websites.
> If I use Remote Desktop, I can log in on the server and perform any DB task,
> using any of the created logins without any problem. I can use Enterprise
> Manager & Query Analyzer without any problems.
> But if I connect with Query Analyzer or Enterprise Manager remotely (from my
> home or office PC), things start getting really strange.
> First, I don't see all the databases when I connect as sa in Query Analyzer
> or Enterprise Manager. Now, I don't like using sa remotely in the first
> place, so I have an account that has complete admin access to the database
> server. And that account works fine on the server. But no other account on
> the server will connect to the database remotely except sa. When I do
> connect as sa, I don't see all objects on the server. I don't see 1
> database. The other 2 databases I do see? I don't see all the objects using
> either QA or EM. I can't run select statements on them. If I run SELECT *
> FROM SYSOBJECTS remotely, I don't see all the objects. If I run SELECT *
> FROM SYSOBJECTS on the server, I see everything. And yes, the accounts are
> set up correctly, they all work as configured on the server. And when I
> thought it couldn't behave more wierd, it did. I didn't see a table named
> ClubCalendar' remotely. So I created it remotely to see what would happen.
> And it created the new table. And if I log on the server, I don't see that
> table. I know I'm connecting to the correct server because I doubt anyone
> else would use the password I'm using for sa and that account does connect.
> Anyone have any ideas about this? This is starting to drive me batty.
> --
> Dan

Odd Server Behavior

I have a new SQL Server installation on a remote dedicated web server server
hosting several websites.
If I use Remote Desktop, I can log in on the server and perform any DB task,
using any of the created logins without any problem. I can use Enterprise
Manager & Query Analyzer without any problems.
But if I connect with Query Analyzer or Enterprise Manager remotely (from my
home or office PC), things start getting really strange.
First, I don't see all the databases when I connect as sa in Query Analyzer
or Enterprise Manager. Now, I don't like using sa remotely in the first
place, so I have an account that has complete admin access to the database
server. And that account works fine on the server. But no other account on
the server will connect to the database remotely except sa. When I do
connect as sa, I don't see all objects on the server. I don't see 1
database. The other 2 databases I do see? I don't see all the objects usin
g
either QA or EM. I can't run select statements on them. If I run SELECT *
FROM SYSOBJECTS remotely, I don't see all the objects. If I run SELECT *
FROM SYSOBJECTS on the server, I see everything. And yes, the accounts are
set up correctly, they all work as configured on the server. And when I
thought it couldn't behave more wierd, it did. I didn't see a table named
ClubCalendar' remotely. So I created it remotely to see what would happen.
And it created the new table. And if I log on the server, I don't see that
table. I know I'm connecting to the correct server because I doubt anyone
else would use the password I'm using for sa and that account does connect.
Anyone have any ideas about this? This is starting to drive me batty.
DanProblem solved. I had to shut down the local instance and not start it and
reset the TCP port. Even though it was set to 1433, I had to set it again.
Odd, since this was working yesterday morning.
Dan
"Dan" wrote:

> I have a new SQL Server installation on a remote dedicated web server serv
er
> hosting several websites.
> If I use Remote Desktop, I can log in on the server and perform any DB tas
k,
> using any of the created logins without any problem. I can use Enterprise
> Manager & Query Analyzer without any problems.
> But if I connect with Query Analyzer or Enterprise Manager remotely (from
my
> home or office PC), things start getting really strange.
> First, I don't see all the databases when I connect as sa in Query Analyze
r
> or Enterprise Manager. Now, I don't like using sa remotely in the first
> place, so I have an account that has complete admin access to the database
> server. And that account works fine on the server. But no other account
on
> the server will connect to the database remotely except sa. When I do
> connect as sa, I don't see all objects on the server. I don't see 1
> database. The other 2 databases I do see? I don't see all the objects us
ing
> either QA or EM. I can't run select statements on them. If I run SELECT
*
> FROM SYSOBJECTS remotely, I don't see all the objects. If I run SELECT *
> FROM SYSOBJECTS on the server, I see everything. And yes, the accounts ar
e
> set up correctly, they all work as configured on the server. And when I
> thought it couldn't behave more wierd, it did. I didn't see a table named
> ClubCalendar' remotely. So I created it remotely to see what would happen
.
> And it created the new table. And if I log on the server, I don't see tha
t
> table. I know I'm connecting to the correct server because I doubt anyone
> else would use the password I'm using for sa and that account does connect
.
> Anyone have any ideas about this? This is starting to drive me batty.
> --
> Dan

Odd scheduled job behavior

I've scheduled a job to run on a certain schedule, but the Last Run Status date comes back very oddly, a couple years out of synch, the other jobs scheduled report back just fine.
Anyone seen this behavior?
Edward R Hunter, Data Application Designer
comScore Networks, Inc....haven't seen that one, but you could always check the sysjobhistory table in msdb (if you have the rights) to see what the rundate column has stored.

What version of SQL Server are you on?

Odd results from query

The environment is ASP.Net accessing the SQL server through IIS.
While, I've been told that .Net does not create a cache the behavior I'm
observing for intensive purposes looks as if there is a cache. I'm at a bit
of a loss to explain this behavior. Also, if I wait five or ten minutes, and
refresh I get the correct value.
I have a stored procdure that returns a series of rows, from which I sum a
particular number.
There is an action which causes another row to be added.
However, when I make this change to the database the change is not returned
in a query immediatly following the update. I have simultaneously run sql
query analyzer and verified that the table was updated correctly.
When the form is first displayed and then shortly after I perfomr the update
the value is recalculated via the following mechanism:
Using the global connection string a new SqlConnection object is built. Then
a new SqlCommand object is built using the new SqlConnection. The command
type is stored procedure. After the SqlCommand is setup (the command text and
parameters filled in), a new SqlDataAdapter is built using the SqlCommand.
The SqlDataAdapter fills a new DataSet, via the Fill method.
When I say that the object is new I mean that literally, as in
SqlCommand cmd = new SqlCommand (ConnectionString);
I would very much like to get the correct result right away.
Thanks,
Dave
I think you should share more details about the nature of your stored proc.
Are any parameters being passed to the procedure? When you say 5 or 10
minutes have passed before it returns the correct sum, are you using the same
active browser window, or are you starting a new browser (thus starting a new
session, and perhaps refreshing the state with the server, thus passing new
parameters to the procedure).
Just a guess - I'm probably way off and you probably already thought of all
that
Brian
"Dave" wrote:

> The environment is ASP.Net accessing the SQL server through IIS.
> While, I've been told that .Net does not create a cache the behavior I'm
> observing for intensive purposes looks as if there is a cache. I'm at a bit
> of a loss to explain this behavior. Also, if I wait five or ten minutes, and
> refresh I get the correct value.
> I have a stored procdure that returns a series of rows, from which I sum a
> particular number.
> There is an action which causes another row to be added.
> However, when I make this change to the database the change is not returned
> in a query immediatly following the update. I have simultaneously run sql
> query analyzer and verified that the table was updated correctly.
> When the form is first displayed and then shortly after I perfomr the update
> the value is recalculated via the following mechanism:
> Using the global connection string a new SqlConnection object is built. Then
> a new SqlCommand object is built using the new SqlConnection. The command
> type is stored procedure. After the SqlCommand is setup (the command text and
> parameters filled in), a new SqlDataAdapter is built using the SqlCommand.
> The SqlDataAdapter fills a new DataSet, via the Fill method.
> When I say that the object is new I mean that literally, as in
> SqlCommand cmd = new SqlCommand (ConnectionString);
> I would very much like to get the correct result right away.
> Thanks,
> Dave
sql

Odd report behavior

When I run a reporting services report from report manager the report
executes over and over again. I have to close the browser to stop it. Has
anyone seen this behavior
ThanksCheck report properties, autorefresh and make sure there is not a value for
it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Will Byron" <will.byron@.maxqtech.com> wrote in message
news:eBbrdKb%23EHA.2568@.TK2MSFTNGP11.phx.gbl...
> When I run a reporting services report from report manager the report
> executes over and over again. I have to close the browser to stop it. Has
> anyone seen this behavior
> Thanks
>|||Thanks
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23aLcjRb%23EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Check report properties, autorefresh and make sure there is not a value
for
> it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Will Byron" <will.byron@.maxqtech.com> wrote in message
> news:eBbrdKb%23EHA.2568@.TK2MSFTNGP11.phx.gbl...
> > When I run a reporting services report from report manager the report
> > executes over and over again. I have to close the browser to stop it.
Has
> > anyone seen this behavior
> > Thanks
> >
> >
>

Odd null behavior

I am writing an upsert proc that should detect the change in state for a record. The change in state happens when a particular date field (default null) is populated. However, I can not get a record set that detects the changes properly.

Here is an example
set ANSI_NULLS on
go
create table #t1
(
ID int,
DateField datetime
)

create table #t2
(
ID int,
DateField datetime
)

insert into #t1 (ID, DateField) values (1, '7/20/2006')
insert into #t2 (ID, DateFIeld) values (1, null)

select * from #t1 join #t2 on #t1.ID = #t2.ID where #t1.DateField <> #t2.DateField

drop table #t1
drop table #t2

The select should return a record because NULL does not equal '7/20/2006' but it doesn't.
What am I missing?

Thanks in advance.

straight out of BOL:

"Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown."

use the IS NULL clause to assist.

HTH,

Derek

|||Then why does this return a record?:

set ANSI_NULLS off
go
declare @.var datetime
select @.var = null

create table #t1
(
ID int,
DateField datetime
)

insert into #t1 (ID, DateField) values (1, '7/20/2006')

select * from #t1 where #t1.DateField <> @.var

drop table #t1|||

ANSI NULLS setting only affects comparisons of the form:

column <Operator> @.value

column <Operator> literal

value <Operator> @.value

value <Operator> literal

and combinations thereof. The column to column comparison will always follow the ANSI semantics for NULL value comparison. So you could write your SELECT like:

-- Depends on whether you have datetime values with the default value or not

select * from #t1 join #t2 on #t1.ID = #t2.ID

where #t1.DateField <> coalesce(#t2.DateField, '')

--or

select * from #t1 join #t2 on #t1.ID = #t2.ID

where #t1.DateField <> #t2.DateField

or (#t1.DateField IS NULL and #t2.DateField IS NOT NULL)

or (#t1.DateField IS NOT NULL and #t2.DateField IS NULL)

Odd Locking Behavior

This summary is not available. Please click here to view the post.

Odd Locking Behavior

This summary is not available. Please click here to view the post.

Odd behavior when calling a user function within an aggregation function

While debugging a code on the sql server 2000 debugger, I have encountered a
strange behavior while calling a user function from whitin the sum
aggregation function.
I have reproduced this behavior with the following code:
CREATE FUNCTION Test1 (@.prm decimal(18,2))
RETURNS decimal(18,2)
AS
BEGIN
return @.prm
END
---
CREATE PROCEDURE Test2
AS
declare @.var decimal(18,2)
set @.var=0
select @.var=sum(dbo.test1(Col1))
from Tbl1
print @.var
---
Col1 is a decimal(18,2) column on table Tbl1.
Now, when debuggin the sp 'Test2', it enters the function 'Test1' *twice*
for each row of Tbl1, but the sum result is ok.
For example if I had 2 rows on Tbl1 with values 1,2 on Col1, then i see it
enters twice to function 'Test2' with prm=1 and then twice with prm=2, but
the result will be 3 as it should be.
On my original code I am using a recursive function that is running on a
tree and not a simple one like in this example, so in my case it harms the
performance quite a lot (actually any node on the tree will be executed 2^h
when h is the height of the node).
I replaced the sum function with the variable that sums itself the values as
followed:
select @.var = @.var + dbo.test1(Col1)
But still i'm not sure if there's no a better idea to solve it.
What is the cause of this behavior? Is it only while debugging?
Any ideas and thoughts will be appreciated.
Thanks, apscalar function gets invoked once per row. this is by design, i'm afraid.
-oj
<ap> wrote in message news:%23blucZYRFHA.2736@.TK2MSFTNGP09.phx.gbl...
> While debugging a code on the sql server 2000 debugger, I have encountered
> a strange behavior while calling a user function from whitin the sum
> aggregation function.
> I have reproduced this behavior with the following code:
> CREATE FUNCTION Test1 (@.prm decimal(18,2))
> RETURNS decimal(18,2)
> AS
> BEGIN
> return @.prm
> END
> ---
> CREATE PROCEDURE Test2
> AS
> declare @.var decimal(18,2)
> set @.var=0
> select @.var=sum(dbo.test1(Col1))
> from Tbl1
> print @.var
> ---
> Col1 is a decimal(18,2) column on table Tbl1.
> Now, when debuggin the sp 'Test2', it enters the function 'Test1' *twice*
> for each row of Tbl1, but the sum result is ok.
> For example if I had 2 rows on Tbl1 with values 1,2 on Col1, then i see it
> enters twice to function 'Test2' with prm=1 and then twice with prm=2, but
> the result will be 3 as it should be.
> On my original code I am using a recursive function that is running on a
> tree and not a simple one like in this example, so in my case it harms the
> performance quite a lot (actually any node on the tree will be executed
> 2^h when h is the height of the node).
> I replaced the sum function with the variable that sums itself the values
> as followed:
> select @.var = @.var + dbo.test1(Col1)
> But still i'm not sure if there's no a better idea to solve it.
> What is the cause of this behavior? Is it only while debugging?
> Any ideas and thoughts will be appreciated.
> Thanks, ap
>
>|||>> What is the cause of this behavior?
In the topic
http://msdn.microsoft.com/library/e...des_08_460j.asp
they say:
"The number of times that a function specified in a query is actually
executed can vary between execution plans built by the optimizer."
This behaviour can also be revealed by using the "Show Server Trace"
option in Query Analyzer or using Profiler, so it seems that it does
not happen only while debugging.
You can use this statement, instead:
SELECT @.var=SUM(X) FROM (
select top 100 percent dbo.test1(Col1) X
from Tbl1 order by X
) A
It seems that this way the function is called only once for each row.
Razvan

Odd behavior in partitioning in SQL 2000

Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.
This seems to suggest parameter sniffing:
http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=en&ie=UTF-8&oe=UTF-8&
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181744183.369244.18310@.i38g2000prf.googlegro ups.com...
Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.
|||Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested: http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>
|||Without seeing all of your DDL and knowing your data distribution, it would
be hard to tell. Since you have a workaround via dynamic SQL, you may very
well have to live with that until you can upgrade to SQL 2005. Just make
sure you put in some defensive coding to prevent SQL injection attacks.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181763906.605010.12270@.d30g2000prg.googlegro ups.com...
Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested:
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>
|||Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.
|||Some resources:
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032276813&CountryCode=US
[url]http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032270016&EventC ategory=3&culture=en-US&CountryCode=US[/url]
http://download.microsoft.com/download/4/1/f/41f09116-19b3-40fe-9d54-d1e9b7af9e82/REAL_Lifecycle_Partitioning.doc
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181931792.613927.65580@.c77g2000hse.googlegro ups.com...
Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.

Odd behavior in partitioning in SQL 2000

Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.This seems to suggest parameter sniffing:
http://groups.google.ca/groups/sear...
=UTF-8&
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181744183.369244.18310@.i38g2000prf.googlegroups.com...
Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.|||Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested: http://omnibuzz-sql.blogspot.com/20...procedures.html
The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/sear...fing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>|||Without seeing all of your DDL and knowing your data distribution, it would
be hard to tell. Since you have a workaround via dynamic SQL, you may very
well have to live with that until you can upgrade to SQL 2005. Just make
sure you put in some defensive coding to prevent SQL injection attacks.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181763906.605010.12270@.d30g2000prg.googlegroups.com...
Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested:
http://omnibuzz-sql.blogspot.com/20...es.html

The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/sear...fing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>|||Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.|||Some resources:
http://msevents.microsoft.com/CUI/W...&CountryCode=US
http://msevents.microsoft.com/CUI/W...&CountryCode=US
http://download.microsoft.com/downl...artitioning.doc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181931792.613927.65580@.c77g2000hse.googlegroups.com...
Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.

Odd behavior in partitioning in SQL 2000

Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.This seems to suggest parameter sniffing:
http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=en&ie=UTF-8&oe=UTF-8&
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181744183.369244.18310@.i38g2000prf.googlegroups.com...
Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.|||Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested: http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>|||Without seeing all of your DDL and knowing your data distribution, it would
be hard to tell. Since you have a workaround via dynamic SQL, you may very
well have to live with that until you can upgrade to SQL 2005. Just make
sure you put in some defensive coding to prevent SQL injection attacks.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181763906.605010.12270@.d30g2000prg.googlegroups.com...
Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested:
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>|||Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.|||Some resources:
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032276813&CountryCode=US
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032270016&EventCategory=3&culture=en-US&CountryCode=US
http://download.microsoft.com/download/4/1/f/41f09116-19b3-40fe-9d54-d1e9b7af9e82/REAL_Lifecycle_Partitioning.doc
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181931792.613927.65580@.c77g2000hse.googlegroups.com...
Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.

Odd behavior (Pagebreaks & Visibility)

I built a report, that displays several Subreports. Then a request came, if it wouldn't be possible, to build a switch to turn on/off a page break after each report.

Since RS doesn't support a fexible page break controlling (you can either set or not set a page break hardcoded), I got an idea for a workaround:

After my Subreport I put a rectangle with a page break. Now every Subreport was displayed on a separate page.

Then I set the visibility for the rectangle to "hidded" and as expected, there was no page break after each subreport.

So far, so good. I then created a boolean parameter and set the Visibility expression to:

=Not Parameters!PageBreakAfterEachReport.Value

Now here comes the strange thing: If the Parameter is false, the rectangle is not displayed and therefor there is no page break, BUT, if the parameter is true, the rectangle is displayed correctly but there is STILL NO page break!!!?!?!!?!?

Why does the page break work, when I set the visibility hardcoded, but not when I try to control it via a parameter & expression?

I'm having the same issue here. If my control's visibility is dynamic, the pagebreaks do not work correctly. Is there any resolution/workaround to this problem?|||

Hi,

You could load the xml generated in XmlDocument and then using Xpath select the pagebreak element. Set this element dynamically.

Regards,

Alok

|||

Folks, I don't know if it is going to make you feel better or not, but apparently this behavior is "currently by design" -- at least I think that what is described in this thread is the same as what you are discussing.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=672379&SiteID=1

Actually (editing this post) now that I think about it, I may already have worked out a workaround that will work for this situation. Let me know what you think.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1682115&SiteID=1

In that post, I included a special dummy group and used a boolean parameter to determine the conditional page break. In your case, rather than the parameter, your condition would be something "if my other thing isn't visible, do this page break".

Hope this makes sense...

>L<