Objects related to DB: null

Showing posts with label null. Show all posts

Monday, February 20, 2012

Object_name of object in one database while running in another..

It seems for this to work, I need to be running in the context of an
existing database to get the object name or else it returns null.
select object_name(s2.objectid) session_current_procedure_name
from sys.dm_exec_requests req
cross apply sys.dm_exec_sql_text(req.sql_handle) s2
So how can I get the object name of the object irrespective of which
database I run this query from ?Hassan
Since you can have the same object_id in two different databases, SQL Server
needs to know which database to look in.
If you are running SQL 2005 SP2, you can add a second parameter to
object_name which provides the database_id:
SELECT object_name(object_id, db_id)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
> It seems for this to work, I need to be running in the context of an
> existing database to get the object name or else it returns null.
> select object_name(s2.objectid) session_current_procedure_name
> from sys.dm_exec_requests req
> cross apply sys.dm_exec_sql_text(req.sql_handle) s2
> So how can I get the object name of the object irrespective of which
> database I run this query from ?
>|||Nice..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
> Hassan
> Since you can have the same object_id in two different databases, SQL
> Server needs to know which database to look in.
> If you are running SQL 2005 SP2, you can add a second parameter to
> object_name which provides the database_id:
> SELECT object_name(object_id, db_id)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It seems for this to work, I need to be running in the context of an
>> existing database to get the object name or else it returns null.
>> select object_name(s2.objectid) session_current_procedure_name
>> from sys.dm_exec_requests req
>> cross apply sys.dm_exec_sql_text(req.sql_handle) s2
>> So how can I get the object name of the object irrespective of which
>> database I run this query from ?
>|||I thought so... I've been asking for this feature for years, since Sybase
added it in their System 10. Finally, I made an official request through
Connect, and Microsoft responded to that. So I am happy to take all the
credit for this one!
;-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:eQdytAdTIHA.3916@.TK2MSFTNGP02.phx.gbl...
> Nice..
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
>> Hassan
>> Since you can have the same object_id in two different databases, SQL
>> Server needs to know which database to look in.
>> If you are running SQL 2005 SP2, you can add a second parameter to
>> object_name which provides the database_id:
>> SELECT object_name(object_id, db_id)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It seems for this to work, I need to be running in the context of an
>> existing database to get the object name or else it returns null.
>> select object_name(s2.objectid) session_current_procedure_name
>> from sys.dm_exec_requests req
>> cross apply sys.dm_exec_sql_text(req.sql_handle) s2
>> So how can I get the object name of the object irrespective of which
>> database I run this query from ?
>>
>|||I am sure there's a LOT more in the sql server world for which you can take
credit!! :-)
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uP2ELyjTIHA.4476@.TK2MSFTNGP06.phx.gbl...
>I thought so... I've been asking for this feature for years, since Sybase
>added it in their System 10. Finally, I made an official request through
>Connect, and Microsoft responded to that. So I am happy to take all the
>credit for this one!
> ;-)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:eQdytAdTIHA.3916@.TK2MSFTNGP02.phx.gbl...
>> Nice..
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
>> Hassan
>> Since you can have the same object_id in two different databases, SQL
>> Server needs to know which database to look in.
>> If you are running SQL 2005 SP2, you can add a second parameter to
>> object_name which provides the database_id:
>> SELECT object_name(object_id, db_id)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It seems for this to work, I need to be running in the context of an
>> existing database to get the object name or else it returns null.
>> select object_name(s2.objectid) session_current_procedure_name
>> from sys.dm_exec_requests req
>> cross apply sys.dm_exec_sql_text(req.sql_handle) s2
>> So how can I get the object name of the object irrespective of which
>> database I run this query from ?
>>
>>
>|||The question is what happens when Kalen retires ? We all know she cant do
this forever..
Kalen have you ever thought about how long you are going to do this ?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nqohil8ca9sdb@.corp.supernews.com...
>I am sure there's a LOT more in the sql server world for which you can take
>credit!! :-)
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uP2ELyjTIHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I thought so... I've been asking for this feature for years, since Sybase
>>added it in their System 10. Finally, I made an official request through
>>Connect, and Microsoft responded to that. So I am happy to take all the
>>credit for this one!
>> ;-)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:eQdytAdTIHA.3916@.TK2MSFTNGP02.phx.gbl...
>> Nice..
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
>> Hassan
>> Since you can have the same object_id in two different databases, SQL
>> Server needs to know which database to look in.
>> If you are running SQL 2005 SP2, you can add a second parameter to
>> object_name which provides the database_id:
>> SELECT object_name(object_id, db_id)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It seems for this to work, I need to be running in the context of an
>> existing database to get the object name or else it returns null.
>> select object_name(s2.objectid) session_current_procedure_name
>> from sys.dm_exec_requests req
>> cross apply sys.dm_exec_sql_text(req.sql_handle) s2
>> So how can I get the object name of the object irrespective of which
>> database I run this query from ?
>>
>>
>>
>|||There are so many terrific people answering questions here now, no one would
even realize it if I disappeared quietly into the sunset...
A Mexican beach sounds nice, right about now...
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:eP6syqyTIHA.1212@.TK2MSFTNGP05.phx.gbl...
> The question is what happens when Kalen retires ? We all know she cant do
> this forever..
> Kalen have you ever thought about how long you are going to do this ?
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13nqohil8ca9sdb@.corp.supernews.com...
>>I am sure there's a LOT more in the sql server world for which you can
>>take credit!! :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:uP2ELyjTIHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I thought so... I've been asking for this feature for years, since Sybase
>>added it in their System 10. Finally, I made an official request through
>>Connect, and Microsoft responded to that. So I am happy to take all the
>>credit for this one!
>> ;-)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:eQdytAdTIHA.3916@.TK2MSFTNGP02.phx.gbl...
>> Nice..
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
>> Hassan
>> Since you can have the same object_id in two different databases, SQL
>> Server needs to know which database to look in.
>> If you are running SQL 2005 SP2, you can add a second parameter to
>> object_name which provides the database_id:
>> SELECT object_name(object_id, db_id)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It seems for this to work, I need to be running in the context of an
>> existing database to get the object name or else it returns null.
>> select object_name(s2.objectid) session_current_procedure_name
>> from sys.dm_exec_requests req
>> cross apply sys.dm_exec_sql_text(req.sql_handle) s2
>> So how can I get the object name of the object irrespective of which
>> database I run this query from ?
>>
>>
>>
>>
>|||It kinda sounds good but it's still nice to see you around here ;)
--
Ekrem Önsoy
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OEe%23TC0TIHA.4360@.TK2MSFTNGP06.phx.gbl...
> There are so many terrific people answering questions here now, no one
> would even realize it if I disappeared quietly into the sunset...
> A Mexican beach sounds nice, right about now...
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:eP6syqyTIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> The question is what happens when Kalen retires ? We all know she cant do
>> this forever..
>> Kalen have you ever thought about how long you are going to do this ?
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13nqohil8ca9sdb@.corp.supernews.com...
>>I am sure there's a LOT more in the sql server world for which you can
>>take credit!! :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:uP2ELyjTIHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I thought so... I've been asking for this feature for years, since
>>Sybase added it in their System 10. Finally, I made an official request
>>through Connect, and Microsoft responded to that. So I am happy to take
>>all the credit for this one!
>> ;-)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:eQdytAdTIHA.3916@.TK2MSFTNGP02.phx.gbl...
>> Nice..
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
>> Hassan
>> Since you can have the same object_id in two different databases, SQL
>> Server needs to know which database to look in.
>> If you are running SQL 2005 SP2, you can add a second parameter to
>> object_name which provides the database_id:
>> SELECT object_name(object_id, db_id)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
>>> It seems for this to work, I need to be running in the context of an
>>> existing database to get the object name or else it returns null.
>>>
>>> select object_name(s2.objectid) session_current_procedure_name
>>> from sys.dm_exec_requests req
>>> cross apply sys.dm_exec_sql_text(req.sql_handle) s2
>>>
>>> So how can I get the object name of the object irrespective of which
>>> database I run this query from ?
>>>
>>
>>
>>
>>
>

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_name of object in one database while running in another..

Object_name of object in one database while running in another..

Object_name of object in one database while running in another..

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