SQLServerCentral / Discuss Content Posted by Eli Leiba / Article Discussions / Article Discussions by Author / Methods For Converting a Stored Procedure / Latest Posts

RE: Methods For Converting a Stored Procedure

Fal — Mon, 11 Apr 2011 21:37:21 GMT

[quote][b]sandro977 (4/11/2011)[/b][hr]... because in a Function you can't use a temporal table to reduce the universe, and this is something very important...[/quote]For that you need: DBCC SHRINKUNIVERSE :-P[quote][b]sandro977 (4/11/2011)[/b][hrBy other side, I see a deep problem of concurrency. If I have several users calling the store procedure, the last win.I mean, if I have 3 users calling the store procedure to select customers, and this customers should be in the resulting table.If first need customers with customer name like '%jho%'the second user need customers with customer name like '%Mik%'the third user need customers with customer name like '%Mij%'and three users ask at same time the resulset, the 3 users will get the same results in the table, the data matching with last user where.Is there some way to avoid it?[/quote]Temporary tables (not temporal) are unique to each session. The problem you describe is what would happen if you used Global Temporary Tables.Steve.

RE: Methods For Converting a Stored Procedure

sandro977 — Mon, 11 Apr 2011 14:33:40 GMT

In my experience, is good to have some way to use SPs instead Table Valued Functions because in a Function you can't use a temporal table to reduce the universe, and this is something very important when you work with a lot of records.By other side, I see a deep problem of concurrency. If I have several users calling the store procedure, the last win.I mean, if I have 3 users calling the store procedure to select customers, and this customers should be in the resulting table.If first need customers with customer name like '%jho%'the second user need customers with customer name like '%Mik%'the third user need customers with customer name like '%Mij%'and three users ask at same time the resulset, the 3 users will get the same results in the table, the data matching with last user where.Is there some way to avoid it?

RE: Methods For Converting a Stored Procedure

pbarbin — Fri, 08 Apr 2011 17:05:29 GMT

One more minor add-on:I wrote a similar stored procedure that allows me to pass in a stored procedure name and parameters and it would write the results to a table. I've used it several times to test the results from a new stored procedure against the results from an existing stored proc. All of the data gets put into tables and then you can use checksums to verify that the data in each table is exactly the same. It's a great way to unit test code changes.Since this happens on dev servers not production, changing the config settings is not a problem.Paul

RE: Methods For Converting a Stored Procedure

bobrandt — Fri, 08 Apr 2011 08:30:45 GMT

Just to add to what David McKinney already said - a simple example of how to use the OPENROWSET that makes use of the possibilities offered in the article without wrapping into a stored procedure:[code="sql"]USE masterSELECT *FROM OPENROWSET('SQLOLEDB','SERVER=.;Trusted_Connection=yes','SET FMTONLY OFF EXEC sp_lock') ORSLEFT OUTER JOIN (VALUES ('S', 'Shared'), ('IS', 'Intent Shared')) LockExpl(Mode, ModeText) ON ORS.Mode = LockExpl.Mode[/code]

RE: Methods For Converting a Stored Procedure

starunit — Fri, 08 Apr 2011 08:24:12 GMT

Thanks for the article Eli,I was just the other day trying to remember how to get the resulset back like that. I'd seen an article a while back... maybe it was yours from 2009?I think your example threw some people off - They seem obsessed with other ways to access the sp_who and whatever... but that wasn't the point, was it: sometimes we need to get the records from an already-written procedure... that procedure being already in use in other places.. no need to go through a Create Table #xxyyy() to do an Insert / Execute.[code="sql"]SELECT * FROM OPENROWSET ( 'SQLOLEDB', 'SERVER=.;Trusted_Connection=yes', ' SET FMTONLY OFF; EXEC [dbname].[dbo].[spName] @paramName = paramValue')[/code]Unfortunately, I have to agree with a few others with regards to opening up the server to Ad Hoc Distributed Queries: no can do - not generally; further, reconfiguring a production server on the fly, then setting it back after the process is done - another no can do.However, I'll keep this technique in my back pocket: it'll be a good tool for those one-off requests that inevitably come up.Cheers and best regards form Denver,

RE: Methods For Converting a Stored Procedure

James A Skipwith — Fri, 08 Apr 2011 02:35:04 GMT

Nice article Eli and has certainly generated some discussion. I too would use OPENROWSET in a derived table, rather than creating the view etc. if I was trying to achieve the same aim with system stored procedures in SQL 2000, but anything that generates four pages of forum posts is good stuff! We only learn from discussions such as this and, if no one has the bottle to put up something to discuss, no one learns!In 2005+ i would use the DMVs.Keep on writing.James

