Devart Forums

Posted: **Sun 20 Nov 2016 19:05**

Hi,

My system is Windows 10 64Bit, MS-SQL Server 2012 Express 64Bit.
I sometimes get error at the time of first connection to my db server. Moreover, some of the customers also having exact same problem. One of them using Windows 10 64Bit, MS-SQL Server 2014 Express 64bit.

I could not find a pattern to figure source of the problem. Google searches were not helpful.

My application use below code to connect to db:

Code: Select all

procedure ReadDatabaseSettingsFromIni(const DB: TUniConnection; const IniSectionName, BaseDirectory: string; AutoOpen: Boolean = True);
var
  Ini: TIniFile;
  TempString: string;
begin
  if not DirectoryExists(IncludeTrailingPathDelimiter(BaseDirectory)) then Exit();

  Ini := TIniFile.Create(IncludeTrailingPathDelimiter(BaseDirectory) + ININAME);
  try
    DB.Close();
    DB.ProviderName := 'SQL Server';
    DB.Server       := Ini.ReadString(IniSectionName, 'Server', EmptyStr);
    DB.Port         := Ini.ReadInteger(IniSectionName, 'Port', 0);
    TempString      := Ini.ReadString(IniSectionName, 'User', EmptyStr);
    if TempString = EmptyStr then Exit();
    try
      TempString := DecryptItAES(TempString, string(KeyDB));
    except
      Exit();  // Someone messed with encrypted information
    end;
    DB.Username     := TempString;

    TempString      := Ini.ReadString(IniSectionName, 'Pass', EmptyStr);
    if TempString = EmptyStr then Exit();
    try
      TempString := DecryptItAES(TempString, string(KeyDB));
    except
      Exit();  // Someone messed with encrypted information
    end;
    DB.Password     := TempString;

    DB.Database     := Ini.ReadString(IniSectionName, 'Database', EmptyStr);
  finally
    Ini.Free();
  end;

  DB.SpecificOptions.Values['Provider'] := 'prDirect';
  DB.LoginPrompt := False;

  if AutoOpen then
  begin
    try
      DB.Open();
    except
      on E:EUniError do
      begin
        TempString := 'Error connecting to database.' + sLineBreak +
                      'Server: ' + DB.Server + ':' + DB.Port.ToString + sLineBreak +
                      'Database: ' + DB.Database + sLineBreak +
                      'Error: ' + FormatFloat('(0) ', E.ErrorCode) + E.Message;

        Log(TempString, BaseDirectory);
        TErrorBox.Show(TempString);
      end;
    end;
  end;
end;

My log is something like below:

Code: Select all

2016-11-19 22:05:25.002 Error connecting to database. Server: localhost:0 Database: sarel Error: (0) Socket error on reading
2016-11-20 12:28:37.546 Error connecting to database. Server: localhost:0 Database: sarel Error: (0) Socket error on reading
2016-11-20 17:09:57.747 Error connecting to database. Server: localhost:0 Database: sarel Error: (0) Socket error on reading
2016-11-20 21:53:38.022 Error connecting to database. Server: localhost:0 Database: sarel Error: (0) Socket error on reading

I appreciate any help to figure what I am doing wrong here.

Thanks.

Posted: **Tue 22 Nov 2016 21:58**

I would like to provide an update that version 6.4.16 still gives me above mentioned error exactly as it was explained.

Posted: **Wed 23 Nov 2016 14:36**

Please make sure that hosts/ports you use when connecting to SQL Server are correct and are not blocked by antivirus, firewall, security policy, etc. Also to solve the issue, you can get some advice from the article at MSDN: https://blogs.msdn.microsoft.com/sql_pr ... ty-issues/

Posted: **Sat 26 Nov 2016 16:27**

Thank for providing information. Please find below additional information.

- I confirm that Host/port information is correct and always same. They are read from an encrypt text file.
- I can get a connection to my second attempt. It is very rare that I get same error for my second attempt right after I receive error. Time between two attempts is less than 5 seconds mostly.
- There is no special security policy I implemented in my system as it is a development system. I cannot comment for my customer computers though.

I also checked the link provided. It has main list of possible cause for this problem:
1) Network issue
I am using localhost. This should not be the problem I believe.

2) SQL Server configuration issue.
I can get connection established for my second trials. This should not be the problem.

3) Firewall issue
There is only Windows firewall in my system. I especially added allow rules for my application and will observe the situation

4) Client driver issue
I do use direct mode to get connected to SQL Server. Complete connection code is provided above.

5) Application configuration issue
This may not be a problem since I am using same connection file and it works for the second attempt in a few seconds after I get error message.

6) Authentication and logon issue.
This may not be a problem as second attempt in a few seconds gets me connected. SQL Server username and password is saved in a file. User is not manually entering these information.

You may need additional information, please advise.

Posted: **Sun 27 Nov 2016 01:51**

ertank wrote:Thank for providing information. Please find below additional information.
- I can get a connection to my second attempt.

Reading you post got me looking into SQL Server LocalDB again and with a few code changes that was implemented.

While reading the documents on how to build a connection string I saw the warning that these "LocalDB" versions of SQL Server Express take a bit of time to load.
No issues on my system but I did see 'SQL Server.ConnectionTimeout', might be worth a try to increase that.

Posted: **Sun 27 Nov 2016 09:26**

Dear FredS,

I do not have any LocalDB installed. It is just SQL Server 2012 Express Edition 64bit. Does your info this still apply to express editions?

I am yet to check SQL server log files at the log times. I may find something in there, I will share it here.

Thanks.

Posted: **Wed 30 Nov 2016 13:00**

Please specify whether this issue is reproduced if standard tools are used to connect to SQL Server, for example SQL Server Management Studio.

Whether the issue is reproduced if SQL Native Client provider is used in the user application to connect to SQL Server - for this set the 'prNativeClient' value to the TUniConnection.SpecificOptions.Values['SQL Server.Provider'] property; for standard OLEDB provider - the 'prSQL' value.

If the mentioned issue is stably reproduced, do you have a possibility to provide us a remote access to investigate it?

Posted: **Tue 06 Dec 2016 21:07**

Hello,

I confirm that I never get this error so far on SQL Server Management Studio.
I will try to change TUniConnection.Provider to prNativeClient, observe and provide a feedback here.
Then I will try to change TUniConnection.Provider to prSQL, observe and provide a feedback here.

In case problem continues, we may try to setup a TeamViewer session.

Thanks.

Posted: **Fri 09 Dec 2016 11:08**

We will be waiting for the results.

Posted: **Fri 23 Dec 2016 17:44**

Hello,

I have changed my provider for MS-SQL server connection on 2016-12-09, I confirm that I did not get any socket error since then. Below is my complete log file which is not too long

Code: Select all

