Re: Wierd Primary Key issue

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Keith Kratochvil (sqlguy.back2u_at_comcast.net)
Date: 01/27/05 

Date: Thu, 27 Jan 2005 13:47:41 -0600

Is the data within the orders table on princess5-14 unique? You don't
specify column names within the insert/select....are the columns in the same
order within both tables? The column(s) that make up the primary key: are
those unique within the production server? Yeah, it is a crazy question but
sometimes crazy things happen.

I don't use openquery with linked servers. I have used a method like this
in the past. Does it work for you?

TRUNCATE TABLE Orders
INSERT INTO Orders
SELECT * FROM [PRINCESS5-14].ORDERSDB.dbo.Orders WITH (NOLOCK) WHERE
OrderDate >= DATEADD(m ,-4,GETDATE()) 

-- 
Keith
"Evan Nelson" <EvanNelson@discussions.microsoft.com> wrote in message
news:FE2D9821-47AD-4845-9A1A-F47A3A4C9333@microsoft.com...
> I am trying to transfer selected data from my production server to my test
> server.  To do this, I have registered my production server as a linked
> server on my test server.
>
> I have written the following stored procedure, every time I have run it
> today I have received an error message that totally has me stumped.  Here
are
> the statements in the SP.
>
> TRUNCATE TABLE Orders
> INSERT INTO Orders
> SELECT * FROM OPENQUERY([PRINCESS5-14], 'SELECT * FROM ORDERSDB..Orders
WITH
> (NOLOCK) WHERE OrderDate >= DATEADD(m ,-4,GETDATE()) ')
>
> Every time I've run it today I have received the following error:
>
> Violation of PRIMARY KEY constraint 'PK_OrderID'. Cannot insert duplicate
> key in object 'Orders'.
>
> Except for the fact that I have removed all foreign key contraints from
the
> table in my test environment, the tables are exactly the same.  The
> production table has the exact same Primary Key as the test table. I have
> also run the following query against the production table and it verifies
> that the Primary Key constraint has not been violated there.
>
> SELECT Count(OrderID) as Total, OrderID FROM Orders GROUP BY OrderID
HAVING
> Count(OrderID) <> 1
>
> Both the production server and the test server are running SQL Server 2000
> with the lastest patches and Windows 2000 Server.
>
> Any help would be appreciated.  Have removed the Primary Key constraint
from
> my test server to see if the error goes away.

Relevant Pages

  • Re: Select from 2 Servers
    ... where is specified the Server? ... > You don't refer to the server, you refer to the Informix instance. ... >> columns in a table except the primary key could/should be nullable. ... > and you don't have a parent row for it, just do an insert into the parent ...
    (comp.databases.informix)
  • Re: Wierd Primary Key issue
    ... the production server and then I removed the foreign key restraints. ... I have written queries to access data on linked servers using 4-part names ... The columnthat make up the primary key: ...
    (microsoft.public.sqlserver.tools)
  • Re: Performance problems oracle 8i
    ... > processors, a bulk of memory) and a test server (win 2000, only one ... > processor, less memory). ... The production server should be faster than ... > But now we're trying the scripts on the production server and it all ...
    (comp.databases.oracle.misc)
  • Re: Grant Object Access
    ... Now that I had resolved the issues on my test server, ... and performing the various system changes on the new production server, ... I had a similiar problem with this on the test server. ... >> has no control over the scheduled tasks in themselves (all the user can ...
    (microsoft.public.windows.server.security)
  • Re: from test to production
    ... > how are you guys handling the deployment and settings from a test server ... > a production server? ...
    (microsoft.public.dotnet.framework.aspnet)