Duplicates

Hi!
Grateful for some help as a newbie...
I have a
OLE db SQL command: SELECT DISTINCT convert (char(8),date,112) as day,...etc

Resulting in error "Violation of PRIMARY KEY constraint 'PK_Dim_Date"... so Column Day in Dim_date contains duplicates.

I suppose i need a delete in Lookup or how would I eliminate duplicates in Dim?
DELETE day from dim_date where day in(Select day from date ...

This sounds like a transact-sql question, not an SSIS question.

But, what is the primary key of the table Dim_date?|||

Your post is not clear enough...

How you are getting a violation PK error when running a Select?

Please provide more details

|||Ok, let me clarify..
I have a sql command in OLE db Source:
SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine)

But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".

Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how...
Delete day from Dim_Date where ...|||

curiousss wrote:

Ok, let me clarify..
I have a sql command in OLE db Source:
SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine)

But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".

Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how...
Delete day from Dim_Date where ...

Fine. But we still don't know anything about the destination table. What is the primary key?|||

Ah, ok, the destination table primary key is Day (int).

|||

How is this Day columnn populated? Is it a surrogate key or is it a date format like 20070214?

You say you want to delete the duplicates in the destination. And then insert them again? Why not skip the records that are already there?

Pipo1

|||See the first thread on the main page of this forum for checking if a record exists, and if it does update it else insert.

Take that logic, and omit the update statement if you don't want to do anything when the record exists.|||Thank you so much!
How about if I delete and empty the whole table. This command does not seem to work though.

DELETE day

FROM dbo.Dim_Date ?|||

curiousss wrote:

Thank you so much!
How about if I delete and empty the whole table. This command does not seem to work though.

DELETE day

FROM dbo.Dim_Date ?

truncate table dbo.dim_date will delete the whole table
delete from dbo.dim_date will do the same thing, sort of

This whole scenario you have set up here isn't very clear. If day is the key to DIM_DATE, then there are no duplicates...|||Thank you...
Problems disappeared when I deleted the tables, and executed them again.

So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be?

