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,

alter table

I am using MS SQL server 2005 management studio. When I go to the "script
table as", the "alter to" is grey out but the other options such as "create
to", "select to" and etc ... are all available. I already login as sa. Can
anyone please help? Thanks.
00KobeBrian wrote:
> I am using MS SQL server 2005 management studio. When I go to the "script
> table as", the "alter to" is grey out but the other options such as "create
> to", "select to" and etc ... are all available. I already login as sa. Can
> anyone please help? Thanks.
You can script procs, views and functions as ALTER scripts. You can't
do that with tables because there is no equivalent statement for
tables. For example the ALTER PROCEDURE statement really means
"recreate the entire procedure". ALTER TABLE on the other hand just
adds to the existing table structure.
The nearest equivalent would be to DROP and then CREATE the table - but
of course you lose your data if you do that.
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
|||OK. Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1163055114.197968.327260@.i42g2000cwa.googlegr oups.com...
> 00KobeBrian wrote:
>
> You can script procs, views and functions as ALTER scripts. You can't
> do that with tables because there is no equivalent statement for
> tables. For example the ALTER PROCEDURE statement really means
> "recreate the entire procedure". ALTER TABLE on the other hand just
> adds to the existing table structure.
> The nearest equivalent would be to DROP and then CREATE the table - but
> of course you lose your data if you do that.
> --
> 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
> --
>

alter table

I am using MS SQL server 2005 management studio. When I go to the "script
table as", the "alter to" is grey out but the other options such as "create
to", "select to" and etc ... are all available. I already login as sa. Can
anyone please help? Thanks.00KobeBrian wrote:
> I am using MS SQL server 2005 management studio. When I go to the "script
> table as", the "alter to" is grey out but the other options such as "create
> to", "select to" and etc ... are all available. I already login as sa. Can
> anyone please help? Thanks.
You can script procs, views and functions as ALTER scripts. You can't
do that with tables because there is no equivalent statement for
tables. For example the ALTER PROCEDURE statement really means
"recreate the entire procedure". ALTER TABLE on the other hand just
adds to the existing table structure.
The nearest equivalent would be to DROP and then CREATE the table - but
of course you lose your data if you do that.
--
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
--|||OK. Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1163055114.197968.327260@.i42g2000cwa.googlegroups.com...
> 00KobeBrian wrote:
>> I am using MS SQL server 2005 management studio. When I go to the "script
>> table as", the "alter to" is grey out but the other options such as
>> "create
>> to", "select to" and etc ... are all available. I already login as sa.
>> Can
>> anyone please help? Thanks.
>
> You can script procs, views and functions as ALTER scripts. You can't
> do that with tables because there is no equivalent statement for
> tables. For example the ALTER PROCEDURE statement really means
> "recreate the entire procedure". ALTER TABLE on the other hand just
> adds to the existing table structure.
> The nearest equivalent would be to DROP and then CREATE the table - but
> of course you lose your data if you do that.
> --
> 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
> --
>

alter table

I am using MS SQL server 2005 management studio. When I go to the "script
table as", the "alter to" is grey out but the other options such as "create
to", "select to" and etc ... are all available. I already login as sa. Can
anyone please help? Thanks.00KobeBrian wrote:
> I am using MS SQL server 2005 management studio. When I go to the "script
> table as", the "alter to" is grey out but the other options such as "creat
e
> to", "select to" and etc ... are all available. I already login as sa. Can
> anyone please help? Thanks.
You can script procs, views and functions as ALTER scripts. You can't
do that with tables because there is no equivalent statement for
tables. For example the ALTER PROCEDURE statement really means
"recreate the entire procedure". ALTER TABLE on the other hand just
adds to the existing table structure.
The nearest equivalent would be to DROP and then CREATE the table - but
of course you lose your data if you do that.
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
--|||OK. Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1163055114.197968.327260@.i42g2000cwa.googlegroups.com...
> 00KobeBrian wrote:
>
> You can script procs, views and functions as ALTER scripts. You can't
> do that with tables because there is no equivalent statement for
> tables. For example the ALTER PROCEDURE statement really means
> "recreate the entire procedure". ALTER TABLE on the other hand just
> adds to the existing table structure.
> The nearest equivalent would be to DROP and then CREATE the table - but
> of course you lose your data if you do that.
> --
> 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
> --
>

