| Groups | Blog | Home |
sql server programming : writing ntext: strange issue for MVPs
I am having trouble writing/saving large text strings to a sql server 2000 table with 3 ntext columns. The command object (from adodb or ado.net) times out. I have looked through all the postings and help and it would seem that this should always be a problem and that using WRITETEXT or UPDATETEXT is one way to address the issue. The STRANGE part is that this does not consistently happen: a) inconsistency #1: the problem does not occur on 2 dev servers (xeon), but happens on another (pentium). Is there some type of hardware limitation? I have tried sql server developer SP4 and enterprise Sp4 - same issues occur on the same machine b) inconsistency #2: if I restart the sql server service, the update statement works fine from both query analyzer and from the ado.net app. The minute I run a select statement against this table, the update statement subsequently hangs. I've checked the active connections and they're not running excessivley. what am I missing? thx
Uri
The database is tiny. The test table I am using has just 1 record in it. Further, the situations where this works, the database is much larger with many more records. So its not a size issue. My original statement was a simple SQL UPDATE statement (not even using the UPDATETEXT / WRITETEXT approach). It makes no difference where I execute this from (ado.net, query analyzer etc), the SQL SERVER will hang regardless. Here is an example of the statement: Update myTable set BigNTextField = N'.....long text string here.....' where id=12345 Avi [quoted text, click to view] "Uri Dimant" wrote:
> Hi,Avi > > I'm not a MVP , however if you post DDL+ sample data I could suggest you > something. > > What is the error you are getting? > How big is your database? > Have you looked at an execution plan of the query? Does an optimizer use > indexes defined on the table? > > > "Avi" <Avi@discussions.microsoft.com> wrote in message > news:5FD694E8-12BD-41B3-A9D8-E4B93C157FD5@microsoft.com... > > Hi > > > > I am having trouble writing/saving large text strings to a sql server 2000 > > table with 3 ntext columns. The command object (from adodb or ado.net) > > times > > out. I have looked through all the postings and help and it would seem > > that > > this should always be a problem and that using WRITETEXT or UPDATETEXT is > > one > > way to address the issue. > > > > The STRANGE part is that this does not consistently happen: > > a) inconsistency #1: the problem does not occur on 2 dev servers (xeon), > > but > > happens on another (pentium). Is there some type of hardware limitation? I > > have tried sql server developer SP4 and enterprise Sp4 - same issues occur > > on > > the same machine > > b) inconsistency #2: if I restart the sql server service, the update > > statement works fine from both query analyzer and from the ado.net app. > > The > > minute I run a select statement against this table, the update statement > > subsequently hangs. I've checked the active connections and they're not > > running excessivley. > > > > what am I missing? > > > > thx > > Avi > >
Yes. I understand it works fine. The problem is that there is some
inconsistency. Hence the title "strange". [quoted text, click to view] "Uri Dimant" wrote:
> Avi > I did some testing and it worked fine (SS2000, Personal Edition ,SP3) > > > > create table tmp(pkid int identity,txt text) > > insert tmp select replicate('a',8000) > > select * from tmp > > go > create proc myusp > @t1 text, > @t2 text, > @pkid int > as > set nocount on > > --overriding the current text > update tmp > set txt=@t1 > where pkid=@pkid > > --usage > declare @1 varchar(8000),@2 varchar(5555) > select @1=replicate('b',8000),@2=replicate('c',5555) > Exec myusp @1, @2, 1 > > > > > > > "Avi" <Avi@discussions.microsoft.com> wrote in message > news:D500CF92-9EB4-4F85-AE50-1E6F658CA61B@microsoft.com... > > Uri > > > > The database is tiny. The test table I am using has just 1 record in it. > > Further, the situations where this works, the database is much larger with > > many more records. So its not a size issue. > > > > My original statement was a simple SQL UPDATE statement (not even using > > the > > UPDATETEXT / WRITETEXT approach). It makes no difference where I execute > > this from (ado.net, query analyzer etc), the SQL SERVER will hang > > regardless. > > Here is an example of the statement: > > > > Update myTable set BigNTextField = N'.....long text string here.....' > > where > > id=12345 > > > > Avi > > > > "Uri Dimant" wrote: > > > >> Hi,Avi > >> > >> I'm not a MVP , however if you post DDL+ sample data I could suggest you > >> something. > >> > >> What is the error you are getting? > >> How big is your database? > >> Have you looked at an execution plan of the query? Does an optimizer use > >> indexes defined on the table? > >> > >> > >> "Avi" <Avi@discussions.microsoft.com> wrote in message > >> news:5FD694E8-12BD-41B3-A9D8-E4B93C157FD5@microsoft.com... > >> > Hi > >> > > >> > I am having trouble writing/saving large text strings to a sql server > >> > 2000 > >> > table with 3 ntext columns. The command object (from adodb or ado.net) > >> > times > >> > out. I have looked through all the postings and help and it would seem > >> > that > >> > this should always be a problem and that using WRITETEXT or UPDATETEXT > >> > is > >> > one > >> > way to address the issue. > >> > > >> > The STRANGE part is that this does not consistently happen: > >> > a) inconsistency #1: the problem does not occur on 2 dev servers > >> > (xeon), > >> > but > >> > happens on another (pentium). Is there some type of hardware > >> > limitation? I > >> > have tried sql server developer SP4 and enterprise Sp4 - same issues > >> > occur > >> > on > >> > the same machine > >> > b) inconsistency #2: if I restart the sql server service, the update > >> > statement works fine from both query analyzer and from the ado.net app. > >> > The > >> > minute I run a select statement against this table, the update > >> > statement > >> > subsequently hangs. I've checked the active connections and they're not > >> > running excessivley. > >> > > >> > what am I missing? > >> > > >> > thx > >> > Avi > >> > >> > >> > >
Avi
I did some testing and it worked fine (SS2000, Personal Edition ,SP3) create table tmp(pkid int identity,txt text) insert tmp select replicate('a',8000) select * from tmp go create proc myusp @t1 text, @t2 text, @pkid int as set nocount on --overriding the current text update tmp set txt=@t1 where pkid=@pkid --usage declare @1 varchar(8000),@2 varchar(5555) select @1=replicate('b',8000),@2=replicate('c',5555) Exec myusp @1, @2, 1 [quoted text, click to view] "Avi" <Avi@discussions.microsoft.com> wrote in message news:D500CF92-9EB4-4F85-AE50-1E6F658CA61B@microsoft.com... > Uri > > The database is tiny. The test table I am using has just 1 record in it. > Further, the situations where this works, the database is much larger with > many more records. So its not a size issue. > > My original statement was a simple SQL UPDATE statement (not even using > the > UPDATETEXT / WRITETEXT approach). It makes no difference where I execute > this from (ado.net, query analyzer etc), the SQL SERVER will hang > regardless. > Here is an example of the statement: > > Update myTable set BigNTextField = N'.....long text string here.....' > where > id=12345 > > Avi > > "Uri Dimant" wrote: > >> Hi,Avi >> >> I'm not a MVP , however if you post DDL+ sample data I could suggest you >> something. >> >> What is the error you are getting? >> How big is your database? >> Have you looked at an execution plan of the query? Does an optimizer use >> indexes defined on the table? >> >> >> "Avi" <Avi@discussions.microsoft.com> wrote in message >> news:5FD694E8-12BD-41B3-A9D8-E4B93C157FD5@microsoft.com... >> > Hi >> > >> > I am having trouble writing/saving large text strings to a sql server >> > 2000 >> > table with 3 ntext columns. The command object (from adodb or ado.net) >> > times >> > out. I have looked through all the postings and help and it would seem >> > that >> > this should always be a problem and that using WRITETEXT or UPDATETEXT >> > is >> > one >> > way to address the issue. >> > >> > The STRANGE part is that this does not consistently happen: >> > a) inconsistency #1: the problem does not occur on 2 dev servers >> > (xeon), >> > but >> > happens on another (pentium). Is there some type of hardware >> > limitation? I >> > have tried sql server developer SP4 and enterprise Sp4 - same issues >> > occur >> > on >> > the same machine >> > b) inconsistency #2: if I restart the sql server service, the update >> > statement works fine from both query analyzer and from the ado.net app. >> > The >> > minute I run a select statement against this table, the update >> > statement >> > subsequently hangs. I've checked the active connections and they're not >> > running excessivley. >> > >> > what am I missing? >> > >> > thx >> > Avi >> >> >>
Hi,Avi
I'm not a MVP , however if you post DDL+ sample data I could suggest you something. What is the error you are getting? How big is your database? Have you looked at an execution plan of the query? Does an optimizer use indexes defined on the table? [quoted text, click to view] "Avi" <Avi@discussions.microsoft.com> wrote in message news:5FD694E8-12BD-41B3-A9D8-E4B93C157FD5@microsoft.com... > Hi > > I am having trouble writing/saving large text strings to a sql server 2000 > table with 3 ntext columns. The command object (from adodb or ado.net) > times > out. I have looked through all the postings and help and it would seem > that > this should always be a problem and that using WRITETEXT or UPDATETEXT is > one > way to address the issue. > > The STRANGE part is that this does not consistently happen: > a) inconsistency #1: the problem does not occur on 2 dev servers (xeon), > but > happens on another (pentium). Is there some type of hardware limitation? I > have tried sql server developer SP4 and enterprise Sp4 - same issues occur > on > the same machine > b) inconsistency #2: if I restart the sql server service, the update > statement works fine from both query analyzer and from the ado.net app. > The > minute I run a select statement against this table, the update statement > subsequently hangs. I've checked the active connections and they're not > running excessivley. > > what am I missing? > > thx > Avi
Have you checked for locks on the data you are updating?
You say this does not happen on two dev servers, but does happen on the third. Are you talking about 3 different databases running on each of these servers, or and application running on each? [quoted text, click to view] "Avi" <Avi@discussions.microsoft.com> wrote in message news:5FD694E8-12BD-41B3-A9D8-E4B93C157FD5@microsoft.com... > Hi > > I am having trouble writing/saving large text strings to a sql server 2000 > table with 3 ntext columns. The command object (from adodb or ado.net) times > out. I have looked through all the postings and help and it would seem that > this should always be a problem and that using WRITETEXT or UPDATETEXT is one > way to address the issue. > > The STRANGE part is that this does not consistently happen: > a) inconsistency #1: the problem does not occur on 2 dev servers (xeon), but > happens on another (pentium). Is there some type of hardware limitation? I > have tried sql server developer SP4 and enterprise Sp4 - same issues occur on > the same machine > b) inconsistency #2: if I restart the sql server service, the update > statement works fine from both query analyzer and from the ado.net app. The > minute I run a select statement against this table, the update statement > subsequently hangs. I've checked the active connections and they're not > running excessivley. > > what am I missing? > > thx > Avi
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |