So I needed to add a new form field to an existing application, which usually requires a new column to a SQL table. Cool, no biggie. Everything is fine and dandy until I go to SharePoint to view the external lists connected to the database, I kept getting errors about string to datetime conversion. WTF…
These external lists were reading from SQL Views, so I took a look at the content coming from the views, and about halfway through the columns everything was shifted right by one. This happened at exactly where I inserted the new column (I didn’t append it to the table). The issue is that when I created the view, I used a select * instead of implicitly stating the columns. When you do this, I guess SQL Server expands * to all existing columns to make “select a, b, c, d from table” and saves THAT as the View query. So when the “select” from view was being called from SharePoint, it was referencing the old “c” column position but now that was different. (NULL for all existing rows)
This should help you to visualize:
1) Created view with select *
2) SQL Server expanded to select a, b, c, d and saved as view.
3) Now by selecting all from view, it’s using: select a, b, c, d from myView
a | b | c | d
1 | 2 | 3 | 4
1) Added column newCol after column b
2) View now still calling using: select a,b, c, d from myView, but now newCol was being used for c, and then c was used:
a | b | c | d
1 | 2 | NULL | 3
AH d is using c column value!! So the rest are now off by one.
To fix, just ran ALTER on the view
But lesson learned, DON’T create views with SELECT *