Results 1 to 4 of 4

Thread: Dynamic SQL

  1. #1
    Join Date
    Mar 2012
    Posts
    5

    Unanswered: Dynamic SQL

    I was trying to create dynamic SQL to change database mode to read only.But not able to execute successfully.

    declare @db_name varchar(50)
    declare @execute varchar(200)
    declare db_cursor_read CURSOR read_only FOR
    SELECT database_NAME FROM #TMP_TABLE

    OPEN db_cursor_read
    FETCH NEXT FROM db_cursor_read INTO @db_name


    WHILE @@FETCH_STATUS = 0
    BEGIN TRY
    set @execute = N'ALTER DATABASE ' + @db_name + N' SET READ_ONLY'
    print @db_name
    exec @execute
    --print @db_name

    FETCH NEXT FROM db_cursor_read INTO @db_name
    END TRY
    BEGIN CATCH
    PRINT 'FAILED'
    SELECT
    ERROR_NUMBER() AS ERRORNUMER,------ returns the number of the error.
    ERROR_SEVERITY(),--- - returns the severity.
    ERROR_STATE(), ---- returns the error state number.
    ----ERROR_PROCEDURE(), --- returns the name of the stored procedure or trigger where the error occurred.
    ERROR_LINE() ,--- returns the line number inside the routine that caused the error.
    ERROR_MESSAGE(); --- returns the complete text of the error message.
    FETCH NEXT FROM db_cursor_read INTO @db_name
    END CATCH

    CLOSE db_cursor_read
    DEALLOCATE db_cursor_read


    Error message :

    ERRORNUMBER : 2812
    MESSAGE : Could not find stored procedure 'ALTER DATABASE [XYZ] SET READ_ONLY'.
    Please let me know if I have missed anything.
    Thanks in advance

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    For starters:

    Code:
    declare @db_name varchar(50)
    declare @execute varchar(200)
    declare db_cursor_read CURSOR read_only FOR 
    SELECT database_NAME FROM #TMP_TABLE
    Probably need to assign @db_name a value from table. But how is tmp table populated?

    Code:
    set @execute = N'ALTER DATABASE ' + @db_name + N' SET READ_ONLY'
    Might want to add a space after @db_name. The error message is probably because @db_name = nothing?

  3. #3
    Join Date
    Mar 2012
    Posts
    5

    Dynamic SQL

    Thanks for quick reply.


    Tmp table is populated based on following condition ..

    if OBJECT_ID('tempdb..#TMP_table') is not null
    drop table #TMP_table
    go
    create table #TMP_table
    (
    database_id int,
    database_name varchar(20) not null unique
    );
    go
    insert into #TMP_table(database_id,database_name)
    select database_id,name from SYS.DATABASES
    WHERE DATABASE_ID >4 and NAME not like 'S%%' -- to ignore some dbs


    ---select * from #TMP_table

    declare @db_name varchar(50)
    declare @execute varchar(200)
    declare db_cursor_read CURSOR read_only FOR
    SELECT database_NAME FROM #TMP_TABLE

    OPEN db_cursor_read
    FETCH NEXT FROM db_cursor_read INTO @db_name


    WHILE @@FETCH_STATUS = 0
    BEGIN TRY
    set @execute = N'ALTER DATABASE ' + @db_name + N' SET READ_ONLY'
    --print @db_name
    exec @execute


    FETCH NEXT FROM db_cursor_read INTO @db_name
    END TRY
    BEGIN CATCH
    PRINT 'FAILED'
    SELECT
    ERROR_NUMBER() AS ERRORNUMER,------ returns the number of the error.
    ERROR_SEVERITY(),--- - returns the severity.
    ERROR_STATE(), ---- returns the error state number.
    ----ERROR_PROCEDURE(), --- returns the name of the stored procedure or trigger where the error occurred.
    ERROR_LINE() ,--- returns the line number inside the routine that caused the error.
    ERROR_MESSAGE(); --- returns the complete text of the error message.
    FETCH NEXT FROM db_cursor_read INTO @db_name
    END CATCH

    CLOSE db_cursor_read
    DEALLOCATE db_cursor_read

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Works for me

    Code:
    DECLARE @name varchar(255), @cmd varchar(8000), @SET varchar(25)
    
    SET @SET = 'READ_ONLY'
    -- SET @SET = 'READ_WRITE'
    
    DECLARE myCursor99 CURSOR 
    FOR
    SELECT name 
      FROM SYS.DATABASES
     WHERE name = 'Sandbox'
     --WHERE DATABASE_ID >4 and NAME not like 'S%%'
    
    OPEN myCursor99
    FETCH NEXT FROM myCursor99 INTO @name
    SET @cmd = 'ALTER DATABASE ' + @name + ' SET ' + @SET
    
    WHILE @@FETCH_STATUS = 0
      BEGIN
    	PRINT @cmd
    	EXEC(@cmd)
    	FETCH NEXT FROM myCursor99 INTO @name
    	SET @cmd = 'ALTER DATABASE ' + @name + ' SET ' + @SET
      END
     CLOSE myCursor99
     DEALLOCATE myCursor99
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •