SQLNCLI10, PB 11.2 and RPC Functions.

[mhampton]

Has anyone else noticed this issue? Using SQLNCLI10 and connecting to
SQL Server 2000 - 2008, PB 11.2 Build 8869. OLE DB connection.

Calling an RPC Function that has a date time as a parameter, the
seconds are always passed as 00. So, if the time portion was 15:33:45
of the datatime, this is this SQL that gets sent to SQL Server:

exec dbo.populate_recordlock N'MHAMP','2010-02-03 15:33:00',33315

In terms of ProviderString settings, I've tried both adding and
removing DataTypeCompatibility=80
In terms of DBParm I've tried adding and removing DateTimeFormat.

When I use SQLNCLI it works as expected. Here is an entire dbparm
string:

Provider='SQLNCLI10',DATASOURCE='CLESQL2K8\DEV',PROVIDERSTRING='Database=MDHTEST;WSID=XPHAMPTON
\MHAMP;APP=DIS;DataTypeCompatibility=80',TrimSpaces=1,Identity='SCOPE_IDENTITY
()',DATETIMEFORMAT=\''YYYYMMDD HH:MM:SS
\'',PBTrimCatCharColumns='YES',PBTrimCharColumns='YES',StaticBind=0,OJSyntax='ANSI',Secure=1,IntegratedSecurity='SSPI'

[mhampton]

More information: I created a standalone test application, and it
works correctly. Both applications are using the same connection
string, but there is a difference in the SQL that is actually sent to
the database: The application that does not work correctly executes
this:

exec [MDHTEST].[sys].sp_procedure_params_rowset N'populate_record',
1,N'dbo',NULL
exec dbo.populate_record N'Dispatch','2010-02-11 14:15:00'

The application that works correctly sends this:

exec [MDHTEST].[sys].sp_procedure_params_90_rowset N'populate_record',
1,N'dbo',NULL
exec dbo.populate_record N'Transaction 3','2010-02-11 14:16:08'

Note that the one that works is using sp_procedure_params_90_rowset,
and the one that doesn't work is using sp_procedure_params_rowset. How
does PowerBuilder determine which one to use?

[mhampton]

More information:

When the application that does not work connects, here is the first
thing it does is:

exec [sys].sp_oledb_ro_usrname
go
exec [sys].sp_provider_types_rowset NULL,NULL
go

When the application that does work connects, it executes the
following:

exec [sys].sp_oledb_ro_usrname
go
exec [sys].sp_provider_types_90_rowset NULL,NULL
go

Note that the result sets for sp_provider_types_rowset and
sp_provider_types_90_rowset are in a slightly different order.
Same database, same login, same connection string, same version of
PowerBuilder. Connecting to SQL Server 2008 with this dbparm:

Provider='SQLNCLI10',DATASOURCE='CLESQL2K8\DEV',PROVIDERSTRING='Database=MDHTEST;WSID=XPHAMPTON

\MHAMP;APP=DIS;DataTypeCompatibility=80',TrimSpaces=1,Identity='SCOPE_IDENTITY()',PBTrimCatCharColumns='YES',PBTrimCharColumns='YES',StaticBind=0,OJSyntax='ANSI',Secure=1,IntegratedSecurity='SSPI'

Can't see why one is choosing to call sp_provider_types_rowset and the
other chooses to call sp_provider_types_90_rowset, but the one that
uses sp_provider_types_90_rowset works and the other always set the
seconds portion of the time of a date time to :00 when calling a RPC
call.

[mhampton]

OK, I made a mistake... I can now duplicate the issue in my testing
application, and have found it related to DataTypeCompatability=80. If
this is present, then the seconds are truncated (set to :00) when
using SQLNCLI10.