Thursday, June 05, 2008 8:23 AM
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
var serverName = "MyServer";
var database = "_MSCRM";
var username = "SQLASPLogin";
var password = "letmein";
if (password == "-")
data = "Nothing";
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) + "";
var txtMain = mainWindow.getElementById("txtMain");
//document.getElementById("data").innerHTML = data;|
Monday, June 09, 2008 8:45 PM
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...
Monday, June 16, 2008 10:17 AMModerator
Monday, June 16, 2008 9:14 PMModerator
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,
DynamicsCrmKicks - Community driven Microsoft Dynamics CRM news
Tuesday, June 17, 2008 8:32 AM
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 AMModerator
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,
DynamicsCrmKicks - Community driven Dynamics CRM news
Wednesday, June 18, 2008 8:22 AM
Thanks for the information Marco.
If anyone wants to discuss further, my email is email@example.com
Thanks in advance....
Wednesday, June 18, 2008 11:29 AM
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"+
' 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:ColumnSet xsi:type="q1:ColumnSet">' +
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 SnippetSELECT 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]
Can anyone offer any insights?
Wednesday, June 18, 2008 4:15 PMModerator
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
Hope it help,
Thursday, June 19, 2008 8:55 AM
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!