Product(s): Active Roles Posted by blakhani on December 10, 2019 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 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] GO CREATE 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. Here is the workaround to overcome such behavior. We need to modify data source to use a connection option CONNECTION_OPTIONS = ‘UseDefaultEncryptionOptions=false’ First I needed to drop data source as it was used by external table. Since there is no data loss, we should be OK to drop them. -- drop external table USE [SourceDB] GO DROP EXTERNAL TABLE [dbo].[External_Table_SQL2019_DestinationTable] GO -- drop external data source USE [SourceDB] GO DROP EXTERNAL DATA SOURCE [SQLServer_DestinationDB] GO -- create external data source with UseDefaultEncryptionOptions=false . USE SourceDB GO CREATE EXTERNAL DATA SOURCE SQLServer_DestinationDB WITH ( LOCATION = 'sqlserver://10.0.0.4:21433', CREDENTIAL = SQLServerCredentials, CONNECTION_OPTIONS = 'UseDefaultEncryptionOptions=false' ); GO -- create external table 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 Lets try to run the same query. Voilà! It works! Hope this helps! Cheers, Balmukund
Hi Folks, My application is on PB 2017 R2 and its running on windows server 2012 R2 OS. Application is connecting with local DB which is in SQL anywhere and remote database which is in SQL server. This application is basically fetching the files from a location, storing the data in local dbase (SQL anywhere ) and making the PDFs. While creating the PDFs its doing a entry of report category in few tables of SQL server database. Exe is scheduled to execute in every 1 and half hour. Recently we have upgraded our dbase from SQL server 2008 to SQL server 2016. After this we have started receiving the below error frequently. Every alternate day mostly. Error connecting to Web Reports database SQLSTATE = 08001Microsoft SQL Server Native Client 11.0 TCP Provider: An existing connection was forcibly closed by the remote host. We have upgraded SQL native client as well , it has version 2011.110.7001.00. There is no SQL log created for this error at database side. We also checked the database server timeout settings , this is also set as 0 (no timeout limit). We have checked event logs as well but didn't found anything suspicious. Windows firewall is off. As a work around we are restarting the exe and it starts working but we are looking for permanent solution of this. We are struggling to find the cause and solution of this issue since couple of weeks. Any help on this issue would be highly appreciated. Thanks,
My application is on PB 2017 R2 and its running on windows server 2012 R2 OS. Application is connecting with local DB which is in SQL anywhere and remote database which is in SQL server. This application is basically fetching the files from a location, storing the data in local dbase (SQL anywhere ) and making the PDFs. While creating the PDFs its doing a entry of report category in few tables of SQL server database. Exe is scheduled to execute in every 1 and half hour. Recently we have upgraded our dbase from SQL server 2008 to SQL server 2016. After this we have started receiving the below error frequently. Every alternate day mostly. Error connecting to Web Reports database SQLSTATE = 08001 Microsoft SQL Server Native Client 11.0 TCP Provider: An existing connection was forcibly closed by the remote host. We have upgraded SQL native client as well , it has version 2011.110.7001.00. There is no SQL log created for this error at database side. We also checked the database server timeout settings , this is also set as 0 (no timeout limit). We have checked event logs as well but didn't found anything suspicious. Windows firewall is off. As a work around we are restarting the exe and it starts working but we are looking for permanent solution of this. We are struggling to find the cause and solution of this issue since couple of weeks. Any help on this issue would be highly appreciated. Thanks, Deepshikha |
Sqlncli11 tcp provider: an existing connection was forcibly closed by the remote host
zusammenhängende Posts
Urheberrechte © © 2024 frojeostern Inc.
