Boolean value

How can I return boolean value to the client.
This doesn't work:
SELECT column1=case when exists (select ...) then TRUE else FALSE end,...
If I return :
SELECT column1=case when exists (select ...) then 1 else 0 end,...
then client gets the int32 value
regards,S> How can I return boolean value to the client.
You can't. There is no Boolean datatype in TSQL. There is a bit datatype, wh
ich is a integer-class
datatype restricted to the values 0, 1 and NULL. Some libraries (ADO etc) wi
ll interpret 1 as TRUE
and 0 as FALSE. If you want to return but, you have to CAST. for example
SELECT case when exists (select ...) then CAST(1 AS bit) else CAST(0 AS bit)
end AS column1
Or you can do the CAST outside the CASE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"simon" <simon.zupan@.stud-moderna.si> wrote in message news:O3fYef9oFHA.3256@.tk2msftngp13.p
hx.gbl...
> How can I return boolean value to the client.
> This doesn't work:
> SELECT column1=case when exists (select ...) then TRUE else FALSE end,...
> If I return :
> SELECT column1=case when exists (select ...) then 1 else 0 end,...
> then client gets the int32 value
> regards,S
>|||There's no boolean data type in SQL. I'd suggest using the bit data type.
Like this:
ELECT column1=case when exists (select ...) then cast(1 as bit) else cast(0
as bit) end
ML

Books On Lies - Partitioned Views

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.

blue return arrow icon on Replication monitor - significance?

Good morning,
This morning I opened up enterprise manager (sql Server 2000) and noticed a
blue return arrow looking icon on the Replication Monitor folder. When I
opened up Replication monitor I observed the same blue return arrow looking
icon on the publishers and agents folders. What is the significance of this
icon? How do I remove it?
When I get a red X icon I just restart the distribution agent and refresh
everythihg and the red x goes away. What do I need to do for this blue arrow
icon?
Thanks,
Rich
Rich,
do you mean the retrying icon? What is the message of the agent's history?
(I get this message when experiencing connectivity problems).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thank you for getting back to me on this. In the Agent History, the Last
action (11/28/06) says No Replicated Transactions are available. Is there a
site anywhere that explains what the various alert icons signify? This way I
can use the standardized lingo to describe what I am seeing.
It is still a little bit early over my way, and customers haven't been
calling about any problems (yet). Do I need to do anything or will this
resolve itself? We have, been having some connectivity problems in recent
times. But usually, I get a big red x when that happens. I restart the
Distribution Agent and it goes away.
"Paul Ibison" wrote:

> Rich,
> do you mean the retrying icon? What is the message of the agent's history?
> (I get this message when experiencing connectivity problems).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Well, I just refreshed the replication monitor and the blue arrow Icon did in
fact go away. At least it wasn't a big red x.
"Rich" wrote:
[vbcol=seagreen]
> Hi Paul,
> Thank you for getting back to me on this. In the Agent History, the Last
> action (11/28/06) says No Replicated Transactions are available. Is there a
> site anywhere that explains what the various alert icons signify? This way I
> can use the standardized lingo to describe what I am seeing.
> It is still a little bit early over my way, and customers haven't been
> calling about any problems (yet). Do I need to do anything or will this
> resolve itself? We have, been having some connectivity problems in recent
> times. But usually, I get a big red x when that happens. I restart the
> Distribution Agent and it goes away.
>
> "Paul Ibison" wrote:

Blocking and return parameters

Is it possible the existence of a return parameter on an SP running
insert statements would cause blocking to happen (any removing it
might help eliminate it?)
The SPs in question have three INSERT INTO statements using an
@.@.IDENTITY to help populate the second and third tables based on the
PK of the newly inserted record in the first INSERT INTO Statement.
I have recently stared to receive timeout errors in a multi-user
enviroment and wonder if that's part of the cause. In this
environment, users are requerying a table that is also being written
to. The writes are infrequent but the reads of that table are very
frequent.
On the read side I have added Select from TableX WITH (READPAST)...
On the write side I have elimnated the return parameters. I'm hoping
these two approaches help.
Help is greatly appreciated.
lq"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404110746.4de8d6d7@.posting.google.c om...
> Is it possible the existence of a return parameter on an SP running
> insert statements would cause blocking to happen (any removing it
> might help eliminate it?)
> The SPs in question have three INSERT INTO statements using an
> @.@.IDENTITY to help populate the second and third tables based on the
> PK of the newly inserted record in the first INSERT INTO Statement.
> I have recently stared to receive timeout errors in a multi-user
> enviroment and wonder if that's part of the cause. In this
> environment, users are requerying a table that is also being written
> to. The writes are infrequent but the reads of that table are very
> frequent.
> On the read side I have added Select from TableX WITH (READPAST)...
> On the write side I have elimnated the return parameters. I'm hoping
> these two approaches help.
> Help is greatly appreciated.
> lq