2016-10-23 12:46:36.655 Error connecting to database. Server: localhost:0 Database: erp0 Error: (0) Socket error on reading
2016-12-08 22:46:47.941 Error connecting to database. Server: localhost:0 Database: erp0 Error: (4060) Cannot open database "erp0" requested by the login. The login failed.
2016-12-08 22:46:50.610 Error connecting to database. Server: localhost:0 Database: erp1 Error: (4060) Cannot open database "erp1" requested by the login. The login failed.
2016-12-08 22:46:50.689 Error connecting to database. Server: localhost:0 Database: erp2 Error: (4060) Cannot open database "erp2" requested by the login. The login failed.
2016-12-08 22:46:52.316 Error connecting to database. Server: localhost:0 Database: erp0 Error: (4060) Cannot open database "erp0" requested by the login. The login failed.
2016-12-08 22:46:56.124 Error connecting to database. Server: localhost:0 Database: erp1 Error: (4060) Cannot open database "erp1" requested by the login. The login failed.
2016-12-08 22:46:56.203 Error connecting to database. Server: localhost:0 Database: erp2 Error: (4060) Cannot open database "erp2" requested by the login. The login failed.
2016-12-09 13:10:36.191 (2627) Violation of PRIMARY KEY constraint 'PK__stkharde__8FBBA04C166F2255'. Cannot insert duplicate key in object 'dbo.stkhardet'. The duplicate key value is ({866B1498-3AAF-4CB8-9A68-93D2986CC52C}).
2016-12-09 13:19:27.321 (257) Implicit conversion from data type sql_variant to nvarchar is not allowed. Use the CONVERT function to run this query.
2016-12-09 13:30:45.977 (257) Implicit conversion from data type sql_variant to nvarchar is not allowed. Use the CONVERT function to run this query.
2016-12-09 13:31:11.078 (257) Implicit conversion from data type sql_variant to nvarchar is not allowed. Use the CONVERT function to run this query.
2016-12-09 13:37:32.862 (257) Implicit conversion from data type sql_variant to nvarchar is not allowed. Use the CONVERT function to run this query.
2016-12-09 13:38:47.035 (257) Implicit conversion from data type sql_variant to nvarchar is not allowed. Use the CONVERT function to run this query.
2016-12-09 13:40:40.336 (2627) Violation of PRIMARY KEY constraint 'PK_stkhar'. Cannot insert duplicate key in object 'dbo.stkhar'. The duplicate key value is ({DB1B8D76-C30C-4A9B-A7C3-40C299ECF023}).
2016-12-09 22:52:07.659 Error connecting to database. Server: localhost:0 Database: erp0 Error: (0) Connection has been gracefully closed by server
2016-12-09 23:01:01.000 Change to "prNativeClient" conection type
2016-12-14 15:47:16.641 (2627) The statement has been terminated. Violation of PRIMARY KEY constraint 'PK_stkhar'. Cannot insert duplicate key in object 'dbo.stkhar'. The duplicate key value is ({EB2894CD-DD62-43B6-AD6B-6CCF87702E77}).
2016-12-17 13:17:02.239 frmUretimEmriGiris.spdEntegrasyonClick(207) Invalid column name 'durum'.
2016-12-17 20:32:15.804 frmUretimFisi.spdEntegrasyonClickThe statement has been terminated. Violation of PRIMARY KEY constraint 'PK_stkhar'. Cannot insert duplicate key in object 'dbo.stkhar'. The duplicate key value is ({BBC6C293-EA70-413E-A42A-91976BA96E0F}).

Line indicating change of connection type is manual input of mine to know time I have made the change. As you can see no socket error since then. I used to get this type of errors several times in a week.

Should I revert back to prDirect provider and test again? Or, if this information is fine and still interested, we can plan a TeamViewer session. I can be reached from my forum e-mail.

Just a side note: I am using SnapShot isolation level in this project and prDirect cannot handle it properly at the moment. Reverting may not be so easy for me.

Thanks.

Posted: **Tue 27 Dec 2016 14:28**

UniDAC supports setting TRANSACTION ISOLATION LEVEL to SNAPSHOT for both the 'prDirect' and 'prNativeClient' providers. Please make sure that when testing your application the SNAPSHOT value is set to the active transaction.

To find out ISOLATION LEVEL you can call in the current transaction the TUniQuery.Open method for the following SQL query:

Code: Select all

-- DBCC USEROPTIONS;

DECLARE   @UserOptions TABLE(SetOption varchar(100), Value varchar(100))
DECLARE   @IsolationLevel varchar(100)

INSERT    @UserOptions
EXEC('DBCC USEROPTIONS WITH NO_INFOMSGS')

SELECT    @IsolationLevel = Value
FROM      @UserOptions
WHERE     SetOption = 'isolation level'

SELECT    @IsolationLevel as IsolationLevel

After successful query execution the IsolationLevel field will contain the ISOLATION LEVEL value for the current transaction. More details about DBCC USEROPTIONS at MSDN: https://msdn.microsoft.com/en-us/library/ms180065.aspx .

Devart Forums

MS-SQL Server - Socket error on reading

MS-SQL Server - Socket error on reading

Re: MS-SQL Server - Socket error on reading

Re: MS-SQL Server - Socket error on reading

Re: MS-SQL Server - Socket error on reading

Re: MS-SQL Server - Socket error on reading

Re: MS-SQL Server - Socket error on reading

Re: MS-SQL Server - Socket error on reading

Re: MS-SQL Server - Socket error on reading

Re: MS-SQL Server - Socket error on reading

Re: MS-SQL Server - Socket error on reading

Re: MS-SQL Server - Socket error on reading