Stored procedure not showing under list

Question

Stored procedure not showing under list

daveriya

SSCrazy

Points: 2352
More actions
November 11, 2011 at 9:02 am

#262651

hi
i have created 1 stored procedure and i am executing it under same database,but i am not able to see it under stored procedure node ,any help?

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Right-click the stored procedures folder -> refresh

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

daveriya SSCrazy Points: 2352 More actions · Answer 2

daveriya

SSCrazy

Points: 2352

November 11, 2011 at 9:09 am

#1406365

i did it,still its not showing

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

daveriya (11/11/2011)
i did it,still its not showing

USE <database_name>;

GO

SELECT name AS SP_name

,SCHEMA_NAME(schema_id) AS schema_name

,type_desc

,create_date

,modify_date

FROM sys.objects

WHERE type_desc LIKE '%STORED_PROCEDURE';

GO

If you can find your SP in this list, it's SSMS refresh issue. Try to re-connect / refresh Server which is highest in object hierarchy.(Server\DB\SP folder)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

You have object explorer connected to the right server? Are you looking in the right DB? There's no connection between the server and DB that object explorer is pointing to and the one that the query window is connected to.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 5

make sure you didn't create it in the master database by mistake.

it's possible that it is in the "System Stored Procedures" folder if someone ran EXECUTE sp_ms_marksystemobject 'YourProcName' on the procs as well.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

daveriya SSCrazy Points: 2352 More actions · Answer 6

i am connected to the right server,i am able to execute it,but its not showing in the list

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 7

daveriya (11/11/2011)
i am connected to the right server,i am able to execute it,but its not showing in the list

Did you try what I suggested?

SQLback SSCrazy Points: 2897 More actions · Answer 8

What is your SQL Server version and what is your SSMS version ?

I noticed this problem if SQL Server is SQL 2008 R2 and your SSMS is older version like SQL 2005 or SQL 2008. Make sure your SSMS and SQL Server are of the same version.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

daveriya (11/11/2011)
i am connected to the right server,i am able to execute it,but its not showing in the list

Is object explorer connected to the same server and pointing at the same DB as your query window? It doesn't have to be, there's no link between the two.

Most common reason I've seen for this problem is exactly that, object explorer's connected to one server, query window to another.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

daveriya SSCrazy Points: 2352 More actions · Answer 10

no its not in the master database or under system stored procedure.Any other option,while creating it i didint use 'USE DATABASENAME'.

but i know i have selected correct database on the top.and i am able to execute and see he result

Lowell SSC Guru Points: 323518 More actions · Answer 11

daveriya (11/11/2011)
no its not in the master database or under system stored procedure.Any other option,while creating it i didint use 'USE DATABASENAME'.
but i know i have selected correct database on the top.and i am able to execute and see he result

does it start with sp_ by chance?

ok after creating the procedure, you right clicked the Proecures folder in object explorer to update the list, right?

any chance it's under a differnet schema? like user.procname instead of dbo.procname, so it's down at the bottom where you dfo not expect it?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

daveriya SSCrazy Points: 2352 More actions · Answer 12

DEV,i tried it ,it showing over there,but it is under different schema,not dbo,thats y it is not showing,how to change schema to dbo

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 13

daveriya (11/11/2011)
no its not in the master database or under system stored procedure.Any other option,while creating it i didint use 'USE DATABASENAME'.
but i know i have selected correct database on the top.and i am able to execute and see he result

Can you please upload a screenshot highlighting following?

•Current Server (in Tree View)

•Current DB (in Tree View)

•Current DB (in Query Window)

•Status Bar (below the query window)

•And Create SP statement.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 14

daveriya (11/11/2011)
DEV,i tried it ,it showing over there,but it is under different schema,not dbo,thats y it is not showing,how to change schema to dbo

If ALTER works, good else recreate it with new SCHEMA NAME.

ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]

[ { @parameter [ type_schema_name. ] data_type }

[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]

] [ ,...n ]

[ WITH <procedure_option> [ ,...n ] ]

[ FOR REPLICATION ]

AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

[;]

<procedure_option> ::=

[ ENCRYPTION ]

[ RECOMPILE ]

[ EXECUTE AS Clause ]