I need to find a way to re wright some or most of stored procedures we currently use to gather information from all the different server. The problem is this:
All the stored procedures use cursors to circle through a table containing all of our server details, this then builds a openrowset query the is executed per server on the list. In the end depositing the results in a staging table.
USE Db GO
ALTER PROCEDURE [dbo].[usp_get_loginData]
Being a bit new to the development side of SQL Server, the idea to move away from cursor's is there but I have no idea where to start.
Any suggestions on how to re wright this kind of script to do away with cursors?
Personally I'd ditch it completely and go with a PowerShell approach. Use PS to query the list of servers and start jobs that grab and load the information that you want (that can be done in parallel and so faster).
answered Aug 29, 2012 at 09:01 PM
I agree with SirSQL's concept and Grant's comment, I would move this out of SQL and into something like PowerShell or Python. SQL is a domain specific language. It is absolutely fantastic at what it does (query and manipulate database objects), but it is mediocre at best at other tasks. This type of task is best handled in a different language.
I know you mentioned you needed to keep your SQL Server vanilla, but permitting powershell or Python access is not really a security risk. If necessary, the script can be run from a computer other than the server, and if it helps PowerShell comes with later versions of SQL Server. While Python would have to be installed separately, it comes with most flavors of Linux which is often chosen specifically for its security.
With all that said, if you need to keep it in T-SQL you can move away from a cursor using a table variable and a while loop. You can select the server data into your table variable before the loop. In the loop, you would select the top entry from the table to process, process it, and then delete it, and loop while there are rows in the table variable. This technique tends to be shorter than the cursor approach and avoids the cursor.
But that technique, like the cursor, is still inherently a loop and is not set based. But this type of problem is not set based.
answered Aug 30, 2012 at 04:10 PM