Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Sunday, December 16, 2018 
 
SalesLogix Synchronization Report  
Description:  This article provides a script that can be used to track synchronization activity. The purpose of the script is to analyse the OUTFILES directory and report on each user by date/time and size of outbound TEFs. Any user who has not synced within seven days is highlighted in red within the report.

Category:  SalesLogix Administration
Author:  Mike Spragg
Submitted:  2/10/2004
   
Stats: 
Article has been read 21854 times

Rating: - 4.9 out of 5 by 19 users
 

fiogf49gjkf0d
SalesLogix Synchronization Report

This article provides a script that can be used to track synchronization activity. The purpose of the script is to analyse the OUTFILES directory and report on each user by date/time and size of outbound TEFs. Any user who has not synced within seven days is highlighted in red within the report.


Server Implementation Steps

  • Logged into MS Query Analyser as SYSDBA execute the following:

    CREATE TABLE SYNC_REPORT (
    	SITECODE varchar (4) NULL ,
    	FILESIZE float NULL ,
    	FILEDATE datetime NULL )

  • Copy the enclosed sendMail.dll into the \system32 directory. Register the DLL using regsvr32 sendmail.dll. NB: If you already have a sendmail.dll you can copy the DLL to any location and register it from there.

  • Copy the supplied WeeklySyncReport.vbs file to a known location

  • Optional - setup Scheduler to run the script on a weekly/daily basis.

Alter the VB Script to match your details

NB: You must do this before running for the 1st time! Open WeeklySyncReport.vbs and alter the details as follows:

Line 23 - set this to the location of your outfiles folder:

Set fOutfiles = FSO.GetFolder("\\mike1200\c$\SLXData.NET\slxnet\Outfiles")

Line 28 - set:

Source = Your SQL Server machine name
Catalog = Database Name

dbConn.Open "Provider=sqloledb;Data Source=MIKE1200;Initial Catalog=SLXNET;User Id=sysdba;Password=masterkey"

Line 94 - 104 - set the email addresses and server name:

' Set the message properties
msg.RecipientEmail = "mike.spragg@myemailaddress.com" '<- Use a distribution list
msg.SenderName = "SalesLogix Sync Report"
msg.SenderEmail = "mike.spragg@myemailaddress.com"
msg.Subject = "Weekly SalesLogix Sync Report"

' add the report to the message body
msg.MessageHTML = strReport

' Send the message
msg.Send "10.0.0.4" '<- enter the name/IP address of your SMTP server here

NB: Check Line 39 & 52 - remove CONVERT if not being used in UK.


Multiple SyncServers/FTP Sites

If you are running multiple sync servers and/or FTP sites you will need to alter lines 43 - 53 to include any additional directories you wish to scan:


'----------------------------------------------------------------------------------------
' ------- 2nd/3rd etc Outfiles e.g. for FTP Sync and multiple outfile directories -------
'----------------------------------------------------------------------------------------

' Go through the Outfiles folder on sync server 2
' Set fOutfiles = FSO.GetFolder("\\mike1200\c$\SLXData.NET\slxnet\Outfiles2")
	
' Go through the Outfiles Dir for additional syncservers or FTP sync files and populate the SYNC_REPORT Table

' for each f In FOutfiles.Files
'	dbConn.Execute "INSERT INTO SYNC_REPORT VALUES ('" &  left(f.Type,4) & "', " & toMB(f.size) & _ 
'	               ", convert(datetime, '" & f.DateLastModified & "',103))"
' Next


Run the Report

Simply double-click the WeeklySyncReport.vbs script - this will execute and then send the report via email to the recipient(s) named above.


Viewing the output

A successful email output will look like this:




Downloading the code

Download the files and complete script needed to generate the synchronization report below:

 Click here to download the files


 

About the Author

  Mike Spragg
(SalesLogix Business Partner)
empath-e Limited

fiogf49gjkf0d
10 Years+ with SLX !
(Formerly of e1 Business)


View online profile for Mike Spragg
 

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

Comments & Discussion you must log-in to add comments. [login here] 
 
