• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 702
  • Last Modified:

error in upsizing a table from access to sql server 05

Im having trouble upzizing a MS Access DB to SQL Server 2005

One table in particular keeps failing to upsize.

The table is called "master" (if that has anything to do with my error?)

The table has 1987 rows so should be able to upsize???

Im not getting any error when I try and upsize it on its own. It just comes back and says

"table was skipped or export failed"

All the other tables upsize without error.

When I try and upsize them altoghether with the same relationship etc I get the following error on a few tables all connected to the master table

Server Error 1776: There are no primary or candidate keys in the referenced table 'Master' that match the referencing column list in the foreign key 'table1'.
Server Error 1750: Could not create constraint. See previous errors.

Anyone have any ideas??
0
jim_bob_jim
Asked:
jim_bob_jim
  • 5
  • 5
  • 3
  • +1
1 Solution
 
tonydemarcoCommented:
Create a primary key in the Master table.
Try an Autonumber field called "idx" and make it a key field.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Looks like you have a referential integrity problem.
See what table are relationated with master table and search for records that don't have a match there.
0
 
jim_bob_jimAuthor Commented:
there is a primary in the Master table and it is an auto number
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
adraughnCommented:
or you could chuck the upsizing tool, create your table in sql server management studio and import the data (right click on db in managment studio and click on tasks - import data)

that will save you from the grief of all these errors.

a
0
 
jim_bob_jimAuthor Commented:
Looks like you have a referential integrity problem.
See what table are relationated with master table and search for records that don't have a match there.:::

Should it not upsize correctly if you select it not to import the table releationship but just the data??

Just out of interest??

thanks
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You are right, but this message error is about references error:

Server Error 1776: There are no primary or candidate keys in the referenced table 'Master' that match the referencing column list in the foreign key 'table1'.

What's the 'table1'?
0
 
jim_bob_jimAuthor Commented:
Its just another table I was referring to.  I get that error 9 times all referencing the master table and other tables.

All facts are pointing to that being the error cause they all upsize perfectly when the master table is not included
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
And when you add table 'master' the error is "table was skipped or export failed"?
0
 
jim_bob_jimAuthor Commented:
yes
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I found this article http://support.microsoft.com/default.aspx/kb/153034
See if it helps you.

Good luck.
0
 
jim_bob_jimAuthor Commented:
No I had already come across this article.  It does not apply to my case

thanks
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Maybe you know this article too, but...
http://support.microsoft.com/default.aspx?scid=kb;EN-US;241743
0
 
adraughnCommented:
so if all of your other tables were imported with no errors, I would suggest adding the table manually. Sometimes Access and SQL can be quirky together. You have a workaround. Be glad you got what you did imported. I tried to use that tool once and got so many errors that I deceided it wasn't worth it and did it all manually. I wouldn't spend too much time on researching why it doesn't work, just go to plan B. Do you know how to add a table manually in sql? we can help if you need it. We can also help you with adding constraints, if needed. When you add the table in sql, just take time with your data types. Better to get them right the first time.

Since you are new to SQL, I would suggest using the gui interface to add the table. Just right click on 'Tables' in your new database, and click New. You can add the fields one at a time and select the appropriate data types. If you need help, post back. Here is some code for constraints (if you need more information let us know):

adria


USE DatabaseName
GO
--Check for constraints on a table
exec sp_helpconstraint TableName

--Add pk constraint
ALTER TABLE TableName
ADD CONSTRAINT PK_ConstraintName_iUsuallyUseFieldName PRIMARY KEY (FieldName)
GO

--Add fk constraint
ALTER TABLE ChildTableName
ADD CONSTRAINT FK_ConstraintName_iUsuallyUseFieldName FOREIGN KEY (FieldName)
      REFERENCES MainTableName(FieldName)
      ON UPDATE NO ACTION
      ON DELETE CASCADE

--Add unique constraint
ALTER TABLE TbaleName
ADD CONSTRAINT CN_ConstraintName UNIQUE (FieldName)

--Drop Constraint
ALTER TABLE TableName
DROP CONSTRAINT ConstraintName

--Add Check Constraint
ALTER TABLE TableName
ADD CONSTRAINT CK_ConstraintName
CHECK
(FiedlName <= LogicalExpr)
0
 
adraughnCommented:
note: the above are examples that should be done separately. That is not one procedure.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now