one column causing duplicate rows - wrong join used?

Hi,
Consider the following result set:
PNID PN_NUMBER Date1 Date2 Status PN_Na
me
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted NULL
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Here is the sql that returns the above:
SELECT
DISTINCT(PNP.PNID) AS 'PN_ID',
PNP.PNNumber AS 'PN_NUMBER',
CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
PNP.PNStatus AS 'Status',
BB.Name AS 'PN_NAME',
FROM
tblPNProperties PNP
LEFT JOIN tblBusinessBoard BB
ON PNP.PNID = BB.PNID
My desired resultset would be to have pnids 28 and 29 to be unique,
however because I am
selecting PN_Name it causes the rows to have duplicates. How would I be
able to obtain my desired resultset? (see below) Is my join correct?
PNID PN_NUMBER Date1 Date2 Status PN_Na
me
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Any ideas?
Thanks
qhChange the LEFT JOIN to a JOIN.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Quackhandle" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1133522905.310939.208490@.g49g2000cwa.googlegroups.com...
Hi,
Consider the following result set:
PNID PN_NUMBER Date1 Date2 Status PN_Name
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted NULL
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Here is the sql that returns the above:
SELECT
DISTINCT(PNP.PNID) AS 'PN_ID',
PNP.PNNumber AS 'PN_NUMBER',
CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
PNP.PNStatus AS 'Status',
BB.Name AS 'PN_NAME',
FROM
tblPNProperties PNP
LEFT JOIN tblBusinessBoard BB
ON PNP.PNID = BB.PNID
My desired resultset would be to have pnids 28 and 29 to be unique,
however because I am
selecting PN_Name it causes the rows to have duplicates. How would I be
able to obtain my desired resultset? (see below) Is my join correct?
PNID PN_NUMBER Date1 Date2 Status PN_Name
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Any ideas?
Thanks
qh|||Follow-up: If that doesn't fix it, could you please post your DDL for the
two tables + INSERT's of the sample data? We may have to change your query
further.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uTb$rXz9FHA.916@.TK2MSFTNGP10.phx.gbl...
Change the LEFT JOIN to a JOIN.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Quackhandle" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1133522905.310939.208490@.g49g2000cwa.googlegroups.com...
Hi,
Consider the following result set:
PNID PN_NUMBER Date1 Date2 Status PN_Name
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted NULL
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Here is the sql that returns the above:
SELECT
DISTINCT(PNP.PNID) AS 'PN_ID',
PNP.PNNumber AS 'PN_NUMBER',
CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
PNP.PNStatus AS 'Status',
BB.Name AS 'PN_NAME',
FROM
tblPNProperties PNP
LEFT JOIN tblBusinessBoard BB
ON PNP.PNID = BB.PNID
My desired resultset would be to have pnids 28 and 29 to be unique,
however because I am
selecting PN_Name it causes the rows to have duplicates. How would I be
able to obtain my desired resultset? (see below) Is my join correct?
PNID PN_NUMBER Date1 Date2 Status PN_Name
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Any ideas?
Thanks
qh|||Hi Tom,
thanks for both replies. Unfortunately using JOIN did not work. when
I type the following
select * from tblPNProperties
where pnid = '28'
I get 1 row
select * from tblBusinessboard
where pnid = '28'
however here I get two rows
I have a hunch that the data is incorrect.
Back to the drawing board
cheers
qh|||On 2 Dec 2005 03:28:25 -0800, Quackhandle wrote:

