May 30, 2011 at 5:28 am
I am an experienced developer, with a very good knowledge of SQL Server (versions 5 onwards), but have hit a big problem with SSRS.
I have a model I created using Autogenerate from connection on a report server.
The model was then saved to an smdl file and uploaded using the web based Report Manager to a few other servers and client installations.
This model needs updating. I can click on Regenerate on the actual model which is the original, but that server is no longer available.
From experience, when Upload File is used to deploy a Model, the Regenerate Model button does not show anymore.
Each of the client deployments mentioned above has many reports using this model (including a lot of client specific ones, which have been created over time), and thus the GUID values in the underlying Model, so a new Model from scratch isn't really possible as the GUIDs will all be different.
The reason for the new model, is that the original model was generated based on a database with a few FK relationships missing, and these need to be added, and in addition, a lot of Columns and Tables have been added to the underlying database.
I have tried a few things:
1) I downloaded the SMDL file from a client deployment, brought it back to the dev machines, opened it in BIDS and pointed at the new database structure, updated the Data Source View and then the Model, but at the Autogenerate Model stage I get a lot of errors relating to columns / roles / not existing, being wrong and such like.
2) I tried to trick things, regenerated a model, swapped it out for the same name, got the GUID error.
This is one hell of a learning curve for me in terms of reports, I had hoped to learn a lot more before going for it, but things changed and the original deployments etc were not done by me, so I am picking up a left over "mess"
Can anyone shed any light, help, give any tips, anything?
If I have been vague please post and I will reply with more details!
Thanks in advance
Ian
May 31, 2011 at 12:12 am
I came across a similar problem a year or two ago; a company had started down the road of report models and none of the reports would work after some tables and field names were changed (it also appeared even recreating the table from a schema declaration would invalid all the GUIDs too). So many GUIDs were wrong that BIDS would refuse to do anything with the model anymore.
Luckily, the workload was small enough that I could run the reports alongside with the SQL Profiler tool to sniff out the final query that SSRS was sending; then I recreated SQL statements from those, adjusted them as necessary, and replaced the report model data source with the queries.
It sounds like you won't be able to do this because your clients really use the models, and there are so many it would be a huge job. So, I feel really sorry for you, and that the whole thing was really poorly thought out design on MS's part. It's no surprise that hardly any of the SQL books (even 2005) go into much detail about report models in actual production use.
I never did find anything about how the GUIDs were internally mapped to real tables and fields. I wonder if it's stored somewhere between the model definition, the report file, the database info fields, or the reportserver database. If you can find the link, you might be able to programmatically regenerate the links with very little code.
Definitely, definitely, and please, keep us informed with what you do in the end though.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply