ALTER table option is not available

Does anyone know why the ALTER to option is not available on Query Analyzer, Enterprise Manager or MS SQL Server Management Studio?

On Query Analyzer and Enterprise Manager, this option is visible by right clicking on the table, selecting "Script object to New Window" and "Alter".

On MS SQL Server Management Studio, this option is visible by right clicking on the table, selecting "Script table as", then "Alter to" is visible but not available. I'm logged in under the db_owner role.

Any ideas?

Hi saleyoum,

This functionality should be the same in all versions of the tools you mentioned above (i.e. Query Analyzer, EM, SSMS)...meaning, you shouldn't be able to generate an alter table script from any of the GUI's...is that what you are seeing, or are you saying you are seeing it as possible in Query Analyzer and Enterprise Manager (you shouldn't be I hope :-))...

Basically, there are so many possibilities with altering a table, it would be near impossible to generate an alter script template to a new window/clipboard/etc....you could want to alter a column, multiple columns, all columns, add columns, drop columns, manage constraints (table and column level), change collations, compute/persist columns, switch partitions, enable/disable/manage triggers, etc., etc., etc...that's the part of the reason you don't see it enabled I'm sure...

HTH,

|||I understand your explanation but why have it visible for tables? Is this functionality only available for functions & stored procedures?|||

Well, the functionality to ALTER tables is available, you just don't get a fancy GUI menu option for it :-)...if you need to alter a table, you'll have to code the alter script yourself is all.

As for why to have it visible and disabled vs. invisible, not sure, would have to ask the GUI folks that one...probably just to be consistent with the options I guess...

HTH,

All Required Data Is Not Immediately Available - What to do?

What are some acceptable ways to deal with "required" data that is
unavailable at the time a row is added to a table?
Example: Lab tests are requested for a patient. Some results come in right
away and others come days later. All are eventually required in order for
the patient's lab records to be completed. It is unacceptable to the
customer to wait until ALL the values are available; they want to enter the
values AS they become available.
Obviously we cannot place a "NOT NULL" constraint on the columns in order to
allow for required data coming in late. I'd prefer to avoid having
application-level logic take care of this; but I currently don't see a way
around that.
Suggestions?
Thanks!"Fred Mertz" <A@.B.COM> wrote in message
news:%23DOJXeumGHA.856@.TK2MSFTNGP03.phx.gbl...
> What are some acceptable ways to deal with "required" data that is
> unavailable at the time a row is added to a table?
> Example: Lab tests are requested for a patient. Some results come in right
> away and others come days later. All are eventually required in order for
> the patient's lab records to be completed. It is unacceptable to the
> customer to wait until ALL the values are available; they want to enter
> the values AS they become available.
> Obviously we cannot place a "NOT NULL" constraint on the columns in order
> to allow for required data coming in late. I'd prefer to avoid having
> application-level logic take care of this; but I currently don't see a way
> around that.
> Suggestions?
> Thanks!
>
Like this for example:
CREATE TABLE Patients (PatientID INT NOT NULL PRIMARY KEY, PatientLastName
VARCHAR(35) NOT NULL /* ... etc */);
CREATE TABLE PatientTestResults (PatientID INT NOT NULL PRIMARY KEY
REFERENCES Patients (PatientID), Result VARCHAR(20) NOT NULL);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||So it sounds as if you want to enforce data validation before a status
can be changed or checked. If the process of changing status is manual
(i.e, a technician clicks a button that "signs off" on the data), you
could use a trigger to verify that data is complete before allowing
that status to be changed.
If the process is automatic (you just want to check if the status is
complete), you could run a check process in your stored procedure to
look for NULL tuples and report that status is Incomplete.
Tossing some ideas around.
Stu
Fred Mertz wrote:
> What are some acceptable ways to deal with "required" data that is
> unavailable at the time a row is added to a table?
> Example: Lab tests are requested for a patient. Some results come in right
> away and others come days later. All are eventually required in order for
> the patient's lab records to be completed. It is unacceptable to the
> customer to wait until ALL the values are available; they want to enter th
e
> values AS they become available.
> Obviously we cannot place a "NOT NULL" constraint on the columns in order
to
> allow for required data coming in late. I'd prefer to avoid having
> application-level logic take care of this; but I currently don't see a way
> around that.
> Suggestions?
> Thanks!|||RE:
<< Tossing some ideas around >>
Just what I was looking for. Your ideas are helpful.|||Just a guess here -REMOVE the NOT NULL constraint?
CREATE a 'holding' table that accepts the incomplete data, and move it to
the 'real' table when complete.
Alternatively, set a default value. If char/varchar datatype default to
'N/A' or whatever makes sense. If numeric datatype, and if zero has
significance, then default to a predetermined 'magic number' that signals
the data is still missing, e.g., 999.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Fred Mertz" <A@.B.COM> wrote in message
news:%23DOJXeumGHA.856@.TK2MSFTNGP03.phx.gbl...
> What are some acceptable ways to deal with "required" data that is
> unavailable at the time a row is added to a table?
> Example: Lab tests are requested for a patient. Some results come in right
> away and others come days later. All are eventually required in order for
> the patient's lab records to be completed. It is unacceptable to the
> customer to wait until ALL the values are available; they want to enter
> the values AS they become available.
> Obviously we cannot place a "NOT NULL" constraint on the columns in order
> to allow for required data coming in late. I'd prefer to avoid having
> application-level logic take care of this; but I currently don't see a way
> around that.
> Suggestions?
> Thanks!
>

All free memory gone...

Hi!
I am running MS SQL 2000 Ent sp3 + MS Windows 2000 Datacenter on a
server with 16Gb memory. Total available memory is typically ~200MB
and pretty stable. I use Max Server Memory of 15700MB. Today I could
see a very unusual behavior: the available memory gone from 200MB to
4MB in few seconds. I decreased the Max Server Memory by 200MB:
sp_configure [max server memory (MB)], 15500
reconfigure with override
go
It did not help much: it has 8MB available memory now. The server has
a lot of paging.
What is happening to the server?
Thanks.You should run Windows performance monitor and see who is using the
memory... It is in the processes section...
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0312010553.2bf2d450@.posting.google.com...
> Hi!
> I am running MS SQL 2000 Ent sp3 + MS Windows 2000 Datacenter on a
> server with 16Gb memory. Total available memory is typically ~200MB
> and pretty stable. I use Max Server Memory of 15700MB. Today I could
> see a very unusual behavior: the available memory gone from 200MB to
> 4MB in few seconds. I decreased the Max Server Memory by 200MB:
> sp_configure [max server memory (MB)], 15500
> reconfigure with override
> go
> It did not help much: it has 8MB available memory now. The server has
> a lot of paging.
> What is happening to the server?
> Thanks.|||AWE memory is not dynamic so changing max server memory won't have any
effect until you restart the service. You really need to leave more room for
the OS, try setting max server memory to 14 GB and monitoring its stability.
Also make sure the /3GB switch is not in boot.ini - just use the /PAE
switch. There is a fair bit of OS overhead in managing AWE memory so you
need to give the OS room to breathe.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0312010553.2bf2d450@.posting.google.com...
Hi!
I am running MS SQL 2000 Ent sp3 + MS Windows 2000 Datacenter on a
server with 16Gb memory. Total available memory is typically ~200MB
and pretty stable. I use Max Server Memory of 15700MB. Today I could
see a very unusual behavior: the available memory gone from 200MB to
4MB in few seconds. I decreased the Max Server Memory by 200MB:
sp_configure [max server memory (MB)], 15500
reconfigure with override
go
It did not help much: it has 8MB available memory now. The server has
a lot of paging.
What is happening to the server?
Thanks.|||Performance monitor does not show processes that eat that much memory.
It does not show memory usage correctly with AWE enabled.
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message news:<uZllf7BuDHA.3536@.tk2msftngp13.phx.gbl>...
> You should run Windows performance monitor and see who is using the
> memory... It is in the processes section...
>
> "Roust_m" <roustam@.hotbox.ru> wrote in message
> news:a388fd78.0312010553.2bf2d450@.posting.google.com...
> > Hi!
> >
> > I am running MS SQL 2000 Ent sp3 + MS Windows 2000 Datacenter on a
> > server with 16Gb memory. Total available memory is typically ~200MB
> > and pretty stable. I use Max Server Memory of 15700MB. Today I could
> > see a very unusual behavior: the available memory gone from 200MB to
> > 4MB in few seconds. I decreased the Max Server Memory by 200MB:
> >
> > sp_configure [max server memory (MB)], 15500
> > reconfigure with override
> > go
> >
> > It did not help much: it has 8MB available memory now. The server has
> > a lot of paging.
> >
> > What is happening to the server?
> >
> > Thanks.|||It did work fine for 3 months with 200MB available memory.
This article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;274750
states that you need 1GB for OS only if you have 32+GB RAM:
"When you allocate SQL Server AWE memory on a 32 GB system, Windows
2000 may require at least 1 GB memory to manage AWE. "
As for /3G switch, I also seen an article stating that you need it for
up to 16GB RAM.
Anyway it did work fine...
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<OFS92FEuDHA.3436@.tk2msftngp13.phx.gbl>...
> AWE memory is not dynamic so changing max server memory won't have any
> effect until you restart the service. You really need to leave more room for
> the OS, try setting max server memory to 14 GB and monitoring its stability.
> Also make sure the /3GB switch is not in boot.ini - just use the /PAE
> switch. There is a fair bit of OS overhead in managing AWE memory so you
> need to give the OS room to breathe.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Roust_m" <roustam@.hotbox.ru> wrote in message
> news:a388fd78.0312010553.2bf2d450@.posting.google.com...
> Hi!
> I am running MS SQL 2000 Ent sp3 + MS Windows 2000 Datacenter on a
> server with 16Gb memory. Total available memory is typically ~200MB
> and pretty stable. I use Max Server Memory of 15700MB. Today I could
> see a very unusual behavior: the available memory gone from 200MB to
> 4MB in few seconds. I decreased the Max Server Memory by 200MB:
> sp_configure [max server memory (MB)], 15500
> reconfigure with override
> go
> It did not help much: it has 8MB available memory now. The server has
> a lot of paging.
> What is happening to the server?
> Thanks.|||Windows 2000 Datacenter Server Does Not Locate Memory Greater Than 16 GB
http://support.microsoft.com/default.aspx?scid=kb;EN-US;292934
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<OFS92FEuDHA.3436@.tk2msftngp13.phx.gbl>...
> AWE memory is not dynamic so changing max server memory won't have any
> effect until you restart the service. You really need to leave more room for
> the OS, try setting max server memory to 14 GB and monitoring its stability.
> Also make sure the /3GB switch is not in boot.ini - just use the /PAE
> switch. There is a fair bit of OS overhead in managing AWE memory so you
> need to give the OS room to breathe.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Roust_m" <roustam@.hotbox.ru> wrote in message
> news:a388fd78.0312010553.2bf2d450@.posting.google.com...
> Hi!
> I am running MS SQL 2000 Ent sp3 + MS Windows 2000 Datacenter on a
> server with 16Gb memory. Total available memory is typically ~200MB
> and pretty stable. I use Max Server Memory of 15700MB. Today I could
> see a very unusual behavior: the available memory gone from 200MB to
> 4MB in few seconds. I decreased the Max Server Memory by 200MB:
> sp_configure [max server memory (MB)], 15500
> reconfigure with override
> go
> It did not help much: it has 8MB available memory now. The server has
> a lot of paging.
> What is happening to the server?
> Thanks.