URGENT!! VB/SQL Question PLEASE HELP!!

URGENT!! VB/SQL Question PLEASE HELP!!

Post by Angie Schillin » Tue, 28 Sep 1999 04:00:00



I am trying to execute a stored procedure through vb 6.0 in sql 7.  I get
very different results running it from vb app than from query analyzer.  Any
suggestions??

--
Angela M. Schilling

 
 
 

URGENT!! VB/SQL Question PLEASE HELP!!

Post by Robert Chapma » Tue, 28 Sep 1999 04:00:00


Use the SQL Profiler to verify that the parameters that you are passing to
the stored procedure are the same from both VB and the query analyzer.

Is it possible for you to post your VB code and the stored procedure?

Cheers
Rob


> I am trying to execute a stored procedure through vb 6.0 in sql 7.  I get
> very different results running it from vb app than from query analyzer.
Any
> suggestions??

> --
> Angela M. Schilling



 
 
 

URGENT!! VB/SQL Question PLEASE HELP!!

Post by Angie Schillin » Thu, 30 Sep 1999 04:00:00


VB 6.0 Code:

Sub ExecuteSP()
With db3

.Connect = "dsn=ero;uid=;pwd=;"
.EstablishConnection rdDriverNoPrompt, True
If cmbDelivery.Text = "Fax" Then

Else

End If
Label14.Caption = SQL3
Set Qy3 = .CreateQuery("Sample3", SQL3)
Set Rs3 = Qy3.OpenResultset(rdOpenStatic)

Rs3.Close
db3.Close

End With

End Sub

Stored Procedure:

Begin




if exists(select * from sysobjects where name='faxtemp' and type='U')
 truncate table faxtemp

declare random cursor forward_only for select * from FaxJobs
open random


BEGIN





END
CLOSE random
DEALLOCATE random
End

MailList is similar to FaxList only more fields.

Thanks,
Angie

> Use the SQL Profiler to verify that the parameters that you are passing to
> the stored procedure are the same from both VB and the query analyzer.

> Is it possible for you to post your VB code and the stored procedure?

> Cheers
> Rob



> > I am trying to execute a stored procedure through vb 6.0 in sql 7.  I
get
> > very different results running it from vb app than from query analyzer.
> Any
> > suggestions??

> > --
> > Angela M. Schilling


 
 
 

URGENT!! VB/SQL Question PLEASE HELP!!

Post by Robert Chapma » Fri, 01 Oct 1999 04:00:00


I am afraid that I can not be of much help in this case as I don't use RDO
at all.  I'm an ADO guy to the bitter end.

Good luck.
Rob


> VB 6.0 Code:

> Sub ExecuteSP()
> With db3

> .Connect = "dsn=ero;uid=;pwd=;"
> .EstablishConnection rdDriverNoPrompt, True
> If cmbDelivery.Text = "Fax" Then

> Else

> End If
> Label14.Caption = SQL3
> Set Qy3 = .CreateQuery("Sample3", SQL3)
> Set Rs3 = Qy3.OpenResultset(rdOpenStatic)

> Rs3.Close
> db3.Close

> End With

> End Sub

> Stored Procedure:

> Begin




> if exists(select * from sysobjects where name='faxtemp' and type='U')
>  truncate table faxtemp

> declare random cursor forward_only for select * from FaxJobs
> open random


> BEGIN





> END
> CLOSE random
> DEALLOCATE random
> End

> MailList is similar to FaxList only more fields.

> Thanks,
> Angie


> > Use the SQL Profiler to verify that the parameters that you are passing
to
> > the stored procedure are the same from both VB and the query analyzer.

> > Is it possible for you to post your VB code and the stored procedure?

> > Cheers
> > Rob



> > > I am trying to execute a stored procedure through vb 6.0 in sql 7.  I
> get
> > > very different results running it from vb app than from query
analyzer.
> > Any
> > > suggestions??

> > > --
> > > Angela M. Schilling


 
 
 

URGENT!! VB/SQL Question PLEASE HELP!!

Post by Michael Vlad » Wed, 15 Dec 1999 04:00:00


Angie,

Two considerations.

1. Make sure the RAND() returns the same numeric sequence in both cases (VB and
QA).

2. There is some float \ real (see note) type formatting during popping the
VB's  x value from stack in your stored procedures. To avoid it, try passing an
integer number and formatting it in the stored procedure and setting the decimal
point in there. There should be some convention \  protocal between how you
normalize your real value (x) in VB into integer (like multiplying by 100 and
rounding up before passing it to your stored procedure and restoring it there
back to the floating point format  - dividing by 100).

M.

Note.. You did not mention x's type in VB .


> I am trying to execute a stored procedure through vb 6.0 in sql 7.  I get
> very different results running it from vb app than from query analyzer.  Any
> suggestions??

> --
> Angela M. Schilling


 
 
 

URGENT!! VB/SQL Question PLEASE HELP!!

Post by Michael Vlad » Wed, 15 Dec 1999 04:00:00


Angie,

Two considerations.

1. Make sure the RAND() returns the same numeric sequence in both cases (VB and
QA).

2. There is some float \ real (see note) type formatting during popping the
VB's  x value from stack in your stored procedures. To avoid it, try passing an
integer number and formatting it in the stored procedure and setting the decimal
point in there. There should be some convention \  protocal between how you
normalize your real value (x) in VB into integer (like multiplying by 100 and
rounding up before passing it to your stored procedure and restoring it there
back to the floating point format  - dividing by 100).

M.

Note.. You did not mention x's type in VB .


> I am trying to execute a stored procedure through vb 6.0 in sql 7.  I get
> very different results running it from vb app than from query analyzer.  Any
> suggestions??

> --
> Angela M. Schilling