Try to include time part in your params i.e. '2004/08/08 00:00:00' (both in SP and in your query) - then run it and check how many rows returned.

This may help...

 bliviu - Thanks for your suggestion.

But it means that every XML parse in SQL server (from text field  column) must have two calls...

I'm not sure what service pack it is - but it's SQL server 2000 enterprise edition with the latest service pack. I can check it tomorrow morning.

Alex

noeld - could you please show your create table statement for dbo.XML_Transactions table?

Thanks,

  Alex

So you are saying you have table XML_Transactions where field Object_XML

is defined as TEXT?

Anyway - I cannot even compile that SP with 1 line of code: SET @sXML = ''

SQL...

Noel - thanks for the link - I knew this option, but I hoped to see something else. 

Basically I resolved that issue by ... not storing XML in...

 Well - that's too simple - I tried to compile the following SP:

CREATE PROCEDURE XXXX

@local TEXT = '',

@anythingelse VARCHAR(100)

AS

begin

  SET @local = 'Some TEXT field here'

end

and I've got the...

 The problem is SQL server itself. You cannot really use nested transactions in T-SQL.

If you rollback - you rollback everything up to the first transaction you started in this batch.

That's...

The problem is that I'm storing XML in table column.

For example I have table:

create table Table1(ID int, InXML text)

Let's  say I have 1 row in this table and InXML column...

Paste your statement(s) into SQL Query analyzer window and click Ctrl-L

 - it will show you the estimated execution plan in the bottom window. Move mouse over every picture - and...

Steve  - It's a good idea to add " union" at the end of every row in your insert statement (except the last one). In this case it's one transaction...