RE: Methods For Converting a Stored Procedure

rene.vaessen — Thu, 12 Nov 2009 18:09:25 GMT

use mastergoexec sp_configure 'allow updates',1goreconfigure with overridegoexec sp_configure 'Ad Hoc Distributed Queries',1goreconfigure with override>>>>Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.????????????SQL Server 2008 is what i'm using..

RE: Methods For Converting a Stored Procedure

TheMOTU — Thu, 29 Oct 2009 19:28:34 GMT

Guys,The subject in discussion is not practical in an application development domain for obvious reasons.From the perspective of DBA/Consultant I can imagine (HARDLY) that sometimes it would be interesting / impressive to show off how to use result set produced by execution of a stored procedure in a SUB-SELECT (you can't use EXECUTE in sub-query at the moment...).I think that all the story with stored procedures and views on-a-fly - is pure scholastics in the best traditions of Old Testament and Talmud.Below is a simplified example how to achieve the same results (assuming you have sysadmin credentials, of course). You can substitute system stored procedures with your own.-- Allow distributed queries firstuse masterEXEC sp_configure 'show advanced options', 1GOreconfigure with overrideGOEXEC sp_configure 'allow updates',1GOreconfigure with overrideGOEXEC sp_configure 'Ad Hoc Distributed Queries',1GOreconfigure with overrideGO-- Run your "tricks"SET NOCOUNT ONSELECT S.status, S.dbName, S.cmd, O.name, L.TYPE, L.mode, L.statusFROM (SELECT * FROM OPENROWSET ( 'SQLOLEDB','Server=.\SQL2008;Database=master;Trusted_Connection=yes;','SET FMTONLY OFF EXEC SP_WHO')) S, (SELECT * FROM OPENROWSET ( 'SQLOLEDB','Server=.\SQL2008;Database=master;Trusted_Connection=yes;','SET FMTONLY OFF EXEC SP_LOCK')) L, sys.objects OWHERE L.OBJID = O.object_idAND S.SPID = L.SPIDGO-- Cleanup after yourselfEXEC sp_configure 'Ad Hoc Distributed Queries', 0GOreconfigure with overrideGOEXEC sp_configure 'allow updates', 0GOEXEC sp_configure 'show advanced options', 0GOreconfigure with overrideGO

RE: Methods For Converting a Stored Procedure

Toby White — Thu, 29 Oct 2009 15:11:40 GMT

It's the double hop issue that always prevents me from using the Trusted_Connection=yesHowever, if the account that runs your SQL Server instance has the ability to register its own SPN as is explained athttp://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspxthen you should find that the connection you have open is using Kerberos authentication with the following query:select auth_scheme from sys.dm_exec_connectionswhere session_id = @@SpidIf 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 1Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.then check out http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspxThis only applies to trusted because there is no security token passed with SQL authentication.

RE: Methods For Converting a Stored Procedure

John Fager — Thu, 29 Oct 2009 11:46:42 GMT

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

RE: Methods For Converting a Stored Procedure

Tom Garth — Thu, 29 Oct 2009 11:30:29 GMT

That explains a lot. Like [quote]linked server "(null)"[/quote]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.

RE: Methods For Converting a Stored Procedure

Ramon-218872 — Thu, 29 Oct 2009 11:21:04 GMT

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

RE: Methods For Converting a Stored Procedure

Tom Garth — Thu, 29 Oct 2009 11:07:19 GMT

Ramon,I told you I was confusing myself.[code="sql"]USE TESTGOCREATE PROCEDURE dbo.sp_ControlStock AS SELECT 'ONE', 'TWO', 'THREE'GOSELECT *FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'exec TEST.dbo.sp_ControlStock');GO[/code]That works fine for me. Maybe you should start over, or go another route.

RE: Methods For Converting a Stored Procedure

Ramon-218872 — Thu, 29 Oct 2009 10:43:14 GMT

Hi,SELECT *FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'exec .dbo.sp_who');It worksBut:SELECT *FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'exec .dbo.sp_ControlStock');Gets:Msg 7357, Level 16, State 2, Line 1Cannot 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.

RE: Methods For Converting a Stored Procedure

Tom Garth — Thu, 29 Oct 2009 10:32:57 GMT

