Error deploying "Unexpectedly Failed to locate the field..."
ToddHarry
Posts: 5
Morning all:
I'm running into an error with SQL Compare trying to deploy a stored procedure change from one DB to another. In trying to move proc "A" from server "A" to server "B", I get an error about a missing FileTable "C" on server "B" (server "A" has the FileTable defined, "B" does not). The error reads: "Unexpectedly Failed to locate the field stream_id on the table [dbo].[{file table C}] for the {key name} foreign key". I'm on SQL Compare version 12.1.0.3760 Standard, and I've submitted this error report through the UI a few times, but have not received any reply yet.
What's interesting is that I can recreate the error at will whenever I try to Deploy any object from A to B, but when I'm moving objects from B to A, I don't encounter this issue. I'd be happy to be contacted by a support rep to send screen shots or schema information if that is helpful. I'd like to get this resolved as I'm resorting to manually ALTERing objects in SSMS to deploy code from server A to B, since SQL Compare isn't working to help me migrate between those two environments only.
I'm curious to see if this is just a standard type of bug in SQL Compare, or if it's specific to my environment, since the tool is getting snagged on a File Table object in the compare at Deploy time. Anyone else having a similar issue? Is there a workaround?
Many thanks,
Todd M. Harry
I'm running into an error with SQL Compare trying to deploy a stored procedure change from one DB to another. In trying to move proc "A" from server "A" to server "B", I get an error about a missing FileTable "C" on server "B" (server "A" has the FileTable defined, "B" does not). The error reads: "Unexpectedly Failed to locate the field stream_id on the table [dbo].[{file table C}] for the {key name} foreign key". I'm on SQL Compare version 12.1.0.3760 Standard, and I've submitted this error report through the UI a few times, but have not received any reply yet.
What's interesting is that I can recreate the error at will whenever I try to Deploy any object from A to B, but when I'm moving objects from B to A, I don't encounter this issue. I'd be happy to be contacted by a support rep to send screen shots or schema information if that is helpful. I'd like to get this resolved as I'm resorting to manually ALTERing objects in SSMS to deploy code from server A to B, since SQL Compare isn't working to help me migrate between those two environments only.
I'm curious to see if this is just a standard type of bug in SQL Compare, or if it's specific to my environment, since the tool is getting snagged on a File Table object in the compare at Deploy time. Anyone else having a similar issue? Is there a workaround?
Many thanks,
Todd M. Harry
Comments
Since I didn't receive any community feedback, nor response from Red Gate to my submissions using the in-app error reporting, our team started experimenting to figure out how to work around this issue. We found we could recreate the issue 100% of the time any time the source environment for the compare had a normal user-defined table (dbo.{whatever}) that has a column with a Foreign Key referencing the stream_id" column of a FileTable.
Here's how we worked around it:
Normally when we push code, we use three scripts: a SQL Compare-produced 2nd script for producing the "meat" portion ("Deployment") of our code pushes, sandwiched between two additional scripts we use for data transformation (a "PreDeployment" script we run first, and a "PostDeployment" script we run third, ala SSDT-style.) So, we dropped the Foreign Key in our Source database, so SQL Compare could be used to generate the 2nd script (no FK, no error). And then we added the FK back in the Post-Deploy. The thought is, when we go to push code between server A and B next time, the FK will be there in both tables, and so SQL Compare won't stumble on it when trying to calculate the dependencies to produce the deploy script.
Note that we were able to recreate this regardless of whether or not we had "Deploy all dependencies (recommended)" checked in the Deployment wizard.
Hope this helps someone.... we don't often have FK's to FileTable columns (this is a first for us, in a project with 100's of tables and views, and several 100's of stored procs), so I don't see us having to remember this workaround for long, or use it often... but I do hope that this issue gets resolved in the next release of SQL Compare or two so we don't have to work around the issue.
Post back if anyone has any questions or concerns.
Thanks.
Todd M. Harry