Solved

Trouble with imported tables in SQL Server Express 2012

Posted on 2014-02-16
7
846 Views
Last Modified: 2014-02-20
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
0
Comment
Question by:Eric Bourland
  • 4
  • 3
7 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39862856
"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."
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.

Also, with large tables you are better off scripting it out as the designer will timeout after 30 seconds.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 39862866
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39862882
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.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 3

Author Comment

by:Eric Bourland
ID: 39862908
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
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 39863424
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39863806
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.
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
ID: 39874453
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
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now