Solved

Why can't SQL Server 2008 find a stored procedure that exists?

Posted on 2010-08-28
19
627 Views
Last Modified: 2012-05-10
I have a stored procedure that returns an error message:

     "Msg 2812, Level 16, State 62, Procedure usp_SearchResults, Line 22
      Could not find stored procedure. ''

The procedure exists in the correct database.  As far as I can tell the problem seems to be on the line:

     EXEC @strSQL

If I change it to :

     EXEC (@strSQL)

Then an empty recordset is returned.

Please help.
USE [Data]

GO

/****** Object:  StoredProcedure [dbo].[usp_SearchResults]    Script Date: 08/28/2010 20:16:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- Batch submitted through debugger: SQLQuery19.sql|7|0|C:\Users\Administrator\AppData\Local\Temp\1\~vsD524.sql

-- =============================================

-- Author:		Rob

-- Create date: 8/24/2010

-- Description:	Returns results of PN search.

-- =============================================

ALTER PROCEDURE [dbo].[usp_SearchResults] 

	-- Add the parameters for the stored procedure here

	@WHEREStatement nvarchar(1000) = NULL

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;



    -- Insert statements for procedure here

	DECLARE @strSQL nvarchar(1000)

    

	SET @strSQL = 'SELECT Item_NUMBER, DESCRIPTION, ProdLine, DOCUMENT_TYPE 

				   FROM tblItem ' + @WhereStatement 

			

	EXEC @strSQL

END

Open in new window

0
Comment
Question by:NevSoFly
  • 9
  • 4
  • 2
  • +3
19 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33551633
Since it is SQL Server 2008, I would recommend using

exec sp_executesql @strSQL
instead of EXEC @strSQL

Make sure that
1. You are connected to the correct database in SSMS
2. Try using CREATE PROCEDURE [dbo].[usp_SearchResults]
instead of ALTER PROCEDURE [dbo].[usp_SearchResults]
0
 

Author Comment

by:NevSoFly
ID: 33551694
I did as you suggested but got the below error in return.

     "Msg 217, Level 16, State 1, Procedure usp_SearchResults, Line 21
      Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33551891
Are you sure that your Stored procedure code is exactly the one you posted above..
For SELECT statements you should not receive the error posted above..

And do you have any triggers on the table tblItem
0
 

Author Comment

by:NevSoFly
ID: 33552509
Here is the exact code I have for the SP and the trigger that is on tblItem


SP code:



USE [Data]

GO

/****** Object:  StoredProcedure [dbo].[usp_SearchResults]    Script Date: 08/28/2010 21:32:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:		Robert Nevins

-- Create date: 8/24/2010

-- Description:	Returns results of PN search.

-- =============================================

ALTER PROCEDURE [dbo].[usp_SearchResults] 

	-- Add the parameters for the stored procedure here

	@WHEREStatement nvarchar(1000) = NULL

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;



    -- Insert statements for procedure here

	DECLARE @strSQL nvarchar(1000)

    

	SET @strSQL = 'SELECT Item_NUMBER, DESCRIPTION, ProdLine, DOCUMENT_TYPE 

				   FROM tblItem ' + @WhereStatement 

			

	EXEC usp_SearchResults @strSQL

END





trigger on tblItem:



USE [Data]

GO

/****** Object:  Trigger [dbo].[tr_tblItem_Update]    Script Date: 08/29/2010 03:26:22 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:		Robert Nevins

-- Create date: 8/24/2010

-- Description:	updates the DateStamp field to the current date whenever a record is added or any data in a record is modified.

-- =============================================

ALTER TRIGGER [dbo].[tr_tblItem_Update] 

   ON  [dbo].[tblItem] 

   AFTER INSERT,UPDATE

AS 

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;



    -- Insert statements for trigger here

    UPDATE tblItem SET DateStamp = getdate() 

    FROM tblItem, inserted i 

    WHERE tblItem.Item_Number = i.Item_Number

   /*AND x.othercolums = i.othercolumns*/

   

   UPDATE tblSynched SET tblsynched.tblItem = GETDATE()

   FROM tblSynched

END

Open in new window

0
 
LVL 1

Expert Comment

by:JensMig
ID: 33553924
You recall your own stored procedure indefinately.
Line 30: EXEC usp_SearchResults @strSQL


0
 

Author Comment

by:NevSoFly
ID: 33554096
thanks, I fixed that by removing  usp_SearchResults so it now reads 'EXEC @strSQL' but I am back to my original problem.
0
 

Author Comment

by:NevSoFly
ID: 33555314
Maybe we should start from the begining.

What I need is a stored procedure that will allow me to have a dynamic WHERE clause.
0
 
LVL 2

Expert Comment

by:marat-oz
ID: 33556409
Let's try Debugging
add print after
SET @strSQL = 'SELECT Item_NUMBER, DESCRIPTION, ProdLine, DOCUMENT_TYPE
                           FROM tblItem ' + @WhereStatement
as follows
print @strSQL
What do you get?

I guess you have CONCAT_NULL_YIELDS_NULL  ON

Try to add the following code into your procedure, at the top
SET CONCAT_NULL_YIELDS_NULL OFF

or
set default value to empty string (which I prefer)

@WHEREStatement nvarchar(1000) = ''


0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 400 total points
ID: 33556762
Dynamic SQL is run using

EXEC (@strSQL)

Brackets are NOT optional.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 400 total points
ID: 33556776
Now that that is sorted, the other problem is that the default for @WHERESTATEMENT is NULL.
When you add NULL to a string, the entire string becomes null, unless non-standard SET settings are used.

Try this

------

ALTER PROCEDURE [dbo].[usp_SearchResults]
      -- Add the parameters for the stored procedure here
      @WHEREStatement nvarchar(1000) = NULL
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      DECLARE @strSQL nvarchar(1000)
   
      SET @strSQL = 'SELECT Item_NUMBER, DESCRIPTION, ProdLine, DOCUMENT_TYPE
                           FROM tblItem ' + IsNull(@WhereStatement , '')
                  
      EXEC (@strSQL)
END
0
 
LVL 14

Expert Comment

by:wolfman007
ID: 33556796
I think the problem could be that you have called your database [Data], which is listed as a future key word in the following website.

Reserved Keywords (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms189822.aspx

You should try and avoid using key words when naming your databases of tables.

Temporarily rename your database to see if it fixes the problem.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33558088
Few comments for the code attached in comment: 33552509

Replace EXEC usp_SearchResults @strSQL
with EXEC sp_executesql @strSQL

And I don't see any practical usage of the trigger from the application point of view. Its adding addtional overhead to your table.
1. You can achieve this operation
    UPDATE tblItem SET DateStamp = getdate()
    FROM tblItem, inserted i
    WHERE tblItem.Item_Number = i.Item_Number
by using a default constraint with value as getdate() for Datestamp column

2. May I know how many records this UPDATE would be modifying

   UPDATE tblSynched SET tblsynched.tblItem = GETDATE()
   FROM tblSynched

second one can impact your query performance and as I mentioned above, the actual reason for failure may be some other.
And kindly confirm whether tblItem is a table or view.
0
 

Author Comment

by:NevSoFly
ID: 33559131
marat-0z,

This is the result:
   SELECT Item_NUMBER, DESCRIPTION, ProdLine, DOCUMENT_TYPE
   FROM tblItem WHERE item_Number=100000-1

This gives me the same error.

When I set @WHEREStatement = "" it tells me that aliases defined as "" or [] are not allowed.
When I tried 'SET CONCAT_NULL_YIELDS_NULL OFF' I got"
   'Msg 2812, Level 16, State 62, Procedure usp_SearchResults, Line 21
Could not find stored procedure 'SELECT Item_NUMBER, DESCRIPTION, ProdLine, DOCUMENT_TYPE
                   FROM tblItem WHERE item_number = 100000-1'.'
0
 

Author Comment

by:NevSoFly
ID: 33559174
cyberkiwi,

I tried the EXEC (@strSQL) before and it didn't work.  I don't know what was different this time but I got a different error 'can't make conversion from nvarchar to int' (or something simular).  I changed the code from WHERE item_number = 100000-1 to WHERE item_number = '100000-1' and it worked.

Also combining it with the ISNULL change got me the desired results incase I didn't have a WHERE clause.

Thank you.
0
 

Author Comment

by:NevSoFly
ID: 33559189
wolfman007,

that didn't solve my problem but your right, I should do it thanks.
0
 

Author Comment

by:NevSoFly
ID: 33559351
rrjegan17,

I tested your suggestion yet but to answer some of your questions.

1.  The trigger has nothing to do with this query.  I was asked if there was a trigger on the table so I supplied
     the code.  The trigger was ment only to keep track of when the table was updated and when a specific
     record on that table was updated or inserted.

     Will using a default constraint with GETDATE() as the value work for updating a record as well as
     inserting a new record?

2.  It only updates one record and tblItem is a table.  
0
 
LVL 2

Assisted Solution

by:marat-oz
marat-oz earned 100 total points
ID: 33563326
Hi NevSoFly,
I created your SP and tested it, it runs fine, returning records when it supose to.
Please analyse and run below code; let me know of results
ALTER PROCEDURE [dbo].[usp_SearchResults]
      -- Add the parameters for the stored procedure here
      @WHEREStatement nvarchar(1000) = ''
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      DECLARE @strSQL nvarchar(1000)
   
      SET @strSQL = 'SELECT Item_NUMBER, DESCRIPTION, ProdLine, DOCUMENT_TYPE
                           FROM tblItem ' + @WhereStatement
                  
      EXEC (@strSQL)
END

GO

exec [dbo].[usp_SearchResults] 'WHERE item_Number=''100000-1'''
exec [dbo].[usp_SearchResults] 'WHERE item_Number=''Hello'''
exec [dbo].[usp_SearchResults] ''
exec [dbo].[usp_SearchResults]
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33564625
>> Will using a default constraint with GETDATE() as the value work for updating a record as well as
     inserting a new record?

I assume, updates can happen in the table only via application or through some stored procedures. In that case, you can include this column in UPDATE clause to avoid the need of a trigger.
0
 

Author Closing Comment

by:NevSoFly
ID: 33566029
Thank you all for your assistance.  I think what really confused me on this problem was that the error message didn't seem to match the actual error.  Agian thank you all.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now