Currently Scribe Insight has a function for running SQL queries, however it only supports running them against the source, target, and internal databases. The lack of this functionality has caused me issues in the past when I have needed to query for external data. For example, say that you are using the CRM Publisher to publish the changes occurring in CRM to GP. The publisher outputs the changes in XML format so your source must be XML. The GP database through the GP Adapter will be your target. Now if you need to retrieve some additional information from CRM, perhaps because it lies outside the CRM Publisher’s limitation of one layer parent-child relationships, you are unable to do so since neither the source, target, or internal databases are your CRM database.
There are several workarounds, including cross server queries, but they are not the most convenient or efficient. This idea has been proposed on Scribe OpenMind and received 19 votes, however there has been no indication from Scribe that this functionality will be provided (https://openmind.scribesoftware.com/ideas/show/66).
I’ve created a custom function to allow this functionality since I needed it for a project that I was working on. The function is called SQLQUERYX and it accepts six parameters:
- * Source Field (optional)
- Server Name
- Database name
- SQL Username
- Password – keep in mind that the password will be stored unencrypted – I highly recommend creating a SQL user with very limited access
- SQL Statement
For example:
SQLQUERYX( S1, “dev1″, “TWO”, “scribetest”, “password”, “select CUSTNAME FROM RM00101 WHERE CUSTNMBR = ‘%s’” )
It works very similar to the SQLQUERY function that Scribe already has built-in. I have packaged everything up and created a readme for installation. There are also two other functions included in the assembly, LTRIM and RTRIM, since I saw that somebody was requesting those in another idea posted on OpenMind.
http://www.summitgroupsoftware.com/download/SGS.Database.zip
Enjoy!
3 comments
Custom Software says:
April 11, 2011 at 12:47 pm (UTC -6 )
I am just starting to use SQL and I have a feeling that this will come in handy. Thanks for the post.
Susan Walker says:
June 2, 2011 at 4:23 am (UTC -6 )
I start using this suggested software. it is really easy to use. Thanks for sharing this valuable post.
Susan Walker
custom software application development
Dhwani Pandya says:
June 10, 2011 at 12:19 pm (UTC -6 )
Thank you for posting this very valuable function. I don’t know why I am not able to finish the registration of the databasefunc.dll file all the way through. It gives me a “Failed to register component” error. Can you please help.
Thanks,
Dhwani