September 15, 2009 at 3:51 am
Hello Guys,
I was just wondering if someone can help here, I have the code below which runs as part of a batch, but I need to make sure that the IF statement doesn’t get executed if the database does not exists.
I know one possible solution is to wrap this around T-SQL and make it dynamic, but I was wondering if there are other ways of doing this.
Thanks in advance.
IF EXISTS(SELECT * FROM sysdatabases where name = 'PersonProfile')
EXEC sp_executesql N'USE PersonProfile'
IF db_id('PersonProfile') is not NULL
BEGIN
USE PersonProfile
IF NOT EXISTS(SELECT * FROM sysusers where name = 'loginname')
exec sp_grantdbaccess N'loginname',N'loginname'
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
update table set BillCode = 0 where ID <= 2000
END
[/code]
September 15, 2009 at 8:26 am
Any ideas guys ?
September 15, 2009 at 8:38 am
select from sys.databases?
September 15, 2009 at 8:55 am
Steve Jones - Editor (9/15/2009)
select from sys.databases?
That doesnt work, even beforeit checks the sys.databases the compiler already attempts to connect to a database that might not exists, thats where the problem is.
September 15, 2009 at 10:14 am
Put your code into system procedure on master database and execute it like this:
IF EXISTS(SELECT * FROM sysdatabases where name = 'PersonProfile')
EXEC PersonProfile.dbo.sp_MyGrantPermissionProc
If it does not work like this make it dynamic.
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply