Problem with SQLCacheDependency

I am using a grid to display the data on my webpage, and I have 3 projects in my solution (UI, BLL, DAL). When I load the webpage I am creating the instance of a class written n my BLL project to populate the data, and in BLL I'm creating the object for the class in DAL and returning the datatable.

Now when I refresh the page using F5, the SQLDependency is working fine. But, when I use the Paging or Sorting option's on my grid I see in SQL Profiler that the query is posted back to the SQLServer to get the data.

If this is the senario then how can I make use of the cache object in .NET 2.0.

Please advice,
Ravi

I've posted a blog article that tries to explain some of the misteries behind Query Notifications here: http://blogs.msdn.com/remusrusanu/archive/2006/06/17/635608.aspx, maybe it can help you troubleshoot the issue.

I'm relly no expert in the SqlDependnecy functionality, but the explanation you give seems way too short to understand what the problem is.

HTH,
~ Remus

sql

Problem with SELECT, GROUP BY and aggregate function

Hi all,

I have a problem with an SQL-query and I don't know what the best solution would be to solve the problem.

/*INSERT INTO WERKS (
WERKS.Z8601,
WERKS.Z8602,
WERKS.Z8603,
WERKS.Z8604,
WERKS.Z8605,
WERKS.Z8606,
WERKS.Z8607,
WERKS.Z8608,
WERKS.Z8609,
WERKS.Z8610,
WERKS.Z8611,
WERKS.Z8621,
WERKS.Z8622,
WERKS.Z8623,
WERKS.Z8624,
WERKS.Z8625,
WERKS.Z8626,
WERKS.Z8627,
WERKS.Z8628,
WERKS.Z8629,
WERKS.Z8630,
WERKS.Z8631,
WERKS.Z8632) */
SELECT
0,
Stati.z4414,
Stati.z4402,
'',
'',
'',
Isnull((select Srtas.z02 from Srtas where Srtas.z00 = Stati.z4400 and Srtas.z01 = Stati.z4414), ''),
Isnull((select Klant.z0102 From Klant where Klant.z0101 = Stati.z4402), ''),
'',
'',
'',
sum (Case when Stati.z4407 = 200609 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 = 200609 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 = 200609 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4411 Else 0 End)
FROM STATI
WHERE
(Stati.z4402 Between '40000' AND 'ZONE6') AND
(Stati.z4414 Between '2005028' AND '2005028') AND
(Stati.z4417 = 'A')
GROUP BY Stati.z4414, Stati.z4402

I get the following error:

Msg 8120, Level 16, State 1, Line 25
Column 'STATI.Z4400' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I know it has something todo with the select on the table SRTAS, but what's the best way to solve this problem without the chance of getting a wrong result.

The SELECT on SRTAS is to get the "description" of STATI.Z4414 who's stored in the table SRTAS.
I only want to group on the fields that will be inserted in WERKS.Z8602, WERKS.Z8603, WERKS.Z8604, WERKS.Z8605, WERKS.Z8606. So adding STATI.Z4400 to the GROUP BY would give me wrong results?

This query is dynamicly generated from within my program from what the user selected.

Also, if there are better ways to write the query, I would be happy to get some hints and tips, but if possible without stored procedures.

Thanks in advance!

If you really want to use that dynamic query, I would use a Subquery to use the column names to group, otherwise it often the case that you use the overview over the statement.

SELECT col1,col2
FROM
(
Your dynamic query here
) Subquery
Group by col1, col2

HTH, Jens K. Suessmeyer.

http://www.sqlserver205.de|||Assuming the value of STAI.Z4400 is always the same for the group do this:
.
.
.
MAX(Isnull((select Srtas.z02 from Srtas where Srtas.z00 = Stati.z4400 and Srtas.z01 = Stati.z4414), '')),
MAX(Isnull((select Klant.z0102 From Klant where Klant.z0101 = Stati.z4402), '')),
.
.
.

That should fix it.

In this case, it would be easier to JOIN the STAI file intead of doing the subquery. Then just do

CASE WHEN Srtas.z02 IS NULL THEN '' ELSE Srtas.z02 END,
CASE WHEN Klant.z0102 IS NULL THEN '' ELSE Klant.z0102 END,

|||Thank you both for your opinion and help on this problem.

Reading your posts gave me some new ideas so I started changing the query like this:

SELECT

0,

Stati.z4414,

Stati.z4402,

'',

'',

'',

Max(Srtas.z02),

Max(Klant.z0102),

'',

'',

'',

