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

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

Path	csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!204.52.135.9.MISMATCH!newsfeed.hal-mli.net!feeder1.hal-mli.net!65.115.166.3.MISMATCH!feed.news.qwest.net!mpls-nntp-03.inet.qwest.net!nx02.iad01.newshosting.com!newshosting.com!news-out.readnews.com!transit3.readnews.com!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
From	rja.carnegie@gmail.com
Newsgroups	comp.databases.ms-sqlserver
Subject	SS2005, test that object exists / OBJECT_ID() NOT NULL, reliable?
Date	Mon, 2 Jul 2012 09:35:00 -0700 (PDT)
Organization	http://groups.google.com
Lines	32
Message-ID	<32eaaf26-9cca-4dbc-a4e7-e5bf92635876@googlegroups.com> (permalink)
NNTP-Posting-Host	194.83.173.135
Mime-Version	1.0
Content-Type	text/plain; charset=ISO-8859-1
X-Trace	posting.google.com 1341248561 22286 127.0.0.1 (2 Jul 2012 17:02:41 GMT)
X-Complaints-To	groups-abuse@google.com
NNTP-Posting-Date	Mon, 2 Jul 2012 17:02:41 +0000 (UTC)
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=194.83.173.135; posting-account=dELd-gkAAABehNzDMBP4sfQElk2tFztP
User-Agent	G2/1.0
Xref	csiph.com comp.databases.ms-sqlserver:1160

Show key headers only | 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