Showing posts with label entry. Show all posts
Showing posts with label entry. Show all posts

Thursday, March 29, 2012

Duplicate SQL Server entry

When creating a new data source for SQL Server, there is one server
that shows up twice in the list. Could this cause any problems, and
how do I remove the duplicate entry?Are you referring to the ODBC datasource list? It shouldn't be a problem.
--
Anith

Tuesday, March 27, 2012

Duplicate Removal and Intelligent T-SQL

I am looking for some T_SQL code that would help us eliminate
duplicate data entry into our mailing db. Finding and removing the
existing dups is not rocket science, but I am looking for a complete
solution.

We also want to be able to come up with a T-SQL script to be
intelligent enough to know if:

"Bill Jones" is the same as "Billy Jones" or

"Bill Jones" is the same as "William Jones" or

"Bill Jones at 123 My street" is the same as "Bill Jones at 123 My
St."

Purchasing scripts from a company is not out of the question. Its a
matter of script cost versus my hourly rate to create them... Needless
to say, something cheap.

We have only heard of one app that is dynamic enough to handle this,
and the cost was way, way out of our range, 6 figures is almost 2
years salary.

Any ideas would be greatly appreciated.funkdm1@.yahoo.com (Dave) wrote in message news:<f5174e0f.0406071223.73e8fc2d@.posting.google.com>...
> I am looking for some T_SQL code that would help us eliminate
> duplicate data entry into our mailing db. Finding and removing the
> existing dups is not rocket science, but I am looking for a complete
> solution.
> We also want to be able to come up with a T-SQL script to be
> intelligent enough to know if:
> "Bill Jones" is the same as "Billy Jones" or
> "Bill Jones" is the same as "William Jones" or
> "Bill Jones at 123 My street" is the same as "Bill Jones at 123 My
> St."
> Purchasing scripts from a company is not out of the question. Its a
> matter of script cost versus my hourly rate to create them... Needless
> to say, something cheap.
> We have only heard of one app that is dynamic enough to handle this,
> and the cost was way, way out of our range, 6 figures is almost 2
> years salary.
> Any ideas would be greatly appreciated.

This may help - I've used it successfully to do fuzzy matching of
duplicate company names:

http://www.winnetmag.com/SQLServer/...6094/26094.html

Simon|||Simon,

That was a very interesting article. It also came with about 1200
lines of t-SQL. I dont think thats exactly what I need, but I can
definetely use pieces of it.

Thanks

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0406072358.1cb5c387@.posting.google.com>...
> funkdm1@.yahoo.com (Dave) wrote in message news:<f5174e0f.0406071223.73e8fc2d@.posting.google.com>...
> > I am looking for some T_SQL code that would help us eliminate
> > duplicate data entry into our mailing db. Finding and removing the
> > existing dups is not rocket science, but I am looking for a complete
> > solution.
> > We also want to be able to come up with a T-SQL script to be
> > intelligent enough to know if:
> > "Bill Jones" is the same as "Billy Jones" or
> > "Bill Jones" is the same as "William Jones" or
> > "Bill Jones at 123 My street" is the same as "Bill Jones at 123 My
> > St."
> > Purchasing scripts from a company is not out of the question. Its a
> > matter of script cost versus my hourly rate to create them... Needless
> > to say, something cheap.
> > We have only heard of one app that is dynamic enough to handle this,
> > and the cost was way, way out of our range, 6 figures is almost 2
> > years salary.
> > Any ideas would be greatly appreciated.
> This may help - I've used it successfully to do fuzzy matching of
> duplicate company names:
> http://www.winnetmag.com/SQLServer/...6094/26094.html
> Simon|||>> I am looking for some T_SQL code that would help us eliminate
duplicate data entry into our mailing db .. not rocket science, but I
am looking for a complete solution. <<

Yes, it is :) Get a package and save yourself 5-10 man-years.
Call the NCSC at 800-238-3150 and request the following free
publications:

