Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Thursday, December 12, 2024 
 
Understanding the SalesLogix OLE DB Connection String (For versions 6.1 and prior)  
Description:  The new OLE DB Provider allows you to access SalesLogix data via ADO or ADO.NET. However, before you can use the provider, it is important to understand how to connect to it. This article will examine the various parts of the SalesLogix OLE DB Provider connection string. Applies to version 6.1 and prior.

Category:  SalesLogix OLE DB Provider
Author:  Ryan Farley
Submitted:  11/7/2002
   
Stats: 
Article has been read 54063 times

Rating: - 5.0 out of 5 by 8 users
 

fiogf49gjkf0d
Understanding the SalesLogix OLE DB Connection String

The new OLE DB Provider allows you to access SalesLogix data via ADO or ADO.NET. However, before you can use the provider, it is important to understand how to connect to it. This article will examine the various parts of the SalesLogix OLE DB Provider connection string.

Note: This article applies to versions 6.1 and prior of SalesLogix 6 only. For an article discussing the connection string for 6.2, see Understanding the SalesLogix 6.2 Connection String

Background

A connection string is something used when connecting to an OLE DB provider. This is something that you will need to connect to a database via ADO or ADO.NET. This article will explain the various parts of the connection string, not how to use it. Other articles are provided on this site to explain using the SalesLogix connection string with ADO & ADO.NET. Note that this article focuses on the parts of the connection string used when making a connection. There are other uses of some of these connection string parameters that will be explored in a future article (watch for Related Articles at the end of this article).

The Connection String Parts

  • Provider
    The provider indicates the OLE DB provider, or driver, to use. For SalesLogix, this will always be 'SLXNetwork.1'
  • Data Source
    The Data Source is an alias defined in the Connection Manager. This alias points to a SalesLogix server and database - but is defined on the server (that will be defined in the extended properties below).
  • User ID
    This is a valid SalesLogix user name (not a database level user, like sysdba, but a SalesLogix level user, like Admin or Lee). The user provided here will dictate the SalesLogix security for the connection. For example. If you use the user 'Lee' here, then the data returned from SELECT statements opened with this connection will only return rows that Lee has access to.
  • Password
    The password that corresponds to the user name given for the User ID.
Extended Properties

The extended properties is a list of extra properties that are required by the SalesLogix provider. These properties are:
  • SLX Server
    This is the name of the server where the Data Source (defined above) exists. Note: previously, this article did not cover this parameter. If this parameter is omitted, then the 'local' server is assumed. However, be aware, in some cases, such as on a remote or a computer that is not connected to a network, the connection will fail without this parameter explicitly included in the string. Therefore, this parameter should always be present to indicate the SLX Server when making a connection, even if the local server is used. Thanks to Stuart Carnie for this correction (introduced in v6 beta3) and to Stephen Redmond for tracking down the problem. (There are some exceptions to this which will be discussed in a future article).
  • Address
    This is the location of the provider (Not to be confused with the SLX Server). This should always be 'localhost' (or some equivalent to indicate the local machine such as 127.0.0.1, but 'localhost' is recommended).
  • Type
    For the current build of SalesLogix (initial gold release build 6.0.0.3205) this is always ODBC. Don't be confused by this. The SalesLogix Provider does not use ODBC. This basically tells the provider which SQL parser to use for parsing SQL statements.
  • Port
    The port that the SalesLogix Server is running on. Although this can be changed, the default is 1706.

Putting it all together

Now that we have seen all of the parts of the connection string, let's look at how to put it together. Let's first put standard properties together. The order does not matter. The list or properties must be delimited with semi-colons (;). Let's use the SalesLogix Admin user with no password and connect to an alias named SALESLOGIX_EVAL. Remember, this is the alias defined in the Connection Manager on the server (which we'll add later).

Provider=SLXNetwork.1;User ID=Admin;Password="";Data Source=SALESLOGIX_EVAL;

Now, let's add the extended properties. The extended properties is a quoted string. We'll use a server named SLGXSERVER (This is where the alias used as the Data Source defined above exists).

Provider=SLXNetwork.1;User ID=Admin;Password="";Data Source=SALESLOGIX_EVAL;Extended Properties="SLX Server=SLGXSERVER;
ADDRESS=localhost;Type=ODBC;PORT=1706"

Now we have a complete connection string. One thing to note is that this connection string will open a read-only connection. In order to have a "writable" connection we must define the RWPassword which will be discussed in the next section.

Additional (Optional) Extended Properties

