multiple resultsets: record

Showing posts with label record. Show all posts

Friday, March 23, 2012

Multiply record fields with same mainID.

Hi everybody,
What's the most efficient way to get the following result:
I have a number of records with a MainID and a Value fields.
eg:
SubID MainID Value
1 1 1
2 1 1
3 1 2
4 1 3
5 2 1
6 2 1
7 3 2
8 3 2
I need the product of the Value field for each MainID.
The result has to be like this:
MainID PrValue
1 6 (1*1*2*3)
2 1 (1*1)
3 4 (2*2)
TIA,
Martin.select MainID, sum(value) as PrValue
from table
group by MainID
"martin" <kashaan007@.hotmail.com> wrote in message
news:Of%23nDPnRFHA.3144@.tk2msftngp13.phx.gbl...
> Hi everybody,
> What's the most efficient way to get the following result:
> I have a number of records with a MainID and a Value fields.
> eg:
> SubID MainID Value
> 1 1 1
> 2 1 1
> 3 1 2
> 4 1 3
> 5 2 1
> 6 2 1
> 7 3 2
> 8 3 2
> I need the product of the Value field for each MainID.
> The result has to be like this:
> MainID PrValue
> 1 6 (1*1*2*3)
> 2 1 (1*1)
> 3 4 (2*2)
> TIA,
> Martin.
>
>|||Try,
use northwind
go
create table t (
SubID int,
MainID int,
Value int
)
go
insert into t values(1, 1, 1)
insert into t values(2, 1, 1)
insert into t values(3, 1, 2)
insert into t values(4, 1, 3)
insert into t values(5, 2, 1)
insert into t values(6, 2, 1)
insert into t values(7, 3, 2)
insert into t values(8, 3, 2)
go
select
MainID,
POWER(10, SUM(LOG10(Value)))
from
t
group by
MainID
drop table t
go
I took the idea from:
The T-SQL Banker
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-SQLBanker.asp
AMB
"martin" wrote:
> Hi everybody,
> What's the most efficient way to get the following result:
> I have a number of records with a MainID and a Value fields.
> eg:
> SubID MainID Value
> 1 1 1
> 2 1 1
> 3 1 2
> 4 1 3
> 5 2 1
> 6 2 1
> 7 3 2
> 8 3 2
> I need the product of the Value field for each MainID.
> The result has to be like this:
> MainID PrValue
> 1 6 (1*1*2*3)
> 2 1 (1*1)
> 3 4 (2*2)
> TIA,
> Martin.
>
>|||Hi,AMB
It gives me a wrong output for the col1=2
CREATE TABLE #Test
(
col1 INT NOT NULL,
col2 INT NOT NULL
)
INSERT INTO #Test VALUES (1,10)
INSERT INTO #Test VALUES (1,2)
INSERT INTO #Test VALUES (1,3)
INSERT INTO #Test VALUES (2,4)
INSERT INTO #Test VALUES (2,2)
SELECT col1,POWER(10, SUM(LOG10(col2)))
FROM #Test GROUP BY col1
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> Try,
> use northwind
> go
> create table t (
> SubID int,
> MainID int,
> Value int
> )
> go
> insert into t values(1, 1, 1)
> insert into t values(2, 1, 1)
> insert into t values(3, 1, 2)
> insert into t values(4, 1, 3)
> insert into t values(5, 2, 1)
> insert into t values(6, 2, 1)
> insert into t values(7, 3, 2)
> insert into t values(8, 3, 2)
> go
> select
> MainID,
> POWER(10, SUM(LOG10(Value)))
> from
> t
> group by
> MainID
> drop table t
> go
> I took the idea from:
> The T-SQL Banker
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-SQLBanker.asp
>
> AMB
> "martin" wrote:
> > Hi everybody,
> >
> > What's the most efficient way to get the following result:
> >
> > I have a number of records with a MainID and a Value fields.
> >
> > eg:
> >
> > SubID MainID Value
> > 1 1 1
> > 2 1 1
> > 3 1 2
> > 4 1 3
> > 5 2 1
> > 6 2 1
> > 7 3 2
> > 8 3 2
> >
> > I need the product of the Value field for each MainID.
> >
> > The result has to be like this:
> >
> > MainID PrValue
> > 1 6 (1*1*2*3)
> > 2 1 (1*1)
> > 3 4 (2*2)
> >
> > TIA,
> >
> > Martin.
> >
> >
> >
> >|||Alejandro's solution is good for values greater than zero. Here's a
more general solution for all integers (this one adapted from Celko and
others):
SELECT mainid,
CAST(ROUND(
COALESCE(EXP(SUM(LOG(ABS(NULLIF(value,0))))),0)
* SIGN(MIN(ABS(value)))
* (COUNT(NULLIF(SIGN(value),1))%2*-2+1)
,0) AS INTEGER) AS product
FROM T
GROUP BY mainid
--
David Portas
SQL Server MVP
--|||Thanks a lot.
Works great.
Martin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> Try,
> use northwind
> go
> create table t (
> SubID int,
> MainID int,
> Value int
> )
> go
> insert into t values(1, 1, 1)
> insert into t values(2, 1, 1)
> insert into t values(3, 1, 2)
> insert into t values(4, 1, 3)
> insert into t values(5, 2, 1)
> insert into t values(6, 2, 1)
> insert into t values(7, 3, 2)
> insert into t values(8, 3, 2)
> go
> select
> MainID,
> POWER(10, SUM(LOG10(Value)))
> from
> t
> group by
> MainID
> drop table t
> go
> I took the idea from:
> The T-SQL Banker
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/
html/TheT-SQLBanker.asp
>
> AMB
> "martin" wrote:
> > Hi everybody,
> >
> > What's the most efficient way to get the following result:
> >
> > I have a number of records with a MainID and a Value fields.
> >
> > eg:
> >
> > SubID MainID Value
> > 1 1 1
> > 2 1 1
> > 3 1 2
> > 4 1 3
> > 5 2 1
> > 6 2 1
> > 7 3 2
> > 8 3 2
> >
> > I need the product of the Value field for each MainID.
> >
> > The result has to be like this:
> >
> > MainID PrValue
> > 1 6 (1*1*2*3)
> > 2 1 (1*1)
> > 3 4 (2*2)
> >
> > TIA,
> >
> > Martin.
> >
> >
> >
> >|||If we add +0.001 it works fine
SELECT col1,POWER(10, SUM(LOG10(col2))+0.001)
FROM #Test GROUP BY col1
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uYVS5cnRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> > Try,
> >
> > use northwind
> > go
> >
> > create table t (
> > SubID int,
> > MainID int,
> > Value int
> > )
> > go
> >
> > insert into t values(1, 1, 1)
> > insert into t values(2, 1, 1)
> > insert into t values(3, 1, 2)
> > insert into t values(4, 1, 3)
> > insert into t values(5, 2, 1)
> > insert into t values(6, 2, 1)
> > insert into t values(7, 3, 2)
> > insert into t values(8, 3, 2)
> > go
> >
> > select
> > MainID,
> > POWER(10, SUM(LOG10(Value)))
> > from
> > t
> > group by
> > MainID
> >
> > drop table t
> > go
> >
> > I took the idea from:
> >
> > The T-SQL Banker
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-SQLBanker.asp
> >
> >
> > AMB
> >
> > "martin" wrote:
> >
> > > Hi everybody,
> > >
> > > What's the most efficient way to get the following result:
> > >
> > > I have a number of records with a MainID and a Value fields.
> > >
> > > eg:
> > >
> > > SubID MainID Value
> > > 1 1 1
> > > 2 1 1
> > > 3 1 2
> > > 4 1 3
> > > 5 2 1
> > > 6 2 1
> > > 7 3 2
> > > 8 3 2
> > >
> > > I need the product of the Value field for each MainID.
> > >
> > > The result has to be like this:
> > >
> > > MainID PrValue
> > > 1 6 (1*1*2*3)
> > > 2 1 (1*1)
> > > 3 4 (2*2)
> > >
> > > TIA,
> > >
> > > Martin.
> > >
> > >
> > >
> > >
>|||Cast 10 to float.
SELECT
col1,
cast(POWER(cast(10 as float), SUM(LOG10(col2))) as decimal(5))
FROM #Test GROUP BY col1
go
AMB
"Uri Dimant" wrote:
> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> > Try,
> >
> > use northwind
> > go
> >
> > create table t (
> > SubID int,
> > MainID int,
> > Value int
> > )
> > go
> >
> > insert into t values(1, 1, 1)
> > insert into t values(2, 1, 1)
> > insert into t values(3, 1, 2)
> > insert into t values(4, 1, 3)
> > insert into t values(5, 2, 1)
> > insert into t values(6, 2, 1)
> > insert into t values(7, 3, 2)
> > insert into t values(8, 3, 2)
> > go
> >
> > select
> > MainID,
> > POWER(10, SUM(LOG10(Value)))
> > from
> > t
> > group by
> > MainID
> >
> > drop table t
> > go
> >
> > I took the idea from:
> >
> > The T-SQL Banker
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-SQLBanker.asp
> >
> >
> > AMB
> >
> > "martin" wrote:
> >
> > > Hi everybody,
> > >
> > > What's the most efficient way to get the following result:
> > >
> > > I have a number of records with a MainID and a Value fields.
> > >
> > > eg:
> > >
> > > SubID MainID Value
> > > 1 1 1
> > > 2 1 1
> > > 3 1 2
> > > 4 1 3
> > > 5 2 1
> > > 6 2 1
> > > 7 3 2
> > > 8 3 2
> > >
> > > I need the product of the Value field for each MainID.
> > >
> > > The result has to be like this:
> > >
> > > MainID PrValue
> > > 1 6 (1*1*2*3)
> > > 2 1 (1*1)
> > > 3 4 (2*2)
> > >
> > > TIA,
> > >
> > > Martin.
> > >
> > >
> > >
> > >
>
>|||Good catch!!!
AMB
"Uri Dimant" wrote:
> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> > Try,
> >
> > use northwind
> > go
> >
> > create table t (
> > SubID int,
> > MainID int,
> > Value int
> > )
> > go
> >
> > insert into t values(1, 1, 1)
> > insert into t values(2, 1, 1)
> > insert into t values(3, 1, 2)
> > insert into t values(4, 1, 3)
> > insert into t values(5, 2, 1)
> > insert into t values(6, 2, 1)
> > insert into t values(7, 3, 2)
> > insert into t values(8, 3, 2)
> > go
> >
> > select
> > MainID,
> > POWER(10, SUM(LOG10(Value)))
> > from
> > t
> > group by
> > MainID
> >
> > drop table t
> > go
> >
> > I took the idea from:
> >
> > The T-SQL Banker
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-SQLBanker.asp
> >
> >
> > AMB
> >
> > "martin" wrote:
> >
> > > Hi everybody,
> > >
> > > What's the most efficient way to get the following result:
> > >
> > > I have a number of records with a MainID and a Value fields.
> > >
> > > eg:
> > >
> > > SubID MainID Value
> > > 1 1 1
> > > 2 1 1
> > > 3 1 2
> > > 4 1 3
> > > 5 2 1
> > > 6 2 1
> > > 7 3 2
> > > 8 3 2
> > >
> > > I need the product of the Value field for each MainID.
> > >
> > > The result has to be like this:
> > >
> > > MainID PrValue
> > > 1 6 (1*1*2*3)
> > > 2 1 (1*1)
> > > 3 4 (2*2)
> > >
> > > TIA,
> > >
> > > Martin.
> > >
> > >
> > >
> > >
>
>|||David,
This is really a good one.
AMB
"David Portas" wrote:
> Alejandro's solution is good for values greater than zero. Here's a
> more general solution for all integers (this one adapted from Celko and
> others):
> SELECT mainid,
> CAST(ROUND(
> COALESCE(EXP(SUM(LOG(ABS(NULLIF(value,0))))),0)
> * SIGN(MIN(ABS(value)))
> * (COUNT(NULLIF(SIGN(value),1))%2*-2+1)
> ,0) AS INTEGER) AS product
> FROM T
> GROUP BY mainid
> --
> David Portas
> SQL Server MVP
> --
>|||Use better the one posted by David Portas, and if you decide to use this one,
read the correction in my answer to Uri.
AMB
"martin" wrote:
> Thanks a lot.
> Works great.
> Martin.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> > Try,
> >
> > use northwind
> > go
> >
> > create table t (
> > SubID int,
> > MainID int,
> > Value int
> > )
> > go
> >
> > insert into t values(1, 1, 1)
> > insert into t values(2, 1, 1)
> > insert into t values(3, 1, 2)
> > insert into t values(4, 1, 3)
> > insert into t values(5, 2, 1)
> > insert into t values(6, 2, 1)
> > insert into t values(7, 3, 2)
> > insert into t values(8, 3, 2)
> > go
> >
> > select
> > MainID,
> > POWER(10, SUM(LOG10(Value)))
> > from
> > t
> > group by
> > MainID
> >
> > drop table t
> > go
> >
> > I took the idea from:
> >
> > The T-SQL Banker
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/
> html/TheT-SQLBanker.asp
> >
> >
> > AMB
> >
> > "martin" wrote:
> >
> > > Hi everybody,
> > >
> > > What's the most efficient way to get the following result:
> > >
> > > I have a number of records with a MainID and a Value fields.
> > >
> > > eg:
> > >
> > > SubID MainID Value
> > > 1 1 1
> > > 2 1 1
> > > 3 1 2
> > > 4 1 3
> > > 5 2 1
> > > 6 2 1
> > > 7 3 2
> > > 8 3 2
> > >
> > > I need the product of the Value field for each MainID.
> > >
> > > The result has to be like this:
> > >
> > > MainID PrValue
> > > 1 6 (1*1*2*3)
> > > 2 1 (1*1)
> > > 3 4 (2*2)
> > >
> > > TIA,
> > >
> > > Martin.
> > >
> > >
> > >
> > >
>
>|||Ok thanks.
Martin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:210DC41A-BF9B-4211-9E16-995A554C0374@.microsoft.com...
> Use better the one posted by David Portas, and if you decide to use this
one,
> read the correction in my answer to Uri.
>
> AMB
> "martin" wrote:
> > Thanks a lot.
> >
> > Works great.
> >
> > Martin.
> >
> >
> > "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> > news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> > > Try,
> > >
> > > use northwind
> > > go
> > >
> > > create table t (
> > > SubID int,
> > > MainID int,
> > > Value int
> > > )
> > > go
> > >
> > > insert into t values(1, 1, 1)
> > > insert into t values(2, 1, 1)
> > > insert into t values(3, 1, 2)
> > > insert into t values(4, 1, 3)
> > > insert into t values(5, 2, 1)
> > > insert into t values(6, 2, 1)
> > > insert into t values(7, 3, 2)
> > > insert into t values(8, 3, 2)
> > > go
> > >
> > > select
> > > MainID,
> > > POWER(10, SUM(LOG10(Value)))
> > > from
> > > t
> > > group by
> > > MainID
> > >
> > > drop table t
> > > go
> > >
> > > I took the idea from:
> > >
> > > The T-SQL Banker
> > >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/
> > html/TheT-SQLBanker.asp
> > >
> > >
> > > AMB
> > >
> > > "martin" wrote:
> > >
> > > > Hi everybody,
> > > >
> > > > What's the most efficient way to get the following result:
> > > >
> > > > I have a number of records with a MainID and a Value fields.
> > > >
> > > > eg:
> > > >
> > > > SubID MainID Value
> > > > 1 1 1
> > > > 2 1 1
> > > > 3 1 2
> > > > 4 1 3
> > > > 5 2 1
> > > > 6 2 1
> > > > 7 3 2
> > > > 8 3 2
> > > >
> > > > I need the product of the Value field for each MainID.
> > > >
> > > > The result has to be like this:
> > > >
> > > > MainID PrValue
> > > > 1 6 (1*1*2*3)
> > > > 2 1 (1*1)
> > > > 3 4 (2*2)
> > > >
> > > > TIA,
> > > >
> > > > Martin.
> > > >
> > > >
> > > >
> > > >
> >
> >
> >