PUB 25 - Designing Business Letter Mail
PUB 28 - Postal Addressing Standards
PUB 221 - Addressing for Success

Buy a mailing list product to scrub your data. It is much harder than
it looks. Here is an old list of companies that sell ZIP code and
mailing related product:

BCC Software Inc.
39 Saginaw Drive #630
Rochester, NY 14623

Comco, Inc.
2211 Grant Street
Box #349
Bettendorf, IA 52722

CD Light
8861 South Silverstone Way
Sandy, UT 84093
CD-Rom of ZIP+4 for $30

Dassgar Systems
Box #915
Bennington, VT 05201-0915
Global-Z; international postal codes for lots of places

Eric Isaacson Software
416 East Bloomington
Bloomington, IN 47401-4739
demo version of ZipKey

Group 1 Software
4200 Parliament Place #600
Lanham, MD 20706

Melissa Data Corporation
32122 Paseo Adelanto
San Juan Capistrano, CA 92675

Search Software America
1445 East Putnam Ave
Old Greenwich, CT 06870

(ask for their booklet "The Math, Myth & Magic of Name Searching and
Matching". It is quite informative)

For 'UK postcode databases', the Royal Mail holds the copyright and
will licence you an 'outwards' database (i.e. the first segment of the
full postcode) for about 150 a year, or an 'inwards' database (i.e.
the full 9- or 10-character postcode) for about 500 UK pounds per
year.

KJL Software (www.kjlsoftware.com) gives you 5-digit ZIP Code, City,
State,USPS Status Code, LATEST Area Code(s) from NANPA, Time
Zone,Latitude, and Longitude for all valid US Postal Service 5 digit
ZIP Codes/City/State combinations and a Distance Calculator.|||I'd also recommend http://www.qas.com/

We use them for address checking/changing and there is additional
functionality (in the UK at least) to check against the electoral role
to verify if someone is at a specific address.

Failing that, UK wise, Experian or Equifax may be of some help. I'm
not sure what they do outside the UK though.

Hope that helps.

Thursday, March 22, 2012

duplicate entry in a primary key field

Hi everybody couldn't get through with saving my data on the table with two primary keys...

my table structure is this

pubid int Unchecked (primary key)
pub char(1) Unchecked
publ char(1) Unchecked
pubcode char(2) Unchecked (primary key)

a sample data is here

pubid pub publ pubcode

1 a b ab
1 b b bb
2 a b ab
2 b b bb

when i save this table modifying the pubid and pubcode as primary keys the following error displays...

Unable to create index 'PK_PUBS3'.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'.
Could not create constraint. See previous errors.
The statement has been terminated.

what i understand is that on the primary key duplicates are not allowed how could i allow it?

thanksAlex - you could probably do with a brush up on relational database design & theory.
http://r937.com/relational.html

1) You can't have two primary keys - there can only be one. Most likely you have a composite primary key.
2) The whole point of a primary key is that it is unique - this is pretty well the central tenet of a primary key.

What columns in your table uniquely identify a row? It looks like it could be a combination of pubid and pubcode but please let us know.|||You can have exactly 1 primary key. If I understand correctly, the PK is now a composite key on 2 columns, pubid and pubcode. There are no duplicate combinations of values in that combination of columns, even though both columns individually do contain duplicate values.|||there is no column that uniquely identify a row actually the table is a junction table for many to many relationship pubid is the foreign key for table1 and pubcode for table3 I'm normalizing the database so i created this juction table for tables1 & 3 so with it is it okey if i will not make both a primary key?|||so with it is it okey if i will not make both a primary key?is it okay? well, yes, provided that you really do wish to allow the possibility of the same pubid being related to the same pubcode more than once

in most many-to-many relationships, this would be an error|||so what's the best way i could do it?

thanks|||create a composite primary key on both columns|||You have a composite primary key - this is one primary key comproised of 2+ columns.

