[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

exec stored procedure returns nothing

Posted on 2009-02-12
8
Medium Priority
?
743 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?
0
Comment
Question by:stee1hed
  • 4
  • 3
8 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 23623449
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
 
LVL 4

Expert Comment

by:Igor-K
ID: 23623487
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
 

Author Comment

by:stee1hed
ID: 23623858
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Accepted Solution

by:
Igor-K earned 2000 total points
ID: 23624032
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
 
LVL 4

Expert Comment

by:Igor-K
ID: 23624068
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
 

Author Comment

by:stee1hed
ID: 23624075
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
 

Author Closing Comment

by:stee1hed
ID: 31546114
Profiler was the key!
0
 

Author Comment

by:stee1hed
ID: 23625137
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question