CODEWITHSUNDEEP
sqlsql-servervisual-studiossisetl
Laura Nuncira
Laura Nuncira

Reputation: 43

How can i delete a partition fuction on sql

So i got and ETL that stores 3 years '17 (corrupt), '18(corrupt), '19:

STG_tables: import Data from 3 differents DB and Export it to

DWH_tables: This is the Relational fase where all the historical information is stored. Here only the normalization and parameterization of the tables and the fields are carried out to adapt them to the developed logical model, but no business rules are applied.

DIM_tables: Finally, in the Dimensional Fase, the business rules are applied and the tables and indexes are optimized for the queries, since this is where the analytical tools will attack.

I got 2 types of Reloads:

Daily Reload: This Job is responsible for executing the SSIS packages necessary to perform the incremental daily load of the Data Warehouse. it only loads the last partition of the large tables (corresponding to the current year) in the dimensional Fase.

Full Reload: Loads full 3 years (this one is not working)

This wasn't done by me and i have 0 technical documentation, so im just trying to figure out how this works, my thinking is that once i get to do this full reload, the data will restore.

Im getting error on STG_fase:

DROP TABLE DWH_PROD.DWH_XX; DROP TABLE ... ':' The partition function 'pfPetitions' is being used in one or more partition schemes.'. Possible reasons for the error: problems with the query, the property 'ResultSet' was not set correctly, parameters not set correctly or connection poorly established.

i dont know how to drop this partition so i can create it again and cant find 'ResultSet' property, please help

USE DB;
GO

DROP TABLE DWH_PROD.DWH_ALBARANES_TARIFA;
DROP TABLE DWH_PROD.DWH_PETICIONES;
DROP TABLE DWH_PROD.DWH_SOLICITUDES;
DROP TABLE DWH_PROD.DWH_RESULTADOS;
DROP TABLE DWH_PROD.DWH_INCIDENCIAS;

-------i delete code so the text is not so big------
Here there are all the creation of the drop tables above

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = N'DWH_ALBARANES_TARIFA')
    CREATE TABLE DWH_PROD.DWH_ALBARANES_TARIFA (
    );

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = N'DWH_INCIDENCIAS')
    CREATE TABLE DWH_PROD.DWH_INCIDENCIAS (
    );

IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfPeticiones')
    DROP PARTITION FUNCTION pfPeticiones;

CREATE PARTITION FUNCTION pfPeticiones (DATE)
AS RANGE RIGHT FOR VALUES 
('2017-01-01', '2018-01-01', '2019-01-01');

IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psPeticiones')
    DROP PARTITION SCHEME psPeticiones;

CREATE PARTITION SCHEME psPeticiones
AS PARTITION pfPeticiones 
ALL TO ([Primary]);

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = N'DWH_PETICIONES')
    CREATE TABLE DWH_PROD.DWH_PETICIONES (
    ) ON psPeticiones(FEC_PETICION);

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = N'DWH_SOLICITUDES')
    CREATE TABLE DWH_PROD.DWH_SOLICITUDES (
    ) ON psPeticiones(FEC_PETICION);

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = N'DWH_RESULTADOS')
    CREATE TABLE DWH_PROD.DWH_RESULTADOS (
    ) ON psPeticiones(FEC_PETICION);

Upvotes: 4

Views: 951

Answers (1)

M. Kanarkowski
M. Kanarkowski

Reputation: 2195

You need to perform a few actions in order to do delete a partitioning function:

  1. Delete or move (i.e. if you have a heap, create a clustered index on PRIMARY) all tables that use the partitioning schema.
  2. Delete the partitioning schema.
  3. Delete the partitioning function.

Upvotes: 1

Related Questions