I eneded up approaching it from a different angle and used temporary tables in stead

thanks, that does work. I do have another question related to the same problem though.

Im using an IF ELSE statement, how do i get it to work with a...

The function actually works if i run the query, but its highlighted in red in the query window of management studio

'cannot find either column 'dbo' or the user defined function...

and (GetDate() <= (DATEADD( month,CONVERT(INT,Lookup.SubRef),Data_Landline.ModifiedDate)))

i was trying to use this

and (GetDate() <= (DATEADD( month,CONVERT(INT,dbo.UDF_ParseNumChars(Lookup.SubRef)),Data_Landline.ModifiedDate)))

the subref column may contain some text instead of a number, so the calculation wont...

heres the code

ALTER FUNCTION [dbo].[UDF_ParseNumChars]

(

@string VARCHAR(8000)

)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @IncorrectCharLoc SMALLINT

SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)

WHILE @IncorrectCharLoc > 0

BEGIN

SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')

SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)

END

IF LEN(@string) = 0

SET @string...

if you read my post again, youll see that it was prefixed with dbo.

thanks, that sorted it

I dont reference the column anywhere in my insert statement, im expecting the database to insert the current date, but it doesnt

finally managed to solve this, heres the answer

BEGIN TRY

EXEC sp_xml_preparedocument @idoc OUTPUT, @_xml

BEGIN TRANSACTION

UPDATE AdapterTransaction

SET StatusID = @UpdateStatus

FROM OPENXML(@idoc,'/Root/id')

WITH (id bigint '.') xmlTable

WHERE AdapterTransaction.TransactionID = xmlTable.id

EXEC sp_xml_removedocument @idoc

COMMIT

END TRY

Input XML

<Root>

<id>3756</id>

<id>3757</id>

<id>3758</id>

<id>3759</id>

<id>3760</id>

<id>3761</id>

</Root>

Input Status value = 1

the table has a primary key column 'TransactionID' and a statusID column of...

Ive followed the suggestion from microsoft (http://support.microsoft.com/kb/315968/en-gb)

but it just will not change any data. Can anyone help ?

BEGIN TRY

EXEC sp_xml_preparedocument @idoc OUTPUT, @_xml

BEGIN TRANSACTION

UPDATE AdapterTransaction

SET StatusID...

Ive taken another look at the sql and modified it slightly to this

EXEC sp_xml_preparedocument @idoc OUTPUT, @_xml

BEGIN TRANSACTION

UPDATE Table1

SET StatusID = @UpdateStatus

WHERE Table1.ID IN

(SELECT ox.id FROM OPENXML(@idoc,'/Root/id')

WITH (id bigint)...

OK, I guess no one can help me

Anyone ?

ive managed to sort it anway, thanks