Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1385 > unrolled thread

Purge Utility

Started byJAW <jwilliam@aglresources.com>
First post2013-02-13 12:44 -0800
Last post2013-02-21 18:04 -0800
Articles 7 — 4 participants

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Purge Utility JAW <jwilliam@aglresources.com> - 2013-02-13 12:44 -0800
    Re: Purge Utility Erland Sommarskog <esquel@sommarskog.se> - 2013-02-13 22:25 +0100
    Re: Purge Utility JAW <jwilliam@aglresources.com> - 2013-02-14 08:10 -0800
      Re: Purge Utility Erland Sommarskog <esquel@sommarskog.se> - 2013-02-14 21:00 +0100
    Re: Purge Utility JAW <jwilliam@aglresources.com> - 2013-02-14 08:22 -0800
    Re: Purge Utility JAW <willjamu@gmail.com> - 2013-02-21 17:38 -0800
      Re: Purge Utility rja.carnegie@gmail.com - 2013-02-21 18:04 -0800

#1385 — Purge Utility

FromJAW <jwilliam@aglresources.com>
Date2013-02-13 12:44 -0800
SubjectPurge Utility
Message-ID<695679d9-3eb9-44c8-a7fb-2e60ef2b8373@googlegroups.com>
I have written a PURGE utility SPROC that works fine on simple SQL.

I am trying to compose complex SQL.

delete from PurgeUtility where TableName = 'TAB1'

INSERT INTO [DB].[dbo].[PurgeUtility]([TableName],[PurgeRange])
VALUES ('TAB1','BA IN (SELECT BA  FROM  [TAB1].[dbo].[ACCT] (nolock) WHERE  OFFICE > '  + '''001''' + '  OR  TYPE > 5)');


TableName	PurgeRange
TAB1	BA IN (SELECT BA  FROM  [DB].[dbo].[ACCT] (nolock) WHERE  OFFICE > '001'  OR  TYPE > 5)

Block of the code code

I use the table name in the PurgTable to generate the WHERE clause for the remainder of code.

BEGIN TRY
		 -- Load the data critera of rows (TABLOCK required for MINIMAL LOG) REP_TABLE
		 SET   @VSQLQuery = 'REP_' + @I_TABLE 
		 PRINT 'Fill REP Table=' + @VSQLQuery + ' Time='  + cast(getdate() as varchar)    
         SET   @VSQLQuery = 'INSERT INTO ' + @VSQLQuery + ' WITH (TABLOCK) SELECT * FROM TEMP_' + @I_TABLE+ ' WHERE ' + @VParameterDefinition
		 PRINT '===================================================='	
		 PRINT @VSQLQuery
		 PRINT '===================================================='	
         IF @I_MODE = 'SIMULATE'
		  BEGIN
		   PRINT 'Simulation mode this step is skipped'
		   PRINT '===================================================='
          END
		 ELSE
		  BEGIN
		   EXECUTE (@VSQLQuery)
 	       SELECT @Vcount = @@ROWCOUNT
		   PRINT 'Rows Loaded to ' + @VSQLQuery + ' = ' + CAST(@Vcount AS VARCHAR(10))  
		   PRINT '===================================================='	
	     END
		End TRY

When I run code with the apostrophes like the above I get an error saying that a syntax error exists.

[toc] | [next] | [standalone]

#1386

FromErland Sommarskog <esquel@sommarskog.se>
Date2013-02-13 22:25 +0100
Message-ID<XnsA166E41F444F0Yazorman@127.0.0.1>
In reply to#1385
JAW (jwilliam@aglresources.com) writes:
> When I run code with the apostrophes like the above I get an error
> saying that a syntax error exists. 

When I supplemented the code you posted with variable declarations and
a CATCH block, the code executed successfully.

Is your dynamically generated SQL producing an error? Did you look at the 
actual code? You seem to have a PRINT there?

 



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1387

FromJAW <jwilliam@aglresources.com>
Date2013-02-14 08:10 -0800
Message-ID<41b71981-071e-4070-8dd2-700ec898f02d@googlegroups.com>
In reply to#1385
Thanks for the Reply!


Below is the output of the run

====================================================
Start time=Feb 14 2013 11:08AM
====================================================
Clean-up Processing Table= IF  EXISTS (SELECT * FROM sys.objects 
          WHERE object_id = OBJECT_ID('TEMP_BUS_DIR_NO_INSTMT_JAW') AND type in ('U'))  
          BEGIN  
            DROP TABLE TEMP_BUS_DIR_NO_INSTMT_JAW END Time=Feb 14 2013 11:08AM
====================================================
 IF  EXISTS (SELECT * FROM sys.objects 
          WHERE object_id = OBJECT_ID('TEMP_BUS_DIR_NO_INSTMT_JAW') AND type in ('U'))  
          BEGIN  
            DROP TABLE TEMP_BUS_DIR_NO_INSTMT_JAW END
====================================================
Create Staging Table=TEMP_BUS_DIR_NO_INSTMT_JAW Time=Feb 14 2013 11:08AM
====================================================
SELECT * INTO TEMP_BUS_DIR_NO_INSTMT_JAW FROM REP_BUS_DIR_NO_INSTMT_JAW WHERE 0=1
====================================================
Fill Staging Table=TEMP_BUS_DIR_NO_INSTMT_JAW Time=Feb 14 2013 11:08AM
====================================================
INSERT INTO TEMP_BUS_DIR_NO_INSTMT_JAW WITH (TABLOCK) SELECT * FROM REP_BUS_DIR_NO_INSTMT_JAW
====================================================
Rows Loaded to INSERT INTO TEMP_BUS_DIR_NO_INSTMT_JAW WITH (TABLOCK) SELECT * FROM REP_BUS_DIR_NO_INSTMT_JAW = 5000
====================================================
TRUNCATE REP Table=REP_BUS_DIR_NO_INSTMT_JAW Time=Feb 14 2013 11:08AM
====================================================
TRUNCATE TABLE REP_BUS_DIR_NO_INSTMT_JAW
====================================================
Fill REP Table=REP_BUS_DIR_NO_INSTMT_JAW Time=Feb 14 2013 11:08AM
====================================================
INSERT INTO REP_BUS_DIR_NO_INSTMT_JAW WITH (TABLOCK) SELECT * FROM TEMP_BUS_DIR_NO_INSTMT_JAW WHERE KY_BA IN (SELECT KY_BA  FROM  [CISStagingDB].[dbo].[REP_BILL_ACCT] (nolock) WHERE  CD_OFFICE > '079'
====================================================
Msg 50000, Level 16, State 1, Procedure usp_Purge, Line 144
Incorrect syntax near '079'

[toc] | [prev] | [next] | [standalone]

#1389

FromErland Sommarskog <esquel@sommarskog.se>
Date2013-02-14 21:00 +0100
Message-ID<XnsA167D5B8CB6FYazorman@127.0.0.1>
In reply to#1387
JAW (jwilliam@aglresources.com) writes:
>==================================================== 
> INSERT INTO REP_BUS_DIR_NO_INSTMT_JAW WITH (TABLOCK) SELECT * FROM
> TEMP_BUS_DIR_NO_INSTMT_JAW WHERE KY_BA IN (SELECT KY_BA  FROM 
> [CISStagingDB].[dbo].[REP_BILL_ACCT] (nolock) WHERE  CD_OFFICE > '079' 
>====================================================
> Msg 50000, Level 16, State 1, Procedure usp_Purge, Line 144
> Incorrect syntax near '079'

When there is a token missing at the end, SQL Server flags the token as
the culprit, rather than say EOF or similar as some compilers do. There
should be closing parenthesis at the end.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1388

FromJAW <jwilliam@aglresources.com>
Date2013-02-14 08:22 -0800
Message-ID<d2e31d16-9c18-430c-86c7-0f1b5c1b630d@googlegroups.com>
In reply to#1385
Below is the full process.

Basically a procress to clean-up some staging tables for a conversion.

SImply SQL works fine but I am having a big problem with data with apostrophes.

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

Create table PurgeUtility (
TableName   varchar(128) NOT NULL, 
PurgeRange  varchar(2000) NOT NULL 
);

--
--
--

alter table PurgeUtility  add constraint PK_PurgeUtility Primary KEY (TableName);


/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [TableName]
      ,[PurgeRange]
  FROM [CISStagingDB].[dbo].[PurgeUtility]
  
  
DROP TABLE REP_BUS_DIR_NO_INSTMT_JAW  
GO
SELECT  * INTO REP_BUS_DIR_NO_INSTMT_JAW FROM QUERY WITH (NOLOCK)  
GO

delete from PurgeUtility where TableName = 'BUS_DIR_NO_INSTMT_JAW'

INSERT INTO [CISStagingDB].[dbo].[PurgeUtility]([TableName],[PurgeRange])
VALUES ('BUS_DIR_NO_INSTMT_JAW','KY_BA IN (SELECT KY_BA  FROM  [CISStagingDB].[dbo].[REP_BILL_ACCT] (nolock) WHERE  CD_OFFICE > '  + '''079''' + '  OR  CD_ACCT_TYPE > 55)');


--
--  Example  insert for each file you wish to proces


The Full code

Below is the entire code:

USE CISStagingDB;

IF OBJECT_ID ( 'dbo.usp_PurgeCISSTAGEDB', 'P' ) IS NOT NULL 
   DROP PROCEDURE dbo.usp_PurgeCISSTAGEDB;
GO

CREATE Procedure dbo.usp_PurgeCISSTAGEDB
( @I_TABLE varchar(128),@I_MODE varchar(10) = 'SIMULATE')
AS
/*

       
*/
BEGIN
SET NOCOUNT ON;

DECLARE @VSQLQuery            NVARCHAR(2000),
        @VCount               INT,
        @ErrorMessage         NVARCHAR(4000),
        @VParameterDefinition NVARCHAR(100);

/*  
 BEGIN TRANSACTION;
 
*/

  
    	PRINT '===================================================='	
		PRINT 'Start time=' + cast(getdate() as varchar)
		PRINT '===================================================='	

			-- Check for Table Criteria
	    Select  @VParameterDefinition = PurgeRange
         from PurgeUtility where Tablename = @I_TABLE
		 
		 If @@ROWCOUNT <> 1
		 BEGIN
		  PRINT @I_TABLE + ' Table not in the PurgeUtility table'
		  RETURN 4
		 END
	    -- Clean up old TEMP_TABLE_NAME if it exists
	
		  SET   @VSQLQuery = ' IF  EXISTS (SELECT * FROM sys.objects 
          WHERE object_id = OBJECT_ID(''TEMP_'+@I_TABLE+''') AND type in (''U''))  
          BEGIN  
            DROP TABLE TEMP_' + @I_TABLE +   
           ' END'
        PRINT 'Clean-up Processing Table=' + @VSQLQuery + ' Time='  + cast(getdate() as varchar)     

		PRINT '===================================================='	
        PRINT @VSQLQuery
		PRINT '===================================================='
		IF @I_MODE = 'SIMULATE'
		 BEGIN
		  PRINT 'Simulation mode this step is skipped'
		  PRINT '===================================================='
         END
		ELSE	
         EXECUTE sp_executesql @VSQLQuery;
 	  	
		-- Create  Staging Table
	    SET   @VSQLQuery = 'TEMP_' + @I_TABLE 
		PRINT 'Create Staging Table=' + @VSQLQuery + ' Time='  + cast(getdate() as varchar)    
        SET   @VSQLQuery = 'SELECT * INTO ' + @VSQLQuery + ' FROM REP_' + @I_TABLE + ' WHERE 0=1'
        PRINT '===================================================='	
		PRINT @VSQLQuery
		PRINT '===================================================='	
        IF @I_MODE = 'SIMULATE'
         BEGIN
		  PRINT 'Simulation mode this step is skipped'
		  PRINT '===================================================='
         END
		ELSE
	     EXECUTE sp_executesql @VSQLQuery;
 	    	    	
		--   Backup the critera of rows (TABLOCK required for MINIMAL LOG) TEMP_TABLE
		SET   @VSQLQuery = 'TEMP_' + @I_TABLE 
		PRINT 'Fill Staging Table=' + @VSQLQuery + ' Time='  + cast(getdate() as varchar)    
        SET   @VSQLQuery = 'INSERT INTO ' + @VSQLQuery + ' WITH (TABLOCK) SELECT * FROM REP_' + @I_TABLE
		PRINT '===================================================='	
		PRINT @VSQLQuery
		PRINT '===================================================='	
        IF @I_MODE = 'SIMULATE'
		  BEGIN
		  PRINT 'Simulation mode this step is skipped'
		  PRINT '===================================================='
         END
		ELSE
		 BEGIN
		  EXECUTE sp_executesql @VSQLQuery;
		  SELECT @Vcount = @@ROWCOUNT
          PRINT 'Rows Loaded to ' + @VSQLQuery + ' = ' + CAST(@Vcount AS VARCHAR(10))  
		  PRINT '===================================================='	
 	    
 	      IF @Vcount = 0
		   BEGIN
		    PRINT 'REP_' + @I_TABLE + ' is empty'
		    PRINT '===================================================='	
		    RETURN 4
	      END
 	    END
		--   Truncate  REP_table
		SET   @VSQLQuery = 'REP_' + @I_TABLE 
		PRINT 'TRUNCATE REP Table=' + @VSQLQuery + ' Time='  + cast(getdate() as varchar)    
        SET   @VSQLQuery = 'TRUNCATE TABLE ' + @VSQLQuery 
		PRINT '===================================================='	
		PRINT @VSQLQuery
		PRINT '===================================================='	
        IF @I_MODE = 'SIMULATE'
		  BEGIN
		  PRINT 'Simulation mode this step is skipped'
		  PRINT '===================================================='
         END
		ELSE
		 EXECUTE sp_executesql @VSQLQuery; 
			
	    BEGIN TRY
		 -- Load the data critera of rows (TABLOCK required for MINIMAL LOG) REP_TABLE
		 SET   @VSQLQuery = 'REP_' + @I_TABLE 
		 PRINT 'Fill REP Table=' + @VSQLQuery + ' Time='  + cast(getdate() as varchar)    
         SET   @VSQLQuery = 'INSERT INTO ' + @VSQLQuery + ' WITH (TABLOCK) SELECT * FROM TEMP_' + @I_TABLE+ ' WHERE ' + @VParameterDefinition
		 PRINT '===================================================='	
		 PRINT @VSQLQuery
		 PRINT '===================================================='	
         IF @I_MODE = 'SIMULATE'
		  BEGIN
		   PRINT 'Simulation mode this step is skipped'
		   PRINT '===================================================='
          END
		 ELSE
		  BEGIN
		   EXECUTE (@VSQLQuery)
 	       SELECT @Vcount = @@ROWCOUNT
		   PRINT 'Rows Loaded to ' + @VSQLQuery + ' = ' + CAST(@Vcount AS VARCHAR(10))  
		   PRINT '===================================================='	
	     END
		End TRY
		BEGIN CATCH
        SELECT 
         @ErrorMessage = ERROR_MESSAGE();
        RAISERROR (@ErrorMessage, 16, 1);
        RETURN 4
        END CATCH
		-- Clean-up		
        SET   @VSQLQuery = ' IF  EXISTS (SELECT * FROM sys.objects 
          WHERE object_id = OBJECT_ID(''TEMP_'+@I_TABLE+''') AND type in (''U''))  
          BEGIN  
            DROP TABLE TEMP_' + @I_TABLE +   
           ' END'
        PRINT 'Clean-up Processing Table=' + @VSQLQuery + ' Time='  + cast(getdate() as varchar)     

		PRINT '===================================================='	
        PRINT @VSQLQuery
		PRINT '===================================================='	
       IF @I_MODE = 'SIMULATE'
		 BEGIN
		  PRINT 'Simulation mode this step is skipped'
		  PRINT '===================================================='
         END
	   ELSE
	    EXECUTE sp_executesql @VSQLQuery;
	   PRINT 'End  time=' + cast(getdate() as varchar)
       PRINT '===================================================='	
		
		
