Dead lock operation query

If I saw there's a dead lock through the Enterprise Manager's current
activity section, how can I see those processes which have dead lock
(blocking and being blocked) is running what SQL statement? So that I
can know which SQL statement cause the dead lock there.
Usually when I open the deadlock process, I can see something like
'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
should be cause the the SQL statement in the application scripting
portion.
I can kill the process from there, but before doing that, I wish to see
the SQL statement causing that.
Thanks.
Peter CCHHi
sp_who2 will give you information about the processes involved. You may want
to read
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587 and try
sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
John
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>|||Hi,
To add on to John, Enable trace flag (1204); which will record the deadlock
chain into the error log.
Execute the below command from query analyzer to enable trace flag 1204.
DBCC TRACEON(1204,-1)
Also see the below url to reduce deadlock.
http://www.sql-server-performance.com/deadlocks.asp
Thanks
Hari
SQL Server MVP
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>|||Hari,
Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
Thanks,
Gopi
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
> deadlock chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
>> If I saw there's a dead lock through the Enterprise Manager's current
>> activity section, how can I see those processes which have dead lock
>> (blocking and being blocked) is running what SQL statement? So that I
>> can know which SQL statement cause the dead lock there.
>> Usually when I open the deadlock process, I can see something like
>> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
>> should be cause the the SQL statement in the application scripting
>> portion.
>> I can kill the process from there, but before doing that, I wish to see
>> the SQL statement causing that.
>>
>> Thanks.
>>
>> Peter CCH
>|||The -1 means to enable the flag for ALL connections. Without the -1, it only
enables the flag for the current connection, and connections that have other
trace flags already enabled.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gopi" <rgopinath@.hotmail.com> wrote in message
news:%23Gne01IkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hari,
> Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
> Thanks,
> Gopi
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> To add on to John, Enable trace flag (1204); which will record the
>> deadlock chain into the error log.
>> Execute the below command from query analyzer to enable trace flag 1204.
>> DBCC TRACEON(1204,-1)
>> Also see the below url to reduce deadlock.
>> http://www.sql-server-performance.com/deadlocks.asp
>> Thanks
>> Hari
>> SQL Server MVP
>>
>> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
>> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
>> If I saw there's a dead lock through the Enterprise Manager's current
>> activity section, how can I see those processes which have dead lock
>> (blocking and being blocked) is running what SQL statement? So that I
>> can know which SQL statement cause the dead lock there.
>> Usually when I open the deadlock process, I can see something like
>> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
>> should be cause the the SQL statement in the application scripting
>> portion.
>> I can kill the process from there, but before doing that, I wish to see
>> the SQL statement causing that.
>>
>> Thanks.
>>
>> Peter CCH
>>
>|||We've been using this trace flag with lots of success. You can see in the
SQL error log what DB and the object ids involved (tables & indexes) in the
chain. This makes it "much" easier to find the cause(s) of the deadlock.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
deadlock
> chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> > If I saw there's a dead lock through the Enterprise Manager's current
> > activity section, how can I see those processes which have dead lock
> > (blocking and being blocked) is running what SQL statement? So that I
> > can know which SQL statement cause the dead lock there.
> >
> > Usually when I open the deadlock process, I can see something like
> > 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> > should be cause the the SQL statement in the application scripting
> > portion.
> >
> > I can kill the process from there, but before doing that, I wish to see
> > the SQL statement causing that.
> >
> >
> > Thanks.
> >
> >
> >
> > Peter CCH
> >
>|||Peter CCH wrote:
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to
> see the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
Deadlocks and blocking problems are two different things, although they
do share the "blocking" aspect. It sounds like what you are seeing is
blocking problems, not deadlock issues. Deadlocks are quickly resolved
by SQL Server and don't really show up in system tables. They are
normally identified through application error reporting and through
running traces on a server (from Profiler or using the SQL Trace API).
If you are having blocking problems, you need to identify the long
running transactions and figure out why they are taking so long to
complete. It's possible the application is not fetching result sets
quickly enough or not fetching all results to the end of the result set.
It's also possible there are performance problems in the queries that
require tuning.
The best built-in tool to determine these problems is Profiler or,
better yet, using SQL Trace directly from the sp_trace*() functions. You
can have Profiler script a trace for you using the File - Script Trace
menu option.
Look at the SQL:BatchStarting/Completed and RPC:Starting/Completed
events to start with. Examine the CPU, Duration, and Reads columns and
try to get a handle on the high CPU and long running batches.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Dead lock operation query

If I saw there's a dead lock through the Enterprise Manager's current
activity section, how can I see those processes which have dead lock
(blocking and being blocked) is running what SQL statement? So that I
can know which SQL statement cause the dead lock there.
Usually when I open the deadlock process, I can see something like
'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
should be cause the the SQL statement in the application scripting
portion.
I can kill the process from there, but before doing that, I wish to see
the SQL statement causing that.
Thanks.
Peter CCH
Hi
sp_who2 will give you information about the processes involved. You may want
to read
http://support.microsoft.com/default...b;en-us;224587 and try
sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
John
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegr oups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>
|||Hi,
To add on to John, Enable trace flag (1204); which will record the deadlock
chain into the error log.
Execute the below command from query analyzer to enable trace flag 1204.
DBCC TRACEON(1204,-1)
Also see the below url to reduce deadlock.
http://www.sql-server-performance.com/deadlocks.asp
Thanks
Hari
SQL Server MVP
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegr oups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>
|||Hari,
Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
Thanks,
Gopi
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
> deadlock chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegr oups.com...
>
|||The -1 means to enable the flag for ALL connections. Without the -1, it only
enables the flag for the current connection, and connections that have other
trace flags already enabled.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gopi" <rgopinath@.hotmail.com> wrote in message
news:%23Gne01IkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hari,
> Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
> Thanks,
> Gopi
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
>
|||We've been using this trace flag with lots of success. You can see in the
SQL error log what DB and the object ids involved (tables & indexes) in the
chain. This makes it "much" easier to find the cause(s) of the deadlock.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
deadlock
> chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegr oups.com...
>
|||Peter CCH wrote:
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to
> see the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
Deadlocks and blocking problems are two different things, although they
do share the "blocking" aspect. It sounds like what you are seeing is
blocking problems, not deadlock issues. Deadlocks are quickly resolved
by SQL Server and don't really show up in system tables. They are
normally identified through application error reporting and through
running traces on a server (from Profiler or using the SQL Trace API).
If you are having blocking problems, you need to identify the long
running transactions and figure out why they are taking so long to
complete. It's possible the application is not fetching result sets
quickly enough or not fetching all results to the end of the result set.
It's also possible there are performance problems in the queries that
require tuning.
The best built-in tool to determine these problems is Profiler or,
better yet, using SQL Trace directly from the sp_trace*() functions. You
can have Profiler script a trace for you using the File - Script Trace
menu option.
Look at the SQL:BatchStarting/Completed and RPC:Starting/Completed
events to start with. Examine the CPU, Duration, and Reads columns and
try to get a handle on the high CPU and long running batches.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Dead lock operation query

If I saw there's a dead lock through the Enterprise Manager's current
activity section, how can I see those processes which have dead lock
(blocking and being blocked) is running what SQL statement? So that I
can know which SQL statement cause the dead lock there.
Usually when I open the deadlock process, I can see something like
'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
should be cause the the SQL statement in the application scripting
portion.
I can kill the process from there, but before doing that, I wish to see
the SQL statement causing that.
Thanks.
Peter CCHHi
sp_who2 will give you information about the processes involved. You may want
to read
http://support.microsoft.com/defaul...kb;en-us;224587 and try
sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
John
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>|||Hi,
To add on to John, Enable trace flag (1204); which will record the deadlock
chain into the error log.
Execute the below command from query analyzer to enable trace flag 1204.
DBCC TRACEON(1204,-1)
Also see the below url to reduce deadlock.
http://www.sql-server-performance.com/deadlocks.asp
Thanks
Hari
SQL Server MVP
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>|||Hari,
Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
Thanks,
Gopi
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
> deadlock chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
>|||The -1 means to enable the flag for ALL connections. Without the -1, it only
enables the flag for the current connection, and connections that have other
trace flags already enabled.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gopi" <rgopinath@.hotmail.com> wrote in message
news:%23Gne01IkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hari,
> Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
> Thanks,
> Gopi
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
>|||We've been using this trace flag with lots of success. You can see in the
SQL error log what DB and the object ids involved (tables & indexes) in the
chain. This makes it "much" easier to find the cause(s) of the deadlock.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
deadlock
> chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
>|||Peter CCH wrote:
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to
> see the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
Deadlocks and blocking problems are two different things, although they
do share the "blocking" aspect. It sounds like what you are seeing is
blocking problems, not deadlock issues. Deadlocks are quickly resolved
by SQL Server and don't really show up in system tables. They are
normally identified through application error reporting and through
running traces on a server (from Profiler or using the SQL Trace API).
If you are having blocking problems, you need to identify the long
running transactions and figure out why they are taking so long to
complete. It's possible the application is not fetching result sets
quickly enough or not fetching all results to the end of the result set.
It's also possible there are performance problems in the queries that
require tuning.
The best built-in tool to determine these problems is Profiler or,
better yet, using SQL Trace directly from the sp_trace*() functions. You
can have Profiler script a trace for you using the File - Script Trace
menu option.
Look at the SQL:BatchStarting/Completed and RPC:Starting/Completed
events to start with. Examine the CPU, Duration, and Reads columns and
try to get a handle on the high CPU and long running batches.
David Gugick
Quest Software
www.imceda.com
www.quest.com