I'm not entirely sure I follow your description - stored procedures can have
return values, and output parameters, but not "return parameters" so I'm not
clear as to which one you mean here.

As a general comment, if your three INSERTs are inside a transaction, and
that transaction runs for too long, or requires a lot of locks, then it's
possible that the readers will be affected. But without some more
information, such as the piece of code containing the INSERTs, and some
background information about the tables, it's hard to say.

By the way, READPAST is potentially dangerous, because it means the client
will not see data which it should normally see, so the results returned may
be incomplete. Locking hints are not often required, so you should try other
solutions first.

Simon|||"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404110746.4de8d6d7@.posting.google.c om...
> Is it possible the existence of a return parameter on an SP running
> insert statements would cause blocking to happen (any removing it
> might help eliminate it?)
> The SPs in question have three INSERT INTO statements using an
> @.@.IDENTITY to help populate the second and third tables based on the
> PK of the newly inserted record in the first INSERT INTO Statement.
> I have recently stared to receive timeout errors in a multi-user
> enviroment and wonder if that's part of the cause. In this
> environment, users are requerying a table that is also being written
> to. The writes are infrequent but the reads of that table are very
> frequent.
> On the read side I have added Select from TableX WITH (READPAST)...
> On the write side I have elimnated the return parameters. I'm hoping
> these two approaches help.
> Help is greatly appreciated.
> lq

I'm not entirely sure I follow your description - stored procedures can have
return values, and output parameters, but not "return parameters" so I'm not
clear as to which one you mean here.

As a general comment, if your three INSERTs are inside a transaction, and
that transaction runs for too long, or requires a lot of locks, then it's
possible that the readers will be affected. But without some more
information, such as the piece of code containing the INSERTs, and some
background information about the tables, it's hard to say.

By the way, READPAST is potentially dangerous, because it means the client
will not see data which it should normally see, so the results returned may
be incomplete. Locking hints are not often required, so you should try other
solutions first.

Simon|||Simon,
Thank you for your response. I meant to write that in VBA I have
appended an Output Parameters to the prm collection.

Here is the entire SP:

*** START SP CODE:

Alter Procedure "mySPName"
@.par1 nvarchar(15) = null,
@.par2 int = null,
@.par3 nvarchar(50) = null,
@.par4 nvarchar(50) = null,
@.par5 nvarchar(125) = null,
@.par6 smallint = null,
@.par7 ntext = null,
@.par8 smalldatetime = null,
@.par9 smalldatetime = null,
@.par10 smalldatetime = null,
@.par11 smalldatetime = null,
@.par12 datetime = null,
@.par13 datetime = null,
@.par14 smallint = null,
@.par15 smalldatetime = null,
@.par16 tinyint = null,
@.par17 tinyint = null,
@.par18 tinyint = null,
@.par19 nvarchar(100) = null,
@.par20 int = null,
@.par21 int = null,
@.par22 int = null,
@.PKID int OUTPUT

AS
set nocount on
set xact_abort on

/* Insert new event into tblAppointments */

INSERT INTO
tblAppointments (fld1, fld2, fld3, fld4, fld5, fld6, fld7, fld8,
fld9, fld10, fld11, fld12, fld13, fld14, fld15, fld16, fld17, fld18,
fld19, fld20, fld21,fld22,fld23, fld24)
VALUES
(@.par1, @.par2, @.par3, @.par4, @.par5, @.par6, @.par7,
@.par8, @.par9, @.par10, @.par12,@.par13, @.par14, @.par15, @.par16, @.par17,
@.par18,
@.par19, @.par20, @.par21,@.par22, @.par22,@.par18, @.par18)

/* Note: Some columns above receive data from the same parameters */

SELECT @.PKID = @.@.IDENTITY

/* Create an entry in tblAppointmentHistory */

