Hi all,
need some help with stored procedures. I wrote a procedure with parameters which builds up a sql-statement in a variable. The last statement is "EXEC (SQL)". Via SQL Query Analyzer results will be shown. If I execute this procedure via Access I get a message like "stored procedure executed successfully but didn't return records". The SQL server I use is MS SQL version 7.

Do you have any idea resolving this problem?

Thx

Here's the coding of the stored procedure:

CREATE PROCEDURE dbo.spEDIMSucheSAPLaender
   @TOP integer,
   @LAND1 nvarchar(255),
   @INTCA nvarchar(255),
   @INTCA3 nvarchar(255),
   @LANDX nvarchar(255)
AS
   DECLARE @SQL varchar(8000)

   SET @SQL = 'SELECT * FROM tblSAPLaender WHERE 1=1'
   IF @LAND1 <> ''
      SET @SQL = @SQL + ' AND LAND1 LIKE ''' + @LAND1 + ''''

   IF @INTCA <> ''
      SET @SQL = @SQL + ' AND INTCA LIKE ''' + @INTCA + ''''

   IF @INTCA3 <> ''
      SET @SQL = @SQL + ' AND INTCA3 LIKE ''' + @INTCA3 + ''''

   IF @LANDX <> ''
      SET @SQL = @SQL + ' AND LANDX LIKE ''' + @LANDX + ''''

   IF @TOP <> ''
      SET @SQL = REPLACE(@SQL, 'SELECT', 'SELECT TOP ' +
         CONVERT(varchar(10),@Top))

   EXEC (@SQL)
GO

When you call from Query Analyzer- are you using the StoredProc or are you entering the actual SQL statement?

If your not using the SP then your not actually testing the SP.. you could have a problem with your building of the SQL statement-

I would return @SQL out of the SP, rather than execute it, and see if the statement that is built is what I expected.


Hal Levy
Please do your own homework.
I am here to help you, not do it for you.
I do not have sample code for anything

I'm having the same issue. SP runs as expected but no resutls returned.

My sp is migrating data between tables, has cursors in it and takes around 5 seconds to run. It has returned results once from access (when there was nothing to migrate and hence never looped the cursors) and it does work from access on other (much simpler) Sps.

Any ideas?

Try replacing
EXEC(SQL)

with EXEC SP_EXECUTESQL @SQL

and change the type of @SQL to 'nvarchar'

If you run a profiler trace on the calls that access makes to SQL Server you will typically see something like this
SET FMTOnly ON
EXEC yourproc
SET FTMOnly OFF
This asks your stored procedure to return just the framework of your result set (the metadata). Try running this in QA and see what you get.

Additionally, Access likes Procs that return only one result set, and one that is consistent (no if this then select 5 columns else select 4 columns). So remember to set nocount on in your procs and to have one result set returned.

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

Tried setting nocount on and it worked. Many thanks.

Strange thing is though it did work when I had no data. In that case the more complex selects inside my cursors and IFs would have been skipped, however it would still have run several selects throughout albeit much simpler ones.

Also tried FMTOnly ON and that did just return the framework, but I wasn't even getting that far in access. Will definately be checking out profiler in future though, thanks again.