The project for db1 has a db reference to db2 as db1 uses views in db2. db2 contains views with 'select * from '. This causes the much hated 3006 error. The work-around of course is to simply alter the views in db2 to explicitly provide the column names. This I have proven. However in my case I don't own db2 (different department, vendor app, whatever...) and it's locked down so no possibility in altering the views in production.
The db1 model must know the column names however it does not due to the view in db2 not providing them. Surely others have this scenario? For me this is a show stopper. VS cannot be used to develope db projects.
- Edited by Colin Hodges Monday, June 04, 2012 7:04 AM
I agree completely however if I understand your point then you have not understood mine. I do indeed use dev and pre-prod environments. In proving my work-around I modified views to state column names as required in order for the model to build in dev. This is not a solution as a project deployment against prod will still fail.
Deploy fails with 70 or so of these errors:
C:\TechCMDBScratch\sql\debug\TechCMDBScratch.dbschema(33177,7): Error : The reference to external elements from the source named ' CMDBDW.dbschema' could not be resolved, because no such source is loaded.
Perhaps due to the unresolved references within the .dbschema files. Setting the ‘Suppress errors ..’ check box when adding the db references makes no difference.
The project has 371 SQL04151 warnings is that matters?
Do you have the model of db2 included in your project?
If the model of db2 is correctly referenced then VS should be able to resolve such references.
If db2 is developed using other tools than VS, then you can create dummy project for db2 with the required objects only.
I'm not sure your question is valid. Is a .dbschema file a model? If yes then yes I do. If no then no I don't have a model included for db2. As you suggested I created a dummy project for db2 yet I could not build it due to 100's of errors of various types. I gave up fixing the errors after a few days and created the .dbschema file for db2 using vsdbcmd.exe instead.
Yes, I mean .dbschema or VS project.
What I did in past is to create project for db2, but instead of fixing all errors in the project created from reverse engineering I did it from scratch and included only objects used by my project.
If it is 10 or 20 tables/views then it should be easily doable.
If the number is much bigger, it can be a problem. But looking at the number of warnings (usually one per column) it is not that bad.
Thanks Piotr, your method prompted me to try again. Some time ago I imported the entire db2 and removed objects not required but to no avail. This time I started with an empty project and created from scratch just the objects required by db1 and what do you know! The deploy now works.
So the dummy project method works and the vsdbcmd.exe built .dbschema file method does not. I'm sure if I analysed the dbschema files from each of the these two I could determine why but this 4 hour job has already consumed 12. Time for other things.
- Edited by Colin Hodges Thursday, June 14, 2012 4:35 AM