SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Truncate does not truncate
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shiyam198
Yak Posting Veteran

94 Posts

Posted - 05/16/2013 :  11:36:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1770 Posts

Posted - 05/16/2013 :  11:54:48  Show Profile  Reply with Quote
What does this return?
Select TableName, TableStatus 
from dbo.fn_Get_MYDB_Subset_TableList(@idFirm)
order by id desc



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

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/16/2013 :  15:49:20  Show Profile  Visit russell's Homepage  Reply with Quote
is it getting past this?
quote:
if (@@SERVERNAME = 'DESTINATION_SERVER')
return;

Edited by - russell on 05/16/2013 15:49:42
Go to Top of Page

shiyam198
Yak Posting Veteran

94 Posts

Posted - 05/16/2013 :  16:06:56  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000