Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

IF table exists, DROP TABLE then CREATE TABLE - script not working Expand / Collapse
Author
Message
Posted Friday, November 1, 2013 8:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 8, 2014 6:54 AM
Points: 58, Visits: 239
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

Post #1510619
Posted Friday, November 1, 2013 9:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 8, 2014 6:54 AM
Points: 58, Visits: 239
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
Post #1510630
Posted Friday, November 1, 2013 9:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:32 PM
Points: 13,302, Visits: 12,168
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)
Post #1510635
Posted Friday, November 1, 2013 9:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 42,822, Visits: 35,953
Why drop and create a table to change a column name? Why not use sp_rename?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1510641
Posted Friday, November 1, 2013 9:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 8, 2014 6:54 AM
Points: 58, Visits: 239
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.

Post #1510653
Posted Friday, November 1, 2013 9:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 42,822, Visits: 35,953
Then you're going to have to use dynamic SQL.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1510656
Posted Friday, November 1, 2013 10:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 8, 2014 6:54 AM
Points: 58, Visits: 239
I was hoping to avoid that, but ya gotta do what ya gotta do.

Thanks Gail,

~ Jeff
Post #1510675
Posted Monday, November 4, 2013 8:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 8, 2014 6:54 AM
Points: 58, Visits: 239
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
Post #1511145
Posted Monday, November 4, 2013 8:59 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:26 AM
Points: 708, Visits: 3,287
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 )
);');
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
Post #1511153
Posted Monday, November 4, 2013 9:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 8, 2014 6:54 AM
Points: 58, Visits: 239
Other than missing the END statement (which exists later on in my script), I'm still not seeing anything incorrect.
Post #1511165
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse