SCENARIO 1:
I have a SQL Server stored procedure that returns rows/columns. Then in SP Designer (SPD) I create an external content type (ECT) and push to Central Admin (CA). In CA, I assign permissions. On the site, I create an External List (EL) and can view my data. All good.
I change a column name from "Vendor Name" to "Vendor in the procedure. Now I go into SPD to alter the ECT and SOMETIMES it cannot be opened, giving an error:
"The BDC Service application Business Data Connectivity Service is not accessible. The full exception text is: Xml type 'List of xdt:untypedAtomic' does not support a conversion from Clr type 'Guid' to Clr type 'String'."
I didn't see what that had to do with my changes. Undoing my change to the procedure didn't resolve it. I had to delete the ECT and ET and recreate them. However, it sometimes happened again.
SCENARIO 2
While testing to see if it happened again, I discovered it sometimes did not. I had different problems.
After renaming the field in the proc, I went into SPD and successfully opened the ECT and made changes, pushing it CA. In CA, I discovered all permissions had been removed. This is a problem. Why does this happen? How to avoid?
So I redo permissions, then go to the site, where two different "sub" problems occur.
SCENARIO 2 A
The External List is broken and cannot be viewed or updated to show the new column name. I have to delete the list and recreate it. This obviously kills any views and removes it from every page where it was, etc., or anything referencing it. This is a nightmare. How to avoid this? I thought of one way, leading to....
SCENARIO 2 B
Instead of creating an External List, I edited a web page, added a Business Data List (BDL) web part and added my ECT to it. Everything was fine. I then purposely renamed a column in the proc again, opened SPD and (this time) was able to open the ECT, updating the field name. I pushed to CA. All permissions were removed (sigh). I re-added them. Then I went to my web page with my BDL, which was broken. I edited the web part and was able to update it so that data appeared onscreen.
BUT, the old "Vender Name" field was missing (no surprise as I'd renamed it). But the new "Vendor" field was also missing. In the BDL web part, I edited the view, discovering to my surprise that NEITHER field was in the list of fields that could be added to the view. In SPD, I opened the ECT to verify the new field was there (it was). No amount of pushing the ECT to CA, redoing permissions, and editing the BDL resulted in the new field appearing. Then, finally, somehow, the new field appeared onscreen without me ever adding it to the BDL view - caching issue?
THE QUESTION
So now I'm left with two questions: 1. Can you use a External List and avoid the problem above? 2. Why do permissions disappear every time the ECT is updated and how to prevent this?
Thanks.
for Scenario 1: Sharepoint is not cool with the "GUID" datatype. Before committing the change to your ECT, breadcrumb back one level to the ECT (sharepoint will alert you if you're going "too far back" and might lose your changes) and check the datatypes that sharepoint has assigned to each field, if you notice any GUID datatypes, you may have to manually coerce them to varchar in your view/proc and close/reopen the ECT.
Scenario 2: This is likely a timing issue and has happened to me before. What happened is that you created you ECT in designer and saved it, then went to CA to set permissions. if you had no reason to re-update your ECT you would have been all good. But the problem was that you needed to make a change AFTER setting the permissions but probably had left the newly created ECT open in designer. So what you have to do is this - after setting the permissions in CA, DO NOT SAVE ANY CHANGES TO THAT ECT UNTIL YOU VERIFY THAT YOU SEE THE PERMISSIONS YOU SET IN CA IN DESIGNER (this is the box in the upper right). This is the problem : SP designer doesnt let you SET permissions but will HAPPILY override them when saving, so if you see no permissions in designer, then saving that ECT will "reset" the permissions to empty. After setting permissions in designer, you have to wait/refresh (sometimes close and re-open the ECT) the ECT in designer until you see the permissiosn there as well.
Scenario 2B: often making changes to ECTs have to let them "propagate" through to the site (this is just how I envision it, i didnt read this). After making changes I've seen my ECTs break completely (you'll get a correlation ID error that wont be useful) or just not seeing my changes. The solution is "Smoke if ya got em". I usually wait at least 10 minutes after making an update until I start being concerned if the ECT is broken, not displaying my change, etc.
Hope this helps!