How to set square braces to the columns set in the dynamic archival script in SQL?

Question

0.00/5 (No votes)

See more:

I want to set square braces to the columns set in the dynamic archival script in sql.

I am fetching the source column list and destination column list

I want to embrace it with square braces while fetching tables for archival

Linked Server Details Configured        

DB Server- NESTIT-319-E\MSSQL          

Linked Server created- Test

Live DB- NWPS_DIB_LIVE_ARCHIVAL 


Archival DB Server- NTP-228
 
Archival DB- NWPSCS_Archival_DB_2018

What I have tried:

/****** Object:  StoredProcedure [dbo].[DIB_ARCHIVE]    Script Date: 11/03/2015 19:15:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec  [DIB_ARCHIVE] 'FTSEDGE_UAT','FTSEDGE_ARC','05-07-2015','23-11-2014'
exec  [DIB_ARCHIVE] 'FTSEDGE_Live','[10.240.176.79].[UAEFTS_ARC1]','15-07-2015','24-08-2012'
*/              
ALTER PROCEDURE [dbo].[DIB_ARCHIVE]
(                    
  @LIVEDB         NVarchar(100)                  
, @ARCHIVEDB      Varchar(100)                  
, @ArchiveDate    VARCHAR(10)               
, @ArchiveAgeDate VARCHAR(10)                 
)                    
AS                    

SET XACT_ABORT ON;

DECLARE @TableName Varchar(200)                
Declare @DateField Varchar(50)                
Declare @TableType char(1)                
Declare @STRIDENTITY NVarchar(4000)                
Declare @STRINSERT NVarchar(4000)                
Declare @STRDELETE NVarchar(4000)                
declare @SourceColumnList varchar(MAX)
declare @DestinationColumnList varchar(MAX)
declare @STRUSE Nvarchar(100)                
declare @DISABLECONSTRAINT Nvarchar(1000) declare @ENABLECONSTRAINT Nvarchar(1000) Declare @Archive_LogID INT Declare @NumRowsChanged INT                  

SET @DISABLECONSTRAINT=@LIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"'                  
EXECUTE sp_executesql @DISABLECONSTRAINT SET @DISABLECONSTRAINT=@ARCHIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"'                  
EXECUTE sp_executesql @DISABLECONSTRAINT                  

set @STRUSE='USE '+@LIVEDB                
EXECUTE sp_executesql @STRUSE                  


---Archive LOg-------------                  
INSERT INTO  Archive_Log  (LiveDBName,ArchivalDBName,Archival_Date,ArchivalStartTime)                  
VALUES (@LIVEDB,@ARCHIVEDB,getdate(),getdate())                  
SET @Archive_LogID= SCOPE_IDENTITY() ---Archive LOg-------------                  

