Extended Stored Procedures

• 
  I think you've probably stumbled upon a limitation in our meta-data model, however it would be useful to know which version of SQL Server you're talking about? I'll need to check with the guys who worked on this to give you a definitive answer in any case.
  Thanks,
  Bart

  Bart Read December 13, 2006 06:57

  - 0 +
• 
  I'm using SQL 2000 databases and am using both SQL 2000 QA and SQL 2005 SSMS to access them. I haven't tried this on a SQL 2005 database. I can later today and will post the result.

  JTShyman December 13, 2006 08:10

  - 0 +
• 
  OK thanks, I'll go and bug the SQL Compare team and get back to you later with an answer.
  Cheers,
  Bart

  Bart Read December 13, 2006 09:10

  - 0 +
• 
  Thanks, Bart!
  
  The same issue does occur with SQL 2005 databases.

  JTShyman December 13, 2006 09:18

  - 0 +
• 
  Hi there,
  
  
  Well I've talked to three different people and done some research in BOL and the short answer is we won't unfortunately be fixing this for the final release of SQL Prompt 3. On the face of it, it does seem like quite a simple fix, and at the most naive level calling sp_helpextendedproc would give us at least the names, but it's not possible to get information about parameters the way we can with normal or CLR SPs. We also need to distinguish between user and system XPs and there are other factors related to caching that need to be taken into account. As such we're talking about making quite a lot of changes to add support for XPs which isn't something we can really entertain for this version.
  
  Sorry, I realise this is a particularly weaselly response, but from a technical standpoint it's just not a smart thing for us to do at this point. I wouldn't be suprised to see this appear at some point in the future, however even then it will only be available to you if you log in with dbo permissions.
  
  Apologies that I can't offer more direct assistance now, but I hope this goes some way to explaining the reasons why.
  
  
  Many thanks,
  Bart

  Bart Read December 13, 2006 10:16

  - 0 +
• 
  One more thing I should have mentioned. I discovered that those procedures you listed earlier are not actually extended stored procedures at all: you can script them out to SQL in Management Studio (I tried it earlier). It seems they're just badly named normal stored procedures, and only Microsoft knows why they begin with the "xp_" prefix, unless anyone here has any ideas?
  
  Hope that clears up the slightly odd looking behaviour at any rate.
  
  
  Thanks,
  Bart

  Bart Read December 13, 2006 10:24

  - 0 +
• 
  Bart,
  
  You're right. Some of the "extended stored procedures" are really just code like system stored procedures, or call system stored procedures using T-SQL (see xp_execresultset) or, perhaps, system .dlls (like xp_fixeddrives)
  
  I understand why it can't easily be done; thanks for the explanation. I appreciate your looking into it.

  JTShyman December 13, 2006 10:52

  - 0 +
• 
  No problem, you're welcome. Like I say, it can be done although the functionality will be fairly limited, and if it were a bit earlier in the project lifecycle I'd probably say we can do it, or if it were going to be an hour's work I'd probably say we can do it, but right now it's just a bit too risky. I do think it's something we can probably do for a future version though.
  Thanks,
  Bart

  Bart Read December 13, 2006 10:54

  - 0 +

Add comment

Please sign in to leave a comment.