boolean value in column

I need to do an aggregate query against an integer data type, and want to
return three columns based on the value: 1, 2, or 3. If the field name is
"Action", I should be able to sum (action=1), (action=2), and (action=3).
First I tried the following:
SELECT SU.SProc, SUM(SU.[Action] = 1), SUM(SU.[Action] = 2), SUM(SU.[Action]
= 3)
The result of each would be -1 or zero. That doesn't work, I guess because
no boolean data type exists. Using CAST or CONVERT doesn't work either for
the same reason: (SU.[Action] = 1) cannot be interpreted. So thought I would
try IF/ELSE, but cannot get the syntax right. I've used it before but can't
remember how I did it. May have been with DB2. Should go something like:
SELECT SU.SProc, SUM(IF SU.[Action] = 1 BEGIN 1 END ELSE BEGIN 0 END),
SUM(IF SU.[Action] = 2 BEGIN 1 END ELSE BEGIN 0 END), SUM(IF SU.[Action] = 3
BEGIN 1 END ELSE BEGIN 0 END)
If someone would enlighten me I'd appreciate it. ThanksSELECT
SUM CASE WHEN foobar_action = 1 then 1 ELSE 0 END) AS total_1,
SUM CASE WHEN foobar_action = 2 then 1 ELSE 0 END) AS total_2,
SUM CASE WHEN foobar_action = 3 then 1 ELSE 0 END) AS total_3
FROM Foobar;|||David McDivitt wrote:
> I need to do an aggregate query against an integer data type, and
> want to return three columns based on the value: 1, 2, or 3. If the
> field name is "Action", I should be able to sum (action=1),
> (action=2), and (action=3). First I tried the following:
> SELECT SU.SProc, SUM(SU.[Action] = 1), SUM(SU.[Action] = 2),
> SUM(SU.[Action] = 3)
> The result of each would be -1 or zero. That doesn't work, I guess
> because no boolean data type exists. Using CAST or CONVERT doesn't
> work either for the same reason: (SU.[Action] = 1) cannot be
> interpreted. So thought I would try IF/ELSE, but cannot get the
> syntax right. I've used it before but can't remember how I did it.
> May have been with DB2. Should go something like:
> SELECT SU.SProc, SUM(IF SU.[Action] = 1 BEGIN 1 END ELSE BEGIN 0 END),
> SUM(IF SU.[Action] = 2 BEGIN 1 END ELSE BEGIN 0 END), SUM(IF
> SU.[Action] = 3 BEGIN 1 END ELSE BEGIN 0 END)
> If someone would enlighten me I'd appreciate it. Thanks
Try using a CASE statement:
create table #abc (action int)
insert into #abc values (1)
insert into #abc values (2)
insert into #abc values (2)
insert into #abc values (3)
insert into #abc values (3)
insert into #abc values (3)
Select
SUM( CASE action
WHEN 1 THEN 1
ELSE 0
END ) as "Action 1",
SUM( CASE action
WHEN 2 THEN 1
ELSE 0
END ) as "Action 2",
SUM( CASE action
WHEN 3 THEN 1
ELSE 0
END ) as "Action 3"
From #abc
Action 1 Action 2 Action 3
-- -- --
1 2 3
drop table #abc
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Look up CASE WHEN in BOL
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"David McDivitt" <x12code-del@.del-yahoo.com> wrote in message
news:ve9pg1dr6kvo45sc4s2mmmt003cisrhg6t@.
4ax.com...
>I need to do an aggregate query against an integer data type, and want to
> return three columns based on the value: 1, 2, or 3. If the field name is
> "Action", I should be able to sum (action=1), (action=2), and (action=3).
> First I tried the following:
> SELECT SU.SProc, SUM(SU.[Action] = 1), SUM(SU.[Action] = 2),
> SUM(SU.[Action]
> = 3)
> The result of each would be -1 or zero. That doesn't work, I guess because
> no boolean data type exists. Using CAST or CONVERT doesn't work either for
> the same reason: (SU.[Action] = 1) cannot be interpreted. So thought I
> would
> try IF/ELSE, but cannot get the syntax right. I've used it before but
> can't
> remember how I did it. May have been with DB2. Should go something like:
> SELECT SU.SProc, SUM(IF SU.[Action] = 1 BEGIN 1 END ELSE BEGIN 0 END),
> SUM(IF SU.[Action] = 2 BEGIN 1 END ELSE BEGIN 0 END), SUM(IF SU.[Action] =
> 3
> BEGIN 1 END ELSE BEGIN 0 END)
> If someone would enlighten me I'd appreciate it. Thanks
>|||>From: "David Gugick" <david.gugick-nospam@.quest.com>
>Date: Wed, 24 Aug 2005 13:17:27 -0400
>Lines: 62
>David McDivitt wrote:
>Try using a CASE statement:
>create table #abc (action int)
>insert into #abc values (1)
>insert into #abc values (2)
>insert into #abc values (2)
>insert into #abc values (3)
>insert into #abc values (3)
>insert into #abc values (3)
>Select
> SUM( CASE action
> WHEN 1 THEN 1
> ELSE 0
Thanks guys the CASE statement was what I was looking for.

Blocking Problems

We have a stored proc that is causing alot of blocking
problems. Basically, it selects a field based on the PK
and returns the value (0 or 1) as an output parameter:
SELECT @.Anonymous_Bool = Anonymous_Bool
FROM Member_Properties
WHERE GUID = @.GUID
The estimated execution plan shows that it does a
clustered index seek. When we tested it out it was fine,
but when it's in production, it looks like it starts
causing massive blocking problems. I don't see how the
proc itself could be a problem since it is very
efficient.
Has anyone else encountered a similar problem? could it
possibly be something in the front end code? or maybe
connection pooling? any ideas on a direction to look
would be much appreciated...Is @.Guid a UniqueIdentifier datatype? If not and the GUID column is it may
have an issue actually using the index properly. But I suspect you are
seeing the results of the connection changing the isolation level to
serializable. Or you can be seeing the effects of page splitting due to the
Guid is clustered. Check to see what is going on with profile on the
connections doing the blocking. In my opinion Guid's are a horrible PK and
especially if you cluster on them. There are few if any alternatives that
can perform worse than a Guid in a clustered index on a table with new rows
being inserted.
--
Andrew J. Kelly
SQL Server MVP
"A. Sugrue" <sugruea@.hotmail.com> wrote in message
news:058701c356ec$ec57e640$a401280a@.phx.gbl...
> We have a stored proc that is causing alot of blocking
> problems. Basically, it selects a field based on the PK
> and returns the value (0 or 1) as an output parameter:
> SELECT @.Anonymous_Bool = Anonymous_Bool
> FROM Member_Properties
> WHERE GUID = @.GUID
> The estimated execution plan shows that it does a
> clustered index seek. When we tested it out it was fine,
> but when it's in production, it looks like it starts
> causing massive blocking problems. I don't see how the
> proc itself could be a problem since it is very
> efficient.
> Has anyone else encountered a similar problem? could it
> possibly be something in the front end code? or maybe
> connection pooling? any ideas on a direction to look
> would be much appreciated...|||Yes it is a uniqueidentifier and is the PK with a
clustered index and it is also the rowguidcol for merge
replication.
>--Original Message--
>Is @.Guid a UniqueIdentifier datatype? If not and the
GUID column is it may
>have an issue actually using the index properly. But I
suspect you are
>seeing the results of the connection changing the
isolation level to
>serializable. Or you can be seeing the effects of page
splitting due to the
>Guid is clustered. Check to see what is going on with
profile on the
>connections doing the blocking. In my opinion Guid's are
a horrible PK and
>especially if you cluster on them. There are few if any
alternatives that
>can perform worse than a Guid in a clustered index on a
table with new rows
>being inserted.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"A. Sugrue" <sugruea@.hotmail.com> wrote in message
>news:058701c356ec$ec57e640$a401280a@.phx.gbl...
>> We have a stored proc that is causing alot of blocking
>> problems. Basically, it selects a field based on the PK
>> and returns the value (0 or 1) as an output parameter:
>> SELECT @.Anonymous_Bool = Anonymous_Bool
>> FROM Member_Properties
>> WHERE GUID = @.GUID
>> The estimated execution plan shows that it does a
>> clustered index seek. When we tested it out it was
fine,
>> but when it's in production, it looks like it starts
>> causing massive blocking problems. I don't see how the
>> proc itself could be a problem since it is very
>> efficient.
>> Has anyone else encountered a similar problem? could it
>> possibly be something in the front end code? or maybe
>> connection pooling? any ideas on a direction to look
>> would be much appreciated...
>
>.
>|||No, but since we already had a uniqueidentifier there as
the PK, there was no need to add another column to be the
rowguidcol.
>--Original Message--
>Having a Guid for merge replication purposes is fine but
that does not mean
>it has to be the PK.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"A. Sugrue" <sugruea@.hotmail.com> wrote in message
>news:053f01c357ae$41b2aaa0$a301280a@.phx.gbl...
>> Yes it is a uniqueidentifier and is the PK with a
>> clustered index and it is also the rowguidcol for merge
>> replication.
>> >--Original Message--
>> >Is @.Guid a UniqueIdentifier datatype? If not and the
>> GUID column is it may
>> >have an issue actually using the index properly. But I
>> suspect you are
>> >seeing the results of the connection changing the
>> isolation level to
>> >serializable. Or you can be seeing the effects of page
>> splitting due to the
>> >Guid is clustered. Check to see what is going on with
>> profile on the
>> >connections doing the blocking. In my opinion Guid's
are
>> a horrible PK and
>> >especially if you cluster on them. There are few if
any
>> alternatives that
>> >can perform worse than a Guid in a clustered index on a
>> table with new rows
>> >being inserted.
>> >
>> >--
>> >
>> >Andrew J. Kelly
>> >SQL Server MVP
>> >
>> >
>> >"A. Sugrue" <sugruea@.hotmail.com> wrote in message
>> >news:058701c356ec$ec57e640$a401280a@.phx.gbl...
>> >> We have a stored proc that is causing alot of
blocking
>> >> problems. Basically, it selects a field based on
the PK
>> >> and returns the value (0 or 1) as an output
parameter:
>> >>
>> >> SELECT @.Anonymous_Bool = Anonymous_Bool
>> >> FROM Member_Properties
>> >> WHERE GUID = @.GUID
>> >>
>> >> The estimated execution plan shows that it does a
>> >> clustered index seek. When we tested it out it was
>> fine,
>> >> but when it's in production, it looks like it starts
>> >> causing massive blocking problems. I don't see how
the
>> >> proc itself could be a problem since it is very
>> >> efficient.
>> >>
>> >> Has anyone else encountered a similar problem?
could it
>> >> possibly be something in the front end code? or
maybe
>> >> connection pooling? any ideas on a direction to look
>> >> would be much appreciated...
>> >
>> >
>> >.
>> >
>
>.
>

BLOB performance SQL 2005 Do's and Don'ts

Hi,
I have a content based site with over a 1000 downloadable word docs
which are currently stored on a server filesystem.
however these are split in muliple folder paths for different clients
and i am trying to find a more efficient way of doing this. i have
been looking into storing the files as blobs in a db rather than
searching on a filename on a file IO etc
in SQL 2000 this was frowned upon as it was so poorly managed but has
this been improved in 2005 or would i be better off using the same
methods i have in place now?
any comments?If you're searching on filenames, why don't you store the filename in the
database?
"km200281" <k.a.mortimer@.gmail.com> wrote in message
news:1147267971.088679.264550@.u72g2000cwu.googlegroups.com...
> Hi,
> I have a content based site with over a 1000 downloadable word docs
> which are currently stored on a server filesystem.
> however these are split in muliple folder paths for different clients
> and i am trying to find a more efficient way of doing this. i have
> been looking into storing the files as blobs in a db rather than
> searching on a filename on a file IO etc
> in SQL 2000 this was frowned upon as it was so poorly managed but has
> this been improved in 2005 or would i be better off using the same
> methods i have in place now?
> any comments?
>|||Hi,
that is what we do now however a large number of updates are done to
the site on a regular basis and this is because we deal with multiple
versions of the same document in different locations its not easy to
manage hence my query regarding the use of BLOBs

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.

Blank Pages in report

I am supporting a web based application (ASP.NET 1.1) that uses RS 2005 on the production server. I needed to change a report, I make my changes, it looks fine in the Developer Viewer and it exports fine to PDF on my PC. I have deployed it to the production server but when you pull the report within the application (PDF format) it has 3 additional blank pages on the end of the report. The report is the correct size. It does not excede the 8/11 page size including the margins, there are no hidden fields. The report does have 4 tables. I have even viewed the report from //localhost/reports on the server and it shows only one page and exports to pdf with one page. I have looked at all the postings that deal with Blank pages and nothing seems to correct the problem.

Does anyone have any ideas?

Thanks

If you take things out does it still put 3 extra pages in?|||I can try that tonight. Since this only happens in production, I must wait to off hours.|||

Ok, I found the issue. This report is just a sub report to a master report. The master report was not formated to fit on 8.5/11 paper. Thus it pushed the additional blank pages. Once I formated the Master report, there where no more blank pages.

Thanks

|||

This is the most common problem for blank pages in a report.

In development the user will accidentally extend the width of the report beyond normal page limits, so extra pages get printed.

Blank Pages in a report <-- text put there for people searching on this problem.

Make sure that your main report page setup is correct for the hieght and width of your report, and also double check any subreports.

Daryl

Blank Pages in report

I am supporting a web based application (ASP.NET 1.1) that uses RS 2005 on the production server. I needed to change a report, I make my changes, it looks fine in the Developer Viewer and it exports fine to PDF on my PC. I have deployed it to the production server but when you pull the report within the application (PDF format) it has 3 additional blank pages on the end of the report. The report is the correct size. It does not excede the 8/11 page size including the margins, there are no hidden fields. The report does have 4 tables. I have even viewed the report from //localhost/reports on the server and it shows only one page and exports to pdf with one page. I have looked at all the postings that deal with Blank pages and nothing seems to correct the problem.

Does anyone have any ideas?

Thanks

If you take things out does it still put 3 extra pages in?
|||I can try that tonight. Since this only happens in production, I must wait to off hours.|||

Ok, I found the issue. This report is just a sub report to a master report. The master report was not formated to fit on 8.5/11 paper. Thus it pushed the additional blank pages. Once I formated the Master report, there where no more blank pages.

Thanks

|||

This is the most common problem for blank pages in a report.

In development the user will accidentally extend the width of the report beyond normal page limits, so extra pages get printed.

Blank Pages in a report <-- text put there for people searching on this problem.

Make sure that your main report page setup is correct for the hieght and width of your report, and also double check any subreports.

Daryl

bitwise AND with Char column

based on BOL, bitwise AND operator operates only on integer data type
category (int, smallint, tinyint). but i have a char(4) column which
stores integers (disregard the db design issue) which produces the
same/correct results after using bitwise AND with another integer.
does this mean SQL server does the conversion itself?SQL Server will convert a value stored in the CHAR datatype into an INTEGER
because an INTEGER has a high
Data Type Precedence
"joeycalisay" <joeycalisay@.gmail.com> wrote in message
news:1131435508.269803.57870@.o13g2000cwo.googlegroups.com...
> based on BOL, bitwise AND operator operates only on integer data type
> category (int, smallint, tinyint). but i have a char(4) column which
> stores integers (disregard the db design issue) which produces the
> same/correct results after using bitwise AND with another integer.
> does this mean SQL server does the conversion itself?
>|||thanks a lot sir!|||Implicit conversion is at work here and that's potentially bad news.
Implicit conversions are to be avoided where possible because of their
potential to break unexpectedly under schema or code changes or even as
a result of service packs or version upgrades. Obviously a poor design
is at fault in your example but if you must accommodate it then I would
use CASE expressions:
CASE WHEN col1 = '1' AND col2 = '1' THEN 1 ELSE 0 END
David Portas
SQL Server MVP
--|||thanks for the input sir david particularly on the point of it being
breakable. poor design - yeah, i hated it when i saw it, wish i had
the time to revise it but they are at code freeze