May 5, 2015 at 7:50 pm
Can i get help for this below script , facing syntax errors.
================================================
DECLARE @strSQL Varchar(MAX) = '';
select @strSQL = "select 'grant execute on ' +
QuoteName(specific_schema) + '.' +
QuoteName(specific_name) + ' To [AD\ABCD]'
from information_schema.routines
where routine_type='PROCEDURE' and routine_schema='MRP'"
SELECT @strSQL;
Execute (@strSQL)
=================================================
May 5, 2015 at 8:34 pm
charipg (5/5/2015)
Can i get help for this below script , facing syntax errors.================================================
DECLARE @strSQL Varchar(MAX) = '';
select @strSQL = "select 'grant execute on ' +
QuoteName(specific_schema) + '.' +
QuoteName(specific_name) + ' To [AD\ABCD]'
from information_schema.routines
where routine_type='PROCEDURE' and routine_schema='MRP'"
SELECT @strSQL;
Execute (@strSQL)
=================================================
Couple of mistakes there, here is a corrected example
😎
--================================================
DECLARE @strSQL Varchar(MAX) = '';
select @strSQL = 'grant execute on ' +
QuoteName(specific_schema) + '.' +
QuoteName(specific_name) + ' To [AD\ABCD]'
from information_schema.routines
where routine_type='PROCEDURE' and routine_schema='MRP'
SELECT @strSQL;
Execute (@strSQL)
--=================================================
Not certain that this will do what you intend it to do, only returns one grant statement regardless of the number of items in the result set, here is an example of XML concatenation that brings back everything.
--================================================
DECLARE @strSQL Varchar(MAX) = '';
SELECT @strSQL =
(SELECT 'grant execute on ' +
QuoteName(specific_schema) + '.' +
QuoteName(specific_name) + ' To [AD\ABCD];' + CHAR(13) + CHAR(10)
from information_schema.routines
WHERE routine_type='PROCEDURE' and routine_schema='MRP'
FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(MAX)')
SELECT @strSQL;
-- uncomment to execute
--Execute (@strSQL)
--=================================================
May 5, 2015 at 10:15 pm
Thank you !!!
May 5, 2015 at 10:32 pm
i tried with both scripts, both scripts returns only one grant statement, but it should return more grant statements.
May 5, 2015 at 10:42 pm
charipg (5/5/2015)
i tried with both scripts, both scripts returns only one grant statement, but it should return more grant statements.
What does this query return?
😎
SELECT 'grant execute on ' +
QuoteName(specific_schema) + '.' +
QuoteName(specific_name) + ' To [AD\ABCD];' + CHAR(13) + CHAR(10)
from information_schema.routines
WHERE routine_type='PROCEDURE' and routine_schema='MRP'
May 6, 2015 at 9:35 am
it returns 21 rows.
May 6, 2015 at 10:05 am
charipg (5/6/2015)
it returns 21 rows.
Try copy the single row from the second query and paste it to a text editor, the full statement should be there.
😎
May 6, 2015 at 10:30 am
Sorry Erikur !!!
It is working as expected.
May 6, 2015 at 10:33 am
No worries, enjoy!
😎
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply