Greetings, Pepe - 

You should be able to use datetime argument values in a SQL Server stored procedure that is the data source for a DataWindow. The problem you are having is likely related to one or more data type mismatches. Can you please post the initial portion of the SQL source code for the stored procedure that defines the argument list (Declare Procedure dbo.InformeRegistroAnalisisActuacion @DesdeProveedor=???, @HastaProveedor = ???, etc.) so that we may see the SQL data type for each argument parameter?

Also edit the source for the DataWindow and extract the just the part of the syntax where the DataWindow's retrieval arguments are defined and include that information.

Lastly, please tell us the SQLCA.DBParm string you are using to connect to the SQL Server database with the SNC driver. Please mask out any sensitive information.

What version of SQL Server are you using? What version, release, build of PB are you using?

I'm a little concerned with your statement: "the Datetime format that must be yyyymmdd...". Can you please explain what you mean by this, because the display format you use in your PB app should really not affect data retrieval, unless I am misunderstanding what you are saying.

Regards, John

Hi @John !

Here is the SP code:

ALTER PROCEDURE [dbo].[InformeRegistroAnalisisActuacion]
--DECLARE
@DesdeProveedor VARCHAR(20)
, @HastaProveedor VARCHAR(20)
, @RangoProveedores VARCHAR(MAX)
, @DesdeFinca VARCHAR(20)
, @HastaFinca VARCHAR(20)
, @RangoFincas VARCHAR(MAX)
, @DesdeCultivo INT
, @HastaCultivo INT
, @RangoCultivos VARCHAR(MAX)
, @RangoTipoAnalisisFinca VARCHAR(MAX)
, @RangoTipoAnalisisCultivo VARCHAR(MAX)
, @DesdeFecha DATETIME
, @HastaFecha DATETIME
, @RangoFechas VARCHAR(MAX)
, @DesdeTecnico VARCHAR(20)
, @HastaTecnico VARCHAR(20)
, @RangoTecnicos VARCHAR(MAX)
, @ObtenerEsquema BIT
AS
-- Establecemos los valores por defecto
DECLARE @MaxChar VARCHAR(3), @MinChar VARCHAR(1), @MaxDate DATETIME, @MinDate DATETIME, @MinInt INT, @MaxInt INT
SELECT @MaxChar = 'zzz', @MinChar = '', @MaxDate = '30000101', @MinDate = '19000101', @MinInt = 0, @MaxInt = 2147483647

I can't edit the source of the Datawindow because I can't even create it...

Hi John, 

I can't obtain a resultset because the Stored procedure is not mine, is for another company.

I don't have anything in my dbparm string, just Provider='SQLNCLI11',Database='ERPHispatec', nothing about the datetime format. 

This is the version of PB

And this is my connection string:

// Profile sql_hispatec
SQLCA.DBMS = "TRACE SNC SQL NATIVE CLIENT(OLE DB)"
SQLCA.LogPass = <*******>
SQLCA.ServerName = "SRVHAMSQL01\ERP"
SQLCA.LogId = "xxxxx"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Provider='SQLNCLI11',Database='ERPHispatec'"

Thank you very much!

This does not make a lot of sense... I cannot help but think there are some key factors that you have not disclosed. Is the information in this database imported from and IBM DB2 database? When I search for "SQL Server SQLSTATE 22007" I get hits related to data imported from DB2 databases. And you still have not told me what version of SQL Server you are running, so I'm wondering what else you have not told me.

Ignoring PB for a second, in SQL Server Management Studio, can you execute the SQL statement:

execute dbo.InformeRegistroAnalisisActuacion @DesdeProveedor = '0', @HastaProveedor = '0', @RangoProveedores = '0', @DesdeFinca = '0', @HastaFinca = '0', @RangoFincas = '0', @DesdeCultivo = 0, @HastaCultivo = 0, @RangoCultivos = '0', @RangoTipoAnalisisFinca = '0', @RangoTipoAnalisisCultivo = '0', @DesdeFecha = '1990-07-14 00:00:00.000', @HastaFecha = '1990-07-14 00:00:00.000', @RangoFechas = '0', @DesdeTecnico = '0', @HastaTecnico = '0', @RangoTecnicos = '0', @ObtenerEsquema = 0

Does this work? If not, what error(s) do you get?

Still in SQL Server Management Studio, if you then revise the Execute statement to be:

execute dbo.InformeRegistroAnalisisActuacion @DesdeProveedor = '', @HastaProveedor = '', @RangoProveedores = '', @DesdeFinca = '', @HastaFinca = '', @RangoFincas = '', @DesdeCultivo = 0, @HastaCultivo = 0, @RangoCultivos = '', @RangoTipoAnalisisFinca = '', @RangoTipoAnalisisCultivo = '', @DesdeFecha = '19900714', @HastaFecha = '19900714', @RangoFechas = '', @DesdeTecnico = '', @HastaTecnico = '', @RangoTecnicos = '', @ObtenerEsquema = 0

This works?

Two observations:

1. Your assumption that '0' is some kind of default or wild-card is incorrect; It appears that the stored procedure expects an empty string to be used instead.

2. The normal format for date/datetime values in SQL Server does not apply here, which seems to point back to how the data was imported from DB2, if the info regarding SQLSTATE 22007 is correct.

Back in PB, you might try specifying the datetime format in the SQLCA.DBParm setting:

SQLCA.DBParm = "Provider='SQLNCLI11',Database='ERPHispatec',DateTimeFormat='yyyymmdd'"

and maybe also Date='yyyymmdd' will be needed... I don't know as I have never encountered this kind of issue before.

If these suggestions don't work, regrettably I have no other ideas I can offer you.