June 2023
M	T	W	T	F	S	S
	1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30

Archive for the ‘SQL Server 2019’ Category

SQL Server 2019 PolyBase Error: TCP Provider: An existing connection was forcibly closed by the remote host

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
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:

Data source is created for SQL Server running 2016 or above.

Data set is not very big.

Here are the steps to reproduce the error:

In SQL Server 2014, create a login, create a database and then table inside it. Insert some rows. (note that I have selected column as char(5000) so that we fetch big chunk of data in each row)

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')

Now, create external table in SQL Server 2019. Location given in data source is IP:Port of SQL Server 2014 instance.

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

At this point out setup is ready. Lets reproduce the behavior now.

If we try to query 3 rows from external table, it works fine.

If we fetch 4 rows then it fails.

-- 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

Posted in Polybase, SQL Server 2019 | Tagged: An existing connection was forcibly closed by the remote host | 3 Comments »

SQL Server 2019 – Installation Error: An error occurred for a dependency of the feature causing the setup process for the feature to fail.

Posted by blakhani on November 8, 2019

After release of SQL Server 2019, one of our customer was trying to install SQL Server 2019 and encountered an error message. Since I was able to reproduce the issue in my lab, I contacted SSMS product group and they were kind enough to fix this issue very quickly. As of now, this is already called out in the release notes. The purpose of this blog is to show how to confirm if you are hitting the same issue. We would learn how to look at various log file and what are the error messages we need to look.

Issue Description:

Installation of SQL Server 2019 from removable media fails if SQL Server Management Studio 18.3 or earlier is installed. In my lab, I downloaded ISO from download center and mounted it. Here is the final screen shot showing installation failure.

When we click on the hyperlink on the screen, it opens up setup summary file which has this information about failed component. (Database Engine Services)

Detailed results:
Feature:                       Database Engine Services
Status:                        Failed
Reason for failure:            An error occurred for a dependency of the feature causing the setup process for the feature to fail.
Next Step:                     Use the following information to resolve the error, and then try the setup process again.

Next step is to look at Detail.txt file for any hint about dependent feature. (Search for “at Microsoft” keyword)

Target package: "E:\1033_ENU_LP\x64\setup\x64\sqlncli.msi"
InstallPackage: MsiInstallProduct returned the result code 1602.
No retry-able MSI return code detected.
ExecuteActionWithRetryHelper.Failed actionToExecute is ‘Install_sqlncli_Cpu64_Action’, stack trace    at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClass2_0.<ExecuteActionWithRetryHelper>b__0()
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionWithRetryHelper(WorkflowObject metaDb, ActionKey action, ActionMetadata actionMetadata, TextWriter statusStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.InvokeAction(WorkflowObject metabase, TextWriter statusStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionEngine.RunActionQueue()
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClass2_0.<ExecuteActionWithRetryHelper>b__0()
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionWithRetryHelper(WorkflowObject metaDb, ActionKey action, ActionMetadata actionMetadata, TextWriter statusStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.InvokeAction(WorkflowObject metabase, TextWriter statusStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionEngine.RunActionQueue()
   at Microsoft.SqlServer.Setup.Chainer.Workflow.Workflow.RunWorkflow(WorkflowObject workflowObject, HandleInternalException exceptionHandler)
   at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClass2_0.<ExecuteActionWithRetryHelper>b__0()
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionWithRetryHelper(WorkflowObject metaDb, ActionKey action, ActionMetadata actionMetadata, TextWriter statusStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.InvokeAction(WorkflowObject metabase, TextWriter statusStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionEngine.RunActionQueue()
   at Microsoft.SqlServer.Setup.Chainer.Workflow.Workflow.RunWorkflow(WorkflowObject workflowObject, HandleInternalException exceptionHandler)
   at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClass2_0.<ExecuteActionWithRetryHelper>b__0()
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionWithRetryHelper(WorkflowObject metaDb, ActionKey action, ActionMetadata actionMetadata, TextWriter statusStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.InvokeAction(WorkflowObject metabase, TextWriter statusStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionEngine.RunActionQueue()
   at Microsoft.SqlServer.Setup.Chainer.Workflow.Workflow.RunWorkflow(WorkflowObject workflowObject, HandleInternalException exceptionHandler)
   at Microsoft.SqlServer.Chainer.Setup.Setup.RunRequestedWorkflow()
   at Microsoft.SqlServer.Chainer.Setup.Setup.Run(String[] args)
   at Microsoft.SqlServer.Chainer.Setup.Setup.Start(String[] args)
   at Microsoft.SqlServer.Chainer.Setup.Setup.Main()
Error: Action "Install_sqlncli_Cpu64_Action" failed during execution.
Completed Action: Install_sqlncli_Cpu64_Action, returned False

…Trimmed lines…

MSI (s) (5C:3C) [06:42:01:134]: Note: 1: 1729
MSI (s) (5C:3C) [06:42:01:134]: Product: Microsoft SQL Server 2012 Native Client — Configuration failed.

MSI (s) (5C:3C) [06:42:01:134]: Windows Installer reconfigured the product. Product Name: Microsoft SQL Server 2012 Native Client. Product Version: 11.4.7462.6. Product Language: 1033. Manufacturer: Microsoft Corporation. Reconfiguration success or error status: 1602.

From here we can see that issue is during installation of SQLNCLI (SQL Server Native Client) and error code is 1602. The log file associate is sqlncli_Cpu64_1

MSI (s) (5C:3C) [06:42:01:009]: Note: 1: 2203 2: E:\sqlncli.msi 3: -2147287038
MSI (s) (5C:3C) [06:42:01:009]: Source is incorrect. Unable to open or validate MSI package E:\sqlncli.msi.
MSI (s) (5C:3C) [06:42:01:009]: Note: 1: 2203 2: E:\sqlncli.msi 3: -2147287038
MSI (s) (5C:3C) [06:42:01:009]: Source is incorrect. Unable to open or validate MSI package E:\sqlncli.msi.
Please insert the disk:
MSI (s) (5C:3C) [06:42:01:025]: Note: 1: 2265 2: 3: -2147287035
MSI (s) (5C:3C) [06:42:01:025]: User policy value ‘DisableRollback’ is 0
MSI (s) (5C:3C) [06:42:01:025]: Machine policy value ‘DisableRollback’ is 0
Action ended 6:42:01: InstallFinalize. Return value 2.
MSI (s) (5C:3C) [06:42:01:025]: Note: 1: 2318 2:

There we can see an interesting error : “Please insert the disk”.

If we check control panel, I already have SQL Server Native Client and mind you, this was installed when I installed SSMS 18.1.

Solution:

By now you would have understood the reason of the blog. If you are seeing messages in various logs, which are shown above, then you can conclude that you are hitting the issue due to preinstalled SSMS. Here is the screenshot from the release notes.

https://docs.microsoft.com/en-us/sql/sql-server/sql-server-version-15-release-notes

Here are my additional notes.

We have seen this issue earlier when we have SSMS 18.3 already installed and SQL 2019 setup is invoked from ISO mounted as drive. It was combination of few situations.

SSMS 18.3 already installed AND
SQL Server 2019 Setup was running from a mounted ISO.

We have below workarounds:

On existing machines, where SSMS 18.3 or lower is already installed.

Uninstall SQL Server 2012 Native Client which got installed with SSMS. Then install SQL Server 2019. OR
Instead of running setup from mounted ISO, copy the content of media to a local drive or a network share and start setup.exe from those locations.

On new machines, where SSMS is NOT installed.

First install SQL Server 2019 and then install SSMS 18.3 OR
Install version of SSMS greater than 18.3.1 and then install SQL Server 2019

I hope this blog would help someone find solution without contacting Microsoft Product Support team. Please comment and let me know.

Cheers,

Balmukund Lakhani

Twitter @blakhani

Posted in Database Engine Services, Installation, SQL Server 2019 | Tagged: Installation Error, SQL Server 2019 | Leave a Comment »

Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Blog Stats

Blogroll

Select GETDATE()

Subscribe

Follow Blog via Email

Date < getdate()

Select location from world

Meta