EONE BLOG

Tech Tuesday: Executing SmartConnect Maps from SQL


There are many ways to execute a SmartConnect map; through the interface, scheduled, called by the web service, etc. But one method that also works which isn’t talked about as much is executing a map directly from SQL. The SQL code below covers both the initial setup needed to run maps in this manner as well as the actual call to execute them.

First we need to make sure that SQL can run the command that is needed and that it will pass SmartConnect security. In the code below, the only value that should need to be changed is @sqlServiceAcct. This should be set to the account that is running the actual SQL Server windows service. This is needed as any map executed directly from SQL is running under that account and it needs to be in SmartConnect security – it normally isn’t a standard domain account so we add it through here rather than trying to do it through the interface. The rest of the statement enables the xp_cmdshell command within SQL Server. This is disabled by default, but we need it to call our .exe file.

  — Set the parameter to the account running the SQL Server Windows Service
  DECLARE @sqlServiceAcct Char(100)
  SELECT @sqlServiceAcct = ‘NT Service\MSSQL$EONE’

  INSERT INTO [dbo].[User] ([UserId],[AdUser],[AdUserName],[LoginName],[LoginPassword],[Email],[ChangePassAtLogon],[LoginLockedDateTime],[IsActive],[Password],[IsAdmin],       [FirstName],[LastName])
  VALUES (NEWID(), @sqlServiceAcct, @sqlServiceAcct,”,”,”,0,’1900-01-01 00:00:00.000′,1,”,1,’SQL’,’Service’)


  — To allow advanced options to be changed.
  EXEC sp_configure ‘show advanced options’, 1;
  GO
  — To update the currently configured value for advanced options.
  RECONFIGURE;
  GO
  To enable the feature.
  EXEC sp_configure ‘xp_cmdshell’, 1;
  GO
  — To update the currently configured value for this feature.
  RECONFIGURE;
  GO

Once that statement has been executed, we can run any map we want from inside of SQL server using the command listed below. One requirement for this to work is that SmartConnect has to be installed on the SQL Server machine. If SmartConnect isn’t installed on the server then we can’t access the needed .exe from our SQL command. The only value that would change in the following statement is the Map ID, in this example I’m calling a map called TEST_MAP and that should be changed to whatever ID is needed (The path would also be changed if SmartConnect was installed somewhere other than the default location).

  exec master..xp_cmdshell ‘”C:\Program Files (x86)\eOne Solutions\SmartConnect\eOne.SmartConnect.RunMapConsole.exe” TEST_MAP’, no_output;

That command calls the RunMapConsole.exe file and passes in the Map ID that we want to run. The map will be executed with its data source and setup from SmartConnect. The no_output command is added to the call as this .exe shows a lot of unneeded SQL statements while it is running. One thing that the .exe does not do is report the result of the actual map run. There will need to be a process for logging errors and/or checking the results through the error tables in SQL or the event viewer to determine if the map completed or not when using this method.

Have any questions on SmartConnect? Leave a comment below or email sales@eonesolutions.com!

7 Comments

  1. Marie Solange LeBrasseur on August 26, 2015 at 7:36 am

    Hi,
    I want to start a map from SQL and I received this error, can you help me on this ?

    ERROR [HY000] [Microsoft] [ODBC Excel Driver] General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x27a4…

    MSL

    • Nick Waverek on September 4, 2015 at 2:10 pm

      Hi Marie,

      I briefly discussed your question with the technical team, sound like you probably don’t have any/or the correct excel driver installed on the machine where SQL Server is. If you are still running into any issues after trying that. Feel free to open up a support case.

  2. Lyn Barr on August 17, 2016 at 12:22 pm

    I have so much trouble with schedules via the interface, but this method works GREAT! I’m going to use it as my default scheduling method from now on.

    Thanks, Chris!

  3. TPetersen on June 26, 2017 at 3:01 pm

    This doesn’t work for me. It says the query is successful but the map doesn’t run. Any ideas?

    • Jody Wood on October 19, 2017 at 1:18 pm

      +1 to TPetersen. Could it be an issue with running this in SC 2016.

      In an effort to find a solution for ending a map if the source doesn’t contain data (without generating an error in the Event Viewer), I thought I could use this as a solution.

      The real solution would be to stop processing the map if no data was returned without generating an error in the event logs.

  4. Glenn Lasowski on July 12, 2019 at 7:50 am

    Do you have a newer version of this? When I try running it I get “Column name or number of supplied values does not match table definition”

    Thanks

  5. Curtis Beethe on October 31, 2019 at 4:03 pm

    Late to the party but I would be interested in knowing if this approach might be appropriate if I need to conditionally run a map. My use case is Map A runs and may or may not raise a flag. If the flag is raised then run Map B. If not raised then run Map C. In either case I need to run Map C afterwards. If Map B is called, I need it to finish processing before calling Map C.

    Thanks

Leave a Comment





RECENT POSTS


D365 Business Central Integration Bootcamps - Online, July & August 2021
Popdock Bootcamp - Online in July & August 2021
Technical Update for SmartConnect regarding Dynamics 365 Customer Engagement
US Office Closed - Memorial Day, May 31, 2021
Migrating Smartlist data from Dynamics GP to a Azure Data Lake

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

ARCHIVES

open all | close all

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.