Problem Transact-SQL Statement -- sql server clustering

RM
1/6/2005 10:19:05 AM

1. It generates code to change owner of all user tables in the database.

2. You can put table name and owner in quotes like your code does or not
like following:

select 'EXECUTE sp_changeobjectowner ' + name + ', dbo' from sysobjects
where type = 'U'

â€œAllen Yuâ€ç¼–å†™ï¼š

[quoted text, click to view]

RM
1/6/2005 10:39:02 AM

By the way, if existing owner is not dbo, you need following code to change
owner to dbo:

select 'EXECUTE sp_changeobjectowner [' + user_name(uid) + '.' + name + '],
dbo' from sysobjects where type = 'U'

â€œAllen Yuâ€ç¼–å†™ï¼š

[quoted text, click to view]

Sylvain Lafontaine
1/6/2005 12:26:57 PM

The online documentation says that the stored procedure sp_changeobjectowner
will return 0 for a successfull completion or 1 for a failure; this is what
will be returned by the select command.

For the quotation marks, in SQL, the string delimiter is the single quote,
', not the double quote " as in VBA (however, one option for SQL give you
the right to use the double quote also as the string delimiter; which raises
a big deal of confusion for newbies) and you must use two single quotes, '',
to enclose a single quote into a string. You can follow the process with
the following example:

EXEC sp_changeobjectowner 'MyName', 'dbo'

to:
Select 'EXEC sp_changeobjectowner ''MyName'', ''dbo'''

to:
Select 'EXEC sp_changeobjectowner ''' + name + ''', ''dbo'''

The second statement can be rewritten this way, to make it easier to
distingued between the levels for the quotes:

Select ' EXEC sp_changeobjectowner ''MyName'', ''dbo'' '

S. L.

[quoted text, click to view]

Allen Yu
1/7/2005 12:36:32 AM

I've come across a Transact-SQL statement as follows:

-- Statements start here

select 'EXECUTE sp_changeobjectowner ''' + name + ''', ''dbo''' from
sysobjects where type = 'U'

-- end here

Questions:

(1) How should the 'select' statement be interpreted?

(2) What are the rules for using the quotation marks: ''', '',,, etc?

Thanks!

Allen

Allen Yu
1/10/2005 11:11:15 PM

Thanks!

The following statement is understood:
Select 'EXEC sp_changeobjectowner ''MyName'', ''dbo'''

But, what is the rule for converting "MyName" to ''' + Name + ''' ?

Allen

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> ¼¶¼g
©ó¶l¥ó·s»D:OF#auTB9EHA.2804@TK2MSFTNGP15.phx.gbl...
[quoted text, click to view]

RM
1/11/2005 6:21:02 AM

You can concatenate strings with select statement like this:

select 'this is first name ' + au_fname from pubs..authors

If you like to put name in quotes, can use this:

select 'this is first name ''' + au_fname + '''' from authors

In your case, just replace 'this is first name ''' with 'EXEC
sp_changeobjectowner ''' then get object name from sysobjects table.

RM
1/12/2005 5:59:05 AM

Don't have to use cursor, the select statement here generates those code.
Just run those result code.

â€œHeribertoâ€ç¼–å†™ï¼š

[quoted text, click to view]

Heriberto
1/12/2005 10:32:14 AM

Allen as far as I understand you should use cursor

DECLARE @OBJNAME sysname
DECLARE CUROBJNAME CURSOR READ_ONLY FAST_FORWARD FOR SELECT name FROM
sysobjects where type = 'U'
OPEN CUROBJNAME
FETCH NEXT FROM CUROBJNAME INTO @OBJNAME
WHILE @@FETCH_STATUS = 0
BEGIN

EXECUTE sp_changeobjectowner @OBJNAME, 'dbo'
FETCH NEXT FROM CUROBJNAME INTO @OBJNAME

END

CLOSE CUROBJNAME
DEALLOCATE CUROBJNAME

Heriberto

"Allen Yu" <allenyu@netvigator.com> escreveu na mensagem
news:%232hbjay9EHA.2788@TK2MSFTNGP15.phx.gbl...
[quoted text, click to view]

Allen Yu
1/13/2005 9:29:09 PM

Thanks! That's another solution to my problem!
Allen

"Heriberto" <heriberto@mathemathics.com.br> ¼¶¼g©ó¶l¥ó·s»D
:e8b2PJK#EHA.600@TK2MSFTNGP09.phx.gbl...
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server clustering

sql server clustering : Problem Transact-SQL Statement