Showing posts with label sp4. Show all posts
Showing posts with label sp4. Show all posts

Wednesday, March 28, 2012

Precision Scale Problems Between SQL 2K SP3 and SP4

This is a really strange error that I am seeing and I'm wondering if
anyone else has seen it yet.
I have a small database (less than 100k for backup file) that I send to a
third party. On several tables in this database there are fields that are
set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
running 2K sp4. When they restore the database on their side, SOME of
these same numeric fields are showing a scale of 0, so in effect the scale
is lost and rather than reporting "1.5000" we are reporting a value of
"1". The precision and scale on third party's end is decimal(18,0).
Is there any way to explain why the scale for SOME of these fields is
changing? It's not even a complete conversion. There are some fields that
are not getting converted, which REALLY confuses me.
The only thing I can figure is that a version of the database with this
scale exists somewhere on the server and that when they do a restore,
somehow the scale for these fields is retained from that old database,
even though we are restoring from a backup. How this would happen, I have
no idea, but it is the only thing I can think of at this point.
Perhaps the database backup file contains multiple backups and the first
(older schema) is restored by default. You can check this with RESTORE
HEADERONLY:
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyBackup.bak'
Hope this helps.
Dan Guzman
SQL Server MVP
"KBarrett" <kelseybarrett@.matteicos.com> wrote in message
news:op.szk8rrbkbpkth2@.tmc-kbarrett.matteicos.com...
> This is a really strange error that I am seeing and I'm wondering if
> anyone else has seen it yet.
> I have a small database (less than 100k for backup file) that I send to a
> third party. On several tables in this database there are fields that are
> set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
> running 2K sp4. When they restore the database on their side, SOME of
> these same numeric fields are showing a scale of 0, so in effect the scale
> is lost and rather than reporting "1.5000" we are reporting a value of
> "1". The precision and scale on third party's end is decimal(18,0).
> Is there any way to explain why the scale for SOME of these fields is
> changing? It's not even a complete conversion. There are some fields that
> are not getting converted, which REALLY confuses me.
> The only thing I can figure is that a version of the database with this
> scale exists somewhere on the server and that when they do a restore,
> somehow the scale for these fields is retained from that old database,
> even though we are restoring from a backup. How this would happen, I have
> no idea, but it is the only thing I can think of at this point.
>
|||Thanks for the tip Dan. I will check on that.
-k
On Tue, 01 Nov 2005 20:35:28 -0800, Dan Guzman
<guzmanda@.nospam-online.sbcglobal.net> wrote:

> Perhaps the database backup file contains multiple backups and the first
> (older schema) is restored by default. You can check this with RESTORE
> HEADERONLY:
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyBackup.bak'

Precision Scale Problems Between SQL 2K SP3 and SP4

This is a really strange error that I am seeing and I'm wondering if
anyone else has seen it yet.
I have a small database (less than 100k for backup file) that I send to a
third party. On several tables in this database there are fields that are
set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
running 2K sp4. When they restore the database on their side, SOME of
these same numeric fields are showing a scale of 0, so in effect the scale
is lost and rather than reporting "1.5000" we are reporting a value of
"1". The precision and scale on third party's end is decimal(18,0).
Is there any way to explain why the scale for SOME of these fields is
changing? It's not even a complete conversion. There are some fields that
are not getting converted, which REALLY confuses me.
The only thing I can figure is that a version of the database with this
scale exists somewhere on the server and that when they do a restore,
somehow the scale for these fields is retained from that old database,
even though we are restoring from a backup. How this would happen, I have
no idea, but it is the only thing I can think of at this point.Perhaps the database backup file contains multiple backups and the first
(older schema) is restored by default. You can check this with RESTORE
HEADERONLY:
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyBackup.bak'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KBarrett" <kelseybarrett@.matteicos.com> wrote in message
news:op.szk8rrbkbpkth2@.tmc-kbarrett.matteicos.com...
> This is a really strange error that I am seeing and I'm wondering if
> anyone else has seen it yet.
> I have a small database (less than 100k for backup file) that I send to a
> third party. On several tables in this database there are fields that are
> set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
> running 2K sp4. When they restore the database on their side, SOME of
> these same numeric fields are showing a scale of 0, so in effect the scale
> is lost and rather than reporting "1.5000" we are reporting a value of
> "1". The precision and scale on third party's end is decimal(18,0).
> Is there any way to explain why the scale for SOME of these fields is
> changing? It's not even a complete conversion. There are some fields that
> are not getting converted, which REALLY confuses me.
> The only thing I can figure is that a version of the database with this
> scale exists somewhere on the server and that when they do a restore,
> somehow the scale for these fields is retained from that old database,
> even though we are restoring from a backup. How this would happen, I have
> no idea, but it is the only thing I can think of at this point.
>|||Thanks for the tip Dan. I will check on that.
-k
On Tue, 01 Nov 2005 20:35:28 -0800, Dan Guzman
<guzmanda@.nospam-online.sbcglobal.net> wrote:
> Perhaps the database backup file contains multiple backups and the first
> (older schema) is restored by default. You can check this with RESTORE
> HEADERONLY:
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyBackup.bak'