dbo.tLongTxt.tLongTxt concatenation

From Books-on-Line (BOL), which by the way is a great resource to have for r
eference (note the highlighted in Red section.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
+ (String Concatenation)
An operator in a string expression that concatenates two or more character o
r binary strings, columns, or a combination of strings and column names into
one expression (a string operator).
Syntax
expression + expression
Arguments
expression
Is any valid Microsoft SQL ServerT expression of any of the data types in t
he character and binary data type category, except the image, ntext, or text
data types.
"Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message news:3ejmg.27141$1f2
.433450@.weber.videotron.net...
> dbo.tLongTxt.tLongTxt is a Ntext 16
>
> does it have to do with the message error
>
>
>
> UPDATE dbo.tLongTxt
> SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + dbo.tLongTxt.tLongTx
t
> + '</EntityDescription>'
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
>
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTx
t
> not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongT
xt
> not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Bas
ed
> On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
> this is the error message I receive
>
> Server: Msg 403, Level 16, State 1, Line 1
> Invalid operator for data type. Operator equals add, type equals text.
>
>
>dbo.tLongTxt.tLongTxt is a Ntext 16
does it have to do with the message error
UPDATE dbo.tLongTxt
SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + dbo.tLongTxt.tLongTxt
+ '</EntityDescription>'
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
this is the error message I receive
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.|||From Books-on-Line (BOL), which by the way is a great resource to have for r
eference (note the highlighted in Red section.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
+ (String Concatenation)
An operator in a string expression that concatenates two or more character o
r binary strings, columns, or a combination of strings and column names into
one expression (a string operator).
Syntax
expression + expression
Arguments
expression
Is any valid Microsoft SQL ServerT expression of any of the data types in t
he character and binary data type category, except the image, ntext, or text
data types.
"Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message news:3ejmg.27141$1f2
.433450@.weber.videotron.net...
> dbo.tLongTxt.tLongTxt is a Ntext 16
>
> does it have to do with the message error
>
>
>
> UPDATE dbo.tLongTxt
> SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + dbo.tLongTxt.tLongTx
t
> + '</EntityDescription>'
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
>
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTx
t
> not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongT
xt
> not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Bas
ed
> On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
> this is the error message I receive
>
> Server: Msg 403, Level 16, State 1, Line 1
> Invalid operator for data type. Operator equals add, type equals text.
>
>
>|||Well thanks very much. This is absolutely true. I had'nt found it. Great.
But it does not solve my problem, except if I change the data type of that
column.
"Arnie Rowland" <arnie@.1568.com> a crit dans le message de news: e8vx3BZlGH
A.3460@.TK2MSFTNGP02.phx.gbl...
From Books-on-Line (BOL), which by the way is a great resource to have for r
eference (note the highlighted in Red section.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
+ (String Concatenation)
An operator in a string expression that concatenates two or more character o
r binary strings, columns, or a combination of strings and column names into
one expression (a string operator).
Syntax
expression + expression
Arguments
expression
Is any valid Microsoft SQL ServerT expression of any of the data types in t
he character and binary data type category, except the image, ntext, or text
data types.
"Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message news:3ejmg.27141$1f2
.433450@.weber.videotron.net...
> dbo.tLongTxt.tLongTxt is a Ntext 16
>
> does it have to do with the message error
>
>
>
> UPDATE dbo.tLongTxt
> SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + dbo.tLongTxt.tLongTx
t
> + '</EntityDescription>'
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
>
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTx
t
> not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongT
xt
> not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Bas
ed
> On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
> this is the error message I receive
>
> Server: Msg 403, Level 16, State 1, Line 1
> Invalid operator for data type. Operator equals add, type equals text.
>
>
>|||Perzackly!
And the varchar() datatype is limited to approximately 8000 bytes (character
s). If that works for you, then that is your choice.
Of course, if you were working with SQL 2005, there is the xml datatype...
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message news
:Zcnmg.34751$1f2.530324@.weber.videotron.net...
Well thanks very much. This is absolutely true. I had'nt found it. Great.
But it does not solve my problem, except if I change the data type of that
column.
"Arnie Rowland" <arnie@.1568.com> a crit dans le message de news: e8vx3BZlGH
A.3460@.TK2MSFTNGP02.phx.gbl...
From Books-on-Line (BOL), which by the way is a great resource to have for r
eference (note the highlighted in Red section.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
+ (String Concatenation)
An operator in a string expression that concatenates two or more character o
r binary strings, columns, or a combination of strings and column names into
one expression (a string operator).
Syntax
expression + expression
Arguments
expression
Is any valid Microsoft SQL ServerT expression of any of the data types in t
he character and binary data type category, except the image, ntext, or text
data types.
"Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message news:3ejmg.27141$1f2
.433450@.weber.videotron.net...
> dbo.tLongTxt.tLongTxt is a Ntext 16
>
> does it have to do with the message error
>
>
>
> UPDATE dbo.tLongTxt
> SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + dbo.tLongTxt.tLongTx
t
> + '</EntityDescription>'
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
>
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTx
t
> not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongT
xt
> not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Bas
ed
> On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
> this is the error message I receive
>
> Server: Msg 403, Level 16, State 1, Line 1
> Invalid operator for data type. Operator equals add, type equals text.
>
>
>|||Well thanks very much. This is absolutely true. I had'nt found it. Great.
But it does not solve my problem, except if I change the data type of that
column.
"Arnie Rowland" <arnie@.1568.com> a crit dans le message de news: e8vx3BZlGH
A.3460@.TK2MSFTNGP02.phx.gbl...
From Books-on-Line (BOL), which by the way is a great resource to have for r
eference (note the highlighted in Red section.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
+ (String Concatenation)
An operator in a string expression that concatenates two or more character o
r binary strings, columns, or a combination of strings and column names into
one expression (a string operator).
Syntax
expression + expression
Arguments
expression
Is any valid Microsoft SQL ServerT expression of any of the data types in t
he character and binary data type category, except the image, ntext, or text
data types.
"Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message news:3ejmg.27141$1f2
.433450@.weber.videotron.net...
> dbo.tLongTxt.tLongTxt is a Ntext 16
>
> does it have to do with the message error
>
>
>
> UPDATE dbo.tLongTxt
> SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + dbo.tLongTxt.tLongTx
t
> + '</EntityDescription>'
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
>
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTx
t
> not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongT
xt
> not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Bas
ed
> On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
> this is the error message I receive
>
> Server: Msg 403, Level 16, State 1, Line 1
> Invalid operator for data type. Operator equals add, type equals text.
>
>
>|||Perzackly!
And the varchar() datatype is limited to approximately 8000 bytes (character
s). If that works for you, then that is your choice.
Of course, if you were working with SQL 2005, there is the xml datatype...
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message news
:Zcnmg.34751$1f2.530324@.weber.videotron.net...
Well thanks very much. This is absolutely true. I had'nt found it. Great.
But it does not solve my problem, except if I change the data type of that
column.
"Arnie Rowland" <arnie@.1568.com> a crit dans le message de news: e8vx3BZlGH
A.3460@.TK2MSFTNGP02.phx.gbl...
From Books-on-Line (BOL), which by the way is a great resource to have for r
eference (note the highlighted in Red section.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
+ (String Concatenation)
An operator in a string expression that concatenates two or more character o
r binary strings, columns, or a combination of strings and column names into
one expression (a string operator).
Syntax
expression + expression
Arguments
expression
Is any valid Microsoft SQL ServerT expression of any of the data types in t
he character and binary data type category, except the image, ntext, or text
data types.
"Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message news:3ejmg.27141$1f2
.433450@.weber.videotron.net...
> dbo.tLongTxt.tLongTxt is a Ntext 16
>
> does it have to do with the message error
>
>
>
> UPDATE dbo.tLongTxt
> SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + dbo.tLongTxt.tLongTx
t
> + '</EntityDescription>'
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
>
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTx
t
> not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongT
xt
> not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Bas
ed
> On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
> this is the error message I receive
>
> Server: Msg 403, Level 16, State 1, Line 1
> Invalid operator for data type. Operator equals add, type equals text.
>
>
>