a_dba_used_to_oracle wrote:
> I give up - will someone please tell me why in _Oracle_ I can do
> something like:
>
>
> select 'CREATE PUBLIC SYNONYM '||table_name||' FOR
> SYSADM.'||TABLE_NAME||';'
> from dba_tables where owner='SYSADM';
>
> and get:
>
> CREATE PUBLIC SYNONYM CHAINED_ROWS FOR SYSADM.CHAINED_ROWS;
> CREATE PUBLIC SYNONYM CH_CAP FOR SYSADM.CH_CAP;
> CREATE PUBLIC SYNONYM PSACTIVITYDEFN FOR SYSADM.PSACTIVITYDEFN;
> CREATE PUBLIC SYNONYM PSACTIVITYDEL FOR SYSADM.PSACTIVITYDEL;
>
> etc, etc, etc, for ALL tables owned by SYSADM
>
> However, in SQL Server, I simply CANNOT write:
>
> select 'grant select, insert, update, delete on '+table_name+ ' to
> psselect'
> from INFORMATION_SCHEMA.TABLES
> where table_name like '%PS%'
>
> and have it write out to a SQL script file that can be executed!!!!
>
> I have tried something like the following:
>
> create procedure count_ps_tables @pscount int OUTPUT
> AS
> select table_name from INFORMATION_SCHEMA.TABLES
> where table_name like '%PS%'
> set @pscount=@@ROWCOUNT
> return(0)
> exec count_ps_tables @t_count OUTPUT
> begin
> while @t_count !=0
> declare @grant_stmt varchar(133)
> select @grant_stmt ='grant select on '+table_name+ ' to PSSELECT'
> from INFORMATION_SCHEMA.TABLES
> where table_name like '%PS%'
> order by table_name
> print @grant_stmt
> exec (@grant_stmt)
> set @t_count = @t_count - 1
> end
>
> but it _does not work_
>
> If anyone can tell me - I would be ever so grateful!

a_dba_used_to_oracle wrote:
> I give up - will someone please tell me why in _Oracle_ I can do
> something like:
>
>
> select 'CREATE PUBLIC SYNONYM '||table_name||' FOR
> SYSADM.'||TABLE_NAME||';'
> from dba_tables where owner='SYSADM';
>
> and get:
>
> CREATE PUBLIC SYNONYM CHAINED_ROWS FOR SYSADM.CHAINED_ROWS;
> CREATE PUBLIC SYNONYM CH_CAP FOR SYSADM.CH_CAP;
> CREATE PUBLIC SYNONYM PSACTIVITYDEFN FOR SYSADM.PSACTIVITYDEFN;
> CREATE PUBLIC SYNONYM PSACTIVITYDEL FOR SYSADM.PSACTIVITYDEL;
>
> etc, etc, etc, for ALL tables owned by SYSADM
>
> However, in SQL Server, I simply CANNOT write:
>
> select 'grant select, insert, update, delete on '+table_name+ ' to
> psselect'
> from INFORMATION_SCHEMA.TABLES
> where table_name like '%PS%'
>
> and have it write out to a SQL script file that can be executed!!!!
>
> I have tried something like the following:
>
> create procedure count_ps_tables @pscount int OUTPUT
> AS
> select table_name from INFORMATION_SCHEMA.TABLES
> where table_name like '%PS%'
> set @pscount=@@ROWCOUNT
> return(0)
> exec count_ps_tables @t_count OUTPUT
> begin
> while @t_count !=0
> declare @grant_stmt varchar(133)
> select @grant_stmt ='grant select on '+table_name+ ' to PSSELECT'
> from INFORMATION_SCHEMA.TABLES
> where table_name like '%PS%'
> order by table_name
> print @grant_stmt
> exec (@grant_stmt)
> set @t_count = @t_count - 1
> end
>
> but it _does not work_
>
> If anyone can tell me - I would be ever so grateful!

"a_dba_used_to_oracle" <yattwood@yahoo.com> wrote in message
news:1159463990.759729.71050@h48g2000cwc.googlegroups.com...
>
> I do admit that SQLServer has features superior in some ways to
> Oracle; however - Microsoft: Come up with an Export/Import utility (and
> I don't mean the Bulk Data Transfer or the select....into!)

>
> Thanks - Y. Attwood ('a_dba_used_to_oracle')
>

a_dba_used_to_oracle wrote:
> I have recently purchased: Learning SQL on SQLServer 2005 -
> O'Reilly Books, and I was given a SQL2000 Microsoft Library; I'm
> finding many
> of the things applicable to SQL2005 as well.

> I will probably be sent to a SQLServer class in about a year
> (ha)...actually, I've found with new technologies, there's nothing like
> the "throw-the-DBA-in-the-pool-and-let-her-learn-how-to-swim" - it's
> how I learned IMS, CICS, JCL, TSO, UNIX, Oracle and now SQLServer....

> Oracle DBA's (especially the ones working on UNIX) are very, very
> oriented to writing scripts...I've been using the SQL2005 Enterprise
> Studio, but
> our PeopleSoft 8.9 Financials system has over 70,000 tables!
> Arrrrgh!

> I do admit that SQLServer has features superior in some ways to
> Oracle; however - Microsoft: Come up with an Export/Import utility (and
> I don't mean the Bulk Data Transfer or the select....into!)