Link to home
Start Free TrialLog in
Avatar of Eric Bourland
Eric BourlandFlag for United States of America

asked on

Trouble with imported tables in SQL Server Express 2012

MS SQL Server 2012

Hi, friends. I could use a little help.

I imported data tables from MS SQL Server 2005 on a server at HostMySite.com, into MS SQL Server 2012 on a server at viviotech.net. However, the imported data tables are not quite right. There are two problems with each table:

1) no primary key is set -- for example, PageID is no longer marked Primary Key

2) I cannot set "Is Identity" to "Yes", or set Identity Increment to a value of "1"

When I set these things manually and try to save the design of the table, I get an error: "Saving changes is not permitted. The changes you have made require the following tables to be dropped and recreated. You have either made changes to a table that can't be recreated or enabled the option Prevent saving changes that require the table to be recreated."

I definitely need a Primary Key in my tables, and an Identity Increment value of 1.

Have you seen this problem before? I attach a screenshot.

Is there a way to import data into SQL Server 2012, and preserve Primary Key and "Is Identity" settings?

When I import tables, I use the MS SQL Server 2012 Import and Export Data Tool (64-bit).

Thank you very much for your help.

Eric
sql-server-error.jpg
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Eric Bourland

ASKER

Anthony, thank you for this reply.

>>>This was disabled by default in (I believe) SQL Server 2008.  In order to enable making these changes you need to remove the check in the check box: "Prevent saving changes that require table re-creation" under Tools|Options|Designers.

Do I look for this checkbox in SQL Server 2005 (which I am importing from) or SQL Server 2012 (which I am importing into)?

And, is there a way to just import the tables with Primary Key and Identity settings intact?

Eric
Do I look for this checkbox in SQL Server 2005 (which I am importing from) or SQL Server 2012 (which I am importing into)?
It is part of the UI in SSMS, so you need to look for it in SQL Server 2012.  (Besides I don't think it existed in SQL Server 2005).

And, is there a way to just import the tables with Primary Key and Identity settings intact?  You should be able to export the exact schema you had on SQL Server 2005.  What I would do is script out all the objects, create them in the new database and then copy the data.  There are third party tools that do a very efficient job of this, such as Red-Gate's SQL Compare and SQL Data Compare.
I found the checkbox!

>>>>What I would do is script out all the objects, create them in the new database and then copy the data.  There are third party tools that do a very efficient job of this, such as Red-Gate's SQL Compare and SQL Data Compare.

I will try this today -- this looks like a useful solution. I will work on this and return here later today.

Anthony, thanks so much.

best from Eric
Anthony,

The Red Gate products look very useful. I installed the SQL Compare and I saw the SQL that it generated to create a new table.

I don't think I can use SQL Compare because my task is a little ... specific. Currently I have one very large database with some hundreds of tables; each web site on my server uses a few of those tables. I am breaking up this very large database into many smaller ones.

In an ideal world, I could write a script that allowed me to copy selected tables from the larger database in a new database on the new server; while keeping PK and Identity settings intact.

What do you think such a script might look like? Maybe something like

      	CREATE TABLE [dbo].[#form.tablePrefix#Navigation] ( 	
        [PageID] [int] IDENTITY(1,1) NOT NULL
      ,PageTitle 			nvarchar(1024) NULL
      ,PageContentLeft 		nvarchar(MAX) NULL
      ,PageContentRight 	nvarchar(MAX) NULL
      ,DateCreated 			datetime NULL
      ,DateModified 		datetime NULL
      ,ParentID 			int NULL
      ,SortOrder 			int NULL
      ,keywords 			nvarchar(255) NULL
      ,description 			nvarchar(1024) NULL
      ,owner 				nvarchar(128) NULL
      ,author 				nvarchar(255) NULL
      ,titletag 			nvarchar(1024) NULL
      ,safeURL 				nvarchar(1024) NULL
      ,isHome 				bit NULL
      ,CONSTRAINT [PK_#form.tablePrefix#Navigation_PageID] PRIMARY KEY CLUSTERED
          (
                  [PageID] ASC
          )
            )

Open in new window


Though I am not sure how the content of the database would be copied over. I admit I do not know much about what I am doing here. =) Thank you for any ideas.

Eric
There are a couple of ways you can do this:
You can use SQL Compare to select the objects one be one and then script them out or you can use SSMS and the Object Explorer Details (F7) to do the same.

Once you have created the script you can run it on the new database and it will create all the objects.  Then it becomes very simple yo use SQL Data Compare to move the data.
Anthony, thank you very much for your thoughtful replies. I have decided to migrate the data tables manually -- in groups -- using the MS SQL Server 2012 Import and Export tool. This way I can doublecheck all tables and set up an independent datasource for each web site -- which will be more secure. I really appreciate your time. Have a great day.

Eric