2

I'm trying to copy data from one table on SQL Server 2012 into another table on remote server (Azure)

Azure is added as linked server on SQL Server 2012

I'm creating XML statements for inserts and then executing those statements on remote server as mentioned below. 

DECLARE @xml XML

SET @xml = (
        SELECT 'insert into Table_on_Azure values ('  + convert(varchar(300),isnull(ID, 'NULL')) + ','  + convert(varchar(300),isnull(ST_ID, 'NULL')) + ',' ..... etc etc ...');'
        FROM local_table
        FOR XML path('')
        ) 

DECLARE @sql AS VARCHAR(max)

SET @sql = 'set nocount on;' + cast(@xml AS VARCHAR(max)) + 'set nocount off;' --Converts XML back to a long string
EXEC ('select * from remote_table_2') AT Linked_Azure_Server -- To confirm that the Linked Server is working fine and yes I do get results from this.

EXEC ('use myRemoteDB;' + @sql ) AT Linked_Azure_Server

The reason why I'm doing this is : "The local table has around 1 million records and it was taking me more than 3 hours to push the data onto Linked Server. Then I found this answer Linked Server Insert-Select Performance in which it was suggested to create XML insert statements and execute on remote server.

But When I'm doing this I'm getting below error

OLE DB provider "SQLNCLI11" for linked server "Linked_Azure_Server" returned message "Communication link failure".
TCP Provider: The specified network name is no longer available.

I'm sure the link to Linked_Server is fine. To cross check I wrote this statement EXEC ('select * from remote_table_2') AT Linked_Azure_Server just before inserting records and I do get results from this.

I've checked other answers to similar questions related to Communication Link Failure but no one is really helpful.

Rahul Verma
  • 2,946
  • 14
  • 27
  • I don't think the recommendation is necessarily to use XML. More to run the query *on* Azure to *select from the local server as the remote linked server* as opposed to running the query on your local server and *insert to linked Azure remotely*. Note the difference. Looks like you are possibly still attempting to run the query against the same linked server from Azure (i.e. itself). – Jacob H Nov 14 '17 at 20:32
  • @JacobH: _Looks like you are possibly still attempting to run the query against the same linked server from Azure (i.e. itself)_ : I didn't quite understand. Can you elaborate more on this with some suggestions ? – Rahul Verma Nov 15 '17 at 07:28
  • Are you logged into Azure or your local SQL server when you run this query? – Jacob H Nov 15 '17 at 13:30
  • @JacobH: I'm logged onto SQL Server when I run this query. – Rahul Verma Nov 15 '17 at 13:59
  • Right, so log into Azure server and run a *remote query to your local server*. Instead of what you are currently doing, which is logging in locally and *running a remote query to Azure*. Pull vs push. – Jacob H Nov 15 '17 at 14:26
  • @JacobH: Yes That's the challenge. We can't add non-Azure SQL servers as linked servers in Azure. This is the reason I have to push the data rather than taking pull. Remote queries are possible between Azure instances only. If you can suggest me something I will appreciate it. – Rahul Verma Nov 15 '17 at 14:30
  • Ah good point... maybe try this approach? https://dba.stackexchange.com/questions/168537/executing-query-against-azure-sql-db-using-linked-server-takes-hours – Jacob H Nov 15 '17 at 14:38

1 Answers1

0

I've experienced this before as well, and it dead-stopped my productivity for hours. The key for me was the db url. Which needed the word "secure" in it, as follows:

[db_name].database.secure.windows.net

I was shocked that there was no documentation on this, and the url isn't listed anywhere. For reference, I got to the bottom of it by chance when I stumbled upon this article.

pim
  • 12,019
  • 6
  • 66
  • 69
  • `Linked_Azure_Server` in my original question is actually an object that refers to the server `[server_name].database.secure.windows.net`. I've already mentioned FQDN while setting up this Linked server object. Also I'm able to use this object as I mentioned in my question, but for some reason it's giving me communication link failure. Can you recommend me something else or code changes in my original question. Thanks – Rahul Verma Nov 15 '17 at 06:42