large varchar update fails on sql 2000 server, works on dev. machine

A production system (server 2003 enterprise edition) sql 2000 server,
service pack 4 won't update a sort of large varchar field, which works
fine on my development machine, (xp pro sql 2000).
try this in the query analyzer:
CREATE TABLE [dbo].[tbl_why] (
[pr_id] [int] IDENTITY (1, 1) NOT NULL ,
[pr_user_snapshot] [varchar] (2000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--create a row by entering something in the snapshot field like
"what the fu!$*!?"
--test this
declare @.pr_user_snapshot varchar(2000)
set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
associated with these applications:</td><td class=InputLabel
>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
starting on:</td><td class=InputLabel >JAN 1</td></
tr><tr><td>There is a cost per item of:</td><td class=InputLabel
>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
td><td class=InputLabel > </td></tr><tr><td>There is a maximum
cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
tr><tr><td>Proceeds applied to Financial Account:</td><td
class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
applied to Financial Account:</td><td class=InputLabel >20256200
Building Legalization (Penalty) </td></tr>'
print len(@.pr_user_snapshot)
UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
1
...not brain surgery...
It updates to a blank, not null result. Somewhere around 800
characters the thing starts working on the server. Increasing the
field size makes no difference.
Is there some server setting I've missed that is set on my dev. box?
I can't find anything.
What's with this' Is this me'
ThanksOn Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td>
</tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00&am
p;nbsp;</td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:<
/
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
> 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this' Is this me'
> Thanks
[vbcol=seagreen]
Perhaps, did you notice that the table is empty? This means you can't
update can you?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td>
</tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00&am
p;nbsp;</td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:<
/
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
> 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this' Is this me'
> Thanks
I added this before the update
insert tbl_why
select 'bla'
and had no problem
So again are you sure the table is not empty?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for checking.
I am sure the table is not empty.
As I said, it will start updating at around 800 characters, but the
string I'm using is showing a len of around 1550.
Are YOU on a server? - this works for me on my local machine but not
on either of 2 servers (as described) I need to get this going on.
Seems like a setting but what?

> I added this before the update
> insert tbl_why
> select 'bla'
> and had no problem
> So again are you sure the table is not empty?|||Script the entire DDL for the table, including triggers. I suspect either a
poorly written trigger or multiple tables with the same name but different
owners. Both are a frequent cause of unexpected behavior. Your own
statements did not fully qualify the table.
If you still can't figure it out, then I suggest you create/run a complete
script to reproduce the problem. This includes the table creation DDL,
which you yourself run on the "server" in question and verify as acting in
the manner described. A script similar to the one your posted (but with
an insert statement and fully qualified tablenames) works correctly on 2000
sp3a. I seriously doubt that the version of sql server or the OS has
anything to do with the issue.

large varchar update fails on sql 2000 server, works on dev. machine

A production system (server 2003 enterprise edition) sql 2000 server,
service pack 4 won't update a sort of large varchar field, which works
fine on my development machine, (xp pro sql 2000).
try this in the query analyzer:
CREATE TABLE [dbo].[tbl_why] (
[pr_id] [int] IDENTITY (1, 1) NOT NULL ,
[pr_user_snapshot] [varchar] (2000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--create a row by entering something in the snapshot field like
"what the fu!$*!?"
--test this
declare @.pr_user_snapshot varchar(2000)
set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
associated with these applications:</td><td class=InputLabel
>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
starting on:</td><td class=InputLabel >JAN&nbsp;1</td></
tr><tr><td>There is a cost per item of:</td><td class=InputLabel
>200.00&nbsp;</td></tr><tr><td>There is a fixed cost of:</td><td
class=InputLabel >&nbsp;</td></tr><tr><td>There is a minimum cost of:</
td><td class=InputLabel >&nbsp;</td></tr><tr><td>There is a maximum
cost of:</td><td class=InputLabel >&nbsp;</td></tr><tr><td>Late charge
basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
tr><tr><td>Proceeds applied to Financial Account:</td><td
class=InputLabel >20255001 Elevator&nbsp;</td></tr><tr><td>Penalties
applied to Financial Account:</td><td class=InputLabel >20256200
Building Legalization (Penalty)&nbsp;</td></tr>'
print len(@.pr_user_snapshot)
UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
1
...not brain surgery...
It updates to a blank, not null result. Somewhere around 800
characters the thing starts working on the server. Increasing the
field size makes no difference.
Is there some server setting I've missed that is set on my dev. box?
I can't find anything.
What's with this? Is this me?
Thanks
On Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN&nbsp;1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00&nbsp;</td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel >&nbsp;</td></tr><tr><td>There is a minimum cost of:</
> td><td class=InputLabel >&nbsp;</td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel >&nbsp;</td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator&nbsp;</td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty)&nbsp;</td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
> 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this? Is this me?
> Thanks
[vbcol=seagreen]
Perhaps, did you notice that the table is empty? This means you can't
update can you?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||On Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN&nbsp;1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00&nbsp;</td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel >&nbsp;</td></tr><tr><td>There is a minimum cost of:</
> td><td class=InputLabel >&nbsp;</td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel >&nbsp;</td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator&nbsp;</td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty)&nbsp;</td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
> 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this? Is this me?
> Thanks
I added this before the update
insert tbl_why
select 'bla'
and had no problem
So again are you sure the table is not empty?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Thanks for checking.
I am sure the table is not empty.
As I said, it will start updating at around 800 characters, but the
string I'm using is showing a len of around 1550.
Are YOU on a server? - this works for me on my local machine but not
on either of 2 servers (as described) I need to get this going on.
Seems like a setting but what?

> I added this before the update
> insert tbl_why
> select 'bla'
> and had no problem
> So again are you sure the table is not empty?
|||Script the entire DDL for the table, including triggers. I suspect either a
poorly written trigger or multiple tables with the same name but different
owners. Both are a frequent cause of unexpected behavior. Your own
statements did not fully qualify the table.
If you still can't figure it out, then I suggest you create/run a complete
script to reproduce the problem. This includes the table creation DDL,
which you yourself run on the "server" in question and verify as acting in
the manner described. A script similar to the one your posted (but with
an insert statement and fully qualified tablenames) works correctly on 2000
sp3a. I seriously doubt that the version of sql server or the OS has
anything to do with the issue.

large varchar update fails on sql 2000 server, works on dev. machine

A production system (server 2003 enterprise edition) sql 2000 server,
service pack 4 won't update a sort of large varchar field, which works
fine on my development machine, (xp pro sql 2000).
try this in the query analyzer:
CREATE TABLE [dbo].[tbl_why] (
[pr_id] [int] IDENTITY (1, 1) NOT NULL ,
[pr_user_snapshot] [varchar] (2000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--create a row by entering something in the snapshot field like
"what the fu!$*!?"
--test this
declare @.pr_user_snapshot varchar(2000)
set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
associated with these applications:</td><td class=InputLabel
>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
starting on:</td><td class=InputLabel >JAN 1</td></
tr><tr><td>There is a cost per item of:</td><td class=InputLabel
>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
td><td class=InputLabel > </td></tr><tr><td>There is a maximum
cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
tr><tr><td>Proceeds applied to Financial Account:</td><td
class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
applied to Financial Account:</td><td class=InputLabel >20256200
Building Legalization (Penalty) </td></tr>'
print len(@.pr_user_snapshot)
UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id = 1
--
...not brain surgery...
It updates to a blank, not null result. Somewhere around 800
characters the thing starts working on the server. Increasing the
field size makes no difference.
Is there some server setting I've missed that is set on my dev. box?
I can't find anything.
What's with this' Is this me'
ThanksOn Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id => 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this' Is this me'
> Thanks
>>What's with this' Is this me'
Perhaps, did you notice that the table is empty? This means you can't
update can you?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id => 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this' Is this me'
> Thanks
I added this before the update
insert tbl_why
select 'bla'
and had no problem
So again are you sure the table is not empty?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for checking.
I am sure the table is not empty.
As I said, it will start updating at around 800 characters, but the
string I'm using is showing a len of around 1550.
Are YOU on a server? - this works for me on my local machine but not
on either of 2 servers (as described) I need to get this going on.
Seems like a setting but what?
> I added this before the update
> insert tbl_why
> select 'bla'
> and had no problem
> So again are you sure the table is not empty?|||Script the entire DDL for the table, including triggers. I suspect either a
poorly written trigger or multiple tables with the same name but different
owners. Both are a frequent cause of unexpected behavior. Your own
statements did not fully qualify the table.
If you still can't figure it out, then I suggest you create/run a complete
script to reproduce the problem. This includes the table creation DDL,
which you yourself run on the "server" in question and verify as acting in
the manner described. A script similar to the one your posted (but with
an insert statement and fully qualified tablenames) works correctly on 2000
sp3a. I seriously doubt that the version of sql server or the OS has
anything to do with the issue.

large inserts

Dear Experts,

What is the best way to do a large insert WITHOUT having direct access
to the machine SQL Server is running on? For example, imagine I want
to insert something like 20,000 records. If I were to have access to
the server, I could BULK INSERT into a temp table and then insert into
the destination table. But if I can't create a file on the server to
use for BULK INSERT, what is the next best alternative to doing lots
of 1 record insert statements?

Thanks,
-EminOn Thu, 05 Jul 2007 17:34:42 -0000, Emin <emin.shopper@.gmail.com>
wrote:

Quote:

Originally Posted by

>Dear Experts,
>
>What is the best way to do a large insert WITHOUT having direct access
>to the machine SQL Server is running on? For example, imagine I want
>to insert something like 20,000 records. If I were to have access to
>the server, I could BULK INSERT into a temp table and then insert into
>the destination table. But if I can't create a file on the server to
>use for BULK INSERT, what is the next best alternative to doing lots
>of 1 record insert statements?
>
>Thanks,
>-Emin

The Bulk Copy command line utility BCP.EXE is one alternative, as are
DTS (SQL Server 2000) and SSIS (2005) executing locally.

Roy Harvey
Beacon Falls, CT|||Doesn't the client need something like SQL Management studio installed
to use DTS or SSIS? Ideally, I'd like something where the client can
just connect to the database using odbc or ado and do the insert as
opposed to requiring the client to have special software installed.

Thanks,
-Emin

On Jul 5, 5:28 pm, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

>
The Bulk Copy command line utility BCP.EXE is one alternative, as are
DTS (SQL Server 2000) and SSIS (2005) executing locally.
>
Roy Harvey
Beacon Falls, CT

|||On Jul 5, 12:34 pm, Emin <emin.shop...@.gmail.comwrote:

Quote:

Originally Posted by

Dear Experts,
>
What is the best way to do a large insert WITHOUT having direct access
to the machine SQL Server is running on? For example, imagine I want
to insert something like 20,000 records. If I were to have access to
the server, I could BULK INSERT into a temp table and then insert into
the destination table. But if I can't create a file on the server to
use for BULK INSERT, what is the next best alternative to doing lots
of 1 record insert statements?
>
Thanks,
-Emin

You can put your file in a shared folder - BULK INSERT can read it if
it is accessible by the account SQL Server runs under.

Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/|||You don't need the full set of client tools to run DTS packages, just
the dtsrun.exe utility. And for SSIS packages there is the dtexec
command line utility, though I have not used it. And as Erland said,
for BCP you need bcp.exe.

Roy Harvey
Beacon Falls, CT

On Thu, 05 Jul 2007 21:55:53 -0000, Emin <emin.shopper@.gmail.com>
wrote:

Quote:

Originally Posted by

>Doesn't the client need something like SQL Management studio installed
>to use DTS or SSIS? Ideally, I'd like something where the client can
>just connect to the database using odbc or ado and do the insert as
>opposed to requiring the client to have special software installed.
>
>Thanks,
>-Emin
>
>On Jul 5, 5:28 pm, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

>>
>The Bulk Copy command line utility BCP.EXE is one alternative, as are
>DTS (SQL Server 2000) and SSIS (2005) executing locally.
>>
>Roy Harvey
>Beacon Falls, CT

>