VS 2010 SQL03006 error caused by select * from ..


  • 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.

    Monday, June 04, 2012 7:02 AM

All replies

  • Despite the smell of using asterisks in view definitions, you not should use productions databases for code creation. This means create your own development database infrastructure. Then you can simply adjust the views and create a clean model.
    Monday, June 04, 2012 11:45 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.
    Monday, June 04, 2012 11:11 PM
  • Can you please explain it? Who does deployment fail?
    Tuesday, June 05, 2012 11:00 AM
  • 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?

    Wednesday, June 06, 2012 6:16 AM
  • Now I understand. You want to (re)create your database from you model in Visual Studio like described here? Then you should ask you question maybe in an more Visual Studio oriented forum. Otherwise take a look at this link, maybe it helps.
    Wednesday, June 06, 2012 9:26 AM
  • 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.

    Thursday, June 07, 2012 5:05 AM
  • 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.

    Thursday, June 07, 2012 5:18 AM
  • 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.


    Thursday, June 07, 2012 5:32 AM
  • 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.
    Friday, June 08, 2012 1:27 AM