Doing away with cursors

Question

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.

Sample

USE Db GO

ALTER PROCEDURE [dbo].[usp_get_loginData]

AS

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,

         servername

  FROM   server_data

  WHERE  version NOT LIKE '2000' and active = 1

OPEN server_cursor

FETCH next FROM server_cursor INTO @serverid, @servername

WHILE @@FETCH_STATUS = 0

  BEGIN

      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

        @executestring

      FETCH next FROM server_cursor INTO @serverid, @servername

  END

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?

Answer 1

0

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 '12 at 09:01 PM

SirSQL
4.6k ● 1 ● 3

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 '12 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 '12 at 10:57 AM Grant Fritchey ♦♦

(comments are locked)

Answer 2

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.

Doing away with cursors

Follow this question

Related Questions