Post reply

Location of master..function/sp

  • February 24, 2011 at 5:34 am

    #231725

    Hi I have to trace a query.There is a function which calls another, which says "EXEC master..xp_epoch" which is user defined. Where can i find this?

    I tried everywhere in master DB but not able to find it.

    Can someone tell me where the functions related to "master.." stored?

    Thanks in advance for the help.

  • February 25, 2011 at 8:37 am

    #1291571

    Based on the XP its an extended stored procedure which would be listed in SSMS under ServerName->Databases->SystemDatabases->master->programmability->Extended Stored Procedures->

    Of course if its just named that way and is a regular stored procedure then programmability->Stored Procedures

    And both of the above assume that as you said it is user defined.

    Another option is sp_helptext <servername>

    In general extended stored procedures are early CLR and will be removed in a future version of Microsoft SQL Server. They point to an external DLL.

    Good luck

    Kenneth

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • February 27, 2011 at 2:30 am

    #1292006

    Extended stored procedures are just links to an external DLL. You can find information about a given procedure using:

    EXECUTE sys.sp_helpextendedproc @funcname = N'xp_epoch';

    That will show you which DLL the extended procedure calls into. There is no TSQL procedure code associated with it.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 28, 2011 at 12:14 am

    #1292167

    Thanks guys. It was in fact an extended stored procedure. Found it

  • February 28, 2011 at 12:24 am

    #1292170

    prady_1988 (2/28/2011)

    Thanks guys. It was in fact an extended stored procedure. Found it

    Cool. When you get a chance, ask whoever provided it when they will be replacing it with a SQLCLR function.

    Extended stored procedures are deprecated, because SQLCLR functions are much more efficient and less likely to compromise server stability.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 5 posts - 1 through 4 (of 4 total)

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