Pages: 1

#1 2014-03-20 12:11:24

tech
Member
Registered: 2014-01-13
Posts: 110

MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

Hi,

If I launch my app on the server it makes connection fine, but if I launch it on a client machine it doesn't.
What I don't understand is that I've another app making connection fine via ADO component and OLEDB !!

What I'm missing here ?

N.B: The client and the server are on the same domaine.

I got an Unregistred Class exception.

Last edited by tech (2014-03-20 12:41:53)

Offline

#2 2014-03-20 14:21:43

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
Website

Re: MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

Sounds like a missing oledb provider.

Online

#3 2014-03-20 14:31:10

tech
Member
Registered: 2014-01-13
Posts: 110

Re: MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

How can I know ?
I've tested this http://blogs.msdn.com/b/support_sql_fra … -bits.aspx
and all is OK.

Offline

#4 2014-03-20 15:27:24

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
Website

Re: MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

What are the WIndows versions?
What are the OleDB providers installed for MS SQL?
What is the class which is not registered - meaning not installed?

And why do you need drivers on the client?
Why not just switch to a n-Tier / SOA architecture, and let the SQL be confined to the server side?

Online

#5 2014-03-20 15:39:47

tech
Member
Registered: 2014-01-13
Posts: 110

Re: MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

The server is windows 2008 and the client is windows7.
The version of oleDb dll is 6.1.96.
The class name that I got is EOleSysError.

For the n-Tier architecture I will switch to it later, it is juste because my app is not yet OK and I must install it for testing.

Here's my code for testing connection :

procedure TOptionForm.btnmsSQLClick(Sender: TObject);
var PropsMS : TOleDBMSSQL2008ConnectionProperties;
    Conn: TSQLDBConnection;
begin
  try
    try
      PropsMS := TOleDBMSSQL2008ConnectionProperties.Create(edtmsServer.Text, edtmsBdd.Text, edtmsUser.Text, edtmsPwd.Text);
      Conn := PropsMS.NewConnection;
      Conn.Connect;
      ShowMessage('Connexion OK');
    Except
      on E : Exception do
      ShowMessage('Connexion impossible au '+ edtmsServer.Text +'\'+ edtmsBdd.Text + #10#13+
                  'Exception class = '+E.ClassName + #10#13+
                  'Exception message = '+E.Message);
    end;
  finally
    Conn.free;
    PropsMS.Free;
  end;
end;

Offline

#6 2014-03-20 16:38:36

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
Website

Re: MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

EOleSysError is the exception, not the COM class not found...
What is the expected COM class?
What is the full message?

You can enable SynCommons logging, then check the stack trace of the exception to retrieve the faulty Ole/COM command.

BTW are you sure you are using the latest "unstable" version of our units?

Online

#7 2014-03-20 17:39:37

tech
Member
Registered: 2014-01-13
Posts: 110

Re: MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

Yes I've updated to the latest unstable version but the problem persists.

Here's the log file :

C:\Projets\bala\Comm2.exe 1.0.0.0 (2014-03-20 17:26:14)
Host=OMNICLIENT User=bala CPU=1*0-15-1025 OS=4.2=5.2.3790 Wow64=0 Freq=3579545
Environment variables=ALLUSERSPROFILE=C:\Documents and Settings\All Users	APPDATA=C:\Documents and Settings\bala\Application Data	CLIENTNAME=ADELON	ClusterLog=C:\WINDOWS\Cluster\cluster.log	CommonProgramFiles=C:\Program Files\Fichiers communs	COMPUTERNAME=OMNICLIENT	ComSpec=C:\WINDOWS\system32\cmd.exe	FP_NO_HOST_CHECK=NO	HOMEDRIVE=C:	HOMEPATH=\Documents and Settings\bala	LOGONSERVER=\\OMNICLIENT	MOZ_PLUGIN_PATH=C:\Program Files\Foxit Software\Foxit Reader\plugins\	NUMBER_OF_PROCESSORS=1	OS=Windows_NT	Path=C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem	PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH	PROCESSOR_ARCHITECTURE=x86	PROCESSOR_IDENTIFIER=x86 Family 15 Model 4 Stepping 1, GenuineIntel	PROCESSOR_LEVEL=15	PROCESSOR_REVISION=0401	ProgramFiles=C:\Program Files	SESSIONNAME=RDP-Tcp#1	SystemDrive=C:	SystemRoot=C:\WINDOWS	TEMP=C:\DOCUME~1\bala\LOCALS~1\Temp\1	TMP=C:\DOCUME~1\bala\LOCALS~1\Temp\1	USERDOMAIN=OMNICLIENT	USERNAME=bala	USERPROFILE=C:\Documents and Settings\bala	windir=C:\WINDOWS
TSQLLog 1.18 2014-03-20T17:29:17