Insert Into
tblAppointmentsHistory (fldA, fldB, fldC, fldD, fldE)
Values
(@.PKID, @.par2, @.par3,'New Entry','Appt Type = ' + @.par4 + ' ;
AppTitle= ' + @.par5 + ' ; AppDescription= ' + isnull(Cast(@.par7 AS
nvarchar(4000)),'<none>') + ' ; Location = ' + @.par19 + ' ; Start
Date = ' + CAST(@.par12 AS nvarchar(25)) + ' ; End Date = ' +
IsNull(CAST(@.par13 AS nvarchar(25)),'<none'))

***END SP CODE

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<407af642$1_2@.news.bluewin.ch>...
> "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> news:47e5bd72.0404110746.4de8d6d7@.posting.google.c om...
> > Is it possible the existence of a return parameter on an SP running
> > insert statements would cause blocking to happen (any removing it
> > might help eliminate it?)
> > The SPs in question have three INSERT INTO statements using an
> > @.@.IDENTITY to help populate the second and third tables based on the
> > PK of the newly inserted record in the first INSERT INTO Statement.
> > I have recently stared to receive timeout errors in a multi-user
> > enviroment and wonder if that's part of the cause. In this
> > environment, users are requerying a table that is also being written
> > to. The writes are infrequent but the reads of that table are very
> > frequent.
> > On the read side I have added Select from TableX WITH (READPAST)...
> > On the write side I have elimnated the return parameters. I'm hoping
> > these two approaches help.
> > Help is greatly appreciated.
> > lq
> I'm not entirely sure I follow your description - stored procedures can have
> return values, and output parameters, but not "return parameters" so I'm not
> clear as to which one you mean here.
> As a general comment, if your three INSERTs are inside a transaction, and
> that transaction runs for too long, or requires a lot of locks, then it's
> possible that the readers will be affected. But without some more
> information, such as the piece of code containing the INSERTs, and some
> background information about the tables, it's hard to say.
> By the way, READPAST is potentially dangerous, because it means the client
> will not see data which it should normally see, so the results returned may
> be incomplete. Locking hints are not often required, so you should try other
> solutions first.
> Simon|||Simon,
Thank you for your response. I meant to write that in VBA I have
appended an Output Parameters to the prm collection.

Here is the entire SP:

*** START SP CODE:

Alter Procedure "mySPName"
@.par1 nvarchar(15) = null,
@.par2 int = null,
@.par3 nvarchar(50) = null,
@.par4 nvarchar(50) = null,
@.par5 nvarchar(125) = null,
@.par6 smallint = null,
@.par7 ntext = null,
@.par8 smalldatetime = null,
@.par9 smalldatetime = null,
@.par10 smalldatetime = null,
@.par11 smalldatetime = null,
@.par12 datetime = null,
@.par13 datetime = null,
@.par14 smallint = null,
@.par15 smalldatetime = null,
@.par16 tinyint = null,
@.par17 tinyint = null,
@.par18 tinyint = null,
@.par19 nvarchar(100) = null,
@.par20 int = null,
@.par21 int = null,
@.par22 int = null,
@.PKID int OUTPUT

AS
set nocount on
set xact_abort on

/* Insert new event into tblAppointments */

INSERT INTO
tblAppointments (fld1, fld2, fld3, fld4, fld5, fld6, fld7, fld8,
fld9, fld10, fld11, fld12, fld13, fld14, fld15, fld16, fld17, fld18,
fld19, fld20, fld21,fld22,fld23, fld24)
VALUES
(@.par1, @.par2, @.par3, @.par4, @.par5, @.par6, @.par7,
@.par8, @.par9, @.par10, @.par12,@.par13, @.par14, @.par15, @.par16, @.par17,
@.par18,
@.par19, @.par20, @.par21,@.par22, @.par22,@.par18, @.par18)

/* Note: Some columns above receive data from the same parameters */

SELECT @.PKID = @.@.IDENTITY

/* Create an entry in tblAppointmentHistory */

Insert Into
tblAppointmentsHistory (fldA, fldB, fldC, fldD, fldE)
Values
(@.PKID, @.par2, @.par3,'New Entry','Appt Type = ' + @.par4 + ' ;
AppTitle= ' + @.par5 + ' ; AppDescription= ' + isnull(Cast(@.par7 AS
nvarchar(4000)),'<none>') + ' ; Location = ' + @.par19 + ' ; Start
Date = ' + CAST(@.par12 AS nvarchar(25)) + ' ; End Date = ' +
IsNull(CAST(@.par13 AS nvarchar(25)),'<none'))

***END SP CODE

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<407af642$1_2@.news.bluewin.ch>...
> "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> news:47e5bd72.0404110746.4de8d6d7@.posting.google.c om...
> > Is it possible the existence of a return parameter on an SP running
> > insert statements would cause blocking to happen (any removing it
> > might help eliminate it?)
> > The SPs in question have three INSERT INTO statements using an
> > @.@.IDENTITY to help populate the second and third tables based on the
> > PK of the newly inserted record in the first INSERT INTO Statement.
> > I have recently stared to receive timeout errors in a multi-user
> > enviroment and wonder if that's part of the cause. In this
> > environment, users are requerying a table that is also being written
> > to. The writes are infrequent but the reads of that table are very
> > frequent.
> > On the read side I have added Select from TableX WITH (READPAST)...
> > On the write side I have elimnated the return parameters. I'm hoping
> > these two approaches help.
> > Help is greatly appreciated.
> > lq
> I'm not entirely sure I follow your description - stored procedures can have
> return values, and output parameters, but not "return parameters" so I'm not
> clear as to which one you mean here.
> As a general comment, if your three INSERTs are inside a transaction, and
> that transaction runs for too long, or requires a lot of locks, then it's
> possible that the readers will be affected. But without some more
> information, such as the piece of code containing the INSERTs, and some
> background information about the tables, it's hard to say.
> By the way, READPAST is potentially dangerous, because it means the client
> will not see data which it should normally see, so the results returned may
> be incomplete. Locking hints are not often required, so you should try other
> solutions first.
> Simon|||"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404121731.52fe5214@.posting.google.c om...
> Simon,
> Thank you for your response. I meant to write that in VBA I have
> appended an Output Parameters to the prm collection.
> Here is the entire SP:

<snip
At first glance, there doesn't seem to be anything obviously incorrect with
your procedure, assuming that you haven't removed anything significant from
it. Small issues are that you should probably use SCOPE_IDENTITY() instead
of @.@.IDENTITY (see Books Online), and you may want to review your use of SET
XACT_ABORT:

http://www.sommarskog.se/error-hand...html#XACT_ABORT

To go back to your original timeout issue, why do you think the timeouts are
connected to this specific procedure? Have you monitored what's happening on
the server when timeouts occur, especially locking? You can use Profiler to
capture what's happening on the server, and this KB article may be useful:

http://support.microsoft.com/defaul...9&Product=sql2k

Erland also has a tool for examining locks:

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

Simon|||"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404121731.52fe5214@.posting.google.c om...
> Simon,
> Thank you for your response. I meant to write that in VBA I have
> appended an Output Parameters to the prm collection.
> Here is the entire SP:

<snip
At first glance, there doesn't seem to be anything obviously incorrect with
your procedure, assuming that you haven't removed anything significant from
it. Small issues are that you should probably use SCOPE_IDENTITY() instead
of @.@.IDENTITY (see Books Online), and you may want to review your use of SET
XACT_ABORT:

http://www.sommarskog.se/error-hand...html#XACT_ABORT

To go back to your original timeout issue, why do you think the timeouts are
connected to this specific procedure? Have you monitored what's happening on
the server when timeouts occur, especially locking? You can use Profiler to
capture what's happening on the server, and this KB article may be useful:

http://support.microsoft.com/defaul...9&Product=sql2k

Erland also has a tool for examining locks:

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

Simon

blob info

I put an image as a blob into database.
When I return it and put it into picturebox can I somehow get it's
information: PictureSize, PictureType(jpg,gif...) ?
HrckoSQL Server can return the image length (DATALENGTH function) but has no
other knowledge of the binary contents. If you need that info, create a
separate column for that data.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
news:e1nv1n$d2d$1@.ss405.t-com.hr...
>I put an image as a blob into database.
> When I return it and put it into picturebox can I somehow get it's
> information: PictureSize, PictureType(jpg,gif...) ?
> Hrcko
>

blob info

I put an image as a blob into database.
When I return it and put it into picturebox can I somehow get it's
information: PictureSize, PictureType(jpg,gif...) ?
HrckoSQL Server can return the image length (DATALENGTH function) but has no
other knowledge of the binary contents. If you need that info, create a
separate column for that data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
news:e1nv1n$d2d$1@.ss405.t-com.hr...
>I put an image as a blob into database.
> When I return it and put it into picturebox can I somehow get it's
> information: PictureSize, PictureType(jpg,gif...) ?
> Hrcko
>

blank spaces....

select * from Customer where customerId ='123 '
select * from Customer where customerId ='123'
Both the above queries return the same result.
What is the property which ignores the white space character?
SQL 2K.
Thank you,
Larry
Larry Smith wrote:
> select * from Customer where customerId ='123 '
> select * from Customer where customerId ='123'
> Both the above queries return the same result.
> What is the property which ignores the white space character?
> SQL 2K.
> Thank you,
> Larry
Assuming you really have blank spaces in your customerId column, you can
use a LIKE clause to force the search for a trailing blank.
create table #whitespacetest (col1 varchar(10))
insert into #whitespacetest values ('ABC')
insert into #whitespacetest values ('ABC ')
select col1, col1 + '1' from #whitespacetest
select col1 from #whitespacetest where col1 = 'ABC'
select col1 from #whitespacetest where col1 = 'ABC '
select col1 from #whitespacetest where col1 LIKE 'ABC'
select col1 from #whitespacetest where col1 LIKE 'ABC[ ]'
drop table #whitespacetest
David Gugick
Quest Software
www.imceda.com
www.quest.com

blank spaces....

select * from Customer where customerId ='123 '
select * from Customer where customerId ='123'
Both the above queries return the same result.
What is the property which ignores the white space character?
SQL 2K.
Thank you,
LarryLarry Smith wrote:
> select * from Customer where customerId ='123 '
> select * from Customer where customerId ='123'
> Both the above queries return the same result.
> What is the property which ignores the white space character?
> SQL 2K.
> Thank you,
> Larry
Assuming you really have blank spaces in your customerId column, you can
use a LIKE clause to force the search for a trailing blank.
create table #whitespacetest (col1 varchar(10))
insert into #whitespacetest values ('ABC')
insert into #whitespacetest values ('ABC ')
select col1, col1 + '1' from #whitespacetest
select col1 from #whitespacetest where col1 = 'ABC'
select col1 from #whitespacetest where col1 = 'ABC '
select col1 from #whitespacetest where col1 LIKE 'ABC'
select col1 from #whitespacetest where col1 LIKE 'ABC[ ]'
drop table #whitespacetest
David Gugick
Quest Software
www.imceda.com
www.quest.com

blank spaces....

select * from Customer where customerId ='123 '
select * from Customer where customerId ='123'
Both the above queries return the same result.
What is the property which ignores the white space character?
SQL 2K.
Thank you,
LarryLarry Smith wrote:
> select * from Customer where customerId ='123 '
> select * from Customer where customerId ='123'
> Both the above queries return the same result.
> What is the property which ignores the white space character?
> SQL 2K.
> Thank you,
> Larry
Assuming you really have blank spaces in your customerId column, you can
use a LIKE clause to force the search for a trailing blank.
create table #whitespacetest (col1 varchar(10))
insert into #whitespacetest values ('ABC')
insert into #whitespacetest values ('ABC ')
select col1, col1 + '1' from #whitespacetest
select col1 from #whitespacetest where col1 = 'ABC'
select col1 from #whitespacetest where col1 = 'ABC '
select col1 from #whitespacetest where col1 LIKE 'ABC'
select col1 from #whitespacetest where col1 LIKE 'ABC[ ]'
drop table #whitespacetest
David Gugick
Quest Software
www.imceda.com
www.quest.com

Blank Parameters

I am trying to use a stored procedure that can accept blank parameters to
return a complete set of data. when I use the preview screen in reporting
services, the sproc does not return anything, yet when i run the sproc from
anywhere else, it works. Is there a setting I am missing to be able pass
balnk values to a sproc from the report?
Yes, if i pass values to the report, not blanks, the sproc does run and
returns data.
--
John Cleveland
Network Manager
Urban systems LtdTry using SQL Profiler to find out what is actually sent to the stored
procedure. Blank might not be blank. Do you expect a NULL or a '' as a blank
in the sp? Anyway, SQL Profiler shows what is actually sent, so you can
modify either Parameter or Stored Procedure accordingly.
Kaisa M. Lindahl Lervik
"John Cleveland" <JohnCleveland@.discussions.microsoft.com> wrote in message
news:EA34E0D4-7E12-4730-96F5-B783261CADF4@.microsoft.com...
>I am trying to use a stored procedure that can accept blank parameters to
> return a complete set of data. when I use the preview screen in reporting
> services, the sproc does not return anything, yet when i run the sproc
> from
> anywhere else, it works. Is there a setting I am missing to be able pass
> balnk values to a sproc from the report?
> Yes, if i pass values to the report, not blanks, the sproc does run and
> returns data.
> --
> John Cleveland
> Network Manager
> Urban systems Ltd
>|||When you click on preview, is it giving a small entry screen ? if yes dont
enter anything like double quotes etc.. before that is it giving a screen?
Amarnath
"John Cleveland" wrote:
> I am trying to use a stored procedure that can accept blank parameters to
> return a complete set of data. when I use the preview screen in reporting
> services, the sproc does not return anything, yet when i run the sproc from
> anywhere else, it works. Is there a setting I am missing to be able pass
> balnk values to a sproc from the report?
> Yes, if i pass values to the report, not blanks, the sproc does run and
> returns data.
> --
> John Cleveland
> Network Manager
> Urban systems Ltd
>|||I ran a trace in profiler and here is what I got:
exec spCostReport @.Project=N'', @.Phase=N''
These are the 2 params i am trying to send. I have default values set in
the sproc that should kick in if the params are blank. I exported the trace
to make sure there was nothing between the '', and there wasn't. Any thing
else to check for?
When I run this from the dataset screen in reporting services, it runs fine
leaving the params blank. it only seems to be when the report is run.
--
John Cleveland
Network Manager
Urban systems Ltd
"Kaisa M. Lindahl Lervik" wrote:
> Try using SQL Profiler to find out what is actually sent to the stored
> procedure. Blank might not be blank. Do you expect a NULL or a '' as a blank
> in the sp? Anyway, SQL Profiler shows what is actually sent, so you can
> modify either Parameter or Stored Procedure accordingly.
> Kaisa M. Lindahl Lervik
> "John Cleveland" <JohnCleveland@.discussions.microsoft.com> wrote in message
> news:EA34E0D4-7E12-4730-96F5-B783261CADF4@.microsoft.com...
> >I am trying to use a stored procedure that can accept blank parameters to
> > return a complete set of data. when I use the preview screen in reporting
> > services, the sproc does not return anything, yet when i run the sproc
> > from
> > anywhere else, it works. Is there a setting I am missing to be able pass
> > balnk values to a sproc from the report?
> >
> > Yes, if i pass values to the report, not blanks, the sproc does run and
> > returns data.
> >
> > --
> > John Cleveland
> > Network Manager
> > Urban systems Ltd
> >
>
>

Bitmask Multi-value report parameters

Hello all,
Is there any way to return a multi-value parameter as a sum of the
values instead of a comma seperated string?
For example, I have a table where one of the values is a bitmasked
number, representing a combination of 4 possible options.
I have the Report Parameter setup with Non-queried values. Ex. Opt1 = 1, Opt2 = 2, Opt3 = 4, Opt4 = 8.
And in my query, the WHERE clause is setup like: WHERE @.param &
ColumnX != 0
But of course, if I select multiple values for Opt1 and Opt3, it
returns 1,4 instead of 5.
Any suggestions?
Thanks - JasonYour parameter can be mapped to an expression. Query parameter and Report
Parameters are two different things. RS automatically creates the report
parameter for you so it seems like they are the same thing.
To do this is a two step process:
1. In layout tab, Report Menu ->Report Parameters. Change the name of the
parameter so it does not equal the name in your query.
2. Dataset tab, click on the ..., parameters tab. In the right column click
on the mapping and change to an expression which brings up the expression
builder. Have the expression builder change this to the way you want. You
might need to create some code behind report to do this.
I suggest whipping together a little VB test app to work out the code to
convert a comma separated string into a bitmask.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jason" <flajason@.gmail.com> wrote in message
news:1176830583.305883.64460@.n59g2000hsh.googlegroups.com...
> Hello all,
> Is there any way to return a multi-value parameter as a sum of the
> values instead of a comma seperated string?
> For example, I have a table where one of the values is a bitmasked
> number, representing a combination of 4 possible options.
> I have the Report Parameter setup with Non-queried values. Ex. Opt1 => 1, Opt2 = 2, Opt3 = 4, Opt4 = 8.
> And in my query, the WHERE clause is setup like: WHERE @.param &
> ColumnX != 0
> But of course, if I select multiple values for Opt1 and Opt3, it
> returns 1,4 instead of 5.
> Any suggestions?
> Thanks - Jason
>