In SQL I have a table function which returns a set of data.
Because you can't access table functions directly in Access and Excel (I am working in Excel at the moment..) we also have to create a view
create view dbo.myview as select * from dbo.mytablefunction
When you query this view in Excel, you get back the expected data set.
Now I enhance the table function to add new fields to the data set.
If you run select * from dbo.mytablefunction() then the new fields are returned. If you try to query the view in Excel, the new fields are not available - Confounding mystery!
After some poking around and not finding a solution on the web, I 'stumbled' upon the solution.
When I ran the view (select * from dbo.myview), it also did not return the new fields. I had to modify the view (open it in MSSM and execute the alter view script) and then the new fields were available. now when I access the view in Excel, the new fields are there - hooray!!
It is a shame that SQL does not warn you (or give you a prompt to update dependent queries) - yet another reason not to use select * I guess.
No comments:
Post a Comment