sum (Case when Stati.z4407 = 200609 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 = 200609 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 = 200509 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 = 200509 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 = 200609 then Stati.z4411 Else 0 End),

sum (Case when Stati.z4407 = 200509 then Stati.z4411 Else 0 End),

sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4411 Else 0 End),

sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4411 Else 0 End)

FROM STATI

LEFT JOIN KLANT ON STATI.Z4400 = KLANT.Z0100 AND STATI.Z4402 = KLANT.Z0101

LEFT JOIN SRTAS ON STATI.Z4400 = SRTAS.Z00 AND STATI.Z4414 = SRTAS.Z01

WHERE

(Stati.z4402 Between '40000' AND 'ZONE6') AND

(Stati.z4414 Between '2005028' AND '2005028') AND

(Stati.z4417 = 'A')

GROUP BY Stati.z4414, Stati.z4402

This query seems to work.

I have to admit that there's a huge pile of dust ontop of my MSSQL-knowledge. Been a while since I last used it.

So if any of you have any comments on me doing something wrong, I would be pleased to hear it :-)

Problem with role-based security

Hi everyone

I have searched the forums for a solution, but i cannot find one that i can see apllies
thus I post.

I am having trouble restricting the access of one of my roles.
Here is the structure of my cube:

Cube: 'Sales'
Dimension : 'Product'
Hierarchy: 'Code - Company'
Member: '123'

I simply need to restrict the user to this member. I have set up the
security in Dimesion data on : Cube>>Dimension and Dimension...
I only selected
[Product].[Code - Company].&[123]
but when viewing a report using this user it doesn't seem to take effect,.
{All the company codes can still be seen in the company code parameter drop down}

When I try to implement this on the cell data tab, I can't see anything in the dropdown
when running the report in the browser

{Using 'Enable read permissions', 'Enable read
contingent Permissions' or a combonation of both}

I'm stuck as can be! Please help to shed some light on
this for me.

I thank you in advance

Gerhard Davids
(PS: If I have been unclear in anyway point it out to me plz
and I shall rephrase it)

Do you have any other roles that the user is a member of that would provide them access to the other company codes? Roles in AS2005 are cumulative, so if a user has access to a set of dimension members via one role but they are restricted to a subset of the dimension members via another role, they will still be able to see all of the dimension members...

HTH,

Dave Fackler

|||

Hi Dave

Thanks for the response.

No, th user is only part of one role so he cannot be overiden by
another. The user is also not part of the Domain, I dont know if that changes anything.
The user is only added on the local-mashine fo the reporting services.

Cube security work when I use the test cube security via the link in
the cell data tab.

G

|||

Two things to check:

1. Run Profiler for AS and make sure that the connection open from RS indeed authenticates as this user

2. Check whether this user is member of Administrators NT group on the machine - if so security doesn't apply to him by default

3. Check response from DISCOVER_CATALOGS while connected as this user and see the content of ROLES column. If it has * in it - it is a bad sign.

|||

Hi Mosha

Thank you for the responce.

I ran the profiler(first time using profiler..) and connected to my ssas
then connected to the reports server and opened the report etc...

The stack trace that was produced did show the correct user authenticated,
The user is also not part of the NT Admin group.

The trace did not show a DISCOVER_CATALOGS and no roles
column was present, however other DISCOVER_ were shown.
Did I do something incorect for this data to be missing?

The only strange thing that i saw was upon clicking the drop down
for the parameter the MDX querry to populate, the 'querry end' event's
error column contained a '1'.

Gerhard

Note: I am using SSAS 2005

|||Anyone have any ideas as to what may be causing this?|||

Hi Gerhard,

Are you using separate Analysis and Reporting servers? If so, the Report server doesn't connect to SSAS using the ID of the report end-user, unless you're using Kerberos - see this past post in the SQL Server OLAP newsgroup:

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/ad755b009d23f2e2

>>

microsoft.public.sqlserver.olap > AS + RS

...

Sounds like the classic NT 2-hop authentication problem.
NT credentials can only be passed between two machines (i.e. client and then
RS server). If you attempt to transfer them again from the RS server to the
AS server, then you get a blank username (actually an error, depending on
the OS and its settings). This is a well-known limitation of NT -- it is
totally unrelated to RS or AS. If you really need to do this then you have a
few choices:
1) run RS and AS on the same machine
2) implement kerberos
You could also switch to saved connections on the RS machine, but that would
defeat the dynamic security that you have already established.
--
Dave Wickert [MSFT]
dwick...@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)

>>