Methods For Converting a Stored Procedure

  • Tom Garth (10/29/2009)


    David McKinney (10/29/2009)


    ruedifuchs (10/29/2009)


    I agree, the code for using OPENROWSET is very interesting.

    But since the stored procedure has to return a result set, what is the advantage over using a table-valued function?

    I'm not sure if I understand your question, ruedi, but here goes.

    The examples given, sp_who, sp_lock, are SYSTEM stored procedures (i.e. not yours!) - they are not table-valued functions. The data returned by these is very useful - but difficult to use effectively (filter, join, sort etc) unless you can put it into a recordset of some sort (table / view / CTE etc). The OPENROWSET code enables you to do that. Short of rewriting the entire sp as a table-valued function, I'm not sure how a table-valued function can help you?

    Does this clarify?

    I think that "ruedifuchs" meant that a Table Valued Function does provide a recordset including the table definition and could probably replace the stored procedure and be called directly with a select statement.

    Tom,

    ....could probably replace WHICH stored procedure? The sp_who / sp_lock? If this is what you mean, then I agree, sp_who, sp_lock would be handier if they were table type functions, BUT THEY ARE NOT! Microsoft didn't write them that way. Until they do (and as was pointed out DMV's may be an alternative), this (with some caveats) provides a fairly generic way of converting their output to a recordset. The alternative would be to rewrite ourselves sp_who / sp_lock as a table type function, but that's hardly a generic or quick solution.

    Do you get my point .... or have I missed yours?

    Regards,

    David.

  • Yes, Tom,

    Thanks for pointing out the problems with Microsofts built-in stored procedures.

    If the OPENROWSET can handle this, that is great.

    BTW:

    In Excel I'm using table-valued functions and do not specify "Table" but "SQL" on a datasource (which provides the connection to the database):

    select *, Datediff(day,open_date,getdate()) [Days Open]

    from dbo.tvFn_TicketsLastActivity('IT Application Management', 1)

    order by 15,6 desc

    As you can see my table-valuied-function is called with parameters, but things like getdate() and sorting are not accepted in the table-valued-function and must be.

  • Do you get my point .... or have I missed yours?

    Regards,

    David.

    Maybe both Dave. I thought the article was real good and gave it a good rating. The UDF would not handle the multiple sp_ procedures easily because of the need for a defined output.

    Thanks,

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • First off. Nice article with a lot of things to think about.

    As mentioned in the previous couple of posts. A ConvertProcToFunction would, in a lot of cases be even handier.

    It's given me something to think about and some good starting blocks. I shall ponder on this further.

    [font="Arial"]_______________________________________________________
    Change is inevitable... Except from a vending machine.[/font]

  • Hi to all,

    I'm trying to use the code, because it's that I need, but I get the error:

    Msg 7357, Level 16, State 2, Procedure sp_ConvProc2View, Line 27

    Cannot process the object "SET FMTONLY OFF EXEC sp_ControlStock". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

    Some body can help me?

    Thanks in advance...

  • Ramon-218872 (10/29/2009)


    Hi to all,

    I'm trying to use the code, because it's that I need, but I get the error:

    Msg 7357, Level 16, State 2, Procedure sp_ConvProc2View, Line 27

    Cannot process the object "SET FMTONLY OFF EXEC sp_ControlStock". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

    Some body can help me?

    Thanks in advance...

    Do you have exec permssions on the stored procedure with the name sp_ControlStock?

    i.e. if you type 'EXEC sp_ControlStock' (with no parameters) does that return you some rows?

  • Yes I can exec sp_ControlStock without problems...

    I make some tests:

    exec SGANPS.dbo.sp_ControlStock

    It works

    SELECT *

    FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',

    'SELECT *

    FROM SGANPS.dbo.ARTICULOS');

    It works

    SELECT *

    FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',

    'exec SGANPS.dbo.sp_ControlStock');

    Msg 7357, Level 16, State 2, Line 1

    Cannot process the object "exec SGANPS.dbo.sp_ControlStock". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

    Some suggestion?

    Thanks in advance

  • I would avoid such approaches as overly complex. I don't even want to think if this is secure or not. It is so very much easier to just reuse code wrapped in UDFs, so why would anyone want to go for a much more complex approach?

  • Ramon-218872 (10/29/2009)


    Yes I can exec sp_ControlStock without problems...

    I make some tests:

    exec SGANPS.dbo.sp_ControlStock

    It works

    SELECT *

    FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',

    'SELECT *

    FROM SGANPS.dbo.ARTICULOS');

    It works

    SELECT *

    FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',

    'exec SGANPS.dbo.sp_ControlStock');

    Msg 7357, Level 16, State 2, Line 1

    Cannot process the object "exec SGANPS.dbo.sp_ControlStock". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

    Some suggestion?

    Thanks in advance

    I think I'm beginning to confuse myself. However while your credentials are passed along to the '.' linked version of your server, will the linked server acknowledge them for that procedure? Try - SELECT *

    FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',

    'exec .dbo.sp_who');

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • Hi,

    SELECT *

    FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',

    'exec .dbo.sp_who');

    It works

    But:

    SELECT *

    FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',

    'exec .dbo.sp_ControlStock');

    Gets:

    Msg 7357, Level 16, State 2, Line 1

    Cannot process the object "exec .dbo.sp_ControlStock". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

  • Ramon,

    I told you I was confusing myself.

    USE TEST

    GO

    CREATE PROCEDURE dbo.sp_ControlStock AS SELECT 'ONE', 'TWO', 'THREE'

    GO

    SELECT *

    FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',

    'exec TEST.dbo.sp_ControlStock');

    GO

    That works fine for me. Maybe you should start over, or go another route.

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • I'm not sure but perpahs the problem it's that in sp_ControlStock I'm using temporal tables or OPENQUERY statments because if I use a "simple" store procedure, now it works...

  • That explains a lot. Like

    linked server "(null)"

    It sounds like your OPENROWSET statement within the procedure isn't passing suitable credentials.

    Check BOL for Linked Server Security and Delegates regarding double hops.

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • The security implications of this and lack of discussion and warning on them really bothers me.

  • It's the double hop issue that always prevents me from using the

    Trusted_Connection=yes

    However, if the account that runs your SQL Server instance has the ability to register its own SPN as is explained at

    http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx

    then you should find that the connection you have open is using Kerberos authentication with the following query:

    select auth_scheme from sys.dm_exec_connections

    where session_id = @@Spid

    If it is NTLM then the service account of SQL probably doesn't have the permission to register it's own SPN which causes security to collapse to NTLM which has the affect of disallowing the SQL instance to trust a security token passed to it. The double hop then becomes impossible and the security context is always lost on the second hop. If you are having trouble with the openrowset across servers with error:

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    then check out http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx

    This only applies to trusted because there is no security token passed with SQL authentication.

Viewing 15 posts - 16 through 30 (of 38 total)

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