Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


IF table exists, DROP TABLE then CREATE TABLE - script not working


IF table exists, DROP TABLE then CREATE TABLE - script not working

Author
Message
jhager
jhager
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 322
Hi all,

This code to change a column name is not working and I'm not able to figure out why:

IF OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuReportCriteria', N'U') IS NOT NULL
   BEGIN;
DROP TABLE dbo.mtzcus_2014_CoreMenuReportCriteria;

      CREATE TABLE dbo.mtzcus_2014_CoreMenuReportCriteria
(
MeasureType VARCHAR(20) NOT NULL
,MeasureOrder TINYINT NOT NULL
,ObjectiveNumber VARCHAR(3) NOT NULL
,ObjectiveName VARCHAR(100)
,CriteriaCertificationNumber VARCHAR(50)
,ExclusionDescription VARCHAR(100)
,Threshold2014Stage2 DECIMAL(3, 0)
,Threshold2014Stage1 DECIMAL(3, 0)
,CONSTRAINT mtzcus_2014_CoreMenuReportCriteria_PK PRIMARY KEY CLUSTERED
( MeasureType, MeasureOrder, ObjectiveNumber )
);
END;

Previous version was:

CREATE TABLE dbo.mtzcus_2014_CoreMenuReportCriteria
(
MeasureType VARCHAR(20) NOT NULL
,MeasureOrder TINYINT NOT NULL
,ObjectiveNumber TINYINT NOT NULL
,ObjectiveName VARCHAR(100)
,CriteriaCertificationNumber VARCHAR(50)
,ExclusionDescription VARCHAR(100)
,Threshold2014Stage2 DECIMAL(3, 0)
,Threshold2011Or2013Stage1 DECIMAL(3, 0)
,CONSTRAINT mtzcus_2014_CoreMenuReportCriteria_PK PRIMARY KEY CLUSTERED
( MeasureType, MeasureOrder, ObjectiveNumber )
);

The error message is:

Msg 207, Level 16, State 1, Line 32
Invalid column name 'Threshold2014Stage1'.

This seems to be because the DROP TABLE never executes or fails (no error message).

Any help is appreciated.

~ Jeff
jhager
jhager
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 322
I found a MS reference that states:

"DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur."

and also:

"A table cannot be changed and then the new columns referenced in the same batch."

Now I just have to figure out how to accomplish this given the restrictions.

Thanks for looking, though.

~ Jeff
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16515 Visits: 16989
It is quite simple. Don't do this in the same batch.


IF OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuReportCriteria', N'U') IS NOT NULL
   DROP TABLE dbo.mtzcus_2014_CoreMenuReportCriteria;

CREATE TABLE dbo.mtzcus_2014_CoreMenuReportCriteria
(
   MeasureType VARCHAR(20) NOT NULL
   ,MeasureOrder TINYINT NOT NULL
   ,ObjectiveNumber VARCHAR(3) NOT NULL
   ,ObjectiveName VARCHAR(100)
   ,CriteriaCertificationNumber VARCHAR(50)
   ,ExclusionDescription VARCHAR(100)
   ,Threshold2014Stage2 DECIMAL(3, 0)
   ,Threshold2014Stage1 DECIMAL(3, 0)
   ,CONSTRAINT mtzcus_2014_CoreMenuReportCriteria_PK PRIMARY KEY CLUSTERED
   ( MeasureType, MeasureOrder, ObjectiveNumber )
);



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47091 Visits: 44345
Why drop and create a table to change a column name? Why not use sp_rename?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


jhager
jhager
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 322
The script is used to deploy code and data objects to our customers, and contains several objects entirely contained within a TRY/CATCH block. The script is designed to be run as a whole whether or not the customer has run it before.

The purpose of the script is not to rename the column, but rather to build the necessary objects. We just want to change the name as part of the process.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47091 Visits: 44345
Then you're going to have to use dynamic SQL.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


jhager
jhager
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 322
I was hoping to avoid that, but ya gotta do what ya gotta do.

Thanks Gail,

~ Jeff
jhager
jhager
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 322
This is really beginning to frustrate me.

