CODEWITHSUNDEEP
sqlreporting-services
Merlin
Merlin

Reputation: 87

SQL Issue: the clr type does not exist or you do not have permission to access it

I keep throwing the following exception when working on a project in SSRS with Visual Studio:

the clr type does not exist or you do not have permission to access it

with the following query, the issue has something to do with the variables @rvMonthTwoStart and @rvMonthTwoEnd if I hardcode the dates for these two variables, everything works, if I pass the variables it errors out.:

SELECT        MEMBERS.scancode, MEMBERS.fname, MEMBERS.lname, MEMBERS.mtypeid, MEMBERS.status, MEMBERS.datejoin, MEMBERS.dateexpire, 
                         MEMBERS.daterenewal, ARTRANS.datetrans, ARTRANS.transdescription, ARTRANS.invoice, ARTRANSITEMS.itemdescription, ARTRANSITEMS.price, 
                         ARTRANSITEMS.quantity, PRODUCTS.description, PRODUCTS.scancode AS ProductCode, MEMBERTYPES.description AS MembershipType, SITES.sitename
FROM            ARTRANS INNER JOIN
                         ARTRANSITEMS ON ARTRANS.transid = ARTRANSITEMS.transid INNER JOIN
                         MEMBERS ON ARTRANS.memid = MEMBERS.memid INNER JOIN
                         PRODUCTS ON ARTRANSITEMS.productid = PRODUCTS.productid INNER JOIN
                         PRODUCTCATS ON PRODUCTS.productcatid = PRODUCTCATS.productcatid INNER JOIN
                         SITES ON ARTRANS.siteid = SITES.siteid INNER JOIN
                         MEMBERTYPES ON MEMBERS.mtypeid = MEMBERTYPES.mtypeid AND PRODUCTCATS.productcatgroupid = 2 AND NOT EXISTS
                             (SELECT        MEMBERS.memid
                               FROM            ARTRANS AS ART2 INNER JOIN
                                                         ARTRANSITEMS ON ART2.transid = ARTRANSITEMS.transid INNER JOIN
                                                         MEMBERS ON ART2.memid = MEMBERS.memid INNER JOIN
                                                         PRODUCTS ON ARTRANSITEMS.productid = PRODUCTS.productid INNER JOIN
                                                         PRODUCTCATS ON PRODUCTS.productcatid = PRODUCTCATS.productcatid INNER JOIN
                                                         MEMBERTYPES ON MEMBERS.mtypeid = MEMBERTYPES.mtypeid AND PRODUCTCATS.productcatgroupid = 2
                               WHERE        (ART2.memid = ARTRANS.memid) AND (ART2.datetrans BETWEEN @rvMonthTwoStart AND @rvMonthTwoEnd))
WHERE        (ARTRANS.datetrans BETWEEN @rvMonthOneStart AND @rvMonthOneEnd)

Upvotes: 0

Views: 2885

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10880

Have you updated your parameters and tried refreshing your dataset?

I think this error is when there is an element missing and SSRS assumes that it's because you don't have access or it doesn't exist.

It sounds like your parameters may not be linked or you're missing a field.

Try refreshing the dataset - Dataset Properties --> Refresh.

Then go to the Parameters tab and make sure they are all linked to a parameter.

enter image description here

Upvotes: 1

Related Questions