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!

Architect Forums - SalesLogix Scripting & Customization
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.

Forums RSS Feed

Back to Forum List | Back to SalesLogix Scripting & Customization | New Thread

View: Search:

Author

Thread: slx db provider - trouble with statement

Stefan Held
Posts: 18

slx db provider - trouble with statement Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 07 Aug 08 2:04 AM

Hi there,

In one of my scripts I have the following sql statement:

---
strsql = "select * from account where createuser = '" & trim (userid) & "' and datepart (yyyy, createdate) = " _
& cbjahr.Text & " and datepart (wk, createdate) = " & cint (cbwoche.Text) & " order by accountid "
set rs = conn.execute (strsql)
---

(cbjahr in a combo box selecting a 4 digit year, cbwoche selects a 2 digit week, userid is a selected userid from a list.)

Now, this is what happens:

1st version: conn is a slx db provider connection (from application.GetNewConnection)

If I run this script as ADMIN, it works. If I run it as a different user, it returns a "Failed to parse SQL"

2nd version: conn is an ADODB Connection connecting through sqlncli directly to the database (as user sysdba).

Now this script runs fine for both, ADMIN and normal users.

So this is my question: Is the SLX db Provider messing up sql statements for non-admin users?! Does anyone know how to fix this?
(Running on SLX7.0 HF2)

Thanks for any ideas....
Stefan

[Reply][Quote]

Steve Robertson
Posts: 146

Re: slx db provider - trouble with statement Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 07 Aug 08 3:25 AM

Hi Stefan,

Run SLXProfiler and you will see the SQL statements being run through SalesLogix. The SalesLogix OLEDB provider adds the security part for users to restrict the user's access to only records they can see.

Your second version is by passing SalesLogix security.

Cheers,

Steve.

[Reply][Quote]

Bob (RJ)Ledger
Posts: 1103

Re: slx db provider - trouble with statement Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 08 Aug 08 12:09 PM

If the UserId you are passing in does not pass the Provider Security rules I'd expect an error.

Like Steve says the provider (hard-coded) adds security to your "Where Clause" to restrict what records you can see (SeccodeID).

The Admin user is hard coded to bypass the check.

[Reply][Quote]

Harendra Sinha
Posts: 66

Re: slx db provider - trouble with statement Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 08 Aug 08 3:42 PM

stefan,
I had same problem.Its a problrm with SLX internal connection string . SLX creates connection string dynamically with logged-in user credentials.
So define the connection string with 'windows authentication' to pypass the user credential(sql server)
e.g
Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

-Harendra

[Reply][Quote]

Bob (RJ)Ledger
Posts: 1103

Re: slx db provider - trouble with statement Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 09 Aug 08 8:36 AM

Quote:

Originally posted by Harendra Sinha

stefan,
I had same problem.Its a problrm with SLX internal connection string . SLX creates connection string dynamically with logged-in user credentials.
So define the connection string with 'windows authentication' to pypass the user credential(sql server)
e.g
Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

-Harendra

That string is a SalesLogix Provider bypass.. and in doing so you will allow access to data that the user may not have rights to.. and GMT dattimes will be off.

[Reply][Quote]

Stefan Held
Posts: 18

Re: slx db provider - trouble with statement Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 11 Aug 08 1:25 AM

Ok, thanks a lot, the profiler did it:

"Ambiguous column name":
SLX adds a join to the secrights table and for whatever reason is unable to add the table name as a prefix to the column names within the date function.

[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 \|

page cache (param): 8/24/2025 6:24:52 PM