a Distinct Query

I have 2 tables as following :

tbl_Articles
3 ArticleID int
0 AuthorID int
0 ArticleTitle
0 ArticleText
0 ArticleDate

tbl_Authors
3 AuthorID
0 AuthorFullName
0 AuthorEmail
0 AuthorDescription
0 AuthorImage

I want to write a query to see the Authors and their last articles with no distinct values.
Like AuthorImage - AuthorFullName - ArticleTitle - ArticleDate

If anyone knows the solution i will be glad .
Thanks from now onselect AuthorImage, AuthorFullName, ArticleTitle, ArticleDate = aDate
from tbl_Authors a
inner join (
select AuthorID, aDate = max(ArticleDate)
from tbl_Articles) x
on a.AuthorID = x.AuthorID
inner join tbl_Articles b
on x.AuthorID = b.AuthorID
and x.aDate = b.ArticleDate|||another version:select AuthorImage
, AuthorFullName
, ArticleTitle
, ArticleDate
from tbl_Authors AUTH
inner
join tbl_Articles ART
on AUTH.AuthorID
= ART.AuthorID
where ART.ArticleDate
= ( select max(ArticleDate)
from tbl_Articles
where AuthorID
= AUTH.AuthorID )

501 when connect to Endpoint

I Created SP and endpoint that exposes SP as web service :

CREATE PROCEDURE TimeServer.ResponseTime2
(
@.TimeType int,
)
AS
IF (@.TimeType = 0 )
SELECT @.Result AS CZAS
ELSE
SELECT @.Result AS CZAS
GO
CREATE ENDPOINT MyWebService
STATE = STARTED
AS HTTP
(
PATH = '/AdventureWorks/MyWebService',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
SITE = 'localhost'
)
FOR SOAP
(
WEBMETHOD 'GetTime' (Name = 'AdventureWorks.TimeServer.ResponseTime2', FORMAT = ROWSETS_ONLY),
DATABASE = 'AdventureWorks'
)

GO

