the procedure executed successfully but did not return records

[Barry George]

I am new to sqlServer and am doing an application front end using Access
2003 and the backend sqlServerExpress 2005.

When I run a very simple Stored Procedure on a table called 'MiscCharges'
such as;

SET NOCOUNT ON
UPDATE MiscCharges SET Description='New Description'
WHERE Joborder='1000'

I get the following message: "the procedure executed successfully but did
not return records"

This happens regardless of how I run the SP. Either in the SP window or from
VBA code.

I don't get this message with a simple SELECT sql statement.

What am I doing wrong?

 

[Bob]

Try this Barry...

Dim oCmd As Command, param As Parameter
Dim cn As New ADODB.Connection, sqlString As String

sqlString = "YourStoredProc"
Set oCmd = New ADODB.Command
Set cn = CurrentProject.Connection
Set oCmd.ActiveConnection = cn
oCmd.CommandText = sqlString
oCmd.CommandType = adCmdStoredProc
oCmd.CommandTimeout = 15

oCmd.Execute , , adExecuteNoRecords
cn.close
set cn = nothing

hth.. bob

 

[Barry George]

Thank you Bob. I will give that a try.

BG

Try this Barry...

Dim oCmd As Command, param As Parameter
Dim cn As New ADODB.Connection, sqlString As String

sqlString = "YourStoredProc"
Set oCmd = New ADODB.Command
Set cn = CurrentProject.Connection
Set oCmd.ActiveConnection = cn
oCmd.CommandText = sqlString
oCmd.CommandType = adCmdStoredProc
oCmd.CommandTimeout = 15

oCmd.Execute , , adExecuteNoRecords
cn.close
set cn = nothing

hth.. bob

 

[Barry George]

Hi Bob,

Yes, everything is working much better now.

Many Thanks,

Barry

 

[Robert Morley]

Just to clarify, you're not doing anything wrong. Bob's post is a good way around the behaviour, but what you're experiencing is
exactly the expected behaviour.

When you run a SELECT query, it returns records, so there's no need to inform you of its success. When you run something like an
INSERT, UPDATE or DELETE query (also called Action Queries in MDBs), Access is nice and informs you of its success. The message
you're getting isn't an error, it's simply an acknowledgement that everything went as expected, but that you weren't running a
SELECT query, so it has nothing to show you.



Rob

 

[Barry George]

Thanks for the Info Rob.

Barry

Just to clarify, you're not doing anything wrong. Bob's post is a good

way around the behaviour, but what you're experiencing is

exactly the expected behaviour.

When you run a SELECT query, it returns records, so there's no need to

inform you of its success. When you run something like an

INSERT, UPDATE or DELETE query (also called Action Queries in MDBs),

Access is nice and informs you of its success. The message

you're getting isn't an error, it's simply an acknowledgement that

everything went as expected, but that you weren't running a