asked
Curser using dynamic sql
I am tying to get all user using dynamic sql it is not giving any output. Can someone help me writing the correct way DECLARE @Text nvarchar(max) declare @IDbName sysname create table #TempServer(SName sysname) insert into #TempServer select name From sys.databases where database_id not in (1,2,3,4) SET @Text= ' declare CurServer Cursor for select SName from #TempServer open CurServer fetch next from CurServer into @IDbName WHILE @@FETCH_STATUS = 0 BEGIN SELECT ''DATABASE NAME := '' + @IDbName + '' '' + getdate() SELECT ''------- SCRIPT FOR CREATING DATABASE USER ---'' SELECT ''CREATE USER '' + QUOTENAME(DbPrin.name) COLLATE DATABASE_DEFAULT + '' FOR LOGIN '' + QUOTENAME(SerPrin.name ) COLLATE DATABASE_DEFAULT + CASE WHEN DbPrin.default_schema_name IS NULL THEN '''' ELSE '' WITH DEFAULT_SCHEMA= '' + QUOTENAME(DbPrin.default_schema_name) END FROM ['+ @IDbName +' ].sys.database_principals DbPrin JOIN ['+ @IDbName +' ].sys.server_principals SerPrin ON SerPrin.sid = DbPrin.sid WHERE DbPrin.principal_id NOT IN (1,2,3,4) fetch next from CurServer into @IDbNamee end close CurServer deallocate CurServer' drop table #TempServer EXEC (@Text)
What about just not dropping the temp table until you actually execute the query? By dropping the temp table after you declare the string, but before you execute it, you're making it attempt to reference something that's not there.
Yeah, great point. I should have said the same thing. In order to know what you're going to execute, print it out first. Thanks @JohnM.
14 People are following this question.
