return all rows

Hello,
Here is my query for my report. Can you make this query fetch all rows if
@.myID parameter is null?
SELECT FName, MName, LName, ID
FROM MyTable
WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
Thanks,
Jim.write a stored procedure and use an if condition
"JIM.H." wrote:
> Hello,
> Here is my query for my report. Can you make this query fetch all rows if
> @.myID parameter is null?
> SELECT FName, MName, LName, ID
> FROM MyTable
> WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> Thanks,
> Jim.
>|||is it possible without stored procedure?
"NI" wrote:
> write a stored procedure and use an if condition
> "JIM.H." wrote:
> > Hello,
> > Here is my query for my report. Can you make this query fetch all rows if
> > @.myID parameter is null?
> >
> > SELECT FName, MName, LName, ID
> > FROM MyTable
> > WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> >
> > Thanks,
> > Jim.
> >|||Hello Jim
Try to set your "is null" clause in the report settings itself
Ruud Boots
Holland
> SELECT FName, MName, LName, ID
> FROM MyTable
> WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
"JIM.H." wrote:
> Hello,
> Here is my query for my report. Can you make this query fetch all rows if
> @.myID parameter is null?
> SELECT FName, MName, LName, ID
> FROM MyTable
> WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> Thanks,
> Jim.
>|||Here is a technique I like for the WHERE clause so that null means "all":
SELECT FName, MName, LName, ID
FROM MyTable
WHERE (ID = @.myID or @.myID is null)
AND myDate BETWEEN @.StartDate AND @.EndDate
I am currently trying to find how to pass a null parameter to a report. If
you know how to pass a null in RS, I would appreciate the feedback.
Thanks.
Randy Howie
--
"Ruud" wrote:
> Hello Jim
> Try to set your "is null" clause in the report settings itself
> Ruud Boots
> Holland
> > SELECT FName, MName, LName, ID
> > FROM MyTable
> > WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> "JIM.H." wrote:
> > Hello,
> > Here is my query for my report. Can you make this query fetch all rows if
> > @.myID parameter is null?
> >
> > SELECT FName, MName, LName, ID
> > FROM MyTable
> > WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> >
> > Thanks,
> > Jim.
> >sql

Return a 0 instead of null

