Increase bScale in DBPARAMBINDINFO Error in PB 12.1 Ver 7000

[Former Member]

Hello.  Can't seem to get around the below error in PB 12.1 Ver 7000 when calling a Stored Procedure from Powerscript and am passing Datetime values.  I have played around with the connection strings Date formatting, but no matter what I attempt, it still looks like the PB is passing MM/DD/YYYY HH:MM:SS when SQL Server 2012 is expectring YYYY-MM-DD HH:MM:SS.

Full error:

SQLSTATE = 22007

Microsoft SQL Server Native Client 11.0

The fractional part of the provided time value overflows the scale of the corresponding SQL Server parameter or column. Increase bScale in DBPARAMBINDINFO or column scale to correct this error.

I've tried to use providers of SQLOLEDB, SQLNCLI10 & SQLNCLI11

Connection Profile String (Connection works fine):

// Profile for SIngle Sign On....

SQLCA.DBMS = "OLE DB"

SQLCA.Lock = "RU"

SQLCA.AutoCommit = False

SQLCA.DBParm = "PROVIDER='SQLNCLI11',DATASOURCE='DB-DNS-ENTRY',PBCatalogOwner='dbo',INTEGRATEDSECURITY='SSPI'"

In the above, I have also tried to specify the following, whcih also works, but the same result (notice the date parameters match what SQL Server should expect for Datetime data types...

// Profile

SQLCA.DBMS = "OLE DB"

SQLCA.LogPass = <**************>

SQLCA.LogId = "USERID"

SQLCA.Lock = "RU"

SQLCA.AutoCommit = False

SQLCA.DBParm = "PROVIDER='SQLOLEDB',DATASOURCE='DB-DNS-ENTRY',PROVIDERSTRING='Database=DB-NAME',DateFormat='yyyy-mm-dd',DateTimeFormat='yyyy-mm-dd hh:mm:ss.fff',TimeFormat='hh:mm:ss.fff'"

[Former Member]

Ok, I think that I found the issue.  I did not apply any more changes to the DB Profile.  Our app is over 15 years old and many hands at varying times have made modifications.  I was working on a workaround to our DBPARAMBIND problem by modifying the Stored Procedure and what parameters it expected (limiting datetime parameters), but the code was not "pulling in" or recognizing the latest changes I made. It turns out that the following style of code to invoke a Stored Procedure was not pulling in the most recent version of the SP:

SQLCA.PROCEDURE_NAME(ld_param1, ld_param2, ldt_param3...)

I am not sure what version of Stored Procedure it was invoking??  Only then did I attempt to rewrite the call to what I was familiar with in the following format:

Declare pb_procedure_alias procedure for PROCEDURE_NAME &

@param1 = ld_param1,

@param2 = ld_param2,

@param3 = ld_param3 USING SQLCA;

Execute pb_procedure_alias;

The DBPARAMBINDINFO error disappeared.  Original problem solved, no need for a workaround.

Not sure if this issue is a result of our move to SQL Server 2008 and then to 2012 and still using an older version of Powerbuilder (12.1 Build 7000)???

Explanations???

[Former Member]

First, some clarification.  SQL Server does not "expect" any particular format for datetime literals.  It may not be able to correctly interpret some formats depending on certain factors, but it does not require any particular format.  It is best to use a format that is not ambiguous, but that is a different subject.

I suggest you look at the actual statement that is generating this error.  You seem to have jumped to a conclusion that is not consistent with the error message.  So before you waste a lot of time, verify that your assumption is correct. The error message explicitly states that the time portion is overflowing - it is not complaining about interpreting the date portion.

FWIW I think this is likely to involve the black art of configuring PB and the database interface.  I'm not certain if OLE uses the pbodbxxx.ini file for datetime formats or if it can really be controlled via dbparm.  The documentation is sparse and confusing. You might try fiddling with that - and be aware of which actual file you need to fiddle with.

And I'll second the suggestion to move to SNC.  Not had any problems using datetime values with multiple versions sql server and PB in a while.  You should also verify that the column involved is not smalldatetime - which of cource only supports seconds (not fractional seconds).

[Former Member]

One thing that I neglected to specify is that when you generate the executable, we do not have this issue.  It is only when running within Powerbuilder that we do, thus the Database Profile.  We are running PB 12.1 Version 7000.

We have PBODB120.DLL as well as PBOLE120.DLL along with PBODB120.INI in our runtime directory.

It sounds like that you are stating that we do not need to specify date or time formatting within the Database Profile when running the Powerbuilder tool?  We always have.  Maybe our move to SQL 2008 and then to SQL 2012 caused this issue, as this is not new code.

Fo this application, we don't care about the fractional second time values.  As a quick test, I converted the SQL Server SP to utilize SmallDateTime, and still received the above error.  PB needs something on it's end to communicate.  Unfortunately, PB does not have a smalldatetime, as I would have changed that data type as well for another quick test.

In debug mode, I have previously reviewed the values that are being passed to SQL Server, and the Datetime values were in the MM/DD/YYYY HH:MM:SS

format (2/17/2015 13:04:21), even though I had specified YYYY-MM-DD HH:MM:SS:FFF within the DB Profile as SQL Server expects.

[Former Member]

Hi Clint;

  Your Date & Time formats are different than what I use....

SQLCA.DBParm = "Database='Master',Provider='SQLNCLI10',DelimitIdentifier=1,TrustedConnection=1,DateFormat='YYYY-MM-DD',TimeFormat='HH:MM:SS',DateTimeFormat='YYYY-MM-DD HH:MM:SS:FFF'"

I'm not sure why you have "\" in yours?

Regards ... Chris

[Former Member]

Hi Clint;

  FWIW: Setting the "provider" in your OLE connection string to SNC is really not really using SS's "native client". Its still flowing through the OLE "middle-ware" which could be the source of your problem. Try setting your DB Client to SNC directly.

ie:  SQLCA.DBMS = "SNC"

HTH

Regards ... Chris

[Former Member]

Thanks Chris for responding quickly.  I applied your suggestion and it resulted in the following (I was able to get a good connection).  However, I am still getting the DBPARAMBINDINFO error that I stated above.  Not certain if Date format is accurate.  I know it is using SNC 10.0 for SQL Server 2008.  We have SQL Server 2012, but a prior post of yours may have indicated that what you suggest would work.

[DBMS_PROFILES]
Profiles=AMS DEV SNC

[Profile AMS DEV SNC]
DBMS=SNC SQL Native Client(OLE DB)
Database=
UserId=
DatabasePassword=xxxxxxxxxxxxxxxxxxxxxxxxxx
LogId=
LogPassword=xxxxxxxxxxxxxxxxxx
ServerName=xxxxxxxxxxxxxxxxxxxx
DBParm=ProviderString='INTEGRATEDSECURITY='SSPI',DataTypeCompatibility=',AppName='AMS',DateTimeFormat=''\'yyyy-mm-dd hh:mm:ss.fff\'',TrustedConnection=1,StaticBi',Provider='SQLNCLI10'
Lock=RU
Prompt=FALSE
AutoCommit=FALSE
NewLogic=EAS

From the above, I did notice that sometimes the Preview truncates some of the values set, like 'taticBind' versus 'StaticBI' above, but that is another story.