Msg 2714

Any idea why I get the following error:
Msg 2714, Level 16, State 1, Line 16
There is already an object named '#errlog' in the database.
when I run the following statement:
IF object_id('tempdb..#errlog') IS NOT NULL
DROP TABLE #errlog
IF @.@.VERSION LIKE '%9.00%'
CREATE TABLE #errlog
(
logdate datetime,
processinfo varchar(50),
logtext varchar(2000)
)
ELSE
CREATE TABLE #errlog
(
errorlog varchar(2000),
c2 int
)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1Yes...even though you have the logic contract, the
statement will be seen as two create table statements
attempting to create tables of the same name.
One option is to wrap each create table #errlog...etc
statement in an EXEC.
IF @.@.VERSION LIKE '%9.00%'
EXEC ('CREATE TABLE #errlog
(
logdate datetime,
processinfo varchar(50),
logtext varchar(2000)
)')
ELSE
EXEC('CREATE TABLE #errlog1
(
errorlog varchar(2000),
c2 int
)')
-Sue
On Mon, 24 Apr 2006 20:51:52 GMT, "cbrichards" <u3288@.uwe>
wrote:
>Any idea why I get the following error:
>Msg 2714, Level 16, State 1, Line 16
>There is already an object named '#errlog' in the database.
>when I run the following statement:
>IF object_id('tempdb..#errlog') IS NOT NULL
> DROP TABLE #errlog
>IF @.@.VERSION LIKE '%9.00%'
> CREATE TABLE #errlog
> (
> logdate datetime,
> processinfo varchar(50),
> logtext varchar(2000)
> )
>ELSE
> CREATE TABLE #errlog
> (
> errorlog varchar(2000),
> c2 int
> )|||Is that because it is a DDL statement that it nees to be wrapped as such?
Sue Hoegemeier wrote:
>Yes...even though you have the logic contract, the
>statement will be seen as two create table statements
>attempting to create tables of the same name.
>One option is to wrap each create table #errlog...etc
>statement in an EXEC.
>IF @.@.VERSION LIKE '%9.00%'
> EXEC ('CREATE TABLE #errlog
> (
> logdate datetime,
> processinfo varchar(50),
> logtext varchar(2000)
> )')
>ELSE
> EXEC('CREATE TABLE #errlog1
> (
> errorlog varchar(2000),
> c2 int
> )')
>-Sue
>>Any idea why I get the following error:
>>Msg 2714, Level 16, State 1, Line 16
>[quoted text clipped - 18 lines]
>> c2 int
>> )
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||In the original version you had, when parsing or compiling
the query the engine will see two distinct create table
statements trying to create a table of the same name.
If you wrap it in an exec, the engine will use deferred
resolution.
-Sue
On Mon, 24 Apr 2006 22:31:59 GMT, "cbrichards via
SQLMonster.com" <u3288@.uwe> wrote:
>Is that because it is a DDL statement that it nees to be wrapped as such?
>Sue Hoegemeier wrote:
>>Yes...even though you have the logic contract, the
>>statement will be seen as two create table statements
>>attempting to create tables of the same name.
>>One option is to wrap each create table #errlog...etc
>>statement in an EXEC.
>>IF @.@.VERSION LIKE '%9.00%'
>> EXEC ('CREATE TABLE #errlog
>> (
>> logdate datetime,
>> processinfo varchar(50),
>> logtext varchar(2000)
>> )')
>>ELSE
>> EXEC('CREATE TABLE #errlog1
>> (
>> errorlog varchar(2000),
>> c2 int
>> )')
>>-Sue
>>Any idea why I get the following error:
>>Msg 2714, Level 16, State 1, Line 16
>>[quoted text clipped - 18 lines]
>> c2 int
>> )|||Why does it see "two distinct create table statements" when it is within an
IF construct? Truly their are two create statements, but the IF is
conditional, correct?
Sue Hoegemeier wrote:
>In the original version you had, when parsing or compiling
>the query the engine will see two distinct create table
>statements trying to create a table of the same name.
>If you wrap it in an exec, the engine will use deferred
>resolution.
>-Sue
>>Is that because it is a DDL statement that it nees to be wrapped as such?
>[quoted text clipped - 25 lines]
>> c2 int
>> )
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||On Wed, 26 Apr 2006 17:19:57 GMT, cbrichards via SQLMonster.com wrote:
>Why does it see "two distinct create table statements" when it is within an
>IF construct? Truly their are two create statements, but the IF is
>conditional, correct?
Hi cbrichards,
At parse and compile time, the batch is scanned from top to bottom,
without checking conditional execution. This check sees two CREATE TABLE
statements and regards that as an error condition.
--
Hugo Kornelis, SQL Server MVP|||It doesn't know the path for the logic branch until run time
- not at parse or compile time. Consequently it will see two
different create table statements of the same name.
-Sue
On Wed, 26 Apr 2006 17:19:57 GMT, "cbrichards via
SQLMonster.com" <u3288@.uwe> wrote:
>Why does it see "two distinct create table statements" when it is within an
>IF construct? Truly their are two create statements, but the IF is
>conditional, correct?
>Sue Hoegemeier wrote:
>>In the original version you had, when parsing or compiling
>>the query the engine will see two distinct create table
>>statements trying to create a table of the same name.
>>If you wrap it in an exec, the engine will use deferred
>>resolution.
>>-Sue
>>Is that because it is a DDL statement that it nees to be wrapped as such?
>>[quoted text clipped - 25 lines]
>> c2 int
>> )