Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Trouble with imported tables in SQL Server Express 2012

Posted on 2014-02-16
7
Medium Priority
?
924 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 2000 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

916 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