[quote][b]Ramon-218872 (10/29/2009)[/b][hr]Yes I can exec sp_ControlStock without problems...I make some tests:exec SGANPS.dbo.sp_ControlStockIt worksSELECT *FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'SELECT * FROM SGANPS.dbo.ARTICULOS');It worksSELECT *FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'exec SGANPS.dbo.sp_ControlStock');Msg 7357, Level 16, State 2, Line 1Cannot 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[/quote]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 [b]that [/b]procedure? Try - [code="sql"]SELECT *FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'exec .dbo.sp_who');[/code]

RE: Methods For Converting a Stored Procedure

Alexander Kuznetsov — Thu, 29 Oct 2009 10:25:19 GMT

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?

RE: Methods For Converting a Stored Procedure

Ramon-218872 — Thu, 29 Oct 2009 10:06:04 GMT

Yes I can exec sp_ControlStock without problems...I make some tests:exec SGANPS.dbo.sp_ControlStockIt worksSELECT *FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'SELECT * FROM SGANPS.dbo.ARTICULOS');It worksSELECT *FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'exec SGANPS.dbo.sp_ControlStock');Msg 7357, Level 16, State 2, Line 1Cannot 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

RE: Methods For Converting a Stored Procedure

David McKinney — Thu, 29 Oct 2009 09:39:56 GMT

[quote][b]Ramon-218872 (10/29/2009)[/b][hr]Hi to all,I'm trying to use the code, because it's that I need, but I get the error:[i]Msg 7357, Level 16, State 2, Procedure sp_ConvProc2View, Line 27Cannot 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.[/i]Some body can help me?Thanks in advance...[/quote]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?

RE: Methods For Converting a Stored Procedure

Ramon-218872 — Thu, 29 Oct 2009 09:37:35 GMT

Hi to all,I'm trying to use the code, because it's that I need, but I get the error:[i]Msg 7357, Level 16, State 2, Procedure sp_ConvProc2View, Line 27Cannot 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.[/i]Some body can help me?Thanks in advance...

RE: Methods For Converting a Stored Procedure

Rob Fisk — Thu, 29 Oct 2009 09:13:17 GMT

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.

RE: Methods For Converting a Stored Procedure

Tom Garth — Thu, 29 Oct 2009 09:13:04 GMT

[quote]Do you get my point .... or have I missed yours?Regards,David. [/quote]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,

RE: Methods For Converting a Stored Procedure

ruedifuchs — Thu, 29 Oct 2009 09:12:56 GMT

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

RE: Methods For Converting a Stored Procedure

David McKinney — Thu, 29 Oct 2009 08:55:00 GMT

[quote][b]Tom Garth (10/29/2009)[/b][hr][quote][b]David McKinney (10/29/2009)[/b][hr][quote][b]ruedifuchs (10/29/2009)[/b][hr]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?[/quote]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?[/quote]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.[/quote]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.

RE: Methods For Converting a Stored Procedure

Tom Garth — Thu, 29 Oct 2009 08:37:47 GMT

[quote][b]David McKinney (10/29/2009)[/b][hr][quote][b]ruedifuchs (10/29/2009)[/b][hr]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?[/quote]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?[/quote]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.Some advantages are...It can use stored procedures to populate it.It can utilize temp tables where views cannot.It can have parameters.It will present the data definition of it's output to a calling program where a stored procedure will not.It can be used in the FROM clause of a view if it doesn't have parameters.Some disadvantages are...Excel will not allow you to use it for a datasource.

RE: Methods For Converting a Stored Procedure

cwalker-700550 — Thu, 29 Oct 2009 07:49:33 GMT

I'm stupid. you are using MASTER and I made a best practice comment on the assumption you weren't using MASTER. I should stop answering the phone when I read these columns. :hehe:[strike]Just an aside, I never name my stored procedures with sp_XXX as this is normally reserved to MASTER.It also causes additional searches and added i/oIt will first search MASTER for the procedure, if not found it will then acquire a exclusive COMPILE lock to perform a second search of the other databasesAlso if the procedure has the same name as an sp_XXX procedure in MASTER your stored procedure will never execute[/strike]

RE: Methods For Converting a Stored Procedure

alen teplitsky — Thu, 29 Oct 2009 07:09:49 GMT

we just migrated a server to new hardware and SQL 2005 from 2000 and got rid of a lot of openrowset code in the process. One reason is there is a bug in SQL 2005 SP2 and earlier. I forgot the CU where they fixed it, but it was due to a PSS case we opened. almost 2 years ago we bought a new reporting server and set it up according to security best practices. no domain administrator account to run the service.a few months later people complain that they can't see some indexes in a database. we open a case and that was about the time we find that using a linked server to that server was flaky and didn't work a lot of times with integrated authentication. turns out there was a bug if you install SQL according to BOL instructions then some things won't work.

