This came up for me 2x this week alone on 2 different projects. Just about every time you have to insert a record into a GP Table you have to get the next note index. While it's a simple thing, it may not be obvious exactly how to do it.
Run this against your COMPANY database to increment and return the next note index from the DYNAMICS.dbo.SY01500 table:
--Declare some variables
DECLARE @I_sCompanyID smallint,
@O_mNoteIndex numeric(19,5),
@O_iErrorState int
--Get the CompanyID
select @I_sCompanyID = CMPANYID
from DYNAMICS..SY01500
where INTERID = DB_Name()
--Get and increment the next note index
exec DYNAMICS.[dbo].[smGetNextNoteIndex] @I_sCompanyID, 1, @O_mNoteIndex output, @O_iErrorState output
--Print the Next Note Index
print(@O_mNoteIndex)
--Print the Error State
print(@O_iErrorState)
9 comments:
Thanks a lot for this excellent post--your timing could not have been better. I just had to create a mod to insert a new batch record, which of course requires a valid note index. Saved me a fair amount of time trying to remember / track down the stored proc and figure out it's proper usage.
This appears to be for a pure-SQL insertion, am I right?
Are you familiar with how this would be done in Integration Manager? Would a "Before Document" script be used? What would the scripting syntax look like and how would the returned NOTEINDX be mapped to the destination adapter? Thanks!
Devo,
You could use this in DIM on the Before Document script. You'd have to massage the syntax of course but technically, you could do it.
In what scenario does DIM not automatically handle the NOTEINDX for you? I'll be happy to help with that problem.
Thanks
You're right, we double checked and DIM did handle it appropriately. Thanks.
Thank you for this post. Perfect time for me as well... had a situation this morning where data being integrated through sql script into gp was not actually filling in the NoteIndx field. The script worked great!!
really amazing blog and I was thankful to you for sharing such a useful information.
- Microsoft Dynamics CRM
Hi Michael,
How can I use this to insert multiple records into a table in SQL or table import
Janakiram,
I use this often in cursors and loops. You could loop through your records and get the next note index before inserting each one. Just paste the code in this post inside your fetch.
Here's a template for a cursor you might find helpful. http://mbsguru.blogspot.com/2010/08/row-by-row-analysis-and-execution-with.html
Let me know if this doesn't help.
Michael
Thanks Michael for answering my question and your help on this.
Post a Comment