2

I have two SQL Server 2016 VMs in Azure. I am trying to connect these two sql server instances using External datasource. I can safely connect the two instances from my local pc, remotely.

While querying using external table/data source, I am getting this:

enter image description here

Kindly note

  1. I have used both RDBMS/SHARD_MAP_Manager

  2. Credential is valid credential

  3. I know I can use Linked Servers, but I don't want it. As I am planning to move these databases to SQL Azure. External table and datasource is not mandatory. You can suggest alternatives other than linked servers.

I need a query or part of query that will work with both, SQL Server (virtual / physical server) and SQL Azure (Database as a Service)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Is it erroring on the DROP or the CREATE? Please indicate which line the error occurs on. Are you certain sharding works with VM's? All examples I see are for Azure DB – Nick.Mc Aug 02 '17 at 12:10
  • https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql says: _Use SHARD_MAP_MANAGER when creating an external data source for Elastic Database query for sharding on Azure SQL Database_ - it seems sharding is only for Azure SQL DB, Not VM's – Nick.Mc Aug 02 '17 at 12:11
  • @Nick.McDermaid I tried RDBMS too.. Even my Drop DataSource statement inside` if` giving same error. –  Aug 02 '17 at 12:18
  • What's the compatability level? – Nick.Mc Aug 02 '17 at 13:34
  • master 130 tempdb 130 model 130 msdb 130 TestDb 130 –  Aug 03 '17 at 03:53
  • Mine too. Here's a script that works for me: https://stackoverflow.com/questions/45269147/microsoft-tech-references-for-bulk-insert-may-have-some-gaps-cant-get-them/45452574#45452574. I suggest you copy paste the entire script rather than just screenshotting it. Then you might discover the issue – Nick.Mc Aug 03 '17 at 06:37
  • Mine also highlights exactly the same way: `CREATE EXTERNAL DATA` is one colour and `SOURCE` is a different colour – Nick.Mc Aug 03 '17 at 06:38
  • Did you ever find an answer? – dim_user Jan 30 '18 at 19:50
  • No. We dropped the migration plan and went with old VM based sql server. –  Feb 05 '18 at 16:12

0 Answers0