Report Model (Data Source View) issue

We've built an oltp that uses nothing but synonyms that reach out to various DB's in the organization thru linked servers.

The issue that we are having is Data Source Views can only see tables and views. Is it possible to use synonyms? If not what would be a work around.

Please advise. Thanks

Steve

I use linked servers in reports (actually, let's be honest, I use linked servers pretty much ALL THE TIME ).

I haven't done this with Report Models that I can recall. But I am pretty sure that what works for me would work in Report Models.

There are a couple of things you can do as a workaround:

1. If you don't have other uses for the synonyms, eliminate the synonym from the equation entirely and just use fully-qualified syntax in views, for example (and of course you don't have to use * here!) :

CREATE VIEW vw_Synonym1 AS SELECT * FROM MyLinkedServer.MyDB1.dbo.MyTable

2. I'm not entirely sure that the models can't use synonyms! It's likely that they can but the wizards and interfaces that you're given in the designer cannot. If I'm right, you just need to write something to handle the model XML directly to get what you want.

I am not going to mess with this to test it out, because I don't spend a lot of time with report models, but start with the XSD (which must be published) and figure out whether the appropriate values should go. Then alter a copy of an existing model that you have to test -- I bet it works. If you want to go this route and get stuck on the details, holler. <g>

3. If you do have other uses for the synonyms, as apparently you do, and if you don't want to mess with the model xml, you can create a view that represents each synonym in a one-to-one relationship. IOW, pretty much CREATE VIEW vw_Synonym1 AS SELECT * FROM Synonym1. Again I know that * is evil but you get the picture <s>. Extend this properly as follows:

You can avoid the evil "*" by interrogating the properties of the thing underneath. You can automate all current synonyms to create new views (and leverage the interrogation code you just wrote) by examining SELECT * FROM sys.synonyms and writing code that works through the list. You can automate all future synonyms you create to do the same thing automatically by using a database trigger. Here is a sketch: