SQL queries and Excel - refresh query definition

Scenario:

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.

crystal reports using t-sql table functions

Using MS SQL2005, I have created a table function.  Although you can see it in CR2008, when I try to access it directly I get an error message 

Database Connector Error: 'ADO Error Code: )x

Source: Microsoft SQL Native Client

Description Line 1: Invalid procedure number (0), Musst be between 1 and 32767.

SQL State 42000

Native Error: [Database Vendor Code: 1005]'

I think it may be to do with the fact that when you select from your table function in MSSM you need to put the parameters in parenthesis and even if there are no params, you still need to add the brackets to the function call.

The solution to this is to create a view which calls the table function.  This view is then available to CR2008.

HOWEVER...

If you then change the columns that are returned by the table function, the CR dataset is not automatically updated.  In order to update the dataset, you need to edit the view in MSSM and re-save it, then go into CR2008 and right click on the table and Set Datasource Location.  Your new data should now be available for the report.

This is fine for one table, where you know the data has changed.  If you are not sure, or have serveral data maps that have been modified, then it is probably best to use the menu option Database->Verify Database.

Publish Post