adding a filed in table is impossible!!

Question

adding a filed in table is impossible!!

nazaninahmady_sh

Default port

Points: 1465
More actions
December 3, 2013 at 11:27 pm

#301931

Hi
I want to add a filed in my table , but I receive an error , this is : save as text file !!!!
and I have to delete all my DB and create it again, would you please help me?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What exactly are you trying to do, what is the exact error you're getting?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PRAMANA.DBA SSCertifiable Points: 5507 More actions · Answer 2

I think u r trying to add column from designer ?

Add column by using sql script

Ex:

ALTER TABLE Table_Name ADD COLUMN_NAME INT

If u want add column from designer , before adding do this changes ,

Tools---> Options--->Designers-->

Un-check this check box: Prevent Saving changes that require table recreation and click ok

nazaninahmady_sh Default port Points: 1465 More actions · Answer 3

Yes, Exactlty , I want to add a column , of course I have created my table before , an it has data , but now I want to add a column to my table an it does not permit me, it said : save as text file.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

'Save as text file' is not an error. It's very hard to help you without knowing exactly what you're doing and having a description of the error you're getting.

Are you using the GUI designer to add a column?

If so, what is the error you are getting ('save as text file isn't an error')

Have you tried using T-SQL to add the column?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nazaninahmady_sh Default port Points: 1465 More actions · Answer 5

Yes, in design mode I want to add a column, I said it is error because it does not add a column really, It wants to save my table as a text in my hard , without changing my table , I think it is because of permission , do u think so?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

I don't know if it's due to permission because you still haven't posted the error. If there's an error, it would have been displayed in a dialog.

Have you tried using the T-SQL command instead of the designer?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Knight SSCommitted Points: 1856 More actions · Answer 7

Sounds like you're trying to add a column mid-table which is causing a table rebuild and hence it won't automatically do this unless you de-select the option from => Tools => Options => Designer = Untick 'Prevent saving changes that require table re-creation'.

You really shouldn't be adding columns mid-table however, an ALTER TABLE statement is better. Imagine trying this with a billion row production table. The DBA's wouldn't allow it for starters 😉

Ratheesh4sql SSCommitted Points: 1811 More actions · Answer 8

Yes,try to do that

Options => Designer = Untick 'Prevent saving changes that require table re-creation'. it will work.!!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

Or you could skip the GUI completely and use the following, changing integer to the data type you need.

ALTER TABLE table_name ADD column_name integer;

http://msdn.microsoft.com/en-us/library/ms190273%28v=sql.100%29.aspx

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Umer Akram SSC-Insane Points: 22209 More actions · Answer 10

Ed Wagner (12/4/2013)
Or you could skip the GUI completely and use the following, changing integer to the data type you need.
ALTER TABLE table_name ADD column_name integer;
http://msdn.microsoft.com/en-us/library/ms190273%28v=sql.100%29.aspx

+1 , its way better this way.