Solved

Could not Find Store Procedure

Posted on 2009-07-12
15
265 Views
Last Modified: 2012-08-13
I have created a stored procedure with the code below, and tried to use it in, but i have the error message "could not find store procedure UpdateRetailDBSO".

create storeprodure
 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO
 

CREATE PROCEDURE UpdateRetailDBSO 
 

AS

BEGIN

	SET NOCOUNT ON;

END

GO
 
 

Alter Stored Procedure
 

USE [RetailDB]

GO
 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO
 

ALTER PROCEDURE [dbo].[UpdateRetailDBSO]

	

	@sale_id nvarchar(50)
 

AS
 

   UPDATE ONEU_Sales_Order
 

SET 

	  

	sale_id = @sale_id
 

.net sql
 

 try

                      {
 

                          SqlCommand cmdSQL = new SqlCommand("UpdateRetailDBSO", sqlsourceconnExtract);

                          cmdSQL.CommandType = CommandType.StoredProcedure;

                          cmdSQL.Connection = sqlsourceconnExtract;
 

                          SqlParameter parameterdesc = new SqlParameter("@sale_id", SqlDbType.NVarChar, 50);

                          parameterdesc.Direction = ParameterDirection.Input;

                          cmdSQL.Parameters.AddWithValue("@sale_id", 1);
 

                          try

                          {

                              sqlsourceconnExtract.Open();

                              cmdSQL.ExecuteNonQuery();

                          

                          }

                          catch (Exception ex)

                          {

                              lstViewLog.Items.Add(ex.Message.ToString());

                          }
 
 

                      }

                      catch (Exception ex)

                      {

                          lstViewLog.Items.Add(ex.Message.ToString());

                      }

                      finally

                      {

                          lstViewLog.Items.Add("completed");

                          sqlsourceconnExtract.Close();

                      }

Open in new window

0
Comment
Question by:doramail05
  • 6
  • 5
  • 2
  • +2
15 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24837383
may be u created on a different database / schema


CREATE PROCEDURE dbo.UpdateRetailDBSO
 
AS
BEGIN
      SET NOCOUNT ON;
END
GO
 
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24837389
Have you compiled that in your SSMS window by hitting F5 on the query window.
You need to compile the view so that you can use it later on.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24837411
I agree with both the opinion above, in short, I have strong doubts that either it is belongs to different schema or it doesn't exist. can you please run following query, which will show you whether SP is exist or not?

select * from sys.procedures where name='UpdateRetailDBSO'
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24837443
and below query will show you all stored procedure with it's schema name. have a look

select ss.name + '.' + sp.name from sys.procedures as sp join sys.schemas ss on sp.schema_id=ss.schema_id

you can filter it with your specific SP

select ss.name + '.' + sp.name from sys.procedures as sp join sys.schemas ss on sp.schema_id=ss.schema_id where sp.name='UpdateRetailDBSO'
0
 
LVL 1

Author Comment

by:doramail05
ID: 24837497
select * from sys.procedures where name='UpdateRetailDBSO'

found stored procedure in sys.procedures
0
 
LVL 1

Author Comment

by:doramail05
ID: 24837514
with this
select * from sys.procedures where name='UpdateRetailDBSO'

the stored procedure is found

and i tried this

CREATE PROCEDURE dbo.UpdateRetailDBSO2
 
AS
BEGIN
      SET NOCOUNT ON;
END
GO

still could not find
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24837540
>> still could not find

I didn't get what you meant by this. Can you explain a little bit more on this
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24837584
please run this query and show us the results:


select ss.name + '.' + sp.name from sys.procedures as sp join sys.schemas ss on sp.schema_id=ss.schema_id where sp.name='UpdateRetailDBSO'
0
 
LVL 1

Author Comment

by:doramail05
ID: 24837601
select ss.name + '.' + sp.name from sys.procedures as sp join sys.schemas ss on sp.schema_id=ss.schema_id where sp.name='UpdateRetailDBSO'

that returns dbo.UpdateRetailDBSO, in SSMS

that 'could not find' refers to the try catch exception in the code that uses this storedprocedure
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24837615
why don't you use like this:

SqlCommand cmdSQL = new SqlCommand("dbo.UpdateRetailDBSO", sqlsourceconnExtract);
0
 
LVL 1

Author Comment

by:doramail05
ID: 24837628
SqlCommand cmdSQL = new SqlCommand("dbo.UpdateRetailDBSO2", sqlsourceconnExtract);

added but, still has the same error : <

 = Could not find stored procedure 'dbo.UpdateRetailDBSO2'.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24837629
your stored procedure is "dbo.UpdateRetailDBSO" not "dbo.UpdateRetailDBSO2"
0
 
LVL 1

Author Comment

by:doramail05
ID: 24837666
i changed the connection string to by adding attachdbfilename

string sourceconnstringextract = "Data Source=" + txtSourceDS.Text + "; AttachDbFilename=RetailDB; uid=" + txtSourceUsername.Text + "; password=" + txtSourcePassword.Text + "; persist security info=true;";

it gives this =
An attempt to attach an auto-named database for file RetailDB failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

then i replaced with the long directory in 'attachdbfilename' , it gives like below :
Keyword not supported: 'c:\program files\microsoft sql server\mssql.1\mssql\data\retaildb.mdf; uid'.
0
 
LVL 1

Author Comment

by:doramail05
ID: 24837722
i tried these..

string sourceconnstringextract = "Data Source=IT01\\SQLEXPRESS;Integrated Security=True;Connect Timeout=30;User Instance=True;initial catalog=RetailDB; user id=sa; password=123";

the error = 'Cannot open database "RetailDB" requested by the login. The login failed.
Login failed for user 'IT01\myusername'.'

then i tried this..

string sourceconnstringextract = "Data Source=IT01\\SQLEXPRESS;AttachDbFilename=&quot;C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\RetailDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

got this error =
Keyword not supported: 'c:\program files\microsoft sql server\mssql.1\mssql\data\retaildb.mdf;integrated security'.
0
 
LVL 28

Accepted Solution

by:
strickdd earned 500 total points
ID: 24839774
1) After you create the stored procedure, verify that it exists in the database through management studio. It should appear in [DatabaseName] > Programability > Stored Procedures

2) If it exists, verify its context. It should be called "dbo.UpdateRetailDBSO"

3) If it doesn't exists here, then you created it in the wrong DB or it wasn't created at all

4) If it does exist where expected, verify your connection string (www.connectionstrings.com)

5) Once add your connection string you can make sure it is connecting to the correct DB and has access to the stored procedure by adding an SqlDataSource to a page, using the connection string as its connection, and verifying that the stored pocedure is in the list of stored procedures

6) If it doesn't appear in the list then you probably have to wrong connection string.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 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

23 Experts available now in Live!

Get 1:1 Help Now