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 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.

Allowing Remote Connections

When I attempt to connect to our new SQLServer 2005 from my workstation running the latest XP Pro and SQL Server Mgmt Studio 2005, I get the following error. I checked the setup on the server, and TCP/IP connections are allowed. I connect to all our older SQL Server 2000 servers/databases just fine.

TITLE: Connect to Server

Cannot connect to <server>\<instance> (deleted).

ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

BUTTONS:

OK

Terry,

I'm going to move this thread to the "Data Access" forum where I think you'll get a quicker response.

-Jeffrey

|||

Terry,

The error message shows that the sql browser is not able to be contacted by the client. So you need to (1) enble the sql browser service and start it. (2) make exception on your firewall setting to allow 1434 udp port.

Otherwise, you can specify port number of your sqlserver in your connection string. For example. tcp:<servername>,<serverport>

SQL Browswer is used to resolve connection paramenter for named instance. It is a seperate service from SQL Server.

|||

Terry,

I didn't do what the one reply did, but it gave me a clue on where to look.

SQL 2005 has a new SQL Server Configuration Manager under Configuration Tools. Click on that, after it opens go to SQL Server 2005 Services and verity that SQL Server Browser is running.

Now this is the change I had to make. Open SQL Server 2005 Network Configuation, and click on Protocols for <server>. You should just need to enable TCP/IP then restart SQL Server 2005 Service, if you like to you can enable the others if you use them here.

Hope this helps.

Randy

Allowing Remote Connections

When I attempt to connect to our new SQLServer 2005 from my workstation running the latest XP Pro and SQL Server Mgmt Studio 2005, I get the following error. I checked the setup on the server, and TCP/IP connections are allowed. I connect to all our older SQL Server 2000 servers/databases just fine.

TITLE: Connect to Server

Cannot connect to <server>\<instance> (deleted).

ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

BUTTONS:

OK

Terry,

I'm going to move this thread to the "Data Access" forum where I think you'll get a quicker response.

-Jeffrey

|||

Terry,

The error message shows that the sql browser is not able to be contacted by the client. So you need to (1) enble the sql browser service and start it. (2) make exception on your firewall setting to allow 1434 udp port.

Otherwise, you can specify port number of your sqlserver in your connection string. For example. tcp:<servername>,<serverport>

SQL Browswer is used to resolve connection paramenter for named instance. It is a seperate service from SQL Server.

|||

Terry,

I didn't do what the one reply did, but it gave me a clue on where to look.

SQL 2005 has a new SQL Server Configuration Manager under Configuration Tools. Click on that, after it opens go to SQL Server 2005 Services and verity that SQL Server Browser is running.

Now this is the change I had to make. Open SQL Server 2005 Network Configuation, and click on Protocols for <server>. You should just need to enable TCP/IP then restart SQL Server 2005 Service, if you like to you can enable the others if you use them here.

Hope this helps.

Randy

Allow iis to connect to a sql express database

Folks,

I have an asp.net web app developed in visual studio 2005. The app works fine with the development server from visual studio and sql express. When I try to test the app under IIS before deploying to my host I get a permission not allowed message.

"[SqlException (0x80131904): User does not have permission to perform this action.]".

How do I give my web app access to my sql server database?

Thanks

Doug

These references should help:

Web Applications -Connect to SQL Server
Configuring an ASP.NET 2.0 Application to Work with Microsoft SQL Server 2000 or SQL Server 2005
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=395
How To: Create a Service Account for an ASP.NET 2.0 Application
http://msdn2.microsoft.com/en-us/library/ms998297.aspx
How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0
http://msdn2.microsoft.com/en-us/library/ms998300.aspx

Allow iis to connect to a sql express database

Folks,

I have an asp.net web app developed in visual studio 2005. The app works fine with the development server from visual studio and sql express. When I try to test the app under IIS before deploying to my host I get a permission not allowed message.

"[SqlException (0x80131904): User does not have permission to perform this action.]".

How do I give my web app access to my sql server database?

Thanks

Doug

These references should help:

Web Applications -Connect to SQL Server
Configuring an ASP.NET 2.0 Application to Work with Microsoft SQL Server 2000 or SQL Server 2005
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=395
How To: Create a Service Account for an ASP.NET 2.0 Application
http://msdn2.microsoft.com/en-us/library/ms998297.aspx
How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0
http://msdn2.microsoft.com/en-us/library/ms998300.aspx

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