There are several extended properties that can be used in the connection string. These are simply added to the extended properties part of the connection string.

  • RWPass
    This is the most important of the extended properties. This is the property that will allow you to open a "writable" connection. The RWPassword is defined for each alias in the Connection Manager. Look for further explanation of the RWPassword in a future slxdeveloper.com article.
  • IncludeCalcFields
    This extended property is set to either ON or OFF. The default setting is OFF. This tells the provider to include calculated fields in SELECT * queries. You can still manually select individual calculated fields (i.e. SELECT FULLNAME FROM CONTACT)
  • Log
    Set to either ON or OFF. The default is ON. This enables or disables sync logging for the connection.
  • Impersonate
    Set this extended property to a valid SalesLogix username (such as lee or bgates). Adding this to the connection string will enable security for the user you are impersonating for security. For example, if I supply 'lee' as the value for Impersonate, then all queries will return only rows that lee has access to, even though you are logged in as admin. Why is this useful? Because you can act as the user 'lee' in the database without logging in as lee - so you don't need to know lee's password.
  • AdoNetSupport
    This new parameter was added for ADO.NET support. It is not required for ADO.NET, but will fix the error "cannot convert VARCHAR to INT is reported" when using an updateable DataSet. Valid values are ON & OFF.

Wrapping it up

Understanding the parts of the SalesLogix connection string will provide you with the knowledge to build great new applications to take full advantage of the SalesLogix OLE DB provider. Stay tuned for articles showing how to use the connection string via ADO and ADO.NET from various languages such as C#, VB, & VB.NET.

Until next time, happy coding.
-Ryan


Updates

  • 12/2/2002 - Updated to reflect correct information about SLX Server parameter introduced in v6 beta3. Thanks to Stuart Carnie for this correction (introduced in v6 beta3) and to Stephen Redmond for tracking down the problem.
  • 1/22/2004 - Updated to include the ADONETSUPPORT param for ADO.NET support.

 

About the Author

  Ryan Farley
(SalesLogix Business Partner)
Customer FX Corporation

fiogf49gjkf0d

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix since 2001 and believes in sharing with the community. He loves C#, Javascript, Python, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

View Ryan's SalesLogix Mobile Seveloper Series
View Ryan's SalesLogix SData Developer Series
View Ryan's Git for the SalesLogix Developer series



View online profile for Ryan Farley
 

[ back to top] [ send to a friend]  

Rate This Article you must log-in to rate articles. [login here] 
 
Please log in to rate article.
 

Related Articles 
 - Understanding the SalesLogix 6.2 Connection String - Submitted by: Ryan Farley
 - SalesLogix 6.1 Procedures - Submitted by: Ryan Farley
 - DataLink Designer - Submitted by: Ryan Farley

 

Comments & Discussion you must log-in to add comments. [login here] 
 
Author Article Comments and Discussion
Carlos H. Treviño
 

Data Link files
Posted: 2/4/2003 9:12:03 AM
fiogf49gjkf0d
In case someone else is wondering how to build these expressions using the OLE DB connection builder and put them into a file (like you do with ODBC) it's very easy. Some times you will have the option to create a new "Data Link file" directly in the windows explorer, but if you don't - don't panic - just create a text file and change the extension to .udl then you can access the OLE DB builder just double-clicking the file. This is convenient for some apps, for example Crystal Reports 9 where for some reason you can only enter database, name and password in the first screen ... and of course you're missing the server name which has to be entered in the Extended Properties field. A Data Link file makes is much easier and you can use it every time you create a report.
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: Data Link files...
Posted: 2/4/2003 6:10:36 PM
fiogf49gjkf0d
True, you can use UDL files to create connection strings, however, it is still important to understand the various parts of any connection string so you know exactly what you're creating.

To assist in creating connection strings, I uploaded a utility which makes it easy to create connection strings (sits in tray for easy access). Take a look here:
http://www.slxdeveloper.com/devhome/page.aspx?id=35&articleid=36
 
Eric Gronholz
 

Re: Understanding the SalesLogix OLE DB Connection String
Posted: 3/16/2004 11:19:47 AM
fiogf49gjkf0d
Is there a way using the Connection String to determine if the OLE DB connection is against an Oracle database vs. a SQL database? I've written a VB COM object that connects to the SLX database and inserts accounts and contacts. However, the insert statements are slightly different between the two databases - primarily the handling of date fields. Since I have network users hitting Oracle and remote users hitting the MSDE, I need a way to know which database they are using so I can apply the proper SQL statements.
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: Understanding the SalesLogix OLE DB Connection String
Posted: 3/16/2004 6:44:32 PM
fiogf49gjkf0d
Eric,

Yes, use the slx_getNativeConnInfo procedure to return the underlying native connection string used by the provider. This will tell you based on the provider name in that string if it is SQL or Oracle.

-Ryan
 
Eric Gronholz
 

Re: Understanding the SalesLogix OLE DB Connection String
Posted: 3/17/2004 8:54:38 AM
fiogf49gjkf0d
Thanks Ryan. I also learned that the Application.BasicFunctions.SystemInfoFor("DatabaseType") will give you similar information. I pass the value from this function to my COM object and I know which database I'm using.

A tech support person from SalesLogix also informed me that when inserting date values into SalesLogix, use the following format so you won't need to write separate sql statements for Oracle and SQL Server. Apparently all dates sent to the SLX OLEDB provider need to be in this ISO standard. My example uses VB code to format the current date:

Format(Now(), "YYYYMMDDHHMMSS")

I tried it and it works. Hopefully others will find it helpful too.

Eric
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: Understanding the SalesLogix OLE DB Connection String
Posted: 3/17/2004 9:51:24 AM
fiogf49gjkf0d
Eric,

Yes all dates need to be ISO formatted dates. While in VB you can use the Format function, however, you'll need to modify it a bit from what you have. There needs to be a space between the date and time portions, and colons separating the time parts. This should give you a proper ISO formatted date for the SLX provider:

Format(Now(), "YYYYMMDD HH:MM:SS")

Keep in mind however that you only need to format the date this way when using the date as a string in a SQL statement, not when using parameterized queries or updateable recordsets. You can take a look here for more info: http://saleslogixblog.com/rfarley/archive/2004/03/02/392.aspx
 
Larry Hand
 

Re: Understanding the SalesLogix OLE DB Connection String
Posted: 3/23/2004 7:25:12 PM
fiogf49gjkf0d
The date strings are not quite right. They should be: Format(Now(), "YYYYMMDD HH:NN:SS")
If you use MM for minutes you WILL be surprised!!! From experience....MM is for month and NN is for day.
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: Understanding the SalesLogix OLE DB Connection String
Posted: 3/23/2004 9:22:09 PM
fiogf49gjkf0d
Doh! Thanks Larry, good catch. I typically don't use VB, I am a semi-colon curly-brace kind of guy so I really should have checked before. Thanks.
 
Alexander Ruf
 

Re: Understanding the SalesLogix OLE DB Connection String
Posted: 5/26/2004 10:07:34 AM
fiogf49gjkf0d
If i try do a INERT-Statement over the SLX OLEDB Provider i get the following error:
"Failed to parse SQL.INSERT INTO sysdba.ACCOUNTPRODUCTGROUPS (ACCOUNTID,PRODUCTGROUP) VALUES ('AA2EK0013017','Audio');"
What am i doing wrong?
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: Understanding the SalesLogix OLE DB Connection String
Posted: 5/26/2004 11:01:41 AM
fiogf49gjkf0d
Alexander,

You have this:
INSERT INTO sysdba.ACCOUNTPRODUCTGROUPS (ACCOUNTID,PRODUCTGROUP) VALUES ('AA2EK0013017','Audio');

1) No need for the sysdba part, get rid of that.
2) Drop the semi-colon from the end.

ie:
INSERT INTO ACCOUNTPRODUCTGROUPS (ACCOUNTID,PRODUCTGROUP) VALUES ('AA2EK0013017','Audio')

-Ryan
 
Rich
 

Re: Understanding the SalesLogix OLE DB Connection String
Posted: 6/25/2004 2:03:44 PM
fiogf49gjkf0d
Can you call stored procedures through the 6.1 provider? I get "Failed to parse SQL" errors everytime I try through OleDb namespace in in VS.NET.
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: Understanding the SalesLogix OLE DB Connection String
Posted: 6/25/2004 2:49:10 PM
fiogf49gjkf0d
Rich,

The 6.1 provider does not allow for stored procedures to be called. However, there is some hope coming in 6.2.

-Ryan
 
Eric Gronholz
 

Re: Understanding the SalesLogix OLE DB Connection String
Posted: 7/29/2004 3:59:27 PM
fiogf49gjkf0d
I'm having some trouble when executing an insert statement against a remote database. I've written code that inserts Account and Contact data using the OLE DB Provider via a VB COM object. All is well when I run it against my server database, but I'm having issues when I attempt to run the same code against the remote database. I receive the error "Cannot start more transactions on this session" when the insert statement executes. Has anyone else run into this error?

Microsoft has a published KB article stating the OLE DB Provider for SQL Server does not allow nested transactions, but as far as I can tell, I'm not using nested transactions.
 
S Mummert
 

Re: Understanding the SalesLogix OLE DB Connection String (For versions 6.1 and prior)
Posted: 5/10/2005 4:00:08 PM
fiogf49gjkf0d
Has anyone attempted to use the OLEDB provider as a linked server in another SQL Server database. I am attempting to do this and receive a SLX...IDBInit error from the provider.

Help would be great!
 
 

       Visit the slxdeveloper.com Community Forums!
Not finding the information you need here? Try the forums! Get help from others in the community, share your expertise, get what you need from the slxdeveloper.com community. Go to the forums...
 



 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2024 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): 12/12/2024 1:07:52 PM