BEGIN TRY                
	BEGIN TRAN                  

	IF CURSOR_STATUS('global','ARCHIVE')>=-1                
	BEGIN                
	DEALLOCATE ARCHIVE                
	END                 

	DECLARE ARCHIVE CURSOR FOR 
	SELECT TableName,DateField,TableType FROM dbo.Archive_config 

		
	OPEN ARCHIVE fetch next from ARCHIVE into @TableName, @DateField,@TableType                  

	WHILE @@FETCH_STATUS = 0                
	BEGIN                  

		SET @STRIDENTITY=''                    
		set @SourceColumnList=''                   
		set @DestinationColumnList=''
		set @STRINSERT=''
		
		select @SourceColumnList = @SourceColumnList + ',' + case when DATA_TYPE = 'xml' then 'convert(varchar(max),'+column_name+')' else column_name end   
		from information_schema.columns  
		where table_name = @TableName  and DATA_TYPE not in ('timestamp')  

		select @DestinationColumnList= @DestinationColumnList+ ',' + column_name 
		from information_schema.columns  
		where table_name = @TableName  and DATA_TYPE not in ('timestamp')                  

		set  @SourceColumnList= RIGHT(@SourceColumnList,Len(@SourceColumnList)-1)                  
		set  @DestinationColumnList= RIGHT(@DestinationColumnList,Len(@DestinationColumnList)-1)
								
		IF(@TableType='M') --Master Table                  
		BEGIN     
		
			                  
			SET @STRDELETE='delete from '+ @ARCHIVEDB +'.dbo.'+@TableName                     
			EXECUTE sp_executesql @STRDELETE                  

			SET @NumRowsChanged=@@ROWCOUNT                  
			INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])                  
			VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Delete from Table(Master Table) : ' +@TableName +', Archival DB  : '+ @ARCHIVEDB )                  
			
			--select @SourceColumnList = @SourceColumnList + ',' + case when DATA_TYPE = 'xml' then 'convert(varchar(max),'+column_name+')' else column_name end   
			--from information_schema.columns  
			--where table_name = @TableName  and DATA_TYPE not in ('timestamp')  
			
			--select @DestinationColumnList= @DestinationColumnList+ ',' + column_name 
			--from information_schema.columns  
			--where table_name = @TableName  and DATA_TYPE not in ('timestamp')                  
									
			--set  @SourceColumnList= RIGHT(@SourceColumnList,Len(@SourceColumnList)-1)                  
			--set  @DestinationColumnList= RIGHT(@DestinationColumnList,Len(@DestinationColumnList)-1)
			
			SET @STRINSERT=@STRIDENTITY+'INSERT INTO '+ @ARCHIVEDB +'.dbo.'+ @TableName +'('+ @DestinationColumnList + ')                  
			SELECT '+ @SourceColumnList + ' FROM  '+ @LIVEDB +'.dbo.'+@TableName        								                          
			EXECUTE sp_executesql @STRINSERT                  
			
			SET @NumRowsChanged=@@ROWCOUNT                  
			INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])                  
			VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Insert Into Table(Master Table) : ' +@TableName +', Archival DB  : '+ @ARCHIVEDB )                  
			
		END                  
		ELSE IF(@TableType='T') --Transaction Table                
		BEGIN                  
			
			--select @SourceColumnList = @SourceColumnList + ',' + case when DATA_TYPE = 'xml' then 'convert(varchar(max),'+column_name+')' else column_name end   
			--from information_schema.columns  
			--where table_name = @TableName  and DATA_TYPE not in ('timestamp')  
			
			--select @DestinationColumnList= @DestinationColumnList+ ',' + column_name 
			--from information_schema.columns  
			--where table_name = @TableName  and DATA_TYPE not in ('timestamp')                  
									
			--set  @SourceColumnList= RIGHT(@SourceColumnList,Len(@SourceColumnList)-1)                  
			--set  @DestinationColumnList= RIGHT(@DestinationColumnList,Len(@DestinationColumnList)-1)               
			
			SET @STRINSERT=@STRIDENTITY+'INSERT INTO '+ @ARCHIVEDB +'.dbo.'+ @TableName +'('+ @DestinationColumnList + ')                  
			SELECT '+ @SourceColumnList + ' FROM  '+ @LIVEDB +'.dbo.'+@TableName + '                   
			WHERE '+ @DateField + '                  
			< CONVERT(DATETIME,'''+@ArchiveAgeDate+''',103)+ 1'                   
			
			--print @STRINSERT
			EXECUTE sp_executesql @STRINSERT                  
			
			SET @NumRowsChanged=@@ROWCOUNT                  
			INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])                  
			VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Insert Into Table(Transaction Table) : ' +@TableName +' , Archival DB  : '+ @ARCHIVEDB )                  
													
			SET @STRDELETE='delete from '+ @LIVEDB +'.dbo.'+@TableName + '                  
			WHERE '+ @DateField + '                  
			< CONVERT(DATETIME,'''+@ArchiveAgeDate+''',103)+ 1'                 
			
			--print @STRINSERT
			EXECUTE sp_executesql @STRDELETE                  

			SET @NumRowsChanged=@@ROWCOUNT                  
			INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])                  
			VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Delete from Table(Transaction Table) : ' +@TableName +' ,Live DB : '+ @LIVEDB )                  
			
		END

		FETCH NEXT                
		from ARCHIVE into @TableName, @DateField,@TableType 
	
	END CLOSE ARCHIVE DEALLOCATE ARCHIVE COMMIT TRAN                  

	---Archive LOg-------------                  

	UPDATE Archive_Log SET ArchivalEndTime=GETDATE(),Status='Successfully Completed '                  
	WHERE  Archive_LogID=@Archive_LogID                  

	---Archive LOg-------------                 

	PRINT 'Successfully Completed'                  
END TRY                

BEGIN CATCH                
	ROLLBACK TRAN                  

	UPDATE Archive_Log SET ArchivalEndTime=GETDATE(),Status='Archive Script Failed at Table : '+  @TableName +' , Error Message: ' + ERROR_MESSAGE()                   
	WHERE  Archive_LogID=@Archive_LogID                  

	PRINT 'Archive Script Failed at Table : '+  @TableName +' , Error Message: ' + ERROR_MESSAGE()             

END CATCH                  

SET @ENABLECONSTRAINT=@LIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"'                  
EXECUTE sp_executesql @ENABLECONSTRAINT 

SET @ENABLECONSTRAINT=@ARCHIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"'            
EXECUTE sp_executesql @ENABLECONSTRAINT

Posted 11-Jun-18 2:12am

ranio

Updated 11-Jun-18 3:48am

v6

Add a Solution

Comments

Maciej Los 11-Jun-18 9:14am

Are you sure that [RRR_PROCESSING_CUT-OFF_TIME] field exits in [TEST].[NWPSCS_Archival_DB_2018].dbo.SR_PCNRRRR001?

[no name] 11-Jun-18 13:24pm

Wrong tool for the job.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Answer 1 · 2018-06-11T03:20:00

Solution 1

The specified column name wasn't found in the specified table. The exception is pretty clear. Either you got the column name wrong, or you got the table name wrong.

EDIT ==================

Before anyone down-votes this answer, be aware that the OP significantly changed his question after I answered it.

Posted 11-Jun-18 3:20am

#realJSOP

Updated 11-Jun-18 4:39am

v2

Comments

ranio 11-Jun-18 9:56am

The column is there but the issue was not becoz setting the column list with square braces while fetching the columns of a table dynamically as below.
core code is as below:
select @SourceColumnList = @SourceColumnList + ',' + case when DATA_TYPE = 'xml' then 'convert(varchar(max),'+column_name+')' else column_name end
from information_schema.columns
where table_name = @TableName and DATA_TYPE not in ('timestamp')

select @DestinationColumnList= '[' + @DestinationColumnList+ ']'+ ',' + column_name
from information_schema.columns
where table_name = @TableName and DATA_TYPE not in ('timestamp')

select @SourceColumnList

select @DestinationColumnList

set @SourceColumnList= RIGHT(@SourceColumnList,Len(@SourceColumnList)-1)
set @DestinationColumnList= RIGHT(@DestinationColumnList,Len(@DestinationColumnList)-1)