Query

[Soniya]

Hi,

I was connecing to a dbo tabe using sql query code

the dbo name was Ab_cd_ef.dbo and everything run fine.

Now I have another dbo to which i want to connect. the dbo name is
Ab.cd.ef.dbo and it generates an error.(exceeds maximum prefixes
allowed) I cud connect using Access, but if there a way to do this with
excel itself?

thanks

[DM Unseen]

Pls post the actual SQL code whe you have SQL issues. Also name the
Database platform and version and OS as well.

I suspect you use SQL server 2000. and have a name with dots . pls
surround each (part) of an sql identiefier with double quotes or square
brackets when doing an Excel query.

select * from "my.database"."dbo"."my.table"
or select * from [my.databse].[dbo].[my.table]

Access automatically maps illegal characters to legal ones, so
[my.databse].[dbo].[my.table] becomes my_database_dbo_my_table

DM Unseen

[Soniya]

Hi again!

I use
I you use SQL server 2000. and windows XP Pro and Office 2003

my dbo got fout dots in it

when I use

" SELECT * FROM abc_def_ghi.dbo. hkl m"

it works fine

but when i want to use another dbo

" SELECT * FROM abc.def.ghi.dbo. hkl m"
it doesn't work



the problem is extra dots (and it is not underscore)

Thanks for your reply

[DM Unseen]

You cannot have more than 3 dots (normally) because any databse data
object it is of form

<datatabse name, usually can be omitted >.<table owner name, usually
this is dbo, can be omitted>.<table name>

The dots have a formal meaning and cannot be used for anything else
that seperating the different naming elemenst

DM Unseen

[Soniya]

that means eventhough i could get the data using MSAccess I cannot do
it with MSExcel?

I want to use this dbo A.T.F.dbo and table T1

The same server got everything (DBOs and Tables Names) repeated in it
according to year.

so there are A.T.F2003.dbo and having several tables under it and
A.T.F2004.dbo and several tables under it having the same name as in
2003 etc.

So if I avoid dbo name and only take table name it will be confusing
(?) which table under which dbo.

Thanks

[DM Unseen]

Soniya,

In SQL server there are 2 ways to help out with illegal characters,
enclosing table names in strings or square brackets should help you out

examples:

[dbo].[mytable.1]

or

"dbo"."mytable.1"

both work.

BTW how many owners do you have in your database? most databses I know
only have 1 owner i.e. DBO so I normally ommit that.

DM Unseen

[Soniya]

thanks a lot

when I used the square brackets as u suggested it works.



{When I use SQL Server login under options >>Databse I could see around
10 Names listed there.. That means Ten DBOs. It will increase on every
year. I had no problem until now when the DBO names was like
abc_def_ghi.dbo, Suddenly this year i could find they are is using the
format abc.def.ghi.dbo}

thanks for your kind help.