Multiply record fields with same mainID.

Wednesday, March 21, 2012

Multiple values in one column

I'm trying to write a query which allows that multiple values from one
column are placed in one record.

ex:
table
NrLetters
1A
2A
2B
2C
3A
3B
3C
3D
3E
4A

The result I want to get from an select:
NrAll Letters
1A
2A, B, C
3A, B, C, D, E
4A

OlivierOlivier (olivier.lammens@.belgacom.be) writes:
> I'm trying to write a query which allows that multiple values from one
> column are placed in one record.
> ex:
> table
> Nr Letters
> 1 A
> 2 A
> 2 B
> 2 C
> 3 A
> 3 B
> 3 C
> 3 D
> 3 E
> 4 A
> The result I want to get from an select:
> Nr All Letters
> 1 A
> 2 A, B, C
> 3 A, B, C, D, E
> 4 A

You cannot do this in a single query in SQL 2000. And this is not a
thing you are supposed to do in a relational database, because repeating
values in a column is not supposed to occur.

You can achieve it by an iterative solution and aggregat into a temp
table. (Some people may suggest shortcuts for this, but they are not
reliable.) But in essence, this is really something that should be performed
on the client side.

On a side note, this is actually possible to do in SQL2005, currently in
beta, by using some XML extensions. But it still not really a relational
thing to do, so the client is still the best place for the work.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||olivier.lammens@.belgacom.be (Olivier) wrote in message news:<66db75c6.0412020207.4eb3a569@.posting.google.com>...
> I'm trying to write a query which allows that multiple values from one
> column are placed in one record.
> ex:
> table
> NrLetters
> 1A
> 2A
> 2B
> 2C
> 3A
> 3B
> 3C
> 3D
> 3E
> 4A
> The result I want to get from an select:
> NrAll Letters
> 1A
> 2A, B, C
> 3A, B, C, D, E
> 4A
> Olivier

Hey, man!
Try to have a look at http://www.aspfaq.com/show.asp?id=2529
You have to write a UDF to concatenate first.
HTH|||"Olivier" <olivier.lammens@.belgacom.be> wrote in message news:66db75c6.0412020207.4eb3a569@.posting.google.c om...
> I'm trying to write a query which allows that multiple values from one
> column are placed in one record.
> ex:
> table
> Nr Letters
> 1 A
> 2 A
> 2 B
> 2 C
> 3 A
> 3 B
> 3 C
> 3 D
> 3 E
> 4 A
> The result I want to get from an select:
> Nr All Letters
> 1 A
> 2 A, B, C
> 3 A, B, C, D, E
> 4 A
> Olivier

Just get a copy of iAnywhere's Adaptive Server Anywhere (ASA), which includes the List() aggregate function (and has had
it for about 8 years).

-Paul-|||The result you want isn't much use in a database - it's really a
formatted report for display or printing. For this reason it's probably
best done in your presentation tier rather than in SQL. If you have no
other option, however, you may be able to do something like this:

SELECT T.nr,
MAX(CASE WHEN seq=1 THEN letter END)+
MAX(CASE WHEN seq=2 THEN ','+letter ELSE '' END)+
MAX(CASE WHEN seq=3 THEN ','+letter ELSE '' END)+
MAX(CASE WHEN seq=4 THEN ','+letter ELSE '' END)+
MAX(CASE WHEN seq=5 THEN ','+letter ELSE '' END)+
MAX(CASE WHEN seq=6 THEN ','+letter ELSE '' END)
/* ... etc ... */
FROM
(SELECT T1.nr, T1.letter, COUNT(*) AS seq
FROM YourTable AS T1
JOIN YourTable AS T2
ON T1.nr = T2.nr
AND T1.letter >= T2.letter
GROUP BY T1.nr, T1.letter) AS T
GROUP BY T.nr

I'm assuming here that (nr,letter) is unique and non-NULL.
--
David Portas
SQL Server MVP
--

Monday, March 12, 2012

Multiple table record

I have two table and there are releationsheep between them.

Topic:

topicId, topicName, topicDate

Reply:

replyId,topicId,replyName,replyDate

I want to insert two table at the same time.

Topic: 1,example,10/21/2007

Reply:1,1,example, 10/21/2007

You can only INSERT into one table at a time. So you would need to do 2 INSERTs.

Wednesday, March 7, 2012

multiple sessions when using ADO

Hi
I have an application that uses an ADOConnection named CONNECTION1 and an AD
ODataset.The dataset fetches a record for editing it.This dataset has no Con
nectionString and its connection is the CONNECTION1.After opening the datase
t and editing record (befor
posting it) I execute a Stord Procedure to update another record in another
table with CONNECTION1.EXECUTE.
After opening the dataset I see a row in Management -> Current Activity -> P
rocess Info in EnterPrise Manager.And After CONNECTION.EXECUTE statement I s
ee 2 rows in Enterprise Manager.
I do all data modification by one ADOConnection in Delphi5 but SQL SERVER 20
00 do these modification by separate SPIDs (sessions).
How can I prevent this?
Dose ADOConnection or ADODataset have any property to prevent that? Or shoul
d I do anything in SQL SERVER?
ThanksYou may not be able to prevent it. ADO and ODBC, for that matter, will
spawn connections as they see fit. In most cases it does this because the
first SPID is in the middle of some process that has not completed, so the
second SPID is necesary to perform any other task. Normally this does not
cause any problems.
Rand
This posting is provided "as is" with no warranties and confers no rights.

