Create a SQL agent job to monitor for new articles

SQL replication does not replicate articles (Tables, Views,…) automatically, so you must set up a SQL agent job to check regularly for any additions that may be the result of a FactoryLogix upgrade of the database schema.

While running SQL Server Management Studio (SSMS) as an Administrator, connect to the FactoryLogix database server (production database server).
Expand the SQL Server Agent folder, right-click Jobs, then select New Job.
Enter a Name for the job (for example, CheckNewArticlesForReplication), set the owner as the dbo user you created earlier (Configure SQL transactional replication | Create-Windows-accounts-for-replication), then select Steps on the left side of the window.
Select New on the Steps page, then name the step (for example, QueryForArticles).
Select the FactoryLogix production database in the Database drop-down, copy and paste the query (Create a SQL agent job to monitor for new articles | SQL-Query-for-job-creation-to-find-new-articles).
Replace the text ENTER PUBLICATION NAME HERE with the publication name) in the Command section, then select OK.
Select Schedules on the left side of the window, then select New.
Enter a Name for the schedule (for example, ArticleCheckSch).
Set the Occurs drop-down to Monthly, select one day a month to run the task, set the time on the selected day that the task should run, then select OK.
Select OK to confirm your selections.

FactoryLogix functionality utilizing the reporting database

iMonitor/Dashboards
DataMiner/Report Designer
All Reporting Services
- Report Generation/Manufacturing Workbook
- Report Scheduler
- Query Executing

SQL Query for job creation to find new articles

CODE

/* === PARAMETERS TO SET === */

DECLARE @publication sysname = N'ENTER PUBLICATION NAME HERE';
DECLARE @schema      sysname = N'dbo';           -- or a dedicated schema like 'ops'
DECLARE @preCreation nvarchar(10) = N'drop';     -- what to do at subscriber if table exists: keep/drop/truncate/none

/* === FIND NEW TABLES NOT YET PUBLISHED === */
;WITH pubs AS (
  SELECT pubid FROM syspublications WHERE name = @publication
),

candidates AS (SELECT
                T.object_id,
                S.name + N'.' + T.name [TWO_PART],
                S.name [SOURCE_OWNER],
                T.name [SOURCE_OBJECT],
                'logbased' [typ]
                FROM sys.tables T
                JOIN sys.schemas S ON S.schema_id = T.schema_id
                CROSS JOIN pubs
                LEFT JOIN sysarticles A ON A.objid = T.object_id
                    AND A.pubid = pubs.pubid
                WHERE A.artid IS NULL              -- not yet published
                    AND S.name = @schema             -- limit to chosen schema
                    AND T.is_ms_shipped = 0
                UNION ALL
                SELECT
                V.object_id,
                S.name + N'.' + V.name [TWO_PART],
                S.name [SOURCE_OWNER],
                V.name [SOURCE_OBJECT],
                'view schema only' [typ]
                FROM sys.views V
                JOIN sys.schemas S ON S.schema_id = V.schema_id
                CROSS JOIN pubs
                LEFT JOIN sysarticles A ON A.objid = V.object_id
                    AND A.pubid = pubs.pubid
                WHERE A.artid IS NULL
                    AND S.name = @schema
                    AND V.is_ms_shipped = 0
                UNION ALL
                SELECT
                P.object_id,
                S.name + N'.' + P.name [TWO_PART],
                S.name [SOURCE_OWNER],
                P.name [SOURCE_OBJECT],
                'proc schema only' [typ]
                FROM sys.procedures P
                JOIN sys.schemas S ON S.schema_id = P.schema_id
                CROSS JOIN pubs
                LEFT JOIN sysarticles A ON A.objid = P.object_id
                    AND A.pubid = pubs.pubid
                WHERE A.artid IS NULL
                    AND S.name = @schema
                    AND P.is_ms_shipped = 0
                UNION ALL
                SELECT
                O.object_id,
                S.name + N'.' + O.name [TWO_PART],
                S.name [SOURCE_OWNER],
                O.name [SOURCE_OBJECT],
                'func schema only' [typ]
                FROM sys.objects O
                JOIN sys.schemas S ON S.schema_id = O.schema_id
                CROSS JOIN pubs
                LEFT JOIN sysarticles A ON A.objid = O.object_id
                    AND A.pubid = pubs.pubid
                WHERE A.artid IS NULL
                    AND S.name = @schema
                    AND O.is_ms_shipped = 0
                    AND O.type = 'FN'
)

SELECT * INTO #to_add FROM candidates;

IF EXISTS (SELECT 1 FROM #to_add WHERE #to_add.typ = 'logbased')
BEGIN
  DECLARE @source_owner sysname, @source_object sysname, @article sysname, @type sysname, @cmd nvarchar(max);

  DECLARE cur CURSOR FAST_FORWARD FOR
    SELECT source_owner, source_object, two_part, typ FROM #to_add;

  OPEN cur;
  FETCH NEXT FROM cur INTO @source_owner, @source_object, @article, @type;

  WHILE @@FETCH_STATUS = 0
  BEGIN
    PRINT CONCAT('Adding article ', @article, ' ...');

    EXEC sp_addarticle
      @publication        = @publication,
      @article            = @article,          -- article name; two-part keeps it readable
      @source_owner       = @source_owner,
      @source_object      = @source_object,
      @type               = @type,
      @description        = NULL,
      @creation_script    = NULL,
      @pre_creation_cmd   = @preCreation

    -- If you need row filters, add sp_articlefilter here.

    FETCH NEXT FROM cur INTO @source_owner, @source_object, @article, @type;
  END

  CLOSE cur; DEALLOCATE cur;

  PRINT 'Requesting a snapshot for the new articles...';

  -- EITHER start the Snapshot Agent job by name (push publications)
  -- Job name is usually: 'Snapshot Agent publication_name'
  IF EXISTS (SELECT 1 FROM #to_add WHERE #to_add.typ = 'logbased')
  BEGIN TRY
    DECLARE @job sysname = N'Snapshot Agent ' + @publication;
    EXEC msdb.dbo.sp_start_job @job_name = @job;
  END TRY
  BEGIN CATCH
    -- Fallback: direct call in case you prefer this style
    EXEC sp_startpublication_snapshot @publication = @publication;
  END CATCH

END
ELSE
BEGIN
  PRINT 'No new tables to add.';
END
DROP TABLE IF EXISTS #to_add;