SQL Server Performance

XP_EXEC, what is it, where does it come from

Discussion in 'General DBA Questions' started by jsciii, Jun 18, 2007.

  1. jsciii New Member

    I am trying to find out information about the Extended stored procedure: XP_EXEC. I've just learned a vendor is calling it and I am concerned about the security of it.

    I surfed the net and found little infor. There is nothing in BOL.

    I could not find the file on the new server but found it in the vendor folder on the old server.

    So, what is it? How does it get installed? Is it a security issue?

    Thanks....JC III
  2. satya Moderator

    YEs that is an undocumented SP and you can get more information from the book, "The Guru's
    Guide to SQL Server(TM) Stored Procedures, XML, and HTML" by Ken Henderson. He explained that
    quote:

    Now that you've seen how easy it is to put together a simple extended procedure, let's try one that's a little more complex. The code that follows in Listing 20-10 implements an extended procedure named xp_exec. As I mentioned in the chapter on UDFs, you aren't allowed to call EXEC() from within a UDF. Only extended procedures can be called, and only ones that begin with xp (sp_executesql isn't allowed even though it's an extended procedure because it doesn't begin with xp). Xp_exec works around this. You pass it a query you'd like to run, and it returns a result set, if there is one. Although you can't insert this result into a table variable (INSERT…EXEC isn't supported with table variables), you can manipulate tables and data, perform administrative tasks, and do most of the other things Transact-SQL allows you to do.

    Because xp_exec needs to execute queries apart from the calling connection, it must initiate its own connection (over ODBC) to the server using the connection info from the caller. SQLAllocHandle() and SQLConnect() are the key functions we'll use.
    Your concern is correct and better to contact Vendor of your application for asking the reason behind using this.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. jsciii New Member

    Thanks for the info, I'll have to see if I can find more in the book you mentioned....JC III

Share This Page