MS SQL Server / RoboHelp Server 7

Report · Mar 04, 2008

Has anyone integrated RoboHelp Server 7 with MS SQL ? I see that the JET .MDB database works fine but when I point the configuration app to a MS SQL Server database I am able to generate the required tables but one of the pages uses a dialect of SQL that MS SQL Server does not like. i.e.

http://{server-ip}/Robo/Reports/data/os_stats.asp?print=0&lstReport=%3CView+another+report%3E&date=d...

fails with SQL:

SELECT us_OperatingSystem, COUNT(*) FROM TUser, TSession WHERE TUser.us_UserID = TSession.us_UserID AND NOT (Left$(TUser.us_ClientIP,4) = 'test')AND se_InitiationTime>='03/03/2008 11:00:00' AND se_InitiationTime<='03/04/2008 11:00:00' GROUP BY us_OperatingSystem ORDER BY COUNT(*) DESC

This is because MS SQL Server has no such function "LEFT$" but they do have "LEFT".

Have I mis-configured something or is this a breakdown in the code?

Report · Mar 05, 2008

Cheese

I have tested (briefly) using the jet DB and then pointing to a SQL instance and I recommend checking the following.If you only point it to a SQL instance then you will get failures etc. You will have to go through the DB setup again.
1. Create new System DSN ODBC connection pointing to the relevant server and SQL instance with correct SQL credentials.
2. During the ODBC creation process make sure that the "Perform translation for character data" option is ticket (should be default)
3. Test connection first before proceeding.
4. Launch RBH Config Manager and select Database icon then select Microsoft SQL Server/Oracle 9i or higher and complete the SQL configuration as create above.
5. DB will created with tables etc etc , you should notice a confirmation msg

Try that now and all should be OK. I have just tested that and all works fine for me on the following config

Windows 2003 Std server SP1
MS SQL 2005 Std

Cheers

JP

Report · Mar 05, 2008

Cheese

Just another update I forgot about my previous post

When setting up the OBDC settings make sure to tick the option to "Change default Database to:" and select Master.

Cheers

JP

Report · Mar 06, 2008

Thanks for your generous reply, IfNotWhyNot !

Unfortunately after trying a few times I am unable to get this specific page to work properly. MS SQL does, indeed, work with RoboHelp Server 7 from a superficial level for me. I've reinstalled a few times, setup different ODBC connections, reconfigured, etc.

However, the problem is that the *specific* SQL that is generated on Robo/Reports/data/os_stats.asp fails when "Exclude IPs beginning with ....." is used. The programmer uses the function "LEFT$" when MS SQL does not have this such function. It has the function LEFT, though. When I remove the $ from the SQL the page works properly.

I feel this is a programming bug: the expectation that "LEFT$" is honoured by the database when, in fact, it isn't in the case of MS SQL. This should fail on Oracle, too - I believe.

There is one last possibility I see: The MS SQL ODBC Driver should have been able to translate LEFT$ to LEFT, but I'm not sure that ODBC is supposed to be doing that... :)