1

I have a database that is sitting on on-prem sql server. I go to management studio and then select the database that I want to deploy on Azure and follow the guide. Eventually, I am getting a bunch of errors such as

One or more unsupported elements were found in the schema used as part of a data package. Error SQL71561: Error validating element [dbo].[patientInfo]: View: [dbo].[patientInfo] has an unresolved reference to object [198.18.21.71].[db_cs_dw].[dbo].[invoice]. External references are not supported when creating a package from this platform.

Error SQL71562: Error validating element [dbo].[getAllClaimsByPatientId]: Procedure: [dbo].[getAllClaimsByPatientId] has an unresolved reference to object [198.18.21.71].[db_cs_dw].[dbo].[all_clients_view]. External references are not supported when creating a package from this platform.

Do I need to set up a linked server on my Azure sql server with [198.18.21.71]. Can I do that?

Improve this question

2 Answers 2

Reset to default
2

As others have rightly stated, you cannot set-up a linked server in Azure. The validation failures you're seeing are because 4-part [server].[database].[schema].[table] references have been detected, that aren't supported in Azure.

Assuming that your [db_cs_dw] database will also be hosted in Azure, you can perform an Elastic Query to perform the cross-database queries. There is a bit of set-up required to undertake this, including creating a security context, data source and External Tables to represent the [invoice] table or any other data you wish to access in the other database. Your old references to [198.18.21.71].[db_cs_dw].[dbo].[invoice] would now reference the External Table. You could also encapsulate this further by introducing Synonyms - so the synonym references the External Table and the [dbo].[patientInfo] view will reference the synonym.

Otherwise, you will need to host the data within the same database and have some kind of ETL/replication process in place to move/sync the data.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Do I need to set up a linked server on my Azure sql server with [198.18.21.71]. Can I do tHAT ?

you can't setup a linked server from Azure to onpremises..

as the error says,you need to resolve those conflicts by either removing the views or modfying them

Improve this answer

4 Comments

How to modify them?
You can remove those views
I need those views and stored procedures .. is there a way to fix this error or go around it?
No.you can't.but one approach can be to materialize data from your view to an onpremise table and replicate it

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.