multiple sessions when using ADO

Hi
I have an application that uses an ADOConnection named CONNECTION1 and an ADODataset.The dataset fetches a record for editing it.This dataset has no ConnectionString and its connection is the CONNECTION1.After opening the dataset and editing record (befor
posting it) I execute a Stord Procedure to update another record in another table with CONNECTION1.EXECUTE.
After opening the dataset I see a row in Management -> Current Activity -> Process Info in EnterPrise Manager.And After CONNECTION.EXECUTE statement I see 2 rows in Enterprise Manager.
I do all data modification by one ADOConnection in Delphi5 but SQL SERVER 2000 do these modification by separate SPIDs (sessions).
How can I prevent this?
Dose ADOConnection or ADODataset have any property to prevent that? Or should I do anything in SQL SERVER?
Thanks
You may not be able to prevent it. ADO and ODBC, for that matter, will
spawn connections as they see fit. In most cases it does this because the
first SPID is in the middle of some process that has not completed, so the
second SPID is necesary to perform any other task. Normally this does not
cause any problems.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Saturday, February 25, 2012

Multiple selects on PArameter drop down

I have a parameter dropdown as USERID.

I want a functionality that the user can selct multiple userid's to show more than one record as output for comparison purpose.

How can i achieve this multiple select (i.e cntrl+click) within parameter dropdown.

