Video Screencast Help

ESM SER Enterprise Reporting and MS SQL 2005

Created: 07 Dec 2007 • Updated: 22 May 2010 | 6 comments
Sunny G's picture
Hello there,
 
Is there any definitive guide on how to set up ESM 6.5.3 (and subsequently sp1 and sp2) with Enterprise Reporting 6.1.1 using SQL server 2005?
 
The sp1 and sp2 release notes make reference that it is supported and I know that it can work, but having difficulty in implementing.
 
First off, for the database foundations installation I needed to modify the database creation and set up scripts and run them manually in order to get them to complete successfully - note that you should be choosing complex passwords for all phases of the setup or will run in to problems!  then run the installer and use the import data option, which looked like it worked, but i got a fatal error message.  "
 
Now when i tried to do the initial install using the 6.5.3sp1 media, the install worked fine without having to go custom (it appeared) but then I got the same fatal error towards the end... ?
 
Looking at the DB, all the tables are created, logins, etc.  so looks pretty good, but there is no info in any of the tables.
 
so moving along.....  i'm working on setting up the RDL and the install works marvelous and shows as successful.  yet i am not getting any replication and upon further inspection of the database_link.log i see a SQL java connection error (or several) that looks like:
 
"WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] unable to fill pool
org.jboss.resouce.JBossResourceException: could not create connection; - nested throwable: (java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 (""): Data type 0x38 is unknown.)
 
AND
 
Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Paramter 1 (""): Data type 0x38 is unknown."
 
I know this is the SQL2000 jdbc driver from Microsoft.  I tried the SQL2005 version, but the install would not complete.  it complained about it.  but i have it and it is installed that I can use.
 
Any ideas, or thoughts on how to proceed are appreciated.  And if there is some documentation outlining the specific requirements and steps required to install Enterprise Reproting using SQL2005 that would be MOST helpful.
 
Thank you,
Sunny
Discussion Filed Under:

Comments 6 CommentsJump to latest comment

Tim White's picture
You must use the installer provided with SP1 or SP2.  This can be obtained from technical support.
 
The documentation does not directly address a fresh install, however describes it describes the upgrade installation.
 
The fresh installation should follow the same steps as for MSSQL 2000, however the JDBC driver uses different parameters.  You should consult the selected JDBC driver documentation for the appropriate URL, class name, etc.  In the past it was recommended to use the SourceForge third party driver, however we are currently testing the Microsoft JDBC driver and other customers are using it successfully.
 
For Microsoft's 2005 driver, they changed the format for the class name and URL.  I have used the following successfuly:
JDBC Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC Classpath: C:\Program Files\Microsoft SQL Server 2005 JDBC Driver\sqljdbc_1.1\enu\sqljdbc.jar
URL: jdbc:sqlserver://localhost:1433;databaseName=ESM
 
You may also need to modify the local security policy temporarily.  I think this was corrected in the final build, so only do this if you run into an error - I haven't tested either way with the final build personally.
 

Open the Local Security Policy editor and change the security policy to not require password complexity, and set minimum password length to 0 characters.  This can be switched back after installation.

 

If you require assistance please contact technical support. 

 

Thanks,

TW

Sunny G's picture

Thanks for the info...  a couple of comments.

I was able to complete the install using the 6.5.3 media by first modifying the create_esm_db and asssociated sql scripts and running that part of the DBF set up manually.  so that's good.  Then used the 6.5.3 DBF installer with 'Import Option' and was able to successfully complete the installation.

The next steps will be to upgrade to SP1 and SP2, which I have the media for, and don't expect to be a big problem.

With the JTDS driver, I used the sourceforge JTDS-1.2 JDBC driver and this worked OK, once I had the proper Syntax for the Driverclass, Classpath, and SQL connection URL.

I tried also with the SQL 2005 JDBC driver as you suggested, but received an error with the Classpath variable.  That should be the easiest one, right?  its simply the path to the folder and .JAR file.  but it claimed it could not find it for some reason and I couldn't continue.  What version of the 2005 MS JDBC Driver did you use?  i tried with 1.2.  I think that would be the best option going forward if it works.

Thanks,
Sunny

Tim White's picture
The 6.5.3 install would work, however there were changes that had to be made to natively support SQL 2005.  The supported option is to do the install fresh with SP1, which does not require the manual steps.  SQL 2005 was not supported with 6.5.3.
 
The upgrade shouldn't be a problem though - and upon completion you will be on a supported build for SQL 2005.
 
As for the driver, the JTDS driver should work fine.  I don't recall which Microsoft driver I downloaded, it was quite a while back.  The classpath should be the path to the file.  Perhaps it needs quotes if it didn't find it and your ceratin the path is correct.  I'm not sure offhand why else it would fail.
 
tw
Sunny G's picture
Hmm..  i don't recall if i tried the SQL 2005 driver with the SP1 ESM install.  I can try that or switch to it after/during the upgrade.  I found a 1.0, 1.1, and 1.2 version of the JDBC driver from the Microsoft site so i'll go with the newest.
 
I didn't use quotes, but did copy/paste the path so as to not err with typos...
 
I will likely go through some of the upgrade this week.  Thanks for the advice.
 
Sunny
Sunny G's picture
Looks like i did not have any issues with 6.5.3sp1.  I first upgraded while still using the JTDS driver, and then re-ran the installer with Import Data option and changed the JDBC driver to the MS SQL 2005 v1.2 version.
 
For those out there looking for some syntax clarifications:
 
when upgrading Database Foundations (without "quotes"):
JDBC Driver Class = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
JDBC Classpath = "C:\Program Files\Java\jre1.5.0_09\sqljdbc_1.2\enu\sqljdbc.jar (or wherever you unzipped it)
JDBC Library Path = <blank>
Database Connection URL = "jdbc:sqlserver://SQLSERVERNAME\INSTANCENAME:PORT;DatabaseName=ESM"
User: "sa"
Password: <sa Password>
 
when upgrading the reporting database link (without "quotes"):
JDBC Driver Class = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
JDBC Classpath = "C:\Program Files\Java\jre1.5.0_09\sqljdbc_1.2\enu\sqljdbc.jar (or wherever you unzipped it)
Database Connection URL = "jdbc:sqlserver://SQLSERVERNAME\INSTANCENAME:PORT;DatabaseName=ESM"
User: "ESM_DB_LINK_USER"
Password: <esm_db_link_user password>
 
Cheers,
Sunny
Sunny G's picture
i like how the emoticons mess up what i'm trying to write..  :)
 
to clarify the database connection url
 
jdbc:sqlserver://SQLSERVERNAME <backslash> INSTANCENAME <colon> PORT <semi-colon> DatabaseName=ESM
 
If there is no named instance, then omit the <backslash> INSTANCENAME and use
 
... SQLSERVERNAME <colon> PORT ... etc
 
for example, with a named instance
"jdbc:sqlserver://mysqlserver\esmdb:1433;DatabaseName=ESM"
 
without a named instance
"jdbc:sqlserver://mysqlserver:1433;DatabaseName=ESM"
 
Sunny