Client Automation Standard Practitioners Forum
cancel

Reporting Problem (Query Error)

SOLVED
Go to solution
Highlighted
IvanArce
Super Collector

Reporting Problem (Query Error)

Hello

I have a message error when I try to access to Client Automation Standard - reporting:

42S22 207 {[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'SESSIONID'.}
while executing
"$dbHandle "$query""
(object "::sql0" method "::rrs::sql::query" body line 12)
invoked from within
"query "DELETE FROM [set l_sessionTable] WHERE SESSIONID = [sqlValue $sessionId]""
(object "::sql0" method "::rrs::sql::cleanDeviceTable" body line 28)
invoked from within
"cleanDeviceTable"
(object "::sql0" method "::rrs::sql::doMount" body line 16)
invoked from within
"doMount"
(object "::sql0" method "::rrs::sql::getDatabaseType" body line 3)
invoked from within
"$::rrs::sqlDb($DSN) getDatabaseType"
(procedure "window_PrintWindow" line 54)
invoked from within
"$procName $viewparams($window) $windowParams $window"
(procedure "resultStub" line 41)
invoked from within
"resultStub"
invoked from within
"catch $cmd"

I hope that you can help me with this issue

Regards
21 REPLIES
Steve Berube
Frequent Visitor
Solution

Re: Reporting Problem (Query Error)

In your SQL DAtabase, how many tables do you have that begin with RRS_?
There should be one called RRS_SESSIONTABLE I believe, can you check if that exists?
IvanArce
Super Collector

Re: Reporting Problem (Query Error)

Thank you for your answer.

I have 3 tables with this prefix(RRS_)and one of it is "RRS_SESSION", Should I need change the name to "RRS_SESSIONTABLE"?. I was trying to run the prereq Script for RIM, but I have this error: "Server: Msg 107, Level 16, State 2, Procedure DevicesPrimary, Line 5
The column prefix 'dbo.deviceconfig' does not match with a table name or alias name used in the query.

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)"

I looked in the DB and this table exist and the Owner is dbo.
Steve Berube
Frequent Visitor

Re: Reporting Problem (Query Error)

Ok, is this CAS (Standard version of CA) Because CAS shipped with a pre-populated SQL database, if this is for the enterprise version, this thread should be moved to that forum. Please let me know.
IvanArce
Super Collector

Re: Reporting Problem (Query Error)

Is the standar version
Steve Berube
Frequent Visitor

Re: Reporting Problem (Query Error)

Ok, the prereq script shouldn't be required to be ran then. The ccmdb.mdf provided is a ready to go database schema all setup.

can you verify the ODBC info used for the CAS setup is pointing to the correct database?
IvanArce
Super Collector

Re: Reporting Problem (Query Error)

Ok, I check and I only have one System DSN ODBC configurate on the server, and is the same that I use for CAS the name is "HPCA_CORE" and is pointing to "HPCA_CORE_FINAL" DB, I made a test conecction with the ODBC and it was successfully.

Actually I don't have many devices on the CAS, so if you recomend I can reinstall the applicationa and do it step by step, maybe I miss something.
Steve Berube
Frequent Visitor

Re: Reporting Problem (Query Error)

Ok you have given me a bit more info that will help. What version of CAS are you on?

There is a service called hpca-ms, when that starts up, it will create the DB structure required. That does run a prereq script, to manually run it, go into this folder
ReportingServer/prereq/sql

Open the 3 sql files in SQL Query Analyzer
RIM PreReq Creation Script.sql
RPM PreReq Creation Script.sql
RUM PreReq Creation Script.sql

And run each of them, once that has completed, please retry accessing reporting.

IvanArce
Super Collector

Re: Reporting Problem (Query Error)

Where I can find the Version?

I haven't the service hpca-ms but I have "hpca-messaging server"(run) and "hpca-multicast server" (stop).

I ran the 3 scripts but only RUM's did the job, the other 2 fail.

Steve Berube
Frequent Visitor

Re: Reporting Problem (Query Error)

What was the failure message on the other two?
IvanArce
Super Collector

Re: Reporting Problem (Query Error)

RIM:"Server: Msg 107, Level 16, State 2, Procedure DevicesPrimary, Line 5
The column prefix 'dbo.deviceconfig' does not match with a table name or alias name used in the query.

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)"

and for RPM

