Multiple inserts against single insert from temp table

Hi,

I have table (AttributeID INT NOT NULL, ValueID INT NOT NULL), both of them are foreign keys. I need replace several rows which have same AttributeID and various ValueID with new values. So I must delete all rows WHERE AttributeID=@.AttributeID and then insert new values. What is better,

for every row make one call to SP which will do single INSERT;

or supplies all ValueIDs as single parameter to SP and then extracts ValueIDs them to temp table and then do INSERT MyTable SELECT * FROM #TempTable.

Which solution will be faster ? Or do you have idea for other solution which will outperform those two ?

Much thanks for your replies.

In general, a set base solution (i.e. single dml that affects more than one row at a time) is faster and better than row-per-row. So, if you have lots of rows to be processed, putting the data into a temp table (i.e. staging table) then invoking a single dml command would give you the most bang for your buck.

multiple insert call for a table having insert trigger

Hi

I am trying to use multiple insert for a table T1 to add multiple rows.

Ti has trigger for insert to add or update multiple rows in Table T2.

When I provide multiple insert SQL then only first insert works while rest insert statements does not work

Anybody have any idea about why only one insert works for T1

ThanksLooks like SQL Server is treating these multiple inserts as a batch and therefore only assuming one insert.

Try using the GO statement between the inserts and this will cause your trigger to fire with every insert.

INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO

Cheers|||Thanks for reply me back..
I am using cursor to call multiple insert inside that curser.. here is the code..

OPEN DestinationIDList
FETCH NEXT FROM DestinationIDList INTO @.DestinationID

WHILE @.@.FETCH_STATUS = 0

BEGIN

INSERT INTO Table(ID, DestinationID) VALUES(@.ID, @.DestinationID)

FETCH NEXT FROM DestinationIDList INTO @.DestinationID
END
CLOSE DestinationIDList
DEALLOCATE DestinationIDList

What should I have to do so that trigger fire for each insert ?

Thanks

Originally posted by aldo_2003
Looks like SQL Server is treating these multiple inserts as a batch and therefore only assuming one insert.

Try using the GO statement between the inserts and this will cause your trigger to fire with every insert.

INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO

Cheers|||What you could do is to write the Insert as a stored procedure.

Then call this proc from your cursor passing the variables that you have defined in your cursor

something like :

create proc Insert_T1
(@.id as int , @.DestinationID as int)
as

INSERT INTO T1(ID, DestinationID) VALUES(@.ID, @.DestinationID)
go

All you do call this proc in your cursor land this should make your trigger fire for every insert you perform

Give it a go and see if it works