Thanks,

Kiran.

In the report parameters window, check the 'Multi-value' box on your UserID parameter. In the available values section, you can type the possible values, or query the database to get the list.

You will also need to take the multiple values into account on your data query, you'll need to use 'IN' instead of '=' on your where clause for UserID.

This will show on the report as a drop down, where the user can check which values to run the report with.

Hope this helps,

Jarret

|||

Thank you Jarret.

I cannot find teh checkbox for 'multi-value'' allowed . I am using reporting services 2000 on visual Studios.NET 2003 SQL Server 2000.

Is this an issue for me only. I have the checkboxes for allow null values and allow blank values for the userid parameter.

thanks.

|||

Sorry, I had assumed that you were running RS 2005. In Reporting Services 2000, a report parameter can only be defined to allow the user to enter a single value.

Jarret

|||

Thanks Jarret,

Is there a wor around that I should be trying.

Appreciate the time.

|||

Hmmm...

You could try changing the parameter type to string and make your data query use 'IN', then have the user type a comma delimited list. I haven't tried it, but you might be able to get it to work.

Jarret

|||Did this work for you?|||

This did not work for me. I shifted to 2005 and implemented it.

Thanks

Multiple selects on PArameter drop down

I have a parameter dropdown as USERID.

I want a functionality that the user can selct multiple userid's to show more than one record as output for comparison purpose.

