If it is as you described then it's very simple update query and you probably will not get any noticable loss in performance using dynamic SQL.

This will be bad and...

No, I call this SP from external application.

And this application supplies whole file as a parameter.

SELECT @ptrval = TEXTPTR(NewsText) --- text is wrapped in the varaible

FROM tblNews

WHERE ????

EXECUTE does accept text.

I use to run execute with 20k XML as a parameter. And it works great.

Because in second query you select from ##AvgCost and as I can conclude from the query for 1st derived table it contains more than 1 record per PersonId

Try this:

SELECT...

BOL:

UPDATETEXT

Updates an existing text, ntext, or image field. Use UPDATETEXT to change only a portion of a text, ntext, or image column in place. Use WRITETEXT to update and...

Ignore previous post.

Variables of the datatype TEXT are supported in SQL Server 2000 in the way you use it - as a parameter for SP.

Probably problem is @@identity.

Read BOL...

You messed up with cursors.

Why not to use simple and clear set approach?

DECLARE @p_strXML VARCHAR(8000)

SET  @p_strXML = '

<REQUEST>

     <NUMBER>1</NUMBER>

     <SUB_NUMBER>2</SUB_NUMBER>

     <DATA>

         <F1>1</F1>

         <DETAILS>

               <DETAIL>

                    <AMOUNT>1</AMOUNT>

                    <NOTE>1</NOTE>

               </DETAIL>

             <DETAIL>

                   <AMOUNT>2</AMOUNT>

                   <NOTE>2</NOTE>

            ...

A lot of things.

1st of all, don't use SELECT TOP 100 PERCENT in views.

If you need it for ORDER BY don't do it. It's really bad for performance.

Perform all orderings...

Actually I used bcp to call SP. So, it was command line utility.

I ran the same SP from QA just to find what is causing the error.

And it returned 2...

column sails of my table invoiceslines

column cost of my table invoiceslines

column myprofit [sails]-[cost])*100/[sails]

SELECT sails, cost, (sails-cost)*100/sails as myprofit

FROM ....

Any guess what settings can cause this problem and if it's possible to modify it?

It's not QA, and not bcp.

What could it be?

Select distinct u.userId, a.activityId

from users u, activities a

where not exists (select 1 from attempts t

where t.userid = u.userid and t.activityid = a.activityid)

Finish your statement:

ORDER BY <criteria>

Open BOL and type "WHERE clause, SELECT" in index tab.