Row Size Limit?

The following works on my server:
UPDATE tb_tmp
SET sm_PublicText = CONVERT(NVARCHAR(2000), @.data)
WHERE sm_pk = @.id
BUT, at my client's server it does NOT - no errors given, just it is left
NULL. To work I have to change the 2000 (in convert) to 200.
Does he has some row size limit. Where can I look?
Evanpermissions for client?
"Evan Camilleri" wrote:

> The following works on my server:
> UPDATE tb_tmp
> SET sm_PublicText = CONVERT(NVARCHAR(2000), @.data)
> WHERE sm_pk = @.id
>
> BUT, at my client's server it does NOT - no errors given, just it is left
> NULL. To work I have to change the 2000 (in convert) to 200.
> Does he has some row size limit. Where can I look?
>
> Evan
>
>|||The row size limit in 2000 and below is 8060 bytes (including overhead). In
2005, you can overflow
the regular variable length datatypes.
Either way, the max row size does not include text, ntext, image, and the ne
w varchar(max),
nvarchar(max) and varbinary(max).
If it were an overflow problem, SQL Server would return an error message. Pe
rhaps the client
application suppresses this error? Did you execute the UPDATE from Query ana
lyzer, or? Or perhaps
there's a trigger on the table which silently modifies the column value?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Evan Camilleri" <e70mt@.yahoo.co.uk.nospam> wrote in message
news:%23hTaOk4kGHA.3924@.TK2MSFTNGP03.phx.gbl...
> The following works on my server:
> UPDATE tb_tmp
> SET sm_PublicText = CONVERT(NVARCHAR(2000), @.data)
> WHERE sm_pk = @.id
>
> BUT, at my client's server it does NOT - no errors given, just it is left
NULL. To work I have to
> change the 2000 (in convert) to 200.
> Does he has some row size limit. Where can I look?
>
> Evan
>|||I do not think permissions have to do with it since CONVERT(NVARCHAR(200),
@.data) works (with 200 it works, with 2000 it does not)
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:41715CFE-A538-46A4-B17E-9BBC8B383342@.microsoft.com...
> permissions for client?
>
> "Evan Camilleri" wrote:
>|||Check the table definition, also look for any triggers.
ML
http://milambda.blogspot.com/|||
Thanks for your reply. We used Query Analyzer. There is no triggers.
Before this sp is executed I DROP the table and recreate it in the sp
itself... so there is no trigger.
DROP TABLE tb_tmp
CREATE TABLE tb_tmp (
[sm_pk] [int] NOT NULL,
[sm_Ref] [nvarchar] (50) NULL ,
[sm_PublicText] text NULL
)
ALTER TABLE tb_tmp WITH NOCHECK ADD CONSTRAINT [PK_tb_SM] PRIMARY
KEY CLUSTERED ([sm_pk])
INSERT INTO tb_tmp (sm_pk, sm_PublicText)
VALUES (@.id, '')
.......................................SET @.data here
--PRINT @.data ....................prints correctly
-- Save
UPDATE tb_tmp
SET sm_PublicText = CONVERT(NVARCHAR(4000), @.data)
WHERE sm_pk = @.id
As I said this works on my server BUT DOES NOT on the server of the client.
It leaves the row NULL. If I change the 4000 to 500 it works (obviously
truncating my records)
Evan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23K3%23I64kGHA.3596@.TK2MSFTNGP05.phx.gbl...
> The row size limit in 2000 and below is 8060 bytes (including overhead).
> In 2005, you can overflow the regular variable length datatypes.
> Either way, the max row size does not include text, ntext, image, and the
> new varchar(max), nvarchar(max) and varbinary(max).
> If it were an overflow problem, SQL Server would return an error message.
> Perhaps the client application suppresses this error? Did you execute the
> UPDATE from Query analyzer, or? Or perhaps there's a trigger on the table
> which silently modifies the column value?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Evan Camilleri" <e70mt@.yahoo.co.uk.nospam> wrote in message
> news:%23hTaOk4kGHA.3924@.TK2MSFTNGP03.phx.gbl...
>|||this is the SP:
DROP TABLE tb_tmp
CREATE TABLE tb_tmp (
[sm_pk] [int] NOT NULL,
[sm_Ref] [nvarchar] (50) NULL ,
[sm_PublicText] text NULL
)
ALTER TABLE tb_tmp WITH NOCHECK ADD CONSTRAINT [PK_tb_SM] PRIMARY
KEY CLUSTERED ([sm_pk])
INSERT INTO tb_tmp (sm_pk, sm_PublicText)
VALUES (@.id, '')
.......................................SET @.data here
--PRINT @.data ....................prints correctly
-- Save
UPDATE tb_tmp
SET sm_PublicText = CONVERT(NVARCHAR(4000), @.data)
WHERE sm_pk = @.id
As I said this works on my server BUT DOES NOT on the server of the client.
It leaves the row NULL. If I change the 4000 to 500 it works (obviously
truncating my records)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:3F67A2FD-1B7B-40C2-AF32-4524C35961CA@.microsoft.com...
> Check the table definition, also look for any triggers.
>
> ML
> --
> http://milambda.blogspot.com/|||Try casting the value as the actual column's data type:
CONVERT(text, @.data)
ML
http://milambda.blogspot.com/