Client Automation Standard Practitioners Forum
Showing results for 
Search instead for 
Do you mean 

Reporting Problem (Query Error)

SOLVED
Go to Solution
Advisor

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
Trusted Contributor

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?
Advisor

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.
Trusted Contributor

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

Re: Reporting Problem (Query Error)

Is the standar version
Trusted Contributor

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?
Advisor

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.
Trusted Contributor

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.

Advisor

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.

Trusted Contributor

Re: Reporting Problem (Query Error)

What was the failure message on the other two?
Advisor

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'.
Trusted Contributor

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

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.
Trusted Contributor

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?
Advisor

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
Trusted Contributor

Re: Reporting Problem (Query Error)

Ok can you post all the logs from MessengingServer/logs please
Advisor

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
Advisor

Re: Reporting Problem (Query Error)

part2
Trusted Contributor

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

Re: Reporting Problem (Query Error)

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

Regards
Frequent Advisor

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
Frequent Advisor

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