IF OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuAuditDetails', N'U') IS NOT NULL
AND EXISTS ( SELECT TOP 1
'X'
FROM
sys.columns
WHERE
name = N'Threshold2011Or2013Stage1'
AND [object_id] = OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuAuditDetails') )
BEGIN;
                  /* Rename two columns */
EXECUTE sys.sp_rename
'dbo.mtzcus_2014_CoreMenuAuditDetails.Threshold2011Or2013Stage1'
,'dbo.mtzcus_2014_CoreMenuAuditDetails.Threshold2014Stage1';
EXECUTE sys.sp_rename
'dbo.mtzcus_2014_CoreMenuAuditDetails.MetThreshold2011Or2013Stage1'
,'dbo.mtzcus_2014_CoreMenuAuditDetails.MetThreshold2014Stage1';
END;
ELSE
IF OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuAuditDetails', N'U') IS NULL
BEGIN;
EXECUTE(N'
               CREATE TABLE dbo.mtzcus_2014_CoreMenuAuditDetails
(
AuditID UNIQUEIDENTIFIER NOT NULL
,MeasureName VARCHAR(100)
,CriteriaCertificationNumber VARCHAR(50)
,ObjectiveNumber VARCHAR(3)
,ObjectiveName VARCHAR(100)
,Denominator DECIMAL
,Numerator DECIMAL
,Percentage DECIMAL(5, 2)
,Threshold2014Stage2 DECIMAL(3, 0)
,MetThreshold2014Stage2 CHAR(1)
,Threshold2014Stage1 DECIMAL(3, 0)
,MetThreshold2014Stage1 CHAR(1)
,ExclusionDescription VARCHAR(100)
,MetExclusion CHAR(1)
,SelfAttestation CHAR(1)
,CONSTRAINT mtzcus_2014_CoreMenuAuditDetailsFK FOREIGN KEY ( AuditID ) REFERENCES dbo.mtzcus_2014_CoreMenuAuditRun ( AuditID )
);');

When the table does NOT exist, the last section of code "executes" (I put a SELECT 'X' before the dynamic statement, which is displayed) but the table is NOT created (I immediately run SELECT TOP 1 * FROM dbo.mtzcus_2014_CoreMenuAuditDetails and get error "Invalid object name 'dbo.mtzcus_2014_CoreMenuAuditDetails'."

Am I missing something obvious here, or maybe something not so obvious?

Thanks,

Jeff
Abu Dina
Abu Dina
Right there with Babe
Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)

Group: General Forum Members
Points: 719 Visits: 3323
A little bit of formatting + fix ;-)

You don't need to include the schema name when you call the OBJECT_ID function:

OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuAuditDetails', N'U')



should be just

OBJECT_ID(N'mtzcus_2014_CoreMenuAuditDetails', N'U')





IF OBJECT_ID(N'mtzcus_2014_CoreMenuAuditDetails', N'U') IS NOT NULL
AND EXISTS ( SELECT TOP 1 FROM sys.columns
                     WHERE name = N'Threshold2011Or2013Stage1'
                     AND [object_id] = OBJECT_ID(N'mtzcus_2014_CoreMenuAuditDetails') )
   BEGIN;
      /* Rename two columns */
      EXECUTE sys.sp_rename
      'dbo.mtzcus_2014_CoreMenuAuditDetails.Threshold2011Or2013Stage1'
      ,'dbo.mtzcus_2014_CoreMenuAuditDetails.Threshold2014Stage1';
      EXECUTE sys.sp_rename
      'dbo.mtzcus_2014_CoreMenuAuditDetails.MetThreshold2011Or2013Stage1'
      ,'dbo.mtzcus_2014_CoreMenuAuditDetails.MetThreshold2014Stage1';
   END;
ELSE IF OBJECT_ID(N'mtzcus_2014_CoreMenuAuditDetails', N'U') IS NULL
   BEGIN;
      EXECUTE(N'
      CREATE TABLE dbo.mtzcus_2014_CoreMenuAuditDetails
      (
      AuditID UNIQUEIDENTIFIER NOT NULL
      ,MeasureName VARCHAR(100)
      ,CriteriaCertificationNumber VARCHAR(50)
      ,ObjectiveNumber VARCHAR(3)
      ,ObjectiveName VARCHAR(100)
      ,Denominator DECIMAL
      ,Numerator DECIMAL
      ,Percentage DECIMAL(5, 2)
      ,Threshold2014Stage2 DECIMAL(3, 0)
      ,MetThreshold2014Stage2 CHAR(1)
      ,Threshold2014Stage1 DECIMAL(3, 0)
      ,MetThreshold2014Stage1 CHAR(1)
      ,ExclusionDescription VARCHAR(100)
      ,MetExclusion CHAR(1)
      ,SelfAttestation CHAR(1)
      ,CONSTRAINT mtzcus_2014_CoreMenuAuditDetailsFK FOREIGN KEY ( AuditID ) REFERENCES dbo.mtzcus_2014_CoreMenuAuditRun ( AuditID )
      Wink;');
   END
   



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


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
jhager
jhager
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 322
Other than missing the END statement (which exists later on in my script), I'm still not seeing anything incorrect.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search