How can i Switch database inside procedure in Sybase ASE

[former_member754528]

In SQL Server I am using "use database_name" in exec statement inside procedure..

but Sybase ASE i cant able to change database dynamically...

can anyone help me please

[former_member754528]

I already Tried this way

created sp_exec_cmd procedure to all database.. its work good

but my problem is how to execute all sp_exec_cmd dynamically by database list cursor

if i hard code  database name in procedure call its working fine like you said

Your tried

>> exec testdb..sp__execute_command 'dynamic query';

But This I tried(dynamic database name and dynamic query)

>> exec (@dbname+'..sp__execute_command ''dynamic query''');

--Error--I get error cant use exec command inside exec

[Mark_A_Parsons]

===================

declare @dbname varchar(30),

        @proc   varchar(300),

        @cmd    varchar(16384)

select  @dbname = >>whatever_you_determine<<

select  @proc = @dbname + '..sp__execute_command',

select  @cmd = 'create table t1 (a int, b int)'

select  @cmd = @cmd + space(1) + 'grant select on t1 to public'

exec @proc @cmd

go

===================

[former_member754528]

Thankq It's working.....

[former_member187136]

Srinath,

I tried reproducing your request, I got the below error:

Server 'ASE157', Procedure 'Demo', Line 4:

A USE DATABASE statement is not allowed in a procedure or trigger.

1> 2> Msg 208, Level 16, State 1:

Server 'ASE157', Line 1:

Eg:

create procedure Demo

as

Begin

Use master

go

select * from sysprocesses

go

use pubs2

go

select * from sales

go

END

Regards

Kiran K Adharapuram

[former_member187136]

Srinath,

Instead check this alternate way, may be it will help

create procedure Test

as

select * from pubs2..sales

select * from master..sysprocesses

go

Regards

Kiran K Adharapuram

[former_member754528]

Selecting table i able to provide database name before schema name..

But in some process like Drop and create tigger,index and role i not able to give database name in query..

For executing grant commands by procedure i need to change DB...

SO I need something similar to use database

[Former Member]

Why use a stored procedure for granting permissions?  If you need an automated thing to do it, use a shell script or similar outside of the database unless you want to use Java in the database to do it

jason