"Bad variable type" error msg can't get past it.

I posted this here a few days ago, and someone said to ask on a delphi
forum. Well, I did, and also have searched everywhere I can imagine,
without luck.
My stored proc when executed from delphi 7 produces a Bad variable type
error. This happens on win2k and win98, but does not happen on XP.
It runs from query analyser both on XP and on win2k. It runs in the app on
XP.
The app creates the parameters for the sp component and assigns data types,
which I've verified match the sp.
Clearly no one here knows the exact answer, or you would have said it by
now. So how about more ideas about where to look?
JeremyHi
I assume you are using ADO? Have you checked out MDAC consistency and
versions? Have you SET NOCOUNT ON?
You may also want to post some code and DDL.
John
"JeremyGrand" wrote:

> I posted this here a few days ago, and someone said to ask on a delphi
> forum. Well, I did, and also have searched everywhere I can imagine,
> without luck.
> My stored proc when executed from delphi 7 produces a Bad variable type
> error. This happens on win2k and win98, but does not happen on XP.
> It runs from query analyser both on XP and on win2k. It runs in the app o
n
> XP.
> The app creates the parameters for the sp component and assigns data types
,
> which I've verified match the sp.
> Clearly no one here knows the exact answer, or you would have said it by
> now. So how about more ideas about where to look?
> Jeremy
>
>|||Yes, ADO. MDAC is the latest, and the win2k sps are up to date.
I will try setting nocount on. How might this help?
Here's the table I'm inserting into:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[AGNT_STATMENT]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[AGNT_STATMENT]
GO
CREATE TABLE [dbo].[AGNT_STATMENT] (
[StatementNumber] [bigint] NOT NULL ,
[Item] [smallint] NOT NULL ,
[DatePosted] [smalldatetime] NULL ,
[Type] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Debit] [float] NULL ,
[Credit] [float] NULL ,
[Balance] [float] NULL ,
[Description] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoanNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Certificate] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CommissionPercent] [float] NULL ,
[FeeDr] [money] NULL ,
[FeeCr] [money] NULL ,
[EntryDate] [smalldatetime] NULL ,
[Department] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PaymentStatementNumber] [bigint] NULL ,
[PaymentItem] [int] NULL ,
[PaymentAmount] [money] NULL ,
[PaymentFee] [money] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AGNT_STATMENT] WITH NOCHECK ADD
CONSTRAINT [PK_AGNT_STATMENT] PRIMARY KEY CLUSTERED
(
[StatementNumber],
[Item]
) ON [PRIMARY]
GO
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:18DADDD6-1A68-4615-97AB-55A502A51176@.microsoft.com...
> Hi
> I assume you are using ADO? Have you checked out MDAC consistency and
> versions? Have you SET NOCOUNT ON?
> You may also want to post some code and DDL.
> John
> "JeremyGrand" wrote:
>|||Hi
I would not expect SET NOCOUNT ON to make a difference unless you are
looking at returning a result set. It is not clear if you have are looking a
t
the same database/server when you experience the problem. It is also not
clear if it happens every time on the platforms that it does not work on. If
this is the same server then it points to a local problem on the clients tha
t
fail.
You may want to run SQL Profiler to see what is being sent to the database.
At a guess the most likely cause would be the date/time or money values whic
h
could be effected by the regional settings.
John
"John Bell" wrote:
> Hi
> I assume you are using ADO? Have you checked out MDAC consistency and
> versions? Have you SET NOCOUNT ON?
> You may also want to post some code and DDL.
> John
> "JeremyGrand" wrote:
>|||John, Profiler output is interesting. The trace seems to only show the
steps up to but not including the execution of the problem stored proc. I
have the execution wrapped in a try-block, and an exception is thrown. I
don't see evidence of this in the profiler trace. I've searched the output
and can't find any reference to the stored proc in question.
So, this probably implies that my ado component is not happy with the data
or parameters I'm feeding it.
On the other hand, I can consistently reproduce the error on the only 2
win2k machines I've tested, and it also is reported to fail on a win98
machine. The program consistently runs without error on at least 2 XP
machines, so this seems very OS dependent.
I've looked at regional settings (money, dates) on my own w2k machine, and
they seem normal.
I did a trace while running the stored proc on an XP machine. Here's the
line where the sp executes. Nothing leaps out at me, but maybe someone will
see something.
exec agnt_statment_InsertNew 261, NULL, 'CAN', 0.000000000000000e+000,
5.050000000000000e+002, -2.072300000000000e+004, 'XQN000000055',
'72AG99-143', 'XQN', '000000055', 3.500000000000000e+001,
0.000000000000000e+000, 0.000000000000000e+000, 'Nov 1 2005
12:00:00:000AM', '', NULL, NULL, 0.000000000000000e+000,
0.000000000000000e+000
-Jeremy

> You may want to run SQL Profiler to see what is being sent to the
> database.
> At a guess the most likely cause would be the date/time or money values
> which
> could be effected by the regional settings.
> John
>|||Hi
Are you tracing the start events?
You may want to try creating a dummy procedure with the same parameters and
see if that works, you may have to return a default result set. It will rule
out the parameter passing. You could try hard coding the parameters that you
listed and seeing if it works, then change the values to be dynamic one at a
time, alternatively you could try remove each parameter one at a time until
you don't get the error.
John
"JeremyGrand" wrote:

> John, Profiler output is interesting. The trace seems to only show the
> steps up to but not including the execution of the problem stored proc. I
> have the execution wrapped in a try-block, and an exception is thrown. I
> don't see evidence of this in the profiler trace. I've searched the output
> and can't find any reference to the stored proc in question.
> So, this probably implies that my ado component is not happy with the data
> or parameters I'm feeding it.
> On the other hand, I can consistently reproduce the error on the only 2
> win2k machines I've tested, and it also is reported to fail on a win98
> machine. The program consistently runs without error on at least 2 XP
> machines, so this seems very OS dependent.
> I've looked at regional settings (money, dates) on my own w2k machine, and
> they seem normal.
> I did a trace while running the stored proc on an XP machine. Here's the
> line where the sp executes. Nothing leaps out at me, but maybe someone wi
ll
> see something.
> exec agnt_statment_InsertNew 261, NULL, 'CAN', 0.000000000000000e+000,
> 5.050000000000000e+002, -2.072300000000000e+004, 'XQN000000055',
> '72AG99-143', 'XQN', '000000055', 3.500000000000000e+001,
> 0.000000000000000e+000, 0.000000000000000e+000, 'Nov 1 2005
> 12:00:00:000AM', '', NULL, NULL, 0.000000000000000e+000,
> 0.000000000000000e+000
> -Jeremy
>
>|||John, brilliant idea. I've created a dummy program and a dummy stored proc
and am experimenting. I can actually make a program where the stored proc
succeeds by dropping a sp component on a form, whereas creating it in code
fails on w2k boxes. I guess you've gotta say that's progress!
The evidence also seems to be supporting the notion that the problem is in
delphi. There was an issue with D7.1 last year, and I'm pursuing this at
the moment.
Thanks.
Jeremy
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3FD30FD2-D288-46AD-94AB-1AF6B9ECEBCB@.microsoft.com...
> Hi
> Are you tracing the start events?
> You may want to try creating a dummy procedure with the same parameters
> and
> see if that works, you may have to return a default result set. It will
> rule
> out the parameter passing. You could try hard coding the parameters that
> you
> listed and seeing if it works, then change the values to be dynamic one at
> a
> time, alternatively you could try remove each parameter one at a time
> until
> you don't get the error.
>
> John|||Hi
"Bad variable type" is definitely generated by Delphi!
I googled this!
http://tinyurl.com/9h39b
This seems to imply you need to be very careful when choosing the datatypes
so you may not want to leave the application to do this automatically.
http://tinyurl.com/aguh8
John
"JeremyGrand" wrote:

> John, brilliant idea. I've created a dummy program and a dummy stored pro
c
> and am experimenting. I can actually make a program where the stored proc
> succeeds by dropping a sp component on a form, whereas creating it in code
> fails on w2k boxes. I guess you've gotta say that's progress!
> The evidence also seems to be supporting the notion that the problem is in
> delphi. There was an issue with D7.1 last year, and I'm pursuing this at
> the moment.
> Thanks.
> Jeremy
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3FD30FD2-D288-46AD-94AB-1AF6B9ECEBCB@.microsoft.com...
>
>|||John, good catch, these are hits I didn't find. I eventually followed the
trail to Borland's Quality Central, where it claims to be resolved, but
maybe not in Delphi 7, which is what I'm using.
However, fwiw, the solution for me seems to be to ask Delphi to enumerate
the sp's parameters (tadodataset.parameters.refresh, if you care to know),
and then I assign values in code. The app now works on w2k. Previously I
was creating the parameter objects and setting properties, and no variation
could be made to work.
Thanks for your help!
Jeremy
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:43541BEF-DFEE-4A07-9C98-90EFE2259D89@.microsoft.com...
> Hi
> "Bad variable type" is definitely generated by Delphi!
> I googled this!
> http://tinyurl.com/9h39b
> This seems to imply you need to be very careful when choosing the
> datatypes
> so you may not want to leave the application to do this automatically.
> http://tinyurl.com/aguh8
> John
>
>
> "JeremyGrand" wrote:
>