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.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 collateral
effect?
Thanks again.
"Tibor Karaszi" wrote:
> 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 when 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 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 when 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 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.|||> 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 collateral
> effect?
It depends. If the "object_name" part has the owner in it, you can't just quote 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...
> 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 collateral
> effect?
> Thanks again.
>
> "Tibor Karaszi" wrote:
>> 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 when 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 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.
>>|||So the name of the table if MyPrefix.SomeTable, therefore MyPrefix is
not the owner right ? Normally, it is supposed to be the owner rather
than part of the name. Anyway, try to use the command with putting the
name in brackets.
OBJECT_ID('[MyPrefix.SampleTable]')
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--