Showing posts with label modify. Show all posts
Showing posts with label modify. Show all posts

Friday, March 23, 2012

How to alter an used alias type?

If I created an alias type as followed,
CREATE TYPE SSN
FROM varchar(11) NOT NULL
For some reasons, I would like to modify this alias type to varchar(20) not
null,
How to do it?
--Frank, using SQL2005devYou can to drop it and re-create it. But you cannot drop it as long as it is
being used, so...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Lee" <Reply@.to.newsgroup> wrote in message news:OI8c$X$LGHA.140@.TK2MSFTNGP12.phx.gbl
..
> If I created an alias type as followed,
> CREATE TYPE SSN
> FROM varchar(11) NOT NULL
> For some reasons, I would like to modify this alias type to varchar(20) no
t
> null,
> How to do it?
> --Frank, using SQL2005dev
>|||Frank Lee (Reply@.to.newsgroup) writes:
> If I created an alias type as followed,
> CREATE TYPE SSN
> FROM varchar(11) NOT NULL
> For some reasons, I would like to modify this alias type to varchar(20)
> not null,
> How to do it?
That's a bit of work! Rename the type (sp_rename), create the new
definition. Run ALTER TABLE ALTER COLUMN on all tables that uses
the type, and recreate all procedures that uses the type as a parameter.
Finally you can drop the old definition.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se>
'?:Xns9769A5B38E43Yazorman@.127.0.0.1...
> Frank Lee (Reply@.to.newsgroup) writes:
> That's a bit of work! Rename the type (sp_rename), create the new
> definition. Run ALTER TABLE ALTER COLUMN on all tables that uses
> the type, and recreate all procedures that uses the type as a parameter.
> Finally you can drop the old definition.
>
Oh My god!
I would like to use alias type because I thought that if one day I want to
alter the difinition of alias type, it would be more easy as it has central
difinition. However, it's not really. It's more complex. :-(
Anyway, if it's the only way to alter the used alias type, I accept.
--Frank|||Frank Lee (Reply@.to.newsgroup) writes:
> I would like to use alias type because I thought that if one day I want
> to alter the difinition of alias type, it would be more easy as it has
> central difinition. However, it's not really. It's more complex. :-(
And it's maybe just as well. For a small database with little data in it
a single ALTER TYPE may be harmless. But for a widely used type in a 1TB
database it could send the database offline for a w.
Changing table schema in a large database requires careful planning, and
even more so if you change a type.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se>
'?:Xns97695FE4BC283Yazorman@.127.0.0.1...
> And it's maybe just as well. For a small database with little data in it
> a single ALTER TYPE may be harmless. But for a widely used type in a 1TB
> database it could send the database offline for a w.
> Changing table schema in a large database requires careful planning, and
> even more so if you change a type.
>
Ya, you are right.sql

Monday, March 12, 2012

How to add or modify "login name" of existing users

How is it possible to add or modify "login name"
through "Enterprise Manager" on an existing users of a
database.
The "login name" has been created but I need to effect the
changes on the users. An example is either to either add
or change the "login name" of the user "dbo".
Advise would be appreciated.There is no way that I know of to edit the login name of a login through EM.
From your post, are you trying to change the dbo of the database? If so,
look at sp_changedbowner
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"topokin" <bimosekin@.hotmail.com> wrote in message
news:021901c3c540$546acc90$a001280a@.phx.gbl...
quote:

> How is it possible to add or modify "login name"
> through "Enterprise Manager" on an existing users of a
> database.
> The "login name" has been created but I need to effect the
> changes on the users. An example is either to either add
> or change the "login name" of the user "dbo".
> Advise would be appreciated.
|||You can change the login name of the dbo with the sp_changedbowner
procedure, but you cannot change the login name that current exists for the
dbo.
Rand
This posting is provided "as is" with no warranties and confers no rights.

How to add or change "login name" on users

How is it possible to add or modify "login name"
through "Enterprise Manager" on an existing users of a
database.
The "login name" has been created but I need to effect the
changes on the users. An example is either to either add
or change the "login name" of the user "dbo".
Advise would be appreciated.sp_change_users_login
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"topokin" <bimosekin@.hotmail.com> wrote in message news:04b601c3c540$3a4d2920$a101280a@.phx.gbl...
> How is it possible to add or modify "login name"
> through "Enterprise Manager" on an existing users of a
> database.
> The "login name" has been created but I need to effect the
> changes on the users. An example is either to either add
> or change the "login name" of the user "dbo".
> Advise would be appreciated.

How to add new column in MERGE replication

I am using MERGE replication.
Subscription is done @. 17 branch offices @. various locations. It running
successfuly.
Now I want to modify the table and want to add new column to the customer
table.
What should I do. Any changes made @. publisher side, how to apply @.
subscribers side.
Best Regards
Sanjay
Sanjay - please check out sp_repladdcolumn in BOL.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com