Re: Changing Default Names

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 04/02/04 

Date: Fri, 2 Apr 2004 09:07:54 -0800

Did you run the WHOLE script the way Adam specified, creating a new table
and then immediately renaming the default. We need to see if there is a
problem with your objects or with your SQL Server.

Thanks 

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dave" <anonymous@discussions.microsoft.com> wrote in message
news:1782f01c418d0$df8a3f20$a401280a@phx.gbl...
> A table owns the constrains..........Defaults...
>
> ALTER TABLE [differentowner].[mytable] WITH NOCHECK ADD
> CONSTRAINT [DF__mytable__col1__834637] DEFAULT (0)
> FOR [col1]
> GO
>
>
> I ran the sp_rename the way he specified and I got:
>
> Server: Msg 15248, Level 11, State 1, Procedure sp_rename,
> Line 223
> Either the parameter @objname is ambiguous or the claimed
> @objtype (object) is wrong.
>
>
>
>
> >-----Original Message-----
> >But who owns the constraints? If the constraint has a
> different owner, you
> >must specify the owner name when referencing the object.
> >
> >--
> >HTH
> >----------------
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Dave" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:1768501c418b6$97e79e70$a401280a@phx.gbl...
> >> I am connecting as sysadmin.
> >>
> >>
> >>
> >> >-----Original Message-----
> >> >Hi Dave
> >> >
> >> >Is it possible the constraint has a different owner
> than
> >> the user you are
> >> >currently connected as?
> >> >
> >> >--
> >> >HTH
> >> >----------------
> >> >Kalen Delaney
> >> >SQL Server MVP
> >> >www.SolidQualityLearning.com
> >> >
> >> >
> >> >"Dave" <anonymous@discussions.microsoft.com> wrote in
> >> message
> >> >news:1725d01c41833$5dffcb20$a401280a@phx.gbl...
> >> >> NO. Here is what I am running:
> >> >>
> >> >>
> >>
> sp_rename 'DF__admuserpr__origi__2B0043CC', 'admuserpr_orig
> >> >> i_df', 'object'
> >> >>
> >> >>
> >> >> Here is what I get:
> >> >>
> >> >> Server: Msg 15248, Level 11, State 1, Procedure
> >> sp_rename,
> >> >> Line 223
> >> >> Either the parameter @objname is ambiguous or the
> >> claimed
> >> >> @objtype (object) is wrong.
> >> >>
> >> >>
> >> >>
> >> >> >-----Original Message-----
> >> >> >sorry, that was a CHECK constraint... here's a
> default:
> >> >> >
> >> >> >
> >> >> >create table a(id int)
> >> >> >GO
> >> >> >
> >> >> >create table b (id int)
> >> >> >GO
> >> >> >
> >> >> >alter table b add constraint r_b default (1) for id
> >> >> >GO
> >> >> >
> >> >> >sp_rename 'r_b', 'r_c', 'object'
> >> >> >GO
> >> >> >
> >> >> >"Adam Machanic" <amachanic@air-
> >> >> worldwide.nospamallowed.com> wrote in message
> >> >> >news:#oIt9BDGEHA.4044@TK2MSFTNGP10.phx.gbl...
> >> >> >> It works for me... Does the following work for
> you:
> >> >> >>
> >> >> >>
> >> >> >> create table a(id int)
> >> >> >> GO
> >> >> >>
> >> >> >> create table b (id int)
> >> >> >> GO
> >> >> >>
> >> >> >> alter table b add constraint r_b check (id = 1)
> >> >> >> GO
> >> >> >>
> >> >> >> sp_rename 'r_b', 'r_c', 'object'
> >> >> >> GO
> >> >> >>
> >> >> >> "Dave" <anonymous@discussions.microsoft.com>
> wrote in
> >> >> message
> >> >> >> news:1722a01c4182f$5d987aa0$a401280a@phx.gbl...
> >> >> >> > I can not use sp_rename because the defaults are
> >> not
> >> >> >> > recognized as objects.
> >> >> >> >
> >> >> >> >
> >> >> >> > >-----Original Message-----
> >> >> >> > >Hard to say if there are side effects; it's not
> >> the
> >> >> >> > supported way of
> >> >> >> > >renaming objects.  Instead, use sp_rename.
> Look
> >> up
> >> >> >> > syntax in BOL.
> >> >> >> > >
> >> >> >> > >
> >> >> >> > >"Dave" <anonymous@discussions.microsoft.com>
> >> wrote in
> >> >> >> > message
> >> >> >> > >news:1444801c4182b$45f41160$a601280a@phx.gbl...
> >> >> >> > >> I am planning to change the default names
> from
> >> >> >> > >>
> >> >> >> > >> 'DF__tablename__columnname_28F7FFC9'to
> >> >> >> > >> 'tablename_columnname_df'.
> >> >> >> > >>
> >> >> >> > >> Depending on the number of default
> columns 'df1,
> >> >> df2,
> >> >> >> > >> df3 .....e.t.c' will be added.
> >> >> >> > >>
> >> >> >> > >> I am going to rename them in the 'sysobjects'
> >> table
> >> >> >> > where
> >> >> >> > >> I see the only location for them.
> >> >> >> > >>
> >> >> >> > >> Is there any side effects to this ?.
> >> >> >> > >>
> >> >> >> > >> Thanks for any help.
> >> >> >> > >
> >> >> >> > >
> >> >> >> > >.
> >> >> >> > >
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Relevant Pages

  • Re: Changing Default Names
    ... you will have as sysadmin) then you must specify the owner name. ... If the constraint has a> different owner, ...
    (microsoft.public.sqlserver.server)
  • Re: conflicting object names in sql server 2000
    ... The owner of the object will depend on the user ... On the local database server when it does the select * from ... she must specify the owner: ...
    (microsoft.public.sqlserver.server)
  • Re: Difference between semivowel and consonant
    ... would seem to specify only how the tongue is oriented relative to the ... than there is such a constraint on its fricative counterpart SAMPA ... So there is an implicit contraint to the position of the lips? ... there are many examples both of vowels and of consonants undergoing ...
    (sci.lang)
  • Re: Default value: ISNULL()
    ... You can create a default constraint to specify a default value for a column. ... Col1 int NOT NULL, ... > ISNULL() as a default value to avoid NULL entries when importing data ...
    (comp.databases.ms-sqlserver)
  • Re: How to restore a backup to another machine
    ... > You need to specify the MOVE option. ... > RESTORE DATABASE newdb ... > SQL Server MVP ... >> another server where the db doesn't exist what is the basic syntax. ...
    (microsoft.public.sqlserver.server)