September 14, 2009 at 1:48 am
Hi,
I have got two strange issue on my database:
Scenario:
I have a stored procedure mySP, which has a select query that JOINs two tables, say T1 in the current database and T2 in Database2.
When I executed sp_depends mySP, it has not given any dependencies. So, I dropped the procedure and re-created. Then, it gave me one dependency T1 but not the other table (in Database2) in JOIN.
I'm not sure why sp_depends didn't give results before I re-created SP.
I have many SPs and I'm not sure how many of them lack dependency information.
1) Is there any way to scan through all SPs and created dependencies without dropping/re-creating them manually one by one?
2) Doesn't sp_depends reflect the tables used, if they are in differenet to the current database? In this case, how can I get this information? Do we have any workaround for this?
Please help me in resolving the above two issues.
Thanks in advance
September 14, 2009 at 2:00 am
sarath.tata (9/14/2009)
Hi,I have got two strange issue on my database:
Scenario:
I have a stored procedure mySP, which has a select query that JOINs two tables, say T1 in the current database and T2 in Database2.
When I executed sp_depends mySP, it has not given any dependencies. So, I dropped the procedure and re-created. Then, it gave me one dependency T1 but not the other table (in Database2) in JOIN.
I'm not sure why sp_depends didn't give results before I re-created SP.
I have many SPs and I'm not sure how many of them lack dependency information.
1) Is there any way to scan through all SPs and created dependencies without dropping/re-creating them manually one by one?
2) Doesn't sp_depends reflect the tables used, if they are in differenet to the current database? In this case, how can I get this information? Do we have any workaround for this?
Please help me in resolving the above two issues.
Thanks in advance
try this link for better understanding
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/can-t-depend-on-sp_depends-try-using-sp_
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 14, 2009 at 3:02 am
thanks for your reply,
I'm using SQL Server 2005 and could not find the SP sp_refreshsqlmodule in my database.
Could you please send me the script, if possible, so that I can create this SP and run it?
September 14, 2009 at 3:29 am
sp_refreshsqlmodule is in the master database
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 14, 2009 at 3:31 am
I'm very sorry that I'm bothering again. Actually, i couldn't find it in master as well.
September 14, 2009 at 4:02 am
use [mydatabase]
go
exec master..sp_refreshsqlmodule 'object name in here'
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 14, 2009 at 5:44 am
hey,
the problem is I don't have this stored procedure in master. So I'm unable to run it.
if possible, can you pls email me the script for that SP to sarath.tata@gmail.com? I'll create it here and run it.
Also, can anyone please answer my 2nd query?
Thanks,
September 14, 2009 at 5:53 am
sarath.tata (9/14/2009)
hey,the problem is I don't have this stored procedure in master. So I'm unable to run it.
You really really really do have it you know 😉
It's part of SQL Server - part of the core product. In the master database, where system stuff goes.
You won't see it under Stored Procedures in Management Studio, because it is under System Stored Procedures.
Because it is part of the system. In the master database.
You see?
I find it often rewarding to actually try stuff I am being told rather than making zero effort and waiting for someone to send teh codez.
I won't be answering your second question on this occasion.
Life is too short.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2009 at 5:58 am
Thanks for your reply,
Though I am not proficient in SQL, I have tried to do the below steps.
I have selected master database and executed the below one:
EXEC sp_refreshsqlmodule 'mySP'
Output:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_refreshsqlmodule'.
September 14, 2009 at 6:18 am
What is the result of running SELECT @@VERSION please?
...and I am assuming you have sysadmin permissions to the SQL Server, is that correct?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 25, 2009 at 12:26 am
My experience is that you need a minimum of SQL Server 2005 SP2, before Service Pack 2 this SP 'sp_refreshsqlmodule' does not exist.
June 16, 2010 at 3:28 pm
Paul,
I thought you needed SQL 2005 SP2 to get sp_refreshsqlmodule.
Doug
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply