Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    1

    Unhappy Unanswered: USE statement with a variable?

    I'm having some trouble modifing a script to save me tons of work. The script if from Microsoft, and it is used as step 3 in a 6 step process to move MS Great Plains users from one server to another. Anyway, the script runs on only 1 company database at a time, and for most Great Plains environments there would only be 1 or 2 company DBs. But I am administering in an ASP environment and we have over 30 company DBs to move. So, I though I would adapt thier script to iterate over each company DB to do the work (rather than creating 30 separate scripts). So I wrapped their loop with my loop to do the iteration. The problem is that T-SQL will not let me use a variable in a USE statement. I've tried to remove the USE statements, but that added a lot of complexity in the internal loop. What is the best way to do this?

    Here is the modified code:

    /*
    ** Drop_Users_Company.sql
    **
    ** This script will remove all users from the DYNGRP in the company database
    ** specified. It will then drop the DYNGRP and readd the DYNGRP to the company.
    ** It will then add all users back to the DYNGRP based on the SY60100 table.
    ** NOTE: You will need to replace %Companydb% with the company database
    ** name.
    */
    /* Instead of replacing %Companydb% (in each USE statement) with the name of the
    single company database that this script is supposed to work on, I've added
    @cCompany to hold the company DB name through each iteration of the outside
    cursor/while loop.
    */

    declare @cCompany sysname /* ADDED BY ME FOR THE OUTSIDE LOOP */
    declare @cStatement varchar(255) /* Misc exec string */
    declare @DynDB varchar(15) /* DB Name exec string */
    declare @DYNGRPgid int /* Id of DYNGRP group */

    /*
    ** Loop through all company databases, emptying the DYNGRP group.
    */
    SET QUOTED_IDENTIFIER OFF

    use DYNAMICS

    /* Select all of the Great Plains database names from the DB_Upgrade table, where the DB names are conviently stored */
    declare C_cursor CURSOR for select db_name from DYNAMICS..DB_Upgrade where db_name not in ('DYNAMICS')

    OPEN C_cursor
    FETCH NEXT FROM C_cursor INTO @cCompany
    WHILE (@@FETCH_STATUS <> -1)
    begin
    use @cCompany
    select @DYNGRPgid = (select gid from sysusers where name = 'DYNGRP')

    declare G_cursor CURSOR for select "sp_dropuser [" + name+"]" from sysusers
    where gid = @DYNGRPgid and name <> 'DYNGRP'

    set nocount on

    OPEN G_cursor
    FETCH NEXT FROM G_cursor INTO @cStatement
    WHILE (@@FETCH_STATUS <> -1)
    begin
    EXEC (@cStatement)
    FETCH NEXT FROM G_cursor INTO @cStatement
    end
    DEALLOCATE G_cursor
    /*
    ** Do not delete the group to attempt to preserve the permissions already
    ** granted to it.
    */
    use @cCompany
    if exists (select gid from sysusers where name = 'DYNGRP')
    begin
    exec sp_dropgroup DYNGRP
    end
    /*
    ** Recreate the DYNGRP group in all company databases.
    */
    use @cCompany
    if not exists (select name from sysusers where name = 'DYNGRP')
    begin
    exec ("sp_addgroup DYNGRP")
    end

    end
    DEALLOCATE C_cursor

    ______________________________________
    Thanks for any help you have.
    Last edited by scadavidson; 06-17-04 at 15:26.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You could temporarily make the script a system stored procedure (ie master.dbo.sp_MS_GP_Fix), then use sp_MSforeachdb to execute it.

    You could add another cursor to cycle through the databases (using dynamic SQL for the USE statement), and wrap that around the existing script.

    There may be other solutions, but I'm too lazy to think more until you "bless" or refuse one of these.

    -PatP

Posting Permissions

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