Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
SQL SERVER - Store Procedure
I have a SP in which i need to add one condition to check multiple databases by using sys.dm_database_encryption
when encryption_state is 2 and percentage_completed is 0 in the results it should print the query i will provide.
when encryption_state is 2 and percentage_completed is 0 in the results it should print the query i will provide.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I tried this
DECLARE @Test varchar(10)
SELECT @Test = Database_ID from
sys.dm_database_encryption
WHERE Encryption_state = 2 and complete_percentage = 0
If (@test not NULL)
Begin
PRINT("ALTER DB NAme")
If i have multiple DB's which meets the about. It should PRINT multiple ALTER statements with DB Names.
DECLARE @Test varchar(10)
SELECT @Test = Database_ID from
sys.dm_database_encryption
WHERE Encryption_state = 2 and complete_percentage = 0
If (@test not NULL)
Begin
PRINT("ALTER DB NAme")
If i have multiple DB's which meets the about. It should PRINT multiple ALTER statements with DB Names.
Mind that a variable can store only a single value so you'll never have this working for all databases.
Try this alternative solution:
Try this alternative solution:
SELECT 'ALTER DB ' + DB_NAME(Database_ID)
FROM sys.dm_database_encryption_keys
WHERE Encryption_state = 2 and percent_complete = 0
Basically, I have no idea what you are up-to. The question doesn't shed much light on that. However, I can give you a technically correct answer.
You can do it as follows:
You can do it as follows:
declare @databases nvarchar(max) = ''
SELECT @databases += N'ALTER DB ' + DB_NAME(Database_ID) + '
'
FROM sys.dm_database_encryption_keys
WHERE Encryption_state = 2 and percent_complete = 0
select @databases
Premium Content
You need an Expert Office subscription to comment.Start Free Trial