Multi-value parameter dropdown horizontal scrollbar problem

Hi,

I wonder if anyone ecountered (and successfully solved) the following problem:

I have a query-based multi-valued parameter (let's call it "A"). When query that populates the parameter returns only one value and that value is a long text, then it's almost impossible to select this value through the Report Manager UI. It's because the horizontal scrollbar covers the value.

For now I have found two workarounds to this problem:

1. Cut the lenght of the Parameter Label value (Instead of 'Large Power Transformer", I show "Large Power Tran...")

2. Make the label in the multi-value dropdown smaller by using custom style sheets for report manager. I added a "LABEL { font-size: 7pt; }" section to the Htmlviewer.css and modified the RSReportServer.config file to point to the Htmlviewer.css (for info on how to do this please see: http://msdn2.microsoft.com/en-us/library/ms345247.aspx)

So far I am using the No 2. workaround. Any other suggestions on final solution to that matter would be highly appreciated.

Hi,

This is a bug, which was fixed in SQL Server 2005 in a post-SP1 QFE, and in Visual Studio controls in VS 2005 SP1. The fix will also ship in the upcoming SQL Server 2005 SP2.

multi-thread tasks using a single connection object concurrently

is it possible to use a single connection object shared by several
tasks where each task is handled by a thread? these tasks call stored
procedures that return record sets, no editing, update or delete.

my objective is that db connection is expensive and each user can only
have 1 connection object. each user submits a request to the web
server, and the result web page construction consists of a number of
result sets obtained from several stored procedure from the same
database instance.

i could obtained my result sets by making each sp call one at a time
but was thinking whether i could instead break up the tasks, create a
thread for each task that shares the same db connection object
concurrently. the connection object will be alive until all tasks are
completed, that is if the final object ive could be achieved in a
shorter time. No connection pooling here.

i am wondering over questions like:

1. within the same db instance, if 2 stored procedures need to access
the same table, does SQLserver queue-up and service one request at a
time.

2. would there be any problem using the connection object this way,
sharing between multiple theads?

3. is it possible that a 'dead-lock' may occur within the DB?

of cos this whole idea can be absurd and into the trash if the
connection object doesn't support multi-thread and is queue based in
the first place.

pardon me if my SQL server basics is off track. been doing too many
other things.

thanks,
mengmeng (hui_km@.star-quest.com) writes:
> i could obtained my result sets by making each sp call one at a time
> but was thinking whether i could instead break up the tasks, create a
> thread for each task that shares the same db connection object
> concurrently. the connection object will be alive until all tasks are
> completed, that is if the final object ive could be achieved in a
> shorter time. No connection pooling here.

I honestly don't know if you can share connection objects between threads,
but in any case it seems like a pointless thing, because the connection
object would be a common resource. You cannot submit to queries on the
same connection object at the same time. ...ah, wait, actually with ADO
you can, but what happens is that ADO opens a new connection behind your
back for you.

> 1. within the same db instance, if 2 stored procedures need to access
> the same table, does SQLserver queue-up and service one request at a
> time.

Depends on how they procedures access the tables. For read operations,
the procedures may well execute in parallel. But if one process starts
to update, the other process will be blocked. (Unless it uses an
isoalation level of uncommitted.)

> 2. would there be any problem using the connection object this way,
> sharing between multiple theads?

As I said, it would be a pointless thing to do.

> 3. is it possible that a 'dead-lock' may occur within the DB?

Yes. That is not to say that you will experience dead-locks, only that
it could happen to you.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||hi erland,

i was thinking the connection object is like a handle to a database
connection, but if has its own state, and if its not a thread-safe
object, then it would not work at all. even if i can wrap the connection
object in thread-safe codes, if it cannot handle thread context switch,
the idea is useless. the part about ado opening a connection on its own
accord is interesting though. i didn't know that at all.

the piece of info is helping decide how i should implement and write my
app server components. thanks for your response.

regards,
meng

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Multiple statement handles on one connection

I want to use multiple concurrently active statement
handles on a single connection handle. An ODBC call to
SQLGetInfo with the SQL_ACTIVE_STATEMENS key returns 1,
indicating that there is a limit of 1 active statement on
a single connection. If I try to SQLExecDirect on another
statement handle on the same connection handle I get an
error complaining that there is another active statement
on the same connection.
Is there any way to configure either SQLServer or its ODBC
driver to allow multiple concurrently active statement
handles on the same connection? This is pretty common
database programming practice to Exec one query, then use
a loop with SQLFetch and then execute multiple SQL
statements on another statement handle within the loop.
Even lowly MSAccess allows multiple concurrently active
statement handles on the same connection.
My ODBC driver version is 2000.85.1022.00.
My SQLServer version SQL Server Developer Edition 8.00.194
(RTM)This is not possible with current versions of SQL Server or the SQL Server
ODBC Driver, when using the default "firehose" cursor. You can use
server-side cursors, which allow you to fetch a single row at a time from
the server (and therefore free up the connection between each row), or you
can cache the data from the first statement yourself (therefore freeing up
the connection). And of course, you can open up a second connection for the
second statement.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Lee Scheffler" <anonymous@.discussions.microsoft.com> wrote in message
news:b47e01c4077d$ef011af0$a101280a@.phx.gbl...
> I want to use multiple concurrently active statement
> handles on a single connection handle. An ODBC call to
> SQLGetInfo with the SQL_ACTIVE_STATEMENS key returns 1,
> indicating that there is a limit of 1 active statement on
> a single connection. If I try to SQLExecDirect on another
> statement handle on the same connection handle I get an
> error complaining that there is another active statement
> on the same connection.
> Is there any way to configure either SQLServer or its ODBC
> driver to allow multiple concurrently active statement
> handles on the same connection? This is pretty common
> database programming practice to Exec one query, then use
> a loop with SQLFetch and then execute multiple SQL
> statements on another statement handle within the loop.
> Even lowly MSAccess allows multiple concurrently active
> statement handles on the same connection.
> My ODBC driver version is 2000.85.1022.00.
> My SQLServer version SQL Server Developer Edition 8.00.194
> (RTM)

Multiple SP values overriding the desired SP value

I have a stored procedure (lets call him Earl to keep things clear) that executes another sp (lets call him Daryl). Daryl returns a value, and although the last thing the first Earl does is selects a record set to return to the code, the only thing the code can access is values from Daryl. I am using NOCOUNT ON in the beginning of Earl, and NOCOUNT OFF at the end of Earl, but I still get Daryl's values.

I even put NOCOUNT ON / OFF in Daryl, but no such luck. How do I bypass Daryl's values and get to Earl's?

Thanks.

Could you post your code?|||

NOCOUNT only effects the rowcount (as in the message (n row(s) affected), and the global @.@.rowcount) not the actual returning of values.

So, are you saying the rowcount is being updated even with SET NOCOUNT ON ?

|||

no, nothing to do with the row count I guess.

What it is: because the secondary SP, which is called by the first, uses a select statement, that data generated from the select is being returned from the primary SP, and not the intended data.

Here is a boiled down version of the SP:

alter procedure earl
as
begin

insert into table (x, y, z) values ('a', 1, 'c')
@.id = @.@.identity

exec daryl(@.id )

select * from table where id = @.id

end

alter daryl
@.id int
as
begin
insert into joiningtable (x, y, z) values (@.id, 1, 'c')

@.error = @.@.error

if(@.error > 0)
select @.error as result
else
select @.@.identity as result

end

so the output from Earl is @.@.identity from Daryl, and not "select * from table where id = @.id " from Earl.

Daryl needs to return data, because this SP can be called directly from another function if @.id is known.

|||Earl will return the intended records in the second result set. So you can try to reach the Next resultset.|||

Yes, this will return two result sets.

What are you using on the client side to recieve these results? If try this from sqlcmd\osql\SSMS, you will see both result sets.

Multiple ResultSets

Hi
I have a stored procedure which returns mulitple resultsets
rs = call.executeQuery();
call.getMoreResults();
irs = call.getResultSet();
I want to pass these two resultsets to another method.
When i pass these two
i get error saying "ResultSet is closed" for the first resultset.
i came to know there is method in jdk1.4.2 which keeps the resultsets open.
"call.getMoreResults(Statement.Keep_Current_Result )"
But i tried to use that But again i have another problem
Im using WSAD5.1 which uses to jdk1.4.1
So i configured the setting in WSAD so that it uses jdk1.4.2.
So at compile time im not gettign any problem
But at runtime it says
"getMoreResults(int) is not supported by webshpere java.sql.Statement
Implementation"
Can you please help me regarding this..
Thanks in Advance
jaya nair wrote:

> Hi
> I have a stored procedure which returns mulitple resultsets
> rs = call.executeQuery();
> call.getMoreResults();
> irs = call.getResultSet();
> I want to pass these two resultsets to another method.
> When i pass these two
> i get error saying "ResultSet is closed" for the first resultset.
> i came to know there is method in jdk1.4.2 which keeps the resultsets open.
> "call.getMoreResults(Statement.Keep_Current_Result )"
> But i tried to use that But again i have another problem
> Im using WSAD5.1 which uses to jdk1.4.1
> So i configured the setting in WSAD so that it uses jdk1.4.2.
> So at compile time im not gettign any problem
> But at runtime it says
> "getMoreResults(int) is not supported by webshpere java.sql.Statement
> Implementation"
> Can you please help me regarding this..
> Thanks in Advance
Hi. By JDBC spec, getMoreResults() will close any previous
result set. You have to do whatever processing you want
with a given resultset before getting another from the same
statement.
Joe Weinstein at BEA Systems
|||Hi Joe,
I have a problem again while retriveing multiple results sets from SQLServer
stored procedure
I used call.execute(), then tried to retrive OUT parameters using
call.getString(1) , it works fine
later im trying to retrive multiple resultsets one after other.
As you said first i tried to retrieve first resultset using
rs1 = call.getResultSet();
and tried to retrieve second one
call.getMoreResults()
rs2 = call.getResultSet()
But im getting rs1 and rs1 are null. Im not getting resultsets
But when i execute this stored proc using Query Analyser. It returns both
resultsets and i can see the records.
But using jdbc im not able to retrieve.
Can you tell me why?
thanks in advance
sudha
"Joe Weinstein" wrote:

>
> jaya nair wrote:
>
> Hi. By JDBC spec, getMoreResults() will close any previous
> result set. You have to do whatever processing you want
> with a given resultset before getting another from the same
> statement.
> Joe Weinstein at BEA Systems
>

multiple resultsets

I am just having many problems trying to call a procedure with multiple
parameters in multiple datasets. The proc works fine in query analyzer with
each parameter specified. I've got all the fields in each of the datasets,
and I've drug them all into the report layout. right now, my rpt is failing
with this:
The value expression for the textbox 'fieldname' refers to the fields
'fieldname'.
Report item expressions can only refer to fields within the current data set
scope or,
if inside and aggregate, the specified data set scope.
Can anybody tell me how to get around this? each of the datasets, when run
individually, work fine. (using the ! exclamation to run) but i receive
this error for one of the datasets when trying to preview the entire report.
-- Lynn
LynnI fixed that error. Somehow the fields of dataset1 got into the fields of
dataset5. but, now i get this:
An error has occurred during report processing.
Annot read the next data row for the data set DataSet3.
There is insufficient result space to convert a money value to varchar.
Again, each procedure call works fine in QA.
-- Lynn
"Lynn" wrote:
> I am just having many problems trying to call a procedure with multiple
> parameters in multiple datasets. The proc works fine in query analyzer with
> each parameter specified. I've got all the fields in each of the datasets,
> and I've drug them all into the report layout. right now, my rpt is failing
> with this:
> The value expression for the textbox 'fieldname' refers to the fields
> 'fieldname'.
> Report item expressions can only refer to fields within the current data set
> scope or,
> if inside and aggregate, the specified data set scope.
> Can anybody tell me how to get around this? each of the datasets, when run
> individually, work fine. (using the ! exclamation to run) but i receive
> this error for one of the datasets when trying to preview the entire report.
> -- Lynn
> Lynn|||I fixed that error, too. Now the report returns. But no values are returned
for Dataset1, dataset3 and dataset5. And, dataset2 and dataset4 only contain
duplicates.
for example, dataset2 is supposed to be like this:
Symbol #Trades Volume Total $
..
...
...
but i get only this:
CRNX 213
It returns only Symbol and #trades, and it displays only one, replicated
over and over. Same for dataset4. It should be:
EndPoint #Trades Volume Total $
...
....
.....
but i get only 5 of these:
6BM6 2
Any direction?
-- Lynn
"Lynn" wrote:
> I fixed that error. Somehow the fields of dataset1 got into the fields of
> dataset5. but, now i get this:
> An error has occurred during report processing.
> Annot read the next data row for the data set DataSet3.
> There is insufficient result space to convert a money value to varchar.
> Again, each procedure call works fine in QA.
> -- Lynn
>
> "Lynn" wrote:
> > I am just having many problems trying to call a procedure with multiple
> > parameters in multiple datasets. The proc works fine in query analyzer with
> > each parameter specified. I've got all the fields in each of the datasets,
> > and I've drug them all into the report layout. right now, my rpt is failing
> > with this:
> >
> > The value expression for the textbox 'fieldname' refers to the fields
> > 'fieldname'.
> > Report item expressions can only refer to fields within the current data set
> > scope or,
> > if inside and aggregate, the specified data set scope.
> >
> > Can anybody tell me how to get around this? each of the datasets, when run
> > individually, work fine. (using the ! exclamation to run) but i receive
> > this error for one of the datasets when trying to preview the entire report.
> >
> > -- Lynn
> > Lynn