>Hi,
>Consider the following result set:
> PNID PN_NUMBER Date1 Date2 Status PN_Na
me
> ========================================
========================
>27 2051 08 Sep 1941 NULL Received NULL
>28 2143 01 Jan 1945 NULL Accepted NULL
>28 2143 01 Jan 1945 NULL Accepted R Anderson
>29 2151 NULL NULL Accepted NULL
>29 2151 NULL NULL Accepted W Yarwood
>30 1579 17 Nov 1925 NULL Received NULL
>31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
>
>Here is the sql that returns the above:
>SELECT
> DISTINCT(PNP.PNID) AS 'PN_ID',
> PNP.PNNumber AS 'PN_NUMBER',
> CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
> CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
> PNP.PNStatus AS 'Status',
> BB.Name AS 'PN_NAME',
>FROM
> tblPNProperties PNP
> LEFT JOIN tblBusinessBoard BB
> ON PNP.PNID = BB.PNID
>My desired resultset would be to have pnids 28 and 29 to be unique,
>however because I am
>selecting PN_Name it causes the rows to have duplicates. How would I be
>able to obtain my desired resultset? (see below) Is my join correct?
>
> PNID PN_NUMBER Date1 Date2 Status PN_Na
me
> ========================================
========================
>27 2051 08 Sep 1941 NULL Received NULL
>28 2143 01 Jan 1945 NULL Accepted R Anderson
>29 2151 NULL NULL Accepted W Yarwood
>30 1579 17 Nov 1925 NULL Received NULL
>31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
>
>Any ideas?
>
>Thanks
>qh
Hi qh,
Since you didn't post CREATE TABLE and INSERT statements, here's a wild
and completely untested guess:
SELECT
PNP.PNID AS 'PN_ID',
PNP.PNNumber AS 'PN_NUMBER',
CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
PNP.PNStatus AS 'Status',
MAX(BB.Name) AS 'PN_NAME'
FROM
tblPNProperties PNP
LEFT JOIN tblBusinessBoard BB
ON PNP.PNID = BB.PNID
GROUP BY
PNP.PNID,
PNP.PNNumber,
PNP.PNDate,
PNP.InspectionDate,
PNP.PNStatus
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

OLEDB AcquireConnection Issues

On day 1 of the first package. In playing around with some settings for using variables, I've seemed to have set a few things that are causing issues.

In my dataflow I have a source and target destination with 3 separate lookups. All of these objects utilize the same connection manager. When trying to revert the package to a ProtectionLevel=EncryptSensitiveWithUserKey, I'll see one of the 5 objects fail with a No AcquireConnection issue. I'll stop the debug, test the connection to re-validate it. The next debug run will cause the next object to fail. This repeats in an infinite loop until I get tired of re-validating all of the objects.

What option have I set that has screwed up things?

ControlFlow.Security.ProtectionLevel=EncryptSensitiveWithUserKey

Connection.DelayValidation=False

Connection.RetainSameConnection=False

Any Ideas? Thanks in advance.

Just thought I'd add an update. It appears to be tied to the entire solution and not just a particular package.

Here's the actual error:
[LKP-UCM DepartmentId [276]] Error: The AcquireConnection method call to the connection manager "MDW_Staging" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "LKP-UCM DepartmentId" (276) failed validation and returned error code 0xC020801C.

Again, when going into the individual objects I can preview the results and everything is fine. It seems that the connection made is not held for later connections (lookups, destinations). My only solution now is to have the password in the connection manager's connectstring. Seems like a security issue there.

OLEDB AcquireConnection Issues

On day 1 of the first package. In playing around with some settings for using variables, I've seemed to have set a few things that are causing issues.

In my dataflow I have a source and target destination with 3 separate lookups. All of these objects utilize the same connection manager. When trying to revert the package to a ProtectionLevel=EncryptSensitiveWithUserKey, I'll see one of the 5 objects fail with a No AcquireConnection issue. I'll stop the debug, test the connection to re-validate it. The next debug run will cause the next object to fail. This repeats in an infinite loop until I get tired of re-validating all of the objects.

What option have I set that has screwed up things?

ControlFlow.Security.ProtectionLevel=EncryptSensitiveWithUserKey

Connection.DelayValidation=False

Connection.RetainSameConnection=False

Any Ideas? Thanks in advance.

Just thought I'd add an update. It appears to be tied to the entire solution and not just a particular package.

Here's the actual error:
[LKP-UCM DepartmentId [276]] Error: The AcquireConnection method call to the connection manager "MDW_Staging" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "LKP-UCM DepartmentId" (276) failed validation and returned error code 0xC020801C.

Again, when going into the individual objects I can preview the results and everything is fine. It seems that the connection made is not held for later connections (lookups, destinations). My only solution now is to have the password in the connection manager's connectstring. Seems like a security issue there.