Truncate does not truncate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Truncate does not truncate

Author

Topic

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2013-05-16 : 11:36:57

Hi,

The SP in our application is suppose to truncate tables and when it is run it says "Command Completed Successfully" but it doesn't really truncate any thing.

This is what it does:

1. It takes a table list from the function - fn_Get_MYDB_Subset_TableList
2. Goes through the loop and truncate the tables from the above function.
You can see there is a print command and it does not get printed either. That means, it is not going through the loop. Can someone please spot what is wrong with it?


USE [DESTINATION_DB]
GO

/****** Object:  StoredProcedure [dbo].[sp_Cleanup_MYDB_Subset_Tables]    Script Date: 05/16/2013 11:24:47 ******/
SET ANSI_NULLS ON
GO

CREATE  PROCEDURE [dbo].[sp_Cleanup_MYDB_Subset_Tables](@idFirm int, @idBuild int = null)  
AS
BEGIN 
    
    if (@@SERVERNAME = 'DESTINATION_SERVER')
			return;

    Declare	@vSourceTable	nvarchar(255)
	,@vTableStatus			tinyint
	,@sql	varchar(2000)
	
--BEGIN TRY
    -- drop all FOREIGN KEYs before clean up

	while(exists(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
	begin
		SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
		+ '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
		FROM information_schema.table_constraints
		WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
		exec (@sql)
	end
    
    
	declare c cursor for 
	Select TableName, TableStatus 
		from dbo.fn_Get_MYDB_Subset_TableList(@idFirm)
		   order by id desc

		
	open c

	fetch next from c into @vSourceTable, @vTableStatus

	While @@fetch_status =0 BEGIN
	    
		Set @sql = 'truncate table ' + @vSourceTable
		print @sql

		exec (@sql)
	
		
		fetch next from c into @vSourceTable, @vTableStatus

	END

	close c
	deallocate c



return 

END

GO

The function:


CREATE  FUNCTION [dbo].[fn_Get_MYDB_Subset_TableList](@idFirm int
)  
Returns   @TableList Table  (
	id [int] IDENTITY(1,1) NOT NULL  primary key clustered
	,TableName		varchar(100)
	,TableStatus	TINYINT
	,DateCopied		datetime

) 
AS
BEGIN 

if (@idFirm = 576)
begin

Insert into @TableList
	(TableName, TableStatus)
	--Tables have dependency
	SELECT 'SharkAdvisor', 2 UNION
	SELECT 'SharkBranch', 2 UNION
	SELECT 'SharkRegion', 2 UNION	
	SELECT 'SharkFirm', 2 UNION
	SELECT 'SharkMeasure', 3

Insert into @TableList
	(TableName, TableStatus)
	SELECT 'OctoPusLabel', 3 UNION
	SELECT 'OctoPusReportConfigurationAdvisor', 1 UNION
	SELECT 'OctoPusReportConfigurationBranch', 1 UNION
	SELECT 'OctoPusReportConfigurationRegion', 1 UNION
	SELECT 'OctoPusReportConfigurationFirm', 1 UNION
	
	-- added for ML
	SELECT 'OctoPusReportConfigurationMapping', 1 UNION
	SELECT 'OctoPusInputCodeTranslation', 2 UNION
    	
	SELECT 'SharkHousehold', 2 UNION
	SELECT 'SharkMarket', 3 UNION
	SELECT 'SharkMessage', 3 UNION
	SELECT 'SharkPeerGroup', 3 UNION
	SELECT 'SharkMeasureSummary', 3 UNION
	SELECT 'SharkCodeLevel', 3 UNION
	SELECT 'prcBuild', 1 UNION
	SELECT 'WhaleAdvisor', 1 UNION
	SELECT 'WhaleBranch', 1 UNION
    SELECT 'WhaleRegion', 1 UNION
	SELECT 'WhaleFirm', 1 UNION

	SELECT 'WhaleHousehold', 1 UNION
	--SELECT 'WhaleHouseholdTrend', 1 UNION
	--SELECT 'WhaleHousehold12MonthBack', 1 UNION	
	SELECT 'WhaleHouseholdPricing', 1 UNION

	SELECT 'WhaleMeasureAdvisor', 1 UNION
	SELECT 'WhaleMeasureBranch', 1 UNION
	SELECT 'WhaleMeasureRegion', 1 UNION
    SELECT 'WhaleMeasureFirm', 1 UNION
	
	SELECT 'WhaleMeasureSummary', 1 UNION
	SELECT 'WhaleMeasureSummaryTrend', 1 UNION	
	SELECT 'WhaleMeasureTrendAdvisor', 1 UNION	
	SELECT 'WhaleMeasureTrendBranch', 1 UNION
	SELECT 'WhaleMeasureTrendRegion', 1 UNION
    SELECT 'WhaleMeasureTrendFirm', 1 UNION
	SELECT 'WhaleMessageAdvisor', 1 UNION
	SELECT 'WhaleMessageBranch', 1 UNION
	SELECT 'WhaleMessageRegion', 1 UNION
    SELECT 'WhaleMessageFirm', 1 UNION
    
    -- added for ML
	SELECT 'WhaleMeasurePricingAdvisor', 1 UNION
	SELECT 'WhaleMeasurePricingBranch', 1 UNION	
	SELECT 'WhaleMeasurePricingRegion', 1 UNION	
	SELECT 'WhaleMeasurePricingFirm', 1 UNION
	SELECT 'WhaleFixedIncomeSecurityAdvisor', 1 UNION
    SELECT 'WhaleFixedIncomeSecurityBranch', 1 UNION
	SELECT 'WhaleFixedIncomeSecurityRegion', 1 UNION
	SELECT 'WhaleFixedIncomeSecurityFirm', 1 UNION
	SELECT 'WhaleFBHouseholdAssetBucketAdvisor', 1 UNION
	SELECT 'WhaleFBHouseholdAssetBucketBranch', 1 UNION
    SELECT 'WhaleFBHouseholdAssetBucketRegion', 1 UNION
    SELECT 'WhaleFBHouseholdAssetBucketFirm', 1 UNION
    SELECT 'WhaleTransaction', 1 UNION

    SELECT 'WhaleTopOppAdvisorBranch', 1 UNION
    SELECT 'WhaleTopOppBranchRegion', 1 UNION
    SELECT 'WhaleTopOppBranchFirm', 1 UNION

	SELECT 'SharkPeriodData', 3 UNION
   	-- added for ML
   	SELECT 'WhaleHouseholdTopClient', 3 UNION
   	SELECT 'SharkBranchTier', 3 UNION
	SELECT 'SharkAssetBucket', 3 

end
else if (@idFirm = 16)
begin

Insert into @TableList
	(TableName, TableStatus)
	--Tables have dependency
	SELECT 'SharkAdvisor', 2 UNION
	SELECT 'SharkBranch', 2 UNION
	SELECT 'SharkRegion', 2 UNION	
	SELECT 'SharkFirm', 2 UNION
	SELECT 'SharkMeasure', 3

Insert into @TableList
	(TableName, TableStatus)
	SELECT 'OctoPusLabel', 3 UNION
	SELECT 'OctoPusReportConfigurationAdvisor', 1 UNION
	SELECT 'OctoPusReportConfigurationBranch', 1 UNION
	SELECT 'OctoPusReportConfigurationRegion', 1 UNION
	SELECT 'OctoPusReportConfigurationFirm', 1 UNION
	
	-- added for ML
	--SELECT 'OctoPusReportConfigurationMapping', 1 UNION
    	
	SELECT 'SharkHousehold', 2 UNION
	SELECT 'SharkMarket', 3 UNION
	SELECT 'SharkMessage', 3 UNION
	SELECT 'SharkPeerGroup', 3 UNION
	SELECT 'SharkMeasureSummary', 3 UNION
	SELECT 'SharkCodeLevel', 3 UNION
	SELECT 'prcBuild', 1 UNION
	SELECT 'WhaleAdvisor', 1 UNION
	SELECT 'WhaleBranch', 1 UNION
    SELECT 'WhaleRegion', 1 UNION
	SELECT 'WhaleFirm', 1 UNION

	SELECT 'WhaleHousehold', 1 UNION
	--SELECT 'WhaleHouseholdTrend', 1 UNION
	--SELECT 'WhaleHousehold12MonthBack', 1 UNION	
	SELECT 'WhaleHouseholdPricing', 1 UNION

	SELECT 'WhaleMeasureAdvisor', 1 UNION
	SELECT 'WhaleMeasureBranch', 1 UNION
	SELECT 'WhaleMeasureRegion', 1 UNION
    SELECT 'WhaleMeasureFirm', 1 UNION
	
	SELECT 'WhaleMeasureSummary', 1 UNION
	SELECT 'WhaleMeasureSummaryTrend', 1 UNION	
	SELECT 'WhaleMeasureTrendAdvisor', 1 UNION	
	SELECT 'WhaleMeasureTrendBranch', 1 UNION
	SELECT 'WhaleMeasureTrendRegion', 1 UNION
    SELECT 'WhaleMeasureTrendFirm', 1 UNION
	SELECT 'WhaleMessageAdvisor', 1 UNION
	SELECT 'WhaleMessageBranch', 1 UNION
	SELECT 'WhaleMessageRegion', 1 UNION
    SELECT 'WhaleMessageFirm', 1 UNION
    
    -- added for ML
	--SELECT 'WhaleMeasurePricingAdvisor', 1 UNION
	--SELECT 'WhaleMeasurePricingBranch', 1 UNION	
	--SELECT 'WhaleMeasurePricingRegion', 1 UNION	
	--SELECT 'WhaleMeasurePricingFirm', 1 UNION
	--SELECT 'WhaleFixedIncomeSecurityAdvisor', 1 UNION
 --   SELECT 'WhaleFixedIncomeSecurityBranch', 1 UNION
	--SELECT 'WhaleFixedIncomeSecurityRegion', 1 UNION
	--SELECT 'WhaleFixedIncomeSecurityFirm', 1 UNION
	--SELECT 'WhaleFBHouseholdAssetBucketAdvisor', 1 UNION
	--SELECT 'WhaleFBHouseholdAssetBucketBranch', 1 UNION
 --   SELECT 'WhaleFBHouseholdAssetBucketRegion', 1 UNION
 --   SELECT 'WhaleFBHouseholdAssetBucketFirm', 1 UNION

    SELECT 'WhaleTopOppAdvisorBranch', 1 UNION
    SELECT 'WhaleTopOppBranchRegion', 1 UNION
    SELECT 'WhaleTopOppBranchFirm', 1 UNION

	SELECT 'SharkPeriodData', 3 --UNION
   	-- added for ML
 --  	SELECT 'WhaleHouseholdTopClient', 3 UNION
	--SELECT 'SharkAssetBucket', 3 

end
else if (@idFirm = 1000)
begin

Insert into @TableList
	(TableName, TableStatus)
	--Tables have dependency
	SELECT 'SharkFirm', 2 

Insert into @TableList
	(TableName, TableStatus)
	SELECT 'OctoPusReportConfigurationAdvisor', 1 UNION
	SELECT 'OctoPusReportConfigurationBranch', 1 UNION
	SELECT 'OctoPusReportConfigurationRegion', 1 UNION
	SELECT 'OctoPusReportConfigurationFirm', 1 UNION
	
	-- added for ML
	SELECT 'OctoPusReportConfigurationMapping', 1 UNION
 
	SELECT 'prcBuild', 1 UNION
	SELECT 'WhaleMeasureSummary', 1

end


return 

END

GO

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-05-16 : 11:54:48

What does this return?[CODE]Select TableName, TableStatus
from dbo.fn_Get_MYDB_Subset_TableList(@idFirm)
order by id desc[/CODE]

=================================================
I am not one of those who in expressing opinions confine themselves to facts. (Mark Twain)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-05-16 : 15:49:20

is it getting past this?

quote:
if (@@SERVERNAME = 'DESTINATION_SERVER')
return;

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2013-05-16 : 16:06:56

You beat me to it Russ. I came to update the post.
Sorry for this.

It is getting stuck here. this SP should not have been deployed to this server.

if (@@SERVERNAME = 'DESTINATION_SERVER')
return;

Thanks for your time, all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources