Tuesday, September 13, 2011 4:45 PM
Hi guys, this is on Denali CTP3
When I add a database reference as a Database Location "Different Database, Different Server", it sets up some variables for me $(db) and $(server) but I still get unresolved references when using them e.g. select * from [$(server)].[$(db)].dbo.table .
Why is this, shouldn't this work? Am I going to have trouble deploying this or with the SqlCmd stuff still work on deploy?
- Edited by Brett Gerhardi GRG Tuesday, September 13, 2011 4:47 PM
Tuesday, September 13, 2011 10:43 PM
Couple of things to look at:
1. Ensure that the "Extended T-SQL Verification" property on the file containing the external reference is set to False. Currently, extended verification does not support name parts with variable references.
2. Build the project.
Let us know if this works.
Wednesday, September 14, 2011 8:07 AM
Thanks Alex, will give this a go.
Do we know if this functionality expected to be complete on release of these tools? I'm used to VS 2010 database projects and this worked fine.
With this reduced functionality, I don't quite understand what the point of the database reference "different database, same/different server" functionality is. Unfortunately a lot of real world applications that I have come across do have cross-references, so I really hope that this is going to be fully implemented in sql v.next!
Also what am I turning off when changing "Extended T-SQL Verification" - is it just variable related issues or is it turning off all validation?
Wednesday, September 14, 2011 9:51 AM
Further to this even turning off Extended T-SQL Verification for each of the affected files and rebuilding I am still getting the errors:
C:\Users\gerharb\Documents\Development\XXXX\XXXX Dbs\XXXX Banding Db\Schema\dbo\Stored Procedures\usp_REPORT_TargetChanges_1.sql(35,13): Warning: SQL71502: Procedure: [dbo].[usp_REPORT_TargetChanges] has an unresolved reference to object [$(XXXX)].[dbo].[tblEmployee].
Thursday, September 15, 2011 1:28 AM
Thanks for your reply.
We are aware of the current limitations with Extended T-SQL Verification and plan to have a more complete solution in future releases.
Extended T-SQL Verification is an enhanced level of validation that is performed to ensure that objects defined within your project are semantically correct. The benefit of enabling extended T-SQL verification is greater detection of potential problems that can prevent a successful deployment to a physical server. It is important to note that disabling extended T-SQL verification will NOT turn off all validation.
Please be aware that when disabling extended T-SQL verification on a file, errors can continue to occur within other files that do not have extended T-SQL verification disabled. For example, suppose I define a view "View1" inside a file View1.sql and disable extended T-SQL verification on the file. I then define another view "View2" in file View2.sql. If I reference "View1" within the definition of "View2", then I will need to disable extended verification on file View2.sql as well. In summary, the closure of all files that reference "View1" will need to disable extended verification.
You can globally disable extended T-SQL verification for an entire project in the project properties. Right-click on a project node and choose "Properties". Click on the "Build" tab in the project properties dialog. Uncheck the "Enable Extended Transact-SQL Verification" checkbox. Save the project settings.
Thursday, September 15, 2011 1:39 AM
The warning may be due to the database variable being incorrect. Can you please double check your project references and verify that the database variable is correct?
- Edited by Alex Chin - MSFTMicrosoft Employee, Member Thursday, September 15, 2011 1:40 AM
Tuesday, September 20, 2011 8:50 AM
They are definately correct as far as I can tell
(please note not this is inherited, not my code/style! :) )
SELECT d.CardActivationCode FROM dbo.tblEmployee a INNER JOIN [$(eMandrakeSvr)].[$(eMandrakeDb)].dbo.tblCardOrder b ON a.QdosID = b.ClientsReference INNER JOIN [$(eMandrakeSvr)].[$(eMandrakeDb)].dbo.tblClient c ON b.FK_ClientID = c.PK_ClientID AND c.GPassClientID = @GpassClientID INNER JOIN [$(eMandrakeSvr)].[$(eMandrakeDb)].dbo.tblCard d ON b.PK_CardOrderID = d.FK_CardOrderID WHERE a.PK_EmployeeID = @EmployeeID
Database References in Properties
Database Variable Name: $(eMandrakeDb)
Server Variable Name: $(eMandrakeSvr)
Tuesday, December 27, 2011 11:34 PMOwner
Aplogies for the delayed response. Are you still experiencing this issue? I see that you've been using CTP4-- are you able to use server and database variables in 4 part names successfully in CTP4?
Friday, June 08, 2012 12:00 PMI still have this issue on release. I have references to dummy database projects (suppressed resolve warnings), for synonyms. These synonyms use the other db/other server cmd vars. Views using these synonyms fail on compile. I have suppressed extended t-sql verification on both project and view file.
Saturday, June 09, 2012 5:35 PMOwner
I'm sorry to hear that you're experiencing this issue. Will you please pass on the following details so that we can try to reproduce the issue?
- Steps you took to create the reference (.dacpac or in the same solution, what drop down option you selected for internal/external db/server, variables specified)
- Example of how you're using the reference
- Example error message
Looking forward to hearing back from you. Thanks,
Tuesday, June 26, 2012 6:01 PMOwner
Any updates here? Please let me know if you can provide any additional information to help us narrow down what's happening.
Friday, July 06, 2012 8:11 PMOwner
We aren't able to reproduce this issue with the information provided. If additional details or repro steps are provided, we will reopen investigation.
- Marked As Answer by Janet YeildingMicrosoft Employee, Owner Friday, July 06, 2012 8:11 PM
Wednesday, November 21, 2012 9:48 PM
I believe I recently ran into the same issue. I wrote up a Connect item, http://connect.microsoft.com/SQLServer/feedback/details/766658/ssdt-database-reference-to-views,where I explained the issue when trying to reference a view in the referenced database kept coming up with the unresolved reference error.
During the write up of the detailed explanation I noticed a step I had either forgotten or didn't think of at the original time and it ended up solving my issue.
The fix was that as soon as I added the referenced database, I did a build on the referenced database and that resolved my problem. Perhaps this would fix your issue too.