October 28, 2009 at 9:17 pm
Comments posted to this topic are about the item Methods For Converting a Stored Procedure
October 29, 2009 at 12:17 am
What are the advanatages of using this method over the standard method of Insert and Exec ?
October 29, 2009 at 2:12 am
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.
October 29, 2009 at 2:13 am
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.
October 29, 2009 at 2:47 am
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?
October 29, 2009 at 3:04 am
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?
October 29, 2009 at 3:09 am
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.
Clement
October 29, 2009 at 4:27 am
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
October 29, 2009 at 4:29 am
clementhuge (10/29/2009)
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.
Good point about the duplicate column names in sp_who2 etc! But I don't get your point about the linked server?
October 29, 2009 at 4:34 am
What are the security implications of making the configuration change for allowing ad hoc distributed queries?
October 29, 2009 at 5:08 am
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.
Clement
October 29, 2009 at 5:10 am
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.
Clement
October 29, 2009 at 7:09 am
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.
October 29, 2009 at 7:49 am
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:
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/o
It 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 databases
Also if the procedure has the same name as an sp_XXX procedure in MASTER your stored procedure will never execute
October 29, 2009 at 8:37 am
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.
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.
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply