I am trying to add a Subreport in SSRS using parameters getting the below error
soumya
Vertica Customer
When Iam trying to connect to subreport report from main report in SSRS using parameters, I am getting the following error, I am using a sql statement where channelID =@channelID in filter condition, Both are int why the below while execution though, any help would be appreciated.
Error message:
Operator does not exist :int = @int hint : No operator matches the given name and argument type you may need to add explicit type casts
Best Answer
-
avoronin
✭
As far as I know vertica does not have parameters. Under SSIS I had to code constants right into query text. (So called sql injection).
This was intended for some script interpreter. :int = @int hint : It does not work under ADO.NET Driver.
The solution for SSRS could be to use dynamic script on MS SQL side than run it against linked server to vertica.
Fragment of worcable code under SSIS
" ... WHERE POSTINGDATE BETWEEN " + (DT_WSTR, 99999)(@[User::IntDateStart]) + " AND " + (DT_WSTR, 99999)(@[User::IntDateEnd]) + "ORDER BY POSTINGDATE, HASH;"1
Answers
Thank you, Will try this.
To me it seems that the parameters are not configured properly.
What is the version of Vertica, SSRS and Operating System that you are using? Which Vertica driver (Name and Version) are you using?
Please specify the step by step process for reproducing the issue.
See, I am trying to run simple script under SSIS via OLE DB. Latest drivers.
DROP TABLE IF EXISTS A_TMP.START_END_DATE;
CREATE TABLE A_TMP.START_END_DATE AS
SELECT ? AS START_DATE, ? AS END_DATE;
Has added two params, passed two int vars into them, named them "0", "1".
Such approach perfectly works for MS SQL or ORACLE.
But for vertica I get the error
"Failed with the following error: "ERROR 4856: Syntax error at or near "AS" at character 90".
Hi Avoronin, Can you try using ADO.NET driver?
ADO.NET driver that's exactly what I am using. The thing is, it does not have parameters support under SSIS. Any ADO.NET does not have. I am using expressions. The above was test for OLEDB.
Hi Avoronin,
I have gone through the details you have posted. If you need any information regarding the connectivity, you can look into the Connection Guide document. Link for which is:
https://www.vertica.com/kb/SSIScg/Content/Partner/SSIScg.htm
Apart from that there are few points that we need to take care of while working with Vertica's ADO.NET Driver, which are as follows:
1) Since the type of column was not defined while creating table, you may need to type cast the variable which is used for defining the column type and inserting data in it. For your example, you can use it like @::INT
3) If we have multiple queries which are not using the same number of parameter and the same sequence of paramater then avoid using single Execute SQL Task component for them rather use multiple Execute SQL Task components.
4) Value of the parameter is to be provided in the Parameter tab where the parameter is defined.
5) "Parameter Name" field in "Parameter Mapping" tab should contain the name of parameter proceeded by "@". This procedure is different for other drivers.
In case you still are facing issue, kindly write me back.
Thanks