Running a stored procedure in Query Analyzer vs ADO - Execution Di

I am running SQL Server 2000 SP3. I have a stored procedure that I call from
a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler and see that the
stored proc executes in 22 seconds. I take the stored procedure call straight
out of profiler and paste it into SQL Query Analyzer and run the stored
procedure. It executes in Query Analyzer in 4 seconds. When I look at the
execution plan, Query Analyzer uses a different plan (more efficient) than
the plan used when the stored procedure is executed through ADO. How is this
possible? What am I doing wrong with ADO? How come the stored procedure will
not execute in 4 seconds when I call it through ADO? I can consistently
duplicate this.
Todd.Danner@.wachovia.com wrote:
> I am running SQL Server 2000 SP3. I have a stored procedure that I
> call from a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler
> and see that the stored proc executes in 22 seconds. I take the
> stored procedure call straight out of profiler and paste it into SQL
> Query Analyzer and run the stored procedure. It executes in Query
> Analyzer in 4 seconds. When I look at the execution plan, Query
> Analyzer uses a different plan (more efficient) than the plan used
> when the stored procedure is executed through ADO. How is this
> possible? What am I doing wrong with ADO? How come the stored
> procedure will not execute in 4 seconds when I call it through ADO? I
> can consistently duplicate this.
How are you executing the procedure from ADO? Post the code you are
testing on ADO (all relevant code) and the SQL you use in QA..
David G.

Running a stored procedure in Query Analyzer vs ADO - Execution Di

I am running SQL Server 2000 SP3. I have a stored procedure that I call from
a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler and see that the
stored proc executes in 22 seconds. I take the stored procedure call straigh
t
out of profiler and paste it into SQL Query Analyzer and run the stored
procedure. It executes in Query Analyzer in 4 seconds. When I look at the
execution plan, Query Analyzer uses a different plan (more efficient) than
the plan used when the stored procedure is executed through ADO. How is this
possible? What am I doing wrong with ADO? How come the stored procedure will
not execute in 4 seconds when I call it through ADO? I can consistently
duplicate this.Todd.Danner@.wachovia.com wrote:
> I am running SQL Server 2000 SP3. I have a stored procedure that I
> call from a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler
> and see that the stored proc executes in 22 seconds. I take the
> stored procedure call straight out of profiler and paste it into SQL
> Query Analyzer and run the stored procedure. It executes in Query
> Analyzer in 4 seconds. When I look at the execution plan, Query
> Analyzer uses a different plan (more efficient) than the plan used
> when the stored procedure is executed through ADO. How is this
> possible? What am I doing wrong with ADO? How come the stored
> procedure will not execute in 4 seconds when I call it through ADO? I
> can consistently duplicate this.
How are you executing the procedure from ADO? Post the code you are
testing on ADO (all relevant code) and the SQL you use in QA..
David G.

Running a Stored Procedure from ADO

Hi,
From one of my forms in an Access Project I want to run a Stored Procedure on the "After Insert" event of the form. The stored procedure is an Insert SQL Statement with two variables.
What is the best way to pass the variables to the stored procedure and run it.

Should I run a SQL Statement like

srtSQL="EXEC SP_Insert (@.Var1=Var1,@.Var2=Var2)

Or there is a better way to do it with ADO objects?

Thanks
==============================================

I found two solutions. Which one do you think is the better one?
The stored procedure on the Server:

CREATE PROCEDURE SP_Insert_Into_CompanyAdrsContact
@.CompAdrsID int,
@.ContactID int
AS
Insert into tblCompanyAdrsContact (CompAdrsID,ContactID) Values (@.CompAdrsID,@.ContactID)
GO

On the Access form:

1) Using a SQL stament dynamically to pass parameters from a form:

strSQL = "Exec SP_Insert_Into_CompanyAdrsContact " & Me!CompAdrsID
strSQL = strSQL & "," & Me!ContactID
DoCmd.RunSQL (strSQL)

**********************************************
2) Using ADO objects:
Dim cmd As ADODB.Command
Dim prmContactID As ADODB.Parameter
Dim prmCompAdrsID As ADODB.Parameter

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "SP_Insert_Into_CompanyAdrsContact"

Set prmCompAdrsID = cmd.CreateParameter("@.CompAdrsID", adInteger, adParamInput)
Set prmContactID = cmd.CreateParameter("@.ContactID", adInteger, adParamInput)

cmd.Parameters.Append prmCompAdrsID
prmCompAdrsID.Value = Me![CompAdrsID]
cmd.Parameters.Append prmContactID
prmContactID.Value = Me![ContactID]

cmd.ExecuteIf you are going to call this proc many times, it's best to use command parameter. This will allow you to gain some performance because of cache. If it's just a one time thing, just execute the string is fine.

Run a stored proc using vbscript

I understand that it may not be a good thing to use an ADO execute statement
using VBscript to update a SQL database. Rather the preferred method is to
execute a stored procedure. How does one do this using VBscript, and how
would you confirm that the update took place properly ?
Thanks !Hi,
You will receive an error by Errors collection on connection object.
Tomasz B.
"Rob C" wrote:

> I understand that it may not be a good thing to use an ADO execute stateme
nt
> using VBscript to update a SQL database. Rather the preferred method is
to
> execute a stored procedure. How does one do this using VBscript, and how
> would you confirm that the update took place properly ?
> Thanks !
>
>|||How To Invoke a Stored Procedure with ADO Query Using VBA/C++/Java
http://support.microsoft.com/?kbid=185125
AMB
"Rob C" wrote:

> I understand that it may not be a good thing to use an ADO execute stateme
nt
> using VBscript to update a SQL database. Rather the preferred method is
to
> execute a stored procedure. How does one do this using VBscript, and how
> would you confirm that the update took place properly ?
> Thanks !
>
>|||Rob C wrote:
> I understand that it may not be a good thing to use an ADO execute
> statement using VBscript to update a SQL database. Rather the
> preferred method is to execute a stored procedure. How does one do
> this using VBscript, and how would you confirm that the update took
> place properly ?
> Thanks !
This was written with ASP in mind. Remove "Server." from the createobject
statements to make it relevant to straight vbscript :
http://groups.google.com/groups?hl=...FTNGP12.phx.gbl
As somebody else stated, errors will be returned to the connection's errors
collection (the first error in the collection will usually bubble up to the
vbscript error-handler as well)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.