Hello,
I'm trying to loop through a cursor, which is essentially querying the server names of the SQL Servers I manage.  I pretty much understand this won't work, but many of you will understand what I'm attempting to do.  Is there another way to approach this that will work?  This will basically allow me to automate the process connecting to various servers versus having to use the tedious process of clicking "Query -> Connection -> Change Connection".

I'm currently running this script through the SMSE in SQLCMD mode.  I don't object to doing it another way.


declare @servername varchar(255)
DECLARE servercur CURSOR FOR
    SELECT ServerName FROM tServers
OPEN servercur
FETCH NEXT FROM servercur INTO @servername
WHILE @@FETCH_STATUS = 0 BEGIN
    SELECT CAST (@@version AS char (175))
    PRINT @@version
    GO
FETCH NEXT FROM servercur INTO @servername
END
CLOSE servercur
DEALLOCATE servercur

Thank you in advance,
Allen

Procedural programming 'should' not happen in SQL Server -but sometimes it does what is needed -especially for maintenance.

Don't use a CURSOR when a WHILE loop will do the task. Something like this could work. (Assumes you have a table of Servers and that table has an identity column.

DECLARE
     @ServerName nvarchar(100)
   , @RowCount   int
   , @TotalRows  int

SET @RowCount = 0

SELECT @TotalRows = count(1)
FROM   MyServerList

WHILE @RowCount < @TotalRows
   BEGIN
      SELECT @ServerName = ServerName
      FROM   MyServerList
      WHERE  MyServerList_ID = @RowCount

      {Do your work here

      SET @RowCount = (@RowCount + 1)
   END
    ,

  I'm trying to loop through a cursor, which is essentially querying the server names of the SQL Servers I manage.  I pretty much understand this won't work, but many of you will understand what I'm attempting to do.  Is there another way to approach this that will work?  This will basically allow me to automate the process connecting to various servers versus having to use the tedious process of clicking "Query -> Connection -> Change Connection".

  I'm currently running this script through the SMSE in SQLCMD mode.  I don't object to doing it another way.


  declare @servername varchar(255)
  DECLARE servercur CURSOR FOR
      SELECT ServerName FROM tServers
  OPEN servercur
  FETCH NEXT FROM servercur INTO @servername
  WHILE @@FETCH_STATUS = 0 BEGIN
      SELECT CAST (@@version AS char (175))
      PRINT @@version
      GO
  FETCH NEXT FROM servercur INTO @servername
  END
  CLOSE servercur
  DEALLOCATE servercur

  Thank you in advance,
  Allen

The actual crux of my problem, however, is in the SQLCMD statement.  I agree, I could use a WHILE loop versus the CURSOR, but my issue is with dynamically building the SQLCMD statement

does not work.  What I want is some way to do security setting checks on an array of servers without having to replicate the code over and over after each :connect statement.

I have about 50 servers that I care for and don't want to have to manually connect to each server in order to run my security checking script.
  Procedural programming 'should' not happen in SQL Server -but sometimes it does what is needed -especially for maintenance.

  Don't use a CURSOR when a WHILE loop will do the task. Something like this could work. (Assumes you have a table of Servers and that table has an identity column.

  DECLARE
       @ServerName nvarchar(100)
     , @RowCount   int
     , @TotalRows  int

  SET @RowCount = 0

  SELECT @TotalRows = count(1)
  FROM   MyServerList

  WHILE @RowCount < @TotalRows
     BEGIN
        SELECT @ServerName = ServerName
        FROM   MyServerList
        WHERE  MyServerList_ID = @RowCount

        {Do your work here

        SET @RowCount = (@RowCount + 1)
     END
      ,

  --
  Arnie Rowland, YACE*
  "To be successful, your heart must accompany your knowledge."

  *Yet Another Certification Exam


    Hello,
    I'm trying to loop through a cursor, which is essentially querying the server names of the SQL Servers I manage.  I pretty much understand this won't work, but many of you will understand what I'm attempting to do.  Is there another way to approach this that will work?  This will basically allow me to automate the process connecting to various servers versus having to use the tedious process of clicking "Query -> Connection -> Change Connection".

    I'm currently running this script through the SMSE in SQLCMD mode.  I don't object to doing it another way.


    declare @servername varchar(255)
    DECLARE servercur CURSOR FOR
        SELECT ServerName FROM tServers
    OPEN servercur
    FETCH NEXT FROM servercur INTO @servername
    WHILE @@FETCH_STATUS = 0 BEGIN
        SELECT CAST (@@version AS char (175))
        PRINT @@version
        GO
    FETCH NEXT FROM servercur INTO @servername
    END
    CLOSE servercur
    DEALLOCATE servercur

    Thank you in advance,
    Allen

SQL question
SQLXML 4.0 "timestamp" updategram question
Function results
Incorrect information from a "View"
selecting on a null col.