Error while fetching data from remote server

Question

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

Answer 1

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.

Answer 2

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.