Had something interesting happen yesterday.
Was given an alter table script to add two columns to a table we have. As
we use replication and I needed these two columns to replicate, I couldn't
simply use the alter table script but had to translate it into using
sp_repladdcolumn.
No, in the original script, it was: (and forgive typos, going on memory
here)
alter table table_foo
add column [target_object_cache_time] int not null default(0);
Now, if I create the column that way, it adds the column
target_object_cache_time.
However, when I used sp_repladdcolumn 'table_foo',
'[target_object_cache_time]', 'int not null deafult(0)';
it creates the columns with the [] around the name. i.e.
[target_object_cache_time] Moreover, it appeared to create it as
[target_object_cache_time]] on the subscribers.
Needless to say, when I tried to update the stored proc, which uses the new
column, it failed since it was looking for target_object_cache_time not
[target_object_cache_time].
Ok, not a big deal, I figure I'd issue a sp_repldropcolumn and recreate the
columns.
Here's the error message I recieved:
Category:COMMAND
Source: Failed Command
Number:
Message: if exists (select * from syscolumns where
name='[target_object_cache_time]'
and id = object_id('table_foo'))
begin if exists (select * from sysobjects where name='syspublications')
if exists (select * from sysarticles where objid=object_id('table_foo'))
and @.@.microsoftversion >= 0x07320000
exec sp_repldropcolumn
@.source_object=N'[table_foo]',@.column=N'[target_object_cache_time]'
else alter table [table_foo] drop column [[target_object_cache_time]]] else
alter table [table_foo] drop column [[t
(it got cut off).
As you can see, sometimes it's treating it with an extra set of [] and
sometimes not.
It seems to me that sp_repladdcolumn should treat [] the same way as alter
table does. But even if it doesn't, it seems to me that you should be able
to drop the columns cleanly at least.
Thoughts? (I didn't really test too much reproduction since I was trying to
get the root problem fixed on our prod boxes at the time. :-)
If I get a chance, I may try to reproduce this again later if no one else
can.
--
--
Greg D. Moore
President Green Mountain Software
Personal: http://stratton.greenms.com
SQL Server Consulting sql at greenms.comGo to the Product Feedback Center and file it as a bug. It is using
brackets to ensure that there aren't any issues with spaces in the names.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:eHQ78PxYGHA.5012@.TK2MSFTNGP04.phx.gbl...
> Had something interesting happen yesterday.
> Was given an alter table script to add two columns to a table we have. As
> we use replication and I needed these two columns to replicate, I couldn't
> simply use the alter table script but had to translate it into using
> sp_repladdcolumn.
> No, in the original script, it was: (and forgive typos, going on memory
> here)
> alter table table_foo
> add column [target_object_cache_time] int not null default(0);
> Now, if I create the column that way, it adds the column
> target_object_cache_time.
> However, when I used sp_repladdcolumn 'table_foo',
> '[target_object_cache_time]', 'int not null deafult(0)';
> it creates the columns with the [] around the name. i.e.
> [target_object_cache_time] Moreover, it appeared to create it as
> [target_object_cache_time]] on the subscribers.
> Needless to say, when I tried to update the stored proc, which uses the
> new
> column, it failed since it was looking for target_object_cache_time not
> [target_object_cache_time].
> Ok, not a big deal, I figure I'd issue a sp_repldropcolumn and recreate
> the
> columns.
> Here's the error message I recieved:
> Category:COMMAND
> Source: Failed Command
> Number:
> Message: if exists (select * from syscolumns where
> name='[target_object_cache_time]'
> and id = object_id('table_foo'))
> begin if exists (select * from sysobjects where name='syspublications')
> if exists (select * from sysarticles where objid=object_id('table_foo'))
> and @.@.microsoftversion >= 0x07320000
> exec sp_repldropcolumn
> @.source_object=N'[table_foo]',@.column=N'[target_object_cache_time]'
> else alter table [table_foo] drop column [[target_object_cache_time]]]
> else
> alter table [table_foo] drop column [[t
> (it got cut off).
> As you can see, sometimes it's treating it with an extra set of [] and
> sometimes not.
> It seems to me that sp_repladdcolumn should treat [] the same way as alter
> table does. But even if it doesn't, it seems to me that you should be
> able
> to drop the columns cleanly at least.
> Thoughts? (I didn't really test too much reproduction since I was trying
> to
> get the root problem fixed on our prod boxes at the time. :-)
> If I get a chance, I may try to reproduce this again later if no one else
> can.
>
>
>
> --
> --
> Greg D. Moore
> President Green Mountain Software
> Personal: http://stratton.greenms.com
> SQL Server Consulting sql at greenms.com
>|||"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%23D%23lFF8YGHA.500@.TK2MSFTNGP03.phx.gbl...
> Go to the Product Feedback Center and file it as a bug.
What's the specific URL?
> It is using
> brackets to ensure that there aren't any issues with spaces in the names.
Right, but besides using them wrongly, it's inconsistent in its use of them.
:-)
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:eHQ78PxYGHA.5012@.TK2MSFTNGP04.phx.gbl...
> >
> > Had something interesting happen yesterday.
> >
> > Was given an alter table script to add two columns to a table we have.
As
> > we use replication and I needed these two columns to replicate, I
couldn't
> > simply use the alter table script but had to translate it into using
> > sp_repladdcolumn.
> >
> > No, in the original script, it was: (and forgive typos, going on memory
> > here)
> >
> > alter table table_foo
> > add column [target_object_cache_time] int not null default(0);
> >
> > Now, if I create the column that way, it adds the column
> > target_object_cache_time.
> >
> > However, when I used sp_repladdcolumn 'table_foo',
> > '[target_object_cache_time]', 'int not null deafult(0)';
> >
> > it creates the columns with the [] around the name. i.e.
> > [target_object_cache_time] Moreover, it appeared to create it as
> > [target_object_cache_time]] on the subscribers.
> >
> > Needless to say, when I tried to update the stored proc, which uses the
> > new
> > column, it failed since it was looking for target_object_cache_time not
> > [target_object_cache_time].
> >
> > Ok, not a big deal, I figure I'd issue a sp_repldropcolumn and recreate
> > the
> > columns.
> >
> > Here's the error message I recieved:
> >
> > Category:COMMAND
> > Source: Failed Command
> > Number:
> > Message: if exists (select * from syscolumns where
> > name='[target_object_cache_time]'
> > and id = object_id('table_foo'))
> > begin if exists (select * from sysobjects where name='syspublications')
> > if exists (select * from sysarticles where objid=object_id('table_foo'))
> > and @.@.microsoftversion >= 0x07320000
> >
> > exec sp_repldropcolumn
> > @.source_object=N'[table_foo]',@.column=N'[target_object_cache_time]'
> > else alter table [table_foo] drop column [[target_object_cache_time]]]
> > else
> > alter table [table_foo] drop column [[t
> >
> > (it got cut off).
> >
> > As you can see, sometimes it's treating it with an extra set of [] and
> > sometimes not.
> >
> > It seems to me that sp_repladdcolumn should treat [] the same way as
alter
> > table does. But even if it doesn't, it seems to me that you should be
> > able
> > to drop the columns cleanly at least.
> >
> > Thoughts? (I didn't really test too much reproduction since I was
trying
> > to
> > get the root problem fixed on our prod boxes at the time. :-)
> >
> > If I get a chance, I may try to reproduce this again later if no one
else
> > can.
> >
> >
> >
> >
> >
> >
> > --
> > --
> > Greg D. Moore
> > President Green Mountain Software
> > Personal: http://stratton.greenms.com
> > SQL Server Consulting sql at greenms.com
> >
> >
>|||This is a multi-part message in MIME format.
--040005020201060803030401
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Greg D. Moore (Strider) wrote:
>"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
>news:%23D%23lFF8YGHA.500@.TK2MSFTNGP03.phx.gbl...
>
>>Go to the Product Feedback Center and file it as a bug.
>>
>What's the specific URL?
>
http://lab.msdn.microsoft.com/productfeedback/default.aspx
--
*mike hodgson*
http://sqlnerd.blogspot.com
--040005020201060803030401
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
Greg D. Moore (Strider) wrote:
<blockquote cite="mideCI2gnPZGHA.4752@.TK2MSFTNGP02.phx.gbl" type="cite">
<pre wrap="">"Michael Hotek" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:mike@.solidqualitylearning.com"><mike@.solidqualitylearning.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:%23D%23lFF8YGHA.500@.TK2MSFTNGP03.phx.gbl">news:%23D%23lFF8YGHA.500@.TK2MSFTNGP03.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Go to the Product Feedback Center and file it as a bug.
</pre>
</blockquote>
<pre wrap=""><!-->
What's the specific URL?
</pre>
</blockquote>
<tt><a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://lab.msdn.microsoft.com/productfeedback/default.aspx</a></tt><br>">http://lab.msdn.microsoft.com/productfeedback/default.aspx">http://lab.msdn.microsoft.com/productfeedback/default.aspx</a></tt><br>
<br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
</body>
</html>
--040005020201060803030401--
sadie519590 wrote:
