locked
Only show Users results RRS feed

  • Question

  • Hi Everyone,

    I'm a newbie in the world of SQL so please bare with me, hopefully this question will make some sence.

    I'm running a SharePoint 2010 setup with Intergrated SQL Reporting Services, which is linked to a SQL Database containing all of my companies Student information (we're a college). I'm tring to create a table which displays a students timetable when they access the intranet. I want to make it automated so that a different set of results are show for every person who logs in.

    The script I'm using is:

    select dbo.tochar(t1.[a_start],'Day') "activity.day",
           dbo.tochar(t1.[a_start],'hh24:mi') "activity.start",
           dbo.tochar(t1.[a_end],'hh24:mi') "activity.end",
           t1.[a_reference] "activity.reference",
           t1.[a_name] "activity.name",
           dbo.tochar(t1.[a_start],'DD-MON-YYYY') "activity.startdate",
           dbo.tochar(t1.[a_end],'DD-MON-YYYY') "activity.enddate"
    from   dbo.capd_activity t1 with (nolock) 
            inner join dbo.capd_moduleactivity t7 with (nolock) on t1.[a_id]=t7.[ma_activity] 
            inner join dbo.capd_module t6 with (nolock) on t7.[ma_activitymodule]=t6.[m_id]
            inner join dbo.capd_activity t8 with (nolock) on t7.[ma_activity]=t8.[a_id]
            inner join dbo.capd_moduleenrolment t4 with (nolock) on t6.[m_id]=t4.[e_module]
            inner join dbo.capd_student t5 with (nolock) on t4.[e_student]=t5.[s_id] 
    where  (t1.[a_start]<=getdate()) 
      and  (t1.[a_end]>=getdate()) 
      and  (t4.[e_status]='L')
      and  t5.[s_studentreference] = ''
    order by dbo.tochar(t1.[a_start],'D'),dbo.tochar(t8.[a_start],'HH24:MI:SS')
    

    If I enter a student ID in to the line "and t5.[s_studentreference] = ''" then it returns the timetable without fail.

    Can anyone help advise me on how I can get this this to prepopulate that line with the users ID. The ID we use is their username without our domain name if that helps.

    Any ideas would be much appreciated.

    Neil

    Friday, May 18, 2012 8:31 AM

