question on warnings recieved after running sp_repladdcolumn and sp_repldropcolumn

copy/past of sample error:
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'mytestdb' because schema replication is performed.
Warning: The table 'GeneralInventory' has been created but its maximum row
size (9929) exceeds the maximum number of bytes per row (8060). INSERT or
UPDATE of a row in this table will fail if the resulting row length exceeds
8060 bytes.
Warning: The table 'conflict_mytestdb_GeneralInventory' has been created but
its maximum row size (11924) exceeds the maximum number of bytes per row
(8060). INSERT or UPDATE of a row in this table will fail if the resulting
row length exceeds 8060 bytes.
this was after a sp_repldropcolumn? and I recieved similar messages after
performing an sp_repladdcolumn? What are they talking about? what problems
could this cause? what do I need to do?
any info is appreciated... thanks.
ignore these warnings. The first one is the only meaningful one.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"djc" <noone@.nowhere.com> wrote in message
news:%23$oVUw3XEHA.2448@.TK2MSFTNGP09.phx.gbl...
> copy/past of sample error:
> --
> Warning: only Subscribers running SQL Server 2000 can synchronize with
> publication 'mytestdb' because schema replication is performed.
> Warning: The table 'GeneralInventory' has been created but its maximum row
> size (9929) exceeds the maximum number of bytes per row (8060). INSERT or
> UPDATE of a row in this table will fail if the resulting row length
exceeds
> 8060 bytes.
> Warning: The table 'conflict_mytestdb_GeneralInventory' has been created
but
> its maximum row size (11924) exceeds the maximum number of bytes per row
> (8060). INSERT or UPDATE of a row in this table will fail if the resulting
> row length exceeds 8060 bytes.
> --
> this was after a sp_repldropcolumn? and I recieved similar messages after
> performing an sp_repladdcolumn? What are they talking about? what problems
> could this cause? what do I need to do?
> any info is appreciated... thanks.
>
|||This is a general SQL Server warning and isn't particular to replication eg
you can 2 varchar (8000) cols as you want to a table and the second time
there'll be this warning (if you do it in QA), as potentially you might
exceed the 8060ish max row size.
Regards,
Paul Ibison
|||good. Thank you!
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:Op8xu83XEHA.3664@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> ignore these warnings. The first one is the only meaningful one.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "djc" <noone@.nowhere.com> wrote in message
> news:%23$oVUw3XEHA.2448@.TK2MSFTNGP09.phx.gbl...
row[vbcol=seagreen]
or[vbcol=seagreen]
> exceeds
> but
resulting[vbcol=seagreen]
after[vbcol=seagreen]
problems
>
|||thanks Paul.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:evHKNn4XEHA.3016@.tk2msftngp13.phx.gbl...
> This is a general SQL Server warning and isn't particular to replication
eg
> you can 2 varchar (8000) cols as you want to a table and the second time
> there'll be this warning (if you do it in QA), as potentially you might
> exceed the 8060ish max row size.
> Regards,
> Paul Ibison
>
sql

Question on scripting

I have a question on coding, just want to see if there is a better way
to do this.

Here is some sample data combinations that would come from 2 different
tables.

Item Method Price Cost Percentage
A-1 L 100 50 10
A-2 D 110 55 15
A-3 U 90 40 65

Ok, here is what I want to do.

If L then 100 x (1+(10/100))
if D then 110 x (1-(15/100))
if U then 40 x (1+(65/100))

To produce 1 number per item.

I can do this obviously using multiple When Then Else statements.
Like

Case when method='L'
then Price * (1+(percentage/100))
else case when method='D'
then Price * (1-(percentage/100))
else Cost * (1+(percentage/100))
end
end

However I was thinking there might just be a better way to do this that
I am not familar with, plus I could easily have 8-10 of these methods
that I need to code.
Any ideas on a better way?
Thanks in advance.On 25 Oct 2006 11:30:11 -0700, mike wrote:

Quote:

Originally Posted by

>I have a question on coding, just want to see if there is a better way
>to do this.
>
>Here is some sample data combinations that would come from 2 different
>tables.
>
>Item Method Price Cost Percentage
>A-1 L 100 50 10
>A-2 D 110 55 15
>A-3 U 90 40 65
>
>Ok, here is what I want to do.
>
>If L then 100 x (1+(10/100))
>if D then 110 x (1-(15/100))
>if U then 40 x (1+(65/100))
>
>To produce 1 number per item.
>
>I can do this obviously using multiple When Then Else statements.
>Like
>
>Case when method='L'
then Price * (1+(percentage/100))
else case when method='D'
then Price * (1-(percentage/100))
else Cost * (1+(percentage/100))
end
>end
>
>
>However I was thinking there might just be a better way to do this that
>I am not familar with, plus I could easily have 8-10 of these methods
>that I need to code.
>Any ideas on a better way?
>Thanks in advance.

Hi Mike,

The obvious simplification is to use one case with multiple WHEN clauses
instead of nesting the CASE expressions, like this:

Case when method='L'
then Price * (1+(percentage/100))
when method='D'
then Price * (1-(percentage/100))
else Cost * (1+(percentage/100))
end

Another possible way to simplify this, depending on the nature of the
other methods, would be to use CASE expressions for the variable parts
of the formula. All formula's above start with either Price or Cost,
then multiply this with either the sum or the difference of 1 and
percentage/100. You could rewrite this as

CASE WHEN method IN ('L', 'D')
THEN Price
ELSE Cost
END * (1 + ((percentage / 100)
* CASE WHEN method = 'D' THEN -1 ELSE 1 END))

--
Hugo Kornelis, SQL Server MVP|||On 25 Oct 2006 11:30:11 -0700, "mike" <mike.a.rea@.gmail.comwrote:

Quote:

Originally Posted by

>I can do this obviously using multiple When Then Else statements.
>Like
>
>Case when method='L'
then Price * (1+(percentage/100))
else case when method='D'
then Price * (1-(percentage/100))
else Cost * (1+(percentage/100))
end
>end
>
>However I was thinking there might just be a better way to do this that
>I am not familar with, plus I could easily have 8-10 of these methods
>that I need to code.

You have overly complicated this CASE expression. It does NOT require
nesting, and either format will handle ten (or fourty) alternative
calculations without becoming awkward.

CASE WHEN method='L'
THEN Price * (1+(percentage/100))
WHEN method='D'
THEN Price * (1-(percentage/100))
WHEN method='U'
THEN Cost * (1+(percentage/100))
END

or:

CASE method
WHEN 'L' THEN Price * (1+(percentage/100))
WHEN 'D' THEN Price * (1-(percentage/100))
WHEN 'U' THEN Cost * (1+(percentage/100))
END

Roy Harvey
Beacon Falls, CT