Duplicate records - no error message?

Hi,
I am in the process of moving my back end to the SQL server. I have a test
version in the SQLserver and the front end is in Access 2000 with linked
tables.
When the user enters a duplicate key (primary key) and the user tries to
save the record in Access (using a save button), an error message is
displayed by Jet.
The same kind of error message does not appear when the back end is in SQL
server. The duplicate record does not get stored as well.
Why is this happening?> The same kind of error message does not appear when the back end is in SQL
> server. The duplicate record does not get stored as well.
> Why is this happening?
Well, what does the statement look like? Did you use profiler to see what
is going into and coming out of SQL Server?|||Hi,
Thanks for responsing.
No...I am a newbie to SQL server and don't know how to do it.
Access (jet) gives an error message when I try to save a duplicate record. -
"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or relationship".
This message appears when the f/e and the b/e are Access.
When the b/e is SQL for the same table and Access is the front end..no error
message appears.
Do you suggest I learn how to use a profiler and figure it out?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Well, what does the statement look like? Did you use profiler to see what
> is going into and coming out of SQL Server?
>
>|||Hi
Since you have not provided your INSERT statement ,see Itzik Ben-Gan's
exanple to deal with dulicates
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Priya Henry" <PriyaHenry@.discussions.microsoft.com> wrote in message
news:3A756C75-F872-4168-A497-905031937F0B@.microsoft.com...
> Hi,
> Thanks for responsing.
> No...I am a newbie to SQL server and don't know how to do it.
> Access (jet) gives an error message when I try to save a duplicate
> record. -
> "The changes you requested to the table were not successful because they
> would create duplicate values in the index, primary key or relationship".
> This message appears when the f/e and the b/e are Access.
> When the b/e is SQL for the same table and Access is the front end..no
> error
> message appears.
> Do you suggest I learn how to use a profiler and figure it out?
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Does your table in SQL Server have a unique index on the relevant column?
"Priya Henry" wrote:
> Hi,
> Thanks for responsing.
> No...I am a newbie to SQL server and don't know how to do it.
> Access (jet) gives an error message when I try to save a duplicate record.
-
> "The changes you requested to the table were not successful because they
> would create duplicate values in the index, primary key or relationship".
> This message appears when the f/e and the b/e are Access.
> When the b/e is SQL for the same table and Access is the front end..no err
or
> message appears.
> Do you suggest I learn how to use a profiler and figure it out?
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Yes...My table does have an unique index. Is it possible that Jet is
stopping the message?
"NH" wrote:
> Does your table in SQL Server have a unique index on the relevant column?
> "Priya Henry" wrote:
>|||I havnt seen this before. Our system has a SQL Server backend and Access
front end and the error message do appear when trying to add in a duplicate
into a unique undexed column.
Have you checked the linked tables in Access, are they pointing to the
correct SQL Server database?
"Priya Henry" wrote:
> Yes...My table does have an unique index. Is it possible that Jet is
> stopping the message?
> "NH" wrote:
>sql

Dual Core Processor

We are in process of moving to 64 bit HP servers with sql2005 standard
edition. We were just wondering which is better option, to get a server with
2 dual core processor or to get a srver with just 4 processor? How does
SQL2005 handle the hypertheading of dual processor?
Thanks,
CarlosDual core processors and hyperthreaded processors are totally different
things. Hyperthreading actually adds a "virtual" core, and is not leveraged
very well by SQL Server. Multicore processors, on the other hand, actually
behave like multiple processors -- two cores gives you two processors worth
of power. The great thing in terms of SQL Server licensing is that you only
need to pay per socket, rather than per core. So buying two dual core
processors will save you a substantial amount of money over buying four
single-core processors. And now you can buy quad-core processors, so why
not just get ONE of those?
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:EB6E4323-8944-476E-A01B-D85B158BBB3F@.microsoft.com...
> We are in process of moving to 64 bit HP servers with sql2005 standard
> edition. We were just wondering which is better option, to get a server
> with
> 2 dual core processor or to get a srver with just 4 processor? How does
> SQL2005 handle the hypertheading of dual processor?
> Thanks,
> Carlos|||Thanks Adam, that makes sense
"Adam Machanic" wrote:
> Dual core processors and hyperthreaded processors are totally different
> things. Hyperthreading actually adds a "virtual" core, and is not leveraged
> very well by SQL Server. Multicore processors, on the other hand, actually
> behave like multiple processors -- two cores gives you two processors worth
> of power. The great thing in terms of SQL Server licensing is that you only
> need to pay per socket, rather than per core. So buying two dual core
> processors will save you a substantial amount of money over buying four
> single-core processors. And now you can buy quad-core processors, so why
> not just get ONE of those?
>
> --
> Adam Machanic
> SQL Server MVP - http://sqlblog.com
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Carlos" <carlosarango@.SQLserver.com> wrote in message
> news:EB6E4323-8944-476E-A01B-D85B158BBB3F@.microsoft.com...
> > We are in process of moving to 64 bit HP servers with sql2005 standard
> > edition. We were just wondering which is better option, to get a server
> > with
> > 2 dual core processor or to get a srver with just 4 processor? How does
> > SQL2005 handle the hypertheading of dual processor?
> >
> > Thanks,
> >
> > Carlos
>

Dual Core Processor

We are in process of moving to 64 bit HP servers with sql2005 standard edition. We were just wondering which is better option, to get a server with 2 dual core processor or to get a srver with just 4 processor? How does SQL2005 handle the hypertheading of dual processor?

Thanks,

CarlosWe are in process of moving to 64 bit HP servers with sql2005 standard edition. We were just wondering which is better option, to get a server with 2 dual core processor or to get a srver with just 4 processor? How does SQL2005 handle the hypertheading of dual processor?