alter table

I am using MS SQL server 2005 management studio. When I go to the "script
table as", the "alter to" is grey out but the other options such as "create
to", "select to" and etc ... are all available. I already login as sa. Can
anyone please help? Thanks.00KobeBrian wrote:
> I am using MS SQL server 2005 management studio. When I go to the "script
> table as", the "alter to" is grey out but the other options such as "create
> to", "select to" and etc ... are all available. I already login as sa. Can
> anyone please help? Thanks.
You can script procs, views and functions as ALTER scripts. You can't
do that with tables because there is no equivalent statement for
tables. For example the ALTER PROCEDURE statement really means
"recreate the entire procedure". ALTER TABLE on the other hand just
adds to the existing table structure.
The nearest equivalent would be to DROP and then CREATE the table - but
of course you lose your data if you do that.
--
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
--|||OK. Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1163055114.197968.327260@.i42g2000cwa.googlegroups.com...
> 00KobeBrian wrote:
>> I am using MS SQL server 2005 management studio. When I go to the "script
>> table as", the "alter to" is grey out but the other options such as
>> "create
>> to", "select to" and etc ... are all available. I already login as sa.
>> Can
>> anyone please help? Thanks.
>
> You can script procs, views and functions as ALTER scripts. You can't
> do that with tables because there is no equivalent statement for
> tables. For example the ALTER PROCEDURE statement really means
> "recreate the entire procedure". ALTER TABLE on the other hand just
> adds to the existing table structure.
> The nearest equivalent would be to DROP and then CREATE the table - but
> of course you lose your data if you do that.
> --
> 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
> --
>

ALTER SQL Server 2000 functions on SQL Express

I installed SQL Server Management Express and try to access to a SQL Server 2000 remotly.

I connected correctly but when I try to alter an existing function, I get the following error message:

TITLE: Microsoft SQL Server Management Studio Express

Property AnsiNullsStatus is not available for UserDefinedFunction '[dbo].[functionName]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Express.Smo)

Note that functionName contain the correct function name.

Can someone confirm me that SQL Server Managmt Express can not alter function from previous SQL version or may I need to set some parameters on Server 2000.

Thanks

I'm moving this to the Tools forum.

Mike

alter login disabel

hello all,
When I RT click a login in management studio \script login as \create to new
query window\ the script generated has
ALTER LOGIN [ID] DISABLE
after the creat login statement. I know the login was created enabled.
Is this a feature?I'm not sure I completely understand and/or agree, but did you notice the
comment:
/* For security reasons the login is created disabled and with a random
password. */
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
> hello all,
> When I RT click a login in management studio \script login as \create to
> new
> query window\ the script generated has
> ALTER LOGIN [ID] DISABLE
> after the creat login statement. I know the login was created enabled.
> Is this a feature?|||Thanks Aaron
Just proves that it helps to read the comments.
Joe
"Aaron Bertrand [SQL Server MVP]" wrote:
> I'm not sure I completely understand and/or agree, but did you notice the
> comment:
> /* For security reasons the login is created disabled and with a random
> password. */
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
> > hello all,
> >
> > When I RT click a login in management studio \script login as \create to
> > new
> > query window\ the script generated has
> >
> > ALTER LOGIN [ID] DISABLE
> >
> > after the creat login statement. I know the login was created enabled.
> > Is this a feature?
>
>

alter login disabel

hello all,
When I RT click a login in management studio \script login as \create to new
query window\ the script generated has
ALTER LOGIN [ID] DISABLE
after the creat login statement. I know the login was created enabled.
Is this a feature?
I'm not sure I completely understand and/or agree, but did you notice the
comment:
/* For security reasons the login is created disabled and with a random
password. */
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
> hello all,
> When I RT click a login in management studio \script login as \create to
> new
> query window\ the script generated has
> ALTER LOGIN [ID] DISABLE
> after the creat login statement. I know the login was created enabled.
> Is this a feature?
|||Thanks Aaron
Just proves that it helps to read the comments.
Joe
"Aaron Bertrand [SQL Server MVP]" wrote:

> I'm not sure I completely understand and/or agree, but did you notice the
> comment:
> /* For security reasons the login is created disabled and with a random
> password. */
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
>
>

alter login disabel

hello all,
When I RT click a login in management studio \script login as \create to new
query window\ the script generated has
ALTER LOGIN [ID] DISABLE
after the creat login statement. I know the login was created enabled.
Is this a feature?I'm not sure I completely understand and/or agree, but did you notice the
comment:
/* For security reasons the login is created disabled and with a random
password. */
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
> hello all,
> When I RT click a login in management studio \script login as \create to
> new
> query window\ the script generated has
> ALTER LOGIN [ID] DISABLE
> after the creat login statement. I know the login was created enabled.
> Is this a feature?|||Thanks Aaron
Just proves that it helps to read the comments.
Joe
"Aaron Bertrand [SQL Server MVP]" wrote:

> I'm not sure I completely understand and/or agree, but did you notice the
> comment:
> /* For security reasons the login is created disabled and with a random
> password. */
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
>
>

Alpha Order in Management Studio

I know this isn't the right section, but they offered me no alternative.

I'm in Management Studio and am perusing the columns of a table, but they aren't in alphabetical order. How do I sort them by alphabetical order? There doesn't seem to be an obvious way to do this.

Thanks.

Moving to Transact-SQL forum for starters. This isn't an SSIS issue.

I do know that the columns appear in order of creation. Perhaps writing a query (and hence the move to Transact-SQL forum) would suit your needs.|||Click on the little SQL button in the toolbar, and add a ORDER BY clause to the statement.|||

Agreed Phil. Just use a query as:

select *

from sys.columns

where object_id = object_id('tableName')

order by name

The query could be expanded to include datatypes and such if you want, but if you are just trying to get the columns in the UI sorted, it doesn't work this way. I would suggest you file a suggestion here:

https://connect.microsoft.com/SQLServer/feedback/

Allowing users to schedule jobs in Management Studio

How do I grant a non sysdba user who has bulkadmin and dbcreator rights to schedule jobs on databases they've created?

The user is a developer and we dont want to give him sysdba rights.

try if this works

use the Execute AS command which is used to impersonate any user

havent done much of a research on that one but u can easily get stuff on BOL

|||http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?topicid=12&faqid=137 for your information to accomplish the task.

All-of-a-sudden, I get "Invalid object name"

I have been developing a database in SQL Server Management Studio Express for the past few weeks. I have been writing stored procedures and functions, creating and deleting tables, etc., etc. and everything has been working just fine.
Today, however, if I try to run any of the functions I have written I get "Invalid object name 'functionName'."
This occurs even if I create a new function, execute the CREATE statement. Then if I refresh the Functions folder I can see my newly created function, but I cannot run it.
This is terribly frustrating.

Does anyone have any ideas as to what might have changed? I am logging in as the same user, on the same machine that I have always logged in on. I created the database that is giving the error.

When I right-click SQLEXPRESS in the object browser, and go to Permissions in the Server Properties dialog, no permissions are selected as "granted" for my login name under any of the Logins/Roles. (ex. BUILTIN\Administrators: my login name is listed in the "Explicit permissions for BUILTIN\Administrators" list, but no permissions are granted. If I go down the list selecting [checking] the various permissions, then close the dialog. When I open the dialog again, rows have been added to the list that have the Permission name, then "sa" listed as the Grantor, with that row checked. But, there are still no checks next to my login name.)

The only thing that has changed on my machine, that I am aware of, is that a Windows Authentication update ran on my machine earlier today. I am using Windows Authentication in my SQL Server instance. I assume it has something to do with that.

Please help. This is urgent. We have a presentation on this project in 3 days.
ugh.

Thanks in advance for any help you can provide.

hi,

I'd check that your database users' (database principal) default schema is still the one you are used to have too...

and obviously I'd verify all your code includes full object references in the form "schema_name.object_name" and not only

CREATE PROCEDURE usp_ProcName

....

EXEC usp_ProceureName

but

CREATE PROCEDURE the_schema.usp_ProcedureName

....

EXEC the_schema.usp_ProcedureName

and the like..

regards