Methods For Converting a Stored Procedure

Question

Methods For Converting a Stored Procedure

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

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

David McKinney SSChampion Points: 10472 More actions · Answer 1

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.

ruedifuchs Valued Member Points: 53 More actions · Answer 2

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.

Tom Garth SSCertifiable Points: 6193 More actions · Answer 3

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

Rob Fisk SSCommitted Points: 1861 More actions · Answer 4

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]

Ramon-218872 SSC Enthusiast Points: 152 More actions · Answer 5

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...

David McKinney SSChampion Points: 10472 More actions · Answer 6

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?

Ramon-218872 SSC Enthusiast Points: 152 More actions · Answer 7

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

Alexander Kuznetsov SSCrazy Points: 2217 More actions · Answer 8

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?

Tom Garth SSCertifiable Points: 6193 More actions · Answer 9

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

Ramon-218872 SSC Enthusiast Points: 152 More actions · Answer 10

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.

Tom Garth SSCertifiable Points: 6193 More actions · Answer 11

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

Ramon-218872 SSC Enthusiast Points: 152 More actions · Answer 12

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...

Tom Garth SSCertifiable Points: 6193 More actions · Answer 13

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

John Fager Valued Member Points: 51 More actions · Answer 14

The security implications of this and lack of discussion and warning on them really bothers me.

Toby White SSCertifiable Points: 6187 More actions · Answer 15

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.