The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Execute SQL in the bundle is not executing?
Posted: 27 Jan 09 4:05 PM
|
Hi, I have a project in Architecture, inside there is an action Execute SQL, with the following (it moves some picklist itmes to a new picklist):
------------------------------------------------------------------------
-- declare @itemid varchar(12)
-- declare @newItemid varchar(12)
-- set @itemid = (select itemid from sysdba.picklist where text = 'C_MailingLists-ED')
-- set @newItemid = (select itemid from sysdba.picklist where text = 'C_MailingLists_Ops-ED')
-- update sysdba.picklist set picklistid=@newItemid where picklistid=@itemid and text like 'ops%'
------------------------------------------------------------------------
I am hoping that when I install this bundle on a different server this sql statment would get executed, so I tried to install this bundle onto my dev server, it installed fine but when I checked the database values, the picklist items didn't get moved.
Can someone give me some suggestions?
Thanks!
WL |
|
|
|
Re: Execute SQL in the bundle is not executing?
Posted: 28 Jan 09 5:46 AM
|
SalesLogix will not pass your parameters through, nor will it let you run multi-line SQL.
Also, I am wondering about your picklist logic here.
The picklist id for the list name = select itemid from picklist where text = 'yourpicklistname' and picklistid = 'PICKLISTLIST'
Then all of the items will have that ITEMID in the field picklistid.
If you need to do a lot of picklist related work ... look into picklist manager
http://www.radishtools.com/picklist.html
hth
ws |
|
|
|
Re: Execute SQL in the bundle is not executing?
Posted: 28 Jan 09 7:16 AM
|
Good points Walter.
It continues to amaze me how folks approach SalesLogix. As you have pointed out, you can't "program" SalesLogix using a TSQL approach. All SQL must be single SQL statements.. and even then, some forms of the syntax are not supported (like Select into.. from.. or Insert Into.. From..).
As we know, the parametric approach does work.. but ONLY when it's done via VBScripting (or .NET extensions).
We know this is SalesLogix 101.. and to give the Sage team some points here (as well as solid advice to new comers to SalesLogix) - take a trip to SageU.com and check out the on-line and "blended" training offered by Sage on SalesLogix. You don't need to go to AZ to get training.. you can get it over the net.
--
RJL |
|
|
| |
|
Re: Execute SQL in the bundle is not executing?
Posted: 28 Jan 09 8:18 AM
|
thanks for clarifying this guys, it just seem logical that because i'm creating some new fields via actions, i thought i could do some simple, one time thing data processing in sql, without going to vbscript or forms. if Execute SQL is so limited, why is it even there.
|
|
|
|
Re: Execute SQL in the bundle is not executing?
Posted: 29 Jan 09 4:05 AM
|
| It's there mainly for upgrades and adding new functionality (where you need to change data via simple update statements) - but was never intended as a data processing engine. |
|
|
|
Re: Execute SQL in the bundle is not executing?
Posted: 29 Jan 09 8:25 AM
|
I'll chime in on this one.
I've been to the LAN developer training, but that didn't tell me what I can or can't do via the project. Not to this extent.
Is there somewhere that this is documented? I've got my training manual in front of me and a help file and I can't find anything that clearly states to what extent I can execute SQL in a project action.
I really liked the training, but I still see a lack of cohesiveness in how information is disseminated to new developers.
Sorry for not being explicitly on topic, but I can't help but see this as related. |
|
|
|
Re: Execute SQL in the bundle is not executing?
Posted: 29 Jan 09 1:58 PM
|
The available SLX deveoper training walks you through some typical projects, but not everything is covered, just as not everything is covered in the documentation.
Becoming a master SLX developer/implementor only starts with the documentation and training. It involves a lot of studying existing plugins & code that does things similar to what you want to do, a lot of searching this forum and ITToolBox, and a lot of trial & error.
|
|
|
|
Re: Execute SQL in the bundle is not executing?
Posted: 30 Jan 09 7:21 AM
|
| I agree that everything is not intuitive and in training manuals. Many of these types of things are learned by the "gotcha" method. I have been involved in SalesLogix for almost 10 years and it happens to me all the time. For instance, during our 6.23 to 7.25 upgrade I thought it would be dandy to replace some Scribe jobs I created with the SQL commands in the Bundle. I created the SQL in Enterprise manager and Scribe did the work perfectly. However, the same SQL did not run properly in the Bundle. A rule of thumb might be to check your SQL in the Administrator Execute SQL wizard. If it doesn't work there it won't work in the bundle. |
|
|
|