Duplicated Rows

Hi,

I have just started developing in SQL Express in the last 2 months so still learning. The problem I’m having with my stored procedure is that I get duplicate rows in my results. The row is a duplicate in terms of column 'Job No' as when the query runs in access only one instance of each 'Job No' is returned but when I recreate the query in SQL server I get a number of rows back for the same 'Job No'? How would I go about getting just 1 instance of each 'Job No' back? With column 'Days to Date' showing the total 'Days to Date' for each Job No. Please see Ms Access results if unsure of what I’m asking.

A copy of the stored procedure is below and a sample of the out-put with Ms Access results at very bottom.

ALTER PROCEDURE [dbo].[sl_DaysDonePerJob] AS

SELECT CASE WHEN [Job No] IS NULL THEN '' ELSE [Job No] END AS [Job No], SUM([Actual Days]) AS [Days to Date], CONVERT(nvarchar(10),MIN(SessionDate),101) AS [Start Date],

CONVERT(nvarchar(10),MAX(SessionDate),101) AS [End Date],

MAX(CASE WHEN DATEPART(MM,SessionDate)=1 THEN 'Jan'

WHEN DATEPART(MM,SessionDate)=2 THEN 'Feb'

WHEN DATEPART(MM,SessionDate)=3 THEN 'Mar'

WHEN DATEPART(MM,SessionDate)=4 THEN 'Apr'

WHEN DATEPART(MM,SessionDate)=5 THEN 'May'

WHEN DATEPART(MM,SessionDate)=6 THEN 'Jun'

WHEN DATEPART(MM,SessionDate)=7 THEN 'Jul'

WHEN DATEPART(MM,SessionDate)=8 THEN 'Aug'

WHEN DATEPART(MM,SessionDate)=9 THEN 'Sep'

WHEN DATEPART(MM,SessionDate)=10 THEN 'Oct'

WHEN DATEPART(MM,SessionDate)=11 THEN 'Nov'

WHEN DATEPART(MM,SessionDate)=12 THEN 'Dec' END) AS 'End Month'

FROM Sessions

GROUP BY [Job No], Sessions.SessionDate

ORDER BY [Job No]

Results in SQL Server Express

'Job No' 'DaystoDate' 'Start Date' 'End Date' 'End Month'

1113-001 0 08/16/2001 08/16/2001 Aug
1113-002 0.5 07/11/2000 07/11/2000 Jul
1113-002 0.5 02/09/2000 02/09/2000 Feb
1116-001 1 07/07/1999 07/07/1999 Jul
1116-001 1 07/06/1999 07/06/1999 Jul
1118-001 1 01/12/1999 01/12/1999 Jan
1118-001 0.5 03/17/1999 03/17/1999 Mar
1118-001 1 02/23/1999 02/23/1999 Feb
1118-001 1 01/26/1999 01/26/1999 Jan
1118-001 0.5 03/09/1999 03/09/1999 Mar
1118-001 1 12/15/1998 12/15/1998 Dec
1118-001 1 02/09/1999 02/09/1999 Feb

Results in Ms Access

Days Done per Job

JobNo

Days to Date

Start Date

End Date

End Month

1113-001

0.00

16/08/2001

16/08/2001

Aug01

1113-002

1.00

09/02/2000

11/07/2000

Jul00

1116-001

2.00

06/07/1999

07/07/1999

Jul99

1118-001

6.00

15/12/1998

17/03/1999

Mar99

Not being an expert by any means, my hunch is that you should set up the first column as a primary key. It will never allow duplicate values after that. It will take you a second to do it in the SQL database table. Right click on the column name in designer. It may not eliminate the problem completely in a sense that there might be a bug in your code that does it. At least you will get an error message from the server protesting the fact that somebody is sending duplicates to it. It will be a cue for you where to look.|||Cheers Alex will give it a go.

Duplicated Records

invno frecref pono
---- ------ ----
INV0000006 INV000000601 X0000009
INV0000006 INV000000601 X0000010
INV0000006 INV000000601 X0000043
INV0000006 INV000000602 X0000009
INV0000006 INV000000602 X0000010
INV0000006 INV000000602 X0000043
INV0000006 INV000000603 X0000009
INV0000006 INV000000603 X0000010
INV0000006 INV000000603 X0000043
INV0000006 INV000000604 X0000009
INV0000006 INV000000604 X0000010
INV0000006 INV000000604 X0000043