Cheers|||No Luck :-(|||Can you post the code for your trigger

Cheers|||Here is the trigger's code

CREATE TRIGGER TR_TD
ON dbo.TRHistory FOR INSERT

AS

DECLARE @.ID int
DECLARE @.DSTID int
DECLARE @.RT decimal (18,4)
DECLARE @.Time datetime
DECLARE @.GTID int
DECLARE @.GTExist int
DECLARE @.intErrorCode INT
SET @.GTID = 0
SET @.GTExist = 0
SET @.ID = 0

-- CHECK ID' S VALIDITY

SELECT @.ID = i.ID, @.DSTID= i.DSTID,
@.RT = i.RT, @.Time = i.Time
FROM Inserted i
INNER JOIN ITSPS ON ITSPS.ID = i.ID

IF @.ID <> 0

BEGIN

-- FIND OUT NO OF GTS FOR SPECIFIC DSTS TO UPDATE FOR CORRESPONDING GT
DECLARE GTList CURSOR FOR
-- SELECT GTS FOR DSTID
SELECT GTs.GTID
FROM GTs INNER JOIN
GTDSTs ON GTs.GTID = GTDSTs.GTID INNER JOIN
ITSPs ON GTs.ID = ITSPs.ID
Where GTs.ID = @.ID AND DSTID = @.DSTID

OPEN GTList

FETCH NEXT FROM GTList INTO @.GTID

-- IF NO GT FOUND OF SPECIFIC DST FOR ITSP THEN REJECT
IF (@.GTID = 0)
BEGIN
SELECT @.intErrorCode = 1
CLOSE GTList
DEALLOCATE GTList
GOTO PROBLEM

END
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0

BEGIN

-- CHECK WIEHTER GT ENTERY EXISTS IN TR TABLE
-- IF SO THEN UPDATE ELSE INTER NEW FOR GT

SELECT @.GTExist = GTid
FROM TR
WHERE GTid = @.GTid AND DSTID = @.DSTID

IF @.GTExist > 0

UPDATE TR
SET RT = @.RT,
Time = getdate()

WHERE GTID = @.GTid


ELSE

INSERT INTO TR (GTID, DSTID, RT)
VALUES (@.GTID,@.DSTID,@.RT)
-- IN CASE FOR ANY EXCEPTION GO TO PROBLEM PARA AND CLOSE & DEALLOCATE CURSOR
SELECT @.intErrorCode = @.@.ERROR
IF (@.intErrorCode <> 0)
BEGIN
CLOSE GTList
DEALLOCATE GTList
GOTO PROBLEM
END
FETCH NEXT FROM GTList INTO @.GTID


END
CLOSE GTList
DEALLOCATE GTList
COMMIT TRAN
END
ELSE
-- INVALID ID
BEGIN
RAISERROR (' Invalid ID ', 16, 1)
ROLLBACK TRAN
END


PROBLEM:
IF (@.intErrorCode <> 0)
BEGIN
IF (@.intErrorCode = 1)
RAISERROR (' Insert is rejected due to invalid GT DST info', 16, 1)
ELSE
RAISERROR ('Error occured to udated info for tr' s. Please contact administrator ', @.intErrorCode, 1)
ROLLBACK TRAN


END|||Hi aldo_2003

I am Waiting.. Only problem is that if i use multiple insert with GO then multiple insert works.. but when i call is in cursor for multiple then it add only first insert..|||Just got back from lunch mate

Am goiung to try a couple if things and get back to you

Cheers|||Had a quick look at the trigger

Don't think it is the GO statement as I managed to get a test trigger to fire twice without the GO.

I'm not sure why your trigger is only firing once.

What you might want to do is to try and reduce the complexity of the code within the trigger by putting that logic within the stored proc and calling this from your trigger.

You have cursors within cursors when you take the trigger into account and this may be causing you issues that you are not aware of.

Get the trigger to fire properly without any logic in it i.e

Get the first cursor inserting into your table and the trigger firing a simple insert into a test table. Once you have that then implement your trigger logic in a stored proc and call that from the trigger.

I hope this helps, let me know how you get on.

Cheers|||Well there are just so many things...

first you don't need a cursor...collapse the cursor and the insert in to 1...

second a cursor in a trigger can't be a good idea performance wise...but like I said collapse them

third

-- IF NO GT FOUND OF SPECIFIC DST FOR ITSP THEN REJECT
IF (@.GTID = 0)

isn't a check for existance...

Look at @.@.ROWCOUNT

fourth...never mind...fixe the insert first...

Multiple insert

Hi, I'm trying to insert multiple rows with the following statment and
am getting a syntax error on line 3. The datatypes are varchars except
for status which is numeric.

insert S (S#, SNAME, STATUS, CITY)
values
('S2', 'Jones', 10, 'Paris'),
('S3', 'Blake', 30, 'Paris'),
('S4', 'Clark', 20, 'London'),
('S5', 'Adams', 30, 'Athens');

What's wrong?
Thanks,
SashiYou have to use the INSERT... SELECT form to insert multiple rows.

INSERT INTO S (s#, sname, status, city)
SELECT 'S2', 'Jones', 10, 'Paris' UNION ALL
SELECT 'S3', 'Blake', 30, 'Paris' UNION ALL
SELECT 'S4', 'Clark', 20, 'London' UNION ALL
SELECT 'S5', 'Adams', 30, 'Athens' ;

--
David Portas
SQL Server MVP
--|||The real problem is that SQL Server does not yet support SQL-92 syntax,
in spite of having the power to do so. Your choices are:

1) use a series of INSERT INTO statements (notice that INSERT is a
proprietary shorthand, not Standard SQL).

2) use a proprietary SELECT ..UNION ALL chain to build a table the same
way that the VALUES table constructor would.

Multiple DISTINCTS?

Hello,
Is it possible to constrain a query so that I receive only the duplicate rows (all column data matches)? For example:
SELECT DISTINCT COL1,
DISTINCT COL2,DISTINCT COL3
FROM TABLE
I did a search on multiple DISTINCTS but came up with nothing. Thanks in advance.DISTINCT applys to the whole row of the result set, not individual columns of it. When you do aSELECT DISTINCT col1, col2, col3
FROM myTableyou will get one row for any given combination of col1, col2, and col3. You can use DISTINCT within aggragate functions to get aggrigates of the unique values, but when applied against a result set, DISTINCT applies to the whole set (row).

-PatP|||uh, huh?

SELECT DISTINCT Col1, Col2, Col3

Will give you 1 unique row..

You want to only see dups?

SELECT Col1, Col2, Col3
FROM myTable99
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1

Is that what your after?|||Originally posted by Brett Kaiser
uh, huh?

SELECT DISTINCT Col1, Col2, Col3

Will give you 1 unique row..

You want to only see dups?

SELECT Col1, Col2, Col3
FROM myTable99
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1

Is that what your after?

------------------

Thanks for your suggestions. I am looking for a resultset of:

COL1 COL2 COL3
---------
brown tall dog
brown tall dog
brown tall dog

Thanks again for your help.|||Did the code give you what you wanted...I'm not sure...|||Originally posted by Brett Kaiser
Did the code give you what you wanted...I'm not sure...

Not yet, but I could be doing something wrong.

I've tried the following:
SELECT DISTINCT COL1, COL2, COL3
FROM myTABLE

and:
SELECT COL1, COL2, COL3
FROM myTABLE
GROUP BY COL1, COL2, COL3

and:
SELECT DISTINCT COL1, COL2, COL3
FROM myTABLE
HAVING COUNT(*) > 1

and I've tried:
SELECT COL1, COL2, COL3
FROM myTABLE
GROUP BY COL1, COL2, COL3
HAVING COUNT(*) > 1|||Well that should work...

USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(25),Col2 varchar(25),Col3 varchar(25))
GO

INSERT INTO myTable99(Col1,Col2,Col3)
select 'Brown','Tall','Dog' UNION ALL
select 'Brown','Tall','Dog' UNION ALL
select 'Brown','Tall','Dog' UNION ALL
select 'Blonde','Small','Pussy cat' UNION ALL
select 'Red','Medium','Snapper Turtle'
GO

SELECT Col1, Col2, Col3
FROM myTable99
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1
GO

DROP TABLE myTable99
GO

Oh, are you looking for all three?|||SELECT * FROM myTable99 o WHERE EXISTS(
SELECT *
FROM myTable99 i
WHERE o.Col1 = i.Col1
AND o.Col2 = i.Col2
AND o.Col3 = i.Col3
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1)
GO

All three...|||Brett,
That should do it! Combined with the info in the links provided in this thread: http://www.dbforums.com/t991775.html

I should be able to put something together. I really do appreciate all your hard work.

Regards,
Americus Johnson

Thanks also to Pat!|||Hard Work...Lord no...

That's why I became a dba...

:D

PS Don't forget to take ALL of your animal freinds...ya might get lucky|||Originally posted by Brett Kaiser
Hard Work...Lord no...

That's why I became a dba...

:D

PS Don't forget to take ALL of your animal freinds...ya might get lucky

True dat.|||yup

Multiple Detail Rows Rendering in CSV

I'm not sure if this is the expected feature, but when I render a
simple table that has two detail rows to CSV the second row is appended
to the end of the first row. This does not happen in PDF/HTML/EXCEL.
Here how the report is designed:
Table
Column1|Column2|Column3
TextBox1|TextBox2|TextBox3
TextBox4|TextBox5|TextBox6
In Excel, the output is:
1,2,3
a,b,c
4,5,6
a,b,c
In CSV, the output is
1,2,3,a,b,c
4,5,6,a,b,c
Is there any way to get the output to work in CSV as it does in Excel?
Thanks
scottSome additional research and testing shows that this happens regardless
of if the two detail rows are in one table or two. If you put two
"single detail row" tables in seperate list boxes and have the list
boxes group by the same field you will end up with the same "look and
feel" in HTML/PDF/Excel view (Excel will be slightly different).
However, when saving to CSV the second table's row will be appended to
the end of the first table's row just like in the example above.
The dirty workaround to this is to save as Excel and then save as CSV.
However, the Excel render is much slower since there is a lot of extra
formatting and it produces a much larger file.
Is there any chance this is a bug? Am I setting something up wrong?
It also happens regardless of the encoding method (ASCII vs Unicode).
Is this by design?
Thanks for the help.
scott bieker

Multiple datasource for a grid

Hello,
I have a report specification with a simple grid with 4 indicators and an
dimension on rows
Indicator 1,2 and 3 are coming from a cube so i retrive the data with a MDX
query
The last indicator come from a SQL Server (2k5) database in another system.
Today we have a set a reports on the cube and a set of reports on the SQL
darabase.
Can i make this kind of reports with 2 datasources in Reporting services
(2k5)?
Regards,
NicolasMaybe. I haven't done this with a cube but I have done it with relational
data. The concern I have is less where the data is coming from but whether
or not you are using a table control or a matrix to show the data. If a
table control I know it will work.Create a sub report (a sub report is just
a regular report with a parameter you will use to tie the sub report to the
base report). Test the sub report (going agains the SQL data).
Create an extra cell in the row on the table grid. Drag and drop the sub
report onto the cell. Do a right mouse click on the sub report and map the
parameter to the appropriate field.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nicolas Lievain" <n.lievain@.bravosolution.fr> wrote in message
news:OvKBy0efIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a report specification with a simple grid with 4 indicators and an
> dimension on rows
> Indicator 1,2 and 3 are coming from a cube so i retrive the data with a
> MDX query
> The last indicator come from a SQL Server (2k5) database in another
> system.
> Today we have a set a reports on the cube and a set of reports on the SQL
> darabase.
> Can i make this kind of reports with 2 datasources in Reporting services
> (2k5)?
> Regards,
> Nicolas
>

Multiple Columns Map to Different Rows in Join Table

Can a SQL statement be written that returns all of the information in
the Project table and the corresponding FullName for both the
CreatedBy and LastModifiedBy fields? Table structures are below.
A typical JOIN clause seems insufficient because every row in the
Project table maps to 2 rows in the User table. When doing reporting,
I've always avoided this problem by using subreports to do individual
lookups. However, I'm curious to see if there is a more direct
approach using SQL only.
Project
- ProjectNo
- ProjectName
- CreatedByUserNo
- LastModifiedByUserNo
User
- UserNo
- FullName
Your input is greatly appreciated.
Best Regards,
DavidSELECT P.projectno, P.projectname,
C.fullname AS createdbyuser,
M.fullname AS lastmodifiedbyuser
FROM Project AS P
JOIN User AS C
ON P.createdbyuserno = C.userno
LEFT JOIN User AS M
ON P.lastmodifiedbyuserno = M.userno
(untested)
--
David Portas
--
Please reply only to the newsgroup
--

Multiple Columns into Single Row -- Very urgent

Hi. I want to return multiple rows into a single row in different columns. For example my query returns something like this

The query looks like this
Select ID, TYPE, VALUE From myTable Where filtercondition = 1

ID TYPE VALUE
1 type1 12
1 type2 15
2 type1 16
2 type2 19

Each ID will have the same number of types and each type for each ID might have a different value. So if there are only two types then each ID will have two types. Now I want to write the query in such a way that it returns

ID TYPE1 TYPE2 VALUE1 VALUE2
1 type1 type2 12 15
2 type1 type2 16 19

Type1, Type2, Value1, and Value2 are all dynamic. Can someone help me please. Thank you.

I've done something like this, but not in SQL. What I do is build a datatable from my select results, populate it, and then return that to my caller. It works something like;

Get Results

Loope through results to get my columns (In my case there may not be a value for every type.)

Build datatable

Loop through results again, populating datatable.

Return datatable

Multiple "returnid" in SQLXML?

SQL Server 2000 SQLXML 3.0
Using a single updategram, I am adding two rows to a single table.
UpdateGram follows:
<updg:root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync updg:nullvalue="IsNULL">
<updg:before />
<updg:after updg:returnid="SysCKey1">
<Name updg:at-identity="SysCKey1" Last="313" First="131"
FKeyTable="Contact" FKey="264" />
</updg:after>
<updg:before />
<updg:after updg:returnid="SysCKey2">
<Name updg:at-identity="SysCKey2" Last="654" First="654"
FKeyTable="Contact" FKey="264" />
</updg:after>
</updg:sync>
</updg:root>
Because I am doing inserts, I need to have the server side generated keys
returned (as accomplished via the “returnid” and “at-identity” attri
butes).
The command execution is being handled by a “SqlXmlCommand.ExecuteXmlReade
r”
(C#) method.
The inserts are taking place as both rows are being inserted into the table.
However, I am only seeing one “returnid” element in my return (C#) XmlRe
ader
object.
Return XML follows:
<returnid><SysCKey1>263</SysCKey1></returnid>
When posting the updategram directly to the database via an HTML POST, the
inserts are also taking place, however I receive the following error message
:
Only one top level element is allowed in an XML document. Error processing
resource 'http://test...
<returnid><SysCKey1>265</SysCKey1></returnid>
<returnid><SysCKey2>266</SysCKey2></returnid>
My theory is that since the actual XML being returned does not have a valid
root element (that is there are more than one “returnid” elements) that
the
XML document is invalid. This is the reason for the POST error, but is
causing the SqlXmlCommand.ExecuteXmlReader method to see only the first
returnid element and discarding the rest. This is only a theory.
Is there anyway to return multiple returnid elements? Perhaps envelop the
return XML in a valid root element to allow for multiples? How would that b
e
accomplished?Please put a ROOT element around updg:root like this:
<ROOT>
<updg:root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync updg:nullvalue="IsNULL">
<updg:before />
<updg:after updg:returnid="SysCKey1">
<Name updg:at-identity="SysCKey1" Last="313" First="131"
FKeyTable="Contact" FKey="264" />
</updg:after>
<updg:before />
<updg:after updg:returnid="SysCKey2">
<Name updg:at-identity="SysCKey2" Last="654" First="654"
FKeyTable="Contact" FKey="264" />
</updg:after>
</updg:sync>
</updg:root>
</ROOT>
That should fix the problem.
"Geoff Ely" <GeoffEly@.discussions.microsoft.com> wrote in message
news:89192677-9772-47CE-B324-5AAC15D20743@.microsoft.com...
> SQL Server 2000 SQLXML 3.0
> Using a single updategram, I am adding two rows to a single table.
> UpdateGram follows:
> <updg:root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
> <updg:sync updg:nullvalue="IsNULL">
> <updg:before />
> <updg:after updg:returnid="SysCKey1">
> <Name updg:at-identity="SysCKey1" Last="313" First="131"
> FKeyTable="Contact" FKey="264" />
> </updg:after>
> <updg:before />
> <updg:after updg:returnid="SysCKey2">
> <Name updg:at-identity="SysCKey2" Last="654" First="654"
> FKeyTable="Contact" FKey="264" />
> </updg:after>
> </updg:sync>
> </updg:root>
> Because I am doing inserts, I need to have the server side generated keys
> returned (as accomplished via the "returnid" and "at-identity"
> attributes).
> The command execution is being handled by a
> "SqlXmlCommand.ExecuteXmlReader"
> (C#) method.
> The inserts are taking place as both rows are being inserted into the
> table.
> However, I am only seeing one "returnid" element in my return (C#)
> XmlReader
> object.
> Return XML follows:
> <returnid><SysCKey1>263</SysCKey1></returnid>
> When posting the updategram directly to the database via an HTML POST, the
> inserts are also taking place, however I receive the following error
> message:
> Only one top level element is allowed in an XML document. Error processing
> resource 'http://test...
> <returnid><SysCKey1>265</SysCKey1></returnid>
> <returnid><SysCKey2>266</SysCKey2></returnid>
> My theory is that since the actual XML being returned does not have a
> valid
> root element (that is there are more than one "returnid" elements) that
> the
> XML document is invalid. This is the reason for the POST error, but is
> causing the SqlXmlCommand.ExecuteXmlReader method to see only the first
> returnid element and discarding the rest. This is only a theory.
> Is there anyway to return multiple returnid elements? Perhaps envelop the
> return XML in a valid root element to allow for multiples? How would that
> be
> accomplished?
>

Multiple "returnid" in SQLXML?

SQL Server 2000 SQLXML 3.0
Using a single updategram, I am adding two rows to a single table.
UpdateGram follows:
<updg:root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync updg:nullvalue="IsNULL">
<updg:before />
<updg:after updg:returnid="SysCKey1">
<Name updg:at-identity="SysCKey1" Last="313" First="131"
FKeyTable="Contact" FKey="264" />
</updg:after>
<updg:before />
<updg:after updg:returnid="SysCKey2">
<Name updg:at-identity="SysCKey2" Last="654" First="654"
FKeyTable="Contact" FKey="264" />
</updg:after>
</updg:sync>
</updg:root>
Because I am doing inserts, I need to have the server side generated keys
returned (as accomplished via the “returnid” and “at-identity” attributes).
The command execution is being handled by a “SqlXmlCommand.ExecuteXmlReader”
(C#) method.
The inserts are taking place as both rows are being inserted into the table.
However, I am only seeing one “returnid” element in my return (C#) XmlReader
object.
Return XML follows:
<returnid><SysCKey1>263</SysCKey1></returnid>
When posting the updategram directly to the database via an HTML POST, the
inserts are also taking place, however I receive the following error message:
Only one top level element is allowed in an XML document. Error processing
resource 'http://test...
<returnid><SysCKey1>265</SysCKey1></returnid>
<returnid><SysCKey2>266</SysCKey2></returnid>
My theory is that since the actual XML being returned does not have a valid
root element (that is there are more than one “returnid” elements) that the
XML document is invalid. This is the reason for the POST error, but is
causing the SqlXmlCommand.ExecuteXmlReader method to see only the first
returnid element and discarding the rest. This is only a theory.
Is there anyway to return multiple returnid elements? Perhaps envelop the
return XML in a valid root element to allow for multiples? How would that be
accomplished?
Please put a ROOT element around updg:root like this:
<ROOT>
<updg:root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync updg:nullvalue="IsNULL">
<updg:before />
<updg:after updg:returnid="SysCKey1">
<Name updg:at-identity="SysCKey1" Last="313" First="131"
FKeyTable="Contact" FKey="264" />
</updg:after>
<updg:before />
<updg:after updg:returnid="SysCKey2">
<Name updg:at-identity="SysCKey2" Last="654" First="654"
FKeyTable="Contact" FKey="264" />
</updg:after>
</updg:sync>
</updg:root>
</ROOT>
That should fix the problem.
"Geoff Ely" <GeoffEly@.discussions.microsoft.com> wrote in message
news:89192677-9772-47CE-B324-5AAC15D20743@.microsoft.com...
> SQL Server 2000 SQLXML 3.0
> Using a single updategram, I am adding two rows to a single table.
> UpdateGram follows:
> <updg:root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
> <updg:sync updg:nullvalue="IsNULL">
> <updg:before />
> <updg:after updg:returnid="SysCKey1">
> <Name updg:at-identity="SysCKey1" Last="313" First="131"
> FKeyTable="Contact" FKey="264" />
> </updg:after>
> <updg:before />
> <updg:after updg:returnid="SysCKey2">
> <Name updg:at-identity="SysCKey2" Last="654" First="654"
> FKeyTable="Contact" FKey="264" />
> </updg:after>
> </updg:sync>
> </updg:root>
> Because I am doing inserts, I need to have the server side generated keys
> returned (as accomplished via the "returnid" and "at-identity"
> attributes).
> The command execution is being handled by a
> "SqlXmlCommand.ExecuteXmlReader"
> (C#) method.
> The inserts are taking place as both rows are being inserted into the
> table.
> However, I am only seeing one "returnid" element in my return (C#)
> XmlReader
> object.
> Return XML follows:
> <returnid><SysCKey1>263</SysCKey1></returnid>
> When posting the updategram directly to the database via an HTML POST, the
> inserts are also taking place, however I receive the following error
> message:
> Only one top level element is allowed in an XML document. Error processing
> resource 'http://test...
> <returnid><SysCKey1>265</SysCKey1></returnid>
> <returnid><SysCKey2>266</SysCKey2></returnid>
> My theory is that since the actual XML being returned does not have a
> valid
> root element (that is there are more than one "returnid" elements) that
> the
> XML document is invalid. This is the reason for the POST error, but is
> causing the SqlXmlCommand.ExecuteXmlReader method to see only the first
> returnid element and discarding the rest. This is only a theory.
> Is there anyway to return multiple returnid elements? Perhaps envelop the
> return XML in a valid root element to allow for multiples? How would that
> be
> accomplished?
>

Multi-Parameter (Select All) Detection in SSRS 2005?

I'd like to know if SSRS provides a way to determine if all rows are selected from a multi_valued parameter dropdown list? I thought of how I could do this programmatically, but didn't want to build something that is already in the product (if it exists that is). Thanks.

There is no built-in functionality, but here are some ideas:

* if the multi value parameter has a pre-defined (constant) list of valid values, you know how many values are available for selection. The report parameters in RS 2005 expose a new property called .Count which tells you the count of selected parameter values (e.g. =Parameters!P1.Count). Hence, you could compare the count of the selected values with the count of the total values.

* if the multi value parameter has a dataset-based valid values list, you could just use the same field in a CountDistinct aggregate function to determine how many valid values are available, e.g. =CountDistinct(Fields!A.Value) and compare it again with the Count of selected values.

-- Robert

|||Thank you Robert. I forgot about CountDistinct.