20140320 17291716  +    004DFE6C 
20140320 17291716  +    	TSQLDatabase(01007360).004DF67E 
20140320 17291716 SQL   		PRAGMA cache_size=10000
20140320 17291716  -    	00.006.187
20140320 17291716  -    00.007.715
20140320 17291716  +    TSQLDatabase(01007360).004DF778 
20140320 17291716 SQL   	SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';
20140320 17291716  -    00.000.164
20140320 17291716 debug TSQLDatabase(01007360) TableNames=["Params"]
20140320 17291716 cache TSQLDatabase(01007360) not in cache
20140320 17291716 SQL   TSQLRestServerDB(00F0B5A0) SELECT ID,Societe,CG_RegD,CG_RegC,myServer,myBDD,myUser,myPwd,myLibmySQL,msServer,msBDD,msUser,msPwd FROM Params; is no prepared statement
20140320 17291716 res   TSQLDatabase(01007360) {"fieldCount":13,"values":["ID","Societe","CG_RegD","CG_RegC","myServer","myBDD","myUser","myPwd","myLibmySQL","msServer","msBDD","msUser","msPwd"],"rowCount":0} 
20140320 17300157  +    TOleDBConnection(00FE4038).0064A576 
20140320 17300157  -    00.000.006
20140320 17300157  +    TOleDBConnection(00FE4038).0064A2E4 
20140320 17300207 EXC   	EOleSysError 80040154 ("Classe non enregistrée") at 005659E9  stack trace API 004AF330 004049F0 
20140320 17300207 ERROR 	"EOleSysError(01C49B30)":"Classe non enregistrée" stack trace API 004B0E7E 004B0266 006969F8 0046C656 0046F408 0046F540 0046F408 0042937A 77F4B6E3 77F4B874 77F4C2D3 77F4C337 77E489F7 77E48AD9 77E4ADDE 77F4B6E3 77F4B874 77F4BFCE 77F60463 0046F4EC 
20140320 17300207 EXC   	EOleSysError 80040154 ("Classe non enregistrée") at 005659E9  stack trace API 004AF330 004049F0

Offline

#8 2014-03-20 23:38:15

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
Website

Re: MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

Please add the .map file to the .exe to have line numbers in the stack trace.

Online

#9 2014-03-21 10:00:45

tech
Member
Registered: 2014-01-13
Posts: 110

Re: MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

This is the log file after adding .map file to the exe :

C:\Projets\bala\Comm2.exe 1.0.0.0 (2014-03-21 09:42:14)
Host=OMNICLIENT User=bala CPU=1*0-15-1025 OS=4.2=5.2.3790 Wow64=0 Freq=3579545
Environment variables=ALLUSERSPROFILE=C:\Documents and Settings\All Users	APPDATA=C:\Documents and Settings\bala\Application Data	CLIENTNAME=ADELON	ClusterLog=C:\WINDOWS\Cluster\cluster.log	CommonProgramFiles=C:\Program Files\Fichiers communs	COMPUTERNAME=OMNICLIENT	ComSpec=C:\WINDOWS\system32\cmd.exe	FP_NO_HOST_CHECK=NO	HOMEDRIVE=C:	HOMEPATH=\Documents and Settings\bala	LOGONSERVER=\\OMNICLIENT	MOZ_PLUGIN_PATH=C:\Program Files\Foxit Software\Foxit Reader\plugins\	NUMBER_OF_PROCESSORS=1	OS=Windows_NT	Path=C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem	PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH	PROCESSOR_ARCHITECTURE=x86	PROCESSOR_IDENTIFIER=x86 Family 15 Model 4 Stepping 1, GenuineIntel	PROCESSOR_LEVEL=15	PROCESSOR_REVISION=0401	ProgramFiles=C:\Program Files	SESSIONNAME=RDP-Tcp#3	SystemDrive=C:	SystemRoot=C:\WINDOWS	TEMP=C:\DOCUME~1\bala\LOCALS~1\Temp\1	TMP=C:\DOCUME~1\bala\LOCALS~1\Temp\1	USERDOMAIN=OMNICLIENT	USERNAME=bala	USERPROFILE=C:\Documents and Settings\bala	windir=C:\WINDOWS
TSQLLog 1.18 2014-03-21T09:51:36

