Error while fetching data from remote server

DhanashreePrasun 61 Reputation points
2023-04-04T08:00:33.76+00:00

Hello, We have a scenario where we are fetching data in a hyperscale environment from a Managed instance sql server. This is done through public endpoint. We have a very simple select statement for fetching the records from the MI. When I run it directly on the MI database, it gives results in 10 sec. The count is about 61k. And we are running it from the hyperscale through sp_execute_remote. Below is the code that we run from the HS env. But most of the times, there is an error fetching data:
Msg 50000, Level 16, State 2, Procedure dbo.usp_Update_HourlyCons
An error occurred while retrieving data from remote server.
We have a similar query running against the same database with just different filters and millions of records. That one processes successfully, but this one with just a few thousand records fails most of the time. There is nothing running on the MI or HS when this procedure is running. What could be the possible issue? Any ideas?

DECLARE @LastTimeseries DATETIME2(7) = DATEADD(DAY, -1, (SELECT ISNULL(MAX(INSERT_DATE), '1900-01-01 00:00:00.000') FROM dbo.[HourlyCons]))
declare @stmt nvarchar(max)

set @stmt = '
SELECT
	msv.[IMPORT_SERIES_SEQNO],
	msv.[READING_TIME],
	mis.[BATCH_SEQNO],
	mis.[SERIES_MID],
	mc.[MPOINT_SEQNO],
	mis.[COUNTER_SEQNO],
	mis.[UTILITY],
	mis.[D_FACTOR],
	msv.[D_ORIGIN],
	msv.[Q_STAMP],
	mc.[IS_C_CODE],
	mc.[C_CLASS_ID],
	msv.[R_VALUE],
	mis.[SERIES_TIMESTAMP]
FROM 
	[ark_db].dbo.[SERIES_VALUE] msv
INNER JOIN
	[ark_db].dbo.[IMPORT_SERIES] mis
ON
	msv.IMPORT_SERIE_SEQNO = mis.IMPORT_SERIE_SEQNO
INNER JOIN
	[ark_db].dbo.[COUNTERS] mc
ON
	mis.COUNTER_SEQNO = mc.COUNTER_SEQNO
WHERE
	mis.SERIES_TIMESTAMP >= @LastTimeseries
	AND msv.Q_STAMP >= 2
	AND mc.IS_C_CODE IN(10100, 10102, 10103, 10104, 10115, 10116, 10119, 10120, 10121, 33)'


INSERT dbo.[HourlyCons] 
EXEC sp_execute_remote
			N'ark_db',
			@stmt,
			N'@LastTimeseries datetime2(7)',
			@LastTimeseries

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,868 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. CosmogHong-MSFT 23,561 Reputation points Microsoft Vendor
    2023-04-04T08:48:54.38+00:00

    Hi @DP

    but this one with just a few thousand records fails most of the time

    Do you mean most of the time fail, but sometimes succeed? Please refer to this doc: A network-related or instance-specific error occurred while establishing a connection to SQL Server.

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Olaf Helper 41,021 Reputation points
    2023-04-13T09:55:12.7366667+00:00

    Msg 50000, Level 16, State 2, Procedure dbo.usp_Update_HourlyCons

    Error message 50000 and above are errors raised by customer code, not by the database engine. Review your code where it's raised and log, why it's riased.

    0 comments No comments