/*
     COMMIT TRANSACTION;
  END TRY

  BEGIN CATCH

     IF @@TRANCOUNT > 0
      BEGIN
        ROLLBACK TRANSACTION;
      END
      EXECUTE [dbo].[usp_LogError];  
      RAISERROR ('Review Error in the load CISDB process',       -- Message text.
                  16,                                            -- Severity.
                  1                                              -- State.
                 );
                    /
  END CATCH;
  */
END;

GO

-- Run in simulation mode to verify your SQL
-- exec dbo.usp_PurgeCISSTAGEDB 'SAD'
-- Run for real
-- exec dbo.usp_PurgeCISSTAGEDB 'SAD','EXECUTE'

--select count(*) from TEMPX_SAD where KY_BA = 0

-- insert into REP_SAD select * from TEMPX_SAD

exec dbo.usp_PurgeCISSTAGEDB 'BUS_DIR_NO_INSTMT_JAW','EXECUTE'

[toc] | [prev] | [next] | [standalone]

#1398

FromJAW <willjamu@gmail.com>
Date2013-02-21 17:38 -0800
Message-ID<00d6df61-bf2a-4e17-be9b-3c2b112b118e@googlegroups.com>
In reply to#1385
DECLARE @VSQLQuery            NVARCHAR(2000), 
        @VCount               INT, 
        @ErrorMessage         NVARCHAR(4000), 
        @VParameterDefinition NVARCHAR(100); 


In the words of Fred G. Sanford I fell like a 'real dummy"

My Vparameterdefinition just needed to be increased just needed to be increased and it worked.

[toc] | [prev] | [next] | [standalone]

#1399

Fromrja.carnegie@gmail.com
Date2013-02-21 18:04 -0800
Message-ID<f1c42aae-d328-409f-9a97-7aca77a309cc@googlegroups.com>
In reply to#1398
On Friday, 22 February 2013 01:38:34 UTC, JAW  wrote:
> My Vparameterdefinition just needed to be increased just needed to be increased and it worked.

I like to construct SQL strings with --X
marking the end, and check that it's still there at 
the point of execution - and to use @{name_of_a_token} 
for expressionss to insert (by REPLACE) in a single string.
This seems to work well on nvarchar(max) - I think -
where some constructions (I'm not sure which ones) 
seem to impose a 4000 or 8000 character limit.

But, putting BEGIN and END around a block is another
way to get the benefit of validation.  Some SQL 
statements, you really don't want to cut short - 
like, DELETE with no parameters erases every file 
on the server.  (It doesn't.)
you

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web