20140321 09513636  +    000DEE6C SynSQLite3.TSQLDatabase.DBOpen (3438) 
20140321 09513636  +    	TSQLDatabase(01007360).000DE67E SynSQLite3.TSQLDatabase.Execute (3077) 
20140321 09513636 SQL   		PRAGMA cache_size=10000
20140321 09513636  -    	00.000.512
20140321 09513636  -    00.000.908
20140321 09513636  +    TSQLDatabase(01007360).000DE778 SynSQLite3.TSQLDatabase.Execute (3210) 
20140321 09513636 SQL   	SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';
20140321 09513636  -    00.000.164
20140321 09513636 debug TSQLDatabase(01007360) TableNames=["Params"]
20140321 09513636 cache TSQLDatabase(01007360) not in cache
20140321 09513636 SQL   TSQLRestServerDB(00F0B5A0) SELECT ID,Societe,CG_RegD,CG_RegC,myServer,myBDD,myUser,myPwd,myLibmySQL,msServer,msBDD,msUser,msPwd FROM Params; is no prepared statement
20140321 09513636 res   TSQLDatabase(01007360) {"fieldCount":13,"values":["ID","Societe","CG_RegD","CG_RegC","myServer","myBDD","myUser","myPwd","myLibmySQL","msServer","msBDD","msUser","msPwd"],"rowCount":0} 
20140321 09515145  +    TOleDBConnection(00FE4108).00249576 SynOleDB.TOleDBConnection.Create (1975) 
20140321 09515145  -    00.000.006
20140321 09515145  +    TOleDBConnection(00FE4108).002492E4 SynOleDB.TOleDBConnection.Connect (1938) 
20140321 09515158 EXC   	EOleSysError 80040154 ("Classe non enregistrée") at 001649E9 ComObj.OleError  stack trace API 000AE330 SynCommons.SynRtlUnwind (32354) 000039F0 System.@HandleOnException 
20140321 09515158 ERROR 	"EOleSysError(01C1C780)":"Classe non enregistrée" stack trace API 002959F8 Options.TOptionForm.btnmsSQLClick (170) 0006B656 Controls.TControl.Click 0006E408 Controls.TWinControl.WndProc 0006E540 Controls.DoControlMsg 0006E408 Controls.TWinControl.WndProc 0002837A Classes.StdWndProc 0006E4EC Controls.TWinControl.DefaultHandler 
20140321 09515158 EXC   	EOleSysError 80040154 ("Classe non enregistrée") at 001649E9 ComObj.OleError  stack trace API 000AE330 SynCommons.SynRtlUnwind (32354) 000039F0 System.@HandleOnException

Offline

#10 2014-03-21 12:43:17

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
Website

Re: MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

Sadly, we still can't get the COM object class name missing!
Could you try debug with the IDE on this computer?
Or try to enable the stack frames for the compiler options?

Online

#11 2014-03-22 11:29:59

tech
Member
Registered: 2014-01-13
Posts: 110

Re: MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

Hi,

I've debug remotly the app on the client machine and the exception occurs here :

procedure TOleDBConnection.Connect;
var DataInitialize : IDataInitialize;
    unknown: IUnknown;
    Log: ISynLog;
begin
  ....
    OleCheck(DataInitialize.GetDataSource(nil,CLSCTX_INPROC_SERVER,
      pointer(OleDBProperties.ConnectionString),
      IID_IDBInitialize,IUnknown(fDBInitialize)));  <======================= 1st Exception
      DataInitialize := nil;
      // open the connection to the DB
      OleDBCheck(fDBInitialize.Initialize);        <======================= 2st Exception
  ....
end;

After that I discovered that the sql native client for mssql 2008 was not installed on the client machine. I've installed the SNCLI10 and I got an other exception, this is the log file :