Precision Scale Problems Between SQL 2K SP3 and SP4

This is a really strange error that I am seeing and I'm wondering if
anyone else has seen it yet.
I have a small database (less than 100k for backup file) that I send to a
third party. On several tables in this database there are fields that are
set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
running 2K sp4. When they restore the database on their side, SOME of
these same numeric fields are showing a scale of 0, so in effect the scale
is lost and rather than reporting "1.5000" we are reporting a value of
"1". The precision and scale on third party's end is decimal(18,0).
Is there any way to explain why the scale for SOME of these fields is
changing? It's not even a complete conversion. There are some fields that
are not getting converted, which REALLY confuses me.
The only thing I can figure is that a version of the database with this
scale exists somewhere on the server and that when they do a restore,
somehow the scale for these fields is retained from that old database,
even though we are restoring from a backup. How this would happen, I have
no idea, but it is the only thing I can think of at this point.Perhaps the database backup file contains multiple backups and the first
(older schema) is restored by default. You can check this with RESTORE
HEADERONLY:
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyBackup.bak'
Hope this helps.
Dan Guzman
SQL Server MVP
"KBarrett" <kelseybarrett@.matteicos.com> wrote in message
news:op.szk8rrbkbpkth2@.tmc-kbarrett.matteicos.com...
> This is a really strange error that I am seeing and I'm wondering if
> anyone else has seen it yet.
> I have a small database (less than 100k for backup file) that I send to a
> third party. On several tables in this database there are fields that are
> set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
> running 2K sp4. When they restore the database on their side, SOME of
> these same numeric fields are showing a scale of 0, so in effect the scale
> is lost and rather than reporting "1.5000" we are reporting a value of
> "1". The precision and scale on third party's end is decimal(18,0).
> Is there any way to explain why the scale for SOME of these fields is
> changing? It's not even a complete conversion. There are some fields that
> are not getting converted, which REALLY confuses me.
> The only thing I can figure is that a version of the database with this
> scale exists somewhere on the server and that when they do a restore,
> somehow the scale for these fields is retained from that old database,
> even though we are restoring from a backup. How this would happen, I have
> no idea, but it is the only thing I can think of at this point.
>|||Thanks for the tip Dan. I will check on that.
-k
On Tue, 01 Nov 2005 20:35:28 -0800, Dan Guzman
<guzmanda@.nospam-online.sbcglobal.net> wrote:

> Perhaps the database backup file contains multiple backups and the first
> (older schema) is restored by default. You can check this with RESTORE
> HEADERONLY:
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyBackup.bak'

Wednesday, March 21, 2012

Post SQL 2000 SP4 hotfixes - confused??

