DROP PROCEDURE IF EXISTS system.spDELETE_Connector; GO /* Procedure to DELETE a connector including all queries Saxess Software GmbH Testcall Procedure DECLARE @RC INT; EXEC @RC = system.spDELETE_Connector @Username = N'SQL' , @SourceSystemID = N'NAVISION' , @SourceSystemType = N'MSSQL' , @ModuleID = N'FIN' PRINT @RC SELECT * FROM system.tConnectors SELECT * FROM system.tConnectorQueries Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spDELETE_Connector', NULL, NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spDELETE_Connector', 'PARAMETER', NULL) */ CREATE PROCEDURE system.spDELETE_Connector @Username NVARCHAR(255) , @SourceSystemID NVARCHAR(50) = N'' , @SourceSystemType NVARCHAR(50) = N'' , @ModuleID NVARCHAR(50) = N'' AS BEGIN BEGIN TRY -- Logging DECLARE @TimestampCall DATETIME = GETUTCDATE(); DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); DECLARE @AffectedRows INT = 0; DECLARE @ResultCode INT = 501; DECLARE @Comment NVARCHAR(4000) = N''; DECLARE @ParameterString NVARCHAR(MAX) = N''; DECLARE @TransactUsername NVARCHAR(255) = N''; EXEC system.spGET_ParameterString @ParameterString OUTPUT, 4, @Username, @SourceSystemID, @SourceSystemType, @ModuleID; EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username; -- Input parameter handling SET @SourceSystemID = COALESCE(dbo.sx_pf_pProtectID(@SourceSystemID), N''); SET @SourceSystemType = COALESCE(@SourceSystemType, N''); SET @ModuleID = COALESCE(dbo.sx_pf_pProtectID(@ModuleID), N''); -- Delete Pipeline, this will also delete Steps and Schedules due to key relation DELETE FROM system.tConnectors WHERE SourceSystemID = @SourceSystemID AND SourceSystemType = @SourceSystemType AND ModuleID = @ModuleID; SET @AffectedRows = @@ROWCOUNT; IF @AffectedRows = 0 EXEC system.spSEND_Message N'ERROR', N'Connector doesn''t exist' SET @ResultCode = 200 END TRY BEGIN CATCH SET @ResultCode = 500; SET @Comment = ERROR_MESSAGE(); END CATCH; EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment; IF @ResultCode >= 500 EXEC system.spSEND_Message N'ERROR', @Comment; RETURN @ResultCode; END; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'system' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spDELETE_Connector' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'Procedure to delete a Pipeline including steps and schedules.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@SourceSystemID'; SET @value = N'SourceSystemID - ID of the source ERP system.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@SourceSystemType'; SET @value = N'SourceSystemType - type of connection to the source ERP system (MSSQL, ORACLE, ODBC).'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ModuleID'; SET @value = N'ModuleID - ID of the module.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS system.spEXPORT_Connector; GO /* Procedure to EXPORT a connector Saxess Software GmbH Testcall Procedure DECLARE @RC INT; EXEC @RC = system.spEXPORT_Connector @Username = N'SQL' , @SourceSystemID = N'NAVISION' , @SourceSystemType = N'MSSQL' , @ModuleID = N'FIN' , @ExportType = N'JSON' PRINT @RC Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spEXPORT_Connector', NULL, NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spEXPORT_Connector', 'PARAMETER', NULL) */ CREATE PROCEDURE system.spEXPORT_Connector @Username NVARCHAR(255) , @SourceSystemID NVARCHAR(50) , @SourceSystemType NVARCHAR(50) , @ModuleID NVARCHAR(50) , @ExportType NVARCHAR(50) = N'SQL' AS BEGIN BEGIN TRY -- Logging DECLARE @TimestampCall DATETIME = GETUTCDATE(); DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); DECLARE @AffectedRows INT = 0; DECLARE @ResultCode INT = 501; DECLARE @Comment NVARCHAR(4000) = N''; DECLARE @ParameterString NVARCHAR(MAX) = N''; DECLARE @TransactUsername NVARCHAR(255) = N''; EXEC system.spGET_ParameterString @ParameterString OUTPUT, 5, @Username, @SourceSystemID, @SourceSystemType, @ModuleID, @ExportType; EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username; -- Custom variables DECLARE @SourceClusterName NVARCHAR(100); DECLARE @SourceClusterAPI NVARCHAR(100); -- Input parameter handling SET @SourceSystemID = COALESCE(dbo.sx_pf_pProtectID(@SourceSystemID), N''); SET @SourceSystemType = COALESCE(@SourceSystemType, N''); SET @ModuleID = COALESCE(dbo.sx_pf_pProtectID(@ModuleID), N''); IF @ExportType NOT IN (N'SQL', N'JSON') EXEC system.spSEND_Message N'ERROR', N'Unknown export type. Allowed export types are SQL and JSON.' IF @ExportType = N'SQL' BEGIN -- Create a temporary table to store the collection of export commands DROP TABLE IF EXISTS #tExport; CREATE TABLE #tExport ( RowKey BIGINT IDENTITY (1,1) , MainOrderNumber INT NOT NULL , SubOrderNumber INT NOT NULL , Command NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL , PRIMARY KEY CLUSTERED (RowKey) ); -- Determine Metadata SELECT @SourceClusterName = ValueText FROM system.tSettings WHERE SettingID = N'Clustername'; -- May be empty if not defined SET @SourceClusterName = CONCAT(DB_Name(),' ',@SourceClusterName); SELECT @SourceClusterAPI = ValueText FROM system.tSettings WHERE SettingID = N'DBVersion'; -- MetaHeader INSERT INTO #tExport VALUES (0, 1, N'-- {') , (0, 2, N'-- "Type": "Connector",') , (0, 3, CONCAT(N'-- "SourceClusterName": "', @SourceClusterName, N'",')) , (0, 4, CONCAT(N'-- "SourceClusterAPI": "', @SourceClusterAPI, N'",')) , (0, 5, CONCAT(N'-- "SourceSystemID": "', @SourceSystemID, N'",')) , (0, 6, CONCAT(N'-- "SourceSystemType": "', @SourceSystemType, N'",')) , (0, 7, CONCAT(N'-- "ModuleID": "', @ModuleID, N'"')) , (0, 8, N'-- }') INSERT INTO #tExport VALUES (10, 1, N'--CONFIG: Adjust variable values manually to fit your needs !') , (10, 2, N'DECLARE @Username NVARCHAR(255) = ''SQL''') , (10, 3, N'DECLARE @SourceSystemID NVARCHAR(255) = ''' + @SourceSystemID + N'''') , (10, 4, N'DECLARE @SourceSystemType NVARCHAR(255) = ''' + @SourceSystemType + N'''') , (10, 5, N'DECLARE @ModuleID NVARCHAR(255) = ''' + @ModuleID + N'''') , (10, 6, N'--This Connector will be deleted during import, if it exists. You should be sure!') -- Try_Delete existing connector INSERT INTO #tExport SELECT 900 AS MainOrderNumber , 1000 AS SubOrderNumber , CONCAT( N'EXEC system.spDELETE_Connector ' , N'@Username' , N',@SourceSystemID' , N',@SourceSystemType' , N',@ModuleID' , N';' ) AS Command FROM system.tConnectors WHERE SourceSystemID = @SourceSystemID AND SourceSystemType = @SourceSystemType AND ModuleID = @ModuleID; -- Create the connector as new connector INSERT INTO #tExport SELECT 1000 AS MainOrderNumber , 1000 AS SubOrderNumber , CONCAT( N'EXEC system.spPOST_Connector ' , N'@Username' , N',@SourceSystemID' , N',@SourceSystemType' , N',@ModuleID' , N',''', dbo.sx_pf_pMaskSQL(ConnectorDescription), N'''' , N',''', dbo.sx_pf_pMaskSQL(ConnectorShortDescription), N'''' , N',''', dbo.sx_pf_pMaskSQL(ConnectorDescriptionURL), N'''' , N',''', dbo.sx_pf_pMaskSQL(ConnectorVersion), N'''' , N',''', dbo.sx_pf_pMaskSQL(Manufacturer), N'''' ) AS Command FROM system.tConnectors WHERE SourceSystemID = @SourceSystemID AND SourceSystemType = @SourceSystemType AND ModuleID = @ModuleID; -- Create the Steps INSERT INTO #tExport SELECT 2000 AS MainOrderNumber , 1000 AS SubOrderNumber , CONCAT( N'EXEC system.spPOST_ConnectorQuery ' , N'@Username' , N',@SourceSystemID' , N',@SourceSystemType' , N',@ModuleID' , N',''', dbo.sx_pf_pMaskSQL(cq.QueryName), N'''' , N',''', dbo.sx_pf_pMaskSQL(cq.QuerySQL), N'''' , N',''', dbo.sx_pf_pMaskSQL(cq.QueryVersionComment), N'''' ) AS Command FROM system.tConnectorQueries AS cq INNER JOIN system.tConnectors AS c ON c.ConnectorKey = cq.ConnectorKey LEFT JOIN system.tConnectorQueries AS cqMax ON cq.ConnectorKey = cqMax.ConnectorKey AND cq.QueryName = cqMax.QueryName AND cq.QueryVersion < cqMax.QueryVersion WHERE c.SourceSystemID = @SourceSystemID AND c.SourceSystemType = @SourceSystemType AND c.ModuleID = @ModuleID AND cqMax.QueryVersion IS NULL; -- final GO INSERT INTO #tExport VALUES (9999, 1, N'GO'); -- Data Transaction SELECT Command FROM #tExport ORDER BY MainOrderNumber , SubOrderNumber END IF @ExportType = N'JSON' BEGIN SELECT ( SELECT f1.SourceSystemID , f1.SourceSystemType , f1.ModuleID , f1.ConnectorDescription AS ConnectorDescription , f1.ConnectorShortDescription AS ConnectorShortDescription , f1.ConnectorDescriptionURL AS ConnectorDescriptionURL , f1.ConnectorVersion AS ConnectorVersion , f1.Manufacturer AS Manufacturer , ( SELECT f2.QueryName , f2.QuerySQL AS Query , f2.QueryVersion AS QueryVersion FROM system.tConnectorQueries AS f2 WHERE f2.ConnectorKey = f1.ConnectorKey AND f2.QueryVersion = ( SELECT MAX(f3.QueryVersion) FROM system.tConnectorQueries AS f3 WHERE f3.ConnectorKey = f2.ConnectorKey AND f3.QueryName = f2.QueryName ) ORDER BY CASE WHEN f2.QueryName = N'GetCompanies' THEN 1 ELSE 2 END ASC , f2.QueryName ASC FOR JSON PATH ) AS ConnectorQueries FROM system.tConnectors AS f1 WHERE f1.SourceSystemID = @SourceSystemID AND f1.SourceSystemType = @SourceSystemType AND f1.ModuleID = @ModuleID FOR JSON PATH, ROOT (N'Connector') ) AS Command END SET @AffectedRows = @@ROWCOUNT; SET @ResultCode = 200 END TRY BEGIN CATCH SET @ResultCode = 500; SET @Comment = ERROR_MESSAGE(); END CATCH; EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment; IF @ResultCode >= 500 EXEC system.spSEND_Message N'ERROR', @Comment; RETURN @ResultCode; END; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'system' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spEXPORT_Connector' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'Procedure to create an SQL Script, which imports a connector with all steps in OCT.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@SourceSystemID'; SET @value = N'SourceSystemID - ID of the source ERP system.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@SourceSystemType'; SET @value = N'SourceSystemType - type of connection to the source ERP system (MSSQL, ORACLE, ODBC).'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ModuleID'; SET @value = N'ModuleID - ID of the module.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS system.spGET_Connector; GO /* Procedure to GET connector information Saxess Software GmbH Testcall Procedure DECLARE @RC INT; EXEC @RC = system.spGET_Connector @Username = N'SQL' , @SourceSystemID = N'NAVISION' , @SourceSystemType = N'MSSQL' , @ModuleID = N'FIN' PRINT @RC Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_Connector', NULL, NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_Connector', 'PARAMETER', NULL) */ CREATE PROCEDURE system.spGET_Connector @Username NVARCHAR(255) , @SourceSystemID NVARCHAR(50) = N'' , @SourceSystemType NVARCHAR(50) = N'' , @ModuleID NVARCHAR(50) = N'' AS BEGIN BEGIN TRY -- Logging DECLARE @TimestampCall DATETIME = GETUTCDATE(); DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); DECLARE @AffectedRows INT = 0; DECLARE @ResultCode INT = 501; DECLARE @Comment NVARCHAR(4000) = N''; DECLARE @ParameterString NVARCHAR(MAX) = N''; DECLARE @TransactUsername NVARCHAR(255) = N''; EXEC system.spGET_ParameterString @ParameterString OUTPUT, 4, @Username, @SourceSystemID, @SourceSystemType, @ModuleID; EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username; -- Input parameter handling SET @SourceSystemID = COALESCE(dbo.sx_pf_pProtectID(@SourceSystemID), N''); SET @SourceSystemType = COALESCE(@SourceSystemType, N''); SET @ModuleID = COALESCE(dbo.sx_pf_pProtectID(@ModuleID), N''); -- Return connector information SELECT SourceSystemID , SourceSystemType , ModuleID , ConnectorDescription , ConnectorShortDescription , ConnectorVersion , ConnectorDescriptionURL , Manufacturer FROM system.tConnectors WHERE SourceSystemID = COALESCE(NULLIF(@SourceSystemID, N''), SourceSystemID) AND SourceSystemType = COALESCE(NULLIF(@SourceSystemType, N''), SourceSystemType) AND ModuleID = COALESCE(NULLIF(@ModuleID, N''), ModuleID); SET @AffectedRows = @@ROWCOUNT; IF @AffectedRows = 0 AND @SourceSystemID <> N'' AND @SourceSystemType <> N'' AND @ModuleID <> N'' EXEC system.spSEND_Message N'ERROR', N'Connector doesn''t exist' SET @ResultCode = 200 END TRY BEGIN CATCH SET @ResultCode = 500; SET @Comment = ERROR_MESSAGE(); END CATCH; EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment; IF @ResultCode >= 500 EXEC system.spSEND_Message N'ERROR', @Comment; RETURN @ResultCode; END; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'system' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spGET_Connector' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'Procedure to GET one or all connectors.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- SET parameter documentation SET @level2name = N'@Username'; SET @value = N'Username'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@SourceSystemID'; SET @value = N'SourceSystemID - ID of the source ERP system.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@SourceSystemType'; SET @value = N'SourceSystemType - type of connection to the source ERP system (MSSQL, ORACLE, ODBC).'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ModuleID'; SET @value = N'ModuleID - ID of the module.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS system.spGET_ConnectorQuery; GO /* Procedure to GET a specific query of a connector Saxess Software GmbH Testcall Procedure DECLARE @RC INT; EXEC @RC = system.spGET_ConnectorQuery @Username = N'SQL' , @SourceSystemID = N'NAVISION' , @SourceSystemType = N'MSSQL' , @ModuleID = N'FIN' , @QueryName = N'AccountingJournal' PRINT @RC Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_ConnectorQuery', NULL, NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_ConnectorQuery', 'PARAMETER', NULL) */ CREATE PROCEDURE system.spGET_ConnectorQuery @Username NVARCHAR(255) , @SourceSystemID NVARCHAR(50) , @SourceSystemType NVARCHAR(50) , @ModuleID NVARCHAR(50) = N'' -- optional, especially for GetCompanies query which used to be not bound to a module , @QueryName NVARCHAR(255) = N'' -- optional, if empty return all queries of connector AS BEGIN BEGIN TRY -- Logging DECLARE @TimestampCall DATETIME = GETUTCDATE(); DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); DECLARE @AffectedRows INT = 0; DECLARE @ResultCode INT = 501; DECLARE @Comment NVARCHAR(4000) = N''; DECLARE @ParameterString NVARCHAR(MAX) = N''; DECLARE @TransactUsername NVARCHAR(255) = N''; EXEC system.spGET_ParameterString @ParameterString OUTPUT, 5, @Username, @SourceSystemID, @SourceSystemType, @ModuleID, @QueryName; EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username; -- Input parameter handling SET @SourceSystemID = COALESCE(dbo.sx_pf_pProtectID(@SourceSystemID), N''); SET @SourceSystemType = COALESCE(@SourceSystemType, N''); SET @ModuleID = COALESCE(dbo.sx_pf_pProtectID(@ModuleID), N''); SET @QueryName = COALESCE(@QueryName, N'') IF @SourceSystemID = N'' OR @SourceSystemType = N'' OR (@ModuleID = N'' AND @QueryName <> N'GetCompanies') OR NOT EXISTS ( SELECT 1 FROM system.tConnectors WHERE SourceSystemID = @SourceSystemID AND SourceSystemType = @SourceSystemType AND (ModuleID = @ModuleID OR (@ModuleID = N'' AND @QueryName = N'GetCompanies')) ) EXEC system.spSEND_Message N'ERROR', N'Connector doesn''t exist' -- @ModuleID is empty for GetCompanies -> return first GetCompanies that is found for this SourceSystemID and SourceSystemType IF @ModuleID = N'' AND @QueryName = N'GetCompanies' BEGIN SELECT TOP 1 c.SourceSystemID , c.SourceSystemType , c.ModuleID , cq.QueryName , cq.QuerySQL , cq.QueryVersion , cq.QueryVersionComment , cq.Timestamp , cq.CreatedBy FROM system.tConnectorQueries AS cq JOIN system.tConnectors AS c ON c.ConnectorKey = cq.ConnectorKey LEFT JOIN system.tConnectorQueries AS cqMax ON cq.ConnectorKey = cqMax.ConnectorKey AND cq.QueryName = cqMax.QueryName AND cq.QueryVersion < cqMax.QueryVersion WHERE c.SourceSystemID = @SourceSystemID AND c.SourceSystemType = @SourceSystemType AND cq.QueryName = @QueryName AND cqMax.QueryVersion IS NULL; END -- QueryName specified but doesn't exist -> return empty result set ELSE IF @QueryName <> N'' AND NOT EXISTS ( SELECT 1 FROM system.tConnectorQueries AS cq JOIN system.tConnectors AS c ON c.ConnectorKey = cq.ConnectorKey WHERE c.SourceSystemID = @SourceSystemID AND c.SourceSystemType = @SourceSystemType AND c.ModuleID = @ModuleID AND cq.QueryName = @QueryName ) BEGIN SELECT @SourceSystemID AS SourceSystemID , @SourceSystemType AS SourceSystemType , @ModuleID AS ModuleID , @QueryName AS QueryName , N'' AS QuerySQL , 0 AS QueryVersion , N'' AS QueryVersionComment , NULL AS Timestamp , N'' AS CreatedBy END -- everything is normal -> return regular result ELSE BEGIN SELECT c.SourceSystemID , c.SourceSystemType , c.ModuleID , cq.QueryName , cq.QuerySQL , cq.QueryVersion , cq.QueryVersionComment , cq.Timestamp , cq.CreatedBy FROM system.tConnectorQueries AS cq JOIN system.tConnectors AS c ON c.ConnectorKey = cq.ConnectorKey LEFT JOIN system.tConnectorQueries AS cqMax ON cq.ConnectorKey = cqMax.ConnectorKey AND cq.QueryName = cqMax.QueryName AND cq.QueryVersion < cqMax.QueryVersion WHERE c.SourceSystemID = @SourceSystemID AND c.SourceSystemType = @SourceSystemType AND c.ModuleID = @ModuleID AND ( cq.QueryName = @QueryName OR @QueryName = '' ) AND cqMax.QueryVersion IS NULL; END; SET @AffectedRows = @@ROWCOUNT; SET @ResultCode = 200 END TRY BEGIN CATCH SET @ResultCode = 500; SET @Comment = ERROR_MESSAGE(); END CATCH; EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment; IF @ResultCode >= 500 EXEC system.spSEND_Message N'ERROR', @Comment; RETURN @ResultCode; END; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'system' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spGET_ConnectorQuery' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'Procedure to get one or all ConnectorQueries.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@SourceSystemID'; SET @value = N'SourceSystemID - ID of the source ERP system.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@SourceSystemType'; SET @value = N'SourceSystemType - type of connection to the source ERP system (MSSQL, ORACLE, ODBC).'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ModuleID'; SET @value = N'ModuleID - ID of the module.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@QueryName'; SET @value = N'QueryName - name of the query and target integration table.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS system.spGET_ConnectorQueryFields; GO /* Procedure to GET target fields for a connector query Saxess Software GmbH Testcall Procedure DECLARE @RC INT; EXEC @RC = system.spGET_ConnectorQueryFields @Username = N'SQL' , @ModuleID = N'FIN' , @QueryName = N'AccountingJournal' PRINT @RC Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_ConnectorQueryFields', NULL, NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_ConnectorQueryFields', 'PARAMETER', NULL) */ CREATE PROCEDURE system.spGET_ConnectorQueryFields @Username NVARCHAR(255) , @ModuleID NVARCHAR(50) , @QueryName NVARCHAR(255) AS BEGIN BEGIN TRY -- Logging DECLARE @TimestampCall DATETIME = GETUTCDATE(); DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); DECLARE @AffectedRows INT = 0; DECLARE @ResultCode INT = 501; DECLARE @Comment NVARCHAR(4000) = N''; DECLARE @ParameterString NVARCHAR(MAX) = N''; DECLARE @TransactUsername NVARCHAR(255) = N''; EXEC system.spGET_ParameterString @ParameterString OUTPUT, 3, @Username, @ModuleID, @QueryName; EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username; -- Input parameter handling SET @ModuleID = COALESCE(dbo.sx_pf_pProtectID(@ModuleID), N''); SET @QueryName = COALESCE(@QueryName, N''); -- Table exists? IF @QueryName <> N'GetCompanies' AND (@ModuleID = '' OR @QueryName = '' OR NOT EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.columns WHERE ( (TABLE_SCHEMA = N'integration' AND TABLE_NAME = N't' + @ModuleID + N'_' + @QueryName) OR (@QueryName = N'GetCompanies') ) )) BEGIN SET @Comment = CONCAT(N'Can''t get field list of table ''integration.t', @ModuleID, N'_', @QueryName, N''' because it doesn''t exist. Please check if the ''', @ModuleID, N''' module is installed correctly.'); EXEC system.spSEND_Message N'ERROR', @Comment; END; -- Data Transaction SELECT SCHEMA_NAME(t.schema_id) + N'.' + t.name AS TableName , c.name AS ColumnName , UPPER(TYPE_NAME(c.system_type_id)) + CASE WHEN c.precision = 0 AND c.scale = 0 AND max_length > 0 THEN N'(' + CAST(c.max_length / 2 AS NVARCHAR(10)) + N')' WHEN c.precision > 0 AND c.scale > 0 THEN N'(' + CAST(c.precision AS NVARCHAR(10)) + N', ' + CAST(c.scale AS NVARCHAR(10)) + N')' ELSE N'' END AS DataType , CASE WHEN c.is_nullable = 1 THEN N'NULL' ELSE N'NOT NULL' END AS NULLAble , p.value AS ColumnInformation , RANK() OVER (ORDER BY c.column_id) AS OrderNumber FROM sys.tables AS t JOIN sys.columns AS c ON c.object_id = t.object_id LEFT JOIN sys.extended_properties AS p ON p.major_id = t.object_id AND p.minor_id = c.column_id AND p.class = 1 WHERE SCHEMA_NAME(t.schema_id) = N'integration' AND t.name = N't' + @ModuleID + N'_' + @QueryName AND c.name <> N'RowKey' AND @QueryName <> N'GetCompanies' UNION ALL SELECT SCHEMA_NAME(t.schema_id) + N'.' + t.name AS TableName , c.name AS ColumnName , UPPER(TYPE_NAME(c.system_type_id)) + CASE WHEN c.precision = 0 AND c.scale = 0 AND max_length > 0 THEN N'(' + CAST(c.max_length / 2 AS NVARCHAR(10)) + N')' WHEN c.precision > 0 AND c.scale > 0 THEN N'(' + CAST(c.precision AS NVARCHAR(10)) + N', ' + CAST(c.scale AS NVARCHAR(10)) + N')' ELSE N'' END AS DataType , CASE WHEN c.is_nullable = 1 THEN N'NULL' ELSE N'NOT NULL' END AS NULLAble , p.value AS ColumnInformation , RANK() OVER (ORDER BY c.column_id) AS OrderNumber FROM sys.tables AS t JOIN sys.columns AS c ON c.object_id = t.object_id LEFT JOIN sys.extended_properties AS p ON p.major_id = t.object_id AND p.minor_id = c.column_id AND p.class = 1 WHERE SCHEMA_NAME(t.schema_id) = N'global' AND t.name = N'tCompanies' AND c.name NOT IN (N'RowKey', N'DataSourceKey', N'CompanyKey', N'CustomValuesJSONUser', N'OwnerCODE') AND @QueryName = N'GetCompanies' ORDER BY OrderNumber ASC; SET @AffectedRows = @@ROWCOUNT; SET @ResultCode = 200 END TRY BEGIN CATCH SET @ResultCode = 500; SET @Comment = ERROR_MESSAGE(); END CATCH; EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment; IF @ResultCode >= 500 EXEC system.spSEND_Message N'ERROR', @Comment; RETURN @ResultCode; END; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'system' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spGET_ConnectorQueryFields' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'Procedure to get one or all ConnectorQueries.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@ModuleID'; SET @value = N'ModuleID - ID of the module.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@QueryName'; SET @value = N'QueryName - name of the query and target integration table.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS system.spGET_ConnectorTree; GO /* Procedure to GET the tree structure for all connectors Saxess Software GmbH Testcall Procedure DECLARE @RC INT; EXEC @RC = system.spGET_ConnectorTree @Username = N'SQL' PRINT @RC Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_ConnectorTree', NULL, NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_ConnectorTree', 'PARAMETER', NULL) */ CREATE PROCEDURE system.spGET_ConnectorTree @Username NVARCHAR(255) AS BEGIN BEGIN TRY -- Logging DECLARE @TimestampCall DATETIME = GETUTCDATE(); DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); DECLARE @AffectedRows INT = 0; DECLARE @ResultCode INT = 501; DECLARE @Comment NVARCHAR(4000) = N''; DECLARE @ParameterString NVARCHAR(MAX) = N''; DECLARE @TransactUsername NVARCHAR(255) = N''; EXEC system.spGET_ParameterString @ParameterString OUTPUT, 1, @Username; EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username; -- Temp table to store all integration tables of all modules DROP TABLE IF EXISTS #ModuleTables; SELECT SUBSTRING(t.name, 2, CHARINDEX(N'_', t.name) - 2) COLLATE DATABASE_DEFAULT AS ModuleID, SUBSTRING(t.name, CHARINDEX(N'_', t.name) + 1, 255) COLLATE DATABASE_DEFAULT AS IntegrationTable INTO #ModuleTables FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE s.name = N'integration' AND t.name LIKE N't%[_]%'; -- Add GetCompanies to all modules as default INSERT INTO #ModuleTables SELECT DISTINCT ModuleID , N'GetCompanies' FROM #ModuleTables; -- Data output SELECT SourceSystemID , SourceSystemType , ModuleID , ConnectorDescription , ConnectorShortDescription , ConnectorVersion , QueryName , Manufacturer FROM ( SELECT c.SourceSystemID , c.SourceSystemType , c.ModuleID , c.ConnectorDescription , c.ConnectorShortDescription , c.ConnectorVersion , mt.IntegrationTable AS QueryName , CASE WHEN mt.IntegrationTable = N'GetCompanies' THEN 0 ELSE 1 END IsGetCompanies , c.Manufacturer FROM system.tConnectors AS c CROSS APPLY #ModuleTables AS mt LEFT JOIN system.tConnectorQueries AS ct ON ct.ConnectorKey = c.ConnectorKey AND ct.QueryName = mt.IntegrationTable WHERE c.ModuleID = mt.ModuleID UNION SELECT c.SourceSystemID , c.SourceSystemType , c.ModuleID , c.ConnectorDescription , c.ConnectorShortDescription , c.ConnectorVersion , CASE WHEN ct.ConnectorKey IS NULL THEN N'GetCompanies' ELSE ct.QueryName END AS QueryName , CASE WHEN ct.QueryName = N'GetCompanies' THEN 0 ELSE 1 END IsGetCompanies , c.Manufacturer FROM system.tConnectors AS c LEFT JOIN system.tConnectorQueries AS ct ON ct.ConnectorKey = c.ConnectorKey AND ct.ConnectorKey IS NOT NULL ) AS Queries ORDER BY SourceSystemID , ModuleID , SourceSystemType , IsGetCompanies , QueryName; SET @AffectedRows = @@ROWCOUNT; SET @ResultCode = 200 END TRY BEGIN CATCH SET @ResultCode = 500; SET @Comment = ERROR_MESSAGE(); END CATCH; EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment; IF @ResultCode >= 500 EXEC system.spSEND_Message N'ERROR', @Comment; RETURN @ResultCode; END; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'system' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spGET_ConnectorTree' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'Procedure to get one or all Connectors.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; GO DROP PROCEDURE IF EXISTS system.spGET_DBInfo; GO /* Procedure to get basic informations about one Database. Informations can be requested - from OCTService for an User - from OCTService for himself (passing SQL as Clientusername means check the Current SQL User) TODO: - resolve Rolemembership when assigned over Security Groups - check AccessRight (implicit sysadmin) over AzureAD SQL Server Administrator Group Membership Infolevel - 0 = information it clientuser can access this cluster or not - 1 = all the Service needs to display a tile - access - tileimage - number of users - database version - 2 = full Informations - user role - database size Gerd Tautenhahn for Saxess Software GmbH Last modified: 02/2023 for OCT 5.9 Testcall Procedure -- Service for an user DECLARE @RC INT; EXEC @RC = system.spGET_DBInfo @Username = 'SQL' ,@ClientUser = 'gerd.tautenhahn@saxess-software.com' ,@InfoLevel = 2; PRINT @RC; -- Service for Service DECLARE @RC INT; EXEC @RC = system.spGET_DBInfo @Username = 'SQL' ,@ClientUser = 'SQL' ,@InfoLevel = 2; PRINT @RC; SELECT TOP 3 * FROM system.tAPI_Log; Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_DBInfo',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_DBInfo','PARAMETER',NULL) */ CREATE PROCEDURE system.spGET_DBInfo @Username NVARCHAR(255) ,@ClientUser NVARCHAR(255) ,@InfoLevel INT = 1 AS BEGIN -- SET NOCOUNT ON; -- activate this, if you read the resultset with an external source -- Standard declaration for logging DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID) -- SET PARAMETER LOG STRING -inside in single quotes (N''','''), Numbers without strings inside without quotes (N','), end list with '''' in case of string or '' in case of number on last position ,@ParameterString NVARCHAR(MAX) = N'''' + ISNULL(@Username ,N'NULL') + N''',''' + ISNULL(@ClientUser ,N'NULL') + N'''' ,@EffectedRows INT = 0 ,@ResultCode INT = 501 ,@TimestampCall DATETIME = GETUTCDATE() ,@Comment NVARCHAR(2000) = N'' ,@TransactUsername NVARCHAR(255) = N'' ,@StartTime DATETIME2 = SysUTCDateTime() ,@IsOCTService BIT = 0; -- NULL Protection for all mandatory Input parameters IF @Username IS NULL SET @Username = N''; IF @ClientUser IS NULL SET @ClientUser = N''; -- The string SQL means the octservice asks for himself IF @ClientUser = 'SQL' SELECT @ClientUser = CURRENT_USER; -- !! Clientuser will never exist in OCTUsermanagement -- !! Clientuser may be 'dbo' or an AzureActive Directory Identity -- passing SQL as Clientusername means check the Current SQL User IF EXISTS ( SELECT DP1.[name] FROM sys.database_role_members DRM RIGHT OUTER JOIN sys.database_principals DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.[type] = 'R' AND DP2.[name] = @ClientUser AND DP1.[name] IN ('db_octservice','db_owner') ) SET @IsOCTService = 1; -- is OCTService or can act like OCTService due to db_owner status DECLARE @ClientUserAccess_Flag INT = 0; -- START TRY *********************************************************************************** BEGIN TRY ---- check transaction user existence SELECT @TransactUsername = dbo.sx_pf_Determine_TransactionUsername (@Username); IF @TransactUsername = N'403' BEGIN -- nothing, to enable Footprint Protection PRINT 'Unknown user - eliminate footprint' -- SET @ResultCode = 403; -- RAISERROR('Transaction user don`t exists', 16, 10); END; -- Caching table DROP TABLE IF EXISTS #tInfoCache; CREATE TABLE #tInfoCache ( RowKey BIGINT IDENTITY (1,1) ,PropertyID NVARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL ,PropertyName NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,InfoLevel INT NOT NULL ,ValueInt INT NOT NULL ,ValueMoney MONEY NOT NULL ,ValueText NVARCHAR(4000) COLLATE DATABASE_DEFAULT NOT NULL ,ValueDatetime DATETIME NULL ,ValueBinary VARBINARY(MAX) NULL --max 8000 Byte to avoid Varbinary(MAX) ,PRIMARY KEY CLUSTERED (RowKey) ); -- INFO LEVEL 0 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- check AccessRight for ClientUser IF @IsOCTService = 1 BEGIN SET @ClientUserAccess_Flag = 1; END ELSE BEGIN SELECT @ClientUserAccess_Flag = COUNT(UserKey) FROM system.trUser WHERE UserName = @ClientUser AND Status = 'Active'; END -- check if database has public user IF @ClientUserAccess_Flag = 0 AND EXISTS(SELECT 1 FROM system.trUser WHERE UserName = 'public' AND Status = 'Active') BEGIN SET @ClientUserAccess_Flag = 1 END INSERT INTO #tInfoCache SELECT 'CU01' AS PropertyID ,'Flag for ClientUser is an active User' AS PropertyName ,0 AS InfoLevel ,@ClientUserAccess_Flag AS ValueInt ,0 AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary; -- Footprint protection IF @ClientUserAccess_Flag = 0 BEGIN SET @ParameterString = REPLACE (@ParameterString,@ClientUser,''); SET @Username = ''; END -- INFO LEVEL 1 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- Only if ClientUser has Access, further Informations are delivered (valid for all Info Levels) IF @ClientUserAccess_Flag = 1 BEGIN -- Clustername INSERT INTO #tInfoCache SELECT 'C01' AS PropertyID ,'ClusterName' AS PropertyName ,1 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary; UPDATE #tInfoCache SET ValueText = S.ValueText FROM system.tSettings S WHERE S.SettingID = 'ClusterName' AND #tInfoCache.PropertyID = 'C01'; -- Clusterdescription INSERT INTO #tInfoCache SELECT 'C02' AS PropertyID ,'ClusterDescription' AS PropertyName ,1 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary; UPDATE #tInfoCache SET ValueText = S.ValueText FROM system.tSettings S WHERE S.SettingID = 'ClusterDescription' AND #tInfoCache.PropertyID = 'C02'; -- Clusterversion INSERT INTO #tInfoCache SELECT 'C03' AS PropertyID ,'OCT.core Database Version' AS PropertyName ,1 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,ValueText AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary FROM system.tSettings WHERE SettingID = 'DBVersion'; -- Cluster_Tile_Logo INSERT INTO #tInfoCache SELECT 'C04' AS PropertyID ,'Cluster_Tile_Logo' AS PropertyName ,1 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary; UPDATE #tInfoCache SET ValueBinary = FileBody FROM planning.tFiles WHERE FileID = 'Cluster_Tile_Logo' AND #tInfoCache.PropertyID = 'C04'; -- Maintenance Mode -> this property exists only after first saving of database properties INSERT INTO #tInfoCache SELECT 'C05' AS PropertyID ,'Cluster is in Maintenance Mode Flag' AS PropertyName ,1 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary; UPDATE #tInfoCache SET ValueInt = S.ValueInt FROM system.tSettings S WHERE S.SettingID = 'MaintenanceMode' AND #tInfoCache.PropertyID = 'C05'; -- Scheduler Owner -> this property exists only after first saving of database properties INSERT INTO #tInfoCache SELECT 'C06' AS PropertyID ,'IntegrationPreferencesSchedulerOwnerServer' AS PropertyName ,1 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary; UPDATE #tInfoCache SET ValueText = S.ValueText FROM system.tSettings S WHERE S.SettingID = 'IntegrationPreferencesSchedulerOwnerServer' AND #tInfoCache.PropertyID = 'C06'; -- Cluster_Logo INSERT INTO #tInfoCache SELECT 'C07' AS PropertyID ,'Cluster_Logo' AS PropertyName ,1 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary; UPDATE #tInfoCache SET ValueBinary = FileBody FROM planning.tFiles WHERE FileID = 'Cluster_Logo' AND #tInfoCache.PropertyID = 'C07'; -- Usercount for Licence information INSERT INTO #tInfoCache SELECT 'C10' AS PropertyID ,'Number of active Users' AS PropertyName ,1 AS InfoLevel ,COUNT(UserKey) AS ValueInt ,0 AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary FROM system.trUser WHERE Status = 'Active'; -- Information, if the active User is an OCT Clusteradmin INSERT INTO #tInfoCache SELECT 'CU01' AS PropertyID ,'User is OCT Administrator' AS PropertyName ,1 AS InfoLevel ,COUNT(UserKey) AS ValueInt ,0 AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary FROM system.trUser WHERE UserName = @ClientUser AND IsAdministratorFlag = 1; END -- INFO LEVEL 2 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- Informations for higher Info Levels IF @ClientUserAccess_Flag = 1 AND @InfoLevel >= 2 BEGIN -- Databaseroles of the Clientuser -- Works only if the Clientuser is not the dbo -- returns no Serverroles -- returns no roles if the dabaserole is inherited from a serverrole (e.g. somebody has db_owner role due to sysadmin login etc.) INSERT INTO #tInfoCache SELECT 'CU10' AS PropertyID ,'Database Roles of the Clientuser' AS PropertyName ,2 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,COALESCE( STUFF( -- Stuffs all names in one list ( SELECT DP1.[name] +', ' FROM sys.database_role_members DRM RIGHT OUTER JOIN sys.database_principals DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.[type] = 'R' AND DP2.[name] = @ClientUser FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,0,'' ) , 'No direct assigned Databaseroles (Serveroles,dbo status, groupmemberships or admin status over AAD may be given !)' ) AS ValueText --Databaserolename ,NULL AS ValueDatetime ,NULL AS ValueBinary; -- dbo determination - its different on Azure and on-prem IF CONVERT(NVARCHAR(255),SERVERPROPERTY ('Edition')) LIKE '%Azure%' BEGIN INSERT INTO #tInfoCache SELECT 'DB01' AS PropertyID ,'Name or sid of dbo' AS PropertyName ,2 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,CONVERT(NVARCHAR(255),sid,2) AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary FROM sys.sysusers where name = 'dbo' END ELSE -- on prem BEGIN INSERT INTO #tInfoCache SELECT 'DB01' AS PropertyID ,'Name of dbo' AS PropertyName ,2 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,SUSER_SNAME(owner_sid) AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary FROM sys.databases WHERE name = DB_NAME(); END -- Database Rows Size INSERT INTO #tInfoCache SELECT 'DB02' AS PropertyID ,'Database Size (Rows) in GB' AS PropertyName ,2 AS InfoLevel ,0 AS ValueInt ,SUM(CAST(size AS MONEY) * 8/1024/1024) AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary FROM sys.database_files WHERE type = 0; -- Database Log Size INSERT INTO #tInfoCache SELECT 'DB03' AS PropertyID ,'Database Size (Log) in GB' AS PropertyName ,2 AS InfoLevel ,0 AS ValueInt ,SUM(CAST(size AS MONEY) * 8/1024/1024) AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary FROM sys.database_files WHERE type = 1; -- Options to To: -- Last Database Read Access -- Last Database Write Access -- Number of scheduled Pipelines END -- OUTPUT SELECT * FROM #tInfoCache WHERE InfoLevel <= @InfoLevel ORDER BY InfoLevel, PropertyID; SET @ResultCode = 200; END TRY -- START CATCH *********************************************************************************** BEGIN CATCH DECLARE @Error_state INT = ERROR_STATE(); SET @Comment = ERROR_MESSAGE(); IF @Error_state <> 10 BEGIN SET @ResultCode = 500; PRINT 'Rollback due to not executable command.'; END ELSE IF @ResultCode IS NULL OR @ResultCode/100 = 2 BEGIN SET @ResultCode = 500; END; END CATCH EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @EffectedRows, @ResultCode, @TimestampCall, @Comment; RETURN @ResultCode; END GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'system' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spGET_DBInfo' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1; -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'Procedure to get a collection of database informations. Pass the Clientusername SQL to request the rights of the service.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@ClientUser'; SET @value = N'Not logged if its not a clustermember to protect from footprinting access scans.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@InfoLevel'; SET @value = N'Control the amount of informations deliverd. 1 = Basic Info, 2 = extended Info.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS system.spMOVE_Connector; GO /* Procedure to MOVE a Connector Saxess Software GmbH Testcall DECLARE @RC INT; EXEC @RC = system.spMOVE_Connector @Username = N'SQL' , @SourceSourceSystemID = N'SAGE100DE' , @SourceSourceSystemType = N'MSSQL' , @SourceModuleID = N'FIN' , @TargetSourceSystemID = N'SAGE100DERENAMED' , @TargetSourceSystemType = N'ORACLE' SELECT @RC; Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spMOVE_Connector', NULL, NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spMOVE_Connector', 'PARAMETER', NULL) */ CREATE PROCEDURE system.spMOVE_Connector @Username NVARCHAR(255) , @SourceSourceSystemID NVARCHAR(255) , @SourceSourceSystemType NVARCHAR(255) , @SourceModuleID NVARCHAR(255) , @TargetSourceSystemID NVARCHAR(255) , @TargetSourceSystemType NVARCHAR(255) AS BEGIN BEGIN TRY -- Logging DECLARE @TimestampCall DATETIME = GETUTCDATE(); DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); DECLARE @AffectedRows INT = 0; DECLARE @ResultCode INT = 501; DECLARE @Comment NVARCHAR(4000) = N''; DECLARE @ParameterString NVARCHAR(MAX) = N''; DECLARE @TransactUsername NVARCHAR(255) = N''; EXEC system.spGET_ParameterString @ParameterString OUTPUT, 6, @Username, @SourceSourceSystemID, @SourceSourceSystemType, @SourceModuleID, @TargetSourceSystemID, TargetSourceSystemType; EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username; -- Input parameter handling SET @SourceSourceSystemID = COALESCE(dbo.sx_pf_pProtectID(@SourceSourceSystemID), N''); SET @SourceSourceSystemType = COALESCE(@SourceSourceSystemType, N''); SET @SourceModuleID = COALESCE(dbo.sx_pf_pProtectID(@SourceModuleID), N''); SET @TargetSourceSystemID = COALESCE(dbo.sx_pf_pProtectID(@TargetSourceSystemID), N''); SET @TargetSourceSystemType = COALESCE(@TargetSourceSystemType, N'') -- Error handling IF @SourceSourceSystemID = N'' EXEC system.spSEND_Message N'ERROR', N'No SourceSourceSystemID provided' IF @SourceSourceSystemType = N'' EXEC system.spSEND_Message N'ERROR', N'No SourceSourceSystemType provided' IF @SourceModuleID = N'' EXEC system.spSEND_Message N'ERROR', N'No SourceModuleID provided' IF @TargetSourceSystemID = N'' EXEC system.spSEND_Message N'ERROR', N'No TargetSourceSystemID provided' IF @TargetSourceSystemType = N'' EXEC system.spSEND_Message N'ERROR', N'No TargetSourceSystemType provided' IF EXISTS( SELECT 1 FROM system.tConnectors WHERE SourceSystemID = @TargetSourceSystemID AND SourceSystemType = @TargetSourceSystemType AND ModuleID = @SourceModuleID ) EXEC system.spSEND_Message N'ERROR', N'Connector already exists' -- Rename Connector UPDATE system.tConnectors SET SourceSystemID = @TargetSourceSystemID , SourceSystemType = @TargetSourceSystemType WHERE SourceSystemID = @SourceSourceSystemID AND SourceSystemType = @SourceSourceSystemType AND ModuleID = @SourceModuleID; SET @AffectedRows = @@ROWCOUNT; IF @AffectedRows = 0 EXEC system.spSEND_Message N'ERROR', N'Connector doesn''t exist' SET @ResultCode = 200 END TRY BEGIN CATCH SET @ResultCode = 500; SET @Comment = ERROR_MESSAGE(); END CATCH; EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment; IF @ResultCode >= 500 EXEC system.spSEND_Message N'ERROR', @Comment; RETURN @ResultCode; END; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'system' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spMOVE_Connector' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.Connectors, Custom) ,@SX_Connector NVARCHAR(255) = N'CORE' -- enter Connector name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Connector; EXEC sys.sp_addextendedproperty N'SX_Connector' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'Procedure to GET the Connector tree'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- SET parameter documentation SET @level2name = N'@Username'; SET @value = N'Username'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@SourceSourceSystemID'; SET @value = N'Source System ID of the connector that should be renamed.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@SourceSourceSystemType'; SET @value = N'Source System Type of the connector that should be renamed.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@SourceModuleID'; SET @value = N'Module ID of the connector that should be renamed.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@TargetSourceSystemID'; SET @value = N'New Source System ID of the connector.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@TargetSourceSystemType'; SET @value = N'New Source System Type of the connector.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS system.spPOST_Connector; GO /* Procedure to POST a connector Saxess Software GmbH Testcall Procedure DECLARE @RC INT EXEC @RC = system.spPOST_Connector @Username = N'SQL' , @SourceSystemID = N'NAVISION' , @SourceSystemType = N'MSSQL' , @ModuleID = N'FIN' , @ConnectorDescription = N'FIN Connector for Microsoft Navision based on a SQL Server database.' , @ConnectorDescriptionURL = N'https://www.onecooltool.de/standardschnittstellen-fuer-das-rechnungswesen/ms-dynamics-nav/' , @ConnectorVersion = 1 PRINT @RC; SELECT * FROM system.tConnectors; DELETE FROM system.tConnectors; Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spPOST_Connector', NULL, NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spPOST_Connector', 'PARAMETER', NULL) */ CREATE PROCEDURE system.spPOST_Connector @Username NVARCHAR(255) , @SourceSystemID NVARCHAR(50) , @SourceSystemType NVARCHAR(50) , @ModuleID NVARCHAR(50) , @ConnectorDescription NVARCHAR(4000) = NULL , @ConnectorShortDescription NVARCHAR(255) = NULL , @ConnectorDescriptionURL NVARCHAR(255) = NULL , @ConnectorVersion INT , @Manufacturer NVARCHAR(255) = NULL AS BEGIN BEGIN TRY -- Logging DECLARE @TimestampCall DATETIME = GETUTCDATE(); DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); DECLARE @AffectedRows INT = 0; DECLARE @ResultCode INT = 501; DECLARE @Comment NVARCHAR(4000) = N''; DECLARE @ParameterString NVARCHAR(MAX) = N''; DECLARE @TransactUsername NVARCHAR(255) = N''; EXEC system.spGET_ParameterString @ParameterString OUTPUT, 9, @Username, @SourceSystemID, @SourceSystemType, @ModuleID, @ConnectorDescription, @ConnectorShortDescription, @ConnectorDescriptionURL, @ConnectorVersion, @Manufacturer; EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username; -- Input parameter handling SET @SourceSystemID = COALESCE(dbo.sx_pf_pProtectID(@SourceSystemID), N''); SET @SourceSystemType = COALESCE(@SourceSystemType, N''); SET @ModuleID = COALESCE(dbo.sx_pf_pProtectID(@ModuleID), N''); SET @ConnectorDescription = COALESCE(@ConnectorDescription, N''); SET @ConnectorShortDescription = COALESCE(@ConnectorShortDescription, N''); SET @ConnectorDescriptionURL = COALESCE(@ConnectorDescriptionURL, N''); SET @ConnectorDescriptionURL = COALESCE(@ConnectorVersion, 1); SET @Manufacturer = COALESCE(@Manufacturer, N''); IF @SourceSystemType NOT IN (N'MSSQL', N'ORACLE', N'ODBC') SET @SourceSystemType = N'MSSQL'; -- Data Transaction DROP TABLE IF EXISTS #CurrentConnector; CREATE TABLE #CurrentConnector ( ConnectorKey BIGINT NOT NULL , SourceSystemID NVARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL , SourceSystemType NVARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL , ModuleID NVARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL , ConnectorDescription NVARCHAR(4000) COLLATE DATABASE_DEFAULT NOT NULL , ConnectorShortDescription NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL , ConnectorDescriptionURL NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL , ConnectorVersion INT NOT NULL , Manufacturer NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ); -- Cache current values INSERT INTO #CurrentConnector SELECT ConnectorKey , SourceSystemID , SourceSystemType , ModuleID , ConnectorDescription , ConnectorShortDescription , ConnectorDescriptionURL , ConnectorVersion , Manufacturer FROM system.tConnectors WHERE SourceSystemID = @SourceSystemID AND SourceSystemType = @SourceSystemType AND ModuleID = @ModuleID; IF @@ROWCOUNT = 0 BEGIN -- new connector INSERT INTO system.tConnectors ( SourceSystemID , SourceSystemType , ModuleID , ConnectorDescription , ConnectorShortDescription , ConnectorDescriptionURL , ConnectorVersion , Manufacturer ) VALUES ( @SourceSystemID , @SourceSystemType , @ModuleID , ISNULL(@ConnectorDescription, N'') , ISNULL(@ConnectorShortDescription, N'') , ISNULL(@ConnectorDescriptionURL, N'') , ISNULL(@ConnectorVersion, 1) , ISNULL(@Manufacturer, N'') ); SET @ResultCode = 201; END ELSE BEGIN -- existing Connector UPDATE tC SET ConnectorDescription = ISNULL(@ConnectorDescription, cC.ConnectorDescription) , ConnectorShortDescription = ISNULL(@ConnectorShortDescription, cC.ConnectorShortDescription) , Manufacturer = ISNULL(@Manufacturer, cC.Manufacturer) FROM system.tConnectors AS tC LEFT JOIN #CurrentConnector AS cC ON tC.SourceSystemID = cC.SourceSystemID AND tC.SourceSystemType = cC.SourceSystemType AND tC.ModuleID = cC.ModuleID WHERE tC.SourceSystemID = cC.SourceSystemID AND tC.SourceSystemType = cC.SourceSystemType AND tC.ModuleID = cC.ModuleID; SET @ResultCode = 200; END SET @AffectedRows = @@ROWCOUNT; END TRY BEGIN CATCH SET @ResultCode = 500; SET @Comment = ERROR_MESSAGE(); END CATCH; EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment; IF @ResultCode >= 500 EXEC system.spSEND_Message N'ERROR', @Comment; RETURN @ResultCode; END; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'system' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spPOST_Connector' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'Procedure to POST a Connector.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@SourceSystemID'; SET @value = N'SourceSystemID - ID of the source ERP system.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@SourceSystemType'; SET @value = N'SourceSystemType - type of connection to the source ERP system (MSSQL, ORACLE, ODBC).'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ModuleID'; SET @value = N'ModuleID - ID of the module.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ConnectorDescription'; SET @value = N'Description or installation notes of the connector.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ConnectorShortDescription'; SET @value = N'Short description of the connector.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ConnectorVersion'; SET @value = N'Version number of the connector.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@Manufacturer'; SET @value = N'Company that created the source system.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS system.spPOST_ConnectorQuery; GO /* Procedure to POST one query of a connector Saxess Software GmbH Testcall Procedure DECLARE @RC INT EXEC @RC = system.spPOST_ConnectorQuery @Username = N'SQL' , @SourceSystemID = N'NAVISION' , @SourceSystemType = N'MSSQL' , @ModuleID = N'FIN' , @QueryName = N'AccountingJournal' , @QuerySQL = N'SELECT 123 AS TestField' , @QueryVersionComment = 'A comment' PRINT @RC; SELECT * FROM system.tConnectorQueries; DELETE FROM system.tConnectorQueries; Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spPOST_ConnectorQuery', NULL, NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spPOST_ConnectorQuery', 'PARAMETER', NULL) */ CREATE PROCEDURE system.spPOST_ConnectorQuery @Username NVARCHAR(255) , @SourceSystemID NVARCHAR(50) , @SourceSystemType NVARCHAR(50) , @ModuleID NVARCHAR(50) , @QueryName NVARCHAR(50) , @QuerySQL NVARCHAR(MAX) = NULL , @QueryVersionComment NVARCHAR(4000) = NULL AS BEGIN BEGIN TRY -- Logging DECLARE @TimestampCall DATETIME = GETUTCDATE(); DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); DECLARE @AffectedRows INT = 0; DECLARE @ResultCode INT = 501; DECLARE @Comment NVARCHAR(4000) = N''; DECLARE @ParameterString NVARCHAR(MAX) = N''; DECLARE @TransactUsername NVARCHAR(255) = N''; EXEC system.spGET_ParameterString @ParameterString OUTPUT, 7, @Username, @SourceSystemID, @SourceSystemType, @ModuleID, @QueryName, @QuerySQL, @QueryVersionComment; EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username; -- Input parameter handling SET @SourceSystemID = COALESCE(dbo.sx_pf_pProtectID(@SourceSystemID), N''); SET @SourceSystemType = COALESCE(@SourceSystemType, N''); SET @ModuleID = COALESCE(dbo.sx_pf_pProtectID(@ModuleID), N''); SET @QueryName = COALESCE(@QueryName, N''); SET @QuerySQL = COALESCE(@QuerySQL, N''); SET @QueryVersionComment = COALESCE(@QueryVersionComment, N''); -- Check if connector exists IF NOT EXISTS( SELECT 1 FROM system.tConnectors WHERE SourceSystemID = @SourceSystemID AND SourceSystemType = @SourceSystemType AND ModuleID = @ModuleID ) EXEC system.spSEND_Message N'ERROR', N'Connector doesn''t exist.'; -- Get ConnectorKey DECLARE @ConnectorKey BIGINT; SELECT @ConnectorKey = ConnectorKey FROM system.tConnectors WHERE SourceSystemID = @SourceSystemID AND SourceSystemType = @SourceSystemType AND ModuleID = @ModuleID; -- Get next version number DECLARE @QueryVersion INT; SELECT @QueryVersion = COALESCE(MAX(QueryVersion) + 1, 1) FROM system.tConnectorQueries WHERE ConnectorKey = @ConnectorKey AND QueryName = @QueryName; -- Create new query version INSERT INTO tConnectorQueries ( ConnectorKey , QueryName , QuerySQL , QueryVersion , QueryVersionComment , Timestamp , CreatedBy ) VALUES ( @ConnectorKey , @QueryName , @QuerySQL , @QueryVersion , @QueryVersionComment , SysUTCDateTime() , @TransactUsername ); SET @AffectedRows = @@ROWCOUNT; SET @ResultCode = 200; END TRY BEGIN CATCH SET @ResultCode = 500; SET @Comment = ERROR_MESSAGE(); END CATCH; EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment; IF @ResultCode >= 500 EXEC system.spSEND_Message N'ERROR', @Comment; RETURN @ResultCode; END; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'system' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spPOST_ConnectorQuery' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'Procedure to POST a Connector Query.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@SourceSystemID'; SET @value = N'SourceSystemID - ID of the source ERP system.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@SourceSystemType'; SET @value = N'SourceSystemType - type of connection to the source ERP system (MSSQL, ORACLE, ODBC).'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ModuleID'; SET @value = N'ModuleID - ID of the module.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@QueryName'; SET @value = N'QueryName - name of the query and target integration table.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@QuerySQL'; SET @value = N'SQL statement that is used to read data from the source system.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@QueryVersionComment'; SET @value = N'Comment for this specific version of the connector query.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO UPDATE system.tSettings SET ValueText = '2026.02.0', ValueInt = 2026020 WHERE SettingID = 'DBVersion'