C:\Projets\bala\Comm2 1.0.0.0 (2014-03-22 11:14:12)
Host=OMNICLIENT User=bala CPU=1*0-15-1025 OS=4.2=5.2.3790 Wow64=0 Freq=3579545
Environment variables=ALLUSERSPROFILE=C:\Documents and Settings\All Users	APPDATA=C:\Documents and Settings\bala\Application Data	CLIENTNAME=ADELON	ClusterLog=C:\WINDOWS\Cluster\cluster.log	CommonProgramFiles=C:\Program Files\Fichiers communs	COMPUTERNAME=OMNICLIENT	ComSpec=C:\WINDOWS\system32\cmd.exe	FP_NO_HOST_CHECK=NO	HOMEDRIVE=C:	HOMEPATH=\Documents and Settings\bala	LOGONSERVER=\\OMNICLIENT	MOZ_PLUGIN_PATH=C:\Program Files\Foxit Software\Foxit Reader\plugins\	NUMBER_OF_PROCESSORS=1	OS=Windows_NT	Path=C:\PROGRA~1\Borland\REMOTE~1\7.0\Bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\Program Files\Microsoft SQL Server\80\Tools\Binn\	PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH	PROCESSOR_ARCHITECTURE=x86	PROCESSOR_IDENTIFIER=x86 Family 15 Model 4 Stepping 1, GenuineIntel	PROCESSOR_LEVEL=15	PROCESSOR_REVISION=0401	ProgramFiles=C:\Program Files	SESSIONNAME=RDP-Tcp#3	SystemDrive=C:	SystemRoot=C:\WINDOWS	TEMP=C:\DOCUME~1\bala\LOCALS~1\Temp\1	TMP=C:\DOCUME~1\bala\LOCALS~1\Temp\1	USERDOMAIN=OMNICLIENT	USERNAME=bala	USERPROFILE=C:\Documents and Settings\bala	windir=C:\WINDOWS
TSQLLog 1.18 2014-03-22T11:22:52

20140322 11225240  +    000DEE6C SynSQLite3.TSQLDatabase.DBOpen (3438) 
20140322 11225240  +    	TSQLDatabase(01007360).000DE67E SynSQLite3.TSQLDatabase.Execute (3077) 
20140322 11225240 SQL   		PRAGMA cache_size=10000
20140322 11225240  -    	00.001.948
20140322 11225240  -    00.003.580
20140322 11225240  +    TSQLDatabase(01007360).000DE778 SynSQLite3.TSQLDatabase.Execute (3210) 
20140322 11225240 SQL   	SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';
20140322 11225240  -    00.000.167
20140322 11225240 debug TSQLDatabase(01007360) TableNames=["Params"]
20140322 11225240 cache TSQLDatabase(01007360) not in cache
20140322 11225240 SQL   TSQLRestServerDB(00F0B5A0) SELECT ID,Societe,CG_RegD,CG_RegC,myServer,myBDD,myUser,myPwd,myLibmySQL,msServer,msBDD,msUser,msPwd FROM Params; is no prepared statement
20140322 11225240 res   TSQLDatabase(01007360) {"fieldCount":13,"values":["ID","Societe","CG_RegD","CG_RegC","myServer","myBDD","myUser","myPwd","myLibmySQL","msServer","msBDD","msUser","msPwd"],"rowCount":0} 
20140322 11230615  +    TOleDBConnection(00FE4108).00249576 SynOleDB.TOleDBConnection.Create (1975) 
20140322 11230615  -    00.000.006
20140322 11230615  +    TOleDBConnection(00FE4108).002492E4 SynOleDB.TOleDBConnection.Connect (1938) 
20140322 11233024 ERROR 	"EOleDBException(01015C70)":"Erreur non spécifiée -   (line 0): Fournisseur de canaux nommés : Impossible d'ouvrir une connexion à SQL Server [1326]. \r\n" stack trace API 00249BC7 SynOleDB.TOleDBConnection.OleDBCheck (2079) 002493C5 SynOleDB.TOleDBConnection.Connect (1954) 002959F8 Options.TOptionForm.btnmsSQLClick (170) 0006B656 Controls.TControl.Click 0006E408 Controls.TWinControl.WndProc 0006E540 Controls.DoControlMsg 0006E408 Controls.TWinControl.WndProc 0002837A Classes.StdWndProc 
20140322 11233024 EXC   	EOleDBException ("Erreur non spécifiée -   (line 0): Fournisseur de canaux nommés : Impossible d'ouvrir une connexion à SQL Server [1326]. \r\n") at 00249AAF SynOleDB.EnhancedTest (2073)  stack trace API 000A6DC8 SynCommons.SynRtlUnwind (32354) 000039F0 System.@HandleOnException 
20140322 11233024 ERROR 	"EOleDBException(01015C70)":"Erreur non spécifiée -   (line 0): Fournisseur de canaux nommés : Impossible d'ouvrir une connexion à SQL Server [1326]. \r\n" stack trace API 002959F8 Options.TOptionForm.btnmsSQLClick (170) 0006B656 Controls.TControl.Click 0006E408 Controls.TWinControl.WndProc 0006E540 Controls.DoControlMsg 0006E408 Controls.TWinControl.WndProc 0002837A Classes.StdWndProc 0006E4EC Controls.TWinControl.DefaultHandler 
20140322 11233024 EXC   	EOleDBException ("Erreur non spécifiée -   (line 0): Fournisseur de canaux nommés : Impossible d'ouvrir une connexion à SQL Server [1326]. \r\n") at 00249AAF SynOleDB.EnhancedTest (2073)  stack trace API 000A6DC8 SynCommons.SynRtlUnwind (32354) 000039F0 System.@HandleOnException

I forgot that mssql server used named pipe, TCP/IP and shared memory

Last edited by tech (2014-03-22 12:02:19)

Offline

#12 2014-03-24 11:20:00

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,571
Website

Re: MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

Try to use TODBCConnectionProperties class. It's more stable from my POW (last time I try OleDB about 1 year ago decide to switch to ODBC). I use  TODBCConnectionProperties in real project w/o problems.

Offline

#13 2014-03-24 21:02:01

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
Website

Re: MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

The SynOleDB unit has been improved a lot and fixed since last year.
smile
And speed is slightly higher than with ODBC when accessing MS SQL, for instance (BATCH insert is about 3x faster, and reading of several rows is 2x faster).
See http://blog.synopse.info/post/2014/03/0 … PostgreSQL

But OleDB is officially deprecated in favor to ODBC, by Microsoft's words.
See http://blog.synopse.info/post/2012/02/2 … oy-ODBC%21

What tech is sadly experimenting here is IMHO a missing OleDB provider.
The error message (in French) does not mean anything (and I'm French!)... Why on earth does Microsoft translate technical terms into Microsoft's marketing English terms, then into no-sense foreign terms?
I did not encounter this yet, so perhaps someone else may help!
sad

Online

#14 2014-03-25 16:05:20

tech
Member
Registered: 2014-01-13
Posts: 110

Re: MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

I switched to ODBC as you said, but I get the following error when I check the existence of a record and I try to create it if it does not exist:

'[HY000] [Microsoft][SQL Server Native Client 10.0]La connexion est occupée avec les résultats d'une autre commande (0)'

This is my code

var jmouv : TSQLmsJMOUV;
begin
  Result := True;
  try
    jmouv := TSQLmsJMOUV.CreateAndFillPrepare(globalClient, 'JO_NUM= ? AND JM_date = ?', [JO_NUM, DateToSQL(JM_date)]);
    jmouv.FillOne;
    if jmouv.JO_Num = '' then
    begin
      jmouv.fJO_Num := JO_NUM;
      jmouv.fJM_Date := JM_date;
      jmouv.fJM_Cloture := 0;
      jmouv.fJM_Impression := 0;
      globalClient.Add(jmouv, True);   <==================== The error 
    end;
  finally
    jmouv.Free;
  end;
end;

Offline

#15 2014-03-25 17:55:49

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
Website

Re: MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

Are you sure you are using the latest 1.18 "unstable" version of mORMot source code?
The easiest is to download http://synopse.info/files/mORMotNightlyBuild.zip

Online

#16 2014-03-26 09:46:54

tech
Member
Registered: 2014-01-13
Posts: 110

Re: MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

I updated the source code and the these are the differences that I found :
http://synopse.info/fossil/info/4a0c549975
http://synopse.info/fossil/vinfo?name=9 … 57dbb37b0f

But the error is still here.

Offline

#17 2014-03-26 11:05:21

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
Website

Re: MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

After some searchs, this is a known restriction of the MS SQL ODBC provider:

This error occurs when there are pending results on a statement handle that is then used to execute another query. This causes a problem when the ODBC data source is a SQL Server (Microsoft or Sybase) because, owing to the architectural design, there can be only one active statement per connection on an SQL Server.
http://support.microsoft.com/kb/119023/en-us

This driver expects the "jmouv" statement results to be fully consumed.
It should be the case, since TSQLRestServerStaticExternal.EngineList() calls  TSQLDBStatement.FetchAllToJSON() which should consume all the records...

What is the class of your "globalClient" instance?
Are you running it on the client side or the server side?

Online

#18 2014-03-26 11:32:22

tech
Member
Registered: 2014-01-13
Posts: 110

Re: MSSql connection problem with TOleDBMSSQL2008ConnectionProperties

The "globalClient" is an instance of a class derived from TSQLHttpClient and  I run the code on the client like on the MainDemo.

Offline

Pages: 1

Board footer

Powered by FluxBB