I need to verify if I have partial sales of certain items. I request data
from the server and I am getting NULL.
select sum(matrixamount) matrix
from sales
where invid =@.MerchID
and assettype = 2
If there are NO transactions in Sales for invid = @.MerchID I'd love to
return a 0.
I have tried :
CREATE FUNCTION dbo.GetMatrixTotal
( @.iid int )
RETURNS int AS
BEGIN
declare @.ret int
select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
end ) from sales
where invid = @.iid and assettype = 2
return @.ret
END
Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
But how do I flip it to 0 so there is a return back to a data container in
.NET
TIA
__StephenTry
select ISNULL(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID
and assettype = 2
Mike A.
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:OvYQa73aFHA.1148@.tk2msftngp13.phx.gbl...
>I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> .NET
> TIA
> __Stephen
>
>|||Try,
select isnull(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID and assettype = 2
AMB
"Stephen Russell" wrote:

> I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> ..NET
> TIA
> __Stephen
>
>

Return a 0 instead of null

I need to verify if I have partial sales of certain items. I request data
from the server and I am getting NULL.
select sum(matrixamount) matrix
from sales
where invid =@.MerchID
and assettype = 2
If there are NO transactions in Sales for invid = @.MerchID I'd love to
return a 0.
I have tried :
CREATE FUNCTION dbo.GetMatrixTotal
( @.iid int )
RETURNS int AS
BEGIN
declare @.ret int
select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
end ) from sales
where invid = @.iid and assettype = 2
return @.ret
END
Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
But how do I flip it to 0 so there is a return back to a data container in
.NET
TIA
__StephenTry
select ISNULL(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID
and assettype = 2
Mike A.
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:OvYQa73aFHA.1148@.tk2msftngp13.phx.gbl...
>I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> .NET
> TIA
> __Stephen
>
>|||Try,
select isnull(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID and assettype = 2
AMB
"Stephen Russell" wrote:
> I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> ..NET
> TIA
> __Stephen
>
>

Return a 0 instead of null

I need to verify if I have partial sales of certain items. I request data
from the server and I am getting NULL.
select sum(matrixamount) matrix
from sales
where invid =@.MerchID
and assettype = 2
If there are NO transactions in Sales for invid = @.MerchID I'd love to
return a 0.
I have tried :
CREATE FUNCTION dbo.GetMatrixTotal
( @.iid int )
RETURNS int AS
BEGIN
declare @.ret int
select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
end ) from sales
where invid = @.iid and assettype = 2
return @.ret
END
Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
But how do I flip it to 0 so there is a return back to a data container in
..NET
TIA
__Stephen
Try
select ISNULL(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID
and assettype = 2
Mike A.
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:OvYQa73aFHA.1148@.tk2msftngp13.phx.gbl...
>I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> .NET
> TIA
> __Stephen
>
>
|||Try,
select isnull(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID and assettype = 2
AMB
"Stephen Russell" wrote:

> I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> ..NET
> TIA
> __Stephen
>
>

Return 0 rather than Null

i have query which does the following select x from y where t = "House"

x is an integer. If no record is found, how do i get it to return 0 rather than null?

Use a stored procedure and return the affected rows. This will result in 0 if the Select statement you exampled gets no results.|||This is a query within a stored procedure so is there any other way?|||Why can't you add it to the stored procedure?|||Because i would prefer to do it an alternative way not using a stored procedure.|||The only other way is to assign a value of 0 to an int if the calling app detects that no rows were returned to it by the select statement.|||

Are you using this to populate a datatable or a dataset? This would be the most logical way to handle both cases (returning rows OR looking for a record count).

In your code, do:

' assuming table is a System.Data.DataTable that has been' populated with the results of a SQL query.If table.Rows.Count > 0Then' the table has rows.Else' the table has no rows.End If

|||Thanks for your help. It is returned to a variable within a stored procedure. Could I use an IF statement or anything within the SP?|||

Sorry, I didn't see that your select statement was inside an sp.

You can use an if statement if you want. Just like c++ or c#, if it's only one line after the if, you don't need anything else. If there are multiple lines, use BEGIN and END tags.

Also, I'm not sure how to get the number of rows within the last select statement, but you could perform a count before doing the select.

DECLARE @.Countint-- count the number of rows to be selectedSELECT @.Count =Count(*)from MyTableWhere FName ='Jane'IF @.Count > 0BEGIN-- Perform the SQL statement to get rows.SELECT *from MyTableWhere FName ='Jane'ENDELSEBEGIN-- return 0, no rows were returned.Return 0-- or Return @.CountEND

|||

this would work faster:

IF EXISTS(SELECT *from MyTableWhere FName ='Jane')

BEGIN
-- Perform the SQL statement to get rows.
SELECT *from MyTableWhere FName ='Jane'
END
ELSE
BEGIN
-- return 0 in first cell in one row
SELECT count(*)from MyTableWhere FName ='Jane' -- or just SELECT 0
-- and return if you need
RETURN 0

END

Return 0 if null

Hi all,
I have got a query that returns values based on a date range and grouped by
week. I need to return a value of 0 if there are no entries for that week. At
the moment it just skips that week all together.
Any ideas?
Thanks
Without more details, I'd suggest lookign into IsNull( variable, 0 ) or a
left outer join if you're using multiple tables and a lack of entries for
that week table is eliminating the week row. But to give a specific answer,
we'd need more details.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
"Andrew Jurgens" <AndrewJurgens@.discussions.microsoft.com> wrote in message
news:EBB765D2-2A3D-4151-A241-0B52A9B6E66C@.microsoft.com...
> Hi all,
> I have got a query that returns values based on a date range and grouped
> by
> week. I need to return a value of 0 if there are no entries for that week.
> At
> the moment it just skips that week all together.
> Any ideas?
> Thanks
|||Hi Paul,
My current query is as follows.
SELECT Format([Counting Type_QRY].Date,'ww') AS Expr1, [Counting
Type_QRY].Branch, [Counting Type_QRY].Type, Sum([Counting
Type_QRY].CountOfType) AS SumOfCountOfType
FROM (Branch INNER JOIN Type ON Branch.ID = Type.ID) INNER JOIN [Counting
Type_QRY] ON Branch.ID = [Counting Type_QRY].Branch
GROUP BY Format([Counting Type_QRY].Date,'ww'), [Counting Type_QRY].Branch,
[Counting Type_QRY].Type;
This works great counting my entries and putting then grouping by week.
Trouble is if there is no info for a week it skipps that week.
34 = 4
35 = 2
37 = 7
I need it to return
34 = 4
35 = 2
36 = 0
37 = 7
I am fairly new to this so please excuse the query if it is not great. Just
trying to grow my skills in the real world.
Thanks
"Paul Nielsen" wrote:

> Without more details, I'd suggest lookign into IsNull( variable, 0 ) or a
> left outer join if you're using multiple tables and a lack of entries for
> that week table is eliminating the week row. But to give a specific answer,
> we'd need more details.
> --
> -Paul Nielsen, SQL Server MVP
> SQL Server 2000 Bible, Wiley Press
>
> "Andrew Jurgens" <AndrewJurgens@.discussions.microsoft.com> wrote in message
> news:EBB765D2-2A3D-4151-A241-0B52A9B6E66C@.microsoft.com...
>
>
|||Andrew Jurgens wrote:
> Hi Paul,
> My current query is as follows.
> SELECT Format([Counting Type_QRY].Date,'ww') AS Expr1, [Counting
> Type_QRY].Branch, [Counting Type_QRY].Type, Sum([Counting
> Type_QRY].CountOfType) AS SumOfCountOfType
> FROM (Branch INNER JOIN Type ON Branch.ID = Type.ID) INNER JOIN
> [Counting Type_QRY] ON Branch.ID = [Counting Type_QRY].Branch
> GROUP BY Format([Counting Type_QRY].Date,'ww'), [Counting
> Type_QRY].Branch, [Counting Type_QRY].Type;
> This works great counting my entries and putting then grouping by
> week. Trouble is if there is no info for a week it skipps that week.
> 34 = 4
> 35 = 2
> 37 = 7
> I need it to return
> 34 = 4
> 35 = 2
> 36 = 0
> 37 = 7
> I am fairly new to this so please excuse the query if it is not
> great. Just trying to grow my skills in the real world.
> Thanks
Sounds like you need an OUTER JOIN. I don't know your data, so use an
outer join against the table that may not have a foreign key
relationship. If you wanted all Accounts from the accounts table even if
some of the accounts didn't have a comment in the comments table, you
would:
From Accounts Outer Join Comments on Accounts.id = Comments.id
David Gugick
Imceda Software
www.imceda.com
|||Thanks David,
My problem is that I am querying a date range but there may not be entries
for all dates within that range in the table. I still need to return all
dates even if there is no data. Hence my previous
This works great counting my entries and putting then grouping by[vbcol=seagreen]
I really appreciate your input. Have been stuck for a bit and need to get
out of this hole!
Thanks.
"David Gugick" wrote:

> Andrew Jurgens wrote:
>
> Sounds like you need an OUTER JOIN. I don't know your data, so use an
> outer join against the table that may not have a foreign key
> relationship. If you wanted all Accounts from the accounts table even if
> some of the accounts didn't have a comment in the comments table, you
> would:
> From Accounts Outer Join Comments on Accounts.id = Comments.id
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||On Thu, 14 Oct 2004 01:59:20 -0700, Andrew Jurgens wrote:

>Thanks David,
>My problem is that I am querying a date range but there may not be entries
>for all dates within that range in the table. I still need to return all
>dates even if there is no data. Hence my previous
Hi Andrew,
Looks like you need a calendar table.
See http://www.aspfaq.com/show.asp?id=2516.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

retrieving XML issues - SQL 2000

In my SQL Server 2000 database, I need to return several records
containing xml as a single xml.
This almost gives me what I want:
select
1 tag,
null parent,
XMLData [XMLDataRoot!1!!xmltext]
from XMLTable
for xml explicit
The trouble is that each record includes an xml declaration tag:
<?xml version="1.0" encoding="UTF-8"?>
So, a sample record might look something like:
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
And I want this returned:
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
...
Instead I'm getting this:
<XMLDataRoot version="1.0" encoding="UTF-8">
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
<XMLDataRoot version="1.0" encoding="UTF-8">
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
...
This, of course, is not well-formed xml and results in error as soon as
my app tries to parse it. (I also tried changing the nodename in my
query to differ from the root node name in the record, but still, it
returns not well-formed xml)
If I change my query to use the xml directive instead of xmltext like
this:
select
1 tag,
null parent,
XMLData [XMLDataRoot!1!!xml]
from XMLTable
for xml explicit
I get this:
<XMLDataRoot>
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
</XMLDataRoot>
<XMLDataRoot>
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
</XMLDataRoot>
...
This also results in error as soon as my app tries to parse it: "The XML
Declaration is Unexpected"
I can't strip the xml declaration out using string functions, as the xml
is stored in a text field. For another stored procedure I want to
return just a single record, and the xml declaration is desired for
that.
How can I return the xml without the xml declarations fouling up my
results?
thanks
-ivan.
Two options:
Use string functions to drop the XML declarations (may need more complex
coding because of TEXT column).
Use SQL Server 2005 and cast/alter the column to an XML data type (you still
may have some issues with the UTF-8 encoding depending on your column code
page, then first cast it to varbinary(max) before casting it to XML).
Best regards
Michael
"gilly3" <news@.NOSPAMgilly3.com> wrote in message
news:Xns97637C2EC4F26newsNOSPAMgilly3com@.207.46.24 8.16...
> In my SQL Server 2000 database, I need to return several records
> containing xml as a single xml.
> This almost gives me what I want:
> select
> 1 tag,
> null parent,
> XMLData [XMLDataRoot!1!!xmltext]
> from XMLTable
> for xml explicit
> The trouble is that each record includes an xml declaration tag:
> <?xml version="1.0" encoding="UTF-8"?>
> So, a sample record might look something like:
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> And I want this returned:
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> ...
> Instead I'm getting this:
> <XMLDataRoot version="1.0" encoding="UTF-8">
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> <XMLDataRoot version="1.0" encoding="UTF-8">
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> ...
> This, of course, is not well-formed xml and results in error as soon as
> my app tries to parse it. (I also tried changing the nodename in my
> query to differ from the root node name in the record, but still, it
> returns not well-formed xml)
> If I change my query to use the xml directive instead of xmltext like
> this:
> select
> 1 tag,
> null parent,
> XMLData [XMLDataRoot!1!!xml]
> from XMLTable
> for xml explicit
> I get this:
> <XMLDataRoot>
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> </XMLDataRoot>
> <XMLDataRoot>
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> </XMLDataRoot>
> ...
> This also results in error as soon as my app tries to parse it: "The XML
> Declaration is Unexpected"
> I can't strip the xml declaration out using string functions, as the xml
> is stored in a text field. For another stored procedure I want to
> return just a single record, and the xml declaration is desired for
> that.
> How can I return the xml without the xml declarations fouling up my
> results?
> thanks
> -ivan.
sql

retrieving XML issues - SQL 2000

In my SQL Server 2000 database, I need to return several records
containing xml as a single xml.
This almost gives me what I want:
select
1 tag,
null parent,
XMLData [XMLDataRoot!1!!xmltext]
from XMLTable
for xml explicit
The trouble is that each record includes an xml declaration tag:
<?xml version="1.0" encoding="UTF-8"?>
So, a sample record might look something like:
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
And I want this returned:
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
...
Instead I'm getting this:
<XMLDataRoot version="1.0" encoding="UTF-8">
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
<XMLDataRoot version="1.0" encoding="UTF-8">
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
...
This, of course, is not well-formed xml and results in error as soon as
my app tries to parse it. (I also tried changing the nodename in my
query to differ from the root node name in the record, but still, it
returns not well-formed xml)
If I change my query to use the xml directive instead of xmltext like
this:
select
1 tag,
null parent,
XMLData [XMLDataRoot!1!!xml]
from XMLTable
for xml explicit
I get this:
<XMLDataRoot>
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
</XMLDataRoot>
<XMLDataRoot>
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
</XMLDataRoot>
...
This also results in error as soon as my app tries to parse it: "The XML
Declaration is Unexpected"
I can't strip the xml declaration out using string functions, as the xml
is stored in a text field. For another stored procedure I want to
return just a single record, and the xml declaration is desired for
that.
How can I return the xml without the xml declarations fouling up my
results?
thanks
-ivan.Two options:
Use string functions to drop the XML declarations (may need more complex
coding because of TEXT column).
Use SQL Server 2005 and cast/alter the column to an XML data type (you still
may have some issues with the UTF-8 encoding depending on your column code
page, then first cast it to varbinary(max) before casting it to XML).
Best regards
Michael
"gilly3" <news@.NOSPAMgilly3.com> wrote in message
news:Xns97637C2EC4F26newsNOSPAMgilly3com
@.207.46.248.16...
> In my SQL Server 2000 database, I need to return several records
> containing xml as a single xml.
> This almost gives me what I want:
> select
> 1 tag,
> null parent,
> XMLData [XMLDataRoot!1!!xmltext]
> from XMLTable
> for xml explicit
> The trouble is that each record includes an xml declaration tag:
> <?xml version="1.0" encoding="UTF-8"?>
> So, a sample record might look something like:
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> And I want this returned:
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> ...
> Instead I'm getting this:
> <XMLDataRoot version="1.0" encoding="UTF-8">
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> <XMLDataRoot version="1.0" encoding="UTF-8">
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> ...
> This, of course, is not well-formed xml and results in error as soon as
> my app tries to parse it. (I also tried changing the nodename in my
> query to differ from the root node name in the record, but still, it
> returns not well-formed xml)
> If I change my query to use the xml directive instead of xmltext like
> this:
> select
> 1 tag,
> null parent,
> XMLData [XMLDataRoot!1!!xml]
> from XMLTable
> for xml explicit
> I get this:
> <XMLDataRoot>
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> </XMLDataRoot>
> <XMLDataRoot>
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> </XMLDataRoot>
> ...
> This also results in error as soon as my app tries to parse it: "The XML
> Declaration is Unexpected"
> I can't strip the xml declaration out using string functions, as the xml
> is stored in a text field. For another stored procedure I want to
> return just a single record, and the xml declaration is desired for
> that.
> How can I return the xml without the xml declarations fouling up my
> results?
> thanks
> -ivan.

retrieving selected join records

Hi,
I have the folowing 3 (SS2005) tables:

CREATE TABLE [dbo].[tblSubscription](
[SubscriptionID] [int] IDENTITY(1000000,1) NOT NULL,
[SubscriberID] [int] NOT NULL,
[Status] [int] NOT NULL,
[JournalID] [int] NOT NULL,

CREATE TABLE [dbo].[tblTransaction](
[TransactionID] [bigint] IDENTITY(100000000,1) NOT NULL,
[TransactionTypeID] [int] NOT NULL,
[SubscriptionID] [int] NOT NULL,
[Created] [datetime] NOT NULL,

CREATE TABLE [dbo].[tblMailing](
[MialingID] [bigint] IDENTITY(1000000000,1) NOT NULL,
[SubscriptionID] [int] NOT NULL,
[MailTypeID] [int] NOT NULL,
[MailDate] [datetime] NOT NULL

So for each subscription there can be 1 or more transactions and 0 or
more mailings, and the mailings are not necassarily related to the
transactions. What I am having difficulty doing is this:

I wish to select tblMailing.MailingID, tblMailing.MailDate,
tblMailing.SubscriptionID (or tblSubscription.SubscriptionID),
tblSubscription.SubscriberID, tblSubscription.Status,
tblTransaction.TransactionID, tblTransaction.Created, but I only wish
to retrieve rows from the transaction table where
tblTransaction.Created is the latest dated transaction for that
subscription.
I.E. (maybe this makes more sense..:) I wish to select all rows from
tblMailing along with each mailing's relevent subscription details,
including details of the LATEST TRANSACTION for each of those
subscriptions.

I am currently working along the lines of MAX(tblTransaction.Created)
and possibly GROUP BY in a subquery, but cannot quite figure out the
logic.

Any help appreciated.

Thanks, KoG

King:

Are you wanting the subscription record to appear in the report even if there are as of yet no mailings? That is, do I need to use an outer join or an inner join? I am for the moment assuming that you want the inner join.

Dave

|||

set nocount on
declare @.tblSubscription table
( subscriptionID integer not null,
subscriberID integer not null,
status integer not null,
journalID integer not null,
primary key (subscriptionID)
)

declare @.tblTransaction table
( transactionID integer not null,
transactionTypeId integer not null,
subscriptionID integer not null,
created datetime not null
primary key (transactionID),
unique (subscriptionID, transactionID)
)

declare @.tblMailing table
( mailingId bigint not null,
subscriptionID integer not null,
mailTypeId integer not null,
mailDate datetime not null,
primary key (mailingID),
unique (subscriptionId, mailingId)
)

insert into @.tblSubscription values (1000001, 1000001, 1, 1)
insert into @.tblSubscription values (1000002, 1000002, 1, 1)
insert into @.tblSubscription values (1000003, 1000001, 2, 1)
--select * from @.tblSubscription

insert into @.tblTransaction values (1000001, 1, 1000001, '3/15/6' )
insert into @.tblTransaction values (1000002, 2, 1000001, '4/7/6' )
insert into @.tblTransaction values (1000003, 1, 1000002, '4/3/6' )
insert into @.tblTransaction values (1000004, 1, 1000003, '5/8/6' )
insert into @.tblTransaction values (1000005, 2, 1000003, '10/14/6')
insert into @.tblTransaction values (1000006, 4, 1000003, '9/1/6' )
--select * from @.tblTransaction

insert into @.tblMailing values (1000001, 1000001, 1, '3/15/6' )
insert into @.tblMailing values (1000002, 1000001, 2, '4/4/6' )
insert into @.tblMailing values (1000003, 1000003, 1, '5/9/6' )
insert into @.tblMailing values (1000004, 1000003, 3, '9/3/6' )
--select * from @.tblMailing

--set statistics io on
select m.mailingId,
m.MailDate,
s.subscriptionId,
s.subscriberId,
s.Status,
t.TransactionId,
t.created
from @.tblSubscription s
inner join @.tblMailing m
on s.subscriptionId = m.subscriptionId
inner join
( select q.subscriptionId,
q.transactionId,
row_number () over
( partition by q.subscriptionId
order by q.created desc, q.transactionId desc
) as Seq,
created
from @.tblTransaction q
) t
on t.subscriptionId = s.subscriptionId
and seq = 1
--set statistics io off

-- -- Sample Output: -

-- mailingId MailDate subscriptionId subscriberId Status TransactionId created
-- -- -- -- -- - --
-- 1000001 2006-03-15 00:00:00.000 1000001 1000001 1 1000002 2006-04-07 00:00:00.000
-- 1000002 2006-04-04 00:00:00.000 1000001 1000001 1 1000002 2006-04-07 00:00:00.000
-- 1000003 2006-05-09 00:00:00.000 1000003 1000001 2 1000005 2006-10-14 00:00:00.000
-- 1000004 2006-09-03 00:00:00.000 1000003 1000001 2 1000005 2006-10-14 00:00:00.000

|||Hi Dave,

I only wish to select subscription rows where there is a mailing associated with the subscription. In fact, the driver of the query should be the mailings table, so for each row in tblMailing get the relevent subscription (& latest transaction) data. That means there may be several rows where the data in the subscription-related columns (& hence transaction related ones too) are the same, as a subscription may have several mailings.

I assume that means the inner join is required..

Thanks, Nick
sql

Retrieving rows with 2 indexes

I have a table that contains 2 keys (iId and iVersion) and some other data:

iId int(4) NOT NULL default '0',
iVersion int(4) NOT NULL default '0',
vchName varchar(50) default NULL

The data looks a little like this:

1|1|Fred
1|2|Fred edited once
1|3|Fred edited twice
2|1|Dave
2|2|Dave edited once
2|3|Dave edited twice

I need a sql statement that will return all columns of the latest row (based on the greatest iVersion value) for each unique iId in the table.

So using the data above it should return

1|3|Fred edited twice
2|3|Dave edited twice

Any help would be appreciated!select iId,
iVersion,
vchName
from <table_name>
group by iId
having iVersion = max(iVersion)|||Try:
select iId,
iVersion,
vchName
from <table_name>
where (iId, iVersion) in
( select iId,
max(iVersion) maxVer
from <table_name>
group by iId
);
Or if using Oracle you could use the analytic functions.|||I would suggest using:SELECT *
FROM myTable AS a
WHERE a.iVersion = (SELECT Max(b.iVersion)
FROM myTable AS b
WHERE b.iId = a.iId)-PatP|||May have been that i was using MySQL but none of the above worked!

They did however help me to get some sql that does work...

select iId, vchName, MAX(iVersion) as iVersion
from <Table>
GROUP BY iId

Thanks.|||select iId, vchName, MAX(iVersion) as iVersion
from <Table>
GROUP BY iIdyou may think this works, but it doesn't

even the mysql docs tell you that this gives unpredictable results (holler if you need the link to the page in the docs where it explains this)

what you want is the vchName that comes from the row which has the largest iVersion, but this is not what you are getting, and if it looks like you are getting it, it is a fluke

you could just as easily get this instead --

1|3|Fred edited once
2|3|Dave

i'm sorry if this sounds like i'm dumping all over you, it's not your fault, it's mysql's fault for allowing non-standard sql to run (in any other database system, your query would generate a syntax error)

here's what you want, done without subqueries if you're not on 4.1 yet --
select X.iId
, X.iVersion
, X.vchName
from yourtable as X
inner
join yourtable as Y
on X.iId
= Y.iId
group
by X.iId
, X.iVersion
, X.vchName
having X.iVersion
= max(Y.iVersion)

Retrieving an image from SQL, test for null

I have an employee directory application that displays employees in a gridview. When a record is selected, a new page opens and displays all info about the employee, including their photo. I have the code working that displays the photos, however, when no photo is present an exception is thrown that "Unable to cast object of type System.DbNull to System.Byte[]". I'm not sure how to test for no photo before trying to write it out.

My code is as follows (with no error trapping):

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load,Me.Load

Dim tempAsString

Dim connPhotoAs System.Data.SqlClient.SqlConnection

Dim connstringAsString

connstring = Web.Configuration.WebConfigurationManager.ConnectionStrings("connPhoto").ConnectionString

connPhoto =New System.Data.SqlClient.SqlConnection(connstring)

temp = Request.QueryString("id")

Dim SqlSelectCommand2As System.Data.SqlClient.SqlCommand

Dim sqlstringAsString

sqlstring ="Select * from dbo.PhotoDir WHERE (CMS_ID = " + temp +")"

SqlSelectCommand2 =New System.Data.SqlClient.SqlCommand(sqlstring, connPhoto)

Try

connPhoto.Open()

Dim myDataReaderAs System.Data.SqlClient.SqlDataReader

myDataReader = SqlSelectCommand2.ExecuteReader

DoWhile (myDataReader.Read())

Response.BinaryWrite(myDataReader.Item("ImportedPhoto"))

Loop

connPhoto.Close()

Catch SQLexecAs System.Data.SqlClient.SqlException

Response.Write("Read Failed : " & SQLexec.ToString())

EndTry

EndSub

EndClass

If you could point me in the right direction I would appreciate it.

lwhalen618:

when no photo is present an exception isthrown that "Unable to cast object of type System.DbNull toSystem.Byte[]

lwhalen618:

DoWhile (myDataReader.Read())

Response.BinaryWrite(myDataReader.Item("ImportedPhoto"))

Loop

did you try to check for nulls ??

DoWhile (myDataReader.Read())
if Not IsDBNull(myDataReader.Item("ImportedPhoto")) then
Response.BinaryWrite(myDataReader.Item("ImportedPhoto"))
End if
Loop

hope it works... pls let me know

Good Luck./.

|||

I did try testing for null but was doing it incorrectly. Your code worked fine. Thanks!

Retrieving all Child and Grandchild and Great Grandchild etc Nodes

Given this table:
CREATE TABLE Nodes (
[NodeID] [int] IDENTITY (1, 1) NOT NULL ,
[NodeName] [varchar] (50) NOT NULL ,
[ParentNodeID] [int] NULL ,
[SequenceUnderParent] [int] NULL
)
I would like to have one SELECT statement, if possible, that returns [all of
the descendent nodes] of a given node -- i.e., all of the given node's child
nodes AND all of their child nodes {grand child nodes}, etc... down to 4
possible "generations".
NOTE: It will not be possible (per "business rules" and of course the table
structure, obviously) for any node to have more than one parent node.
If not in one SELECT statement, then how can I accomplish this?
Thanks!See another post few poasts before
Bill of material (SQL2000)
"Jordan S." <A@.B.COM> wrote in message
news:%23$fKXzkXGHA.4212@.TK2MSFTNGP02.phx.gbl...
> Given this table:
> CREATE TABLE Nodes (
> [NodeID] [int] IDENTITY (1, 1) NOT NULL ,
> [NodeName] [varchar] (50) NOT NULL ,
> [ParentNodeID] [int] NULL ,
> [SequenceUnderParent] [int] NULL
> )
> I would like to have one SELECT statement, if possible, that returns [all
> of the descendent nodes] of a given node -- i.e., all of the given node's
> child nodes AND all of their child nodes {grand child nodes}, etc... down
> to 4 possible "generations".
> NOTE: It will not be possible (per "business rules" and of course the
> table structure, obviously) for any node to have more than one parent
> node.
> If not in one SELECT statement, then how can I accomplish this?
> Thanks!
>|||There are many ways to represent a tree or hierarchy in SQL. This is
called an adjacency list model and it looks like this:
CREATE TABLE OrgChart
(emp CHAR(10) NOT NULL PRIMARY KEY,
boss CHAR(10) DEFAULT NULL REFERENCES OrgChart(emp),
salary DECIMAL(6,2) NOT NULL DEFAULT 100.00);
OrgChart
emp boss salary
===========================
'Albert' NULL 1000.00
'Bert' 'Albert' 900.00
'Chuck' 'Albert' 900.00
'Donna' 'Chuck' 800.00
'Eddie' 'Chuck' 700.00
'Fred' 'Chuck' 600.00
Another way of representing trees is to show them as nested sets.
Since SQL is a set oriented language, this is a better model than the
usual adjacency list approach you see in most text books. Let us define
a simple OrgChart table like this.
CREATE TABLE OrgChart
(emp CHAR(10) NOT NULL PRIMARY KEY,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt) );
OrgChart
emp lft rgt
======================
'Albert' 1 12
'Bert' 2 3
'Chuck' 4 11
'Donna' 5 6
'Eddie' 7 8
'Fred' 9 10
The organizational chart would look like this as a directed graph:
Albert (1, 12)
/ \
/ \
Bert (2, 3) Chuck (4, 11)
/ | \
/ | \
/ | \
/ | \
Donna (5, 6) Eddie (7, 8) Fred (9, 10)
The adjacency list table is denormalized in several ways. We are
modeling both the Personnel and the organizational chart in one table.
But for the sake of saving space, pretend that the names are job titles
and that we have another table which describes the Personnel that hold
those positions.
Another problem with the adjacency list model is that the boss and
employee columns are the same kind of thing (i.e. names of personnel),
and therefore should be shown in only one column in a normalized table.
To prove that this is not normalized, assume that "Chuck" changes his
name to "Charles"; you have to change his name in both columns and
several places. The defining characteristic of a normalized table is
that you have one fact, one place, one time.
The final problem is that the adjacency list model does not model
subordination. Authority flows downhill in a hierarchy, but If I fire
Chuck, I disconnect all of his subordinates from Albert. There are
situations (i.e. water pipes) where this is true, but that is not the
expected situation in this case.
To show a tree as nested sets, replace the nodes with ovals, and then
nest subordinate ovals inside each other. The root will be the largest
oval and will contain every other node. The leaf nodes will be the
innermost ovals with nothing else inside them and the nesting will show
the hierarchical relationship. The (lft, rgt) columns (I cannot use the
reserved words LEFT and RIGHT in SQL) are what show the nesting. This
is like XML, HTML or parentheses.
At this point, the boss column is both redundant and denormalized, so
it can be dropped. Also, note that the tree structure can be kept in
one table and all the information about a node can be put in a second
table and they can be joined on employee number for queries.
To convert the graph into a nested sets model think of a little worm
crawling along the tree. The worm starts at the top, the root, makes a
complete trip around the tree. When he comes to a node, he puts a
number in the cell on the side that he is visiting and increments his
counter. Each node will get two numbers, one of the right side and one
for the left. Computer Science majors will recognize this as a modified
preorder tree traversal algorithm. Finally, drop the unneeded
OrgChart.boss column which used to represent the edges of a graph.
This has some predictable results that we can use for building queries.
The root is always (left = 1, right = 2 * (SELECT COUNT(*) FROM
TreeTable)); leaf nodes always have (left + 1 = right); subtrees are
defined by the BETWEEN predicate; etc. Here are two common queries
which can be used to build others:
1. An employee and all their Supervisors, no matter how deep the tree.
SELECT O2.*
FROM OrgChart AS O1, OrgChart AS O2
WHERE O1.lft BETWEEN O2.lft AND O2.rgt
AND O1.emp = :myemployee;
2. The employee and all their subordinates. There is a nice symmetry
here.
SELECT O1.*
FROM OrgChart AS O1, OrgChart AS O2
WHERE O1.lft BETWEEN O2.lft AND O2.rgt
AND O2.emp = :myemployee;
3. Add a GROUP BY and aggregate functions to these basic queries and
you have hierarchical reports. For example, the total salaries which
each employee controls:
SELECT O2.emp, SUM(S1.salary)
FROM OrgChart AS O1, OrgChart AS O2,
Salaries AS S1
WHERE O1.lft BETWEEN O2.lft AND O2.rgt
AND O1.emp = S1.emp
GROUP BY O2.emp;
4. To find the level of each emp, so you can print the tree as an
indented listing. Technically, you should declare a cursor to go with
the ORDER BY clause.
SELECT COUNT(O2.emp) AS indentation, O1.emp
FROM OrgChart AS O1, OrgChart AS O2
WHERE O1.lft BETWEEN O2.lft AND O2.rgt
GROUP BY O1.lft, O1.emp
ORDER BY O1.lft;
5. The nested set model has an implied ordering of siblings which the
adjacency list model does not. To insert a new node, G1, under part G.
We can insert one node at a time like this:
BEGIN ATOMIC
DECLARE rightmost_spread INTEGER;
SET rightmost_spread
= (SELECT rgt
FROM Frammis
WHERE part = 'G');
UPDATE Frammis
SET lft = CASE WHEN lft > rightmost_spread
THEN lft + 2
ELSE lft END,
rgt = CASE WHEN rgt >= rightmost_spread
THEN rgt + 2
ELSE rgt END
WHERE rgt >= rightmost_spread;
INSERT INTO Frammis (part, lft, rgt)
VALUES ('G1', rightmost_spread, (rightmost_spread + 1));
COMMIT WORK;
END;
The idea is to spread the (lft, rgt) numbers after the youngest child
of the parent, G in this case, over by two to make room for the new
addition, G1. This procedure will add the new node to the rightmost
child position, which helps to preserve the idea of an age order among
the siblings.
6. To convert a nested sets model into an adjacency list model:
SELECT B.emp AS boss, E.emp
FROM OrgChart AS E
LEFT OUTER JOIN
OrgChart AS B
ON B.lft
= (SELECT MAX(lft)
FROM OrgChart AS S
WHERE E.lft > S.lft
AND E.lft < S.rgt);
7. To convert an adjacency list to a nested set model, use a push down
stack. Here is version with a stack in SQL/PSM.
-- Tree holds the adjacency model
CREATE TABLE Tree
(node CHAR(10) NOT NULL,
parent CHAR(10));
-- Stack starts empty, will holds the nested set model
CREATE TABLE Stack
(stack_top INTEGER NOT NULL,
node CHAR(10) NOT NULL,
lft INTEGER,
rgt INTEGER);
CREATE PROCEDURE TreeTraversal ()
LANGUAGE SQL
DETERMINISTIC
BEGIN ATOMIC
DECLARE counter INTEGER;
DECLARE max_counter INTEGER;
DECLARE current_top INTEGER;
SET counter = 2;
SET max_counter = 2 * (SELECT COUNT(*) FROM Tree);
SET current_top = 1;
--clear the stack
DELETE FROM Stack;
-- push the root
INSERT INTO Stack
SELECT 1, node, 1, max_counter
FROM Tree
WHERE parent IS NULL;
-- delete rows from tree as they are used
DELETE FROM Tree WHERE parent IS NULL;
WHILE counter <= max_counter- 1
DO IF EXISTS (SELECT *
FROM Stack AS S1, Tree AS T1
WHERE S1.node = T1.parent
AND S1.stack_top = current_top)
THEN BEGIN -- push when top has subordinates and set lft value
INSERT INTO Stack
SELECT (current_top + 1), MIN(T1.node), counter, NULL
FROM Stack AS S1, Tree AS T1
WHERE S1.node = T1.parent
AND S1.stack_top = current_top;
-- delete rows from tree as they are used
DELETE FROM Tree
WHERE node = (SELECT node
FROM Stack
WHERE stack_top = current_top + 1);
-- housekeeping of stack pointers and counter
SET counter = counter + 1;
SET current_top = current_top + 1;
END;
ELSE
BEGIN -- pop the stack and set rgt value
UPDATE Stack
SET rgt = counter,
stack_top = -stack_top -- pops the stack
WHERE stack_top = current_top;
SET counter = counter + 1;
SET current_top = current_top - 1;
END;
END IF;
END WHILE;
-- SELECT node, lft, rgt FROM Stack;
-- the top column is not needed in the final answer
-- move stack contents to new tree table
END;
I have a book on TREES & HIERARCHIES IN SQL which you can get at
Amazon.com right now.

Retrieve Identity on insert: what if table has 2 identity columns

I have a table with 2 identity columns:
ID int identity(200100,1) not null,
OrderNo int Identity(550000,1) not null,
OrderName varchar(60) not null,
etc.
How do I get the values set by SQL server for ID and OrderNo when I insert
a new row in the table ?
Thanks in advance,Eve
If you are sitting on SQL Server 2000 then perform after inserting
SELECT SCOPE_IDENTITY()
Otherwise SELECT @.@.IDENTITY
"Eve" <Eve@.discussions.microsoft.com> wrote in message
news:F1824CDF-BC5D-4E89-98DA-6364A036B577@.microsoft.com...
> I have a table with 2 identity columns:
> ID int identity(200100,1) not null,
> OrderNo int Identity(550000,1) not null,
> OrderName varchar(60) not null,
> etc.
> How do I get the values set by SQL server for ID and OrderNo when I
insert
> a new row in the table ?
> Thanks in advance,
>|||It seems that there is a relationship between the two numbers.Seeing as you
can't have two identity columns. Why don't you use a formula for the ordern
o
Alter table <table> add OrderNo As ID + 300000.
"Eve" wrote:

> I have a table with 2 identity columns:
> ID int identity(200100,1) not null,
> OrderNo int Identity(550000,1) not null,
> OrderName varchar(60) not null,
> etc.
> How do I get the values set by SQL server for ID and OrderNo when I inser
t
> a new row in the table ?
> Thanks in advance,
>|||And on another note. I can't see why you need and ID since the OrderNo is
already unique...
Why is this ?
"Eve" wrote:

> I have a table with 2 identity columns:
> ID int identity(200100,1) not null,
> OrderNo int Identity(550000,1) not null,
> OrderName varchar(60) not null,
> etc.
> How do I get the values set by SQL server for ID and OrderNo when I inser
t
> a new row in the table ?
> Thanks in advance,
>|||I think you are mistaken. Only one IDENTITY column per table is permitted.
Please post the CREATE TABLE statement for your table so that we can
understand what you mean.
In SQL Server 2000 use SCOPE_IDENTITY() to retrieve the last inserted
IDENTITY value.
David Portas
SQL Server MVP
--