Hi,

how do i elimate duplicated records? i tried suppress if duplicated but cant.I dont see any duplicated records in the data you posted... :confused:|||Hi wizards,

whats i mean were repeated data in column.. how do i suppress it?
just wanted to show only

INV0000006 INV000000601 X0000009
INV000000602 X0000010
INV000000603 X0000043
INV000000604|||Do a group on Invno and place the fields in group footer then it will show only the last record of each groupsql

Duplicated parameter names are not allowed.

I am getting this exception in RC1:

SqlCException: Duplicated parameter names are not allowed. [ Parameter name = @.SOLUTIONID ]

With the following statement:

SELECT
[VERSIONID],
[SOLUTIONID],
[BASEVERSIONID],
[VERSIONNUMBER],
[NAME],
[DESCRIPTION],
[CREATEDATE],
[UPDATEDATE],
[VERSIONCLOSED]
FROM VERSIONS
WHERE SOLUTIONID = @.SOLUTIONID AND
VERSIONCLOSED = 1 AND
VERSIONID IN (SELECT MAX(VERSIONID)
FROM VERSIONS WHERE SOLUTIONID = @.SOLUTIONID);

Besides the obvious of adding a new parameter with the same value and a different name - are there any plans to fix this?

Another area where SQLCE seems deficient is non-support of SCOPE_IDENTITY(), which although not important for CE itself is very important for TSQL compatibility with SQL Server - where obviously @.@.IDENTITY won't cut it.

A related problem to this is that SQL CE doesn't support batch statements - which sadly destroys the useful pattern of inserting a record with an INSERT and doing an identity SELECT in the same batch.

When programming against a SQL Server there is benefit from doing as much work as possible in one round trip. However with CE all the work is being done on your desktop so there is little to be gained in creating complex, multistepped batches.

Have you considered splitting your query into 2? Secondly have you considered dynamically creating the SQL, or a prepared query?

By splitting it as:

SELECT MAX(VERSIONID)
FROM VERSIONS WHERE SOLUTIONID = @.SOLUTIONID

as a scalar query and then executing the second part as

SELECT
[VERSIONID],
[SOLUTIONID],
[BASEVERSIONID],
[VERSIONNUMBER],
[NAME],
[DESCRIPTION],
[CREATEDATE],
[UPDATEDATE],
[VERSIONCLOSED]
FROM VERSIONS
WHERE SOLUTIONID = @.SOLUTIONID AND
VERSIONCLOSED = 1 AND
VERSIONID = @.MAXVERSIONID

it allows you to use an index on VERSIONID.

In addition you can check that there is a MAX(VERSIONID) and if there isn't then insert some program logic at this step to avoid the second SELECT.

As for the @.@.IDENTITY problem couldn't you use the Insert method of SQLCEResultSet and then read the identity column?

Just a thought
|||Thanks for the indexing tips Brian, much appreciated - the example TSQL given is unoptimized it's true.

However my question wasn't really about indexing - it was about the subtle differences between SQL Server and SQL CE TSQL (mostly syntactical) which make it really hard to share TSQL between SQL Server and SQL CE.

Is there any chance of SCOPE_IDENTITY() and support for batches sneaking into a version sometime soon? We are using the SQLClient and SQLCE providers via the really useful provider factory in ADO.NET so if the TSQL could be shared that would be ideal.|||I suspect that in the short term SQL CE version of T-SQL will remain very close to that of SQL Mobile from which it has descended.

Perhaps in CE 2.0 some of the features will be added? but wont the mobile people then complain?

Duplicated parameter names are not allowed.

I am getting this exception in RC1:

SqlCException: Duplicated parameter names are not allowed. [ Parameter name = @.SOLUTIONID ]

With the following statement:

