Objects related to DB: object

Showing posts with label object_id. Show all posts

Monday, February 20, 2012

object_id problem

I can use the following successfully:
SELECT OBJECT_ID('myDB..myTable')
but on a linked server I cannot get this working. Is this the right syntax?
Should it work? The server name is box. This gives me a syntax error near
myDB.
Select * From Openquery(box, 'SELECT OBJECT_ID('myDB..myTable')')Try something like this:
DECLARE @.sql NVARCHAR(2000)
SELECT @.sql = 'SELECT OBJECT_ID(''myDB..myTable'')'
PRINT @.sql
EXEC linkedserver..sp_executesql @.sql
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Derek Hart" wrote:

> I can use the following successfully:
> SELECT OBJECT_ID('myDB..myTable')
> but on a linked server I cannot get this working. Is this the right synta
x?
> Should it work? The server name is box. This gives me a syntax error near
> myDB.
> Select * From Openquery(box, 'SELECT OBJECT_ID('myDB..myTable')')
>
>|||You need to double the inner single quotes:
Select * From Openquery(box, 'SELECT OBJECT_ID(''myDB..myTable'')')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Derek Hart" <derekmhart@.yahoo.com> wrote in message news:uiEB$MieGHA.4304@.TK2MSFTNGP05.phx
.gbl...
>I can use the following successfully:
> SELECT OBJECT_ID('myDB..myTable')
> but on a linked server I cannot get this working. Is this the right synta
x? Should it work? The
> server name is box. This gives me a syntax error near myDB.
> Select * From Openquery(box, 'SELECT OBJECT_ID('myDB..myTable')')
>

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
--

object_id function returns null

Object_ID and db_id functions

I am using sql functions with some DMVs as follows
SELECT * FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL,
NULL)
and getting the following error :
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
The statement succed if I use variables as follows :
Declare @.test int
Declare @.test2 int
set @.test = db_id()
set @.test2 = OBJECT_ID('MyDB.dbo.Member2')
SELECT * FROM sys.dm_db_index_physical_stats (@.test, @.test2, NULL, NULL,
NULL)
What is strange is when I executed the first one against AdventureWorks it
works correctly without the need to use variables. It sends back info for al
l
indexes ?......
So am I missing any config param sonewhere
ThanksThis is probably because the database which give you the error isn't in comp
atibility mode 90.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:E0D7F135-ADDE-4C2E-881F-2E84D0BBDDD8@.microsoft.com...
>I am using sql functions with some DMVs as follows
> SELECT * FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL,
> NULL)
> and getting the following error :
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near '('.
> The statement succed if I use variables as follows :
> Declare @.test int
> Declare @.test2 int
> set @.test = db_id()
> set @.test2 = OBJECT_ID('MyDB.dbo.Member2')
> SELECT * FROM sys.dm_db_index_physical_stats (@.test, @.test2, NULL, NULL,
> NULL)
> What is strange is when I executed the first one against AdventureWorks it
> works correctly without the need to use variables. It sends back info for
all
> indexes ?......
> So am I missing any config param sonewhere
> Thanks|||Exact, I changed compatibilityto 90and it worked,thanks a lot
"Tibor Karaszi" wrote:

> This is probably because the database which give you the error isn't in co
mpatibility mode 90.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:E0D7F135-ADDE-4C2E-881F-2E84D0BBDDD8@.microsoft.com...
>

Object_ID and db_id functions

I am using sql functions with some DMVs as follows
SELECT * FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL,
NULL)
and getting the following error :
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
The statement succed if I use variables as follows :
Declare @.test int
Declare @.test2 int
set @.test = db_id()
set @.test2 = OBJECT_ID('MyDB.dbo.Member2')
SELECT * FROM sys.dm_db_index_physical_stats (@.test, @.test2, NULL, NULL,
NULL)
What is strange is when I executed the first one against AdventureWorks it
works correctly without the need to use variables. It sends back info for all
indexes ?......
So am I missing any config param sonewhere
ThanksThis is probably because the database which give you the error isn't in compatibility mode 90.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:E0D7F135-ADDE-4C2E-881F-2E84D0BBDDD8@.microsoft.com...
>I am using sql functions with some DMVs as follows
> SELECT * FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL,
> NULL)
> and getting the following error :
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near '('.
> The statement succed if I use variables as follows :
> Declare @.test int
> Declare @.test2 int
> set @.test = db_id()
> set @.test2 = OBJECT_ID('MyDB.dbo.Member2')
> SELECT * FROM sys.dm_db_index_physical_stats (@.test, @.test2, NULL, NULL,
> NULL)
> What is strange is when I executed the first one against AdventureWorks it
> works correctly without the need to use variables. It sends back info for all
> indexes ?......
> So am I missing any config param sonewhere
> Thanks|||Exact, I changed compatibilityto 90and it worked,thanks a lot
"Tibor Karaszi" wrote:
> This is probably because the database which give you the error isn't in compatibility mode 90.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:E0D7F135-ADDE-4C2E-881F-2E84D0BBDDD8@.microsoft.com...
> >I am using sql functions with some DMVs as follows
> >
> > SELECT * FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL,
> > NULL)
> >
> > and getting the following error :
> > Msg 102, Level 15, State 1, Line 1
> > Incorrect syntax near '('.
> >
> > The statement succed if I use variables as follows :
> >
> > Declare @.test int
> > Declare @.test2 int
> > set @.test = db_id()
> > set @.test2 = OBJECT_ID('MyDB.dbo.Member2')
> > SELECT * FROM sys.dm_db_index_physical_stats (@.test, @.test2, NULL, NULL,
> > NULL)
> >
> > What is strange is when I executed the first one against AdventureWorks it
> > works correctly without the need to use variables. It sends back info for all
> > indexes ?......
> >
> > So am I missing any config param sonewhere
> >
> > Thanks
>

Monday, February 20, 2012

object_id problem

object_id function returns null

object_id function returns null

object_id function returns null

object_id function returns null

Object_ID and db_id functions

Object_ID and db_id functions

Objects related to DB

Blog Archive

About Me