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

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

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

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

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

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!

[toc] | [next] | [standalone]

#1161

> 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
> ....
 
What is the odd error message? How did the complete batch look like?

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1162

On Monday, July 2, 2012 6:09:05 PM UTC+1, Erland Sommarskog wrote:
> > 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
> > ....
>  
> What is the odd error message? How did the complete batch look like?

Sorry for the late reply.  That isn't the error
message, it wasn't really odd - just, that table 
"period" already existed, when the code was trying 
to create it.  The odd thing was that it happened -
and then that it didn't happen when I re-ran 
the procedure. 

Below is more of the code that I think generated 
the error message.  The inside part should only
be running after the "OBJECT_ID() IS NOT NULL" 
test, which is why I was thinking - apart from 
some stupid mistake elsewhere in the program - 
OBJECT_ID() might be sometimes unreliable when
used this way - and then I'd have to stop 
laughing at colleagues' other designs for 
"does the object exist" routines.  But maybe 
I'll just stay puzzled.

...and I wonder why we haven't installed 
Microsoft SQL Server 2005 Service Pack 4
(December 2010).

-----------------------------------------

/* Determine fully qualified table name of period table.  Copy if missing.
*/

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

IF ( OBJECT_ID(@tblPeriod_fq) IS NULL )
BEGIN
    /* Copy latest row of [BO-StudNN]..period to create [BO-SurveyNN]..period.
    */
    IF ( @spverbose = 1 ) PRINT 'Creating period table (years)'

    SET @template = 
    N'
    USE @{dbStud};
    DECLARE @year int;
    SET @year = (SELECT MAX(year) FROM period);
    SELECT * INTO @{tblPeriod} FROM period WHERE ( year = @year );
    --X'
    
    SET @workstring = 
            REPLACE(REPLACE(
                      @template
                    , N'@{dbStud}',    @dbStud_q    )
                    , N'@{tblPeriod}', @tblPeriod_fq)
    ;
    
    IF (RIGHT(@workstring, 3) = N'--' + N'X' )
    BEGIN
        PRINT @workstring
        EXEC sp_executesql @workstring
        IF ( @@ERROR <> 0 ) GOTO failure
    END
    ELSE BEGIN
        RAISERROR(N'ERROR - constructing workstring', 16, 1)
        GOTO failure
    END
END

[toc] | [prev] | [next] | [standalone]

#1163

 (rja.carnegie@gmail.com) writes:
> Sorry for the late reply.  That isn't the error
> message, it wasn't really odd - just, that table 
> "period" already existed, when the code was trying 
> to create it.  The odd thing was that it happened -
> and then that it didn't happen when I re-ran 
> the procedure. 
> 
> Below is more of the code that I think generated 
> the error message.  

Since you are not able to recreate it, I cannot say more. There are some
traps that you can walk into, if you do it all in static SQL, because the 
error happens at compile time, but this does not apply here since you use 
dynamic SQL.

Of course one possibility is that two instances of the script ran in 
parallel.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web