Hi,
I'm confused with what hotfixes needs to be applied after installing SQL
Server 2000 SP4. One of my server is using AWE so applying hotfix - 899761
(FIX: Not all memory is available when AWE is enabled on a computer that is
running a 32-bit version of SQL Server 2000 SP4) makes sense, but there are
some other hotfixes which I don't know if they need to be applied as well or
not. Those hotfixes are:
836651 (FIX: You receive query results that were not expected when you use
both ANSI joins and non-ANSI joins)
826906 (FIX: A query that uses a view that contains a correlated subquery
and an aggregate runs slowly)
887165 (BUG: You may receive an "Access is denied" error message when a
query calls an extended stored procedure after you upgrade from SQL Server
2000 to SQL Server 2000 Service Pack 4)
Do I have also apply the above hotfixes as well? If yes, then in what
sequence or it doesn't matter?
TIA.
Regards,Rob wrote:
> Hi,
> I'm confused with what hotfixes needs to be applied after installing SQL
> Server 2000 SP4. One of my server is using AWE so applying hotfix - 899761
> (FIX: Not all memory is available when AWE is enabled on a computer that is
> running a 32-bit version of SQL Server 2000 SP4) makes sense, but there are
> some other hotfixes which I don't know if they need to be applied as well or
> not. Those hotfixes are:
> 836651 (FIX: You receive query results that were not expected when you use
> both ANSI joins and non-ANSI joins)
> 826906 (FIX: A query that uses a view that contains a correlated subquery
> and an aggregate runs slowly)
> 887165 (BUG: You may receive an "Access is denied" error message when a
> query calls an extended stored procedure after you upgrade from SQL Server
> 2000 to SQL Server 2000 Service Pack 4)
> Do I have also apply the above hotfixes as well? If yes, then in what
> sequence or it doesn't matter?
> TIA.
> Regards,
In general, you should apply any patch that is available. Always apply
them to a development or test server first, let them cook there for a
period of time, then deploy to production. Better to apply them *at
your convenience* than when the place is on fire...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:u3u7XV0qGHA.3248@.TK2MSFTNGP04.phx.gbl...
> Rob wrote:
> > Hi,
> >
> > I'm confused with what hotfixes needs to be applied after installing SQL
> > Server 2000 SP4. One of my server is using AWE so applying hotfix -
899761
> > (FIX: Not all memory is available when AWE is enabled on a computer that
is
> > running a 32-bit version of SQL Server 2000 SP4) makes sense, but there
are
> > some other hotfixes which I don't know if they need to be applied as
well or
> > not. Those hotfixes are:
> >
> > 836651 (FIX: You receive query results that were not expected when you
use
> > both ANSI joins and non-ANSI joins)
> >
> > 826906 (FIX: A query that uses a view that contains a correlated
subquery
> > and an aggregate runs slowly)
> >
> > 887165 (BUG: You may receive an "Access is denied" error message when a
> > query calls an extended stored procedure after you upgrade from SQL
Server
> > 2000 to SQL Server 2000 Service Pack 4)
> >
> > Do I have also apply the above hotfixes as well? If yes, then in what
> > sequence or it doesn't matter?
> >
> > TIA.
> >
> > Regards,
> In general, you should apply any patch that is available.
I will dispute that. And in general Microsoft suggests that you do NOT
apply hot patches unless the fix applies to you. They do not have the full
amount of testing that a service patch has.
> Always apply
> them to a development or test server first, let them cook there for a
> period of time, then deploy to production. Better to apply them *at
> your convenience* than when the place is on fire...
If you do have to apply them, I do agree with this.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hello, Rob
Hotfixes for SQL Server are cummulative (for the same service pack
level), so if you install a later hotfix there is no need to install
the previous hotfixes.
In particular, the first two KB articles you mentioned (836651 and
826906) correspond to the same post-SP4 hotfix (8.00.2145). However,
the post-SP3 versions of the hotfixes are different (8.00.913 and
8.00.850, respectively), but since those post-SP3 hotfixes were not
included in SP4, another post-SP4 hotfix (i.e. 8.00.2145) was made.
This post-SP4 hotfix was not publicily available, so if you are
affected by those problems, it is recommended to install the
cummulative hotfix build 2187, downloadable from KB article 916287.
This post-SP4 hotfix contains all the post-SP3 hotfixes that were not
included in SP4, the AWE hotfix (and possibly some other hotfixes).
Normally, Microsoft recommends that you do not install any particular
hotfix unless you are affected by that specific problem. However, for
cummulative hotfix build 2187 they did not issue this recommendation,
which makes me believe that this hotfix was more thoroughly tested.
Regarding the last KB article you mentioned, there is no hotfix for
that problem. The KB article 887165 only documents the problem and
provides workarounds.
Razvan

Tuesday, March 20, 2012

Post SQL 2000 SP4 hotfixes - confused??

Hi,
I'm confused with what hotfixes needs to be applied after installing SQL
Server 2000 SP4. One of my server is using AWE so applying hotfix - 899761
(FIX: Not all memory is available when AWE is enabled on a computer that is
running a 32-bit version of SQL Server 2000 SP4) makes sense, but there are
some other hotfixes which I don't know if they need to be applied as well or
not. Those hotfixes are:
836651 (FIX: You receive query results that were not expected when you use
both ANSI joins and non-ANSI joins)
826906 (FIX: A query that uses a view that contains a correlated subquery
and an aggregate runs slowly)
887165 (BUG: You may receive an "Access is denied" error message when a
query calls an extended stored procedure after you upgrade from SQL Server
2000 to SQL Server 2000 Service Pack 4)
Do I have also apply the above hotfixes as well? If yes, then in what
sequence or it doesn't matter?
TIA.
Regards,Rob wrote:
> Hi,
> I'm confused with what hotfixes needs to be applied after installing SQL
> Server 2000 SP4. One of my server is using AWE so applying hotfix - 899761
> (FIX: Not all memory is available when AWE is enabled on a computer that i
s
> running a 32-bit version of SQL Server 2000 SP4) makes sense, but there ar
e
> some other hotfixes which I don't know if they need to be applied as well
or
> not. Those hotfixes are:
> 836651 (FIX: You receive query results that were not expected when you use
> both ANSI joins and non-ANSI joins)
> 826906 (FIX: A query that uses a view that contains a correlated subquery
> and an aggregate runs slowly)
> 887165 (BUG: You may receive an "Access is denied" error message when a
> query calls an extended stored procedure after you upgrade from SQL Server
> 2000 to SQL Server 2000 Service Pack 4)
> Do I have also apply the above hotfixes as well? If yes, then in what
> sequence or it doesn't matter?
> TIA.
> Regards,
In general, you should apply any patch that is available. Always apply
them to a development or test server first, let them cook there for a
period of time, then deploy to production. Better to apply them *at
your convenience* than when the place is on fire...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:u3u7XV0qGHA.3248@.TK2MSFTNGP04.phx.gbl...
> Rob wrote:
899761[vbcol=seagreen]
is[vbcol=seagreen]
are[vbcol=seagreen]
well or[vbcol=seagreen]
use[vbcol=seagreen]
subquery[vbcol=seagreen]
Server[vbcol=seagreen]
> In general, you should apply any patch that is available.
I will dispute that. And in general Microsoft suggests that you do NOT
apply hot patches unless the fix applies to you. They do not have the full
amount of testing that a service patch has.

