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!

What is the odd error message? How did the complete batch look like?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

On Monday, July 2, 2012 6:09:05 PM UTC+1, Erland Sommarskog wrote:
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

(rja.carnegie (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) 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