Answers

  • I suggest that you change this in your test query to:

       t1.[a_name] "activity.name",

    to

      SYSTEM_USER "activity.name",

    To see that the system thinks is the current user. Since you use SharePoint and Reporting Services, both entirely outside my realm, that the login is through a proxy user, so that SQL Server does not know about the actual student.

    You should also test

     original_login() "activity.name",

    and

      convert(nvarchar(128, context_info()) "activity.name"

    I like to stress that these variations are not intended to resolve the issues, but they are only intended for debugging. My expectation is that the first two queries will return something like DOMAIN\sharepointuser and the last will return NULL. If my assumption is correct, you will need to talk with your IT folks how to get hold of the id of the user who is logged into Sharepoint.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Iric Wen Monday, May 28, 2012 1:41 AM
    Tuesday, May 22, 2012 10:26 AM

All replies

  • If I understand your question correctly, you should change the line

       t5.[s_studentreference] = ''

    To read:

       t5.[s_studentreference] = SYSTEM_USER

    But this presumes that a "student ID" is the same as the student's login name. If it is not, you will need to explain from where you get the ID.

    I note that you have a number of NOLOCK in your query. Please take them out. You should only use NOLOCK if you fully understand the implications - and that is very difficult. With NOLOCK this can happen:

    1) You may read uncommitted data which may not be consistent.
    2) You may fail to read committed data, which can lead to gross errors.
    3) The query may fail with a spurious error message.

    If blocking is a concern, you should first look to tune the query and add indexes to avoid unnecessary scans. There is also the database option READ_COMMITTED_SNAPSHOT which makes blocking for a query like this one a non-issue.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 18, 2012 9:46 AM
  • Hi Erland,

    Thanks for getting back to me. I've added SYSTEM_USER to the script which completes but unfortunately display the username as DOMAIN\User. Is there anyway for me to add the username minus the Domain?

    I shall try removing the Nolock, the script was generated automaticaly from our Student Database system which is in oracle. Thanks again.

    Neil

    Friday, May 18, 2012 9:58 AM
  • YOu can use:

    Replace(SYSTEM_USER,'Your_Domain_Name\','')

    Friday, May 18, 2012 10:03 AM
  • Hi Steven,

    Should I be using that instead of SYSTEM_USER or in addition to?

    Cheers

    Neil

    Friday, May 18, 2012 10:45 AM
  • Hi Steven,

    Should I be using that instead of SYSTEM_USER or in addition to?

    Cheers

    Neil

    instead of = system_user, you use = Replace(SYSTEM_USER,'Your_Domain_Name\','')
    Friday, May 18, 2012 10:48 AM
  • Hi guys,

    Cheers for all your help so far, unfortunately still no joy, when I run the query it comes back as Query Executed Successfully, and creates the right tables but unfortunately no results.

    I wounder if I might be looking at it in the wrong way. Is it possible to create a query or expression that returns the correct username and then have the results referenced in to my student reference line?

    Also thank you Erland for the call on the No Lock, I've removed them all from the query.

    Cheers

    Neil

    Monday, May 21, 2012 2:36 PM
  • In your original post there was just a query, although you mention creating a table.

    Could you post your complete script, so that we know what you are talking about?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, May 21, 2012 10:10 PM
  • Hi Erland,

    To give full explanation. We are trying to display a students timetable via our intranet (SharePoint 2010). The timetable is created by our MIS team as part of their course creation using some software from a company called CAPITA, which uses Oracle. Within the software you can query the timetable to bring back a students individual timetable. The query is produced as T-SQL with the following query:

    select dbo.tochar(t1.[a_start],'Day') "activity.day",
           dbo.tochar(t1.[a_start],'hh24:mi') "activity.start",
           dbo.tochar(t1.[a_end],'hh24:mi') "activity.end",
           t1.[a_reference] "activity.reference",
           t1.[a_name] "activity.name",
           dbo.tochar(t1.[a_start],'DD-MON-YYYY') "activity.startdate",
           dbo.tochar(t1.[a_end],'DD-MON-YYYY') "activity.enddate"
    from   dbo.capd_activity t1 with (nolock) 
            inner join dbo.capd_moduleactivity t7 with (nolock) on t1.[a_id]=t7.[ma_activity] 
            inner join dbo.capd_module t6 with (nolock) on t7.[ma_activitymodule]=t6.[m_id]
            inner join dbo.capd_activity t8 with (nolock) on t7.[ma_activity]=t8.[a_id]
            inner join dbo.capd_moduleenrolment t4 with (nolock) on t6.[m_id]=t4.[e_module]
            inner join dbo.capd_student t5 with (nolock) on t4.[e_student]=t5.[s_id] 
    where  (t1.[a_start]<=getdate()) 
      and  (t1.[a_end]>=getdate()) 
      and  (t4.[e_status]='L')
      and  t5.[s_studentreference] = '30044399' 
    order by dbo.tochar(t1.[a_start],'D'),dbo.tochar(t8.[a_start],'HH24:MI:SS')

    We have SQL Server 2008r2 installed and I setup the reporting services to run as SharePoint intergrated mode. Using the Report Builder 3.0 I've built a table/matrix using fields from the query

    If I run the query exactly as above it always returns that students timetable:

    What I'd like to do is have this line of the query populate the students enrolment number based on who is logged in:

      and  t5.[s_studentreference] = '30044399' 

    But because we are a company with multiple domains and the timetable software from CAPITA only stores the users without the domain I need to populate it as just the username not domain\username.

    Sorry for such a long reply but I thought it best if I laid it all down.

    Cheers again

    Neil

    
    

    Tuesday, May 22, 2012 7:55 AM
  • I suggest that you change this in your test query to:

       t1.[a_name] "activity.name",

    to

      SYSTEM_USER "activity.name",

    To see that the system thinks is the current user. Since you use SharePoint and Reporting Services, both entirely outside my realm, that the login is through a proxy user, so that SQL Server does not know about the actual student.

    You should also test

     original_login() "activity.name",

    and

      convert(nvarchar(128, context_info()) "activity.name"

    I like to stress that these variations are not intended to resolve the issues, but they are only intended for debugging. My expectation is that the first two queries will return something like DOMAIN\sharepointuser and the last will return NULL. If my assumption is correct, you will need to talk with your IT folks how to get hold of the id of the user who is logged into Sharepoint.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Iric Wen Monday, May 28, 2012 1:41 AM
    Tuesday, May 22, 2012 10:26 AM