SEO

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!

I answered when you posted the same question a month ago:

http://groups.google.co.uk/group/com...84776f21c04331

I'm not sure why you would want a stored proc to do this since it only
takes three keystrokes to execute the output of a SELECT statement
(cut/paste/execute). If you need to automate it then use OSQL or SQLCMD
both of which are documented in Books Online. But since you haven't got
past the stage of "Why isn't SQL Server identical to Oracle?" maybe you
haven't got round to reading anything yet...

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

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!

Also, you can grant permissions directly on a schema. See Books Online
for details. Example:

GRANT SELECT ON SCHEMA::schema_name TO PSSELECT;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Greetings, David Portas,

First, thank you for responding to both of my postings - I
appreciate it, and I apologize for not responding earlier.

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!

However, I did find out about the 'Filter' function in Enterprise
Studio, so it helps in lowering the scope of tables....

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" <yattwood@yahoo.com> wrote in message
news:1159463990.759729.71050@h48g2000cwc.googlegro ups.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!)

Do you mean something like BCP?


>
> 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 would start with the conceptual sections in BOL. They describe pretty
well how SQL Server works and looks inside much similar to the Concepts
guide of Oracle.

> 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....

Not the worst approach around. At least you have to tackle real world
problems as opposed to those often found in classes / tutorials.

> 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!

As was mentioned: osql.

> 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!)

DTS, BCP you can even use osql for that.

I'm sorry, but somehow I still have the feeling that you grunt about
having to work with SQL Server. It's a quite different beast than
Oracle but it definitively has its merits. If you look at Oracle 10's
Enterprise Manager / Database Control you'll find a lot features that
improve manageability - things present in SQL Server for several years.

Kind regards

robert

Greetings, Robert Klemme,

Ah, I have: Oracle 7.3.4.4 on AIX 4.3
Oracle 8.1.7.4 on HP-UX 11.11
Oracle 9.2.0.5 on Windows 2000
Oracle 9.2.0.5 on AIX 5.3
SQLServer 2000 on Windows Terminal Server 2000
SQLServer 2005 on Windows Server 2003

I just figured out what David Portas meant about "cut-and-paste", so,
I'm doing things in SQLServer 2005 with a little more confidence
now....TI've written scripts to generate SQL for the many tables I
have, then right-clicked, cut-and-paste - volia!. The MS SQLServer 2000
Library has been invaluable, as well .

I just found out I'm being given another SQLServer 2005 system, a
brand-new one to support, and likely another one. Soooo - I'm
learning.

I am coming to appreciate more things about SQL Server - I will try
and play, er, test out the DTS and BCP. However, I still love the
Oracle Export/Import utilities!

Thanks - Y. Attwood ('a_dba_used_to_oracle')