ODBC Grouped

The Metering Data Transfer Service contains a source and a destination module for ODBC Grouped that is to be used with SEC / SWPC projects. ODBC Grouped uses ODBC connections to select/insert metering data to a special predefined table structure. This section contains information on how to setup the ODBC SEC tables and connections.

How to create ODBC Connections

../../../_images/add_odbc.png ../../../_images/add_sqlnative.png ../../../_images/choose_server.png ../../../_images/set_credentials.png ../../../_images/set_default_database.png ../../../_images/finish.png ../../../_images/odbc_added.png

SQL Scripts for Setup

6 Minutes Data Tables

-- DCMS (DCS)

CREATE TABLE [dbo].[IPP5TagsDCMS_Status](
	[Group ID] [int] NOT NULL,
	[PP Name] [varchar](8) NOT NULL,
	[Data Source] [varchar](16) NOT NULL,
	[Tag Time Stamp] [datetime] NOT NULL,
	[Access Time Stamp] [datetime] NULL,
	[Check Status] [bit] NOT NULL,
 CONSTRAINT [PK_IPP5TagsDCMS_Status] PRIMARY KEY CLUSTERED 
(
	[Group ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[IPP5TagsDCMS](
	[PP Name] [varchar](8) NOT NULL,
	[Tag Name] [varchar](50) NOT NULL,
	[Tag Time Stamp] [datetime] NOT NULL,
	[Tag Value] [float] NOT NULL,
	[Access Time Stamp] [datetime] NULL,
	[Tag Quality] [bit] NOT NULL,
	[Group ID] [int] NOT NULL,
 CONSTRAINT [PK_IPP5TagsDCMS] PRIMARY KEY CLUSTERED 
(
	[Tag Name] ASC,
	[Tag Time Stamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[IPP5TagsDCMS_Status_AccessTimeStampTrigger] 
   ON  [dbo].[IPP5TagsDCMS_Status] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE [IPP5TagsDCMS_Status] SET [Access Time Stamp] = GETDATE()
	FROM [IPP5TagsDCMS_Status] ipp
	INNER JOIN inserted i ON i.[Group ID] = ipp.[Group ID]

END
GO
ALTER TABLE [dbo].[IPP5TagsDCMS_Status] ENABLE TRIGGER [IPP5TagsDCMS_Status_AccessTimeStampTrigger]
GO

CREATE TRIGGER [dbo].[IPP5TagsDCMS_AccessTimeStampTrigger] 
   ON  [dbo].[IPP5TagsDCMS] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE [IPP5TagsDCMS] SET [Access Time Stamp] = GETDATE()
	FROM [IPP5TagsDCMS] ipp
	INNER JOIN inserted i ON i.[Tag Name] = ipp.[Tag Name] AND i.[Tag Time Stamp] = ipp.[Tag Time Stamp]

END
GO
ALTER TABLE [dbo].[IPP5TagsDCMS] ENABLE TRIGGER [IPP5TagsDCMS_AccessTimeStampTrigger]
GO


-- SMD (MDA)

CREATE TABLE [dbo].[IPP5TagsSMD_Status](
	[Group ID] [int] NOT NULL,
	[PP Name] [varchar](8) NOT NULL,
	[Data Source] [varchar](16) NOT NULL,
	[Tag Time Stamp] [datetime] NOT NULL,
	[Access Time Stamp] [datetime] NULL,
	[Check Status] [bit] NOT NULL,
 CONSTRAINT [PK_IPP5TagsSMD_Status] PRIMARY KEY CLUSTERED 
(
	[Group ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[IPP5TagsSMD](
	[PP Name] [varchar](8) NOT NULL,
	[Tag Name] [varchar](50) NOT NULL,
	[Tag Time Stamp] [datetime] NOT NULL,
	[Tag Value] [float] NOT NULL,
	[Access Time Stamp] [datetime] NULL,
	[Tag Quality] [bit] NOT NULL,
	[Group ID] [int] NOT NULL,
 CONSTRAINT [PK_IPP5TagsSMD] PRIMARY KEY CLUSTERED 
(
	[Tag Name] ASC,
	[Tag Time Stamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[IPP5TagsSMD_Status_AccessTimeStampTrigger] 
   ON  [dbo].[IPP5TagsSMD_Status] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	UPDATE [IPP5TagsSMD_Status] SET [Access Time Stamp] = GETDATE()
	FROM [IPP5TagsSMD_Status] ipp
	INNER JOIN inserted i ON i.[Group ID] = ipp.[Group ID]

END
GO
ALTER TABLE [dbo].[IPP5TagsSMD_Status] ENABLE TRIGGER [IPP5TagsSMD_Status_AccessTimeStampTrigger]
GO


CREATE TRIGGER [dbo].[IPP5TagsSMD_AccessTimeStampTrigger] 
   ON  [dbo].[IPP5TagsSMD] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE [IPP5TagsSMD] SET [Access Time Stamp] = GETDATE()
	FROM [IPP5TagsSMD] ipp
	INNER JOIN inserted i ON i.[Tag Name] = ipp.[Tag Name] AND i.[Tag Time Stamp] = ipp.[Tag Time Stamp]

END
GO
ALTER TABLE [dbo].[IPP5TagsSMD] ENABLE TRIGGER [IPP5TagsSMD_AccessTimeStampTrigger]
GO

Hourly FDM Online Tables

CREATE TABLE [dbo].[IPP5TagsHOnFDM](
	[PP Name] [varchar](8) NOT NULL,
	[Tag Name] [varchar](50) NOT NULL,
	[Tag Time Stamp] [datetime] NOT NULL,
	[Tag Value] [float] NOT NULL,
	[Access Time Stamp] [datetime] NULL,
	[Tag Quality] [bit] NOT NULL,
	[Group ID] [int] NOT NULL,
 CONSTRAINT [PK_IPP5TagsHOnFDM] PRIMARY KEY CLUSTERED 
(
	[Tag Name] ASC,
	[Tag Time Stamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[IPP5TagsHOnFDM_Status](
	[Group ID] [int] NOT NULL,
	[PP Name] [varchar](8) NOT NULL,
	[Data Source] [varchar](16) NOT NULL,
	[Tag Time Stamp] [datetime] NOT NULL,
	[Access Time Stamp] [datetime] NULL,
	[Check Status] [bit] NOT NULL,
 CONSTRAINT [PK_IPP5TagsHOnFDM_Status] PRIMARY KEY CLUSTERED 
(
	[Group ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[IPP5TagsHOnFDM_Status_AccessTimeStampTrigger] 
   ON  [dbo].[IPP5TagsHOnFDM_Status] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE [IPP5TagsHOnFDM_Status] SET [Access Time Stamp] = GETDATE()
	FROM [IPP5TagsHOnFDM_Status] ipp
	INNER JOIN inserted i ON i.[Group ID] = ipp.[Group ID]

END
GO
ALTER TABLE [dbo].[IPP5TagsHOnFDM_Status] ENABLE TRIGGER [IPP5TagsHOnFDM_Status_AccessTimeStampTrigger]
GO

CREATE TRIGGER [dbo].[IPP5TagsHOnFDM_AccessTimeStampTrigger] 
   ON  [dbo].[IPP5TagsHOnFDM] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE [IPP5TagsHOnFDM] SET [Access Time Stamp] = GETDATE()
	FROM [IPP5TagsHOnFDM] ipp
	INNER JOIN inserted i ON i.[Tag Name] = ipp.[Tag Name] AND i.[Tag Time Stamp] = ipp.[Tag Time Stamp]

END
GO
ALTER TABLE [dbo].[IPP5TagsHOnFDM] ENABLE TRIGGER [IPP5TagsHOnFDM_AccessTimeStampTrigger]
GO

Monthly Invoice Online Tables

CREATE TABLE [dbo].[IPP5TagsMOnTCM_Status](
	[Group ID] [int] NOT NULL,
	[PP Name] [varchar](8) NOT NULL,
	[Data Source] [varchar](16) NOT NULL,
	[Tag Time Stamp] [datetime] NOT NULL,
	[Access Time Stamp] [datetime] NULL,
	[Version] [tinyint] NOT NULL,
	[Check Status] [bit] NOT NULL,
 CONSTRAINT [PK_IPP5TagsMOnTCM_Status] PRIMARY KEY CLUSTERED 
(
	[Group ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[IPP5TagsMOnTCM](
	[PP Name] [varchar](8) NOT NULL,
	[Tag Name] [varchar](50) NOT NULL,
	[Tag Time Stamp] [datetime] NOT NULL,
	[Tag Value] [float] NOT NULL,
	[Access Time Stamp] [datetime] NULL,
	[Tag Quality] [bit] NOT NULL,
	[Version] [tinyint] NOT NULL,
	[Group ID] [int] NOT NULL,
 CONSTRAINT [PK_IPP5TagsMOnTCM] PRIMARY KEY CLUSTERED 
(
	[Tag Name] ASC,
	[Tag Time Stamp] ASC,
	[Version] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[IPP5TagsMOnTCM_Status_AccessTimeStampTrigger] 
   ON  [dbo].[IPP5TagsMOnTCM_Status] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE [IPP5TagsMOnTCM_Status] SET [Access Time Stamp] = GETDATE()
	FROM [IPP5TagsMOnTCM_Status] ipp
	INNER JOIN inserted i ON i.[Group ID] = ipp.[Group ID]

END
GO
ALTER TABLE [dbo].[IPP5TagsMOnTCM_Status] ENABLE TRIGGER [IPP5TagsMOnTCM_Status_AccessTimeStampTrigger]
GO

CREATE TRIGGER [dbo].[IPP5TagsMOnTCM_AccessTimeStampTrigger] 
   ON  [dbo].[IPP5TagsMOnTCM] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE [IPP5TagsMOnTCM] SET [Access Time Stamp] = GETDATE()
	FROM [IPP5TagsMOnTCM] ipp
	INNER JOIN inserted i ON i.[Tag Name] = ipp.[Tag Name] AND i.[Tag Time Stamp] = ipp.[Tag Time Stamp] AND i.[Version] = ipp.[Version]

END
GO
ALTER TABLE [dbo].[IPP5TagsMOnTCM] ENABLE TRIGGER [IPP5TagsMOnTCM_AccessTimeStampTrigger]
GO

Hourly FDM Offline Tables

CREATE TABLE [dbo].[IPP5TagsHOffFDM](
	[PP Name] [varchar](8) NOT NULL,
	[Tag Name] [varchar](50) NOT NULL,
	[Tag Time Stamp] [datetime] NOT NULL,
	[Tag Value] [float] NOT NULL,
	[Access Time Stamp] [datetime] NULL,
	[Tag Quality] [bit] NOT NULL,
	[Group ID] [int] NOT NULL,
 CONSTRAINT [PK_IPP5TagsHOffFDM] PRIMARY KEY CLUSTERED 
(
	[Tag Name] ASC,
	[Tag Time Stamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[IPP5TagsHOffFDM_Status](
	[Group ID] [int] NOT NULL,
	[PP Name] [varchar](8) NOT NULL,
	[Data Source] [varchar](16) NOT NULL,
	[Tag Time Stamp] [datetime] NOT NULL,
	[Access Time Stamp] [datetime] NULL,
	[Check Status] [bit] NOT NULL,
 CONSTRAINT [PK_IPP5TagsHOffFDM_Status] PRIMARY KEY CLUSTERED 
(
	[Group ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[IPP5TagsHOffFDM_Status_AccessTimeStampTrigger] 
   ON  [dbo].[IPP5TagsHOffFDM_Status] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE [IPP5TagsHOffFDM_Status] SET [Access Time Stamp] = GETDATE()
	FROM [IPP5TagsHOffFDM_Status] ipp
	INNER JOIN inserted i ON i.[Group ID] = ipp.[Group ID]

END
GO
ALTER TABLE [dbo].[IPP5TagsHOffFDM_Status] ENABLE TRIGGER [IPP5TagsHOffFDM_Status_AccessTimeStampTrigger]
GO

CREATE TRIGGER [dbo].[IPP5TagsHOffFDM_AccessTimeStampTrigger] 
   ON  [dbo].[IPP5TagsHOffFDM] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE [IPP5TagsHOffFDM] SET [Access Time Stamp] = GETDATE()
	FROM [IPP5TagsHOffFDM] ipp
	INNER JOIN inserted i ON i.[Tag Name] = ipp.[Tag Name] AND i.[Tag Time Stamp] = ipp.[Tag Time Stamp]

END
GO
ALTER TABLE [dbo].[IPP5TagsHOffFDM] ENABLE TRIGGER [IPP5TagsHOffFDM_AccessTimeStampTrigger]
GO

Monthly Invoice Offline Tables

CREATE TABLE [dbo].[IPP5TagsMOffTCM_Status](
	[Group ID] [int] NOT NULL,
	[PP Name] [varchar](8) NOT NULL,
	[Data Source] [varchar](16) NOT NULL,
	[Tag Time Stamp] [datetime] NOT NULL,
	[Access Time Stamp] [datetime] NULL,
	[Version] [tinyint] NOT NULL,
	[Check Status] [bit] NOT NULL,
 CONSTRAINT [PK_IPP5TagsMOffTCM_Status] PRIMARY KEY CLUSTERED 
(
	[Group ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[IPP5TagsMOffTCM](
	[PP Name] [varchar](8) NOT NULL,
	[Tag Name] [varchar](50) NOT NULL,
	[Tag Time Stamp] [datetime] NOT NULL,
	[Tag Value] [float] NOT NULL,
	[Access Time Stamp] [datetime] NULL,
	[Tag Quality] [bit] NOT NULL,
	[Version] [tinyint] NOT NULL,
	[Group ID] [int] NOT NULL,
 CONSTRAINT [PK_IPP5TagsMOffTCM] PRIMARY KEY CLUSTERED 
(
	[Tag Name] ASC,
	[Tag Time Stamp] ASC,
	[Version] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[IPP5TagsMOffTCM_Status_AccessTimeStampTrigger] 
   ON  [dbo].[IPP5TagsMOffTCM_Status] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE [IPP5TagsMOffTCM_Status] SET [Access Time Stamp] = GETDATE()
	FROM [IPP5TagsMOffTCM_Status] ipp
	INNER JOIN inserted i ON i.[Group ID] = ipp.[Group ID]

END
GO
ALTER TABLE [dbo].[IPP5TagsMOffTCM_Status] ENABLE TRIGGER [IPP5TagsMOffTCM_Status_AccessTimeStampTrigger]
GO

CREATE TRIGGER [dbo].[IPP5TagsMOffTCM_AccessTimeStampTrigger] 
   ON  [dbo].[IPP5TagsMOffTCM] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE [IPP5TagsMOffTCM] SET [Access Time Stamp] = GETDATE()
	FROM [IPP5TagsMOffTCM] ipp
	INNER JOIN inserted i ON i.[Tag Name] = ipp.[Tag Name] AND i.[Tag Time Stamp] = ipp.[Tag Time Stamp] AND i.[Version] = ipp.[Version]

END
GO
ALTER TABLE [dbo].[IPP5TagsMOffTCM] ENABLE TRIGGER [IPP5TagsMOffTCM_AccessTimeStampTrigger]
GO