SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Could not find stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stefano
Starting Member

10 Posts

Posted - 04/29/2008 :  07:44:00  Show Profile  Reply with Quote
Not sure if this question belongs here or in a .NET forum.
But Im going to give it a shoot. The problem is that Im getting the following error: "Could not find stored procedure 'xxx'".

Ive never used stored procedures before, so what I am wondering is there anything basic that Im forgetting?

I have this simple stored Procedure:

ALTER PROCEDURE Person_info
@FirstN varchar(128),
@LastN varchar(128)
AS
SELECT FName, LName
FROM Person
WHERE (FName = @FirstN) AND (LName = @LastN)

and each time I call this procedure I get the prior stated error.

returnValue = sqlcmd.ExecuteReader(); //crashes when this line executes.

Ive found some people talking about this and it might be caused due to the "initial catalog=<database name>" in the connection string is missing. I tried that but didnt work.

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/29/2008 :  07:49:12  Show Profile  Reply with Quote
The error simply says it cant find the specified stored procedure in the database. the reasons may be many. Like you dont have sp created on database or connection string is wrong,etc.
As a start can you try executing the Stored procedure directly in Query Analyser by passing parameter values?
Go to Top of Page

stefano
Starting Member

10 Posts

Posted - 04/29/2008 :  08:08:14  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

The error simply says it cant find the specified stored procedure in the database. the reasons may be many. Like you dont have sp created on database or connection string is wrong,etc.
As a start can you try executing the Stored procedure directly in Query Analyser by passing parameter values?



I have Microsoft sql server 2005 installed and Microsoft Web developer Express edition. I could not see that I have the query analyser under sql server 2005 ?

I have the stored procedure and I have been playing around with the connection string. The connection string works fine, but now I changing the sql functions to stored procedures.

This is the following connection string:
<add name="dbN" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\...\App_Data\mydb.mdf;Initial Catalog=dbname;Integrated Security=SSPI" providerName="System.Data.SqlClient" />

Edited by - stefano on 04/29/2008 10:04:37
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/29/2008 :  08:09:29  Show Profile  Reply with Quote
quote:
Originally posted by stefano

quote:
Originally posted by visakh16

The error simply says it cant find the specified stored procedure in the database. the reasons may be many. Like you dont have sp created on database or connection string is wrong,etc.
As a start can you try executing the Stored procedure directly in Query Analyser by passing parameter values?



I have Microsoft sql server 2005 installed and Microsoft Web developer Express edition. I could not see that I have the query analyser under sql server 2005 ?

I have the stored procedure and I have been playing around with the connection string. The connection string works fine, but now I changing the sql functions to stored procedures.

This is the following connection string:
<add name="dbN" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\...\App_Data\Lyngsoe.mdf;Initial Catalog=dbname;Integrated Security=SSPI" providerName="System.Data.SqlClient" />



Ok if its sql 2005 can you try executing this stored procedure in sql server management studio?
Go to Top of Page

stefano
Starting Member

10 Posts

Posted - 04/29/2008 :  08:19:31  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by stefano

quote:
Originally posted by visakh16

The error simply says it cant find the specified stored procedure in the database. the reasons may be many. Like you dont have sp created on database or connection string is wrong,etc.
As a start can you try executing the Stored procedure directly in Query Analyser by passing parameter values?



I have Microsoft sql server 2005 installed and Microsoft Web developer Express edition. I could not see that I have the query analyser under sql server 2005 ?

I have the stored procedure and I have been playing around with the connection string. The connection string works fine, but now I changing the sql functions to stored procedures.

This is the following connection string:
<add name="dbN" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\...\App_Data\Lyngsoe.mdf;Initial Catalog=dbname;Integrated Security=SSPI" providerName="System.Data.SqlClient" />



Ok if its sql 2005 can you try executing this stored procedure in sql server management studio?



Yes, When I open the database with sql server management studio I get the overview, Database diagrams, tables and view, etc. But cant see the stored procedures. I found stored procedures under Programmability and there are a lot of procedures (which I didnt create) but I cant see my function ?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 04/29/2008 :  08:25:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Are you trying to attach the database file every time you query the database server?

Have a look at www.connectionstrings.com



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

stefano
Starting Member

10 Posts

Posted - 04/29/2008 :  08:26:21  Show Profile  Reply with Quote
quote:
Originally posted by stefano

quote:
Originally posted by visakh16

quote:
Originally posted by stefano

quote:
Originally posted by visakh16

The error simply says it cant find the specified stored procedure in the database. the reasons may be many. Like you dont have sp created on database or connection string is wrong,etc.
As a start can you try executing the Stored procedure directly in Query Analyser by passing parameter values?



I have Microsoft sql server 2005 installed and Microsoft Web developer Express edition. I could not see that I have the query analyser under sql server 2005 ?

I have the stored procedure and I have been playing around with the connection string. The connection string works fine, but now I changing the sql functions to stored procedures.

This is the following connection string:
<add name="dbN" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\...\App_Data\Lyngsoe.mdf;Initial Catalog=dbname;Integrated Security=SSPI" providerName="System.Data.SqlClient" />



Ok if its sql 2005 can you try executing this stored procedure in sql server management studio?



Yes, When I open the database with sql server management studio I get the overview, Database diagrams, tables and view, etc. But cant see the stored procedures. I found stored procedures under Programmability and there are a lot of procedures (which I didnt create) but I cant see my function ?



