There have been a couple occurrences where a client has required SQL views be used within their SalesLogix system to retrieve complex data queries. Normally this is fairly straight forward for a network only SalesLogix implementation, however in some cases these clients have also had remote databases that also need these views. How can these be distributed?
If you have worked with SalesLogix and creating remote databases, you will know that SalesLogix does not create SQL views as part of the standard database creation. If you have implemented SQL views in your database that the remotes must also use, you are tasked with creating these views.
Now there are several ways of handling this, you could simply create a script that you run on the SalesLogix remote system when attaching the database, however you need to remember to do this each time a new SalesLogix remote database is created. Also running the scripts is often problematic, in that you don’t have access to the system to run the scripts yourself, or because of the type of SQL on the remote computer you do not have access to tools to run scripts. While there is ISQL, you sill need to perform manual steps after every database installations.
Here is one approach we had taken in the past, while it probably could be improved upon, it should give some ideas about how you can automate distribution of SQL views.
This approach utilizes standard SalesLogix scripts to check for the existence of the required views, and if not found, creates them automatically.
First, on the OnOpen event of the form that utilizes the views we add a method, “FXCheckViews” to check on the existence of the views. While you could place this in several places, including the System Logon event, putting it on the OnOpen event only causes the script to be run if the user goes to the screen that needs the views.
Sub MainAXFormOpen(Sender, ID)
FXCheckViews
application.BasicFunctions.ProcessWindowMessages
‘Other code goes here
End Sub
The FXCheckViews routine is defined as follows:
Sub FXCheckViews
dim objrs
Set objrs = GetNewRecordset()
objrs.Open “select id from sysobjects where name = ‘FXLastActivity’ and type=’V'”
if(objrs.eof or objrs.bof) then ExecuteNativeSQL(“CREATE view sysdba.FXLastActivity as select convert(varchar(10),max(startdate),101) activitydate, contactid from activity with(NOLOCK) group by contactid”)
Set objrs = nothing
End Sub
The FXCheckViews uses a couple of custom routines, “GetNewRecordSet” and “ExecuteNativeSQL”. I will discuss those below. The routine will check to see if the view exists in the SQL system catalog. If it does not exist then it runs a T-SQL create view command to insert the view into the database.
“GetNewRecordSet” returns a RecordSet object and is defined as:
Function GetNewRecordset()
Set GetNewRecordset = CreateObject(“ADODB.Recordset”)
Set GetNewRecordset.ActiveConnection = GetNativeConnection()
GetNewRecordset.CursorLocation = 3 ‘adUseClient
GetNewRecordset.CursorType = 3 ‘adOpenStatic
GetNewRecordset.LockType = 3 ‘adLockOptimistic
End Function
Notice the custom call to “GetNativeConnection”, this is another custom routine I will discuss in a little bit. Also, notice that the RecordSet’s properties are set using the numeric value rather than the VB Constant. (This is important as on Vista or server 2008 systems the constants are no longer inherently supported).
Now if you refer back to the FXCheckViews routine you will see it uses “ExecuteNativeSQL”. This function executes a SQL statement against the database using a native SQL connection (bypassing the SalesLogix OLEDB Provider). This is because the SalesLogix OLEDB Provider does play nice with database alteration commands like CREATES, DROPS, ALTER, etc. So to get around this restriction we use a native SQL connection. Here is the sample “ExecuteNativeSQL”, which accepts an input of the SQL statement to be executed:
Function ExecuteNativeSQL(ByVal SQL)
Dim objConn
Dim lRecs
On Error Resume Next
lRecs = -1
Set objConn = GetNativeConnection
objConn.Execute SQL, lRecs, 128 ‘adExecuteNoRecords
If Err.Number <> 0 Then RaiseError “executing SQL statement”
On Error Goto 0
Set objConn = Nothing
ExecuteNativeSQL = lRecs
End Function
Again, notice this function uses the “GetNativeConnection” routine. Lets take a look at this, as this is key to getting around the SalesLogix OLEDB Provider.
Function GetNativeConnection()
Dim objRS
Dim connstring
Dim cn
Set objRS = Application.GetNewConnection.Execute(“slx_getNativeConnInfo()”)
connstring = objRS.Fields(0).Value & “;Password=” & SLXPW
objRS.Close
Set objRS = Nothing
Set cn = CreateObject(“ADODB.Connection”)
With cn
.ConnectionString = connstring
.Open
End With
If Err.Number <> 0 Then RaiseError “opening ADO connection object”
On Error Goto 0
Set GetNativeConnection = cn
Set cn = nothing
End Function
Now within this function we can see it is creating a RecordSet that is populated with a SalesLogix built-in procedure, “slx_getNativeConnInfo()“. This procedure returns the underlying native SQL connection string that is used by the SalesLogix OLEDB provider. As described in this post, the underlying connection string has the password parameter omitted from the returned string. To get around that we append the provided connection string with the password, which is stored in a constant, “SLXPW”:
Const SLXPW = “mypassword”
So there it is. One approach to automatically distributing SQL views out to SalesLogix remote databases. This approach does have a couple disadvantages:
- You need to store the SQL password in the script.
- You must hard-code the T-SQL create view statement in your code.
Aside from these issues, it is a pretty slick way of getting views out to users. This could be made more elegant with more time. Hopefully it might spur some more ideas.
Great post Kris.