External Server and Database references in SSDT

Question

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?

Answer 1

Hi Brett,

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.

 

Thanks,

Alex

Answer 2

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?

Answer 3

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

Answer 4

Hi Brett,

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.

Thanks,
Alex

Answer 5

Hi Brett,

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?

Thanks,

Alex

Answer 6

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)

 

 

Answer 7

Hi Brett,

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?

-Janet Yeilding

Answer 8

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

---

Tech Lead

Answer 9

Hi Frank,

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,

Janet Yeilding

Answer 10

Any updates here?  Please let me know if you can provide any additional information to help us narrow down what's happening.

Thanks,

Janet Yeilding

Answer 11

We aren't able to reproduce this issue with the information provided.  If additional details or repro steps are provided, we will reopen investigation.

Thanks,

Janet Yeilding

Answer 12

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.