Author Article Comments and Discussion
Ryan Farley

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

Re: SalesLogix Sync Report
Posted: 2/11/2004 12:00:19 AM
fiogf49gjkf0d
Excellent stuff Mike. Thanks for sharing.

-Ryan
 
Jason Buss



Re: SalesLogix Sync Report
Posted: 2/11/2004 7:49:45 AM
fiogf49gjkf0d
Very cool stuff...
 
Mike Bisek
 

Re: SalesLogix Sync Report
Posted: 2/11/2004 8:58:51 AM
fiogf49gjkf0d
Sweet!

Thank you! This is the kind of innovation I like to see!

Bravo
Mike Bisek
 
Kevin Hoelzel
 

Re: SalesLogix Synchronization Report
Posted: 3/11/2004 10:50:46 AM
fiogf49gjkf0d
Mike, I'd like to see if I can get this to work in an exchange environment since we don't use an SMTP mail system. Any suggestions? I tried using the Exchange server name but that didn't work. Excellent and simple code also. Great job!
 
Ryan Farley

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

Re: SalesLogix Synchronization Report
Posted: 3/11/2004 8:07:40 PM
fiogf49gjkf0d
Kevin,

I use this with Exchange. I opened Exchange for SMTP that is allows only from the machine this is run on. As far as connecting to Exchange direct without the use of SMTP (I see no benefit in doing this) a Google search should reveal some code that could be translated to VBScript easily.
 
Chris Collins
 

Re: SalesLogix Synchronization Report
Posted: 5/19/2004 8:27:56 AM
fiogf49gjkf0d
Mike,

This works great, can't thank you enough for sharing with the rest of us.
 
Christian B. Mortensen
 

Re: SalesLogix Synchronization Report
Posted: 8/8/2005 8:52:33 AM
fiogf49gjkf0d
can i use the sendmail component directly in saleslogix? and from there send my email?
I know there's an article on how to use the slx mail client, but we don't use that one.

any clues?

regards
Christian
 
Ryan Farley

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

Re: SalesLogix Synchronization Report
Posted: 8/8/2005 11:31:29 AM
fiogf49gjkf0d
Christian,

You certainly can use the sendmail.dll from a SLX script - it is simply a standard COM dll. However, that does mean you need to distribute the DLL and register on all client workstations.

-Ryan
 
Sam Cayze
 

Re: SalesLogix Synchronization Report
Posted: 1/23/2006 6:19:15 PM
fiogf49gjkf0d
Looks like a great tool! However, I am trying to set it up, and I get this error:

Line: 39
Char: 3

The conversion of a char data type to a datetime data type resulted in an out-of-range datatime value.
Code: 80040E07
 
Ryan Farley

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

Re: SalesLogix Synchronization Report
Posted: 2/6/2006 9:40:43 AM
fiogf49gjkf0d
Hi Sam,

Just a few guesses here (I didn't write this one but thought maybe I could proide some insight).

1) Did you create the SYNC_REPORT table?
2) Did you modify the script to point to the correct location for sync files?

Anyway, just a few things to look at to hopefully get this working.

-Ryan
 
Sam Cayze
 

Re: SalesLogix Synchronization Report
Posted: 2/7/2006 10:48:17 AM
fiogf49gjkf0d
Thanks Ryan. I did all those things. I think this is what I am caught on:

"NB: Check Line 39 & 52 - remove CONVERT if not being used in UK."

Since I am in the US, I tried that, but without luck.

Can someone post an example of a US version of this script?

Thanks.
 
Ryan Farley

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

Re: SalesLogix Synchronization Report
Posted: 2/7/2006 12:15:22 PM
fiogf49gjkf0d
Sam,

Maybe you could just change it to convert to USA (101) or default (0 or 100) instead of removing it?

ie:

Change from this:
convert(datetime, '" & f.DateLastModified & "',103)

to this:
convert(datetime, '" & f.DateLastModified & "',101)

Give it a try. BTW, I've not run this myself, but I believe my support guys did without issue.

-Ryan
 
Mike Spragg

slxdeveloper.com Forum Top 10 Poster!

Re: SalesLogix Synchronization Report
Posted: 2/7/2006 12:21:03 PM
fiogf49gjkf0d
Ryan's correct - the conversion is added for the UK only - so, for US, simply remove !
 
Mike Spragg

slxdeveloper.com Forum Top 10 Poster!

Re: SalesLogix Synchronization Report
Posted: 2/7/2006 12:23:54 PM
fiogf49gjkf0d
Sorry, I meant fully remove (watch brackets):

dbConn.Execute "INSERT INTO SYNC_REPORT VALUES ('" & left(f.Type,4) & "', " & toMB(f.size) & ", convert(datetime, '" & f.DateLastModified & "',103))"

to

dbConn.Execute "INSERT INTO SYNC_REPORT VALUES ('" & left(f.Type,4) & "', " & toMB(f.size) & f.DateLastModified & "')"
 
Sam Cayze
 

Re: SalesLogix Synchronization Report
Posted: 2/7/2006 12:30:28 PM
fiogf49gjkf0d
Thanks guys! I removed the 103 on lines 39 and 52, and replaced it with 101. That did the trick. (Well, no mo errors at least).

However, now I get:
Line 65
Error: Invalid Object Name: "sysdba.SYNC_REPORT"
Microsoft OLE DB Provider for SQL

I created the table via that script that was mentioned in the article. Could this be a permission thing?

Can I use sa instead of sysdba?

(I am not a DB expert at all!)

Thanks!
 
Ryan Farley

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

Re: SalesLogix Synchronization Report
Posted: 2/7/2006 1:37:53 PM
fiogf49gjkf0d
Sam,

If you created the SYNC_REPORT table when logged in as the sysdba user then you should prefix it with "sysdba." (unless your connection string specifies sysdba as the user you're logging in with). If the table is not owned by sysdba (which sounds like this is your case), just omit the table owner prefix before the table name.

-Ryan
 
Sam Cayze
 

Re: SalesLogix Synchronization Report
Posted: 2/7/2006 4:26:57 PM
fiogf49gjkf0d
I think I have made some progress, because I am now stuck on it sending the email:

"Component 'MSWINSCK.OCX' or one of its dependencies is not correctly registered: a file is missing or invalid."
Source: Sendmail
 
Ryan Farley

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

Re: SalesLogix Synchronization Report
Posted: 2/7/2006 4:40:24 PM
fiogf49gjkf0d
Sam,

I've not used the sendMail.dll before that Mike used in this article, but it looks like it might be written in VB6 or prior and requires some files that might be missing.

Best bet is to search for MSWINSCK.OCX and you're bound to find some places to download it (as well as complete VB runtimes) that appear to be needed for that DLL to work.

-Ryan
 
Mike Spragg

slxdeveloper.com Forum Top 10 Poster!

Re: SalesLogix Synchronization Report
Posted: 2/24/2006 4:18:47 PM
fiogf49gjkf0d
Sorry, I've only just seen these issues. Please send me an email directly (mike.spragg@e1business.com) and, in return, you'll get full instructions plus the required files (sendmail.dll and mswinsck.ocx)
 
Mike Spragg

slxdeveloper.com Forum Top 10 Poster!

Re: SalesLogix Synchronization Report
Posted: 2/24/2006 4:18:48 PM
fiogf49gjkf0d
Sorry, I've only just seen these issues. Please send me an email directly (mike.spragg@e1business.com) and, in return, you'll get full instructions plus the required files (sendmail.dll and mswinsck.ocx)
 
Konstantin Vishnevsky



Re: SalesLogix Synchronization Report
Posted: 12/7/2006 10:06:45 AM
fiogf49gjkf0d
Just tried this script on my app server. Since the sendmail option doesn't always work (as in my case), we had to find a workaround (works only on Win2000 Server and Win2003 Server).

Instead of:

[code]

' Create a new SMTP message object
Set msg = CreateObject("SendMail.Message")

' Create a new SMTP message object
'Set msg = CreateObject("SendMail.Message")

' Set the message properties
'msg.RecipientEmail = "name@email.com"
'msg.SenderName = "SalesLogix Sync Report"
'msg.SenderEmail = "name@email.com"
'msg.Subject = "Weekly SalesLogix Sync Report"

' add the report to the message body
'msg.MessageHTML = strReport

' Send the message
'msg.Send "10.0.0.4"
[/code]


I used

[code]

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Weekly SalesLogix Sync Report"
objMessage.From = "name@email.com"
objMessage.To = "name@email.com"
objMessage.HTMLBody = strReport

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.0.0.4"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send
[/code]
 
SLX Admn
 

Re: SalesLogix Synchronization Report
Posted: 12/22/2006 2:02:26 PM
fiogf49gjkf0d
Excellent pice of work!

Just one question, How do i sort according to the Date instead of Names in the report? Also, for 3, 4,5 outfile locations, what kind of code I need to add. When I add stuff it works for the Second location, but how do I add 3, 4, 5 locations.

Thanks in advance!
 
Leif
 

Re: SalesLogix Synchronization Report
Posted: 4/25/2007 12:02:13 PM
fiogf49gjkf0d
Oracle version?
 
Mike Spragg

slxdeveloper.com Forum Top 10 Poster!

Re: SalesLogix Synchronization Report
Posted: 4/25/2007 12:06:33 PM
fiogf49gjkf0d
Leif, as this is not database/SLX specific - all you'd need to do is adjust the "provider" details - instead of using sqloledb (the Microsoft SQL Connection) simply use the Oracle equivalent (or MSDAORA) instead.
 
Leif
 

Re: SalesLogix Synchronization Report
Posted: 4/25/2007 12:26:55 PM
fiogf49gjkf0d
Did...get an error on line 64 char 2 Ora-00923 FROM keyword not found where expected.
 
David Henry
 

Re: SalesLogix Synchronization Report
Posted: 4/25/2007 3:46:17 PM
fiogf49gjkf0d
Mike,

Excellent work!

We even took the HTML report output a bit further to include additional custom fields. Also we export the HTML to an Excel template for maipulation by the user community as needed taking in consideration the custom fields we added (as well as the original fields you set) to the report.
 
Leif
 

Re: SalesLogix Synchronization Report
Posted: 5/23/2007 1:31:18 PM
fiogf49gjkf0d
Actually, I am having a problem with this line of code....Oracle 10g doesn't like it:

dbConn.Execute "INSERT INTO SYNC_REPORT VALUES ('" & left(f.Type,4) & "', " & toMB(f.size) & ", convert(datetime, '" & f.DateLastModified & "',103))"
 
Leif
 

Re: SalesLogix Synchronization Report
Posted: 5/23/2007 1:31:33 PM
fiogf49gjkf0d
Actually, I am having a problem with this line of code....Oracle 10g doesn't like it:

dbConn.Execute "INSERT INTO SYNC_REPORT VALUES ('" & left(f.Type,4) & "', " & toMB(f.size) & ", convert(datetime, '" & f.DateLastModified & "',103))"
 
Mike Spragg

slxdeveloper.com Forum Top 10 Poster!

Re: SalesLogix Synchronization Report
Posted: 5/23/2007 1:53:28 PM
fiogf49gjkf0d
Yes, because the DateDiff isn't valid for Oracle - you'd need equivalent function (this is mssql syntax):

convert(datetime, '" & f.DateLastModified & "',103))"