SELECT
[VERSIONID],
[SOLUTIONID],
[BASEVERSIONID],
[VERSIONNUMBER],
[NAME],
[DESCRIPTION],
[CREATEDATE],
[UPDATEDATE],
[VERSIONCLOSED]
FROM VERSIONS
WHERE SOLUTIONID = @.SOLUTIONID AND
VERSIONCLOSED = 1 AND
VERSIONID IN (SELECT MAX(VERSIONID)
FROM VERSIONS WHERE SOLUTIONID = @.SOLUTIONID);

Besides the obvious of adding a new parameter with the same value and a different name - are there any plans to fix this?

Another area where SQLCE seems deficient is non-support of SCOPE_IDENTITY(), which although not important for CE itself is very important for TSQL compatibility with SQL Server - where obviously @.@.IDENTITY won't cut it.

A related problem to this is that SQL CE doesn't support batch statements - which sadly destroys the useful pattern of inserting a record with an INSERT and doing an identity SELECT in the same batch.

When programming against a SQL Server there is benefit from doing as much work as possible in one round trip. However with CE all the work is being done on your desktop so there is little to be gained in creating complex, multistepped batches.

Have you considered splitting your query into 2? Secondly have you considered dynamically creating the SQL, or a prepared query?

By splitting it as:

SELECT MAX(VERSIONID)
FROM VERSIONS WHERE SOLUTIONID = @.SOLUTIONID

as a scalar query and then executing the second part as

SELECT
[VERSIONID],
[SOLUTIONID],
[BASEVERSIONID],
[VERSIONNUMBER],
[NAME],
[DESCRIPTION],
[CREATEDATE],
[UPDATEDATE],
[VERSIONCLOSED]
FROM VERSIONS
WHERE SOLUTIONID = @.SOLUTIONID AND
VERSIONCLOSED = 1 AND
VERSIONID = @.MAXVERSIONID

it allows you to use an index on VERSIONID.

In addition you can check that there is a MAX(VERSIONID) and if there isn't then insert some program logic at this step to avoid the second SELECT.

As for the @.@.IDENTITY problem couldn't you use the Insert method of SQLCEResultSet and then read the identity column?

Just a thought
|||Thanks for the indexing tips Brian, much appreciated - the example TSQL given is unoptimized it's true.

However my question wasn't really about indexing - it was about the subtle differences between SQL Server and SQL CE TSQL (mostly syntactical) which make it really hard to share TSQL between SQL Server and SQL CE.

Is there any chance of SCOPE_IDENTITY() and support for batches sneaking into a version sometime soon? We are using the SQLClient and SQLCE providers via the really useful provider factory in ADO.NET so if the TSQL could be shared that would be ideal.|||I suspect that in the short term SQL CE version of T-SQL will remain very close to that of SQL Mobile from which it has descended.

Perhaps in CE 2.0 some of the features will be added? but wont the mobile people then complain?

duplicated data display

Hi all. i have the following function below,which use to retrieve the order detail from 2 table which are order detail and product. i have many duplicated order id in order detail, and each order id has a unique product id which link to product to display the product information. however when i run the following function below . its duplicated each product info and dsplay in the combo box. May i know whats wrong with my code?

Public Sub ProductShow()

Dim myReader As SqlCeDataReader
Dim mySqlCommand As SqlCeCommand
Dim myCommandBehavior As New CommandBehavior
Try
connLocal.Open()
mySqlCommand = New SqlCeCommand
mySqlCommand = connLocal.CreateCommand
mySqlCommand.CommandText = "SELECT * FROM Product P,orders O,orderdetail OD WHERE OD.O_Id='" & [Global].O_Id & "' AND P.P_Id=OD.P_Id "
myCommandBehavior = CommandBehavior.CloseConnection
myReader = mySqlCommand.ExecuteReader(myCommandBehavior)
While (myReader.Read())
cboProductPurchased.Items.Add(myReader("P_Name").ToString())
End While
myReader.Close()
Catch ex As Exception
MsgBox(ex.ToString)
Finally
connLocal.Close()
End Try

End Sub

Can you please provide sample data also?

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

Duplicated Admin Passwords need to be replaced with unique passwor

We need to change passwords for 4 replicating servers. To date, they've all
shared the same Administrator password and connect using NT authentication.
The servers now need to have unique passwords. 2 servers are members of the
same domain and the other two are members of seperate workgroups. What's the
most secure and efficient setup for replicating servers with unique
passwords? Create a unique administrator rights user on each server and set
SQL to start up as this user on each server and duplicate the user on the
other servers so authentication works? ...
Steven,
I assume you are referring to the agent startup logins? Typically you use a
domain login who is a local administrator - same account for the SQL Server
agent and SQL Server Service. If they are different on each server and
replication is using trusted security, have a look in BOL for the rights
required in replication for the SQL Server Agent in each part of your
topology ("replication, security"), and the entry required in the PAL.
HTH,
Paul Ibison

Duplicate xReference Fields in 1 query ?

HI

Its been a while since i have posted here, but this one has stumped me. maybe i am just super hungry right now and missing something obvious... but i can get this one.

i would search the forums for the answer, but not even sure what to search for... not even sure if this (left joins) is the way to accomplish what i want.

Using MS-SQL database as the back end.

I have 3 data tables.
Table 1 is a cross reference table.
Table 2 is the primary data (group data)
Table 3 is the secondary data (users data) (multiple users in a group)

table 1 provides the text that matches the respective status from both the 2nd and 3rd tables.

xRefTable
MemberStatus MemberStatusText
---- ------
0 Non Member
1 Referral
2 Resident
3 Non Resident

PrimaryUserTable
GroupID GroupStatus
-- ----
1 0
2 3
3 1
4 2

SecondaryDataTable
MemberID GroupID MemberStatus
--- --- ----
1 1 0
2 1 0
3 1 0
4 1 0
5 2 3
6 2 3
7 3 1
8 3 1
9 4 2
10 4 1
11 4 1
12 4 3

The ultimate results i am trying to get is something like :

The Results should be able to yield

GroupID GroupStatus GroupStatusText MemberID MemberStatus MemberStatusText
--- ---- ----- --- ---- ------
4 2 Resident 9 2 Resident
4 2 Resident 10 1 Referral
4 2 Resident 11 1 Referral
4 2 Resident 12 3 Non Resident

i am trying to do this with a single SQL query, i am sure it is possible, but cant finger out how to structure it. this is what i was thinking, but i am sure you can see its not going to work.

Lines 4 and 7 are the hangup i believe.
Since both are referring to 'MemberStatusText', how do i specify that line 4 relates to the primary data and line 7 related to the secondary data ?

1 SELECT
2 PrimaryUserTable.GroupID,
3 PrimaryUserTable.GroupStatus,
4 xRefTable.MemberStatusText as GroupStatusText

5 SecondaryDataTable.MemberID,
6 SecondaryDataTable.MemberStatus,
7 xRefTable.MemberStatusText as MemberStatusText

8 LEFT JOIN SecondaryDataTable on (PrimaryUserTable.GroupID = SecondaryDataTable.GroupID)
9 LEFT JOIN xRefTable on (PrimaryUserTable.GroupStatus = xRefTable.MemberStatus)
10 LEFT JOIN xRefTable on (SecondaryDataTable.MemberStatus = xRefTable.MemberStatus)

11 WHERE GroupID = 4
12 ORDER BY GroupID, MemberIDSince both are referring to 'MemberStatusText', how do i specify that line 4 relates to the primary data and line 7 related to the secondary data ?using table aliasesSELECT PrimaryUserTable.GroupID
, PrimaryUserTable.GroupStatus
, GroupXref.MemberStatusText as GroupStatusText
, SecondaryDataTable.MemberID
, SecondaryDataTable.MemberStatus
, MemberXref.MemberStatusText as MemberStatusText
FROM PrimaryUserTable
INNER
JOIN SecondaryDataTable
on SecondaryDataTable.GroupID = PrimaryUserTable.GroupID
INNER
JOIN xRefTable as GroupXref
on GroupXref.MemberStatus = PrimaryUserTable.GroupStatus
INNER
JOIN xRefTable as MemberXref
on MemberXref.MemberStatus = SecondaryDataTable.MemberStatus
WHERE PrimaryUserTable.GroupID = 4
ORDER
BY PrimaryUserTable.GroupID
, SecondaryDataTable.MemberID:)|||EXCELLENT !

exactly what i needed. the table alisases were just skipping from my mind that night.

THANK YOUsql