i.e.
CREATE TABLE MyTable
(
pubid int
, pub char(1)
, publ char(1)
, pubcode char(2)
, CONSTRAINT pk_MyTable PRIMARY KEY CLUSTERED (pubid, pubcode)
)Again I recommend you read the link. It is only an article not a whole book but it will help you get some of the basics figured out.

duplicate check

Hi,
I've a problem to check if there is a duplicate entry.
In my table i have a computed column which checks for a certain type of
entry which must be unique. E.g. there can be two types of entries. One
which has a type of 1 where period can be 6 or 7. The other type is 2
where period can be from 1 to 5.
Now what i would like to do is when type 1 is being entered it can only
be unique for 6 or 7. There cannot be both entries of 6 and 7. For type
2 it should be unique within each period, in that case there may be more
entries.
E.g.
type | contactid | period
1 1 6
1 1 7 <-should not be possible
2 1 1
2 1 2
2 1 3
2 5 1
2 5 1 <-should not be possible
Below is my ddl of my table and trigger:
CREATE TABLE [Mytable] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Type] [int] NOT NULL ,
[contactId] [int] NOT NULL ,
[Period] [int] NOT NULL ,
[Date] [datetime] NOT NULL ,
[TypeChk] AS (case when ([Type] = 1 and (([Period] = 6 or [Period] =
7))) then null else [Id] end) ,
CONSTRAINT [PK_Mytable] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [IX_Mytable] UNIQUE NONCLUSTERED
(
[Id],
[TypeChk]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TRIGGER DupCheck ON [dbo].[Mytable]
FOR INSERT, UPDATE
AS
IF (select count(contactid)
from mytable
group by contactid, period, type
having count(contactid)>1 and type=2)>1
ROLLBACK TRANSACTION
RAISERROR('there is a duplicate entry',16,1)
Can anyone help me?Jason
Why do you have three rows of 2 1 and it did not throw the error
My guess yiou need something like that
create table #test
(
pk int not null primary key,
type int not null,
contactid int not null,
period int,
)
go
alter table #test ADD CONSTRAINT
UNQ_test UNIQUE
(
type,
contactid
) ON [PRIMARY]
insert into #test values(1,1,1,6)
insert into #test values(2,1,1,7)--failed
insert into #test values(3,2,1,6)
insert into #test values(4,2,1,7)--failed
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:OU8bZM$%23FHA.1600@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I've a problem to check if there is a duplicate entry.
> In my table i have a computed column which checks for a certain type of
> entry which must be unique. E.g. there can be two types of entries. One
> which has a type of 1 where period can be 6 or 7. The other type is 2
> where period can be from 1 to 5.
> Now what i would like to do is when type 1 is being entered it can only be
> unique for 6 or 7. There cannot be both entries of 6 and 7. For type 2 it
> should be unique within each period, in that case there may be more
> entries.
> E.g.
> type | contactid | period
> 1 1 6
> 1 1 7 <-should not be possible
> 2 1 1
> 2 1 2
> 2 1 3
> 2 5 1
> 2 5 1 <-should not be possible
>
> Below is my ddl of my table and trigger:
> CREATE TABLE [Mytable] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [Type] [int] NOT NULL ,
> [contactId] [int] NOT NULL ,
> [Period] [int] NOT NULL ,
> [Date] [datetime] NOT NULL ,
> [TypeChk] AS (case when ([Type] = 1 and (([Period] = 6 or [Period] = 7)))
> then null else [Id] end) ,
> CONSTRAINT [PK_Mytable] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [IX_Mytable] UNIQUE NONCLUSTERED
> (
> [Id],
> [TypeChk]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> CREATE TRIGGER DupCheck ON [dbo].[Mytable]
> FOR INSERT, UPDATE
> AS
> IF (select count(contactid)
> from mytable
> group by contactid, period, type
> having count(contactid)>1 and type=2)>1
> ROLLBACK TRANSACTION
> RAISERROR('there is a duplicate entry',16,1)
>
> Can anyone help me?|||First of all the trigger might not work as expected - multiple statements
following the IF statement must be enclosed in a BEGIN...END section if they
are to be executed only if the condition is true.
Right now the transaction in your trigger is rolled back every time, while
the error is raised only when it should be.
IF (select count(contactid)
from mytable
group by contactid, period, type
having count(contactid)>1 and type=2)>1
begin
ROLLBACK TRANSACTION
RAISERROR('there is a duplicate entry',16,1)
end
ML
http://milambda.blogspot.com/|||I was thinking pretty much the same way, but was thrown off by apparent
inconsistencies.
Maybe a table constraint using the following expression might help enforce
the business rule:
((Type = 1) and (Period between 1 and 5)) or ((Type = 2) and (Period between
6 and 7))
ML
http://milambda.blogspot.com/|||Uri Dimant wrote:
> Jason
> Why do you have three rows of 2 1 and it did not throw the error
> My guess yiou need something like that
> create table #test
> (
> pk int not null primary key,
> type int not null,
> contactid int not null,
> period int,
> )
> go
> alter table #test ADD CONSTRAINT
> UNQ_test UNIQUE
> (
> type,
> contactid
> ) ON [PRIMARY]
> insert into #test values(1,1,1,6)
> insert into #test values(2,1,1,7)--failed
> insert into #test values(3,2,1,6)
> insert into #test values(4,2,1,7)--failed
>
>
> "Jason" <jasonlewis@.hotmail.com> wrote in message
> news:OU8bZM$%23FHA.1600@.TK2MSFTNGP11.phx.gbl...
>
>
>
Hi Uri,
The three 2 1 are what i want because they are a type 2 entry. It should
not allow a duplicate entry if the period is the same like the last row
in my example.
Type 1 should be unique for a period of 6 or 7. ML suggested a table
constraint would that work for me?|||On Thu, 08 Dec 2005 13:29:52 +0100, Jason wrote:

>Hi,
>I've a problem to check if there is a duplicate entry.
>In my table i have a computed column which checks for a certain type of
>entry which must be unique. E.g. there can be two types of entries. One
>which has a type of 1 where period can be 6 or 7. The other type is 2
>where period can be from 1 to 5.
>Now what i would like to do is when type 1 is being entered it can only
>be unique for 6 or 7. There cannot be both entries of 6 and 7. For type
>2 it should be unique within each period, in that case there may be more
>entries.
>E.g.
>type | contactid | period
>1 1 6
>1 1 7 <-should not be possible
>2 1 1
>2 1 2
>2 1 3
>2 5 1
>2 5 1 <-should not be possible
Hi Jason,
You can do this without a trigger, by changing the computed column and
the unique constraint:
CREATE TABLE [Mytable] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Type] [int] NOT NULL ,
[contactId] [int] NOT NULL ,
[Period] [int] NOT NULL ,
[Date] [datetime] NOT NULL ,
[TypeChk] AS (case when ([Type] = 1 and (([Period] = 6 or
[Period] = 7))) then null else [Period] end) ,
CONSTRAINT [PK_Mytable] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [IX_Mytable] UNIQUE NONCLUSTERED
(
[Type],
[contactId],
[TypeChk]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On Thu, 08 Dec 2005 13:29:52 +0100, Jason wrote:
>
>
> Hi Jason,
> You can do this without a trigger, by changing the computed column and
> the unique constraint:
> CREATE TABLE [Mytable] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [Type] [int] NOT NULL ,
> [contactId] [int] NOT NULL ,
> [Period] [int] NOT NULL ,
> [Date] [datetime] NOT NULL ,
> [TypeChk] AS (case when ([Type] = 1 and (([Period] = 6 or
> [Period] = 7))) then null else [Period] end) ,
> CONSTRAINT [PK_Mytable] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [IX_Mytable] UNIQUE NONCLUSTERED
> (
> [Type],
> [contactId],
> [TypeChk]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> Best, Hugo
Hi Hugo,
That did the job, thnx!

Wednesday, February 15, 2012

DTS to start a VB job on another server

When an entry is done on a table that has a trigger tied
to it, I would like to execute a Visual Basic program that
resides on another server. I tries making a DTS package
with the Execute Process task property. There is no
ability to pass a userid/password for a logon. I'm also
not sure, if this was even possible, would the program run
on that server or would is be pulled to the SQL Server and
actually run there?
Any info on if this is possible would be appreciated. If
there is some other method to allow an update to an entry
in the database trigger the running of the .exe on a
different server, that would be great to know.
TIA,
Doug
Try using the extended stored proc 'xp_cmdshell' to execute you exe...either map a drive to you sql server or provide the unc path.......
Brad Feaker
DBA
|||On the Win2k developement kit CD there is a file call exec.vbs which will
start a program on a remote server. The program will run in the remote
server's environment not the calling server. Use xp_cmdshell to start
exec.vbs.
"Brad Feaker" <anonymous@.discussions.microsoft.com> wrote in message
news:8B7FD9BF-8DBC-407E-B34C-44D82FBC5410@.microsoft.com...
> Try using the extended stored proc 'xp_cmdshell' to execute you
exe...either map a drive to you sql server or provide the unc path.......
> Brad Feaker
> DBA
>

DTS to start a VB job on another server

When an entry is done on a table that has a trigger tied
to it, I would like to execute a Visual Basic program that
resides on another server. I tries making a DTS package
with the Execute Process task property. There is no
ability to pass a userid/password for a logon. I'm also
not sure, if this was even possible, would the program run
on that server or would is be pulled to the SQL Server and
actually run there?
Any info on if this is possible would be appreciated. If
there is some other method to allow an update to an entry
in the database trigger the running of the .exe on a
different server, that would be great to know.
TIA,
DougTry using the extended stored proc 'xp_cmdshell' to execute you exe...eithe
r map a drive to you sql server or provide the unc path.......
Brad Feaker
DBA|||On the Win2k developement kit CD there is a file call exec.vbs which will
start a program on a remote server. The program will run in the remote
server's environment not the calling server. Use xp_cmdshell to start
exec.vbs.
"Brad Feaker" <anonymous@.discussions.microsoft.com> wrote in message
news:8B7FD9BF-8DBC-407E-B34C-44D82FBC5410@.microsoft.com...
> Try using the extended stored proc 'xp_cmdshell' to execute you
exe...either map a drive to you sql server or provide the unc path.......
> Brad Feaker
> DBA
>

DTS to start a VB job on another server

When an entry is done on a table that has a trigger tied
to it, I would like to execute a Visual Basic program that
resides on another server. I tries making a DTS package
with the Execute Process task property. There is no
ability to pass a userid/password for a logon. I'm also
not sure, if this was even possible, would the program run
on that server or would is be pulled to the SQL Server and
actually run there?
Any info on if this is possible would be appreciated. If
there is some other method to allow an update to an entry
in the database trigger the running of the .exe on a
different server, that would be great to know.
TIA,
DougTry using the extended stored proc 'xp_cmdshell' to execute you exe...either map a drive to you sql server or provide the unc path......
Brad Feake
DB|||On the Win2k developement kit CD there is a file call exec.vbs which will
start a program on a remote server. The program will run in the remote
server's environment not the calling server. Use xp_cmdshell to start
exec.vbs.
"Brad Feaker" <anonymous@.discussions.microsoft.com> wrote in message
news:8B7FD9BF-8DBC-407E-B34C-44D82FBC5410@.microsoft.com...
> Try using the extended stored proc 'xp_cmdshell' to execute you
exe...either map a drive to you sql server or provide the unc path.......
> Brad Feaker
> DBA
>