May 22, 2006 at 8:24 am
Hi,
we have a procedure via biztalk to update 20 tables via a partitioned view but we are getting the error message:
The adapter failed to transmit message going to send port "SQL://dawhodnx/MISSQL/". It will be retransmitted after the retry interval specified for this Send Port. Details:"HRESULT="0x80040e14" Description="UNION ALL view 'MIS_RTLSAL_LOAD_VIEW' is not updatable because a partitioning column was not found."
The view works OK on out test system but when we deploy it onto our live system it always fails with the above error.
We have compared both databases and there are no differences as far as we can see in the schemas, any help would be appreciated.
Regards
Andy
May 22, 2006 at 10:51 am
It seems you are Updating a UNION ALL View which is what a Partitioned view is by default with stored proc but you cannot do that because you are updating something not updatable by ANSI SQL definition because it spans more than one table. Microsoft covered that limitation with an INSTEAD OF trigger so your stored proc must include an INSTEAD OF trigger for the operation to work without error. Try the link below for more about the limitations and restriction of partitioned Views. Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms187956(SQL.90).aspx
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
May 22, 2006 at 2:41 pm
Many thanks Gift,
Will let you know how I get on in the morning, as am just rebuliding the indexes at the moment, as was advised as this may be a cause due to index fragmentation and the index was being ignored? as the test insert worked Ok on our test system and the developers PC test database and all three databases are identical.
Many thanks again for your speedy responce.
Kind regards Andy
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply