Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Monday, March 18, 2024 
 
Understanding the SalesLogix 6.2 Connection String  
Description:  The SalesLogix OLE DB Provider's connection string has changed for version 6.2. These changes are a result of the completely new OLE DB Provider built for SalesLogix 6.2. Taking advantage of all the new things the SalesLogix Provider has to offer starts with understanding the connection string.

Category:  SalesLogix OLE DB Provider
Author:  Ryan Farley
Submitted:  9/6/2004
   
Stats: 
Article has been read 97475 times

Rating: - 4.9 out of 5 by 7 users
 

fiogf49gjkf0d
Understanding the SalesLogix 6.2 Connection String

The SalesLogix OLE DB Provider's connection string has changed for version 6.2. These changes are a result of the completely new OLE DB Provider built for SalesLogix 6.2. Taking advantage of all the new things the SalesLogix Provider has to offer starts with understanding the connection string.

Note: This article applies to version 6.2 (and higher) of SalesLogix only. For an article discussing the connection string for versions 6.1 and prior, see Understanding the SalesLogix OLE DB Connection String (For versions 6.1 and prior)


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 SaleLogix Provider Connection String

Let's start by taking a look at a complete SalesLogix connection string:

Provider=SLXOLEDB.1;Data Source=MYSLXSERVER;Initial Catalog=MYSLXALIAS;User Id=Admin;Password="";Persist Security Info=True;
Extended Properties="Port=1706;Log=On"


As you can see, there are some differences compared to the older connection string format used in previous versions of SalesLogix. Let's break some of those down.


The Connection String Parts


  • Provider
    This specifies the provider name. For a SalesLogix connection, this will always be SLXOLEDB.1

  • Data Source
    This specifies the host name or IP address of the SalesLogix Server.

  • Initial Catalog
    A valid alias that has been configured in the Connection Manager on the SalesLogix Server.

  • 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.

  • Persist Security Info
    When set to 'true' it will persist the user authentication information. See ADO documentation for additional details.

  • RWPass
    This is the property that will allow you to open a "writable" connection. The RWPassword is defined for each alias in the Connection Manager. There are various scenarios for the RWPass value. Possible Scenarios for ReadWrite/ReadOnly passwords in the Connection Manager:

    • Only RW Password configured
      RWPass must be present and contain valid R/W password to update data outside of the SalesLogix clients.

    • RW and RO password configured
      RWPass must be present and contain valid R/O password to view data outside of SalesLogix clients, however data cannot be updated.


    If RWPass is present, then data can also be updated outside of the SalesLogix clients.

Extended Properties

The extended properties is a list of extra properties that can be used with the SalesLogix provider. These properties are:

  • Port
    The port that the specified SalesLogix Server is listening on. The default port is 1706. This is configured in the SlxLocalServers.xml file found on the SalesLogix Server.

  • Log
    Set to either ON or OFF. The default is ON. This enables or disables sync logging for the connection.

  • Timezone
    Values for this property can be an Index or a Name value for the specified Timezone. Specifies the time zone to use when the OLE DB provider converts date / time fields between the database and client. Date time data is stored in UTC in the database, and must be converted to local time on the client. If this property is omitted, the provider will default to the current time zone settings of the client computer.

    The preferred method is using the numeric INDEX value, however as a user could edit the registry entries or create new time zones, this property may not be present or duplicated, so the option to use the STANDARDNAME is also provided as a back up. The STANDARDNAME is actually the registry key name (See HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersions\Time_Zones) in the time zones section, and cannot be duplicated.

    If the INDEX or STANDARDNAME is not found (or in the case of INDEX, duplicated) a time zone not found error will be reported.

    To disable date / time conversion, specify NONE as the value. In a 6.2 system, this will typically mean all dates will be in GMT.

    For a list of INDEX and STANDARDNAME values, see the slx_timezonelist stored procedure.

  • 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)

  • 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.

  • TrimCharFields
    This connection string property provides additional backward compatibility for connecting to legacy code. In earlier versions of SalesLogix, the Borland Database Engine (BDE) trimmed strings coming from the database, but ADO does not. TrimCharFields allows trimming to be turning on when returning Data from the database on CHAR Data Types. The default setting is off.
Note: There is also an option for connection using the new SalesLogix 6.2 Integrated Security option. Take a look at Integrated Logins with the SalesLogix 6.2 Provider for more details.


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.

Until next time, happy coding.
-Ryan

 

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 
 - Integrated Logins with the SalesLogix 6.2 Provider - Submitted by: Ryan Farley
 - Understanding the SalesLogix OLE DB Connection String (For versions 6.1 and prior) - Submitted by: Ryan Farley

 

Comments & Discussion you must log-in to add comments. [login here] 
 
Author Article Comments and Discussion
David van der Linden
 

Re: Understanding the SalesLogix 6.2 Connection String
Posted: 11/8/2004 12:58:58 PM
fiogf49gjkf0d
Where is the slx_timezonelist stored procedure stored?

Does anyone have a good function/procedure for converting from GMT to Local time.

Most of our reports are written with sprocs and hence cant use the OLE Provider to do the timezone conversion.

David
 
Ryan Farley

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

Re: Understanding the SalesLogix 6.2 Connection String
Posted: 11/8/2004 1:13:41 PM
fiogf49gjkf0d
David,

I have a post on my SLX related blog that goes into this. However, as Todd Hardin mentions in the comments, it does not take daylight savings into consideration.

See http://saleslogixblog.com/rfarley/archive/2004/11/01/1159.aspx

-Ryan
 
David van der Linden
 

Re: Understanding the SalesLogix 6.2 Connection String
Posted: 11/8/2004 1:34:01 PM
fiogf49gjkf0d
I spotted that one. Unfortunately the SQL getutcdate() cannot be used within a function.

What I'm really looking for is the Table of timezones SLX recognises. If you look at the USERINFO table you get a short description of the timezone the user is in. I suspect SLX hasnt made this very sophisticated with a table of Daylight savings zones etc.


Anyway, Try this one we will use

ALTER FUNCTION [SYSDBA].[UTC_LocalTime] (@UTCTime DateTime, @USERID CHAR(12))
RETURNS DateTime AS


-- SELECT [SYSDBA].[UTC_LocalTime] ('10/25/2004 6:29:39 PM','ADMIN')

BEGIN
DECLARE @Year Int
SELECT @Year = DatePart(Year,@UTCTime)
Declare @TimeZone varchar(50)
Declare @LocalTime datetime
DECLARE @BegApr varchar(30)
DECLARE @EndOct varchar(30)
SET @BegApr = Convert(varchar,@Year)+'-04-01 00:00:00'
SET @EndOct = CONVERT (varchar,@Year)+'-10-31 00:00:00'
--checks the first day of month in relat
-- ion to the first Sunday of the month. Se
-- ts @AprDay to the appropriate date. For
-- Daylight savings.
DECLARE @AprDay Int
SET @AprDay =
CASE Datepart(Weekday,@BegApr)
WHEN 1 THEN 01
WHEN 2 THEN 07
WHEN 3 THEN 06
WHEN 4 THEN 05
WHEN 5 THEN 04
WHEN 6 THEN 03
WHEN 7 THEN 02
END

DECLARE @DSTStart DateTime
SET @DSTStart = Convert(DateTime,Convert(varchar,@Year)+'-04-'+Convert(Varchar,@AprDay)+' 07:00:00')
--checks the last day of month in relati
-- on to the last Sunday of the month. Sets
-- @OctDay to the appropriate date. For Da
-- ylight savings.
DECLARE @OctDay Int
SET @OctDay =
CASE Datepart(Weekday,@EndOct)
WHEN 1 THEN 31
WHEN 2 THEN 30
WHEN 3 THEN 29
WHEN 4 THEN 28
WHEN 5 THEN 27
WHEN 6 THEN 26
WHEN 7 THEN 25
END

-- default to Central if the user doesnt have a timezone in the database.
select @timezone = isnull((select timezone from userinfo where userid=@userid),'Central Standard Time')

DECLARE @DSTEnd DateTime

SET @DSTEnd = Convert(DateTime,Convert(varchar,@Year)+'-10-'+Convert(Varchar,@OctDay)+' 06:00:00')
DECLARE @OffSet DateTime

-- NON-Daylight savings adjustments.
SELECT @LocalTime =
(CASE @timezone
WHEN 'Eastern Standard Time' THEN DATEADD(hh, - 5, @UTCTime)
WHEN 'Central Standard Time' THEN DATEADD(hh, - 6, @UTCTime)
WHEN 'Mountain Standard Time' THEN DATEADD(hh, - 7, @UTCTime)
WHEN 'Pacific Standard Time' THEN DATEADD(hh, - 8, @UTCTime)
ELSE DATEADD(hh, - 6, @UTCTime)
END)

-- Daylight savings adjustments.
IF @UTCTime >= @DSTStart AND @UTCTime <= @DSTEnd
Begin
SELECT @LocalTime =
CASE @timezone
WHEN 'Eastern Standard Time' THEN DATEADD(hh, - 4, @UTCTime)
WHEN 'Central Standard Time' THEN DATEADD(hh, - 5, @UTCTime)
WHEN 'Mountain Standard Time' THEN DATEADD(hh, - 6, @UTCTime)
WHEN 'Pacific Standard Time' THEN DATEADD(hh, - 7, @UTCTime)
ELSE DATEADD(hh, - 5, @UTCTime)
END
End

RETURN @LocalTime
END



 
JM
 

Re: Understanding the SalesLogix 6.2 Connection String
Posted: 4/11/2005 7:49:28 PM
fiogf49gjkf0d
does it support command paramaters from within .net? i can only find examples that use adodb via com interop in .net. i would like to do it natively throuhg the oledbcommand.parameters.add but this only generates an empty exception when trying to run this. if this is possible, anyone have an example? or must i only go through adodb or not use sql params and have it all inline? thanks.
 