So, whatever Oracle uses (essentially, you're converting the time of the file to a valid [Oracle] date.
 
Konstantin Vishnevsky



Re: SalesLogix Synchronization Report
Posted: 11/27/2007 9:52:18 AM
fiogf49gjkf0d
Mike, please correct me if I'm wrong, but wouldn't it make more sense to use DateCreated instead of DateLastModified?
The reason is that if the file is a ZIP and it contains, let's say a PDF document from a library that was created in May, DateModifed shows of a PDF file and not the Sync file itself, thus showing in report that user has not synced since May.
 
Mike Spragg

slxdeveloper.com Forum Top 10 Poster!

Re: SalesLogix Synchronization Report
Posted: 11/27/2007 10:23:08 AM
fiogf49gjkf0d
Hi Konstantin

Would make no difference - the reason being - the zip file is actually a zip of the contents and the TEF's therein - not the document date of the content. The createdate/modifydate would actually be the same thing (as the file can never be modified once created).
A zipped TEF file contains many transactions and, importantly, only part of the actual file (if an attachment) as the SyncServer splits the file into several parts if > 1Mb.

So, in summary - we're looking at the date created/modifed of the TEF - not the content.

Cheers
Mike
 
Konstantin Vishnevsky



Re: SalesLogix Synchronization Report
Posted: 11/27/2007 10:28:13 AM
fiogf49gjkf0d
Mike, I beg to differ. I can send you a TEF I have ran across yesterday. It's Created date: Wednesday, November 21, 2007, 3:01:29 PM, but the Modified date: Wednesday, May 23, 2007, 1:08:06 PM
I spoke to Paul Hubbard, SAGE Customer Support Manager and he informed me that this may be the case. Here's what he said in his email:

The ZIP- tells me this is (or was?) an attachment. The dates most likely mean the file compressed in the TEF was probably last modified on May 23, but the TEF file being viewed was created on Nov 21. I've seen that behavior outside of SalesLogix, I believe it was with a PDF file inside a Zip file.


 
Mike Spragg

slxdeveloper.com Forum Top 10 Poster!

Re: SalesLogix Synchronization Report
Posted: 11/27/2007 10:34:31 AM
fiogf49gjkf0d
Really? Oh - it shouldn't do that - I always thought it was re-written especially if it's just a portion of the file. We're talking about a ZIP-FILExxxx file aren't we ?? If so, then, I defer to you and it should be createdate !!
 
Konstantin Vishnevsky



Re: SalesLogix Synchronization Report
Posted: 11/27/2007 10:38:11 AM
fiogf49gjkf0d
Yes sir, we are talking about ZIP files :)
No wonder many of my users were marked as non synching and we couldn't understand why is that.

But your report is a very useful utility, we are using it constantly. Thanks for providing the code!!!
 
Mike Spragg

slxdeveloper.com Forum Top 10 Poster!

Re: SalesLogix Synchronization Report
Posted: 11/27/2007 10:39:38 AM
fiogf49gjkf0d
Thanks for the info though - will adjust the master copy !
 
Ryan Farley

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

Re: SalesLogix Synchronization Report
Posted: 11/27/2007 11:14:44 AM
fiogf49gjkf0d
Mike,

If you end up making this change in the bundle, send me a copy so I can update the download on the site.

Thanks!
-Ryan
 
Tracy
 

Re: SalesLogix Synchronization Report
Posted: 4/8/2008 10:55:33 PM
fiogf49gjkf0d
Ryan.I get this error:
Line 92
Error:Active Invalid Create Object: "SendMail.Message"
 
Tracy
 

Re: SalesLogix Synchronization Report
Posted: 4/9/2008 12:32:39 AM
fiogf49gjkf0d
there is another new problem
line:104
Error: component 'MSWINSCK.OCX' or one of its dependencies not correctly registered:a file is missing orinvalid
 
Mike Spragg

slxdeveloper.com Forum Top 10 Poster!

Re: SalesLogix Synchronization Report
Posted: 4/9/2008 12:36:01 AM
fiogf49gjkf0d
Tracy, there are several components you need to register which I do not include as they can sometimes interfere with normal sendmail.dll - contact me directly on mike.spragg@empath-e.com for details.
 
Kevin
 

Re: SalesLogix Synchronization Report
Posted: 6/29/2010 8:13:58 AM
fiogf49gjkf0d
Mike - I am getting the MSWINSCK.OCX error. I will email you shortly. Thanks!
 
 

       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 © 2018 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/16/2018 4:00:53 PM