UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Design Change - Save Error, SQL Server 2012    
 
   
johnpdmccall
post Nov 4 2018, 06:49 AM
Post#1



Posts: 1,795
Joined: 14-March 00
From: Ayrshire, Scotland


Hi Folks

I'm using Microsoft SQL Server Management to try to change the length of a field (nvarchar) in an Azure SQL database table and getting the error in the attached pic.
The tables were migrated using MSMA for Access.
Any ideas what settings I need to change to enable table design edits?

Thanks for any help

Attached File  SQLDesignChangeError.JPG ( 24.89K )Number of downloads: 0

--------------------
Cheers,
John
Go to the top of the page
 
GroverParkGeorge
post Nov 4 2018, 07:01 AM
Post#2


UA Admin
Posts: 34,671
Joined: 20-June 02
From: Newcastle, WA


Attached File  DesignChanges.jpg ( 147.6K )Number of downloads: 6

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
johnpdmccall
post Nov 4 2018, 07:21 AM
Post#3



Posts: 1,795
Joined: 14-March 00
From: Ayrshire, Scotland


Thanks very much George,

I just couldn't find that.

Will I lose the data in the table?
If so is there a way to adjust the field size (making it bigger) without losing data in the table?

I have backups and can migrate the data into the re-created table if I must.
This post has been edited by johnpdmccall: Nov 4 2018, 07:53 AM

--------------------
Cheers,
John
Go to the top of the page
 
GroverParkGeorge
post Nov 4 2018, 08:15 AM
Post#4


UA Admin
Posts: 34,671
Joined: 20-June 02
From: Newcastle, WA


That's a good question; one that I hadn't given much thought. You can SCRIPT changes with TSQL in any event, so it's obviously not impossible. I think it's time for a quick Bingoogle search.

I can't promise there will not be problems, but I've never experienced any, and as noted you can do the same thing with a TSQL statement.





--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Nov 4 2018, 08:24 AM
Post#5


UA Admin
Posts: 34,671
Joined: 20-June 02
From: Newcastle, WA


Well, the best answer I found on a quick search is that it's a way to slow naive users down. I.e. some changes require SQL Server to create a temp table for the data, drop the old table and create a new one with your modification and then put the data back. That may take a LONG time and lock up the database for users while it's going on. If you insist you know what you're doing you can override this precaution.

There may be more technical factors, but that one sure makes sense to me.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
johnpdmccall
post Nov 4 2018, 08:29 AM
Post#6



Posts: 1,795
Joined: 14-March 00
From: Ayrshire, Scotland


Hi George,

I've got a backup and I'm brave! *
I'll give it a go because I don't know how to write TSQL statements. Need to learn!

* If [John McCall].value = "Brave"
Then Brave = "Stupid"

ohyeah.gif

--------------------
Cheers,
John
Go to the top of the page
 
GroverParkGeorge
post Nov 4 2018, 08:30 AM
Post#7


UA Admin
Posts: 34,671
Joined: 20-June 02
From: Newcastle, WA


Backups are your friend.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
johnpdmccall
post Nov 5 2018, 06:30 AM
Post#8



Posts: 1,795
Joined: 14-March 00
From: Ayrshire, Scotland


Thanks for your help George,

I edited some of table design last night without any problems.
Data was all intact too although I wouldn't want to carry out any changes during office hours because I don't know what impact it may have if the database is being used live.

--------------------
Cheers,
John
Go to the top of the page
 
WayneRyan
post Nov 19 2018, 12:09 AM
Post#9



Posts: 109
Joined: 21-February 03
From: Camarillo, CA


John,

A simple schematic change: alter table alter column SomeColumn Bigint.
That can be done "in place", with just that command.

More complex changes require that a new table be created.
If your table has indexes, foreign keys, triggers, etc. then you'll have to
Preserve those.

You can script these steps out ... or let the Server do it "behind the scenes",
IF you select the option George showed earlier.

Just be sure that you know the pieces and check up on what SSMS has done.
We've lost foreign keys about five times and they were all preceded by this
Scenario.

Hth,
Wayne
This post has been edited by WayneRyan: Nov 19 2018, 12:09 AM
Go to the top of the page
 
johnpdmccall
post Nov 19 2018, 06:07 AM
Post#10



Posts: 1,795
Joined: 14-March 00
From: Ayrshire, Scotland


Thanks Wayne thumbup.gif

--------------------
Cheers,
John
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th March 2019 - 04:25 PM