DISTINCT COLOUR, BUT MUST HAVE PRIM KEY

So how do i solve this sql statment

Select distinct colour from wwwstocktbl where colour <> ' ' order by colour

The stament works as a statment but when run in asp i get an error as theres no primary key, but if i add the primary key then the select distinct fails

Select distinct ProductCode, Colour from wwwstocktbl where colour <> ' ' order by colourThe code as written should work correctly. Are you trying to use this code in a data adapter? We are going to need to see some of hte asp.net code in order to be able to help you.

Terri|||SqlSelectCommand4.CommandText = "select DISTINCT COLOUR from wwwstocktbl order by COLOUR"
SqlDataAdapter4.Fill(DataSet21, "wwwstocktbl")
DropDownListMaxPrice = DataBinder.Eval(DataSet21, "Tables[wwwStockTBL].DefaultView.[0].COLOUR")
DropDownListMinPrice.DataBind()

the problem is that to achive the distinct colour you must exclude the ProductCode field, but doing that generates an error as there is no primary key

<<<<---code copied from error page->>>>>>>
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Source Error:

Line 231:
Line 232: SqlSelectCommand4.CommandText = "select DISTINCT COLOUR from wwwstocktbl order by COLOUR"
Line 233: SqlDataAdapter4.Fill(DataSet21, "wwwstocktbl")
Line 234: DropDownListColour = DataBinder.Eval(DataSet21, "Tables(wwwStockTBL).DefaultView.(0).COLOUR")
Line 235: DropDownListColour.DataBind()

<<<<---end code copied from error page->>>>>>
chnaging the line
SqlSelectCommand4.CommandText = "select DISTINCT ProductCode, COLOUR from wwwstocktbl order by COLOUR"

would give a very differant set of results

???? do you think there are two problems here
1 the error being generated and
2 the code needed for the distinct cluse?|||Well, think about it. If you have BLUE in the table 5 times with 5 different codes, and the user chooses BLUE from the dropdownlist, WHICH BLUE is the database supposed to be using?

If you don't particularly care which BLUE your query chooses, then you can use a query like this:

 SELECT MAX(ProductCode) AS ProductCode, Colour FROM wwwstocktbl GROUP BY Colour ORDER BY Colour

This will give you a resultset of unique colours along with the MAX(ProductCode) for each.

Terri|||excelant, that code was realy interesting

after you asked about was i useing a datset i woundered why, so i created a dataset and dataadapter just for the colour question and it worked, but why, in my first sql code tests i selected all the fields, then in the finnal run just the colour and got the error, i then tryed to "dim" a new dataadapter and dataset at run time but with no sucsess, is there some rule about being able to change the fields returned by say a second sql statment such as at design time i define 3 fields but then at run time only request 2?

Dist. Partitioned Views

I've read that the partitioning column must be part or
all of the primary key.
Is this design advice or a requirement? If it's a
requirement, why?
Any and all help appreciated,
Thanks,
Andrewit is a requirement, so that sql server will know to which
server the new row belongs. This is just to avoid
confilict and overlapping of the data on multiple servers.
>--Original Message--
>I've read that the partitioning column must be part or
>all of the primary key.
>Is this design advice or a requirement? If it's a
>requirement, why?
>Any and all help appreciated,
>Thanks,
>Andrew
>.
>sql

Displaying Print Debugging from Stored Procedure immediately

I am attempting to debug a very long stored procedure, for this I inserted many print statements at key points on the procedure, but I find that once I do an exec sp_name I only get the print statements show in the message tab only after the full execution or cancellation of the stored procedure.

Is there any way to force the message tab to display the messages mid execution?

One way is to use the raiserror command with a severity of 10 and a "with nowait" option. This will not cause an actual error to be raised, as you can see by running this code:

begin try

raiserror('Progress message', 10, 1) with nowait

end try

begin catch

print 'here'

end catch

Ron Rice

displaying non-duplicate keys of all duplicate entries

Ok, so I'm checking for duplicate data in a table. Let's say it has 3 data fields and a key field (i.e. "ID", "FIRST", "MIDDLE", "LAST"). No keys are duplicated. If I find entries that have the same data in each of the non-key fields, I want to know the keys for all those entries. I have been able to find duplicate rows using this...

SELECT
TABLE."FIRST", TABLE."MIDDLE", TABLE."LAST"
FROM
TABLE
GROUP BY
TABLE."FIRST", TABLE."MIDDLE", TABLE."LAST"
HAVING
COUNT(*) > 1

Unfortunately I've found no way to incorporate the return of the TABLE."ID" for every duplicated entry. Is there some way I can join the result with the db.table to find this, or some other way to make this happen?

Thanks,
DeanSure, use:SELECT
A."ID", A."FIRST", A."MIDDLE", A."LAST"
FROM TABLE AS A
WHERE 1 < (SELECT Count(*)
FROM TABLE AS B
WHERE B."FIRST" = A."FIRST"
AND B."LAST" = A."LAST"
AND B."MIDDLE" = A."MIDDLE")-PatP|||Thanks, that worked well, although it takes a good while for the server to process the query.|||Indicies would help this query a lot, particularly an index on last, first, middle.

-PatP

Displaying member name instead of key in query

In the tools that I have used for getting generated MDX queries (Query Analyzer, Proclarity, Excel) the query always seems to show the key value instead of the member name.

