All Forums -> Forum: Dynamics GP Adapter for Insight -> Topic: How to call stored procedure smGetNex...
Forum Topic: "How to call stored procedure smGetNextNoteIndex located in the DYNAMICS database to get the next note index?"

I'm working in Scribe 7 and the most current version of the Adapter for Dynamics GP, and GP 10.0 SP4.

 I'm working with tables from the ISV Binary Stream, and the tables have fields for noteindex numbers. I'd like to call the stored procedure smGetNextNoteIndex that is in the DYNAMICS database to get the next note index that will be stored with the record in the Binary Stream table, and to increment the next note index number in the SY01500 table in the DYNAMICS database.

When I connect to the DYNAMICS database using the Native SQL connection option, I see a list of tables and views, but not the stored procedures. I have set the option in the Scribe DTS to show hidden objects.

How can I call the stored procedure and capture the note index number that it returns?

Posted by Michael Cross (michael.cross@tribridge.com) on Sep 01, 2010 03:47AM Return to top

I figured it out. I had to run the ScribeMetadata.sql script against the Dynamics database.

In the DTS, I added steps to execute the stored procedure. For each step executing the stored procedure, I had to provide the Company Id (I used the DBLOOKUP and DBNAME functions) from the table SY01500 in the Dynamics database, as well as the value 0 for the session id parameter. I created a target variable for each step to store the note index output variable. I then mapped the variable to the appropriate NOTEINDX field.

I had to add a step for EACH note field (one table has 11 note fields).

This is only possible thanks to Scribe 7's new feature allowing multiple targets. This allows me to eliminate a custom stored procedure that is located in the GP company database. Thanks, Scribe!

Posted by Michael Cross (michael.cross@tribridge.com) on Sep 01, 2010 05:41AM Return to top

I should have been more explicit and mentioned that I created a connection to the DYNAMICS database in order to get access to the stored procedure smGetNextNoteIndex.

Posted by Michael Cross (michael.cross@tribridge.com) on Sep 01, 2010 12:39PM Return to top
Add Comment Back to Topics