Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Stored Proc Code

4 posts in General Discussion Last posting was on 2010-05-19 16:10:09.0Z
KRV Posted on 2010-05-19 15:01:44.0Z
Sender: 49a6.4bf3fb8b.1804289383@sybase.com
From: KRV
Newsgroups: sybase.public.ase.general
Subject: Stored Proc Code
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4bf3fd58.4a09.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="-=_forums-1-dub4bf3fd58"
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 19 May 2010 08:01:44 -0700
X-Trace: forums-1-dub 1274281304 10.22.241.41 (19 May 2010 08:01:44 -0700)
X-Original-Trace: 19 May 2010 08:01:44 -0700, 10.22.241.41
Lines: 58
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29228
Article PK: 78461

Hi,

I have written a stored proc (attached) procedure performs
grant permission on all objects for a particular role and
for a database.

The scripts compiles and creates the proc fine, run time it
gives error @ this line

select @em = 'grant select on ' + @obj + ' to test'

If i change the line as

select @em = "grant select on " + @obj + " to test"

it wont execute, obviously using the " quotes at the
starting.

Please help in resolving the problem

Thanks in advance
KRV

create procedure pr_grant
as
begin


declare @dbname varchar(30),
@return int,
@proc varchar(16000)

declare sel_cur cursor for

select name from master..sysdatabases
where name = 'KRV' for read only

open sel_cur

fetch sel_cur into @dbname

while (@@sqlstatus <> 2)
begin

select @proc = "create procedure pr_grant_select
as

declare @obj varchar(30),
@em varchar(999)

use @dbname

declare grant_cur cursor for
select name from sysobjects where type in ('U','V')

open grant_cur
fetch grant_cur into @obj
while @@sqlstatus <> 2
begin
select @em = "grant select on " + @obj + " to test"
exec (@em)

fetch grant_cur into @obj
end
close grant_cur
deallocate cursor grant_cur "

exec @proc
exec ("exec pr_grant_select")
exec ("drop procedure pr_grant_select")

fetch sel_cur into @dbname
end

close sel_cur

deallocate cursor sel_cur

end


Sherlock, Kevin [TeamSybase] Posted on 2010-05-19 15:39:45.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4bf3fd58.4a09.1681692777@sybase.com>
Subject: Re: Stored Proc Code
Lines: 34
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bf40641$1@forums-1-dub>
Date: 19 May 2010 08:39:45 -0700
X-Trace: forums-1-dub 1274283585 10.22.241.152 (19 May 2010 08:39:45 -0700)
X-Original-Trace: 19 May 2010 08:39:45 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29229
Article PK: 78463

Try:

set quoted_identifier off

before using the double quotes.

You might also want to post the error message.

<KRV> wrote in message news:4bf3fd58.4a09.1681692777@sybase.com...
> Hi,
>
> I have written a stored proc (attached) procedure performs
> grant permission on all objects for a particular role and
> for a database.
>
> The scripts compiles and creates the proc fine, run time it
> gives error @ this line
>
> select @em = 'grant select on ' + @obj + ' to test'
>
> If i change the line as
>
> select @em = "grant select on " + @obj + " to test"
>
> it wont execute, obviously using the " quotes at the
> starting.
>
> Please help in resolving the problem
>
> Thanks in advance
> KRV
>


KRV Posted on 2010-05-19 16:10:09.0Z
Sender: 4b95.4bf405e2.1804289383@sybase.com
From: KRV
Newsgroups: sybase.public.ase.general
Subject: Re: Stored Proc Code
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4bf40d61.4ca4.1681692777@sybase.com>
References: <4bf40641$1@forums-1-dub>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="-=_forums-1-dub4bf40d61"
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 19 May 2010 09:10:09 -0700
X-Trace: forums-1-dub 1274285409 10.22.241.41 (19 May 2010 09:10:09 -0700)
X-Original-Trace: 19 May 2010 09:10:09 -0700, 10.22.241.41
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29231
Article PK: 78465

made some changes, script is working fine. Needs small
tuning.

create procedure pr_grant
as
begin


declare @dbname varchar(30),
@return int,
@proc varchar(16000)

create table #t(sql varchar(999))

declare sel_cur cursor for

select name from master..sysdatabases
where name = 'KRV' for read only

open sel_cur

fetch sel_cur into @dbname

while (@@sqlstatus <> 2)
begin

select @proc = "create procedure pr_grant_select
as

declare @em varchar(999),
@obj varchar(30)

declare grant_cur cursor for
select name from sysobjects where type in ('U','V')

open grant_cur
fetch grant_cur into @obj
while @@sqlstatus <> 2
begin

select @em = 'grant select on ' + @obj + ' to test'
insert into #t select @em

fetch grant_cur into @obj
end
close grant_cur
deallocate cursor grant_cur "

exec (@proc)
exec ("exec pr_grant_select")
exec ("drop procedure pr_grant_select")

fetch sel_cur into @dbname
end

close sel_cur

deallocate cursor sel_cur

declare @em varchar(999),
@obj varchar(30)

declare cur cursor for
select sql from #t

open cur
fetch cur into @em
while @@sqlstatus <> 2
begin
exec(@em)
fetch cur into @em
end
close cur
deallocate cursor cur

end


KRV Posted on 2010-05-19 16:02:45.0Z
Sender: 4b95.4bf405e2.1804289383@sybase.com
From: KRV
Newsgroups: sybase.public.ase.general
Subject: Re: Stored Proc Code
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4bf40ba5.4c6b.1681692777@sybase.com>
References: <4bf3fd58.4a09.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 19 May 2010 09:02:45 -0700
X-Trace: forums-1-dub 1274284965 10.22.241.41 (19 May 2010 09:02:45 -0700)
X-Original-Trace: 19 May 2010 09:02:45 -0700, 10.22.241.41
Lines: 8
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29230
Article PK: 78466

1. have removed use @dbname, cannot be used within
proc/trigger
2. cannot execute immediate within execute immediate
3. now the code with this change works fine

select @em = 'grant select on ' + @obj + ' to test'

however need to see how to grant directly