Sign in
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
> 
Why cant I access a saved view?

Pertanyaan Why cant I access a saved view?

  • Thursday, June 05, 2008 8:23 AM
     
     
    Sign In to Vote
    0

     

    Hello All,

     

    I am currently in the process of developing a Yahoo! widget to interogate and process information from Dynamics directly on the desktop, specifically case management and queue checking. I know exactly what I want to do but I am having problems with SQL.

    I have located the 'FilteredIncident' saved view within the SQL database but I cannot seem to access it. When I apply a SQL select statement to it, I just get a 'null' as though there is no data, although interestingly, no errors? I know there is data because suspecting that I was being an idiot, I built the query within the SQL editor myself and then executed it just to make sure. I can access any table I like with no problems. I suspect I can build a query to mimic the view that will be executed at runtime, but I dont see why I should have to when there is a perfectly good FilteredView available. What's going on?

     

    Can anyone advise?

     

    Code Snippet

    function loadSQL()
    {
    var serverName = "MyServer";
    var database = "_MSCRM";
    var username = "SQLASPLogin";
    var password = "letmein";

    if (password == "-")
    {
    data = "Nothing";
    }
    else
    {
       var oSqlConnection = COM.createObject("ADODB.Connection");
        oSqlConnection.open("Provider=sqloledb;Data Source=" + serverName + ";Initial Catalog="+database+";User Id="+username+";Password="+password+";");
       var oRecordSet = oSqlConnection.Execute("SELECT name FROM FilteredAccount");  
       var data = "";
       while (oRecordSet.eof == false)
       {
            data = data + oRecordSet(1) + "";
            oRecordSet.moveNext();
        }
       oRecordSet.close(); 
       oSqlConnection.close();
       }
      var txtMain = mainWindow.getElementById("txtMain");
     txtMain.setAttribute("data", data);
      //document.getElementById("data").innerHTML = data;|
    }

     

     

    Note : Ignore the COM.createObject and read this as oSqlConnection = new ActiveXObject ("adodb.connection"). This line is specific to Yahoo widgets since the widget engine doesnt support ActiveX
     

