Doing away with cursors

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.



ALTER PROCEDURE [dbo].[usp_get_loginData]


 TRUNCATE TABLE staging_loginData
 DECLARE @servername VARCHAR(200),
         @serverid   INT
 DECLARE @query VARCHAR(1000)
 DECLARE @executestring NVARCHAR(1000)
 DECLARE @remotequery NVARCHAR(2000)
 DECLARE @columnname VARCHAR(200)
 DECLARE server_cursor CURSOR FOR
   SELECT serverid,
   FROM   server_data
   WHERE  version NOT LIKE '2000' and active = 1
 OPEN server_cursor
 FETCH next FROM server_cursor INTO @serverid, @servername
       SET @query = 'DRIVER = SQL Server;server=' + @servername
                    + ';Trusted_Connection=yes;'
       SELECT @executestring = 'select ''' + @servername
                               + ''',* from openrowset (''SQLNCLI'','''
                               + @query
              ''',''set fmtonly off exec master.dbo.xp_loginconfig'')'
           SELECT @servername
       INSERT INTO staging_loginData
       EXEC Sp_executesql

       FETCH next FROM server_cursor INTO @serverid, @servername
 CLOSE server_cursor
 DEALLOCATE server_cursor

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?

more ▼

asked Aug 29, 2012 at 07:34 PM in Default

avatar image

80 4 4 8

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Aug 29, 2012 at 09:01 PM

avatar image

4.9k 4 5

PS was one of the options I had a look at, but with the security baselines that are handed down form our head IT office we are forced to keep our SQL servers as close to vanilla as possible(in regards to jobs or traces). I know this is very limiting and that is why the job that collects the above information is located on a separate server all together. What can you do, that is the way they want it and I have to figure out a workaround.

Aug 30, 2012 at 06:35 AM WcMyburgh

You can still use Powershell to query the server list from that server and then make the calls from there. With remoting and threading this process will work so much better in PowerShell than through T-SQL. PowerShell is built for this sort of thing. T-SQL is not.

Aug 30, 2012 at 10:57 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Aug 30, 2012 at 04:10 PM

avatar image

15.6k 22 55 38

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 29, 2012 at 07:34 PM

Seen: 1128 times

Last Updated: Aug 30, 2012 at 04:10 PM

Copyright 2018 Redgate Software. Privacy Policy