We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

exec stored procedure returns nothing

stee1hed
stee1hed asked
on
Medium Priority
757 Views
Last Modified: 2012-05-06
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?
Comment
Watch Question

Commented:
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

Commented:
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.

Author

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.
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
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

Author

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

Author

Commented:
Profiler was the key!

Author

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!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.