In Touch with Dynamics GP

This blog is intended to be a useful resource for Microsoft Dynamics GP users and professionals in the UK and the wider Microsoft Dynamics GP community as a whole.

Tuesday, 19 January 2010

How to Create SQL Scripts in Integration Manager without hardcoding the database

Here is a handy bit of code. Have you ever wanted to run a sql script in Integration manager after the task completes but you don't want to hard code the company database so you can use it against multiple companies?

Well here is the code to do this (in version 10), in the script window within Integration manager start off by setting the ADODB connection.

set MyCon = CreateObject("ADODB.Connection")

Then use the connection string to bring back the inter company ID

MyCon.Connectionstring = "database=" + GPConnection.GPConnInterCompanyID

Open your connection

GPConnection.Open(MyCon)


You can then construct your SQL statement, the below is an update statement

updatecommand = "update PM00200 "
updatecommand = updatecommand & " set PHNUMBR1 = 'xxxxx',"
updatecommand = updatecommand & " PHNUMBR2 = 'xxxxx',"
updatecommand = updatecommand & " PHONE3 = 'xxxx',"
updatecommand = updatecommand & " FAXNUMBR = 'xxxx'"

Then close the record set and exercute the command

recset = MyCon.Execute(updatecommand )

This becomes a very powerful tool when you combine it with before scripts which will enable you to fill out blank fields with place holders etc and then remove the place holders after the integration completes.





Posted by at
Labels: ,

4 comments:

  1. Anonymous30 April 2013 at 11:25

    Hi Andy,

    Thanks you for this article. it helped me with an Integration problem that we had.

    Cheers,
    Phil Clarke

    ReplyDelete
  2. Anonymous13 May 2015 at 17:01

    Helped me as well!!

    ReplyDelete
  3. Anonymous12 June 2015 at 00:12

    Please Help! The following is my script, but when I try to integrate, I receive:
    Error Executing Script - Permission denied

    Here is the script
    sItemAccountFieldName="IVSLSIDX"

    sItemNumber = SourceFields("SOP Items.Location Code")

    'Open a connection to the SQL database
    Set MyCon = CreateObject("ADODB.Connection")
    MyCon.ConnectionString = "database=" & GPConnection.GPConnInterCompanyID
    GPConnection.Open(MyCon)

    'Create a new recordset -- that will hold returned data
    Set recset = CreateObject("ADODB.Recordset")

    'Create a SQL SELECT statement to retrieve the item's default
    'GL sales account number
    sSQL = "SELECT Rtrim(ACTNUMBR_1) 'Segment1', "
    sSQL = sSQL & "Rtrim(ACTNUMBR_2) 'Segment2', "
    sSQL = sSQL & "Rtrim(ACTNUMBR_3) 'Segment3', "
    sSQL = sSQL & "Rtrim(ACTNUMBR_4) 'Segment4' "
    sSQL = sSQL & "FROM IV00101, GL00100 WHERE "
    sSQL = sSQL & "IV00101.ITEMNMBR= '" & sItemNumber & "'"
    sSQL = sSQL & " AND IV00101." & sItemAccountFieldName
    sSQL = sSQL & " =GL00100.ACTINDX"

    'Open the recordset using the SQL statement
    Set recset = MyCon.Execute(sSQL)

    'Set the value of Segments 1, 2, 3 and 4
    sSegment1=recset("Segment1")
    sSegment2=recset("Segment2")
    sSegment3=recset("Segment3")
    sSegment4=recset("Segment4")

    'Concatenate the four segments and set the current field to the
    'resulting value
    CurrentField= sSegment1 & "-" & sSegment2 & "-" & sSegment3 & "-" & sSegment4

    'Close connection when finished
    Call MyCon.Close
    'Release the object
    Set MyCon = Nothing

    ReplyDelete
  4. Anonymous12 June 2015 at 00:55

    Never mind, discovered the Options for distributions was not on Default.

    ReplyDelete

Subscribe to: Post Comments (Atom)