Input dialect was ignored - OLEDB - Sql Server

[RainerE]

Hello!

I have a TD42 application which connects to MS Sql Server 2005 via OLEDB.
If I run the application on a (german) Windows XP computer, everything works fine.
If I run the application on a (german) Windows 7 64 bit computer, I get the Error 'Sql Server database acces failed: 3789 (265933) - Input dialect was ignored and text was returned in different dialect.' This is error 0x00040ECDL (=265933 in decimal) DB_S_DIALECTIGNORED.

On this computer the MS Sql Server 2005 client and the MS Sql Server 2008 R2 client is installed (because we have both, a Sql Server 2005 and a Sql Server 2008).

I connect in the following way:

Code: Select all

Set sConnectionString = 'SQLOLEDB;Data Source=' || sSqlServer || ';Initial Catalog=' || sSqlServerDatabase || ';User ID=' || sSqlServerUsername || ';Password=' || sSqlServerPassword || ';Persist Security Info=True;'
Call SqlCreateSession( hSession, sConnectionString )
Call SqlSetSessionParameter( hSession, DBP_AUTOCOMMIT, FALSE, '' )
Set SqlResultSet = TRUE
Call SqlCreateStatement( hSession, hSqlServer )

The error occures, while a stored procedure is called as follows:

Code: Select all

Call SqlSetResultSet( hSqlServer, FALSE )
Set sStatement = 'exec dbo.Insert_CashEntry :nSqlServerKeyTranslationMode, :cCashEntry.AccNo, ...'
Call SqlPrepareSP( hSqlServer, sStatement, '' )
! Next line brings up the error
Call SqlExecute( hSqlServer )

Note: In real live the code is more complex (has error handling) and is split over several classes.

Any hints?

Regards,
Rainer

[Jeff Luther]

Rainer -- the simple 'answer' is that v4.2 was never certified for 64-bit nor for those versions of MS SS. Here's the compat. chart showing what Gupta originally certified TD v4.2 for: http://www.guptatechnologies.com/Produc ... atrix.aspx

The solution is find what does work with v4.2 and live with that, or upgrade to TD v5.2 or v6 where the OS + MS SS versions you mention are certified to run.

[RainerE]

Now we have the problem on Windows XP SP3 with TD 4.2 and OLEDB against MS Sql Server 2005.
Does anyone has a solution for this?

Regards,
Rainer

P.S.: Upgrading to a newer TD version is not possible for us - please don't tell us so and please don't show us the compatibility matrix. I'm looking for a workaround!

[Jeff Luther]

Upgrading... please don't tell us so and please don't show us the compatibility matrix

OK, I won't. (but I really wanted to )

The error occures, while a stored procedure is called

Well, what testing have you done to see if you can work around the problem?

Can you narrow down which parameter you pass in the SP that might be the problem? Error msg. sounds like it's a String parameter that is causing the error (?)

How about if you test that INSERT as a regular "INSERT INTO..." test with SqlPrepareAndExecute?. Does the INSERT work then if you do not use the SP?

Tell you what: I will ask our router developer about this when I have a little more info. from you, based on my questions above.

[RainerE]

The error occures, while a stored procedure is called

Well, what testing have you done to see if you can work around the problem?

The problem occures only if IE8 is installed. With IE6 installed, the problem does not occure.
If you start the PC, start the Gupta TD program and call SqlPrepareSP() and SqlExecute(), everything works fine in the TD program.
If you start a MS C# .NET program and call this procedure, everything works fine in the .NET program.
If you now start the Gupta TD programm and call SqlPrepareSP() and SqlExecute(), the error occures.
If you now log off from windows and log on again, everything works fine again.
I've set the MS Sql Server database to Sql Server 2000 compatibility, but this does not help.
I've tried to ignore the error (return true on When SqlError), but this does not help.

Can you narrow down which parameter you pass in the SP that might be the problem? Error msg. sounds like it's a String parameter that is causing the error (?)

We've searched the Internet a lot and figured out, that the input dialect is a (GUID) parameter of the OLEDB provider. So something in the implementation of TD 4.2 is the problem. And right, this is because TD 4.2 was not developped for MS Sql Server 2005.
Seems that we have to live with the problem or implement a workaround (do not write to Sql Server any more) or upgrade to TD 5.2.

How about if you test that INSERT as a regular "INSERT INTO..." test with SqlPrepareAndExecute?. Does the INSERT work then if you do not use the SP?

Sorry, this ist not possible because the stored procedure does a lot of translations. Therefor many data is selected, which is only available in the MS Sql Server database.

Tell you what: I will ask our router developer about this when I have a little more info. from you, based on my questions above.

Thanks a lot.

Regards,
Rainer

[Jeff Luther]

And right, this is because TD 4.2 was not developed for MS Sql Server 2005.

'Certified for' is what QA would have done back in the Gupta days if 2005 had been around. And 4.2 came out long before it could be certified for Win7 64-bit.

Seems that we have to (1) live with the problem or (2) implement a workaround (do not write to Sql Server any more) or (3)upgrade to TD 5.2.

Yes, I would 2 or 3 are your best solutions. 1 works if you can stay away from all that "If you do <this>, then do <that>, and finally the <other>, then the error occurs.

Well, there really isn't anything I can ask our router developer about, Rainer. You have narrowed down the problem, it seems. Your If, Else If list is long and what you found narrows down to something TD v4.2 was never designed nor certified to handle: Win7 764-bt & MS SS 2005, plus your other constraints like the MS C# app, and calling a SP. If you stay with v4.2 it sounds like you will need to stay away from this configuration.

P.S. I just see "OLEDB" again and I don't see that I asked: What if you connect via ODBC? Does that work?

[RainerE]

Hi Jeff,

And right, this is because TD 4.2 was not developed for MS Sql Server 2005.

'Certified for' is what QA would have done back in the Gupta days if 2005 had been around. And 4.2 came out long before it could be certified for Win7 64-bit.

We have the problem on Windows XP 32 bit (against MS Sql Server 2005).
We have the problem since IE 8 has been installed.
Further information:
- Select, insert, update are working properly
- SqlPrepareSP() + SqlExecute() sometimes (often) has this problem.

Seems that we have to (1) live with the problem or (2) implement a workaround (do not write to Sql Server any more) or (3)upgrade to TD 5.2.

Yes, I would 2 or 3 are your best solutions. 1 works if you can stay away from all that "If you do <this>, then do <that>, and finally the <other>, then the error occurs.

Our workaround will be to replace the stored procedure by a simple insert.
(3) would not bee an option because of the very very high effort needed to migrate. We are using many many windows api functions and ised quick pdf. Not all of this can be migrated to unicode. And as known, TD 5.2/6.0 applications running slower than TD 4.x applications but we do not have the option to deliver slower applications.

P.S. I just see "OLEDB" again and I don't see that I asked: What if you connect via ODBC? Does that work?

Don't know. Years ago I compared ODBC against OLEDB and figured out, that ODBC is not an option for us.

Regards,
Rainer

[RainerE]

For whom it may be interesting:

Ok, my workaround now is:
Instead of SqlPrepareSP(hSqlServer, 'exec dbo.InsertCashEntry parameter1, parameter2, ...', '') and SqlExecute(hSqlServer)
I call now SqlPrepareAndExecute(hSqlServer, 'exec dbo.InsertCashEntry parameter1, parameter2, ...')
The only disadvantage is, that with the first method I could pass bind variables to SqlServer (as parameters)
and with the second method I cannot. I submit a string containing constants instead.
The disadvantage ist, that MS Sql Server cannot cache the execution plan in this case (the statement has to be prepared each time).

It seems that SqlPrepareSP() and SqlExecute() do not work with Internet Explorer versions > 6 (XP and IE8 in my case, Win7 in another case).

Regards,
Rainer

[PVA]

We just today meat this error.
One of our TD 4.2 application connects to MSSQL 2008 via OLEDB. Application runs on SQL SQLServer computer and works with local SQL database.
On all the MSSQL servers it works fine (all are MSSQL 2008, x64), but on one server there is the problem from some time. We know nothing about changes on this computer unfortunately.
We have got this error "Input dialect was ignored and command was processed using default dialect" when calling stored procedure

Code: Select all

Call SqlPrepareSP( hSqlServer, '{call PROCEDURE_NAME( :sParameter)}', '' )
Call SqlExecute( hSqlServer )

Some details:

1. SQL Server Profiler doesn't show the row with calling of this stored procedure even if I selected "RPC Starting" event.
From Profiler (the same SPID):

Code: Select all

exec [DBNAME]..sp_procedure_params_rowset N'PROCEDURE_NAME',1,NULL,NULL
go
declare @p1 int
set @p1=-1
exec sp_prepexec @p1 output,NULL,N'ROLLBACK'
select @p1
go

So, OLEDB receives parameters of stored procedure, then does someting and then gets an error, and after it application runs rollback command.

2. Via ODBC - no errors for all the MSSQLServer versions for all Windows versions (XP, 7, 8, Server 2008) for all Windows platforms (32, 64).
But redeveloping of this application now to use ODBC is the big problem.

Pavel