Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Thursday, July 10, 2025 
 
slxdeveloper.com Community Forums  
   
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!
 Administration Forums - General Administration
Forum to discuss general administration topics for SalesLogix (including LAN & remote topics). View the code of conduct for posting guidelines.
Forums RSS Feed


 Back to Forum List | Back to General Administration | New ThreadView:  Search:  
 Author  Thread: SQL Question
Steve Knowles
Posts: 657
Top 10 forum poster: 657 posts
 
SQL QuestionYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 03 Dec 07 9:26 AM
Is it possible to run the following query in the sql tool in the administrator? It fails as written in the SLX query tool, but works when run in MS SQL.

UPDATE sysdba.c_OPPORTUNITY_product SET SITEDESIGNATION = AC.SITEDESIGNATION
FROM sysdba.C_opportunity_product inner join sysdba.c_opportunity_product as AC on oppproductid = AC.oppproductid
WHERE oppproductid in('Q6UJ9A00LS8Q',blah blah)

Thanks
[Reply][Quote]
Lloy Sanders
Posts: 69
 
Re: SQL QuestionYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 03 Dec 07 1:03 PM
Try removing the keyword AS i.e. "sysdba.c_opportunity_product AC" I think it should work, although I have not tried it.
[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: SQL QuestionYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 03 Dec 07 5:19 PM
The SLX OLEDB provider does not appear to support UPDATE ... FROM queries. Frustrating, I know.

Also, your query is doing a self join on C_OPPORTUNITY_PRODUCT ... on its own ID field - this achieves nothing

Try changing the syntax along the following lines for updating one table from another:

UPDATE C_OPPORTUNITY_PRODUCT
SET SITEDESIGNATION = (SELECT AC.SITEDESIGNATION FROM OTHER_TABLE AC where AC.ID = C_OPPORTUNITY_PRODUCT.OPPPRODUCTID)

By the way, no need to use the 'sysdba.' prefix when running queries in the Administrator client.

PP
[Reply][Quote]
Steve Knowles
Posts: 657
Top 10 forum poster: 657 posts
 
Re: SQL QuestionYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 03 Dec 07 5:41 PM
Phil, I am actually trying to update some records with themselves in hopes of creating some TEF's and getting some missing data in existing records to a remote office..
[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: SQL QuestionYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 03 Dec 07 8:34 PM
Aha - that old problem.

I'm afraid that this idea won't work - all that will sync out to your remotes is the UPDATE statement, which will then execute on their versions of the db - the data itself will not sync out.
[Reply][Quote]
Steve Knowles
Posts: 657
Top 10 forum poster: 657 posts
 
Re: SQL QuestionYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 04 Dec 07 6:43 AM
Hmmm.. the records exist on the remote already - just need to update some fields that didn't sync.. Anyway to reconsile this?
[Reply][Quote]
Carla Tillman
Posts: 290
 
Re: SQL QuestionYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 04 Dec 07 9:27 AM
Get rid of Remotes?

[Reply][Quote]
Steve Knowles
Posts: 657
Top 10 forum poster: 657 posts
 
Re: SQL QuestionYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 04 Dec 07 9:35 AM
I like the world you live in Carla - how do I sign up? How come nobody has developed a utlity to resync records from the host db? That would be quite useful.. There is always the bundle the record and reapply trick..
[Reply][Quote]
Carla Tillman
Posts: 290
 
Re: SQL QuestionYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 04 Dec 07 9:42 AM
By lottery. People used to queue but the line became too long and crowd control was a complete mess! (big grin)

OK, so the base rcord exists on the remote but the data you want to update said record, exists only on the Host? Is this correct? (Hence the Update won't work because the data does not exist on the remote)

c
[Reply][Quote]
Steve Knowles
Posts: 657
Top 10 forum poster: 657 posts
 
Re: SQL QuestionYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 04 Dec 07 9:44 AM
That is correct
[Reply][Quote]
Carla Tillman
Posts: 290
 
Re: SQL QuestionYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 04 Dec 07 9:53 AM

VIABLE OPTIONS?
1.) Is synching that table to the remote a possibility?
2.) Are manual Updates Possible? Ie.

UPDATE C_OPPORTUNITY_PRODUCT
SET SITEDESIGNATION = "MySiteA"
WHERE C_OPPORTUNITY_PRODUCT in ('MY FIRST 100 OPP_idS')

If #2 is viable, I will dig up a SQL script that will help you actually generate the Update statements so you don't have to type them individually. You can contact me offline if this is a possibility.

How many SITEDESIGNATIONs are we talking about? How many affected records? How many affected remotes?

[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: SQL QuestionYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 04 Dec 07 4:19 PM
Hey, that sounds like an interesting script - if you end up finding it (or something similar) I would like to see it. Please

PP
[Reply][Quote]
Bob (RJ)Ledger
Posts: 1103
Top 10 forum poster: 1103 posts
 
Re: SQL QuestionYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 05 Dec 07 7:14 AM
Quote:
Originally posted by Steve Knowles

.... How come nobody has developed a utlity to resync records from the host db? That would be quite useful.. ..


Actually.. I've been re-synching data to/from remotes/main db for some time.. was doing it "back in 5.2.x".
(being VERY carefull to not cross that "commercial line".. but will "bump it"
We have the technology to do almost anything one wants to do in re-synching. The concept is fairly simple and has been discussed several times in news groups(s). However, the actual "tool" is something that we license out.. not sell or provide for free.
--
rjl
[Reply][Quote]
Steve Knowles
Posts: 657
Top 10 forum poster: 657 posts
 
Re: SQL QuestionYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 05 Dec 07 8:08 AM
I ended up bundling the records and reinstalling. It works but it ain't pretty.

RJ, Are these discussions you mention found on SLXDeveloper.com? It seems that SLX would take notice and ship a tool that would improve one of their biggest weaknesses (in my opinion). Not that you shouldn't get paid for your initiatives, but anyone with remotes has had these type of problems. If you know where I can see these posts I would be very interested. Good to know you have the 'technology' should a sync crisis of epic proportions ever arise.
[Reply][Quote]
Bob (RJ)Ledger
Posts: 1103
Top 10 forum poster: 1103 posts
 
Re: SQL QuestionYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 05 Dec 07 3:34 PM
It works.. but it's not the best way... You really want to "traverse" the hierarchy.. do it based on certain filters, etc..

Don't look to Sage SalesLogix to implement this.. I've talked w/them about it (various times over the past 2/3 years).. Just don't see them doing it. "Tools" have not been their "thing".. never... Even talked about "selling" the tool to them.. no go.

So I'll "license" it's use on a "site by site" (system by system) basis... but not ever sell it outright to anyone or ever make it public domain. There's just too much time and effort invested in it.. and 'll probably not ever re-coup my investment. However, for those who are my clients.. the technology has proved to be invaluable. It's even got me a client or two over the years. So from that point of view.. it certainly works for me

Look at the Partner NG and do asearch.. there's random postings there.
--
rjl
[Reply][Quote]
Walter Shpuntoff
Posts: 167
 
Re: SQL QuestionYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 06 Dec 07 8:24 AM
Once upon a time - i.e. in legacy code, working for a different bp, etc...

We had just done a big conversion, and the guy on site forgot to check the history grooming features on remotes.

I created a routine to "republish" history that could be run on demand
Effectively, it reinserts existing records on the host.
For each record, you do the following

1. capture record information.
2. delete the record
3. insert the record using the captured info

As a quick & dirty, you could probably bang it out in a few hours.
It worked very well.

Hmmm.... maybe it SHOULD become a 3rd party utility

ws
[Reply][Quote]
 Page 1 of 1 
  You can subscribe to receive a daily forum digest in your user profile. View the site code of conduct for posting guidelines.

   Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
 

 
 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2025 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
   
 
page cache (param): 7/10/2025 6:12:14 AM