Loading ...
Sorry, an error occurred while loading the content.
 

ALTER statements within cursors? (SQL 2005)

Expand Messages
  • Jorge Segarra
    Hey all, got a little issue and I was hoping to bounce this off the group. I m trying to pass a variable from a cursor to an ALTER statement. Is this possible?
    Message 1 of 5 , Jun 19, 2008
      Hey all, got a little issue and I was hoping to bounce this off the group. I'm trying to pass a variable from a cursor to an ALTER statement. Is this possible? Here's the code I'm trying out:
       
      declare @lname as nvarchar(128);
       
      DECLARE custom_cursor CURSOR fast_forward FOR
      select name from sys.syslogins;
      OPEN custom_cursor
      FETCH NEXT FROM custom_cursor into @lname;
      SET @lname = @lname
       
      WHILE @@FETCH_STATUS = 0
      BEGIN
      ALTER LOGIN @lname ENABLE
      --PRINT @lname
      FETCH NEXT FROM custom_cursor into @lname;
       
      end
       
      close custom_cursor;
      deallocate custom_cursor;
       
       
      When I run the code it errors saying error near @lname on line 11 (ALTER LOGIN line). If I comment that line out and uncomment the PRINT line, however, the cursor works as I'm expecting and returns all the login names on the server. Any help would be appreciated! Additionally if you're on Experts Exchange and you want to get a few more points from this I've posted the question there as well: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23498937.html
       
      Thanks!
       
       
       
      Jorge Segarra
      Network Analyst II
      University Community Hospital
      813-971-6000 ext. 51092
       

      The views expressed in this e-mail are the views of the sender and do not necessarily represent the views of University Community Health. This e-mail message and its attachments are for the sole use of the intended recipients. They may contain confidential information or other information subject to legal protection and/or restrictions. If you are not the intended recipient of this message, please do not read, copy, use or disclose this message or its attachments. Please notify the sender by replying to this message and delete or destroy all copies of this message and attachments in all media. Thank you.

    • Sean Conway
      Try this.. ALTER LOGIN + @lname + ENABLE Will put around your @lname which might be needed for domain names.. Thank You, Sean T. Conway To:
      Message 2 of 5 , Jun 19, 2008
        Try this..
         
        ALTER LOGIN '''' + @lname + '''' ENABLE
         
         
        Will put '' around your @lname which might be needed for domain names..


        Thank You,
         
         
        Sean T. Conway
         




        To: tampasql@...; tampasql@yahoogroups.com
        From: jsegarra@...
        Date: Thu, 19 Jun 2008 10:25:37 -0400
        Subject: [tampasql] ALTER statements within cursors? (SQL 2005)

        Hey all, got a little issue and I was hoping to bounce this off the group. I'm trying to pass a variable from a cursor to an ALTER statement. Is this possible? Here's the code I'm trying out:
         
        declare @lname as nvarchar(128) ;
         
        DECLARE custom_cursor CURSOR fast_forward FOR
        select name from sys.syslogins;
        OPEN custom_cursor
        FETCH NEXT FROM custom_cursor into @lname;
        SET @lname = @lname
         
        WHILE @@FETCH_STATUS = 0
        BEGIN
        ALTER LOGIN @lname ENABLE
        --PRINT @lname
        FETCH NEXT FROM custom_cursor into @lname;
         
        end
         
        close custom_cursor;
        deallocate custom_cursor;
         
         
        When I run the code it errors saying error near @lname on line 11 (ALTER LOGIN line). If I comment that line out and uncomment the PRINT line, however, the cursor works as I'm expecting and returns all the login names on the server. Any help would be appreciated! Additionally if you're on Experts Exchange and you want to get a few more points from this I've posted the question there as well: http://www.experts- exchange. com/Microsoft/ Development/ MS-SQL-Server/ SQL-Server- 2005/Q_23498937. html
         
        Thanks!
         
         
         
        Jorge Segarra
        Network Analyst II
        University Community Hospital
        813-971-6000 ext. 51092
         

        The views expressed in this e-mail are the views of the sender and do not necessarily represent the views of University Community Health. This e-mail message and its attachments are for the sole use of the intended recipients. They may contain confidential information or other information subject to legal protection and/or restrictions. If you are not the intended recipient of this message, please do not read, copy, use or disclose this message or its attachments. Please notify the sender by replying to this message and delete or destroy all copies of this message and attachments in all media. Thank you.



        Earn cashback on your purchases with Live Search - the search that pays you back! Learn More
      • Scolaro, Jason
        I might try dynamic SQL: declare @sql nvarchar(300) set @sql = ALTER LOGIN + @lname + ENABLE exec sp_executesql @sql Jason Scolaro - scolaro@arch.usf.edu
        Message 3 of 5 , Jun 19, 2008

          I might try dynamic SQL:

           

          declare @sql nvarchar(300)

          set @sql = 'ALTER LOGIN ' + @lname + ' ENABLE'

          exec sp_executesql @sql

           

          Jason Scolaro scolaro@...
          Database Applications Team Lead
          Florida Center for Community Design + Research
          School of Architecture and Community Design, University of South Florida
          4202 E. Fowler Ave HMS 301, Tampa, FL. 33620-8340
          813.974.8459
          www.WaterAtlas.org

           

          From: tampasql@yahoogroups.com [mailto:tampasql@yahoogroups.com] On Behalf Of Jorge Segarra
          Sent: Thursday, June 19, 2008 10:26 AM
          To: tampasql@...; SQL User Group
          Subject: [tampasql] ALTER statements within cursors? (SQL 2005)

           

          Hey all, got a little issue and I was hoping to bounce this off the group. I'm trying to pass a variable from a cursor to an ALTER statement. Is this possible? Here's the code I'm trying out:

           

          declare @lname as nvarchar(128);

           

          DECLARE custom_cursor CURSOR fast_forward FOR
          select name from sys.syslogins;
          OPEN custom_cursor
          FETCH NEXT FROM custom_cursor into @lname;
          SET @lname = @lname

           

          WHILE @@FETCH_STATUS = 0
          BEGIN
          ALTER LOGIN @lname ENABLE
          --PRINT @lname
          FETCH NEXT FROM custom_cursor into @lname;

           

          end

           

          close custom_cursor;
          deallocate custom_cursor;

           

           

          When I run the code it errors saying error near @lname on line 11 (ALTER LOGIN line). If I comment that line out and uncomment the PRINT line, however, the cursor works as I'm expecting and returns all the login names on the server. Any help would be appreciated! Additionally if you're on Experts Exchange and you want to get a few more points from this I've posted the question there as well: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23498937.html

           

          Thanks!

           

           

           

          Jorge Segarra

          Network Analyst II

          University Community Hospital

          813-971-6000 ext. 51092

           

           

          The views expressed in this e-mail are the views of the sender and do not necessarily represent the views of University Community Health. This e-mail message and its attachments are for the sole use of the intended recipients. They may contain confidential information or other information subject to legal protection and/or restrictions. If you are not the intended recipient of this message, please do not read, copy, use or disclose this message or its attachments. Please notify the sender by replying to this message and delete or destroy all copies of this message and attachments in all media. Thank you.

        • Brian Hollister
          Hi Jorge, When statements call for an identifier you can not use variables. This is the case with your code, you are trying to use a variable for the login
          Message 4 of 5 , Jun 19, 2008
            Hi Jorge,
             
             
            When statements call for an identifier you can not use variables. This is the case with your code, you are trying to use a variable for the login identifier. You need to construct the statement in a string and then execute it as followes...
             
             
            declare @lname as nvarchar(128) ;
            declare @sql varchar(8000)
             
            DECLARE custom_cursor CURSOR fast_forward FOR
            select name from sys.syslogins;
            OPEN custom_cursor
            FETCH NEXT FROM custom_cursor into @lname;
            WHILE @@FETCH_STATUS = 0
            BEGIN
            set @sql = 'ALTER ' + @lName + ' ENABLE'
            print @sql
            exec(@sql)
            FETCH NEXT FROM custom_cursor into @lname;
            end
             
            close custom_cursor;
            deallocate custom_cursor;
             
            Notice too that i took out the line where you set @lName to itself, no need for that.
             
             
            hth,
             
            Brian
             
             
             
             
            -----Original Message-----
            From: tampasql@yahoogroups.com [mailto:tampasql@yahoogroups.com]On Behalf Of Jorge Segarra
            Sent: Thursday, June 19, 2008 10:26 AM
            To: tampasql@...; SQL User Group
            Subject: [tampasql] ALTER statements within cursors? (SQL 2005)

            Hey all, got a little issue and I was hoping to bounce this off the group. I'm trying to pass a variable from a cursor to an ALTER statement. Is this possible? Here's the code I'm trying out:
             
            declare @lname as nvarchar(128) ;
             
            DECLARE custom_cursor CURSOR fast_forward FOR
            select name from sys.syslogins;
            OPEN custom_cursor
            FETCH NEXT FROM custom_cursor into @lname;
            SET @lname = @lname
             
            WHILE @@FETCH_STATUS = 0
            BEGIN
            ALTER LOGIN @lname ENABLE
            --PRINT @lname
            FETCH NEXT FROM custom_cursor into @lname;
             
            end
             
            close custom_cursor;
            deallocate custom_cursor;
             
             
            When I run the code it errors saying error near @lname on line 11 (ALTER LOGIN line). If I comment that line out and uncomment the PRINT line, however, the cursor works as I'm expecting and returns all the login names on the server. Any help would be appreciated! Additionally if you're on Experts Exchange and you want to get a few more points from this I've posted the question there as well: http://www.experts- exchange. com/Microsoft/ Development/ MS-SQL-Server/ SQL-Server- 2005/Q_23498937. html
             
            Thanks!
             
             
             
            Jorge Segarra
            Network Analyst II
            University Community Hospital
            813-971-6000 ext. 51092
             

            The views expressed in this e-mail are the views of the sender and do not necessarily represent the views of University Community Health. This e-mail message and its attachments are for the sole use of the intended recipients. They may contain confidential information or other information subject to legal protection and/or restrictions. If you are not the intended recipient of this message, please do not read, copy, use or disclose this message or its attachments. Please notify the sender by replying to this message and delete or destroy all copies of this message and attachments in all media. Thank you.

          • Jorge Segarra
            Awesome, thanks! Also thanks to Jason Scolaro and Sean Conway who also got back to me quick with the solution! Much appreciated guys. Jorge Segarra Network
            Message 5 of 5 , Jun 19, 2008
              Awesome, thanks! Also thanks to Jason Scolaro and Sean Conway who also got back to me quick with the solution! Much appreciated guys.
               
               
              Jorge Segarra
              Network Analyst II
              University Community Hospital
              813-971-6000 ext. 51092
               


              >>> "Brian Hollister" <bhollister@...> 6/19/2008 10:58 AM >>>

              Hi Jorge,
               
               
              When statements call for an identifier you can not use variables. This is the case with your code, you are trying to use a variable for the login identifier. You need to construct the statement in a string and then execute it as followes...
               
               
              declare @lname as nvarchar(128) ;
              declare @sql varchar(8000)
               
              DECLARE custom_cursor CURSOR fast_forward FOR
              select name from sys.syslogins;
              OPEN custom_cursor
              FETCH NEXT FROM custom_cursor into @lname;
              WHILE @@FETCH_STATUS = 0
              BEGIN
              set @sql = 'ALTER ' + @lName + ' ENABLE'
              print @sql
              exec(@sql)
              FETCH NEXT FROM custom_cursor into @lname;
              end
               
              close custom_cursor;
              deallocate custom_cursor;
               
              Notice too that i took out the line where you set @lName to itself, no need for that.
               
               
              hth,
               
              Brian
               
               
               
               
              -----Original Message-----
              From: tampasql@yahoogroup s.com [mailto:tampasql@ yahoogroups. com]On Behalf Of Jorge Segarra
              Sent: Thursday, June 19, 2008 10:26 AM
              To: tampasql@googlegrou ps.com; SQL User Group
              Subject: [tampasql] ALTER statements within cursors? (SQL 2005)

              Hey all, got a little issue and I was hoping to bounce this off the group. I'm trying to pass a variable from a cursor to an ALTER statement. Is this possible? Here's the code I'm trying out:
               
              declare @lname as nvarchar(128) ;
               
              DECLARE custom_cursor CURSOR fast_forward FOR
              select name from sys.syslogins;
              OPEN custom_cursor
              FETCH NEXT FROM custom_cursor into @lname;
              SET @lname = @lname
               
              WHILE @@FETCH_STATUS = 0
              BEGIN
              ALTER LOGIN @lname ENABLE
              --PRINT @lname
              FETCH NEXT FROM custom_cursor into @lname;
               
              end
               
              close custom_cursor;
              deallocate custom_cursor;
               
               
              When I run the code it errors saying error near @lname on line 11 (ALTER LOGIN line). If I comment that line out and uncomment the PRINT line, however, the cursor works as I'm expecting and returns all the login names on the server. Any help would be appreciated! Additionally if you're on Experts Exchange and you want to get a few more points from this I've posted the question there as well: http://www.experts- exchange. com/Microsoft/ Development/ MS-SQL-Server/ SQL-Server- 2005/Q_23498937. html
               
              Thanks!
               
               
               
              Jorge Segarra
              Network Analyst II
              University Community Hospital
              813-971-6000 ext. 51092
               

              The views expressed in this e-mail are the views of the sender and do not necessarily represent the views of University Community Health. This e-mail message and its attachments are for the sole use of the intended recipients. They may contain confidential information or other information subject to legal protection and/or restrictions. If you are not the intended recipient of this message, please do not read, copy, use or disclose this message or its attachments. Please notify the sender by replying to this message and delete or destroy all copies of this message and attachments in all media. Thank you.


              The views expressed in this e-mail are the views of the sender and do not necessarily represent the views of University Community Health. This e-mail message and its attachments are for the sole use of the intended recipients. They may contain confidential information or other information subject to legal protection and/or restrictions. If you are not the intended recipient of this message, please do not read, copy, use or disclose this message or its attachments. Please notify the sender by replying to this message and delete or destroy all copies of this message and attachments in all media. Thank you.

            Your message has been successfully submitted and would be delivered to recipients shortly.