> Always apply
> them to a development or test server first, let them cook there for a
> period of time, then deploy to production. Better to apply them *at
> your convenience* than when the place is on fire...
If you do have to apply them, I do agree with this.

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hello, Rob
Hotfixes for SQL Server are cummulative (for the same service pack
level), so if you install a later hotfix there is no need to install
the previous hotfixes.
In particular, the first two KB articles you mentioned (836651 and
826906) correspond to the same post-SP4 hotfix (8.00.2145). However,
the post-SP3 versions of the hotfixes are different (8.00.913 and
8.00.850, respectively), but since those post-SP3 hotfixes were not
included in SP4, another post-SP4 hotfix (i.e. 8.00.2145) was made.
This post-SP4 hotfix was not publicily available, so if you are
affected by those problems, it is recommended to install the
cummulative hotfix build 2187, downloadable from KB article 916287.
This post-SP4 hotfix contains all the post-SP3 hotfixes that were not
included in SP4, the AWE hotfix (and possibly some other hotfixes).
Normally, Microsoft recommends that you do not install any particular
hotfix unless you are affected by that specific problem. However, for
cummulative hotfix build 2187 they did not issue this recommendation,
which makes me believe that this hotfix was more thoroughly tested.
Regarding the last KB article you mentioned, there is no hotfix for
that problem. The KB article 887165 only documents the problem and
provides workarounds.
Razvan

Post SP4 Hotfix

Can anyone help me to get the hotfix SQL Server 2000 build 8.00.2215 ?
As i do not find any download link.According to http://www.sqlsecurity.com/ (check FAQs, version database), this is a PSS release only. That means that you will have to contact MS and specifically request the patch.

There is no charge for the phone call (at least not last time I had to do this).

Regards,

hmscott|||http://support.microsoft.com/kb/894905|||http://support.microsoft.com/kb/924662/
http://support.microsoft.com/kb/936232/

Post SP Install issue

We have a active/active W2K3 SQL cluster with 3 instances that was upgraded
to SP4 with the 2148 hotfix. All 3 instances upgraded to SP 4 with no
problems.
However, when applying the hotix only the first instance upgraded correctly.
The hotfix install for instances 2 and 3 indicated success but the version
for both still shows build 2139. Can't seem to get the install to work for
these instances. Any help would be appreciated.Hi
You may want to re-contact PSS for this as this is a PSS only hotfix!
John
"GManino" wrote:
> We have a active/active W2K3 SQL cluster with 3 instances that was upgraded
> to SP4 with the 2148 hotfix. All 3 instances upgraded to SP 4 with no
> problems.
> However, when applying the hotix only the first instance upgraded correctly.
> The hotfix install for instances 2 and 3 indicated success but the version
> for both still shows build 2139. Can't seem to get the install to work for
> these instances. Any help would be appreciated.|||Hi
Your answer is Windows Task Scheduler.
See http://msmvps.com/epprecht/archive/2005/08/28/64380.aspx
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"GManino" <GManino@.discussions.microsoft.com> wrote in message
news:9BBD59DE-12E8-4A0C-AA9B-61B25D031BB3@.microsoft.com...
> We have a active/active W2K3 SQL cluster with 3 instances that was
> upgraded
> to SP4 with the 2148 hotfix. All 3 instances upgraded to SP 4 with no
> problems.
> However, when applying the hotix only the first instance upgraded
> correctly.
> The hotfix install for instances 2 and 3 indicated success but the version
> for both still shows build 2139. Can't seem to get the install to work
> for
> these instances. Any help would be appreciated.|||Does anyone know if this will work for all types of corruption?
"John Bell" wrote:
> Hi
> You may want to re-contact PSS for this as this is a PSS only hotfix!
> John
> "GManino" wrote:
> > We have a active/active W2K3 SQL cluster with 3 instances that was upgraded
> > to SP4 with the 2148 hotfix. All 3 instances upgraded to SP 4 with no
> > problems.
> >
> > However, when applying the hotix only the first instance upgraded correctly.
> > The hotfix install for instances 2 and 3 indicated success but the version
> > for both still shows build 2139. Can't seem to get the install to work for
> > these instances. Any help would be appreciated.

Post SP Install issue

We have a active/active W2K3 SQL cluster with 3 instances that was upgraded
to SP4 with the 2148 hotfix. All 3 instances upgraded to SP 4 with no
problems.
However, when applying the hotix only the first instance upgraded correctly.
The hotfix install for instances 2 and 3 indicated success but the version
for both still shows build 2139. Can't seem to get the install to work for
these instances. Any help would be appreciated.
Hi
You may want to re-contact PSS for this as this is a PSS only hotfix!
John
"GManino" wrote:

