Could not Find Store Procedure

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

LVL 1
doramail05Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
may be u created on a different database / schema


CREATE PROCEDURE dbo.UpdateRetailDBSO
 
AS
BEGIN
      SET NOCOUNT ON;
END
GO
 
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
RiteshShahCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

RiteshShahCommented:
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
doramail05Author Commented:
select * from sys.procedures where name='UpdateRetailDBSO'

found stored procedure in sys.procedures
0
doramail05Author Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> still could not find

I didn't get what you meant by this. Can you explain a little bit more on this
0
RiteshShahCommented:
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
doramail05Author Commented:
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
RiteshShahCommented:
why don't you use like this:

SqlCommand cmdSQL = new SqlCommand("dbo.UpdateRetailDBSO", sqlsourceconnExtract);
0
doramail05Author Commented:
SqlCommand cmdSQL = new SqlCommand("dbo.UpdateRetailDBSO2", sqlsourceconnExtract);

added but, still has the same error : <

 = Could not find stored procedure 'dbo.UpdateRetailDBSO2'.
0
RiteshShahCommented:
your stored procedure is "dbo.UpdateRetailDBSO" not "dbo.UpdateRetailDBSO2"
0
doramail05Author Commented:
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
doramail05Author Commented:
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
strickddCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.