[Lookup Salesperson [7405]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.|||

curiousss wrote:

Thank you...
Problems disappeared when I deleted the tables, and executed them again.

So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be?

[Lookup Salesperson [7405]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

You might want to try a "select distinct column from table" in your lookup.|||Thanks, not eliminated with DISTINCT. Should I leave them or remove them?|||

curiousss wrote:

Thanks, not eliminated with DISTINCT. Should I leave them or remove them?

Only you can make that decision now. You know the data best, and we cannot help you further.

You might want to head over to the Transact-SQL forum here to get help in writing queries to suit your needs. Basically, you need to work on getting a distinct list returned in your lookup query. If you cannot do that due to data issues, then well, you'll need to determine how to work with that data. We cannot tell you which route to take.|||How would I actually remove /delete the duplicates manually(without T-sql). I see the duplicates in destination Preview. Like this?
"DELETE id, salary from dbo.Dim_D where id = 199sql

Duplicates

Hi!
Grateful for some help as a newbie...
I have a
OLE db SQL command: SELECT DISTINCT convert (char(8),date,112) as day,...etc

Resulting in error "Violation of PRIMARY KEY constraint 'PK_Dim_Date"... so Column Day in Dim_date contains duplicates.

I suppose i need a delete in Lookup or how would I eliminate duplicates in Dim?
DELETE day from dim_date where day in(Select day from date ...

This sounds like a transact-sql question, not an SSIS question.

But, what is the primary key of the table Dim_date?|||

Your post is not clear enough...

How you are getting a violation PK error when running a Select?

Please provide more details

|||Ok, let me clarify..
I have a sql command in OLE db Source:
SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine)

But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".

Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how...
Delete day from Dim_Date where ...|||

curiousss wrote:

Ok, let me clarify..
I have a sql command in OLE db Source:
SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine)

But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".

Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how...
Delete day from Dim_Date where ...

Fine. But we still don't know anything about the destination table. What is the primary key?|||

Ah, ok, the destination table primary key is Day (int).

|||

How is this Day columnn populated? Is it a surrogate key or is it a date format like 20070214?

You say you want to delete the duplicates in the destination. And then insert them again? Why not skip the records that are already there?

Pipo1

|||See the first thread on the main page of this forum for checking if a record exists, and if it does update it else insert.

Take that logic, and omit the update statement if you don't want to do anything when the record exists.|||Thank you so much!
How about if I delete and empty the whole table. This command does not seem to work though.

DELETE day FROM dbo.Dim_Date ?|||

curiousss wrote:

Thank you so much!
How about if I delete and empty the whole table. This command does not seem to work though.

DELETE day FROM dbo.Dim_Date ?

truncate table dbo.dim_date will delete the whole table
delete from dbo.dim_date will do the same thing, sort of

This whole scenario you have set up here isn't very clear. If day is the key to DIM_DATE, then there are no duplicates...|||Thank you...
Problems disappeared when I deleted the tables, and executed them again.

So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be?

[Lookup Salesperson [7405]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.|||

curiousss wrote:

Thank you...
Problems disappeared when I deleted the tables, and executed them again.

So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be?

[Lookup Salesperson [7405]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

You might want to try a "select distinct column from table" in your lookup.|||Thanks, not eliminated with DISTINCT. Should I leave them or remove them?|||

curiousss wrote:

Thanks, not eliminated with DISTINCT. Should I leave them or remove them?

Only you can make that decision now. You know the data best, and we cannot help you further.

You might want to head over to the Transact-SQL forum here to get help in writing queries to suit your needs. Basically, you need to work on getting a distinct list returned in your lookup query. If you cannot do that due to data issues, then well, you'll need to determine how to work with that data. We cannot tell you which route to take.|||How would I actually remove /delete the duplicates manually(without T-sql). I see the duplicates in destination Preview. Like this?
"DELETE id, salary from dbo.Dim_D where id = 199

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

Dupes

ID CID Date
1 1 3/10/2008
2 2 3/10/2008
3 3 6/20/2007
4 3 6/20/2007
5 4 3/10/2008
6 3 3/30/2007
7 5 6/20/2007
8 3 6/20/2007
IDs 3,4, 8 are duplicates. What would the delete statement look like to
delete IDs 4 & 8 and leave ID 3.Try:
delete MyTable
where exists
(
select
*
from
MyTable m
where
m.CID = MyTable.CID
and
m.[Date] = MyTable.CID
and
m.ID > MyTable.CID
)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:10594163-638B-4CF9-9FB0-7718251D47A0@.microsoft.com...
ID CID Date
1 1 3/10/2008
2 2 3/10/2008
3 3 6/20/2007
4 3 6/20/2007
5 4 3/10/2008
6 3 3/30/2007
7 5 6/20/2007
8 3 6/20/2007
IDs 3,4, 8 are duplicates. What would the delete statement look like to
delete IDs 4 & 8 and leave ID 3.|||Tom,
I modified to:
and
> m.[Date] = MyTable.[Date]
> and
> m.ID > MyTable.ID
This works. Thanks..
"Tom Moreau" wrote:
> Try:
> delete MyTable
> where exists
> (
> select
> *
> from
> MyTable m
> where
> m.CID = MyTable.CID
> and
> m.[Date] = MyTable.CID
> and
> m.ID > MyTable.CID
> )
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:10594163-638B-4CF9-9FB0-7718251D47A0@.microsoft.com...
> ID CID Date
> 1 1 3/10/2008
> 2 2 3/10/2008
> 3 3 6/20/2007
> 4 3 6/20/2007
> 5 4 3/10/2008
> 6 3 3/30/2007
> 7 5 6/20/2007
> 8 3 6/20/2007
> IDs 3,4, 8 are duplicates. What would the delete statement look like to
> delete IDs 4 & 8 and leave ID 3.
>|||On SQL Server 2005 you can do:
;WITH Dups (seq)
AS
(SELECT ROW_NUMBER() OVER(
PARTITION BY cid, date
ORDER BY id)
FROM Foo)
DELETE Dups
WHERE seq > 1;
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Oops! God catch.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:D180E323-D5DE-4B45-A9D8-156EDC31FED4@.microsoft.com...
Tom,
I modified to:
and
> m.[Date] = MyTable.[Date]
> and
> m.ID > MyTable.ID
This works. Thanks..
"Tom Moreau" wrote:
> Try:
> delete MyTable
> where exists
> (
> select
> *
> from
> MyTable m
> where
> m.CID = MyTable.CID
> and
> m.[Date] = MyTable.CID
> and
> m.ID > MyTable.CID
> )
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:10594163-638B-4CF9-9FB0-7718251D47A0@.microsoft.com...
> ID CID Date
> 1 1 3/10/2008
> 2 2 3/10/2008
> 3 3 6/20/2007
> 4 3 6/20/2007
> 5 4 3/10/2008
> 6 3 3/30/2007
> 7 5 6/20/2007
> 8 3 6/20/2007
> IDs 3,4, 8 are duplicates. What would the delete statement look like to
> delete IDs 4 & 8 and leave ID 3.
>sql

Dumb Date Format Question

I want the following query to result in "2004/10".
SELECT DATEPART(yyyy, datein) & '/' & DATEPART(mm, datein) AS theDate
From myTable
However, I get the error:
Syntax error converting the varchar value '/' to a column of data type int.
Is there a better way to do this?
Also, I realize I may be asking this in the wrong forum. Is there a place
for MSSQL newbies to ask their dumb questions?
Thank you!
MatthewHi Matthew
Ask whatever you like here, just don't call anything a dumb question. That
way, you give people who have only been using the product a bit longer than
you a chance to answer questions and feel like they have learned something.
But nobody wants to feel like they can only answer DUMB questions. ;-)
& is the bitwise 'AND' operator, and its operands must be integers. Please
read about bit operations in the Books Online.
My guess is that you want to concatenate strings. In TSQL we use '+' for
concatenation.
But, it still won't work because datepart returns a numeric value. Try using
datename. Or converting to character before you concatenate.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matthew" <turn.deletethis@.alltel.net> wrote in message
news:O6wl$vfvEHA.3276@.TK2MSFTNGP15.phx.gbl...
>I want the following query to result in "2004/10".
> SELECT DATEPART(yyyy, datein) & '/' & DATEPART(mm, datein) AS theDate
> From myTable
> However, I get the error:
> Syntax error converting the varchar value '/' to a column of data type
> int.
> Is there a better way to do this?
> Also, I realize I may be asking this in the wrong forum. Is there a place
> for MSSQL newbies to ask their dumb questions?
> Thank you!
> Matthew
>|||> Ask whatever you like here, just don't call anything a dumb question. That
> way, you give people who have only been using the product a bit longer
> than you a chance to answer questions and feel like they have learned
> something. But nobody wants to feel like they can only answer DUMB
> questions. ;-)
Sorry about that. I'll be more careful in the future.

> My guess is that you want to concatenate strings. In TSQL we use '+' for
> concatenation.
Yup, that's right.

> But, it still won't work because datepart returns a numeric value. Try
> using datename. Or converting to character before you concatenate.
OK, I'll give that a try.
Thank you!
Matthew

Dumb Date Format Question

I want the following query to result in "2004/10".
SELECT DATEPART(yyyy, datein) & '/' & DATEPART(mm, datein) AS theDate
From myTable
However, I get the error:
Syntax error converting the varchar value '/' to a column of data type int.
Is there a better way to do this?
Also, I realize I may be asking this in the wrong forum. Is there a place
for MSSQL newbies to ask their dumb questions?
Thank you!
Matthew
Hi Matthew
Ask whatever you like here, just don't call anything a dumb question. That
way, you give people who have only been using the product a bit longer than
you a chance to answer questions and feel like they have learned something.
But nobody wants to feel like they can only answer DUMB questions. ;-)
& is the bitwise 'AND' operator, and its operands must be integers. Please
read about bit operations in the Books Online.
My guess is that you want to concatenate strings. In TSQL we use '+' for
concatenation.
But, it still won't work because datepart returns a numeric value. Try using
datename. Or converting to character before you concatenate.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matthew" <turn.deletethis@.alltel.net> wrote in message
news:O6wl$vfvEHA.3276@.TK2MSFTNGP15.phx.gbl...
>I want the following query to result in "2004/10".
> SELECT DATEPART(yyyy, datein) & '/' & DATEPART(mm, datein) AS theDate
> From myTable
> However, I get the error:
> Syntax error converting the varchar value '/' to a column of data type
> int.
> Is there a better way to do this?
> Also, I realize I may be asking this in the wrong forum. Is there a place
> for MSSQL newbies to ask their dumb questions?
> Thank you!
> Matthew
>
|||> Ask whatever you like here, just don't call anything a dumb question. That
> way, you give people who have only been using the product a bit longer
> than you a chance to answer questions and feel like they have learned
> something. But nobody wants to feel like they can only answer DUMB
> questions. ;-)
Sorry about that. I'll be more careful in the future.

