Re: Changing Default Names
From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 04/06/04
- Next message: David Morrison: "Re: Querying across servers with Windows authentication"
- Previous message: Warren: "Connecting SQL Server 2000 to legacy (AS/400) systems in a real time view?"
- In reply to: Dave: "Re: Changing Default Names"
- Next in thread: Kalen Delaney: "Re: Changing Default Names"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 6 Apr 2004 07:51:07 -0700
In fact the syntax you have here will not work, because you have not
specified the new name. Or did you want the new name to be 'object'?
-- HTH ---------------- Kalen Delaney SQL Server MVP www.SolidQualityLearning.com "Dave" <anonymous@discussions.microsoft.com> wrote in message news:1603e01c41bd3$e1537910$a601280a@phx.gbl... > Sorry. My bet...........I never tried it this way but > Interesting Enough, It only requires the owner name and > NOT THE TABLE NAME. > > sp_rename '[express]. > [DF__tablename__columnname__874392]', 'object' > > > > >-----Original Message----- > >Then, What is the PROBLEM !!!!!!!!!!!!!!!!!!!!did you > >create a Default under a different owner and test it ???? > > > > > > > >>-----Original Message----- > >>That bug has nothing to do with your problem. You're > >renaming a DEFAULT on > >>a TABLE, not an INDEX on a VIEW. > >> > >> > >>"Dave" <anonymous@discussions.microsoft.com> wrote in > >message > >>news:186d601c41b37$e5b93d10$a501280a@phx.gbl... > >>> This is a BUG. It has nothing to do with DB settings. > >>> > >>> http://support.microsoft.com/default.aspx?scid=kb;en- > >>> us;281326 > >>> > >>> > >>> >-----Original Message----- > >>> >Dave > >>> > > >>> >This syntax works. You're going to have to show us the > >>> sp_help output on the > >>> >table, and the exact command you are running. > >>> > > >>> >Thanks > >>> > > >>> >-- > >>> >HTH > >>> >---------------- > >>> >Kalen Delaney > >>> >SQL Server MVP > >>> >www.SolidQualityLearning.com > >>> > > >>> > > >>> >"Dave" <anonymous@discussions.microsoft.com> wrote in > >>> message > >>> >news:1844801c41b0e$da894da0$a501280a@phx.gbl... > >>> >> I tried all these options. I still get the same > >>> error...... > >>> >> > >>> >> > >>> >> > >>> >> >-----Original Message----- > >>> >> >You don't need to include the tablename, just the > >>> >> ownername. Put the > >>> >> >brackets around the entire name [owner.object] > >>> >> > > >>> >> > EXEC sp_rename > >>> [express.DF__wpcharge__aprexc__155B1B70], > >>> >> wpcharge_aprex > >>> >> >c_df, 'object' > >>> >> > > >>> >> > > >>> >> >-- > >>> >> >HTH > >>> >> >---------------- > >>> >> >Kalen Delaney > >>> >> >SQL Server MVP > >>> >> >www.SolidQualityLearning.com > >>> >> > > >>> >> > > >>> >> >"Dave" <anonymous@discussions.microsoft.com> wrote > >in > >>> >> message > >>> >> >news:176fe01c418d7$96a35ba0$a501280a@phx.gbl... > >>> >> >> Adam's script runs successfully. The table owner > >for > >>> the > >>> >> >> tables are different (Ex: express.tablename). > >>> >> >> > >>> >> >> I add the owner name like: > >>> >> >> > >>> >> >> sp_rename 'express.tablename. > >>> >> >> > [DF__wpcharge__aprexc__155B1B70]', 'wpcharge_aprex > >>> >> >> c_df', 'object' > >>> >> >> > >>> >> >> I still get the same Error. > >>> >> >> > >>> >> >> I put the owner name and the table name in the > >>> brackets > >>> >> >> too but the result is the same. > >>> >> >> > >>> >> >> >-----Original Message----- > >>> >> >> >But who owns the table? If the owner is > different > >>> than > >>> >> >> dbo (the user name > >>> >> >> >you will have as sysadmin) then you must > specify > >the > >>> >> >> owner name. > >>> >> >> > > >>> >> >> > > >>> >> >> > > >>> >> >> >-- > >>> >> >> >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_ori > g > >>> >> >> >> >> >> 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. > >>> >> >> >> >> >> >> > > > >>> >> >> >> >> >> >> > > > >>> >> >> >> >> >> >> > >. > >>> >> >> >> >> >> >> > > > >>> >> >> >> >> >> >> > >>> >> >> >> >> >> >> > >>> >> >> >> >> >> > > >>> >> >> >> >> >> > > >>> >> >> >> >> >> >. > >>> >> >> >> >> >> > > >>> >> >> >> >> > > >>> >> >> >> >> > > >>> >> >> >> >> >. > >>> >> >> >> >> > > >>> >> >> >> > > >>> >> >> >> > > >>> >> >> >> >. > >>> >> >> >> > > >>> >> >> > > >>> >> >> > > >>> >> >> >. > >>> >> >> > > >>> >> > > >>> >> > > >>> >> >. > >>> >> > > >>> > > >>> > > >>> >. > >>> > > >> > >> > >>. > >> > >. > >
- Next message: David Morrison: "Re: Querying across servers with Windows authentication"
- Previous message: Warren: "Connecting SQL Server 2000 to legacy (AS/400) systems in a real time view?"
- In reply to: Dave: "Re: Changing Default Names"
- Next in thread: Kalen Delaney: "Re: Changing Default Names"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