Is it possible or is there a query tool that will generate queries with the member name instead?

See example:

[Organization].&[152551]

Should read

[Organization].[By Division]

thanks,

Andrew

Set "MemberNamesUnique" to True for a particular attribute. That should do it|||

Thanks! Didn't even have to reprocess!

cheers,

Andrew

Display Rowid in Select of a view with double values

Hi there,
I have the following problem. I would like to add an key to my view in the
form of a rowid. My view displays values that can come more then once.
In Oracle there is one database kolumn called RowId what can be used in any
select statements. Is there also something in SQL Server 2000?
Or what can I do to get the rownumbers? There are no keys in my view and the
values can be come more then once (so nothing is unique.. that's why I need a
nice autonumber number)
Alex
SQL Server 2005 has the same function row_number if I remember well
In SQL Server 2000 you can try
SELECT OrderId,(SELECT COUNT(*) FROM Orders O WHERE
O.OrderId<=Orders.Orderid) AS rnk
FROM Orders ORDER BY rnk ASC
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)
|||There is no such thing as ROWID in SQL 2000.
Also, if a row cannot be uniquely identified then the database is not
correctly normalized. i.e. It is not in 2NF so you are going to find it
difficult to use SQL (which relies on good normalization) to provide a
solution.
Nik Marshall-Blank MCSD/MCDBA
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)
|||Thnks all,
Yes cant do a unique identified the rows.. So I can do use with a temp table
and use an identify column.
"Nik Marshall-Blank (delete fcom for my e" wrote:

> There is no such thing as ROWID in SQL 2000.
> Also, if a row cannot be uniquely identified then the database is not
> correctly normalized. i.e. It is not in 2NF so you are going to find it
> difficult to use SQL (which relies on good normalization) to provide a
> solution.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Alex." <Alex@.discussions.microsoft.com> wrote in message
> news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
>
>

Display Rowid in Select of a view with double values

Hi there,
I have the following problem. I would like to add an key to my view in the
form of a rowid. My view displays values that can come more then once.
In Oracle there is one database kolumn called RowId what can be used in any
select statements. Is there also something in SQL Server 2000?
Or what can I do to get the rownumbers? There are no keys in my view and the
values can be come more then once (so nothing is unique.. that's why I need a
nice autonumber number)Alex
SQL Server 2005 has the same function row_number if I remember well
In SQL Server 2000 you can try
SELECT OrderId,(SELECT COUNT(*) FROM Orders O WHERE
O.OrderId<=Orders.Orderid) AS rnk
FROM Orders ORDER BY rnk ASC
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)|||There is no such thing as ROWID in SQL 2000.
Also, if a row cannot be uniquely identified then the database is not
correctly normalized. i.e. It is not in 2NF so you are going to find it
difficult to use SQL (which relies on good normalization) to provide a
solution.
--
Nik Marshall-Blank MCSD/MCDBA
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)|||Thnks all,
Yes cant do a unique identified the rows.. So I can do use with a temp table
and use an identify column.
"Nik Marshall-Blank (delete fcom for my e" wrote:
> There is no such thing as ROWID in SQL 2000.
> Also, if a row cannot be uniquely identified then the database is not
> correctly normalized. i.e. It is not in 2NF so you are going to find it
> difficult to use SQL (which relies on good normalization) to provide a
> solution.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Alex." <Alex@.discussions.microsoft.com> wrote in message
> news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> > Hi there,
> >
> > I have the following problem. I would like to add an key to my view in the
> > form of a rowid. My view displays values that can come more then once.
> >
> > In Oracle there is one database kolumn called RowId what can be used in
> > any
> > select statements. Is there also something in SQL Server 2000?
> >
> > Or what can I do to get the rownumbers? There are no keys in my view and
> > the
> > values can be come more then once (so nothing is unique.. that's why I
> > need a
> > nice autonumber number)
>
>

Display Rowid in Select of a view with double values

Hi there,
I have the following problem. I would like to add an key to my view in the
form of a rowid. My view displays values that can come more then once.
In Oracle there is one database kolumn called RowId what can be used in any
select statements. Is there also something in SQL Server 2000?
Or what can I do to get the rownumbers? There are no keys in my view and the
values can be come more then once (so nothing is unique.. that's why I need
a
nice autonumber number)Alex
SQL Server 2005 has the same function row_number if I remember well
In SQL Server 2000 you can try
SELECT OrderId,(SELECT COUNT(*) FROM Orders O WHERE
O.OrderId<=Orders.Orderid) AS rnk
FROM Orders ORDER BY rnk ASC
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)|||There is no such thing as ROWID in SQL 2000.
Also, if a row cannot be uniquely identified then the database is not
correctly normalized. i.e. It is not in 2NF so you are going to find it
difficult to use SQL (which relies on good normalization) to provide a
solution.
--
Nik Marshall-Blank MCSD/MCDBA
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)|||Thnks all,
Yes cant do a unique identified the rows.. So I can do use with a temp table
and use an identify column.
"Nik Marshall-Blank (delete fcom for my e" wrote:

> There is no such thing as ROWID in SQL 2000.
> Also, if a row cannot be uniquely identified then the database is not
> correctly normalized. i.e. It is not in 2NF so you are going to find it
> difficult to use SQL (which relies on good normalization) to provide a
> solution.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Alex." <Alex@.discussions.microsoft.com> wrote in message
> news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
>
>