SQL Server Performance

"Dynamic" sql and output files

Discussion in 'General Developer Questions' started by cygnus008, Oct 25, 2006.

  1. cygnus008 New Member

    We need to move databases from one server to another quite frequently. We want to use a batch script so that user can execute the script and the process will run. one of the requirements is that the same users and privileges have to be in the target database that were there in the beginning and then after the database has been refreshed. I have everything working except when I extract the users and privileges from the target via the following commands using SQLCMD and with the -o switch

    select 'IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'''+ u.name+''''+')'+' '+
    'EXEC dbo.sp_grantdbaccess N'''+ u.name + ''', N''' + u.name + ''''
    from sysusers u
    where u.status<>0 and u.name <> 'dbo'

    select 'EXEC sp_addrolemember N''' + g.name + ''', N''' + u.name + ''''
    from sysusers u, sysusers g, sysmembers m
    where g.uid = m.groupuid
    and g.issqlrole = 1
    and u.uid = m.memberuid
    and u.name<>'dbo'
    order by 1


    I get an output file like the following with following. Notice the lines with ----s and (9 rows affected)and (15 rows affected). When I go to run the output file these lines are not recognized by the parser/compiler or whatever it is and the script doesn't run.

    Does anyone know of a switch or parameter that can be set that will cause the -o switch to capture only the lines I am specifying. I don't need anything extra.

    TIA
    Larry




    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'ADAPTISNETETL_DEV') EXEC dbo.sp_grantdbaccess N'ADAPTISNETETL_DEV', N'ADAPTISNETETL_DEV'
    IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'ADAPTISNETETL_PROD') EXEC dbo.sp_grantdbaccess N'ADAPTISNETETL_PROD', N'ADAPTISNETETL_PROD'
    IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'ADAPTISNETProgrammers') EXEC dbo.sp_grantdbaccess N'ADAPTISNETProgrammers', N'ADAPTISNETProgrammers'
    IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'ADAPTISNETQCSI DBA - Read Write') EXEC dbo.sp_grantdbaccess N'ADAPTISNETQCSI DBA - Read Write', N'ADAPTISNETQCSI DBA - Read Write'
    IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'ADAPTISNETQCSI MASTERCONFIG - Read') EXEC dbo.sp_grantdbaccess N'ADAPTISNETQCSI MASTERCONFIG - Read', N'ADAPTISNETQCSI MASTERCONFIG - Read'
    IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'ADAPTISNETQCSI Setup Consultants') EXEC dbo.sp_grantdbaccess N'ADAPTISNETQCSI Setup Consultants', N'ADAPTISNETQCSI Setup Consultants'
    IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'ADAPTISNETTEST') EXEC dbo.sp_grantdbaccess N'ADAPTISNETTEST', N'ADAPTISNETTEST'
    IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'ADAPTISNETvraman') EXEC dbo.sp_grantdbaccess N'ADAPTISNETvraman', N'ADAPTISNETvraman'
    IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'inf_metaman') EXEC dbo.sp_grantdbaccess N'inf_metaman', N'inf_metaman'
    (9 rows affected)
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    EXEC sp_addrolemember N'db_accessadmin', N'ADAPTISNETQCSI DBA - Read Write'
    EXEC sp_addrolemember N'db_backupoperator', N'ADAPTISNETQCSI DBA - Read Write'
    EXEC sp_addrolemember N'db_datareader', N'ADAPTISNETETL_DEV'
    EXEC sp_addrolemember N'db_datareader', N'ADAPTISNETETL_PROD'
    EXEC sp_addrolemember N'db_datareader', N'ADAPTISNETProgrammers'
    EXEC sp_addrolemember N'db_datareader', N'ADAPTISNETQCSI MASTERCONFIG - Read'
    EXEC sp_addrolemember N'db_datareader', N'ADAPTISNETQCSI Setup Consultants'
    EXEC sp_addrolemember N'db_datareader', N'ADAPTISNETTEST'
    EXEC sp_addrolemember N'db_datareader', N'ADAPTISNETvraman'
    EXEC sp_addrolemember N'db_datawriter', N'ADAPTISNETETL_DEV'
    EXEC sp_addrolemember N'db_datawriter', N'ADAPTISNETETL_PROD'
    EXEC sp_addrolemember N'db_datawriter', N'ADAPTISNETProgrammers'
    EXEC sp_addrolemember N'db_datawriter', N'ADAPTISNETQCSI Setup Consultants'
    EXEC sp_addrolemember N'db_datawriter', N'ADAPTISNETTEST'
    EXEC sp_addrolemember N'db_datawriter', N'ADAPTISNETvraman'
    (15 rows affected)
  2. khtan New Member

    set nocount on


    KH
  3. cygnus008 New Member

    Thank you that was exactly what was needed.

    Larry

Share This Page