none
Why does a reference to master.dacpac use a relative file path to something that is in Program Files?

    Question

  • Hello,

    We store our SSDT projects in a source control repository and not all of our developers have the same mappings to their local hard drive (I recognise that this is not advisable but nonetheless, its not a situation that should cause any issues). Unfortunately it *does* cause an issue because if you add a database reference to system database 'master' then the following gets added to your .sqlproj file:

        <ArtifactReference Include="..\..\..\Program Files %28x86%29\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac">
          <HintPath>..\..\..\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac</HintPath>
          <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
          <DatabaseVariableLiteralValue>master</DatabaseVariableLiteralValue>
        </ArtifactReference>

    Notice how the path to master.dacpac includes ..\..\..\ in order to get to the drive root and thus this requires every developer to have the same local drive mappings.

    Please tell me that this isn't really the case, that I'm missing something obvious, and that the SSDT developers weren't really dumb enough to do this. Why does this limitation exist?

    Regards
    JT


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Thursday, June 07, 2012 4:02 PM

Answers

All replies

  • I think I'd tend to agree. While I really liked the easy inclusion of "master" and "msdb" for the different DB versions, looks like I'll go back to dropping them into our shared "Schemas" folder so everyone can access easily. :(

    I feel a Connect submission coming on.... :)

    As for our install paths, sometimes we run out of space on C:, especially if using as SSD which has much smaller capacity. I know that a lot of my apps are installed on a D: or E: drive because of that limited space. When our developers try to run things, they use C: as they pretty much all run VMs on Macs. This would definitely cause an issue.

    I like the ..\..\ type notation for schemas stored in a common place, but this isn't the best choice for the global schemas stored in your VS install location.

    Thursday, June 07, 2012 4:20 PM
  • Could you edit the project file by hand once the reference is added and change the path to reference the environment variable for visual studio install path instead? Something like 
    <ArtifactReference Include="$(VSINSTALLDIR)Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac">
    I think this might work but I havent tried it :-)
    Thursday, June 07, 2012 4:21 PM
  • Good thinking Mike, I'll try that when I get in tomorrow. If it works...heavens knows why they didn't do it like that in the first place! Perhaps because VSINSTALLDIR might change if a new version of VS is installed? Who knows!

    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Thursday, June 07, 2012 5:34 PM
  • Hey Guys,

    We have taken a fix in this area recently so that the references to system DACPACs do not rely on relative pathing. Look for this fix to be included in an upcoming release of SSDT! In the mean time, the workaround is to manually edit the project file if you want to reference via absolute path.

    Thanks,

    Adam


    Adam Mahood - Program Manager - Data-Tier Application Framework (DACFX)/SQL Server Data Tools

    Thursday, June 07, 2012 7:11 PM
    Owner
  • Adam,

      Will this work regardless of what machine is using these files or will it be hard-coded to a certain path? Will it handle the different versions if those change in the project (2005 vs 2008 vs 2012)?

    Thursday, June 07, 2012 9:35 PM
  • Hey Peter,

    Just so we don't duplicate info, take a look where Patrick has addressed the same issue with regards to cross-machine/VS version compatibility in the thread here - http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/1c5edbcc-477b-439d-99d0-2d320ea4b9e4 . In terms of different target platform dacpacs, the references will change as they are updated in the project/IDE.

    Thanks,

    Adam


    Adam Mahood - Program Manager - Data-Tier Application Framework (DACFX)/SQL Server Data Tools

    Friday, June 08, 2012 12:49 AM
    Owner
  • Hey Guys,

    We have taken a fix in this area recently so that the references to system DACPACs do not rely on relative pathing. Look for this fix to be included in an upcoming release of SSDT! In the mean time, the workaround is to manually edit the project file if you want to reference via absolute path.

    Thanks,

    Adam


    Adam Mahood - Program Manager - Data-Tier Application Framework (DACFX)/SQL Server Data Tools

    Thanks Adam.

    Just to clarify... using an absolute path still relies on everyone having Visual Studio installed in the same place, right? If so, I'd say that that isn't a satisfactory workaround.

    I tried using Mike's suggestion of leveraging %VSINSTALLDIR% by changing the ArtifactReference to:

        <ArtifactReference Include="$(VSINSTALLDIR)\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac">
          <HintPath>$(VSINSTALLDIR)\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac</HintPath>
          <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
          <DatabaseVariableLiteralValue>master</DatabaseVariableLiteralValue>
        </ArtifactReference>

    And on reloading the project my 'master' database reference has a warning icon over it. When I look at the properties of it the full path is given as "C:\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac"

    It seems my best (only???) option is to move master.dacpac into my solution and use a relative path there.

    JT


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Friday, June 08, 2012 8:44 AM
  • Hey Guys,

    We have taken a fix in this area recently so that the references to system DACPACs do not rely on relative pathing. Look for this fix to be included in an upcoming release of SSDT! In the mean time, the workaround is to manually edit the project file if you want to reference via absolute path.

    Thanks,

    Adam


    Adam Mahood - Program Manager - Data-Tier Application Framework (DACFX)/SQL Server Data Tools

    Thanks Adam.

    Just to clarify... using an absolute path still relies on everyone having Visual Studio installed in the same place, right? If so, I'd say that that isn't a satisfactory workaround.

    I tried using Mike's suggestion of leveraging %VSINSTALLDIR% by changing the ArtifactReference to:

        <ArtifactReference Include="$(VSINSTALLDIR)\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac">
          <HintPath>$(VSINSTALLDIR)\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac</HintPath>
          <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
          <DatabaseVariableLiteralValue>master</DatabaseVariableLiteralValue>
        </ArtifactReference>

    And on reloading the project my 'master' database reference has a warning icon over it. When I look at the properties of it the full path is given as "C:\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac"

    It seems my best (only???) option is to move master.dacpac into my solution and use a relative path there.

    JT


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    OK, extra info. I tried this:

        <ArtifactReference Include="$(VSINSTALLDIR)\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac">
          <HintPath>..\..\..\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac</HintPath>
          <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
          <DatabaseVariableLiteralValue>master</DatabaseVariableLiteralValue>
        </ArtifactReference>

    (i.e. I changed <HintPath>) and in this case the warning icon on the database reference disappeared but upon build I still got the error:

    SQL72027: File "C:\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac" does not exist.

    So, I'm no further along and now I'm left wondering what <HintPath> is all about :)

    Adam, could you fill us in on what the fix is that you have made exactly?

    thanks
    Jamie


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Friday, June 08, 2012 8:51 AM
  • Jamie,

      I think it's the fix referenced in the post above - still have to try it myself. (Sorry for duplicating the info - may have saved posts this time, though. :) )

    For now you might want to place this in your .sqlproj file after our calculation of VisualStudioVersion and before the <Reference> node.  Then use "MyDacPacRootPath" in the Reference statement.

      <PropertyGroup>
        <MyDacPacRootPath Condition="'$(VisualStudioVersion)' == '10.0' AND '$(MyDacPacRootPath)' == ''">$(VS100COMNTOOLS)..\..\</MyDacPacRootPath>
        <MyDacPacRootPath Condition="'$(VisualStudioVersion)' == '11.0' AND '$(MyDacPacRootPath)' == ''">$(VS110COMNTOOLS)..\..\</MyDacPacRootPath>
      </PropertyGroup>

    -Peter

    Friday, June 08, 2012 1:13 PM
  • Jamie,

      I think it's the fix referenced in the post above - still have to try it myself. (Sorry for duplicating the info - may have saved posts this time, though. :) )

    For now you might want to place this in your .sqlproj file after our calculation of VisualStudioVersion and before the <Reference> node.  Then use "MyDacPacRootPath" in the Reference statement.

      <PropertyGroup>
        <MyDacPacRootPath Condition="'$(VisualStudioVersion)' == '10.0' AND '$(MyDacPacRootPath)' == ''">$(VS100COMNTOOLS)..\..\</MyDacPacRootPath>
        <MyDacPacRootPath Condition="'$(VisualStudioVersion)' == '11.0' AND '$(MyDacPacRootPath)' == ''">$(VS110COMNTOOLS)..\..\</MyDacPacRootPath>
      </PropertyGroup>

    -Peter

    OK, thanks Peter. I've now reverted to using .dacpac files stored in my solution - strange though that $(VSINSTALLDIR) when used in <ArtifactReference> wasn't recognised.

    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Friday, June 08, 2012 1:15 PM
  • This doesn't seem to help the TFS build. The dacpacs will have to go in my solution too.


    Tech Lead

    Tuesday, June 12, 2012 10:33 AM
  • Agreed with Jamie & Frank.  Back to using some shared schema folder for our projects until this is fixed in the product and released to us. :)

    -Peter

    Tuesday, June 19, 2012 6:50 PM
  • This is causing major issues in our shop, because some developers have VS2010 installed in an alternate folder tree from C:\Program Files (x86)\...   (Not sure why that was done, but it was never a problem before we started to use SSDT.)

    We use TFS for source control, and if we use the standard (relative) references then each time someone checks in a different master/msdb reference in the .sqlproj, it causes someone else's project build to break the next time they GLV.  I've spent several days going around in circles, trying to reconcile this. I tried including the master.dacpac and msdb.dacpac in the local solution, but now some folks are getting warning signs and errors when I try to include them as a database reference.

    I'm very interested in knowing how it's going to be addressed, because I really don't want to go back and re-install VS2010 on all those developer's systems.

    Thursday, June 21, 2012 3:21 PM
  • Joe, Frank, etc.:  I'm not quite sure what the problem is?  I tried the solution proposed (i.e. add the MyDacPacRootPath variable, and reference that in the ArtifactReference) and it seems to work fine.  Is it not working at all for you?  Or is it something to do with TFS?  I agree that it needs fixing (@MSFTies: will future versions use $(DacPacRootPath) by default?) but it doesn't seem to be a show-stopper to me...

    Tom

    Tuesday, June 26, 2012 9:24 AM