Problem with stored procs using MSSQL2000, Delphi 2007 and DBExpress

[Jauch]

Hi!

I write two programa with Delphi 2007, accessing MSSQL2000 via
DBExpress components.

On my computer (with DElphi installed) all is ok. Both the programs
works perfectly.

On the first, I use various data components, like DBEdit, etc. All the
process is done with SQL queries into TSQLDataSet components of
DBExpress.

On the second, some process use this type of SQL query (like SELECT *
FROM...), but the most part use stored procs, like

SQLDataSet1.CommandType := ctStoredProc;
SQLDataSet1.CommandText := 'getValues';

On MY computer, like I said, all works fine.
But on a test computer (without the Delphi instaled), I got error when
trying to execute the stored Proc.
He finds the stored proc and give me errors of sintax. But if I
execute the stored proc from the SQL Analizer, with the same
parameters, he give me the results!!!!

here is the stored proc and parameters useds for test:


Called with the parameters (0, '''2000-01-01''', '''2008-04-05''', '',
'') works on my computer and on the test computer if executed from the
SQL Analizer.

CREATE PROCEDURE [msGetDocuments]

@OnlyNotIntegrated as bit,
@StartPeriod as varchar(15),
@EndPeriod as varchar(15),
@EnterpriseFilter as varchar(2000),
@SiteFilter as varchar(2000)

AS

DECLARE
@SelectBase as varchar(2000),
@OnlyNotIntegratedBase as varchar(50),
@Select as varchar(6050),
@NumberOfDocuments as int

SET @SelectBase =
'
SELECT
a.idDocAM as DocumentId,
a.natDoc as DocumentType,
a.idOr as ORNumber,
a.typCIGA as CIGA,
a.numDoc as DocumentNumber,
a.datF as BillDate,
a.etaF as ETAF,
a.etaI as ETAI,
a.idCli as ClientId,
a.idVeh as VehicleId,
a.idDev as CurrencyId,
a.idTVARc as IVARcId,
a.idTVA1 as IVAId1,
a.idTVA2 as IVAId2,
a.idTVA3 as IVAId3,
a.idTVA4 as IVAId4,
a.idTVA5 as IVAId5,
a.idTVA6 as IVAId6,
a.monTax1 as MonTax1,
a.monTax2 as MonTax2,
a.monTax3 as MonTax3,
a.monTax4 as MonTax4,
a.monTax5 as MonTax5,
a.monTax6 as MonTax6,
a.totTTC as Total,
a.sender as StartedBy,
o.idSer as SerId,
o.atemag as Atemag,
ce.Libelle as Internal
FROM
wtDocAM a, wtOR o, wtCes ce
WHERE (ce.Code = a.idCesDef)
AND
((a.natdoc = 0) OR (a.natdoc = 1))
AND
(a.etaF = 1)
AND
(o.idOr = a.idOr)
AND
(a.datF >= ' + @StartPeriod + ')
AND
(a.datF <= ' + @EndPeriod + ')'

SET @OnlyNotIntegratedBase = ' AND (a.etaI = 0)'

SET @Select = @SelectBase

IF @OnlyNotIntegrated = 1
SET @Select = @Select + @OnlyNotIntegratedBase

IF @EnterpriseFilter <> ''
SET @Select = @Select + ' AND ' + @EnterpriseFilter

IF @SiteFilter <> ''
SET @Select = @Select + ' AND ' + @SiteFilter

EXEC(@Select)

GO


I try to change the stored proc for a 'normal' query (CommandText :=
'SELCT ...'). Again, on my computer, all is ok. On the other computer,
I got a new error message: TDBXTypes.WIDESTRING value type cannot be
accessed as TDBXTypes.WIDESTRING value type (What??????????????)

The databases contain different data, but the structure is the same...

Don\t appear that missing files is the case... I Copy the dbexpress
dll to the test computer...

Anyone can give me some light here?

[Jauch]

I do some tests.

Create a simple apllication that do this:

with DS1 do
begin
if Active then
Close;

CommandType := ctStoredProc;
SchemaName := 'dbo';
CommandText := 'msGetSites';

Params.Clear;
Params.AddParam(Params.CreateParam(ftString, '@EnterpriseId',
ptInput));
ParamByName('@EnterpriseId').AsString := EnterpriseId;
Open;

Memo1.Lines.Add(FieldByName('SiteId').AsString);
end;


The StoredProc:

IF EXISTS
(
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[msGetSites]')
AND
OBJECTPROPERTY(id, N'IsProcedure') = 1
)
DROP PROCEDURE [dbo].[msGetSites]
GO

CREATE PROCEDURE [msGetSites]

@EnterpriseId as varchar(50)

AS

SET NOCOUNT ON

SELECT
*
FROM
msSite
WHERE
EnterpriseId = @EnterpriseId

GO

SET NOCOUNT OFF

On one computer With Delphi 2007 installed and Windows Server 2003,
this code works.

On another computer, Without the Delphi 2007 instaled and with XP,
this code says this:
SQL State 42000, SQL Error Code: 201
Procedure 'msGetSites' expects parameter '@EnterpriseId', which was
not supplied.


I found many articles on the net about this, not with no solution on
them...

The stored proc works ok on computers one and two.
My code works ok on computer one (server 2003).

What is the difference that make my code not work on other machines
that do not have the Delphi instaled (I tested on a machine with the
VISTA and delphi and works ok)? See that only this problem with pass
paremeters to stored procedures is the problem. Executing sp that do
not take parameters works ok. If I use the select on code, work ok
two.

The databases are the same. I have the ini files and the dbxmss30.dll
on the computers as well...

What I1m missing here?