The stored procedure executed successfully but did not return any records

[mmcardle]

I have an ADP linked to a SQL Server backend and one thing I have set up is
for Access to run a stored procedure on SQL Server when the user opens up a
form. The stored procedure runs a DTS job that imports data from an Informix
database into a table in SQL Server.

It all works perfectly, apart from the above message appearing in a dialog
box in Access. I don't want any message returning, unless there is an error.

My stored procedure is this:

ALTER PROCEDURE sp_xDOCgetsolist
AS
SET NOCOUNT ON
EXECUTE master..xp_cmdshell "dtsrun /Sdata-warehouse /E /NxDOCso", NO_OUTPUT

Any lovely ideas out there?

Regards,

Mark

 

[Danny Seager]

how are you executing this stored procedure?

 

[mmcardle via AccessMonster.com]

how are you executing this stored procedure?

Hi Danny,

Thank you for responding.

I'm executing it from a control event. When the use clicks a button, this
code runs:

' Define variable to hold sp name
Dim stDocName As String

' Assign sp name and execute
stDocName = "sp_xDOCgetsolist" ' sp goes to Strategix and gets list of
current SOs
DoCmd.OpenStoredProcedure stDocName, acViewNormal, acEdit

Cheers,

Mark

 

[Tom van Stiphout]

You didn't set this sproc as the RecordSource for the form, right?
Rather you would typically use ADO's Execute method to run the sproc:
Application.CurrentProject.Connection.Execute "sp_xDOCgetsolist"

Btw, you know that the "sp_" prefix is frowned upon?

-Tom.

 

[Sylvain Lafontaine]

The DoCmd.OpenStoredProcedure is provided to open the resultset returned by
a SP in a datasheet window. As you don't have any resultset returned by
this SP, you should use CurrentProject.Connection.Execute instead:

CurrentProject.Connection.Execute "sp_xDOCgetsolist", , adCmdStoredProc And
adExecuteNoRecords

See http://www.devguru.com/technologies/ado/quickref/connection_execute.html
.. Also, you shouldn't use the prefix « sp_ » as it has a special meaning
for SQL-Server and could lead to some subtil bug hard to find.