> We have a active/active W2K3 SQL cluster with 3 instances that was upgraded
> to SP4 with the 2148 hotfix. All 3 instances upgraded to SP 4 with no
> problems.
> However, when applying the hotix only the first instance upgraded correctly.
> The hotfix install for instances 2 and 3 indicated success but the version
> for both still shows build 2139. Can't seem to get the install to work for
> these instances. Any help would be appreciated.
|||Hi
Your answer is Windows Task Scheduler.
See http://msmvps.com/epprecht/archive/2.../28/64380.aspx
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"GManino" <GManino@.discussions.microsoft.com> wrote in message
news:9BBD59DE-12E8-4A0C-AA9B-61B25D031BB3@.microsoft.com...
> We have a active/active W2K3 SQL cluster with 3 instances that was
> upgraded
> to SP4 with the 2148 hotfix. All 3 instances upgraded to SP 4 with no
> problems.
> However, when applying the hotix only the first instance upgraded
> correctly.
> The hotfix install for instances 2 and 3 indicated success but the version
> for both still shows build 2139. Can't seem to get the install to work
> for
> these instances. Any help would be appreciated.
|||Does anyone know if this will work for all types of corruption?
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You may want to re-contact PSS for this as this is a PSS only hotfix!
> John
> "GManino" wrote:

Post SP Install issue

We have a active/active W2K3 SQL cluster with 3 instances that was upgraded
to SP4 with the 2148 hotfix. All 3 instances upgraded to SP 4 with no
problems.
However, when applying the hotix only the first instance upgraded correctly.
The hotfix install for instances 2 and 3 indicated success but the version
for both still shows build 2139. Can't seem to get the install to work for
these instances. Any help would be appreciated.Hi
You may want to re-contact PSS for this as this is a PSS only hotfix!
John
"GManino" wrote:

> We have a active/active W2K3 SQL cluster with 3 instances that was upgrade
d
> to SP4 with the 2148 hotfix. All 3 instances upgraded to SP 4 with no
> problems.
> However, when applying the hotix only the first instance upgraded correctl
y.
> The hotfix install for instances 2 and 3 indicated success but the versio
n
> for both still shows build 2139. Can't seem to get the install to work fo
r
> these instances. Any help would be appreciated.|||Hi
Your answer is Windows Task Scheduler.
See http://msmvps.com/epprecht/archive/...8/28/64380.aspx
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"GManino" <GManino@.discussions.microsoft.com> wrote in message
news:9BBD59DE-12E8-4A0C-AA9B-61B25D031BB3@.microsoft.com...
> We have a active/active W2K3 SQL cluster with 3 instances that was
> upgraded
> to SP4 with the 2148 hotfix. All 3 instances upgraded to SP 4 with no
> problems.
> However, when applying the hotix only the first instance upgraded
> correctly.
> The hotfix install for instances 2 and 3 indicated success but the version
> for both still shows build 2139. Can't seem to get the install to work
> for
> these instances. Any help would be appreciated.|||Does anyone know if this will work for all types of corruption?
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You may want to re-contact PSS for this as this is a PSS only hotfix!
> John
> "GManino" wrote:
>

Saturday, February 25, 2012

Possible Bug in sp_repladdcolumn? (SQL 2000, SP4)

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_t
ime]]] 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 alt
er
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 a
lter
> 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...
As[vbcol=seagreen]
couldn't[vbcol=seagreen]
alter[vbcol=seagreen]
trying[vbcol=seagreen]
else[vbcol=seagreen]
>|||Greg D. Moore (Strider) wrote:

>"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
>news:%23D%23lFF8YGHA.500@.TK2MSFTNGP03.phx.gbl...
>
>What's the specific URL?
>
http://lab.msdn.microsoft.com/produ...ck/default.aspx
*mike hodgson*
http://sqlnerd.blogspot.com

Possible Bug in sp_repladdcolumn? (SQL 2000, SP4)

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