Problem connecting to SQL server on Win server

[Goran]

Hi,
we have 2 applications that are working also on Windows 2003/2008 server. With dbexpsda30.dll version 3.20.10 the application stops working when Windows server is continuously running more than a week. If Windows server machine is rebooted, then the applications are working normally again. We have replaced the dll with newer version 4.20 and it seems it stopped working again after a bit longer period of time (2 weeks).

One application starts every day on a time schedule for database and other files backup and the second one works all the time. Both applications are written in Delphi 2006 and SQL Server installed is 2005 Express SP3. Any ideas?

[Dimon]

Try to use the SQL Native Client provider. For this you should set the TCRSQLConnection.VendorLib property to 'sqlncli'.
If this does not solve the problem, please describe it in more details. Or try to compose a small sample to demonstrate the problem and send it to dmitryg*devart*com.

[Goran]

Thank You for the information. I shall try this solution as soon as the problem will appear on our server. On the client server where the problem appared they have allready restarted the server...

Regarding the example. There is nothing special, the exception is raised as soon as the code in the application tries to connect to SQL server and the error shown is ERangeCheck error. Code for the establishing the connection is done like this:

Code: Select all

function TdmSkupno.ConnectServer(AProgramStart : boolean) : Boolean;

  function ConnectSQL(var AMsg : String) : integer;
  begin
    AMsg := '';
    Result := -1;
    with sqlConn do
    try
      Open;
      if Connected then
      begin
        Result := 0;
        AMsg := '';
      end;
    except
      on E: Exception do
      begin
        AMsg := E.Message;
        Logger(LogFull, 'Error, dmSkupno, ConnectServer, E: ' + E.ClassName+ ', msg: ' + E.Message);
        if Pos('could not open a connection', LowerCase(AMsg)) > 0 then
          Result := -10;
        if Pos('delay in opening server connection', LowerCase(AMsg)) >0  then
          Result := -10;
      end;
    end;
  end;

var
  iRez : Integer;
  sMsg : String;
begin
  with sqlConn do
  begin
    Close;
    ConnectionName := 'SQLServerConnection';
    DriverName := 'SQLServer';
    GetDriverFunc := 'getSQLDriverSQLServer';
    LibraryName := 'dbexpsda30.dll';
    VendorLib := 'sqloledb.dll';

    Params.Clear;
    Params.Add('BlobSize=-1');
    Params.Add('Hostname=' + FServer);
    Params.Add('DataBase=' + FDatabase);

    Params.Add('LongStrings=True');
    Params.Add('EnableBCD=False');
    Params.Add('FetchAll=True');
    LoginPrompt := True;
    KeepConnection := True;
  end;

  sMsg := '';
  iRez := ConnectSQL(sMsg);
  if iRez = -10 then
    iRez := ConnectSQL(sMsg);
  Result := iRez = 0;
  if (not Result) and AProgramStart then
    Application.MessageBox(PChar('The program can not start. Error:' + ^m + sMsg), 
	                       PChar('Error'), 
						   MB_ICONERROR+MB_OK)
 End;

With best regards,
Goran

[Dimon]

I could not reproduce the problem.
Please check if you can to connect to MS SQL server 2005 using MS SQL Server Management Studio.

[Goran]

That's what is the most interesting. MS Management Studio works as well as ExpressMaint Utility found on SQLdbatips.com but our software can't until server is restarted... No problem connecting from client computers on this computer tough but they are shutting down the computers every evening. The problem is only on server itself where the MS Server 2005 (express) and application are running.

[Dimon]

If I understand you right, when your MS Management Studio and client application with dbExpres driver are placed on one computer, in some cases MS Management Studio can connect to MS Server, but client application raises error when connecting. Is it right?

[Goran]

Hi Dimon,
sorry for late response. I was trying to come into the situation that error will occur so I can respond with some accurate data but unfortunately I still can't reproduce the problem since our Windows Server 2008 has been restarted in the mean time due to the updates.

If I understand you right, when your MS Management Studio and client application with dbExpres driver are placed on one computer, in some cases MS Management Studio can connect to MS Server, but client application raises error when connecting. Is it right?

Not exactly. The problem appeared only at the machines with Windows 2003 and 2008 server editions and if only the software is placed on this computer together with the database. I can connect to the database with MS Management Studio to the database at any time without any problems. The problem is when uptime for the Windows Server is more than 1-2 weeks without any restart in the mean time, then suddenly the application made in Delphi (2006) can not connect to the database. After Windows Server is restarted the problem is solved. This does not happen on the client computers which do not have database installed and are connecting to the server. Maybe there the problem would appear as well but they are daily restarted.

I think the idea with connecting to the native client maybe working but I have to try it. As soon as the suitable conditions will appear, only then I can really start checking.

Thank You for the help for now. I will post any new findings as soon as possible.

With best regards,
Goran