Server: Msg 2760, Level 16, State 1, Procedure DELETE_HPPATCH_DEVICE, Line 11
Specified owner name 'DBO' either does not exist or you do not have permission to use it.
Server: Msg 137, Level 15, State 2, Procedure fn_WUAVERSION, Line 21
Must declare the variable '@var_WUA_STRPOS1'.
Server: Msg 156, Level 15, State 1, Procedure fn_WUAVERSION, Line 25
Incorrect syntax near the keyword 'else'.
Server: Msg 137, Level 15, State 1, Procedure fn_WUAVERSION, Line 25
Must declare the variable '@var_WUA_STRPOS1'.
Server: Msg 137, Level 15, State 1, Procedure fn_WUAVERSION, Line 28
Must declare the variable '@var_WUA_STRPOS1'.
Server: Msg 137, Level 15, State 1, Procedure fn_WUAVERSION, Line 29
Must declare the variable '@var_MediaWUA'.
Server: Msg 137, Level 15, State 1, Procedure fn_WUAVERSION, Line 30
Must declare the variable '@var_WUA_STRPOS1'.
Server: Msg 156, Level 15, State 1, Procedure fn_WUAVERSION, Line 35
Incorrect syntax near the keyword 'else'.
Server: Msg 137, Level 15, State 1, Procedure fn_WUAVERSION, Line 35
Must declare the variable '@var_WUA_STRPOS1'.
Server: Msg 137, Level 15, State 1, Procedure fn_WUAVERSION, Line 38
Must declare the variable '@var_WUA_STRPOS2'.
Server: Msg 137, Level 15, State 1, Procedure fn_WUAVERSION, Line 39
Must declare the variable '@var_MediaWUA'.
Server: Msg 137, Level 15, State 1, Procedure fn_WUAVERSION, Line 41
Must declare the variable '@var_WUA_STRPOS2'.
Server: Msg 156, Level 15, State 1, Procedure fn_WUAVERSION, Line 46
Incorrect syntax near the keyword 'Else'.
Server: Msg 137, Level 15, State 1, Procedure fn_WUAVERSION, Line 46
Must declare the variable '@var_WUA_STRPOS2'.
Server: Msg 137, Level 15, State 1, Procedure fn_WUAVERSION, Line 50
Must declare the variable '@var_WUA_STRPOS3'.
Server: Msg 156, Level 15, State 1, Procedure fn_WUAVERSION, Line 54
Incorrect syntax near the keyword 'else'.
Server: Msg 137, Level 15, State 1, Procedure fn_WUAVERSION, Line 54
Must declare the variable '@var_WUA_STRPOS3'.
Server: Msg 156, Level 15, State 1, Procedure fn_WUAVERSION, Line 58
Incorrect syntax near the keyword 'else'.
Server: Msg 156, Level 15, State 1, Procedure fn_WUAVERSION, Line 63
Incorrect syntax near the keyword 'else'.
Server: Msg 156, Level 15, State 1, Procedure fn_WUAVERSION, Line 68
Incorrect syntax near the keyword 'else'.
Server: Msg 156, Level 15, State 1, Procedure fn_WUAVERSION, Line 74
Incorrect syntax near the keyword 'else'.
Steve Berube
Frequent Visitor

Re: Reporting Problem (Query Error)

Ok when you are running this script, have you selected the proper database in query analyzer? The RIM Script requires the deviceconfig table, but it shows that it can't find that. My guess is that its targeting the wrong database.
IvanArce
Super Collector

Re: Reporting Problem (Query Error)

Yes I'am targeting to the correct DB (well I hope so). I'am attaching you a print screen of this.
Steve Berube
Frequent Visitor

Re: Reporting Problem (Query Error)

Ok, please check the following information

1. Verify the CORE UI under configuration > infrastructure > database settings. Note the ODBC DSN selected
2. Verify the DSN advanced properties is set to use HPCA_CORE_FINAL
3. Does your HPCA_CORE_FINAL have a deviceconfig table?
IvanArce
Super Collector

Re: Reporting Problem (Query Error)

1.- HPCA_CORE

2.-I check this in administrative tools>datasouces(odbc) is correct?. the default database is "HPCA_CORE_FINAL".

3.- yes
Steve Berube
Frequent Visitor

Re: Reporting Problem (Query Error)

Ok can you post all the logs from MessengingServer/logs please
IvanArce
Super Collector

Re: Reporting Problem (Query Error)

there are many of them (113 files) and some of those file are over 40Mb I'am Attaching you what is look like to be the resume log.

thank you for your patience
IvanArce
Super Collector

Re: Reporting Problem (Query Error)

part2
Steve Berube
Frequent Visitor

Re: Reporting Problem (Query Error)

Ok there is definately some odd db configuration issue going on from what I can tell from the logs. I suggest contacting support so they can do a VROOM with you and troubleshoot further.
IvanArce
Super Collector

Re: Reporting Problem (Query Error)

Ok, thank you for your time, I will do that.

Regards
praveen viswana
Regular Collector

Re: Reporting Problem (Query Error)

Hi Ivan

I am also facing the exact problem and all the scenarios u have mentioned are same .

Please let me know if u have solved this issue

Regards
Praveen V
praveen viswana
Regular Collector

Re: Reporting Problem (Query Error)

Hi Ivan

I have found the solution for the above query

Please change the collation in the properties of the SQL database to SQL Latin1_General_CI_AS

Before changing make sure all the CAS services are stopped

Points will be appreciated if the detail is useful to you

Regards
Praveen V
//Add this to "OnDomLoad" event