When I try to connect to webservice (localhost/AdventureWorks/MyWebServic
I get 501 error - Not implemented or not supported.

Any ideas why ?

I don't know if this answers your question. However, we support HTTP GET requests ONLY for requesting WSDL. In this case if you submitted the following request

http://localhost/AdventureWorks/MyWebService?wsdl

it should return WSDL describing the endpoint.

The rest of the SOAP requests have to be submitted via HTTP POST.

Thanks
Srik

|||

I was also getting this at one point. Try going into IE browser->Tools->Internet Options->Advanced and enable "Use HTTP 1.1 through proxy connections" if this is not set (may be related to proxy settings associated wih IE Browser->Connections->LAN Settings)

501 when connect to Endpoint

I Created SP and endpoint that exposes SP as web service :

CREATE PROCEDURE TimeServer.ResponseTime2
(
@.TimeType int,
)
AS
IF (@.TimeType = 0 )
SELECT @.Result AS CZAS
ELSE
SELECT @.Result AS CZAS
GO
CREATE ENDPOINT MyWebService
STATE = STARTED
AS HTTP
(
PATH = '/AdventureWorks/MyWebService',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
SITE = 'localhost'
)
FOR SOAP
(
WEBMETHOD 'GetTime' (Name = 'AdventureWorks.TimeServer.ResponseTime2', FORMAT = ROWSETS_ONLY),
DATABASE = 'AdventureWorks'
)

GO

When I try to connect to webservice (localhost/AdventureWorks/MyWebServic
I get 501 error - Not implemented or not supported.

Any ideas why ?

I don't know if this answers your question. However, we support HTTP GET requests ONLY for requesting WSDL. In this case if you submitted the following request

http://localhost/AdventureWorks/MyWebService?wsdl

it should return WSDL describing the endpoint.

The rest of the SOAP requests have to be submitted via HTTP POST.

Thanks
Srik

|||

I was also getting this at one point. Try going into IE browser->Tools->Internet Options->Advanced and enable "Use HTTP 1.1 through proxy connections" if this is not set (may be related to proxy settings associated wih IE Browser->Connections->LAN Settings)

4 key primary key vs 1 key artificial primary key

Hi all

I have the following table

CREATE TABLE [dbo].[property_instance] (
[property_instance_id] [int] IDENTITY (1, 1) NOT NULL ,
[application_id] [int] NOT NULL ,
[owner_id] [nvarchar] (100) NOT NULL ,
[property_id] [int] NOT NULL ,
[owner_type_id] [int] NOT NULL ,
[property_value] [ntext] NOT NULL ,
[date_created] [datetime] NOT NULL ,
[date_modified] [datetime] NULL
)

I have created an 'artificial' primary key, property_instance_id. The 'true' primary key is application_id, owner_id, property_id and owner_type_id

In this specific instance
- property_instance_id will never be a foreign key into another table
- queries will generally use application_id, owner_id, property_id and owner_type_id in the WHERE clause when searching for a particular row
- Once inserted, none of the application_id, owner_id, property_id or owner_type_id columns will ever be modified

I generally like to create artificial primary keys whenever the primary key would otherwise consist of more than 2 columns.

What do people think the advantages and disadvantages of each technique are? Do you recommend I go with the existing model, or should I remove the artificial primary key column and just go with a 4 column primary key for this table?

Thanks Mattno the subject of surrogate and natural keys, we already have many threads (http://www.dbforums.com/search.php?action=showresults&searchid=479872&sortby=lastpost&sortorder=descending) -- not sure if that will work, it's a search for surrogate and natural

one thing troubles me, and that is how you have both owner_id and owner_type_id in your candidate key

doesn't that violate some normal form or another? isn't owner_type_id totall dependent on which owner it is?|||Originally posted by r937
no the subject of surrogate and natural keys, we already have many threads (http://www.dbforums.com/search.php?action=showresults&searchid=479872&sortby=lastpost&sortorder=descending) -- not sure if that will work, it's a search for surrogate and natural

one thing troubles me, and that is how you have both owner_id and owner_type_id in your candidate key

doesn't that violate some normal form or another? isn't owner_type_id totall dependent on which owner it is?

Thanks for the search keyword tips. I did indeed find a number of helpful threads.

Matt|||I believe the general resolution was that Plain-Bellied Sneetches prefer natural keys, while Star-Bellied Sneetches see the advantages of surrogate keys.

Experts opinions varried:
Moe: Surrogate Keys
Larry: Natural Keys
Curly: Nyuck nyuck nyuck

We are still awaiting final Papal Dispensation on the subject.|||hilarious

i'm afraid that rome will tell you that it can only be a natural primary key, as surrogates are the work of the devil

one of curly's best lines is "i'm tryna think but nothin happens!" which is how i feel sometimes when asked to list the pros and cons of surrogate keys versus natural

maybe i should just say the words "surrogate key" again, and perhaps also the words "natural key" -- so it shouldn't be a total loss, that way they will be salted quite heavily in this post, and therefore this thread, for the benefit of search engines

moe and larry: "niagara falls!! slowly i turn... step by step... inch by inch..."
[moe and larry advance on curly, who backs up into the bathroom, and falls into the bathtub]
moe: "what are you doing in there?!"
curly: "so it shouldn't be a total loss, i'm takin a bath"
moe: "a bath?!"
curly: "yeah, a bath"
moe (after a slight pause): "move over"|||For what it's worth:

it depends upon the scale of the data, the query composition and whether or not you need to sub index on a primary's sub key.

given

application_id, owner_id, and property_id

being the key, the whole key and nothing but the key, do you ever need to index to owner_id alone? if yes then:

owner_id, application_id, and property_id

and so on.

Do you join across tables, i.e. owner_id -> owner details and property_id -> property details? If so, which ordering of the composite key gives fastest access? Who the hell knows without knowledge of the data definition? Nobody, that's who.

There's no generic solution to this question, it depends entirely on your data, and you should normalize to a correctly third normal multiple key and then procedurally denormalize to a compound key if your unique scenario requires it.