locked
how to pull mssql 2019 data from Remote server to Local server (in Premises)? RRS feed

  • Question

  • User-1640542475 posted

    Hi

    I need to pull remote data to local server(In Premises) for 
    Analyzing report for Management Dashboard

    I have used Merge command in Stored Procedure, it works fine in same 
    instance or same network SQL Server. we have remote server credential
    It is also our server.

    But how to pull data from remote server.

    Pls advice me

    Thank you...

    Maideen

    It is my Stored Procedure

    	MERGE [dbo].[tbl_Finance] AS TARGET
    	USING edgeadv.[dbo].[MOHead] AS SOURCE 
    	ON (TARGET.DocNo = SOURCE.DocNo) 
    
    /* =============================================
    	UPDATE 
    	===========================================*/
    	WHEN MATCHED AND
    
    		TARGET.DOCNO <> SOURCE.DOCNO OR TARGET.DOCDATE <> SOURCE.DOCDATE OR
    		TARGET.CATEGORY <> SOURCE.CATEGORY OR TARGET.TYPE <> SOURCE.MOTYPE OR
    		TARGET.SUBTOTAL <> SOURCE.SUBTOTAL OR TARGET.GSTP<> SOURCE.GST OR
    		TARGET.GSTR <> SOURCE.GSTAMT OR TARGET.GROSS <> SOURCE.TOTALGROSS OR
    		TARGET.NETTTOTAL <> SOURCE.NETTTOTAL OR TARGET.TAX <> SOURCE.GSTAMT
    	THEN 
    	UPDATE 
    		SET
    		TARGET.DOCNO = SOURCE.DOCNO , TARGET.DOCDATE = SOURCE.DOCDATE ,
    		TARGET.CATEGORY = SOURCE.CATEGORY , TARGET.TYPE = SOURCE.MOTYPE ,
    		TARGET.SUBTOTAL = SOURCE.SUBTOTAL , TARGET.GSTP = SOURCE.GST ,
    		TARGET.GSTR = SOURCE.GSTAMT, TARGET.GROSS = SOURCE.TOTALGROSS ,
    		TARGET.NETTTOTAL = SOURCE.NETTTOTAL, TARGET.TAX = SOURCE.GSTAMT
    	
    /* =============================================
    	INSERT
    	===========================================*/
    	WHEN NOT MATCHED BY TARGET THEN 
    	INSERT 
    	(DocNo, DocDate, Category, Type, SubTotal, GSTP, GSTR, Gross, Tax, NettTotal)
    	VALUES
    	(SOURCE.DocNo, SOURCE.DocDate, SOURCE.Category, SOURCE.MOType, SOURCE.SubTotal, 
    	 SOURCE.GST, SOURCE.GSTAMT, SOURCE.TOTALGross, SOURCE.GSTAMT, SOURCE.NettTotal )
    ;
    

    Monday, January 25, 2021 3:32 AM

Answers

  • User409696431 posted

    https://www.sqlshack.com/querying-remote-data-sources-in-sql-server/ 

    "But there will be situations in which you need to connect to a remote database that is located in a different instance in the same server or in a different physical server, and process its data in parallel with the local data processing."

    See the article for your options and the security changes you need to make.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 27, 2021 12:07 AM