RE: Methods For Converting a Stored Procedure

clementhuge — Thu, 29 Oct 2009 05:10:04 GMT

I have performed recently a PCC-DSS Sql server audit and one of the best practice on the list was to avoid distributed transactions.therefore if your industry requires stringent security rules, you have to make sure that allowing server properties are not against your industry rules.

RE: Methods For Converting a Stored Procedure

clementhuge — Thu, 29 Oct 2009 05:08:00 GMT

When I deployed the code, the linked server was not recognized (the name of my server is ServerName\Instance1. It seems that the code does not work with named instance.I set up a linked server names .\Instance1 and it worked (I added the parameter @sLinkedServer Varchar(80) to the stored procedure.

RE: Methods For Converting a Stored Procedure

drnetwork — Thu, 29 Oct 2009 04:34:22 GMT

What are the security implications of making the configuration change for allowing ad hoc distributed queries?

RE: Methods For Converting a Stored Procedure

David McKinney — Thu, 29 Oct 2009 04:29:11 GMT

[quote][b]clementhuge (10/29/2009)[/b][hr]The stored procedure is interesting. However it does require a lot of prerequisites and open security holes that PCI-DSS would not allow (for example like distributed transaction).Moreover, the stored procedure does not work with sp_listprocess or sp_who2 as they have duplicate column names.Also, there needs to have the linked server set up in prerequisites (do not forget the instance name).The stored procedure is promising but lack some improvements but I think it is a great start.[/quote]Good point about the duplicate column names in sp_who2 etc! But I don't get your point about the linked server?

RE: Methods For Converting a Stored Procedure

thierry.vandurme — Thu, 29 Oct 2009 04:27:12 GMT

The OPENROWSET code and the examples are interesting, but I wonder about performance. BTW, I'm always reluctant to change system parameters (in this case ad hoc dist queries) to achieve a goal. I would rather search for alternatives first (I'm far from an expert though ;))The examples are ok for 2000. 2005 and up: better to use DMV's I suppose

RE: Methods For Converting a Stored Procedure

clementhuge — Thu, 29 Oct 2009 03:09:07 GMT

The stored procedure is interesting. However it does require a lot of prerequisites and open security holes that PCI-DSS would not allow (for example like distributed transaction).Moreover, the stored procedure does not work with sp_listprocess or sp_who2 as they have duplicate column names.Also, there needs to have the linked server set up in prerequisites (do not forget the instance name).The stored procedure is promising but lack some improvements but I think it is a great start.

RE: Methods For Converting a Stored Procedure

David McKinney — Thu, 29 Oct 2009 03:04:08 GMT

[quote][b]ruedifuchs (10/29/2009)[/b][hr]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?[/quote]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?

RE: Methods For Converting a Stored Procedure

ruedifuchs — Thu, 29 Oct 2009 02:47:51 GMT

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?

RE: Methods For Converting a Stored Procedure

David McKinney — Thu, 29 Oct 2009 02:13:23 GMT

I think the key information in the article is that you can use OPENROWSET to turn the output of a stored proc to a recordset. This message got a bit lost with all the stuff relating to creating a view with dynamic sql.The article could have been structured differently, to deliver the message first strongly and clearly i.e HOW TO DO IT, and WHY THIS IS USEFUL. (A discussion about the difficulties in sorting and filtering the output of a stored procedure.)Then, as a 'bonus', the stuff about automating the view creation, could have been tagged on.But I'm giving you five stars anyway, just for including the code for OPENROWSET.Thanks,David.

RE: Methods For Converting a Stored Procedure

sanjarani — Thu, 29 Oct 2009 02:12:05 GMT

Once a view is created, which can be created dynamically in a Stored Procedure, It can be used to join to other Views and tables.I don't see the advantages of your method. Perhaps you can state the specific uses of this methods and its advantages over other ways of accessing data.

RE: Methods For Converting a Stored Procedure

RamakrishnaMothukuri — Thu, 29 Oct 2009 00:17:34 GMT

What are the advanatages of using this method over the standard method of Insert and Exec ?

Methods For Converting a Stored Procedure

Eli Leiba — Wed, 28 Oct 2009 21:17:27 GMT

Comments posted to this topic are about the item [B]Methods For Converting a Stored Procedure[/B]