From BOL...
CHECK constraints are not needed for the partitioned view to return the correct results. However, if the CHECK constraints have not been defined, the query optimizer must search all the tables instead of only those that cover the search condition on the partitioning column. Without the CHECK constraints, the view operates like any other view with UNION ALL. The query optimizer cannot make any assumptions about the values stored in different tables and it cannot skip searching the tables that participate in the view definition.
Then why am I getting index scans on my partitioning column on tables that fail the search value based on their check constraint?
Not looking for an answer because I know the query optimizer is a fickle b*tch and I did not post any code, but I needed to rant.Now I have no experience here but I will attempt to dredge something from memory...
I believe, based on some discussions on SQL Team I skimmed, that the view will look through ALL tables on its first run but subsequent to that it should only hit the relevant tables.
Have a search of the SQL Team threads if you want to nail this - I am 78.3% sure I have got that about right. :)|||Not looking for an answer because I know the query optimizer is a fickle b*tch...
Have you had these feelings for a long time?
:D
...but I needed to rant.
I know the feeling well.
Take care,
hmscott|||- I am 78.3% sure I have got that about right. :)
What a coincidence, 78.3% of all statistics are made up on the spot.
Regards,
hmscott|||What a coincidence, 78.3% of all statistics are made up on the spot.I thought it was 79.2? :(|||Now I have no experience here but I will attempt to dredge something from memory...
I believe, based on some discussions on SQL Team I skimmed, that the view will look through ALL tables on its first run but subsequent to that it should only hit the relevant tables.
Have a search of the SQL Team threads if you want to nail this - I am 78.3% sure I have got that about right. :)
nope. same execution plan everytime. costly scans on the partitioning column with my check constraint on tables that fail the search condition. I just re-read the rules for partititioned views. everything looks ok and I am getting pissed.|||I thought it was 79.2? :(
As my old artillery instructor used to say, "what's one percent among friends?"
:shocked:
Regards,
hmscott|||As my old artillery instructor used to say, "what's one percent among friends?"
nothing, as long as your shells are 1% more explosive than your friend's shells.|||it appears that your partitioning column has to be the first column in the primary key. not that this written down anywhere obvious.|||that's because it's self-evident.
;)|||well the other part of the primary key is more selective being a surrogate incrementor, so nsince I had to use a composite key in this case I wanted it first in the index. i have had girlfriends I would call surrogate incrementors. Whoops, a TallCowboy moment.
Showing posts with label view. Show all posts
Showing posts with label view. Show all posts
Sunday, March 25, 2012
Monday, March 19, 2012
BOL
Hello All,
BOL states that :-
Before you create a view, consider these guidelines and one of the
guideline is
"The query defining the view cannot include the ORDER BY, COMPUTE, or
COMPUTE BY clauses or the INTO keyword."
But we can include Order By in A view if we use TOP like this
create view v1 as
select top 100 percent * from tablename order by columnName
Any Thoughts
With warm regards
Jatinder SinghWhat's your question? I am not sure.
Have you checked the "CREATE VIEW" page in BOL, which states:
There are a few restrictions on the SELECT clauses in a view definition. A
CREATE VIEW statement cannot:
a.. Include ORDER BY clause, unless there is also a TOP clause in the
select list of the SELECT statement.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123504963.100055.120900@.f14g2000cwb.googlegroups.com...
Hello All,
BOL states that :-
Before you create a view, consider these guidelines and one of the
guideline is
"The query defining the view cannot include the ORDER BY, COMPUTE, or
COMPUTE BY clauses or the INTO keyword."
But we can include Order By in A view if we use TOP like this
create view v1 as
select top 100 percent * from tablename order by columnName
Any Thoughts
With warm regards
Jatinder Singh|||There are issues with doing that.
Yes you can, but doing will force SQL Server to execute the entire view and
perform the order by before using the information. There are other better
ways to accomplish the same thing...
If your view really needs to be sorted, then you should create an index on
the view. As the first index must be clustered, the provides a sort order.
That aside, back to your workaround. By creating a view without
specifying a top and order by, SQL Server has a chance to optimise the
combined select and view query. By doing this it can filter the data before
applying any sort operations. Don't forget that a view (unless indexed)
does not contain any data, it's effectively a pre-prepared SQL statement.
Hope this helps a little.
Regards
Colin Dawson
www.cjdawson.com
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123504963.100055.120900@.f14g2000cwb.googlegroups.com...
> Hello All,
> BOL states that :-
> Before you create a view, consider these guidelines and one of the
> guideline is
> "The query defining the view cannot include the ORDER BY, COMPUTE, or
> COMPUTE BY clauses or the INTO keyword."
> But we can include Order By in A view if we use TOP like this
> create view v1 as
> select top 100 percent * from tablename order by columnName
> Any Thoughts
> With warm regards
> Jatinder Singh
>|||Hi
The guidelines might be for Updatable Views. However you can use Order
By clause in a view.
please let me know if u have any questions
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||Some people do suggest using this "trick" to order views. I believe
that there are good reasons to avoid doing this.
This behaviour of TOP in a view is undocumented or at least,
under-documented. The ORDER BY is valid only for the purpose of
defining the TOP x PERCENT so intuitively you would not expect it to
apply to the result of a SELECT from the view. 99% of the time it *may*
work but there is no guarantee that it will always continue to work.
A view is supposed to behave like a table - without a logical order.
Sometimes you don't want the view to be sorted. Consider this example:
CREATE TABLE foo (x INTEGER PRIMARY KEY NONCLUSTERED, y INTEGER NOT
NULL)
GO
CREATE VIEW foo_view
AS
SELECT TOP 100 PERCENT x,y
FROM foo
ORDER BY y
GO
SELECT x FROM foo_view
The most efficient plan for the SELECT x query is an index scan of the
nonclustered index. The optimizer therefore has a choice either to
ignore the ORDER BY and retrieve an unsorted result set or to force a
sort which gives a sub-optimal execution plan. So TOP 100 PERCENT
either hurts performance, or doesn't work at all!
As always with specific engine behaviour, results could change between
different installations, service packs or versions of SQLServer, which
could break your code if it relies on an undefined feature.
In short, don't use undocumented tricks as a substitute for good design
and if you do use this feature be aware of its limitations and risks.
The "correct" and safe solution is simple:
SELECT ... FROM view ORDER BY ...
Why would you need to order the view itself?
Hope this helps.
David Portas
SQL Server MVP
--|||Hi All,
Thanks Naryan for your informative Answer
In Transact-SQL Reference (Location)
Include ORDER BY clause, unless there is also a TOP clause in the
select list of the SELECT statement.
In Creating and Maintaining Databases (Location)
The query defining the view cannot include the ORDER BY, COMPUTE, or
COMPUTE BY clauses or the INTO keyword.
(The ORDER BY clause is invalid in views, inline functions, derived
tables, and subqueries, unless TOP is also specified.)
Try this
select * fom (select * from tablename order by columnname) AA
The point you mentioned appear even in this case Derived Table . My
Question was simple why there is inconsitency while mentioning
information in BOL. Now prehaps I made myself clear. Thanks again for
showing interest .
David
Excellent post !!
As always your answer give something new to learn .
"So TOP 100 PERCENT either hurts performance, or doesn't work at all! "
The first part "So TOP 100 PERCENT hurts performance" is clear ( a bit)
As my assumption was that Query Optimizer will first expand the query
of view then it will rearrange the whole stuff and the execute it . My
assumption can be totally wrong .
I could not understand "doesn't work at all" part could you please
explain it further.
With Warm regards
Jatinder Singh|||"The optimizer therefore has a choice either to ignore the ORDER BY and
retrieve an unsorted result set [= TOP 100 doesn't work at all] or to force
a
sort which gives a sub-optimal execution plan [= hurts performance]". In
other words, both options are logically possible; neither is very
satisfactory.
David Portas
SQL Server MVP
--
"jsfromynr" wrote:
> Hi All,
> Thanks Naryan for your informative Answer
> In Transact-SQL Reference (Location)
> Include ORDER BY clause, unless there is also a TOP clause in the
> select list of the SELECT statement.
> In Creating and Maintaining Databases (Location)
> The query defining the view cannot include the ORDER BY, COMPUTE, or
> COMPUTE BY clauses or the INTO keyword.
> (The ORDER BY clause is invalid in views, inline functions, derived
> tables, and subqueries, unless TOP is also specified.)
> Try this
> select * fom (select * from tablename order by columnname) AA
> The point you mentioned appear even in this case Derived Table . My
> Question was simple why there is inconsitency while mentioning
> information in BOL. Now prehaps I made myself clear. Thanks again for
> showing interest .
> David
> Excellent post !!
> As always your answer give something new to learn .
> "So TOP 100 PERCENT either hurts performance, or doesn't work at all! "
> The first part "So TOP 100 PERCENT hurts performance" is clear ( a bit)
> As my assumption was that Query Optimizer will first expand the query
> of view then it will rearrange the whole stuff and the execute it . My
> assumption can be totally wrong .
> I could not understand "doesn't work at all" part could you please
> explain it further.
> With Warm regards
> Jatinder Singh
>|||On Mon, 08 Aug 2005 12:53:34 GMT, Colin Dawson wrote:
(snip)
>If your view really needs to be sorted, then you should create an index on
>the view.
Hi Colin,
That won't work. Clustered indexes govern how data is stored on the
disk, not in which order it is processed. The optimizer might still pick
an unexpected plan. The order might even change between two consecutive
executions of the same query. See repro below for a proof that an
indexed view doesn't guarantee that the data is returned in order.
The only way to make sure you get the data in the order you need is to
provide an ORDER BY clause in the SELECT statement actually used to
retrieve the data.
create table Beatles (ID int NOT NULL IDENTITY PRIMARY KEY,
fname varchar(20) NOT NULL,
lname varchar(20) NOT NULL)
go
create view SortedBeatles with schemabinding
as
select fname, lname from dbo.Beatles
go
create unique clustered index BeatleIndex on SortedBeatles (fname)
go
insert into Beatles
values ('John', 'Lennon')
insert into Beatles
values ('Paul', 'McCartney')
insert into Beatles
values ('George', 'Harrison')
insert into Beatles
values ('Ringo', 'Starr')
go
select fname, lname from SortedBeatles
go
drop view SortedBeatles
go
drop table Beatles
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:9kjff159m2i4qtb39a50hganommi87qgid@.
4ax.com...
> On Mon, 08 Aug 2005 12:53:34 GMT, Colin Dawson wrote:
> (snip)
> Hi Colin,
> That won't work. Clustered indexes govern how data is stored on the
> disk, not in which order it is processed. The optimizer might still pick
> an unexpected plan. The order might even change between two consecutive
> executions of the same query. See repro below for a proof that an
> indexed view doesn't guarantee that the data is returned in order.
> The only way to make sure you get the data in the order you need is to
> provide an ORDER BY clause in the SELECT statement actually used to
> retrieve the data.
> create table Beatles (ID int NOT NULL IDENTITY PRIMARY KEY,
> fname varchar(20) NOT NULL,
> lname varchar(20) NOT NULL)
> go
> create view SortedBeatles with schemabinding
> as
> select fname, lname from dbo.Beatles
> go
> create unique clustered index BeatleIndex on SortedBeatles (fname)
> go
> insert into Beatles
> values ('John', 'Lennon')
> insert into Beatles
> values ('Paul', 'McCartney')
> insert into Beatles
> values ('George', 'Harrison')
> insert into Beatles
> values ('Ringo', 'Starr')
> go
> select fname, lname from SortedBeatles
> go
> drop view SortedBeatles
> go
> drop table Beatles
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
I had a feeling that it wasn't guarenteed. Your dead right that the only
way to guarentee the sort order is to use the Order by clause. Bad on me
for trying to cut a corner.
Regards
Colin Dawson
www.cjdawson.com
BOL states that :-
Before you create a view, consider these guidelines and one of the
guideline is
"The query defining the view cannot include the ORDER BY, COMPUTE, or
COMPUTE BY clauses or the INTO keyword."
But we can include Order By in A view if we use TOP like this
create view v1 as
select top 100 percent * from tablename order by columnName
Any Thoughts
With warm regards
Jatinder SinghWhat's your question? I am not sure.
Have you checked the "CREATE VIEW" page in BOL, which states:
There are a few restrictions on the SELECT clauses in a view definition. A
CREATE VIEW statement cannot:
a.. Include ORDER BY clause, unless there is also a TOP clause in the
select list of the SELECT statement.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123504963.100055.120900@.f14g2000cwb.googlegroups.com...
Hello All,
BOL states that :-
Before you create a view, consider these guidelines and one of the
guideline is
"The query defining the view cannot include the ORDER BY, COMPUTE, or
COMPUTE BY clauses or the INTO keyword."
But we can include Order By in A view if we use TOP like this
create view v1 as
select top 100 percent * from tablename order by columnName
Any Thoughts
With warm regards
Jatinder Singh|||There are issues with doing that.
Yes you can, but doing will force SQL Server to execute the entire view and
perform the order by before using the information. There are other better
ways to accomplish the same thing...
If your view really needs to be sorted, then you should create an index on
the view. As the first index must be clustered, the provides a sort order.
That aside, back to your workaround. By creating a view without
specifying a top and order by, SQL Server has a chance to optimise the
combined select and view query. By doing this it can filter the data before
applying any sort operations. Don't forget that a view (unless indexed)
does not contain any data, it's effectively a pre-prepared SQL statement.
Hope this helps a little.
Regards
Colin Dawson
www.cjdawson.com
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123504963.100055.120900@.f14g2000cwb.googlegroups.com...
> Hello All,
> BOL states that :-
> Before you create a view, consider these guidelines and one of the
> guideline is
> "The query defining the view cannot include the ORDER BY, COMPUTE, or
> COMPUTE BY clauses or the INTO keyword."
> But we can include Order By in A view if we use TOP like this
> create view v1 as
> select top 100 percent * from tablename order by columnName
> Any Thoughts
> With warm regards
> Jatinder Singh
>|||Hi
The guidelines might be for Updatable Views. However you can use Order
By clause in a view.
please let me know if u have any questions
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||Some people do suggest using this "trick" to order views. I believe
that there are good reasons to avoid doing this.
This behaviour of TOP in a view is undocumented or at least,
under-documented. The ORDER BY is valid only for the purpose of
defining the TOP x PERCENT so intuitively you would not expect it to
apply to the result of a SELECT from the view. 99% of the time it *may*
work but there is no guarantee that it will always continue to work.
A view is supposed to behave like a table - without a logical order.
Sometimes you don't want the view to be sorted. Consider this example:
CREATE TABLE foo (x INTEGER PRIMARY KEY NONCLUSTERED, y INTEGER NOT
NULL)
GO
CREATE VIEW foo_view
AS
SELECT TOP 100 PERCENT x,y
FROM foo
ORDER BY y
GO
SELECT x FROM foo_view
The most efficient plan for the SELECT x query is an index scan of the
nonclustered index. The optimizer therefore has a choice either to
ignore the ORDER BY and retrieve an unsorted result set or to force a
sort which gives a sub-optimal execution plan. So TOP 100 PERCENT
either hurts performance, or doesn't work at all!
As always with specific engine behaviour, results could change between
different installations, service packs or versions of SQLServer, which
could break your code if it relies on an undefined feature.
In short, don't use undocumented tricks as a substitute for good design
and if you do use this feature be aware of its limitations and risks.
The "correct" and safe solution is simple:
SELECT ... FROM view ORDER BY ...
Why would you need to order the view itself?
Hope this helps.
David Portas
SQL Server MVP
--|||Hi All,
Thanks Naryan for your informative Answer
In Transact-SQL Reference (Location)
Include ORDER BY clause, unless there is also a TOP clause in the
select list of the SELECT statement.
In Creating and Maintaining Databases (Location)
The query defining the view cannot include the ORDER BY, COMPUTE, or
COMPUTE BY clauses or the INTO keyword.
(The ORDER BY clause is invalid in views, inline functions, derived
tables, and subqueries, unless TOP is also specified.)
Try this
select * fom (select * from tablename order by columnname) AA
The point you mentioned appear even in this case Derived Table . My
Question was simple why there is inconsitency while mentioning
information in BOL. Now prehaps I made myself clear. Thanks again for
showing interest .
David
Excellent post !!
As always your answer give something new to learn .
"So TOP 100 PERCENT either hurts performance, or doesn't work at all! "
The first part "So TOP 100 PERCENT hurts performance" is clear ( a bit)
As my assumption was that Query Optimizer will first expand the query
of view then it will rearrange the whole stuff and the execute it . My
assumption can be totally wrong .
I could not understand "doesn't work at all" part could you please
explain it further.
With Warm regards
Jatinder Singh|||"The optimizer therefore has a choice either to ignore the ORDER BY and
retrieve an unsorted result set [= TOP 100 doesn't work at all] or to force
a
sort which gives a sub-optimal execution plan [= hurts performance]". In
other words, both options are logically possible; neither is very
satisfactory.
David Portas
SQL Server MVP
--
"jsfromynr" wrote:
> Hi All,
> Thanks Naryan for your informative Answer
> In Transact-SQL Reference (Location)
> Include ORDER BY clause, unless there is also a TOP clause in the
> select list of the SELECT statement.
> In Creating and Maintaining Databases (Location)
> The query defining the view cannot include the ORDER BY, COMPUTE, or
> COMPUTE BY clauses or the INTO keyword.
> (The ORDER BY clause is invalid in views, inline functions, derived
> tables, and subqueries, unless TOP is also specified.)
> Try this
> select * fom (select * from tablename order by columnname) AA
> The point you mentioned appear even in this case Derived Table . My
> Question was simple why there is inconsitency while mentioning
> information in BOL. Now prehaps I made myself clear. Thanks again for
> showing interest .
> David
> Excellent post !!
> As always your answer give something new to learn .
> "So TOP 100 PERCENT either hurts performance, or doesn't work at all! "
> The first part "So TOP 100 PERCENT hurts performance" is clear ( a bit)
> As my assumption was that Query Optimizer will first expand the query
> of view then it will rearrange the whole stuff and the execute it . My
> assumption can be totally wrong .
> I could not understand "doesn't work at all" part could you please
> explain it further.
> With Warm regards
> Jatinder Singh
>|||On Mon, 08 Aug 2005 12:53:34 GMT, Colin Dawson wrote:
(snip)
>If your view really needs to be sorted, then you should create an index on
>the view.
Hi Colin,
That won't work. Clustered indexes govern how data is stored on the
disk, not in which order it is processed. The optimizer might still pick
an unexpected plan. The order might even change between two consecutive
executions of the same query. See repro below for a proof that an
indexed view doesn't guarantee that the data is returned in order.
The only way to make sure you get the data in the order you need is to
provide an ORDER BY clause in the SELECT statement actually used to
retrieve the data.
create table Beatles (ID int NOT NULL IDENTITY PRIMARY KEY,
fname varchar(20) NOT NULL,
lname varchar(20) NOT NULL)
go
create view SortedBeatles with schemabinding
as
select fname, lname from dbo.Beatles
go
create unique clustered index BeatleIndex on SortedBeatles (fname)
go
insert into Beatles
values ('John', 'Lennon')
insert into Beatles
values ('Paul', 'McCartney')
insert into Beatles
values ('George', 'Harrison')
insert into Beatles
values ('Ringo', 'Starr')
go
select fname, lname from SortedBeatles
go
drop view SortedBeatles
go
drop table Beatles
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:9kjff159m2i4qtb39a50hganommi87qgid@.
4ax.com...
> On Mon, 08 Aug 2005 12:53:34 GMT, Colin Dawson wrote:
> (snip)
> Hi Colin,
> That won't work. Clustered indexes govern how data is stored on the
> disk, not in which order it is processed. The optimizer might still pick
> an unexpected plan. The order might even change between two consecutive
> executions of the same query. See repro below for a proof that an
> indexed view doesn't guarantee that the data is returned in order.
> The only way to make sure you get the data in the order you need is to
> provide an ORDER BY clause in the SELECT statement actually used to
> retrieve the data.
> create table Beatles (ID int NOT NULL IDENTITY PRIMARY KEY,
> fname varchar(20) NOT NULL,
> lname varchar(20) NOT NULL)
> go
> create view SortedBeatles with schemabinding
> as
> select fname, lname from dbo.Beatles
> go
> create unique clustered index BeatleIndex on SortedBeatles (fname)
> go
> insert into Beatles
> values ('John', 'Lennon')
> insert into Beatles
> values ('Paul', 'McCartney')
> insert into Beatles
> values ('George', 'Harrison')
> insert into Beatles
> values ('Ringo', 'Starr')
> go
> select fname, lname from SortedBeatles
> go
> drop view SortedBeatles
> go
> drop table Beatles
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
I had a feeling that it wasn't guarenteed. Your dead right that the only
way to guarentee the sort order is to use the Order by clause. Bad on me
for trying to cut a corner.
Regards
Colin Dawson
www.cjdawson.com
Blocks
Hello,Why an sp autoblock '
If exec my sp and view the connections with sp_who i watch te id of my
connection in the column blk of the result.
Why succedes this '
Thanks in advance.Check out:
http://support.microsoft.com/default.aspx/kb/906344
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
Hello,Why an sp autoblock '
If exec my sp and view the connections with sp_who i watch te id of my
connection in the column blk of the result.
Why succedes this '
Thanks in advance.|||Thanks !!!
But,what i configured in the server because the time of blocked is over 20
seconds ?
Thanks in advance.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23mmGQy%23xGHA.2516@.TK2MSFTNGP06.phx.gbl...
> Check out:
> http://support.microsoft.com/default.aspx/kb/906344
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
> news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hello,Why an sp autoblock '
> If exec my sp and view the connections with sp_who i watch te id of my
> connection in the column blk of the result.
> Why succedes this '
> Thanks in advance.
>|||You'll have to tell us what you "configured".
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
news:eidPzzAyGHA.3844@.TK2MSFTNGP06.phx.gbl...
Thanks !!!
But,what i configured in the server because the time of blocked is over 20
seconds ?
Thanks in advance.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23mmGQy%23xGHA.2516@.TK2MSFTNGP06.phx.gbl...
> Check out:
> http://support.microsoft.com/default.aspx/kb/906344
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
> news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hello,Why an sp autoblock '
> If exec my sp and view the connections with sp_who i watch te id of my
> connection in the column blk of the result.
> Why succedes this '
> Thanks in advance.
>
If exec my sp and view the connections with sp_who i watch te id of my
connection in the column blk of the result.
Why succedes this '
Thanks in advance.Check out:
http://support.microsoft.com/default.aspx/kb/906344
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
Hello,Why an sp autoblock '
If exec my sp and view the connections with sp_who i watch te id of my
connection in the column blk of the result.
Why succedes this '
Thanks in advance.|||Thanks !!!
But,what i configured in the server because the time of blocked is over 20
seconds ?
Thanks in advance.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23mmGQy%23xGHA.2516@.TK2MSFTNGP06.phx.gbl...
> Check out:
> http://support.microsoft.com/default.aspx/kb/906344
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
> news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hello,Why an sp autoblock '
> If exec my sp and view the connections with sp_who i watch te id of my
> connection in the column blk of the result.
> Why succedes this '
> Thanks in advance.
>|||You'll have to tell us what you "configured".
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
news:eidPzzAyGHA.3844@.TK2MSFTNGP06.phx.gbl...
Thanks !!!
But,what i configured in the server because the time of blocked is over 20
seconds ?
Thanks in advance.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23mmGQy%23xGHA.2516@.TK2MSFTNGP06.phx.gbl...
> Check out:
> http://support.microsoft.com/default.aspx/kb/906344
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
> news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hello,Why an sp autoblock '
> If exec my sp and view the connections with sp_who i watch te id of my
> connection in the column blk of the result.
> Why succedes this '
> Thanks in advance.
>
Blocks
Hello,Why an sp autoblock '
If exec my sp and view the connections with sp_who i watch te id of my
connection in the column blk of the result.
Why succedes this '
Thanks in advance.Check out:
http://support.microsoft.com/default.aspx/kb/906344
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
Hello,Why an sp autoblock '
If exec my sp and view the connections with sp_who i watch te id of my
connection in the column blk of the result.
Why succedes this '
Thanks in advance.|||Thanks !!!
But,what i configured in the server because the time of blocked is over 20
seconds ?
Thanks in advance.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23mmGQy%23xGHA.2516@.TK2MSFTNGP06.phx.gbl...
> Check out:
> http://support.microsoft.com/default.aspx/kb/906344
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
> news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hello,Why an sp autoblock '
> If exec my sp and view the connections with sp_who i watch te id of my
> connection in the column blk of the result.
> Why succedes this '
> Thanks in advance.
>|||You'll have to tell us what you "configured".
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
news:eidPzzAyGHA.3844@.TK2MSFTNGP06.phx.gbl...
Thanks !!!
But,what i configured in the server because the time of blocked is over 20
seconds ?
Thanks in advance.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23mmGQy%23xGHA.2516@.TK2MSFTNGP06.phx.gbl...
> Check out:
> http://support.microsoft.com/default.aspx/kb/906344
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
> news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hello,Why an sp autoblock '
> If exec my sp and view the connections with sp_who i watch te id of my
> connection in the column blk of the result.
> Why succedes this '
> Thanks in advance.
>
If exec my sp and view the connections with sp_who i watch te id of my
connection in the column blk of the result.
Why succedes this '
Thanks in advance.Check out:
http://support.microsoft.com/default.aspx/kb/906344
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
Hello,Why an sp autoblock '
If exec my sp and view the connections with sp_who i watch te id of my
connection in the column blk of the result.
Why succedes this '
Thanks in advance.|||Thanks !!!
But,what i configured in the server because the time of blocked is over 20
seconds ?
Thanks in advance.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23mmGQy%23xGHA.2516@.TK2MSFTNGP06.phx.gbl...
> Check out:
> http://support.microsoft.com/default.aspx/kb/906344
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
> news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hello,Why an sp autoblock '
> If exec my sp and view the connections with sp_who i watch te id of my
> connection in the column blk of the result.
> Why succedes this '
> Thanks in advance.
>|||You'll have to tell us what you "configured".
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
news:eidPzzAyGHA.3844@.TK2MSFTNGP06.phx.gbl...
Thanks !!!
But,what i configured in the server because the time of blocked is over 20
seconds ?
Thanks in advance.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23mmGQy%23xGHA.2516@.TK2MSFTNGP06.phx.gbl...
> Check out:
> http://support.microsoft.com/default.aspx/kb/906344
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
> news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hello,Why an sp autoblock '
> If exec my sp and view the connections with sp_who i watch te id of my
> connection in the column blk of the result.
> Why succedes this '
> Thanks in advance.
>
Sunday, March 11, 2012
Blocking issue in DTS package running on 2005
I'm testing an existing 2000 DTS package in 2005. A Data Transformation Task selects from a view and loads a table that was just truncated. The view definition contains a subselect selecting data off the table,
View: select ... from a, (select ... from b) b2 ON ...
Table to insert into: b
There is blocking going on during the execution of the package. The select * from view (Source) and the insert bulk into the table (Destination) are blocking each other. This does not happen in 2000.
Is there a command that I can put in the view definition subselect that will allow me to just grab the data that it can without worrying about blocking? Would READCOMMITTED or READUNCOMMITED work without impact on the view? Other recommendations?
Has anyone ever run into this?
I found that when you uncheck the Table Lock box of the Data Transformation Task, that this blocking does not occur. Does anyone know why this is required in 2005 but works fine in 2000 with the box checked?Sunday, February 19, 2012
Blank Parameters do not work when running stored proc in Data view
I have a stored procedure that gives different outputs based on whether an
input parameter is given or not. When I set the parameter to blank, not
NULL, I assume this will give the same result as running it in Query Analyzer
and not inputting anything for that parameter, but instead I get a "input
string was not in the correct format" error. Is this a bug? It is not the
behavior I would expect. I think it is very important that blank parameters
work correctly.I'm talking about the parameters in a SQL Server stored procedure. When I
run the report, it's fine if I allow blank values, and there is no error.
Only in Data View do I get the error when I try to run it or do refresh
fields, and it asks me to input all parameters.
"Aaron Williams" wrote:
> Did you set the parameter to allow blank values?
> "Stefan Wrobel" wrote:
> > I have a stored procedure that gives different outputs based on whether an
> > input parameter is given or not. When I set the parameter to blank, not
> > NULL, I assume this will give the same result as running it in Query Analyzer
> > and not inputting anything for that parameter, but instead I get a "input
> > string was not in the correct format" error. Is this a bug? It is not the
> > behavior I would expect. I think it is very important that blank parameters
> > work correctly.|||That seems about correct. I assumed if you specified a parameter as blank it
was like not sending it to the SP at all. For clarification, I only need to
use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
need from the parameters list. I assume its as if the Report Server doesn't
even know these parameters exist, and doesn't attempt to set them to
anything. This is how I want it. However, in Data View, if I try to get the
fields in the first place, I have to give some sort of value for all 4
parameters, because if I leave the 2 I don't need set to <Blank>, I get that
error. The only time I ever have run into this problem is while designing
the Report, but not anywhere else. It works fine in Query Analyzer, and also
when I run the report through Report Server.
Actually, now that I think about it, to clarify, here's a simple example
that applies to 1 of the 2 parameters.
@.param1 int,
@.param2 int = @.param1
So basically if I don't specify anything for @.param2, it should just be
equal to what I specified for @.param1. However, there doesn't seem to be any
way in Data View not to specify anything. Leaving it set to <blank> I think
it still tries to set the parameter to something, and obviously to something
that causes an error.
I think that's about the best I can do to explain it. It's not like it
prevents me from using Reporting Services, but it is very annoying and seems
counterintuitive.
Thanks for your help.
"Aaron Williams" wrote:
> Could you provide an example of your stored proc?
> Lets make sure we're on the same page. If I get what you're saying, your
> stored proc allows you to send a parameter value if desired, but if you don't
> send anything, then it will still return a query. So your stored proc might
> look something like this:
> CREATE proc MyStoredProcedure
> @.MyParameter int = null
> AS
> SELECT * FROM MyTable
> WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> In this case, you can run it in query analyzer like so:
> exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> In this case, the first would return one record, and the second would return
> ALL records. So, if this is what you mean, then your problem is that when
> you try to pass a ' ' (blank value) to the report parameter you get an error.
> '
> -Aaron
>
> "Stefan Wrobel" wrote:
> > By the way, I'm speaking about when designing a report in Visual Studio
> >
> > "Stefan Wrobel" wrote:
> >
> > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > run the report, it's fine if I allow blank values, and there is no error.
> > > Only in Data View do I get the error when I try to run it or do refresh
> > > fields, and it asks me to input all parameters.
> > >
> > > "Aaron Williams" wrote:
> > >
> > > > Did you set the parameter to allow blank values?
> > > >
> > > > "Stefan Wrobel" wrote:
> > > >
> > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > work correctly.|||Could you provide an example of your stored proc?
Lets make sure we're on the same page. If I get what you're saying, your
stored proc allows you to send a parameter value if desired, but if you don't
send anything, then it will still return a query. So your stored proc might
look something like this:
CREATE proc MyStoredProcedure
@.MyParameter int = null
AS
SELECT * FROM MyTable
WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
In this case, you can run it in query analyzer like so:
exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
In this case, the first would return one record, and the second would return
ALL records. So, if this is what you mean, then your problem is that when
you try to pass a ' ' (blank value) to the report parameter you get an error.
'
-Aaron
"Stefan Wrobel" wrote:
> By the way, I'm speaking about when designing a report in Visual Studio
> "Stefan Wrobel" wrote:
> > I'm talking about the parameters in a SQL Server stored procedure. When I
> > run the report, it's fine if I allow blank values, and there is no error.
> > Only in Data View do I get the error when I try to run it or do refresh
> > fields, and it asks me to input all parameters.
> >
> > "Aaron Williams" wrote:
> >
> > > Did you set the parameter to allow blank values?
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > I have a stored procedure that gives different outputs based on whether an
> > > > input parameter is given or not. When I set the parameter to blank, not
> > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > and not inputting anything for that parameter, but instead I get a "input
> > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > behavior I would expect. I think it is very important that blank parameters
> > > > work correctly.|||I think the way you want it do it will really only work in Query Analyzer.
Reporting Services gets a list of all the parameters in the stored procedure
when you create the dataset. I assume you could do what you said and remove
the unnessisary parameters from the list, but then you no longer have the
option of using them which is obviously something you want to do.
Another option would be to change your stored procedure so that the optional
parameters can accept NULL's and then change the values later. That way
Reporting Services can send a null value to those parameters and you can
still use them the way you desire. Here's an example of how I would change
your stored proc so you can do what you're looking for.
CREATE proc Tmp_TestParams
@.param1 int,
@.param2 int = null,
@.param3 int = null
AS
-- Here's where you set the value of the blank parameters
IF @.param2 IS null
BEGIN
SET @.param2 = @.param1
END
IF @.param3 is null
BEGIN
SET @.param3 = @.param2
END
SELECT @.param1 as Param1, @.param2 as Param2, @.param3 as Param3
Go
exec Tmp_TestParams 1, null, null
Hope this helps!
-Aaron
"Stefan Wrobel" wrote:
> That seems about correct. I assumed if you specified a parameter as blank it
> was like not sending it to the SP at all. For clarification, I only need to
> use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
> need from the parameters list. I assume its as if the Report Server doesn't
> even know these parameters exist, and doesn't attempt to set them to
> anything. This is how I want it. However, in Data View, if I try to get the
> fields in the first place, I have to give some sort of value for all 4
> parameters, because if I leave the 2 I don't need set to <Blank>, I get that
> error. The only time I ever have run into this problem is while designing
> the Report, but not anywhere else. It works fine in Query Analyzer, and also
> when I run the report through Report Server.
> Actually, now that I think about it, to clarify, here's a simple example
> that applies to 1 of the 2 parameters.
> @.param1 int,
> @.param2 int = @.param1
> So basically if I don't specify anything for @.param2, it should just be
> equal to what I specified for @.param1. However, there doesn't seem to be any
> way in Data View not to specify anything. Leaving it set to <blank> I think
> it still tries to set the parameter to something, and obviously to something
> that causes an error.
> I think that's about the best I can do to explain it. It's not like it
> prevents me from using Reporting Services, but it is very annoying and seems
> counterintuitive.
> Thanks for your help.
> "Aaron Williams" wrote:
> > Could you provide an example of your stored proc?
> >
> > Lets make sure we're on the same page. If I get what you're saying, your
> > stored proc allows you to send a parameter value if desired, but if you don't
> > send anything, then it will still return a query. So your stored proc might
> > look something like this:
> >
> > CREATE proc MyStoredProcedure
> > @.MyParameter int = null
> >
> > AS
> >
> > SELECT * FROM MyTable
> > WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> >
> > In this case, you can run it in query analyzer like so:
> >
> > exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> >
> > In this case, the first would return one record, and the second would return
> > ALL records. So, if this is what you mean, then your problem is that when
> > you try to pass a ' ' (blank value) to the report parameter you get an error.
> > '
> >
> > -Aaron
> >
> >
> > "Stefan Wrobel" wrote:
> >
> > > By the way, I'm speaking about when designing a report in Visual Studio
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > > run the report, it's fine if I allow blank values, and there is no error.
> > > > Only in Data View do I get the error when I try to run it or do refresh
> > > > fields, and it asks me to input all parameters.
> > > >
> > > > "Aaron Williams" wrote:
> > > >
> > > > > Did you set the parameter to allow blank values?
> > > > >
> > > > > "Stefan Wrobel" wrote:
> > > > >
> > > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > > work correctly.|||Good suggestion, thanks!
"Aaron Williams" wrote:
> I think the way you want it do it will really only work in Query Analyzer.
> Reporting Services gets a list of all the parameters in the stored procedure
> when you create the dataset. I assume you could do what you said and remove
> the unnessisary parameters from the list, but then you no longer have the
> option of using them which is obviously something you want to do.
> Another option would be to change your stored procedure so that the optional
> parameters can accept NULL's and then change the values later. That way
> Reporting Services can send a null value to those parameters and you can
> still use them the way you desire. Here's an example of how I would change
> your stored proc so you can do what you're looking for.
> CREATE proc Tmp_TestParams
> @.param1 int,
> @.param2 int = null,
> @.param3 int = null
> AS
> -- Here's where you set the value of the blank parameters
> IF @.param2 IS null
> BEGIN
> SET @.param2 = @.param1
> END
> IF @.param3 is null
> BEGIN
> SET @.param3 = @.param2
> END
> SELECT @.param1 as Param1, @.param2 as Param2, @.param3 as Param3
> Go
> exec Tmp_TestParams 1, null, null
> Hope this helps!
> -Aaron
> "Stefan Wrobel" wrote:
> > That seems about correct. I assumed if you specified a parameter as blank it
> > was like not sending it to the SP at all. For clarification, I only need to
> > use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
> > need from the parameters list. I assume its as if the Report Server doesn't
> > even know these parameters exist, and doesn't attempt to set them to
> > anything. This is how I want it. However, in Data View, if I try to get the
> > fields in the first place, I have to give some sort of value for all 4
> > parameters, because if I leave the 2 I don't need set to <Blank>, I get that
> > error. The only time I ever have run into this problem is while designing
> > the Report, but not anywhere else. It works fine in Query Analyzer, and also
> > when I run the report through Report Server.
> >
> > Actually, now that I think about it, to clarify, here's a simple example
> > that applies to 1 of the 2 parameters.
> >
> > @.param1 int,
> > @.param2 int = @.param1
> >
> > So basically if I don't specify anything for @.param2, it should just be
> > equal to what I specified for @.param1. However, there doesn't seem to be any
> > way in Data View not to specify anything. Leaving it set to <blank> I think
> > it still tries to set the parameter to something, and obviously to something
> > that causes an error.
> >
> > I think that's about the best I can do to explain it. It's not like it
> > prevents me from using Reporting Services, but it is very annoying and seems
> > counterintuitive.
> >
> > Thanks for your help.
> >
> > "Aaron Williams" wrote:
> >
> > > Could you provide an example of your stored proc?
> > >
> > > Lets make sure we're on the same page. If I get what you're saying, your
> > > stored proc allows you to send a parameter value if desired, but if you don't
> > > send anything, then it will still return a query. So your stored proc might
> > > look something like this:
> > >
> > > CREATE proc MyStoredProcedure
> > > @.MyParameter int = null
> > >
> > > AS
> > >
> > > SELECT * FROM MyTable
> > > WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> > >
> > > In this case, you can run it in query analyzer like so:
> > >
> > > exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> > >
> > > In this case, the first would return one record, and the second would return
> > > ALL records. So, if this is what you mean, then your problem is that when
> > > you try to pass a ' ' (blank value) to the report parameter you get an error.
> > > '
> > >
> > > -Aaron
> > >
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > By the way, I'm speaking about when designing a report in Visual Studio
> > > >
> > > > "Stefan Wrobel" wrote:
> > > >
> > > > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > > > run the report, it's fine if I allow blank values, and there is no error.
> > > > > Only in Data View do I get the error when I try to run it or do refresh
> > > > > fields, and it asks me to input all parameters.
> > > > >
> > > > > "Aaron Williams" wrote:
> > > > >
> > > > > > Did you set the parameter to allow blank values?
> > > > > >
> > > > > > "Stefan Wrobel" wrote:
> > > > > >
> > > > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > > > work correctly.|||Did you set the parameter to allow blank values?
"Stefan Wrobel" wrote:
> I have a stored procedure that gives different outputs based on whether an
> input parameter is given or not. When I set the parameter to blank, not
> NULL, I assume this will give the same result as running it in Query Analyzer
> and not inputting anything for that parameter, but instead I get a "input
> string was not in the correct format" error. Is this a bug? It is not the
> behavior I would expect. I think it is very important that blank parameters
> work correctly.|||By the way, I'm speaking about when designing a report in Visual Studio
"Stefan Wrobel" wrote:
> I'm talking about the parameters in a SQL Server stored procedure. When I
> run the report, it's fine if I allow blank values, and there is no error.
> Only in Data View do I get the error when I try to run it or do refresh
> fields, and it asks me to input all parameters.
> "Aaron Williams" wrote:
> > Did you set the parameter to allow blank values?
> >
> > "Stefan Wrobel" wrote:
> >
> > > I have a stored procedure that gives different outputs based on whether an
> > > input parameter is given or not. When I set the parameter to blank, not
> > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > and not inputting anything for that parameter, but instead I get a "input
> > > string was not in the correct format" error. Is this a bug? It is not the
> > > behavior I would expect. I think it is very important that blank parameters
> > > work correctly.
input parameter is given or not. When I set the parameter to blank, not
NULL, I assume this will give the same result as running it in Query Analyzer
and not inputting anything for that parameter, but instead I get a "input
string was not in the correct format" error. Is this a bug? It is not the
behavior I would expect. I think it is very important that blank parameters
work correctly.I'm talking about the parameters in a SQL Server stored procedure. When I
run the report, it's fine if I allow blank values, and there is no error.
Only in Data View do I get the error when I try to run it or do refresh
fields, and it asks me to input all parameters.
"Aaron Williams" wrote:
> Did you set the parameter to allow blank values?
> "Stefan Wrobel" wrote:
> > I have a stored procedure that gives different outputs based on whether an
> > input parameter is given or not. When I set the parameter to blank, not
> > NULL, I assume this will give the same result as running it in Query Analyzer
> > and not inputting anything for that parameter, but instead I get a "input
> > string was not in the correct format" error. Is this a bug? It is not the
> > behavior I would expect. I think it is very important that blank parameters
> > work correctly.|||That seems about correct. I assumed if you specified a parameter as blank it
was like not sending it to the SP at all. For clarification, I only need to
use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
need from the parameters list. I assume its as if the Report Server doesn't
even know these parameters exist, and doesn't attempt to set them to
anything. This is how I want it. However, in Data View, if I try to get the
fields in the first place, I have to give some sort of value for all 4
parameters, because if I leave the 2 I don't need set to <Blank>, I get that
error. The only time I ever have run into this problem is while designing
the Report, but not anywhere else. It works fine in Query Analyzer, and also
when I run the report through Report Server.
Actually, now that I think about it, to clarify, here's a simple example
that applies to 1 of the 2 parameters.
@.param1 int,
@.param2 int = @.param1
So basically if I don't specify anything for @.param2, it should just be
equal to what I specified for @.param1. However, there doesn't seem to be any
way in Data View not to specify anything. Leaving it set to <blank> I think
it still tries to set the parameter to something, and obviously to something
that causes an error.
I think that's about the best I can do to explain it. It's not like it
prevents me from using Reporting Services, but it is very annoying and seems
counterintuitive.
Thanks for your help.
"Aaron Williams" wrote:
> Could you provide an example of your stored proc?
> Lets make sure we're on the same page. If I get what you're saying, your
> stored proc allows you to send a parameter value if desired, but if you don't
> send anything, then it will still return a query. So your stored proc might
> look something like this:
> CREATE proc MyStoredProcedure
> @.MyParameter int = null
> AS
> SELECT * FROM MyTable
> WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> In this case, you can run it in query analyzer like so:
> exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> In this case, the first would return one record, and the second would return
> ALL records. So, if this is what you mean, then your problem is that when
> you try to pass a ' ' (blank value) to the report parameter you get an error.
> '
> -Aaron
>
> "Stefan Wrobel" wrote:
> > By the way, I'm speaking about when designing a report in Visual Studio
> >
> > "Stefan Wrobel" wrote:
> >
> > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > run the report, it's fine if I allow blank values, and there is no error.
> > > Only in Data View do I get the error when I try to run it or do refresh
> > > fields, and it asks me to input all parameters.
> > >
> > > "Aaron Williams" wrote:
> > >
> > > > Did you set the parameter to allow blank values?
> > > >
> > > > "Stefan Wrobel" wrote:
> > > >
> > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > work correctly.|||Could you provide an example of your stored proc?
Lets make sure we're on the same page. If I get what you're saying, your
stored proc allows you to send a parameter value if desired, but if you don't
send anything, then it will still return a query. So your stored proc might
look something like this:
CREATE proc MyStoredProcedure
@.MyParameter int = null
AS
SELECT * FROM MyTable
WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
In this case, you can run it in query analyzer like so:
exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
In this case, the first would return one record, and the second would return
ALL records. So, if this is what you mean, then your problem is that when
you try to pass a ' ' (blank value) to the report parameter you get an error.
'
-Aaron
"Stefan Wrobel" wrote:
> By the way, I'm speaking about when designing a report in Visual Studio
> "Stefan Wrobel" wrote:
> > I'm talking about the parameters in a SQL Server stored procedure. When I
> > run the report, it's fine if I allow blank values, and there is no error.
> > Only in Data View do I get the error when I try to run it or do refresh
> > fields, and it asks me to input all parameters.
> >
> > "Aaron Williams" wrote:
> >
> > > Did you set the parameter to allow blank values?
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > I have a stored procedure that gives different outputs based on whether an
> > > > input parameter is given or not. When I set the parameter to blank, not
> > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > and not inputting anything for that parameter, but instead I get a "input
> > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > behavior I would expect. I think it is very important that blank parameters
> > > > work correctly.|||I think the way you want it do it will really only work in Query Analyzer.
Reporting Services gets a list of all the parameters in the stored procedure
when you create the dataset. I assume you could do what you said and remove
the unnessisary parameters from the list, but then you no longer have the
option of using them which is obviously something you want to do.
Another option would be to change your stored procedure so that the optional
parameters can accept NULL's and then change the values later. That way
Reporting Services can send a null value to those parameters and you can
still use them the way you desire. Here's an example of how I would change
your stored proc so you can do what you're looking for.
CREATE proc Tmp_TestParams
@.param1 int,
@.param2 int = null,
@.param3 int = null
AS
-- Here's where you set the value of the blank parameters
IF @.param2 IS null
BEGIN
SET @.param2 = @.param1
END
IF @.param3 is null
BEGIN
SET @.param3 = @.param2
END
SELECT @.param1 as Param1, @.param2 as Param2, @.param3 as Param3
Go
exec Tmp_TestParams 1, null, null
Hope this helps!
-Aaron
"Stefan Wrobel" wrote:
> That seems about correct. I assumed if you specified a parameter as blank it
> was like not sending it to the SP at all. For clarification, I only need to
> use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
> need from the parameters list. I assume its as if the Report Server doesn't
> even know these parameters exist, and doesn't attempt to set them to
> anything. This is how I want it. However, in Data View, if I try to get the
> fields in the first place, I have to give some sort of value for all 4
> parameters, because if I leave the 2 I don't need set to <Blank>, I get that
> error. The only time I ever have run into this problem is while designing
> the Report, but not anywhere else. It works fine in Query Analyzer, and also
> when I run the report through Report Server.
> Actually, now that I think about it, to clarify, here's a simple example
> that applies to 1 of the 2 parameters.
> @.param1 int,
> @.param2 int = @.param1
> So basically if I don't specify anything for @.param2, it should just be
> equal to what I specified for @.param1. However, there doesn't seem to be any
> way in Data View not to specify anything. Leaving it set to <blank> I think
> it still tries to set the parameter to something, and obviously to something
> that causes an error.
> I think that's about the best I can do to explain it. It's not like it
> prevents me from using Reporting Services, but it is very annoying and seems
> counterintuitive.
> Thanks for your help.
> "Aaron Williams" wrote:
> > Could you provide an example of your stored proc?
> >
> > Lets make sure we're on the same page. If I get what you're saying, your
> > stored proc allows you to send a parameter value if desired, but if you don't
> > send anything, then it will still return a query. So your stored proc might
> > look something like this:
> >
> > CREATE proc MyStoredProcedure
> > @.MyParameter int = null
> >
> > AS
> >
> > SELECT * FROM MyTable
> > WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> >
> > In this case, you can run it in query analyzer like so:
> >
> > exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> >
> > In this case, the first would return one record, and the second would return
> > ALL records. So, if this is what you mean, then your problem is that when
> > you try to pass a ' ' (blank value) to the report parameter you get an error.
> > '
> >
> > -Aaron
> >
> >
> > "Stefan Wrobel" wrote:
> >
> > > By the way, I'm speaking about when designing a report in Visual Studio
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > > run the report, it's fine if I allow blank values, and there is no error.
> > > > Only in Data View do I get the error when I try to run it or do refresh
> > > > fields, and it asks me to input all parameters.
> > > >
> > > > "Aaron Williams" wrote:
> > > >
> > > > > Did you set the parameter to allow blank values?
> > > > >
> > > > > "Stefan Wrobel" wrote:
> > > > >
> > > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > > work correctly.|||Good suggestion, thanks!
"Aaron Williams" wrote:
> I think the way you want it do it will really only work in Query Analyzer.
> Reporting Services gets a list of all the parameters in the stored procedure
> when you create the dataset. I assume you could do what you said and remove
> the unnessisary parameters from the list, but then you no longer have the
> option of using them which is obviously something you want to do.
> Another option would be to change your stored procedure so that the optional
> parameters can accept NULL's and then change the values later. That way
> Reporting Services can send a null value to those parameters and you can
> still use them the way you desire. Here's an example of how I would change
> your stored proc so you can do what you're looking for.
> CREATE proc Tmp_TestParams
> @.param1 int,
> @.param2 int = null,
> @.param3 int = null
> AS
> -- Here's where you set the value of the blank parameters
> IF @.param2 IS null
> BEGIN
> SET @.param2 = @.param1
> END
> IF @.param3 is null
> BEGIN
> SET @.param3 = @.param2
> END
> SELECT @.param1 as Param1, @.param2 as Param2, @.param3 as Param3
> Go
> exec Tmp_TestParams 1, null, null
> Hope this helps!
> -Aaron
> "Stefan Wrobel" wrote:
> > That seems about correct. I assumed if you specified a parameter as blank it
> > was like not sending it to the SP at all. For clarification, I only need to
> > use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
> > need from the parameters list. I assume its as if the Report Server doesn't
> > even know these parameters exist, and doesn't attempt to set them to
> > anything. This is how I want it. However, in Data View, if I try to get the
> > fields in the first place, I have to give some sort of value for all 4
> > parameters, because if I leave the 2 I don't need set to <Blank>, I get that
> > error. The only time I ever have run into this problem is while designing
> > the Report, but not anywhere else. It works fine in Query Analyzer, and also
> > when I run the report through Report Server.
> >
> > Actually, now that I think about it, to clarify, here's a simple example
> > that applies to 1 of the 2 parameters.
> >
> > @.param1 int,
> > @.param2 int = @.param1
> >
> > So basically if I don't specify anything for @.param2, it should just be
> > equal to what I specified for @.param1. However, there doesn't seem to be any
> > way in Data View not to specify anything. Leaving it set to <blank> I think
> > it still tries to set the parameter to something, and obviously to something
> > that causes an error.
> >
> > I think that's about the best I can do to explain it. It's not like it
> > prevents me from using Reporting Services, but it is very annoying and seems
> > counterintuitive.
> >
> > Thanks for your help.
> >
> > "Aaron Williams" wrote:
> >
> > > Could you provide an example of your stored proc?
> > >
> > > Lets make sure we're on the same page. If I get what you're saying, your
> > > stored proc allows you to send a parameter value if desired, but if you don't
> > > send anything, then it will still return a query. So your stored proc might
> > > look something like this:
> > >
> > > CREATE proc MyStoredProcedure
> > > @.MyParameter int = null
> > >
> > > AS
> > >
> > > SELECT * FROM MyTable
> > > WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> > >
> > > In this case, you can run it in query analyzer like so:
> > >
> > > exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> > >
> > > In this case, the first would return one record, and the second would return
> > > ALL records. So, if this is what you mean, then your problem is that when
> > > you try to pass a ' ' (blank value) to the report parameter you get an error.
> > > '
> > >
> > > -Aaron
> > >
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > By the way, I'm speaking about when designing a report in Visual Studio
> > > >
> > > > "Stefan Wrobel" wrote:
> > > >
> > > > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > > > run the report, it's fine if I allow blank values, and there is no error.
> > > > > Only in Data View do I get the error when I try to run it or do refresh
> > > > > fields, and it asks me to input all parameters.
> > > > >
> > > > > "Aaron Williams" wrote:
> > > > >
> > > > > > Did you set the parameter to allow blank values?
> > > > > >
> > > > > > "Stefan Wrobel" wrote:
> > > > > >
> > > > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > > > work correctly.|||Did you set the parameter to allow blank values?
"Stefan Wrobel" wrote:
> I have a stored procedure that gives different outputs based on whether an
> input parameter is given or not. When I set the parameter to blank, not
> NULL, I assume this will give the same result as running it in Query Analyzer
> and not inputting anything for that parameter, but instead I get a "input
> string was not in the correct format" error. Is this a bug? It is not the
> behavior I would expect. I think it is very important that blank parameters
> work correctly.|||By the way, I'm speaking about when designing a report in Visual Studio
"Stefan Wrobel" wrote:
> I'm talking about the parameters in a SQL Server stored procedure. When I
> run the report, it's fine if I allow blank values, and there is no error.
> Only in Data View do I get the error when I try to run it or do refresh
> fields, and it asks me to input all parameters.
> "Aaron Williams" wrote:
> > Did you set the parameter to allow blank values?
> >
> > "Stefan Wrobel" wrote:
> >
> > > I have a stored procedure that gives different outputs based on whether an
> > > input parameter is given or not. When I set the parameter to blank, not
> > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > and not inputting anything for that parameter, but instead I get a "input
> > > string was not in the correct format" error. Is this a bug? It is not the
> > > behavior I would expect. I think it is very important that blank parameters
> > > work correctly.
Thursday, February 16, 2012
Blank page?
In my report I have 4 lists. In the each list I have textbox (Company Name)
And matrix. Each list shows in separate page. So basically if I view report
I have 4 pages.
The problem is.
For example, If in my report parameter, which is â'Group Nameâ'
(A, B, C and D) I unselect 2 groups then I should have 2 pages but I have
still 4 pages and 2 pages is blank. How can I remove that blank pages?
I used matrix in the list to able to view each matrix in separate pages.
Thank you.On Mar 28, 1:54 pm, JT <J...@.discussions.microsoft.com> wrote:
> In my report I have 4 lists. In the each list I have textbox (Company Name)
> And matrix. Each list shows in separate page. So basically if I view report
> I have 4 pages.
> The problem is.
> For example, If in my report parameter, which is 'Group Name'
> (A, B, C and D) I unselect 2 groups then I should have 2 pages but I have
> still 4 pages and 2 pages is blank. How can I remove that blank pages?
> I used matrix in the list to able to view each matrix in separate pages.
> Thank you.
I don't work with lists often but if I were trying to do here is where
I'd start.
Try conditioning the visibility properties of your lists.
Insert an expression into the Visibility>Hidden property of the
individual lists. For the Group Name A list, try something like:
=IIF(InStr(Parameter!GroupName.Value,"A") > 0, False,True)
You may need to fiddle with the syntax some but what you're trying to
get at is
"If A is found in the Group Name param
then show the List
otherwise hide the List"
If needed, the namespace is Microsoft.VisualBasic.
If lists work like tables and other report items, the non-hidden ones
should move up and fill the white space left by the hidden ones.
HTH
toolman|||I do not have problem to hide my lists. If i unselect 2 groups it will hide
the lists but leaves the 2 blank pages. I need to remove these blank pages.
"toolman" wrote:
> On Mar 28, 1:54 pm, JT <J...@.discussions.microsoft.com> wrote:
> > In my report I have 4 lists. In the each list I have textbox (Company Name)
> > And matrix. Each list shows in separate page. So basically if I view report
> > I have 4 pages.
> > The problem is.
> > For example, If in my report parameter, which is 'Group Name'
> > (A, B, C and D) I unselect 2 groups then I should have 2 pages but I have
> > still 4 pages and 2 pages is blank. How can I remove that blank pages?
> > I used matrix in the list to able to view each matrix in separate pages.
> >
> > Thank you.
> I don't work with lists often but if I were trying to do here is where
> I'd start.
> Try conditioning the visibility properties of your lists.
> Insert an expression into the Visibility>Hidden property of the
> individual lists. For the Group Name A list, try something like:
> =IIF(InStr(Parameter!GroupName.Value,"A") > 0, False,True)
> You may need to fiddle with the syntax some but what you're trying to
> get at is
> "If A is found in the Group Name param
> then show the List
> otherwise hide the List"
> If needed, the namespace is Microsoft.VisualBasic.
> If lists work like tables and other report items, the non-hidden ones
> should move up and fill the white space left by the hidden ones.
> HTH
> toolman
>|||Try to adjust the canvas size in layout to match the report width and height
in Report Properties.
Thanks,
"JT" wrote:
> I do not have problem to hide my lists. If i unselect 2 groups it will hide
> the lists but leaves the 2 blank pages. I need to remove these blank pages.
>
> "toolman" wrote:
> > On Mar 28, 1:54 pm, JT <J...@.discussions.microsoft.com> wrote:
> > > In my report I have 4 lists. In the each list I have textbox (Company Name)
> > > And matrix. Each list shows in separate page. So basically if I view report
> > > I have 4 pages.
> > > The problem is.
> > > For example, If in my report parameter, which is 'Group Name'
> > > (A, B, C and D) I unselect 2 groups then I should have 2 pages but I have
> > > still 4 pages and 2 pages is blank. How can I remove that blank pages?
> > > I used matrix in the list to able to view each matrix in separate pages.
> > >
> > > Thank you.
> >
> > I don't work with lists often but if I were trying to do here is where
> > I'd start.
> >
> > Try conditioning the visibility properties of your lists.
> >
> > Insert an expression into the Visibility>Hidden property of the
> > individual lists. For the Group Name A list, try something like:
> > =IIF(InStr(Parameter!GroupName.Value,"A") > 0, False,True)
> > You may need to fiddle with the syntax some but what you're trying to
> > get at is
> > "If A is found in the Group Name param
> > then show the List
> > otherwise hide the List"
> > If needed, the namespace is Microsoft.VisualBasic.
> >
> > If lists work like tables and other report items, the non-hidden ones
> > should move up and fill the white space left by the hidden ones.
> >
> > HTH
> > toolman
> >|||Each list shows in separate pages but if is one list unselected (For exmp:
group A - from report parameter) then it should not show the 1 page. Instead
of that I do have the blank page. How to remove the blank page?
"naren" wrote:
> Try to adjust the canvas size in layout to match the report width and height
> in Report Properties.
> Thanks,
> "JT" wrote:
> > I do not have problem to hide my lists. If i unselect 2 groups it will hide
> > the lists but leaves the 2 blank pages. I need to remove these blank pages.
> >
> >
> > "toolman" wrote:
> >
> > > On Mar 28, 1:54 pm, JT <J...@.discussions.microsoft.com> wrote:
> > > > In my report I have 4 lists. In the each list I have textbox (Company Name)
> > > > And matrix. Each list shows in separate page. So basically if I view report
> > > > I have 4 pages.
> > > > The problem is.
> > > > For example, If in my report parameter, which is 'Group Name'
> > > > (A, B, C and D) I unselect 2 groups then I should have 2 pages but I have
> > > > still 4 pages and 2 pages is blank. How can I remove that blank pages?
> > > > I used matrix in the list to able to view each matrix in separate pages.
> > > >
> > > > Thank you.
> > >
> > > I don't work with lists often but if I were trying to do here is where
> > > I'd start.
> > >
> > > Try conditioning the visibility properties of your lists.
> > >
> > > Insert an expression into the Visibility>Hidden property of the
> > > individual lists. For the Group Name A list, try something like:
> > > =IIF(InStr(Parameter!GroupName.Value,"A") > 0, False,True)
> > > You may need to fiddle with the syntax some but what you're trying to
> > > get at is
> > > "If A is found in the Group Name param
> > > then show the List
> > > otherwise hide the List"
> > > If needed, the namespace is Microsoft.VisualBasic.
> > >
> > > If lists work like tables and other report items, the non-hidden ones
> > > should move up and fill the white space left by the hidden ones.
> > >
> > > HTH
> > > toolman
> > >
And matrix. Each list shows in separate page. So basically if I view report
I have 4 pages.
The problem is.
For example, If in my report parameter, which is â'Group Nameâ'
(A, B, C and D) I unselect 2 groups then I should have 2 pages but I have
still 4 pages and 2 pages is blank. How can I remove that blank pages?
I used matrix in the list to able to view each matrix in separate pages.
Thank you.On Mar 28, 1:54 pm, JT <J...@.discussions.microsoft.com> wrote:
> In my report I have 4 lists. In the each list I have textbox (Company Name)
> And matrix. Each list shows in separate page. So basically if I view report
> I have 4 pages.
> The problem is.
> For example, If in my report parameter, which is 'Group Name'
> (A, B, C and D) I unselect 2 groups then I should have 2 pages but I have
> still 4 pages and 2 pages is blank. How can I remove that blank pages?
> I used matrix in the list to able to view each matrix in separate pages.
> Thank you.
I don't work with lists often but if I were trying to do here is where
I'd start.
Try conditioning the visibility properties of your lists.
Insert an expression into the Visibility>Hidden property of the
individual lists. For the Group Name A list, try something like:
=IIF(InStr(Parameter!GroupName.Value,"A") > 0, False,True)
You may need to fiddle with the syntax some but what you're trying to
get at is
"If A is found in the Group Name param
then show the List
otherwise hide the List"
If needed, the namespace is Microsoft.VisualBasic.
If lists work like tables and other report items, the non-hidden ones
should move up and fill the white space left by the hidden ones.
HTH
toolman|||I do not have problem to hide my lists. If i unselect 2 groups it will hide
the lists but leaves the 2 blank pages. I need to remove these blank pages.
"toolman" wrote:
> On Mar 28, 1:54 pm, JT <J...@.discussions.microsoft.com> wrote:
> > In my report I have 4 lists. In the each list I have textbox (Company Name)
> > And matrix. Each list shows in separate page. So basically if I view report
> > I have 4 pages.
> > The problem is.
> > For example, If in my report parameter, which is 'Group Name'
> > (A, B, C and D) I unselect 2 groups then I should have 2 pages but I have
> > still 4 pages and 2 pages is blank. How can I remove that blank pages?
> > I used matrix in the list to able to view each matrix in separate pages.
> >
> > Thank you.
> I don't work with lists often but if I were trying to do here is where
> I'd start.
> Try conditioning the visibility properties of your lists.
> Insert an expression into the Visibility>Hidden property of the
> individual lists. For the Group Name A list, try something like:
> =IIF(InStr(Parameter!GroupName.Value,"A") > 0, False,True)
> You may need to fiddle with the syntax some but what you're trying to
> get at is
> "If A is found in the Group Name param
> then show the List
> otherwise hide the List"
> If needed, the namespace is Microsoft.VisualBasic.
> If lists work like tables and other report items, the non-hidden ones
> should move up and fill the white space left by the hidden ones.
> HTH
> toolman
>|||Try to adjust the canvas size in layout to match the report width and height
in Report Properties.
Thanks,
"JT" wrote:
> I do not have problem to hide my lists. If i unselect 2 groups it will hide
> the lists but leaves the 2 blank pages. I need to remove these blank pages.
>
> "toolman" wrote:
> > On Mar 28, 1:54 pm, JT <J...@.discussions.microsoft.com> wrote:
> > > In my report I have 4 lists. In the each list I have textbox (Company Name)
> > > And matrix. Each list shows in separate page. So basically if I view report
> > > I have 4 pages.
> > > The problem is.
> > > For example, If in my report parameter, which is 'Group Name'
> > > (A, B, C and D) I unselect 2 groups then I should have 2 pages but I have
> > > still 4 pages and 2 pages is blank. How can I remove that blank pages?
> > > I used matrix in the list to able to view each matrix in separate pages.
> > >
> > > Thank you.
> >
> > I don't work with lists often but if I were trying to do here is where
> > I'd start.
> >
> > Try conditioning the visibility properties of your lists.
> >
> > Insert an expression into the Visibility>Hidden property of the
> > individual lists. For the Group Name A list, try something like:
> > =IIF(InStr(Parameter!GroupName.Value,"A") > 0, False,True)
> > You may need to fiddle with the syntax some but what you're trying to
> > get at is
> > "If A is found in the Group Name param
> > then show the List
> > otherwise hide the List"
> > If needed, the namespace is Microsoft.VisualBasic.
> >
> > If lists work like tables and other report items, the non-hidden ones
> > should move up and fill the white space left by the hidden ones.
> >
> > HTH
> > toolman
> >|||Each list shows in separate pages but if is one list unselected (For exmp:
group A - from report parameter) then it should not show the 1 page. Instead
of that I do have the blank page. How to remove the blank page?
"naren" wrote:
> Try to adjust the canvas size in layout to match the report width and height
> in Report Properties.
> Thanks,
> "JT" wrote:
> > I do not have problem to hide my lists. If i unselect 2 groups it will hide
> > the lists but leaves the 2 blank pages. I need to remove these blank pages.
> >
> >
> > "toolman" wrote:
> >
> > > On Mar 28, 1:54 pm, JT <J...@.discussions.microsoft.com> wrote:
> > > > In my report I have 4 lists. In the each list I have textbox (Company Name)
> > > > And matrix. Each list shows in separate page. So basically if I view report
> > > > I have 4 pages.
> > > > The problem is.
> > > > For example, If in my report parameter, which is 'Group Name'
> > > > (A, B, C and D) I unselect 2 groups then I should have 2 pages but I have
> > > > still 4 pages and 2 pages is blank. How can I remove that blank pages?
> > > > I used matrix in the list to able to view each matrix in separate pages.
> > > >
> > > > Thank you.
> > >
> > > I don't work with lists often but if I were trying to do here is where
> > > I'd start.
> > >
> > > Try conditioning the visibility properties of your lists.
> > >
> > > Insert an expression into the Visibility>Hidden property of the
> > > individual lists. For the Group Name A list, try something like:
> > > =IIF(InStr(Parameter!GroupName.Value,"A") > 0, False,True)
> > > You may need to fiddle with the syntax some but what you're trying to
> > > get at is
> > > "If A is found in the Group Name param
> > > then show the List
> > > otherwise hide the List"
> > > If needed, the namespace is Microsoft.VisualBasic.
> > >
> > > If lists work like tables and other report items, the non-hidden ones
> > > should move up and fill the white space left by the hidden ones.
> > >
> > > HTH
> > > toolman
> > >
Blank page after specifying new parameter criteria.
I'm seeing an issue where, after changing the criteria in two date parameters
and clicking the "View Report" button, the screen is blanked out. If I click
the button again, the criteria is used, and the report is displayed.
Anyone else seeing this?
Is there any solution this?I've been having simular problems, although only on the preview pane, on the
report server it works fine
"Aaron" wrote:
> I'm seeing an issue where, after changing the criteria in two date parameters
> and clicking the "View Report" button, the screen is blanked out. If I click
> the button again, the criteria is used, and the report is displayed.
> Anyone else seeing this?
> Is there any solution this?|||I did notice that if I take out the default values I specified in my
parameters, it doesn't do this anymore. Weird...
Must be a bug.
"Antoon" wrote:
> I've been having simular problems, although only on the preview pane, on the
> report server it works fine
> "Aaron" wrote:
> > I'm seeing an issue where, after changing the criteria in two date parameters
> > and clicking the "View Report" button, the screen is blanked out. If I click
> > the button again, the criteria is used, and the report is displayed.
> >
> > Anyone else seeing this?
> >
> > Is there any solution this?
and clicking the "View Report" button, the screen is blanked out. If I click
the button again, the criteria is used, and the report is displayed.
Anyone else seeing this?
Is there any solution this?I've been having simular problems, although only on the preview pane, on the
report server it works fine
"Aaron" wrote:
> I'm seeing an issue where, after changing the criteria in two date parameters
> and clicking the "View Report" button, the screen is blanked out. If I click
> the button again, the criteria is used, and the report is displayed.
> Anyone else seeing this?
> Is there any solution this?|||I did notice that if I take out the default values I specified in my
parameters, it doesn't do this anymore. Weird...
Must be a bug.
"Antoon" wrote:
> I've been having simular problems, although only on the preview pane, on the
> report server it works fine
> "Aaron" wrote:
> > I'm seeing an issue where, after changing the criteria in two date parameters
> > and clicking the "View Report" button, the screen is blanked out. If I click
> > the button again, the criteria is used, and the report is displayed.
> >
> > Anyone else seeing this?
> >
> > Is there any solution this?
Subscribe to:
Posts (Atom)