StrataFrame Forum

Data Deploy Fails on XML Fields

http://forum.strataframe.net/Topic21897.aspx

By Derek Price - 2/10/2009

We have a simple table with no relationships that is not deploying data due to an XML Field. The DDT error is shown in the attached file "DDT_Data_Deploy_Error.png" and the actual table properties are in the file "Settings_Table_XML.png." Here's a snippet from the text log file:



2/10/2009 8:57:49 AM-> Deploying data from OHRSettings...

2/10/2009 8:57:49 AM-> An error occurred while deploying the data to the server.

2/10/2009 8:57:49 AM-> SqlException

2/10/2009 8:57:49 AM-> Procedure or function 'OHRSettingsInsertOverwrite' expects parameter '@SettingsXML', which was not supplied.




We have a Data Deployment package that will copy data if the User_fk = -1. If there's any data that matches this, the deploy fails with the above message. I checked that the OHRSettingsInsertOverwrite SP is on the db and it is:



USE [SYSTOC800]

GO

/****** Object: StoredProcedure [dbo].[OHRSettingsInsertOverwrite] Script Date: 02/10/2009 10:26:24 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[OHRSettingsInsertOverwrite] @OHRSettings_pk as Int, @User_fk as Int, @RetrievalKey as VarChar(255), @GroupKey as VarChar(255), @SettingsXML as Xml, @RowVersion as Int, @RowState as TinyInt AS BEGIN DELETE FROM OHRSettings WHERE OHRSettings_pk = @OHRSettings_pk SET IDENTITY_INSERT OHRSettings ON INSERT INTO OHRSettings (OHRSettings_pk, User_fk, RetrievalKey, GroupKey, SettingsXML, RowVersion, RowState) VALUES (@OHRSettings_pk, @User_fk, @RetrievalKey, @GroupKey, @SettingsXML, @RowVersion, @RowState) SET IDENTITY_INSERT OHRSettings OFF END




Have you run into any issues with XML fields? If so, do you have a workaround for this situation? I can provide more info if you need it.



Thanks,

Derek
By Dustin Taylor - 2/10/2009

Do you have an example of where you are calling the custom insert procedure?

If I understand correctly, you are deploying everything, and then via a script are seeding deployment data based off of the PK of your user field. So if there are any -1 users, you are calling the OHRSettingsInsertOverwrite stored sproc to insert the values/settings.  Can you copy/paste the code that does that check on -1 and then calls the insert?

I haven't used XML parameters myself much, but I'll check with the other guys and see if they are aware of any gotchas there. In the mean time the above will help us get a better picture of what exactly is happening.

Thanks! Smile

By Derek Price - 2/10/2009

You've almost got it. We do NOT create the OHRSettingsInsertOverwrite stored proc anywhere in our DDT package, so we have to assume that StrataFrame is generating it. I've attached the table properties of the OHRSettings table. We have no other SP defined anywhere named "OHRSettingsInsertOverwrite." I've attached a screenshot of the auto-generated SP which do not include OHRSettingsInsertOverwrite. I've also attached our data deployment section.
By Trent L. Taylor - 2/11/2009

Derek, there is no way we are going to get to the bottom of this like this.  StrataFrame should not be creating anything outside of what you saw in the table properties section.  So there is something else in the mix here.  We will need your PKG file and anything else that may be needed in order to reproduce your problem.  We are going to have to reproduce this in order to give you any type of direct answer.

Also, do you get the same error if you deploy to a new database (instead of updating an existing database)?  Thanks.

By Derek Price - 2/11/2009

Hi Trent - it's been emailed.



Thanks
By Dustin Taylor - 2/12/2009

Hi Derek Smile

I've been working with your package file here, and it looks like we are going to have to come at this from another angle. I can't get the package file you gave me to deploy to my localhost even far enough to get the error you are seeing. Can you reproduce this in a smaller scale to remove some of the other factors? If you can get me a package that only has a single databse with a single table, and the fewest attributes to reproduce your issue, that will make things much easier.

Also, as I understand it you are doing procedures based on the data that already exists in the database (i.e. looking for primary keys of -1), correct? If so, the small sclae database that corresponds with your package file and send that along as well it would be great. That way we'll know we are working from the same starting point.

Thanks!