Snow Monkey
 

Re: Understanding the SalesLogix 6.2 Connection String
Posted: 2/16/2007 12:48:12 PM
fiogf49gjkf0d
Ryan , What about the connect timeout property in our connection string.By Default it is 0 meaning there is no timeout.Is there any information availble for that property in 6.2.4 provider version?

My customer portal users complain that insert ticket process is a "race against time" thing...dont understand why/how..when i test things are OK till ~20 min
 
RJ Samp

slxdeveloper.com Forum Top 10 Poster!

Re: Understanding the SalesLogix 6.2 Connection String
Posted: 8/7/2007 10:07:31 PM
fiogf49gjkf0d
Not working in C# .NEt

static string SLXconnstring = "Provider=SLXOLEDB.1;Password="";Persist Security Info=True;User ID=Admin;Initial Catalog=OCC;Data Source=RJSAMP-D610;Extended Properties="PORT=1706;LOG=ON;CASEINSENSITIVEFIND=ON;AUTOINCBATCHSIZE=1;SVRCERT=;" + Char(34);


Help!

RJ Samp
 
Ryan Farley

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

Re: Understanding the SalesLogix 6.2 Connection String
Posted: 8/7/2007 11:09:13 PM
fiogf49gjkf0d
RJ,

In C#, you can simply escape the quotes. For example, if I want a string variable to have the contents This is a "quoted" string I need to set it as follows:

string var = "This is a \"quoted\" string";

So, for a connection string you'd escape the quotes the same way:

static string SLXconnstring = "Provider=SLXOLEDB.1;Password=\"\";Persist Security Info=True;User ID=Admin;Initial Catalog=OCC;Data Source=RJSAMP-D610;Extended Properties=\"PORT=1706;LOG=ON;CASEINSENSITIVEFIND=ON;AUTOINCBATCHSIZE=1;SVRCERT=;\"";

Make sense?
 
Kathy Lass
 

Re: Understanding the SalesLogix 6.2 Connection String
Posted: 10/31/2007 4:03:53 PM
fiogf49gjkf0d
Hi all:

Does anyone know if RWPass is still required in 7.2? I'm trying to test this part of my external application, and it seems I can still update data even without the "RWPass" part of the connection string, even though the Read/Write password is defined.

Any comments on current experience would be appreciated! Does anyone know what the error should be in the above case?

thanks,
Kathy Lass
Expandable Software, Inc.
 
KevLar
 

Re: Understanding the SalesLogix 6.2 Connection String
Posted: 3/12/2008 12:15:24 AM
fiogf49gjkf0d
I have an ASP web page accessing a SQL server using the connection string:

DIM objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Driver={SQL Server};Server=SERVER;Database=TESTDB;Trusted_Connection=yes;"
objConn.Open

How do I modifiy the the connection string to work with the SLX provider? I tried the code below and I get an "Expected end of statement" error.

DIM conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=SLXOLEDB.1;Data Source=SERVER;Initial Catalog=TESTSLX;User Id=admin;Password="password";Persist Security Info=True;Extended Properties="Port=1706;Log=On";"
conn.Open

If I take out the extended properties portion of the string I get an error like below:

ADODB.Connection error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/includes/SLXconnection.asp, line 5

Thanks in advance for any help on this.
 
Ryan Farley

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

Re: Understanding the SalesLogix 6.2 Connection String
Posted: 3/12/2008 12:28:45 AM
fiogf49gjkf0d
KevLar,

You can't have quotes in the middle of a string. If you do (as you have in the code you posted) it ends the string. So the error you're getting is accurate.

You can either omit the quotes and all will still work fine, or escape them. Try this:

DIM conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=SLXOLEDB.1;Data Source=SERVER;Initial Catalog=TESTSLX;User Id=admin;Password=password;Persist Security Info=True;Extended Properties=Port=1706;Log=On;"
conn.Open

-Ryan
 
KevLar
 

Re: Understanding the SalesLogix 6.2 Connection String
Posted: 3/12/2008 12:55:04 AM
fiogf49gjkf0d
That was quick thanks Ryan.

No more errors, but the page just loads and loads. Nothing happens, it doesn't even time out. I created a test page that just opens and closes the connection just to be sure.

I'm running SBS 2003, SQL 2000, and SLX on the same box (which is also serving up the ASP page in question). Are there certain permissions I need to set to allow access to the SLX provider? Is there somewhere else I should be looking for the problem?
 
KevLar
 

Re: Understanding the SalesLogix 6.2 Connection String
Posted: 3/12/2008 3:16:39 AM
fiogf49gjkf0d
This is the problem I think:
http://www.slxdeveloper.com/forum.aspx?forumid=3000&postid=6487

In the process of going thru these steps it started working. Remember to Iisreset.
 
 

       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): 3/18/2024 10:23:23 PM