Archive for category SQL
This subject seems to popping up more.. and more.. and more…. now that many are switching over to to SQL2008 (and/or 2008R2). Well, fear no more since there are some very basic simple rules you MUST follow to get hooked up correctly. If you follow these rules, the chances of all things working out are probably 100%.
If your installation is a SQL2005 you need to add/install the SQL2005 native client to ALL systems that have any SalesLogix Components (ex: Server, Synch, Web Site, individual Windows desktops running the SalesLogix Windows/LAN client). the ONLY exception are (individual) SalesLogix remote clients since the installation of SQLExpress2005 automatically installs the native client.
If your installation is a SQL2008 (SQL 10.0) (NOT R2 – which is SQL 10.1) Then you need to install BOTH the SQL2005 native client AND the SQL2008 (10.0) native client on the SalesLogix Server. The individual desktops only need the 2008 native client (10.0). Any other systems that have a SalesLogix “client app” ( ex: synch, Web Server, etc…) only need the 2008 (10.0) as well.
If you have SQL2008R2, you have SQL 10.1 and have the same requirements as SQL2008 except you install the R2 version (SQL 10.1) of the native client… as well as the SQL 2005 native client on your SalesLogix “server”.
It may sound confusing but it ends up being simple… You ALWAYS install the SQL2005 native client on your SalesLogix Server in situations where your SQL server is SQL2005/2008/2008R2. You also install ONLY the appropriate SQL native client on ALL systems that have a SalesLogix “client app”. When in doubt.. install both the 2005 and the appropriate 2008/1008R2 native client. It does not hurt.
NOTE: We have also seen several situations where people get confused over the correct driver for SQL2008R2. Always install the 10.1 version in these situations.. NOT the SQL2008 10.0!
Sounds simple.. doesn’t it? Well it is and it is not.
You could go into the SalesLogix Admin application, click on the Systems icon and then the licenses tab and look. It will show you all your licenses. BUT if you have one or two of this and that you need to sort , then count, etc. to see what you have.
Here’s a SQL query that does it very quickly and cleanly:
-- --Saleslogix License types and counts -- SELECT TYPE as TypeCode, CASE TYPE WHEN 'C' THEN 'Concurrent' WHEN 'M' THEN 'Remote' WHEN 'N' THEN 'Named User' WHEN 'P' THEN 'Template' WHEN 'R' THEN 'Retired' WHEN 'V' THEN 'Web Viewer' WHEN 'W' THEN 'Admin' ELSE 'Unknown' END as LicenseType,COUNT(*) As NumberOfLicenses FROM [sysdba].[USERSECURITY] group by type having COUNT(*) > 0 Order by LicenseType
The output will look something like:
TypeCode LicenseType NumberOfLicenses W Admin 1 C Concurrent 55 N Named User 72 M Remote 19 R Retired 222 P Template 12 V Web Viewer 14
We have ALL been taught to NOT use SQL triggers in a SalesLogix database. However, this is not reality. SQL triggers can be very useful if properly designed and implemented.
For example, ExchangeLink (for Sage SalesLogix) implements and users SQL triggers. My favorite “BPM” tool – TaskCentre – has the capability of deploying/maintaining a TRUE SQL trigger to initiate a task/workflow operation.
So just how do you check a (SQL) SalesLogix database to see if you have any triggers? Well, here are a couple of methods:
Run the following SQL and it will show you the Trigger Name and the table(s) :
SELECT name as TriggerName, OBJECT_NAME(parent_obj) as TableName
Where type = ‘TR’
Order By TableName
However I think I would like to know the “status” of the trigger(s) (Enabled or Disabled) since it just might make a difference as to what I will do next. So how about this:
SELECT A1.name as TableName,
A2.name as TriggerName,
WHEN 0 Then ‘Enabled’
END as ‘Status’
FROM sysobjects A1, sysobjects A2
WHERE A1.id = A2.parent_obj
and A2.type = ‘TR’
and A2.xtype = ‘TR’
ORDER BY TableName, TriggerName
Now I not only get to see the Table Names and Trigger (names) but also the Status of the trigger(s)!