In this blog we are sharing an issue which can be seen while querying external table created in SQL 2019 with data source pointing to SQL Server 2014. The error message which comes back is “TCP Provider: An existing connection was forcibly closed by the remote host”
While working with PolyBase feature, few of our customer reported an interesting issue. They informed us that when they query large amount of data by creating an external table in SQL Server 2019 with data source as a table in SQL Server 2014, they get below error.
Msg 7320, Level 16, State 110, Line 82
Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". 105082;Generic ODBC error: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: An existing connection was forcibly closed by the remote host.
Additional error <2>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure, SqlState: 08S01, NativeError: 10054.
This error doesn’t appear when:
Here are the steps to reproduce the error:
USE [master] GO DROP LOGIN [polybaselogin] GO CREATE LOGIN [polybaselogin] WITH PASSWORD = N'A_Very_Strong_P@ssw0rd@123ForPolyBaseLogin', DEFAULT_DATABASE = [master] ,DEFAULT_LANGUAGE = [us_english] ,CHECK_EXPIRATION = OFF ,CHECK_POLICY = OFF GO ALTER SERVER ROLE [sysadmin] ADD MEMBER [polybaselogin] GOCREATE DATABASE [SQL2014DB] GO USE [SQL2014DB] GO CREATE TABLE [dbo].[Table_2014] ( [i] [int] IDENTITY(1, 1) NOT NULL ,[j] [char](5000) NULL) GO INSERT INTO [dbo].[Table_2014] (j) VALUES('Microsoft'), ('SQL'),('Server'),('2019'),('Released')
CREATE DATABASE SourceDB GO USE SourceDB GO CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials WITH IDENTITY = 'polybaselogin', SECRET = 'Sysadmin@123'; GO USE SourceDB GO CREATE EXTERNAL DATA SOURCE SQLServer_DestinationDB WITH ( LOCATION = 'sqlserver://10.0.0.4:21433', CREDENTIAL = SQLServerCredentials ); GO CREATE EXTERNAL TABLE SourceDB.[dbo].[External_Table_SQL2019_DestinationTable] ( [i] [int] NULL, [j] char(5000) NULL ) WITH (DATA_SOURCE = [SQLServer_DestinationDB],LOCATION = N'[SQL2014DB].[dbo].[Table_2014]') GO
-- below works SELECT TOP 3 * FROM [SourceDB].[dbo].[External_Table_SQL2019_DestinationTable] -- this fails SELECT TOP 4 * FROM [SourceDB].[dbo].[External_Table_SQL2019_DestinationTable]
This is due to amount of data fetched by single query. In my example, column is char(5000) and each row is very wide. One of our customer reported that he was able to fetch up to 877 rows and when he adds one more, the error appears. Here is the text of error message.
Msg 7320, Level 16, State 110, Line 8
Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". 105082;Generic ODBC error: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: An existing connection was forcibly closed by the remote host.
Additional error <2>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure, SqlState: 08S01, NativeError: 10054 .
As mentioned earlier, the issue exists with external tables created for versions SQL 2014 or below. It would be recommended to use the later versions of SQL.
Hope this helps!
Cheers,
Balmukund
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.