exec stored procedure returns nothing

Hi,
I have a very basic stored procedure
============================================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[SP1] (@PARAM1 CHAR(40))
AS
BEGIN
      DECLARE @SQL VARCHAR(4000)
      SET @SQL = 'SELECT * FROM OPENQUERY(ORCL_A, ''SELECT * FROM SCHEMA1.TABLE1 WHERE FIELD1 = '''''+  @PARAM1 + ''''' '')'
      EXEC ( @SQL )
      RETURN
END
============================

If I run the sql straight I get a row back.  If I run

exec SP1 'blah'

I get nothing back.  It doesn't error, the fields are there, but there is no record returned.

I have other similar SP's that point to other linked db's that work fine with the sp call.  I must be missing something very simple.  The permissions all look okay, public user has exec grant on the sp and the table on the linked server has grant to the user in the linked server.

What am I missing?
stee1hedAsked:
Who is Participating?
 
Igor-KConnect With a Mentor Commented:
It is strange then.  Try first outputing the string, maybe you will spot something unnoticeable.
If not, then try to run profiler, configure it to return all Errors and Warnings, all Stored Procedures, all TSQL.  Configure the filter to your SPID.  Check if all statements look like expected.  In particular you should see your exec statement there.
0
 
dqmqCommented:
Too many quotes, I think:
ALTER PROCEDURE [dbo].[SP1] (@PARAM1 CHAR(40))
AS
BEGIN 
      DECLARE @SQL VARCHAR(4000)
      SET @SQL = 'SELECT * FROM OPENQUERY(ORCL_A, ''SELECT * FROM SCHEMA1.TABLE1 WHERE FIELD1 = ''' +  @PARAM1 + ''' )'
      EXEC ( @SQL )
      RETURN
END
============================

Open in new window

0
 
Igor-KCommented:
Could you execute this procedure from Management Studio or Query Analizer and see what output will be.
The stored procedure should output a recordset but does not return any result code.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
stee1hedAuthor Commented:
dqmq: The number of quotes should be good.  It's a string within a string that appends a variable.

Igor-K: I am executing from Management Studio and the results are as described in my question.  If I take the openquery select out of the SP and execute it by itself I do get a result row.
0
 
Igor-KCommented:
Outputing the string I ment following
ALTER PROCEDURE [dbo].[SP1] (@PARAM1 CHAR(40))
AS
BEGIN 
      DECLARE @SQL VARCHAR(4000)
      SET @SQL = 'SELECT * FROM OPENQUERY(ORCL_A, ''SELECT * FROM SCHEMA1.TABLE1 WHERE FIELD1 = '''''+  @PARAM1 + ''''' '')'
      print @SQL
      EXEC ( @SQL )
      RETURN
END

Open in new window

0
 
stee1hedAuthor Commented:
fyi, executing the below in Management Studio works and returns a record
DECLARE @SQL VARCHAR(4000)
SET @SQL = 'SELECT * FROM OPENQUERY(ORCL_A, ''SELECT * FROM SHEMA1.TABLE1 WHERE FIELD1 = ''''HITHERE'''' '')'
EXEC ( @SQL )

Open in new window

0
 
stee1hedAuthor Commented:
Profiler was the key!
0
 
stee1hedAuthor Commented:
In the profiler I saw that the string variable had a bunch of spaces appended, it was defined as a CHAR(40), this was the issue.  I put a TRIM in there and it works great.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.