Could not find stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Could not find stored procedure

Author

Topic

stefano
Starting Member

10 Posts

Posted - 2008-04-29 : 07:44:00

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

52326 Posts

Posted - 2008-04-29 : 07:49:12

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?

stefano
Starting Member

10 Posts

Posted - 2008-04-29 : 08:08:14

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" />

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-29 : 08:09:29

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?

stefano
Starting Member

10 Posts

Posted - 2008-04-29 : 08:19:31

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 ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-29 : 08:25:03

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"

stefano
Starting Member

10 Posts

Posted - 2008-04-29 : 08:26:21

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.

stefano
Starting Member

10 Posts

Posted - 2008-04-29 : 08:27:15

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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-29 : 08:33:38

Are you the owner of the function or stored procedure?

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

stefano
Starting Member

10 Posts

Posted - 2008-04-29 : 08:35:27

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 ?

stefano
Starting Member

10 Posts

Posted - 2008-04-29 : 08:37:00

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?

stefano
Starting Member

10 Posts

Posted - 2008-04-29 : 08:55:26

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!

m_k_s@hotmail.com
Insecure what ??

38 Posts

Posted - 2008-04-29 : 09:19:49

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-29 : 09:40:29

"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"

stefano
Starting Member

10 Posts

Posted - 2008-04-29 : 09:47:01

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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-29 : 09:49:03

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"

stefano
Starting Member

10 Posts

Posted - 2008-04-29 : 09:56:06

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-29 : 10:05:03

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"

raky
Aged Yak Warrior

767 Posts

Posted - 2008-05-12 : 02:30:55

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...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources