SMS Query complains about the backslash in the following statement.

ALTER SCHEMA dbo TRANSFER OWRD\julainih.AuthorList

The old trick of doubling the backslash

ALTER SCHEMA dbo TRANSFER OWRD\\julainih.AuthorList

doesn't work either.

What do I need to change to let this statement run ??

Thanks in advance.

Barry
in Oregon

(late in day - brain death has fully set in - thanks for understanding)

Hello frostbb,

Users don't actually own objects (other than schemas) in SQL 2005, so you've
got yourself an interesting schema name there. The normal escape in T-SQL
is [], so this works:

drop table [OWRD\julainih].t1
drop table ginger.t1
go
drop schema [OWRD\julainih]
drop schema ginger
go
create schema ginger
go
create schema [OWRD\julainih]
go
create table [OWRD\julainih].t1(id tinyint)
insert into [OWRD\julainih].t1 values (1)
go
alter schema ginger
transfer [OWRD\julainih].t1
go
select * from ginger.t1
go

Cheers,
kt

Kent,

Awsome, just what I was looking for. Thanks for taking the time to post a
reply. Your quick response is very much appreciated.

Yup, I try to keep db objects under .dbo ownership. Unfortunately,
TrialWorks, one of our 3rd party 'lawyer' db's is moving form a primarily MS
Access based platform to Sql Server 2005. They're obviously still getting
their 'act together' with respect to migrating from MS Access to Sql Server.

Since I wouldn't give them direct (remote) 'sa' privs to our enterprise db
server, they ended up migrating our MS Access db to one of their Sql
Servers and then I 'wired' the .mls & .nds files they created into one of
our instances. Somewhere in the migration progress they created about half
of the systems stored procedures under the 'user' schema ... this worked
fine as long as that user was the only one using the db but yesterday they
asked me to give a 2nd user access and no matter what permissions I set up
the user couldn't attach using their 'front end' application. Drove me nuts
until I realized that the last 100 or so stored procs (natually at the
bottom of the SMS listing) were owned by the user schema.

This morning's going to be dedicated to hunting down all the db objects
owned by [OWRD\julainih] and converting them to .dbo.

Will probably use Steve Schofield's technique of creating secondary ALTER
SCHEMA statments to convert all the bogus owners. (see "How to change
object schemas in Sql Server 2005"
http://weblogs.asp.net/steveschofield/archive/2005/12/31/434280.aspx)

Thanks again for your help!!

Barry
in Oregon

[quoted text, click to view]