E
Eric
I am converting and existing MDB file to a MDB project. Most of the work is
complete. I have run into a problem. I need to execute a stored procedure
and display the results in a dataview. The function OpenStoredProcedure does
exactly that, except for one minor problem. It doesn't support parameters.
So what to do? I have created a new table called AdHocQueries that contains
the user's username, the stored proc to run, and room for two arguments; a
new stored procedure that inserts records into the table; and a stored
procedure that deletes the record. So now the code looks like this:
AddAdHocQuery( UserName, StoredProc, Param1, Param2)
OpenStoredProcedure "AdHocQuery"
DeleteAdHocQuery( UserName )
For testing purposes the stored proc AdHocQuery has the code:
SET NOCOUNT ON;
EXEC MyStoredProc 123, 'ABC'
This works like a charm. The dataview is opened just like you would expect.
So now I modify the stored proc AdHocQuery to read from the table and this
is the code.
SET NOCOUNT ON
DECLARE @UserName varchar(50)
DECLARE @JobName varchar(50)
DECLARE @Param1 varchar(50)
DECLARE @Param2 varchar(50)
DECLARE @cmd varchar(8000)
SELECT @UserName = user
SET @UserName = substring(@UserName,charindex('\',@UserName)+1,99)
SELECT @JobName=JobName, @Param1=Param1, @Param2=Param2 FROM AdHoc WHERE
UserName=@UserName
SET @cmd = @JobName + '''' + @Param1 + ''', ''' + @Param2 + ''''
EXEC( @cmd )
Now whenever the OpenStoredProcedure statement is executed I get the error
message "The stored procedure executed successfully but did not return any
records."
Basically I need the dataview to show the recordset returned by executing
another stored procedure. It appears that having the first two select
statements in the stored proc is confusing the crap out of Access.
Any ideas?
complete. I have run into a problem. I need to execute a stored procedure
and display the results in a dataview. The function OpenStoredProcedure does
exactly that, except for one minor problem. It doesn't support parameters.
So what to do? I have created a new table called AdHocQueries that contains
the user's username, the stored proc to run, and room for two arguments; a
new stored procedure that inserts records into the table; and a stored
procedure that deletes the record. So now the code looks like this:
AddAdHocQuery( UserName, StoredProc, Param1, Param2)
OpenStoredProcedure "AdHocQuery"
DeleteAdHocQuery( UserName )
For testing purposes the stored proc AdHocQuery has the code:
SET NOCOUNT ON;
EXEC MyStoredProc 123, 'ABC'
This works like a charm. The dataview is opened just like you would expect.
So now I modify the stored proc AdHocQuery to read from the table and this
is the code.
SET NOCOUNT ON
DECLARE @UserName varchar(50)
DECLARE @JobName varchar(50)
DECLARE @Param1 varchar(50)
DECLARE @Param2 varchar(50)
DECLARE @cmd varchar(8000)
SELECT @UserName = user
SET @UserName = substring(@UserName,charindex('\',@UserName)+1,99)
SELECT @JobName=JobName, @Param1=Param1, @Param2=Param2 FROM AdHoc WHERE
UserName=@UserName
SET @cmd = @JobName + '''' + @Param1 + ''', ''' + @Param2 + ''''
EXEC( @cmd )
Now whenever the OpenStoredProcedure statement is executed I get the error
message "The stored procedure executed successfully but did not return any
records."
Basically I need the dataview to show the recordset returned by executing
another stored procedure. It appears that having the first two select
statements in the stored proc is confusing the crap out of Access.
Any ideas?