Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1160

SS2005, test that object exists / OBJECT_ID() NOT NULL, reliable?

Show all headers | View raw

I have had an odd error message today from 
"Microsoft SQL Server 2005 - 9.00.4035.00 (X64)"
that suggests that the following test did not work 
as intended, which was to generate and execute 
dynamic SQL code to create a table (by SELECT INTO) 
if the table does not already exist.  The table 
does exist, so I would expect OBJECT_ID(...) NOT NULL.
The "path" consists of a database name and ".[dbo].",
I'm reasonably sure.  And apparently it works /now/.

Should I be using a different "object there" test?

----

SET @tblPeriod_fq = 
        @pathTarget_q + N'[period]'

IF ( OBJECT_ID(@tblPeriod_fq) IS NULL )
BEGIN
...

----

I also don't think that the following applies in my case:
"In SQL Server 2005, a user can only view the metadata of 
securables that the user owns or on which the user has been
granted permission.  This means that metadata-emitting, 
built-in functions such as OBJECT_ID may return NULL if 
the user does not have any permission on the object."
(From Books Online.)

Thank you!

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

SS2005, test that object exists / OBJECT_ID() NOT NULL, reliable? rja.carnegie@gmail.com - 2012-07-02 09:35 -0700
  Re: SS2005, test that object exists / OBJECT_ID() NOT NULL, reliable? Erland Sommarskog <esquel@sommarskog.se> - 2012-07-02 19:09 +0200
    Re: SS2005, test that object exists / OBJECT_ID() NOT NULL, reliable? rja.carnegie@gmail.com - 2012-07-05 06:02 -0700
      Re: SS2005, test that object exists / OBJECT_ID() NOT NULL, reliable? Erland Sommarskog <esquel@sommarskog.se> - 2012-07-05 23:45 +0200

csiph-web