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

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

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

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | 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