Stored Procedure Results Different Between VBA and Query Analyzer Exec

[HardingR2000]

I have a MS SQL stored procedure that updates a couple of tables. It does not return a result set. When executed in Access VBA via an ADODB Execute command it does not process correctly. It is as if some of the update queries within the stored procedure do not execute. When executed in Query Analyzer the tables are updated correctly. The parameters passed to the stored procedure are the same. There are no messages created when executed in Query Analyzer and as far as I can tell there are no message when executed in VBA.

Does anyone have a suggestion?

Randy

 

[PWise]

Are any parameters bit values

 

[HardingR2000]

Here is a snipit of the VBA code containing the parameter setup and Execute command. The "Pricing_Calc_sp" stored procedure brings data together, populates, and updates some tables. This is the stored procedure that is working when run using Query Analyzer but it does not run correctly when executed here. Please understand, I cannot tell if it is failing in some way when run, but it does run to completion and the VBA code continues on to the next stored procedure ( "Pricing_CalcReport_sp" ) which creates the tables used by the report ( "Pricing_Calc_Rpt" ) which is called at the end of the code below. All of this happens without any errors that I am aware of.

As you can see below I have put Debug.Print code within the VBA to check the parameter settings. They are correct.

Dim CMD As New ADODB.Command
Dim Param As ADODB.Parameter
Dim errLoop As ADODB.Error

CMD.ActiveConnection = NGMSConnect
CMD.CommandText = "Pricing_Calc_sp"
CMD.CommandType = adCmdStoredProc

Set Param = New ADODB.Parameter
Param.Name = "Start"
Param.Type = adDBTimeStamp
Param.Value = dteStart
Param.Direction = adParamInput
CMD.Parameters.Append Param

Debug.Print "Pricing_Calc_DialogFrm, cmdOK: dteStart=" & Param.Value & "|"

Set Param = New ADODB.Parameter
Param.Name = "End"
Param.Type = adDBTimeStamp
Param.Value = dteEnd
Param.Direction = adParamInput
CMD.Parameters.Append Param

Debug.Print "Pricing_Calc_DialogFrm, cmdOK: dteEnd=" & Param.Value & "|"

Set Param = New ADODB.Parameter
Param.Name = "Username"
Param.Type = adVarChar
Param.Size = 15
Param.Value = strUserID
Param.Direction = adParamInput
CMD.Parameters.Append Param

Debug.Print "Pricing_Calc_DialogFrm, cmdOK: strUserID=" & Param.Value & "|"

Set Param = New ADODB.Parameter
Param.Name = "LocContract"
Param.Type = adVarChar
Param.Size = 20
Param.Value = strLocContract
Param.Direction = adParamInput
CMD.Parameters.Append Param

Debug.Print "Pricing_Calc_DialogFrm, cmdOK: strLocContract=" & Param.Value & "|"

Set Param = New ADODB.Parameter
Param.Name = "Trans"
Param.Type = adInteger
Param.Value = intTrans
Param.Direction = adParamInput
CMD.Parameters.Append Param

Debug.Print "Pricing_Calc_DialogFrm, cmdOK: intTrans=" & Param.Value & "|"

Set Param = New ADODB.Parameter
Param.Name = "RunID"
Param.Type = adDecimal
Param.Precision = 18
Param.NumericScale = 4
Param.Value = varRunID
Param.Direction = adParamInput
CMD.Parameters.Append Param

Debug.Print "Pricing_Calc_DialogFrm, cmdOK: varRunID=" & Param.Value & "|"

Set Param = New ADODB.Parameter
Param.Name = "Revision_Flag"
Param.Type = adVarChar
Param.Size = 1
Param.Value = "N"
Param.Direction = adParamInput
CMD.Parameters.Append Param

Debug.Print "Pricing_Calc_DialogFrm, cmdOK: Revision_Flag=" & Param.Value & "|"

CMD.Execute

Me!txtMessage = "Calculating ... Pricing_CalcReport_sp."
DoCmd.Hourglass True

Me.txtMessage.Requery
Me.Repaint
Me.Requery

CMD.CommandText = "Pricing_CalcReport_sp"
CMD.CommandTimeout = 0
CMD.Execute

strRevisionFlag = "N"

strScreen = "Pricing_Calc_Rpt"
strScreenReturn = "Pricing_Calc_DialogFrm"
DoCmd.Close acForm, "Pricing_Calc_DialogFrm", acSaveNo
DoCmd.OpenReport "Pricing_Calc_Rpt", acViewPreview
DoCmd.Maximize


The stored procedure parameters are as follows:

CREATE procedure dbo.Pricing_Calc_sp
(
@Start datetime = '2/1/2007',
@End datetime = '2/28/2007',
@Username varchar(15) = 'Randy Harding',
@LocContract varchar(20) = 'NG-50071-017',
@Trans int = 7,
@RunID decimal(18,4) = 9994599.9998,
@Revision_Flag varchar(1) = 'N'
)
AS

 

[PWise]

what happands when you chagee teh type for start and end from adDBTimeStamp to adDate

 

[vbajock]

Four connection, NGMSConnect
, are you using the SQL OLeDB connection string?

 

[HardingR2000]

The result is the same.

 

[HardingR2000]

I am using a SQLOLEDB connection.

Provider='SQLOLEDB';Data Source='SVR-NATGAS';Initial Catalog='NGMSData';Integrated Security='SSPI';

 

[vbajock]

I think pwise is on to it, that timestamp data type may be your problem, you can't assign a value to a timestamp object. Why can't you just declare it as a datetime parameter?

 

[HardingR2000]

Within the VBA code I can use adDBTimeStamp or aDate. I have tried both and get the same results.

I am beginning to think this is a data issue and not a programming issue.

I am going to put this on hold until I can get time to check out the data.

Thanks for everyone's suggestions.