Thanks,

Carlos
How about a Quad processor?

First; it will reduce the load on the mother board's Memory Bus. AMD's data-sheet on 4-core mention that "competitors" (Intel) behave more like two duo-core processors on the same backplane, but I'm not sure how far that goes but it implies that performance wise (at the hardware layer) that Intel 2-Core is similar to two 2-Core processors.

Second; Microsoft only counts it as a single CPU. So; anything licensed (from MS) by CPU will save substantially on licensing fees.

I don't have the link, so here's the quote:

SQL Server Multicore Licensing Policy
Published: August 1, 2005

Multicore processors, which consist of multiple processing execution
units or "cores" on one chip, promise to boost computing power, allowing
servers, workstations, and PCs to perform more functions simultaneously.
By the end of 2006, Intel expects more than 80 percent of its server
products to be shipping with multicore technology. Because most
server-side software is licensed "per processor," it has caused
confusion among some software vendors regarding whether to charge their
customers "per processor" or "per core."

Microsoft has been driving thought leadership in this area by charging
the same amount per processor, regardless of how many cores are in the
processor. Microsoft was the first database vendor to make this
announcement, in October of 2004, and continues to be the only vendor to
date that has taken this position. This strategy is based on the belief
that multicore processors are a natural extension of Moore's Law (that
the number of transistors on a chip doubles about every one to two
years), and that the benefits should be passed on directly to customers.

For example, if you are using SQL Server Enterprise Edition on a
four-processor server with dual-core processors, using all eight cores
(two cores x four processors), you will require only four processor
licenses. That can mean substantial savings for customers of Microsoft
compared to its competitors.

Dual Core Processor

We are in process of moving to 64 bit HP servers with sql2005 standard
edition. We were just wondering which is better option, to get a server with
2 dual core processor or to get a srver with just 4 processor? How does
SQL2005 handle the hypertheading of dual processor?
Thanks,
Carlos
Dual core processors and hyperthreaded processors are totally different
things. Hyperthreading actually adds a "virtual" core, and is not leveraged
very well by SQL Server. Multicore processors, on the other hand, actually
behave like multiple processors -- two cores gives you two processors worth
of power. The great thing in terms of SQL Server licensing is that you only
need to pay per socket, rather than per core. So buying two dual core
processors will save you a substantial amount of money over buying four
single-core processors. And now you can buy quad-core processors, so why
not just get ONE of those?

Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:EB6E4323-8944-476E-A01B-D85B158BBB3F@.microsoft.com...
> We are in process of moving to 64 bit HP servers with sql2005 standard
> edition. We were just wondering which is better option, to get a server
> with
> 2 dual core processor or to get a srver with just 4 processor? How does
> SQL2005 handle the hypertheading of dual processor?
> Thanks,
> Carlos
|||Thanks Adam, that makes sense
"Adam Machanic" wrote:

> Dual core processors and hyperthreaded processors are totally different
> things. Hyperthreading actually adds a "virtual" core, and is not leveraged
> very well by SQL Server. Multicore processors, on the other hand, actually
> behave like multiple processors -- two cores gives you two processors worth
> of power. The great thing in terms of SQL Server licensing is that you only
> need to pay per socket, rather than per core. So buying two dual core
> processors will save you a substantial amount of money over buying four
> single-core processors. And now you can buy quad-core processors, so why
> not just get ONE of those?
>
> --
> Adam Machanic
> SQL Server MVP - http://sqlblog.com
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Carlos" <carlosarango@.SQLserver.com> wrote in message
> news:EB6E4323-8944-476E-A01B-D85B158BBB3F@.microsoft.com...
>

Dual Core Processor

We are in process of moving to 64 bit HP servers with sql2005 standard
edition. We were just wondering which is better option, to get a server with
2 dual core processor or to get a srver with just 4 processor? How does
SQL2005 handle the hypertheading of dual processor?
Thanks,
CarlosDual core processors and hyperthreaded processors are totally different
things. Hyperthreading actually adds a "virtual" core, and is not leveraged
very well by SQL Server. Multicore processors, on the other hand, actually
behave like multiple processors -- two cores gives you two processors worth
of power. The great thing in terms of SQL Server licensing is that you only
need to pay per socket, rather than per core. So buying two dual core
processors will save you a substantial amount of money over buying four
single-core processors. And now you can buy quad-core processors, so why
not just get ONE of those?
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:EB6E4323-8944-476E-A01B-D85B158BBB3F@.microsoft.com...
> We are in process of moving to 64 bit HP servers with sql2005 standard
> edition. We were just wondering which is better option, to get a server
> with
> 2 dual core processor or to get a srver with just 4 processor? How does
> SQL2005 handle the hypertheading of dual processor?
> Thanks,
> Carlos|||Thanks Adam, that makes sense
"Adam Machanic" wrote:

> Dual core processors and hyperthreaded processors are totally different
> things. Hyperthreading actually adds a "virtual" core, and is not leverag
ed
> very well by SQL Server. Multicore processors, on the other hand, actuall
y
> behave like multiple processors -- two cores gives you two processors wort
h
> of power. The great thing in terms of SQL Server licensing is that you on
ly
> need to pay per socket, rather than per core. So buying two dual core
> processors will save you a substantial amount of money over buying four
> single-core processors. And now you can buy quad-core processors, so why
> not just get ONE of those?
>
> --
> Adam Machanic
> SQL Server MVP - http://sqlblog.com
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Carlos" <carlosarango@.SQLserver.com> wrote in message
> news:EB6E4323-8944-476E-A01B-D85B158BBB3F@.microsoft.com...
>