How can i achieve this multiple select (i.e cntrl+click) within parameter dropdown.

Thanks,

Kiran.

In the report parameters window, check the 'Multi-value' box on your UserID parameter. In the available values section, you can type the possible values, or query the database to get the list.

You will also need to take the multiple values into account on your data query, you'll need to use 'IN' instead of '=' on your where clause for UserID.

This will show on the report as a drop down, where the user can check which values to run the report with.

Hope this helps,

Jarret

|||

Thank you Jarret.

I cannot find teh checkbox for 'multi-value'' allowed . I am using reporting services 2000 on visual Studios.NET 2003 SQL Server 2000.

Is this an issue for me only. I have the checkboxes for allow null values and allow blank values for the userid parameter.

thanks.

|||

Sorry, I had assumed that you were running RS 2005. In Reporting Services 2000, a report parameter can only be defined to allow the user to enter a single value.

Jarret

|||

Thanks Jarret,

Is there a wor around that I should be trying.

Appreciate the time.

|||

Hmmm...

You could try changing the parameter type to string and make your data query use 'IN', then have the user type a comma delimited list. I haven't tried it, but you might be able to get it to work.

Jarret

|||Did this work for you?|||

This did not work for me. I shifted to 2005 and implemented it.

Thanks

Multiple selects on PArameter drop down

I have a parameter dropdown as USERID.

I want a functionality that the user can selct multiple userid's to show more than one record as output for comparison purpose.

How can i achieve this multiple select (i.e cntrl+click) within parameter dropdown.

Thanks,

Kiran.

In the report parameters window, check the 'Multi-value' box on your UserID parameter. In the available values section, you can type the possible values, or query the database to get the list.

You will also need to take the multiple values into account on your data query, you'll need to use 'IN' instead of '=' on your where clause for UserID.

This will show on the report as a drop down, where the user can check which values to run the report with.

Hope this helps,

Jarret

|||

Thank you Jarret.

I cannot find teh checkbox for 'multi-value'' allowed . I am using reporting services 2000 on visual Studios.NET 2003 SQL Server 2000.

Is this an issue for me only. I have the checkboxes for allow null values and allow blank values for the userid parameter.

thanks.

|||

Sorry, I had assumed that you were running RS 2005. In Reporting Services 2000, a report parameter can only be defined to allow the user to enter a single value.

Jarret

|||

Thanks Jarret,

Is there a wor around that I should be trying.

Appreciate the time.

|||

Hmmm...

You could try changing the parameter type to string and make your data query use 'IN', then have the user type a comma delimited list. I haven't tried it, but you might be able to get it to work.

Jarret

|||Did this work for you?|||

This did not work for me. I shifted to 2005 and implemented it.

Thanks

Friday, March 23, 2012

Multiply record fields with same mainID.

Multiply record fields with same mainID.

Multiply record fields with same mainID.

Wednesday, March 21, 2012

Multiple values in one column

Monday, March 12, 2012

Multiple table record

Wednesday, March 7, 2012

multiple sessions when using ADO

multiple sessions when using ADO

Saturday, February 25, 2012

Multiple selects on PArameter drop down

Multiple selects on PArameter drop down

Multiple selects on PArameter drop down

multiple resultsets

Blog Archive

About Me