All Replies

  • Monday, June 09, 2008 8:45 PM
     
     
    Sign In to Vote
    0

     

    Hi,

     

    a filtered view shows only the data for which the user has rights (not SQL Server access rights; only rights given to the user in the crm).

    in your function you use a user that is not in the crm? if so you wont see any data in the filtered views...

     

    regards

    Andreas

     
  • Monday, June 16, 2008 10:17 AM
    Moderator
     
     
    Sign In to Vote
    0
    http://crm.ittoolbox.com/groups/technical-functional/ms-crm-l/filter-view-depending-on-user-role-2007729

     

    http://www.microsoft.com/dynamics/crm/using/customizing/reporttutorial.mspx

     

    you can create report and use filter view.

     

     

     
  • Monday, June 16, 2008 9:14 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,

     

    Skafever is right, the user who queries the filtered view needs to be a valid CRM User or you will get no data. But, for what I'm seeing you are querying the data from outside the Windows Domain and using SQL Server Authentication. This will not work with the Filtered Views as they need you to use Windows Authentication (Active Directory) in order to know what user is retrieving the data.

     

    There is also another set of views called in the same way but withou "filtered" at the end. This views provide the same data but unfiltered. If your security requirements can accept this, it could be an option.

     

    Hope it helps,

    Marco Amoedo

     

    DynamicsCrmKicks - Community driven Microsoft Dynamics CRM news

     
  • Tuesday, June 17, 2008 8:32 AM
     
     
    Sign In to Vote
    0

    Thank you all very much for your insights. I just assumed that these saved views were the same as any other SQL saved view in that you could access them like any other SQL Server entity. Obviously, there is a layer of programming that I was not aware of.

     

    @Marco, Yes, the (in)-security of your aproach is acceptable. The people accessing the information would all be general admins as we provide hosted service to our clients. The problem would come if I tried to impliment this out in the wild but since it is for internal use, I'm not much worried about it to be honest. Unfortunately, the unfiltered Incident view doesnt have all the nice joins which turn the plain information into something useful. I guess I am stuck with having to make a view of my own but without all the security that CRM layers on top.

     

    Does anyone know if this would be possible?

     
  • Tuesday, June 17, 2008 9:27 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,

     

    The "Incident" and "FilteredIncident" has the same columns and joins to make it easier to retrieve the data, like having the account name instead of just the account id. The unique difference is that the Filtered one applies the security permissions of the Microsoft Dynamics CRM while the first one doesn't.

     

    So, in your case if you query the Incident view (like Select * from Incident) you will get every cases stored on the CRM, even the closed ones (you can filter with where status = value).

     

    This views are not supported as they are not documented on the SDK, but using them is not a big deal.

     

    About the security, you have two options. Using the windows integrated security when connecting to SQL Server and getting data from the Filtered views, or use the not filtered views (same name as filtered view removing "Filtered") and implement your own security to restrict the records returned.

     

    Hope it helps,

     

    Marco Amoedo

    DynamicsCrmKicks - Community driven Dynamics CRM news

     

     
  • Wednesday, June 18, 2008 8:22 AM
     
     
    Sign In to Vote
    0

    Thanks for the information Marco.

     

    The really wierd thing is that I can access the views using javascript in a webpage but when I try to make this happen from inside a Yahoo widget I get into all sorts of problems. For a start, Yahoo doesnt support ActiveX (yep, that's right..annoying). Ok, so I am trying to use an XML query instead (the hard way I know) but I just cannot access CRM with a SOAP statement. I've tried. I just cannot figure out how it is supposed to work. The problem is that I dont fully understand what a SOAP statement is or what it's supposed to do. Once I get some kind of information out of the CRM, I can modify the XML to suit, but at the moment, I just cannot get into the CRM to retrieve information from the client side. Urg!

     

    All I want is a widget that will query the incident view, pull out the relevant information, display it in a grid with URL's and then display the case using edit.aspx when the user clicks on it (yes, I know there are a couple out there, but they are vista gadgets and they dont do exactly what I want anyway). Simple. Apparently not. The frustrating thing is that I could do this in about half a day with ASP, but Javascript is just giving me an ulcer and Yahoo's brand of it is driving me nuts.

     

    If anyone wants to discuss further, my email is sam.jones@syscomm.co.uk

     

    Thanks in advance....

     
  • Wednesday, June 18, 2008 11:29 AM
     
     
    Sign In to Vote
    0

    OK, things have moved on a little bit now. Still related to this question but slightly off topic....

     

    How does one access the views using a SOAP request. I have the following code...

     

     

    Code Snippet

    xmlhttp.send("<?xml version='1.0' encoding='utf-8'?>"+"\n\n"+"<soap:Envelope"+
         ' xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'+
         ' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"'+
         ' xmlns:xsd="http://www.w3.org/2001/XMLSchema">'+
       ' GenerateAuthenticationHeader()' +
         ' <soap:Body>' +
            '<query xmlns:q1="http://schemas.microsoft.com/crm/2006/Query" xsi:type="q1:QueryExpression" xmlns="http://schemas.microsoft.com/crm/2006/WebServices">'+
            '<q1:EntityName>account</q1:EntityName>' +
            '<q1:ColumnSet xsi:type="q1:ColumnSet">' +
            '<q1:Attributes>' +
            '<q1:Attribute>accountid</q1:Attribute>' +
            '<q1:Attribute>name</q1:Attribute>' +
            '</q1:Attributes>' +
            '</q1:ColumnSet>' +
            '<q1:Distinct>false</q1:Distinct>' +
         ' </query>'+
         ' </soap:Body>'+
         ' </soap:Envelope>')

     

     

     

     

    This works fine, but now I want to change this all to work with the 'incident' view in SQL. I understand that this code is accessing the entity called Account but I dont want to do that at all...Alternatively, how do I create a valid SOAP statement that incorporates all the SQL joins necessary between the relevant entities...?

     

    Here is the query (not particularly complicated by SQL standards...)

     

    Code Snippet
    SELECT     AccountIdName AS [Account Name], ContactIdName AS [Contact Name], CreatedByName AS Creator, SubjectIdName AS Subject, IncidentId, Title,
                          AccountId, PriorityCode AS Priority, StatusCode AS Status, New_SLADate AS [SLA breach]
    FROM         dbo.Incident

     

     

     

     

    Can anyone offer any insights?

     

    Sam

     

     
  • Wednesday, June 18, 2008 4:15 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,

     

    I will recommend you to use two free tools from Stunnware to help you get this sorted.

     

    The first one will help you to create the query using FetchXML to retrieve the data from the Incident and related entities. You will need to use FetchXML as with the queries object you won't be able of retrieve data from data fields from more than one entity. http://www.dynamicscrmkicks.com/Development/Stunnware_Tools_Framework_for_CRM_4_0.aspx

     

    The second tool will help you to create the javascript code to use that query and retrieve the data that you need. This tool is capable of generate the JavaScript code from a C# code. Please, check the explanantion on the web. http://www.dynamicscrmkicks.com/Development/Calling_the_CRM_Web_Service_with_JavaScrip.aspx

     

    Hope it help,

     

    Marco

     

    DynamicsCrmKicks

     
  • Thursday, June 19, 2008 8:55 AM
     
     
    Sign In to Vote
    0

    Hi Marco,

     

    Thank you so much for your reply but I have a problem or two....

     

    Both tools look fairly promising. I've had a look at the Query editor and the Framework. Great!

     

    Now, I have a problem with the conversion to Javascript. I downloaded the JavaScript Web Service Calls zip (unzipped it obviously) and found that it is a VB.net application. Unfortunately, I work for a small company and I havnt yet been able to convince them to buy Visual Studio for me (I'm not familiar with VB.net yet - I'm an ASP person trying to migrate) so I am stuck at that point. I can successfully log onto the CRM server, I can build a query (sort of) and get the XML, but not convert the resulting VB script into valid SOAP. Urg! If I provide a valid piece of code, could someone parse it into Javascript/SOAP for me please?

     

    Kind Regards

     

    Sam