sorry, these stored procedures that I found where under system stored procedures.
But I saw that I was missing dbo. infront of my function name definition.

ALTER PROCEDURE dbo.getSkills_info. I changed that I refreshed the management studio.. but still cant see my function.
Go to Top of Page

stefano
Starting Member

10 Posts

Posted - 04/29/2008 :  08:27:15  Show Profile  Reply with Quote
quote:
Originally posted by Peso

Are you trying to attach the database file every time you query the database server?

Have a look at www.connectionstrings.com



E 12°55'05.25"
N 56°04'39.16"




But shouldnt I be able to see the function in the management studio?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 04/29/2008 :  08:33:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Are you the owner of the function or stored procedure?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

stefano
Starting Member

10 Posts

Posted - 04/29/2008 :  08:35:27  Show Profile  Reply with Quote
quote:
Originally posted by Peso

Are you trying to attach the database file every time you query the database server?

Have a look at www.connectionstrings.com



E 12°55'05.25"
N 56°04'39.16"




Correct me if Im wrong.

I would think that If you are successfully able to connect to the database by hard coding the sql statement you should be able to connect to the database with the same connection string even though your are calling a stored procedure.

Therefor Im pretty sure that my connection string is good. Except if the store procedure require some extra information in the string ?
Go to Top of Page

stefano
Starting Member

10 Posts

Posted - 04/29/2008 :  08:37:00  Show Profile  Reply with Quote
quote:
Originally posted by Peso

Are you the owner of the function or stored procedure?



E 12°55'05.25"
N 56°04'39.16"




I created the function, and have a full access to this machine.
But is the owner of the function defined somewhere? if so, where can I find those information?
Go to Top of Page

stefano
Starting Member

10 Posts

Posted - 04/29/2008 :  08:55:26  Show Profile  Reply with Quote
Ive done endless of changes to my connection string
Gone through a lot of tutorials and examples on how to create and call a stored procedure.

Im really dont know what I can do next.

What I would like to get answers too is:

does stored procedure require some extra information in the connection string, like Initial Catalog or database=<name>, etc.
How can I get access to my stored procedure through sql server management studio?
How can I check the owner of a stored procedure?
if you have time and knowledge .. please share :)

thank you!
Go to Top of Page

m_k_s@hotmail.com
Insecure what ??

38 Posts

Posted - 04/29/2008 :  09:19:49  Show Profile  Visit m_k_s@hotmail.com's Homepage  Reply with Quote
quote:
Originally posted by stefano

Ive done endless of changes to my connection string
Gone through a lot of tutorials and examples on how to create and call a stored procedure.

Im really dont know what I can do next.

What I would like to get answers too is:

does stored procedure require some extra information in the connection string, like Initial Catalog or database=<name>, etc.
How can I get access to my stored procedure through sql server management studio?
How can I check the owner of a stored procedure?
if you have time and knowledge .. please share :)

thank you!



1) confirm stored procedure works directly from SQL Studio
(right click and try to script an execute of it)
2) confirm your connection works to the db
(can you access other tables)
3) confirm you have your permissions setup to be able to execute sprocs for the signon in your connection string
4) make sure you know what you are trying to execute - Stored Procedure <> Function - they are called in different ways
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 04/29/2008 :  09:40:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
"Create Procedure..."

does not create a FUNCTION, so I think you are looking in the wrong folder.
Look under Programmability / Stored Procedures.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

stefano
Starting Member

10 Posts

Posted - 04/29/2008 :  09:47:01  Show Profile  Reply with Quote
quote:
Originally posted by Peso

"Create Procedure..."

does not create a FUNCTION, so I think you are looking in the wrong folder.
Look under Programmability / Stored Procedures.



E 12°55'05.25"
N 56°04'39.16"




I looked under Programmability / Stored Procedures and it is empty.
I also have the northwind database and I can see the it contains several stored procedures. So what I did was to compare the access rights between the northwind database and the one Im working on, but as far as I can see they are identical.
Which perhaps rules out that the access restrictions are in some way causing me this Headache.

Any other ideas?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 04/29/2008 :  09:49:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What is your default database?
If you haven't set it, your default database will be master.
Check if the created procdures is stored there.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

stefano
Starting Member

10 Posts

Posted - 04/29/2008 :  09:56:06  Show Profile  Reply with Quote
quote:
Originally posted by Peso

What is your default database?
If you haven't set it, your default database will be master.
Check if the created procdures is stored there.



E 12°55'05.25"
N 56°04'39.16"




Im not sure were I can see what database is defined as default.
Although I actually did think of that, and I have 3 database defined.
System Databases
My DB
Northwind.

None of them contains the stored procedure that I have defined.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 04/29/2008 :  10:05:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Run this query to see if you have the SP stored anywhere in the db
EXEC sp_msforeachdb '
select * from ?.information_schema.routines
where ''Person_info'' in (specific_name, routine_name)'


Another possibility is that you have stored to procedure in the database file which you reattach with every login.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

raky
Aged Yak Warrior

767 Posts

Posted - 05/12/2008 :  02:30:55  Show Profile  Reply with Quote
Hi,

While u executing it is in alter state just replace alter with create statement then the procedure executed then u find stored procedure

Be cool...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000