Accumulating Data from Third-Party Sources via a Linked SQL Server
Summary: intapiuser provides a detailed guide on using a linked SQL Server to execute commands with external data sources, specifically Netsuite, to create accumulated Transactions Tables using SQL jobs. The document includes prerequisites such as installing ODBC and ensuring a standard SQL Server license, as SQL Server Express has limitations. The steps cover creating history and new transactions tables, setting up and configuring SQL jobs, and modifying job schedules and steps to manage data effectively. The process concludes with appending new data to the History table.
This document describes how to use a linked SQL Server to execute commands to external data sources (in this case - Netsuite), and create accumulated Transactions Tables with SQL JOBS.
Before you Begin
Make sure to install the ODBC for your database on the server.
SQL Server Express supports up to 10GB for the DB size, 1GB of RAM usage, and does not allow for agents (job scheduler). Using this solutionrequires a standard SQL Server license. This documentation from Microsoft outlines differences between SQL server versions.
To create a job, a user must be a member of one of the SQL Server Agent fixed database roles or the sysadmin fixed server role. A job can be edited only by its owner or members of the sysadmin role. For more information about the SQL Server Agent fixed database roles, see SQL Server Agent Fixed Database Roles. Assigning a job to another user does not guarantee that the new owner will have sufficient permission to run the job successfully.
Local jobs are cached by the local SQL Server Agent. Therefore, any modifications force the SQL Server Agent to re-cache the job. Because SQL Server Agent does not cache the job until sp_add_jobserver is called, it is recommended to call sp_add_jobserverlast.
Implementation Steps
1 - Create a History table with all the transactions with the following syntax:
Select CAST(TRANSACTION_ID AS bigint)*100000000+year(MODIFIED)*10000+month(MODIFIED)*100+day(MODIFI ED) as TransKey, ACCOUNTING_PERIOD_ID ,
BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID ,RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED
into TransHistory from openquery([NETSUITE], 'select
ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER ,LAST_MODIFIED_DATE as MODIFIED from [ | Reporting View Only (ODBC)].[TRANSACTIONS]')
2 - Create a “New Transactions” table with the following syntax (should be empty in the beginning):
select CAST(TRANSACTION_ID AS bigint)*100000000+year(MODIFIED)*10000+month(MODIFIED)*100+day(MODIFI ED) as TransKey,
ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE ,TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED
into NewTrans
from
( select * from ( Select CAST(TRANSACTION_ID AS bigint)*100000000+year(MODIFIED)*10000+month(MODIFIED)*100+day(MODIFI ED) as TransKey,
ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED
from openquery([NETSUITE], 'select
ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , LAST_MODIFIED_DATE as MODIFIED
from [ | Reporting View Only (ODBC)].[TRANSACTIONS]')) t except Select
TransKey, ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED
from [dbo].[TransHistory] )t
3 - Create a new job with the following configurations:
4 - Set the desired schedule:
5 - Define the Job steps:
Step 1 - TRUNCATE [NewTrans] TRUNCATE TABLE [dbo].[NewTrans]
Step 2- Create a New Transactions Table
TRUNCATE TABLE [dbo].[NewTrans]
insert into NewTrans
(
TransKey,
ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED )
select * from (Select
CAST(TRANSACTION_ID AS bigint)*100000000+year(MODIFIED)*10000+month(MODIFIED)*100+day(MODIFI ED) as TransKey,
ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED
from openquery([NETSUITE], 'select
ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , LAST_MODIFIED_DATE as MODIFIED
from [ | Reporting View Only (ODBC)].[TRANSACTIONS]')) t except Select
TransKey, ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED
from [dbo].[TransHistory]
Step 3 - Append new table to History table
insert into [dbo].[TransHistory] (TransKey, ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED)
select TransKey,
ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED
from [dbo].[NewTrans]
6 - That’s it, you’re done!
