object_id function returns null

Hello,
I've been using object_id(object_name) function in some queries to retrieve
tables' ids without problems. However, those queries have started to fail
because object_id function returns null when the table name is like this one
:
'MyPrefix.SampleTable'. I've checked sysobjects and the table exists there
and it has a valid id.
Any idea how to solve this? What are my options? Should I rewrite my queries
and use sysobject.name to perform JOINs?
I'm using SQL Server 2000.
Thanks in advance.Is "MyPrefix" part of the table name or is it the owner of the table. If the
former, you need to
quote it in OBJECT_ID, just the same way as when you refer to the table in a
SELECT statement. But
the recommendation is to stick within the limits of standard identifiers whe
n you name objects
(which among other thing doesn't allow a dot in the name):
CREATE TABLE "My.Table"(c1 int)
--Returns NULL, SQL Server assumes "My" is the owner
SELECT OBJECT_ID('My.Table')
--Returns the object id
SELECT OBJECT_ID('"My.Table"')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jos G" <JGeer@.nospamplease.onobox.com> wrote in message
news:75E956AC-BC7F-4A77-A149-3C0AD2B3E220@.microsoft.com...
> Hello,
> I've been using object_id(object_name) function in some queries to retriev
e
> tables' ids without problems. However, those queries have started to fail
> because object_id function returns null when the table name is like this o
ne:
> 'MyPrefix.SampleTable'. I've checked sysobjects and the table exists there
> and it has a valid id.
> Any idea how to solve this? What are my options? Should I rewrite my queri
es
> and use sysobject.name to perform JOINs?
> I'm using SQL Server 2000.
> Thanks in advance.|||Thank you very much for you help. It works as you said.
I'm experiencing the issue in a customer's database.
Do you think it's safe to replace every call to object_id(table_name) with
object_id('"' + table_name + '"') or do you think I could face any collatera
l
effect?
Thanks again.
"Tibor Karaszi" wrote:

> Is "MyPrefix" part of the table name or is it the owner of the table. If t
he former, you need to
> quote it in OBJECT_ID, just the same way as when you refer to the table in
a SELECT statement. But
> the recommendation is to stick within the limits of standard identifiers w
hen you name objects
> (which among other thing doesn't allow a dot in the name):
> CREATE TABLE "My.Table"(c1 int)
> --Returns NULL, SQL Server assumes "My" is the owner
> SELECT OBJECT_ID('My.Table')
> --Returns the object id
> SELECT OBJECT_ID('"My.Table"')
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jos G" <JGeer@.nospamplease.onobox.com> wrote in message
> news:75E956AC-BC7F-4A77-A149-3C0AD2B3E220@.microsoft.com...
>|||> Do you think it's safe to replace every call to object_id(table_name) with
> object_id('"' + table_name + '"') or do you think I could face any collate
ral
> effect?
It depends. If the "object_name" part has the owner in it, you can't just qu
ote the whole lot, you'd
have to quote each part:
CREATE TABLE "theTable"(c1 int)
--Returns object id
SELECT OBJECT_ID('"theTable"')
--Returns NULL
SELECT OBJECT_ID('"dbo.Table"')
--Should be
SELECT OBJECT_ID('"dbo"."Table"')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jos G" <JGeer@.nospamplease.onobox.com> wrote in message
news:6311228F-E9CD-4304-A3EE-292FAD6E6FFA@.microsoft.com...[vbcol=seagreen]
> Thank you very much for you help. It works as you said.
> I'm experiencing the issue in a customer's database.
> Do you think it's safe to replace every call to object_id(table_name) with
> object_id('"' + table_name + '"') or do you think I could face any collate
ral
> effect?
> Thanks again.
>
> "Tibor Karaszi" wrote:
>