> My guess is that you want to concatenate strings. In TSQL we use '+' for
> concatenation.
Yup, that's right.

> But, it still won't work because datepart returns a numeric value. Try
> using datename. Or converting to character before you concatenate.
OK, I'll give that a try.
Thank you!
Matthew

DTS: Converting datatype in the source before import

Hi, In my source (text file connection) there is a date field but the date
is stored yyyymmdd (20080226)
I need to convert this into a real date 02/26/2008 and then import it into
the sql server database into a datetime field.
I'm a newbie with DTS so very detailed insturctions would be greatly
appreciated. When I just tried having it import into SQL Server I got an
error obviously since the date in the source data is not in a format sql
server will understandI'm not going to get detailed but I'll give you one way to do this. You'll
have to do some research on your own.
Import into a varchar column then use convert to transform and push into
your destination datetime column
--
Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org/
Atlanta's Business Intelligence and Data Warehouse Experts
"Patrick Hill" <phill@.nospam_quickcomm.com> wrote in message
news:uKzCVkIeIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi, In my source (text file connection) there is a date field but the date
> is stored yyyymmdd (20080226)
> I need to convert this into a real date 02/26/2008 and then import it into
> the sql server database into a datetime field.
> I'm a newbie with DTS so very detailed insturctions would be greatly
> appreciated. When I just tried having it import into SQL Server I got an
> error obviously since the date in the source data is not in a format sql
> server will understand
>
>|||i forgot to mention use convert function when pushing varchar into datetime
column
--
Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org/
Atlanta's Business Intelligence and Data Warehouse Experts
"Knowledgy" <atlanta business intelligence consultants> wrote in message
news:XaednbkftahIS1nanZ2dnUVZ_gWdnZ2d@.comcast.com...
> I'm not going to get detailed but I'll give you one way to do this.
> You'll have to do some research on your own.
> Import into a varchar column then use convert to transform and push into
> your destination datetime column
> --
> Sincerely,
> John K
> Knowledgy Consulting
> http://knowledgy.org/
> Atlanta's Business Intelligence and Data Warehouse Experts
>
> "Patrick Hill" <phill@.nospam_quickcomm.com> wrote in message
> news:uKzCVkIeIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi, In my source (text file connection) there is a date field but the
>> date is stored yyyymmdd (20080226)
>> I need to convert this into a real date 02/26/2008 and then import it
>> into the sql server database into a datetime field.
>> I'm a newbie with DTS so very detailed insturctions would be greatly
>> appreciated. When I just tried having it import into SQL Server I got an
>> error obviously since the date in the source data is not in a format sql
>> server will understand
>>
>

DTS/bcp a date into a datetime column

Is it possible to bcp a date like '1990-01-01' into a datetime column? The flat file does not contain any time.

I know it is possible using DTS and VB but I am trying to avoid this method. For those familiar with Oracle SQL Loader, you can specify a date format or date conversion function in a control file. I am wondering if I can use the function convert() in some similiar manner.

Thanks,
TPHBCP will handle all reccognized date formats. You do not need to do any type of conversions.

DTS to export formated date to excel

Hello,

I am trying to output data from my sql table to an excel spreadsheet and send it by email which works fine, the problem is he wants the date to be in the format d-mmm-yy, which is easy to format in excel manually, but he do not want to do this manually. I tried to do this when I select the date from the table to spreadsheet, "select convert(char,value_date,106) from table", but this don't get transported to the excel spreadsheet, I get my results on the spread sheet as dd/mm/yy. Can you please help either to set the date on excel forever to be in this format "d-mmm-yy" or to force this output to excelOk, i manage to answer myself, you need to format the cells onto the excel file itself