Tag: SQL Server

Packt Publishing has recently asked me to review the “Microsoft Dynamics CRM 2011 Reporting” book. I have just finished reviewing it and I found the book to be an excellent source for understanding reporting in Microsoft Dynamics CRM. It covers both the core reporting capabilities as well as the advanced dynamics CRM reporting features.

The book provides clear and valuable information about the basics of reporting, SQL Server, Dynamics CRM Entity Relationships and Database Basics. The book then delves into the Microsoft Dynamics CRM reporting capabilities starting with the simple Dynamics CRM report wizard to create Dynamics CRM reports and then focusing on the more advanced features. It introduces the SQL Report Builder and creating contextual and inline Dynamics CRM reports. It then briefly covers the use of reports and charts in Dynamics CRM dashboards. Finally the book discusses custom reporting and automation and the mobile client.

Overall the book is easy to read and has simple approach to explain complex reporting features in Dynamics CRM. I strongly recommend this book for Dynamics CRM reporting developers and other CRM consultants who want to understand more about the types of reports and capabilities Microsoft Dynamics CRM has.

Normally, you should be able to get the picklist display value and picklist item id in the filtered view of any entity. In some cases, you may need to work with StringMap table directly to get the picklist values for a specific field of a given entity. This could be the case if you are not using the filtered view for some reason or if you want to write a report based on the table or the non-filtered view, the following post is providng the query (queries) needed to do so.

To get the display value of any picklist, you need to use the StringMap table. If you are writing a custom ssrs (SQL Server Reporting Services) report for Dynamics CRM 4, you usually need to return the picklist display value not the picklist item id.

For example: in the opportunity entity, you have a picklist attribute “Rating” (attribute name opportunityratingcode). If you are writing a custom SQL report to add to CRM and you want to display the values of the attribute, you will find that the display value of the picklist is not stored in the filtered view of Opportunity (FilteredOpportunity). To get the display value of any picklist, you need to use the StringMap table.

There many ways to get the information from the StringMap table, I will mention two ways here:

1) using Inner Join (simple - one picklist only)
This way, you just simply join the two tables: your entity filteredview and the StringMap table
/* the query uses out of the box attributes, so should run successfully on all CRM organisations*/
SELECT opp.name, opp.estimatedvalue_base, opp.closeprobability, StringMap.Value
FROM Opportunity as Opp INNER JOIN
StringMap ON Opp.opportunityratingcode = StringMap.AttributeValue
WHERE (StringMap.ObjectTypeCode = 3) AND (StringMap.AttributeName = N'opportunityratingcode')
AND (opp.accountid = 'input your accoung GUID here'

This query will return list of all Opportunities for the specified account display the picklist display value rather than the id.

2) using Temp table (my prefered way for multiple picklists)

This query is longer and uses/declares temp tables but it is the simplest way in case you have several picklists and you want to return the display value of multiple picklists. You just create several temp tables just like @TempSM, where each temp table returns the list of display values for each picklist and then join them together.

and so on. You create temp table for each picklist, join it with opportunity temp table and then carry on.

Note: This is one way of doing this. There are obviously many other shorter ways of doing this. I personally prefer temp tables a lot because although longer, they are simpler and easier to use. They also bypass any issues with null values in the right table of the join.

Not related Note: Always use filtered views when writing custom SQL reports on SSRS for CRM as they comply with CRM security.

Like this:

To get straight to the point. I have been trying to setup Microsoft SQL Server 2008 to allow remote connection and specifically I wanted the SQL server to allow SQL Server Management Studio on a different computer completely outside the network to be able to access this remote SQL Server and manage it fully without having to login on the remote Dedicated standalone SQL server. I also wanted to be able to access this server remotely from visual studio .net on the client machine. Please be aware that I am trying the extreme settings here of getting client (management studio or visual studio) to connect to the host server (SQL Server) where the client is on a completely different network from the host. i.e. This is a 100% Internet only remote connection not within the same network, lan, or even trusted domains, forests, etc..

There are a lot of setup steps that you need to do but remember there are two main parts of this setup:

configure SQL Server to accept remote connection and,

Setup and Configure the firewall running on this server machine to allow inbound incoming connections.

Step 1 is straightforward and there are a lot of MSDN and Technet articles that explain it. But anyway, to make this post informative here are the main steps:

Make sure that the SQL Server Browser service is running under (SQL Server Services).

Go to SQL Server Network Configuration, open the SQL Server instance that you want to use. Make sure that the TCP IP Protocol is enabled.

right click on this TCP/IP protocol node and click properties. Go to IP Addresses tab. Make sure that the server IP Address is: 1) on the list of IPs, 2) is Active, 3) is enabled.

You can either: a) let your SQL server to listen on all server ports for any incoming SQL server connection OR b) you can specify which exact port to listen to. For a) set TCP dynamic Port to “0” and leave the TCP port value blank. To achieve b) set dynamic value to blank and the TCP port to the port number that you want the server to listen to.

Restart the SQL Server instance service from SQL server Services node (All this configuration is still done from within the SQL Server Configuration Manager).

Step 2 is the step I struggled with. Basically, I was unable to connect and didn’t know whether it is a Firewall problem or a problem with my configuration of the SQL server (step 1). Best way to troubleshoot that, allow ALL incoming inbound connections on your firewall. Try to connect remotely to the server (using the SQL Server management studio or Visual Studio .NET), if it works, then your issue was the Firewall. If it doesn’t, then it is an issue with SQL server configuration. PLEASE REMEMBER to disallow incoming inbound connections after you did this test!!!

For step 2, you need to do the following to setup and configure your windows server Firewall to accept and allow incoming connections to the host server on which you got SQL Server. So, Host Server Firewall configuration is as follows:

Allow incoming TCP port number 1433

Allow incoming UDP port number 1433

Allow incoming UDP port number 1434

Allow incoming TCP port number 135

Add the program sqlservr.exe to the exceptions list. By default, sqlservr.exe is installed in C:\Program Files\Microsoft SQL Server\MSSQL10.InstanceName\MSSQL\Binn, where InstanceName is MSSQLSERVER for the default instance, and the instance name for any named instance.

Once I have added all these ports to my exception list, i.e. opening all these ports for incoming traffic allowing access to my server, once I have done that, all my problems were solved and I managed to connect to the SQL server remotely via the Internet (not an internal network) where the client and host have no direct connections what so ever.

One final note: The format to input the server name in the SQL Management Studio connect to server object box is as follows:

<IP>,<port>\InstanceName

for example: 92.37.54.28,2345\InstanceName

If you selected the all ports option. i.e. dynamic port = 0 and TCP Port is blank (as in step 1 above), then the format will be something like that:

server name box should have: 92.37.54.28\InstanceName or just the IP address for the default instance (haven’t tried to setup a default instance though.. I have used named instances for added security.

That’s all for now. Hope this helps. Please write a comment if you have anything to add, correct me or if you have any related question. Please let me know if you need any help or support on this.

Like this:

One of our “big and important” clients has approached us with an issue with their system. The problem is that reports are not accessible from the .NET application and CRM that we have built for them. If you try to open reports from these, you get the error in Internet Explorer saying “Website declined to show this webpage. The website requires you to login”. We were getting this error with everything related to reporting services. So when logged on the reports server machine, those urls: http://localhost/reports , http://localhost/reportserver , http://localhost/reportsmanager all come back with this error or with the error 403 forbidden “Error 403 Forbidden access”.

To rectify this issue, I went through all the usual possible routes such as authentication on IIS, reports server and reports website on IIS. Made sure that all the application and system users have the right access to the reports server. Checked the application pool for the reports server, all these looked find with nothing that has been unchanged. I have also tried to check the reports server configuration manager. Went through all the usual steps to check that it is running and configured properly and again these were all fine with the nice green tick next to each step of the reports server configuration manager. I have also checked services running on the server and the server even viewer. All windows services and SQL reporting server services were running correctly and can be restarted with no issue.

Then, a colleague of mine, tried to open reports from the management studio, and that was it. The error we got in the management studio was much more descriptive and helpful. The SSRS reports server management studio error said:

“The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Report server disabled”

The problem here is because that the Reporting Services encryption key has been invalidated, in other words corrupted. This encryption key is stopping reporting services from returning any reports or even displaying the reports manager, etc..

To fix this issue, and after some research, there were two solutions to solve this issue (again solutions found by my colleague, not me!):

Solution1: In SSRS 2005, there is the encryption section on the left hand side. We need to go to this section and restore the encryption key using a backup encryption key. The encryption key should probably (and hopefully) have been backed up during the setup of the Reporting Services. This encryption key should be regularly backed up and stored in a safe location along with the encryption key password. If you got this backup encryption key, and to solve this error, we need to restore it from the Reporting Services Configuration Manager, encryption section.

If you you don’t have backup of it, then you will have to go for the other longer and more annoying option which is:

Solution2: You have to delete the encrypted content. This will remove the connection strings from all the reporting services data sources. Hence, in this case and after removing the encryption and generating a new one, you need to reset the data sources in Report Manager. Datasources could be shared (linked) or could be a separate data source for each report. You will need and update all these data sources after you removed the encryption.

Luckily! Our good client had the backup of the encryption key, so we only needed to do solution one (Thank God for that!). Well, to be fair, they searched on the server and found a file with extension ” *.snk “ on their server and it was the encryption key backup.

All SSRS encryption keys have file extension .snk.

That’s it.. problem solved. Thanks again to my colleagues work on these solutions.

Like this:

Yesterday I spent a day at a client site working on various requests and issues. The main issue that took the majority of my time was an issue with their Microsoft CRM 4.0 where some account records are not being re-assigned to a different user. Everytime someone tries to re-assign these accounts, a blank error comes up saying: “Microsoft CRM Error. Contact your administrator”.

The first step was to look at the event log. Go to start -> Administrative Tools –> Event Viewer –> Application and look for the error.

After spending sometime looking at the records, running some queries on these records in Microsoft SQL Server, I decided to create a list of :

1- All userh s who have created or own these “corrupted” accounts.

2- All users who have created or own any contact, activity, opportunities or any other records that are related or associated with these CRM accounts such as primary contact, activities and history, etc..

This provided me with a list of users and on going through these users on Active directory, I found out that some of these users are not valid active directory users. They have been simply deleted from AD but left on CRM. Some users have been disabled on CRM.

This is a common issue that I have seen with many clients where they delete a user from Active directory but leave its records un-assigned to a different user.

Now to solve this problem, we needed to do one of the following:

1- Create the deleted user again in Activie Directory and then re-associate their CRM accounts with the new windows login you assigned to the new AD user. Once the new user is created and logged on CRM, you can go and re-assign their records to the required user. Once all records are re-assinged, then you should be able to disable the user in CRM and AD.

2- Delete the records that are associated with the deleted users (if you can’t re-assign them) and then you should be able to re-assign the account records with no problem. In this case, there will be no records associated with the deleted user, so no error should arrise.

If the account records are completely corrupted then you shoud do the following:

re-assing all activies, contacts, opportunities, etc.. which are related to these corrupted accounts to another temp account. Delete the corrupted accounts and re-create them with the same information. You can do this by exporting the data in the corrupted accounts. Delete these corrupted accounts and then import the data back in CRM as new accounts. You can also just create new accounts manually and input all their information to be similar to the corrupted accounts and then delete the corrupted ones.

One last thing, to acheive solution 1. CRM allows you to manually enter the windows login to any CRM user. You may want to create a temp Active Directory domain user to be able to change the windows login of the CRM user.

Let me know if you have any questions or require and further details. Please don’t hesitate to get in touch.

This post is a copy of the entry I posted on NopCommerce Forum. NopCommerce is an excellent FREE .NET shopping basket. I suggest any .NET developer/consultant to have a look at it even before going towards the commerce server route. The company I work for is a Microsoft Gold Partner so they are rather inclined (rightly) to go for the Commerce Server option whenever the client project sounds like an online shop or e-commerce portal. But for any smaller kind of project, I definitely recommend Nop Commerce. At least you have the full source code to play with as you wish and add your bits.

Anyway, I had a nightmare trying to setup NopCommerce on GoDaddy. I did quite a bit of fiddling to get it working.

You will have problems with GoDaddy specifically, as you won’t be able to upload the create data sql file (too big – maximum 2.5MB and the files is 4MB). To overcome this, you will need to split your create data to few sql files. Make sure that you have insert set to “ON” on the first line of every one of these sql files.

Also, the installation wizard could not find my database (on GoDaddy), although it connected to the host correctly. i guess it is trying to find the database on an incorrect level. Anyway, to overcome this, you will have to use the sql files in the scripts folder (in the install folder) to create the tables and the data manually rather than using the script.

mmm, what else… yeah, you will also need to amend the web.config manually. Basically either use the wizard or do everything manually. the wizard didn’t work with Go Daddy though when I tried it as I said earlier.

Despite all that, Nop Commerce is excellent and many thanks for all the effort of the development team. I have to say it works perfectly when I play with it on my Dev machine, it is only when you try to set it up on a shared hosting account.

If you still have problems, get in touch and I’m happy to help.

Share this:

Like this:

We have been recently approached by a client who had a problem with their report server. If you try to access reports from the reports manager, reports run fine and you can view any report fully and in the right format. The problem arrises when a .NET application tries to open these reports, it just opens a new internet explorer window and uses a url to show the report. This was working fine until the client has probably made some changes to their report server. Since then, reports are not displaying in Internet explorer.

To cut a long story short, we eliminated all authentication issues by checking that security and access to all reports is properly configured and the right users has the right to run these reports. We also eliminated problems between SQL Server and the report server, as reports can run smoothly from the reports manager.

We finally came with the concolusion that the error must be related to Internet Explorer not able to render the report properly. We looked through IIS but could not find anything that looks wrong or not the way it should. I have then checked the security setting of Internet explorer (Tool –> Internet Options –> Advanced) and this all seemed fine but I ticked the box that says display all scripting errors. We also did something which I am not sure if it made any difference, which is that we added the server to the trusted sites in Internet Explorer. Then when we tried to open the report again, this time we started to get scripting errors (although they were not showing in the status bar before for some reason). Scripting error dialogs start to show and we finally started to understand what is happening. We got errors such as: “RS Controller is undefined”, “RSControl object reference not set to an object”. We quickly googled this error and we found an easy and simple solution for this (can’t find the original solution at the moment). The solution is to go to the report server itself, open IIS, right click on your reports server (usually under default website), go to directory –>configuration –> search for axd and double click to open its properties. On there uncheck the filter option (can’t remember the option on top of my head and I don’t have access to any IIS 6.0 at the moment to get the exact option wording). Finally click ok.

This has simply solved the problem, and we managed to get all reports back working from the browser and the .NET applicaiton now opens these reports externally with no issues at all.

I have created this post few days after the resolution, and I don’t have access to the client’s servers at the moment so if you have a similar problem and the post doesn’t answer your questions, please feel free to comment here and I promise I will get back to you as soon as I can with more details and may be a screenshot!

Like this:

I am currently working on an Integration solution for one of our clients. The solution is a general integration between two systems. The main thing for me was that I wanted to come up with a list of questions that I need an answer for so that I can start planning and designing the integration solution.

I thought about a list of general questions that most (if not all) consultants working on any integration solutions will need to have complete answers for before starting the design phase, let alone the development phase of the project.

In my opinion, the list of questions are as follows (not in real order – just a braindump!):

How many environments do you have? Development, Test and Live? (recommended) or is the project is still in development so you can use live environment for development? Where will be the test environment later on?

Is this a direct integration or in-direct integration? Is this an instant, event driven integration or a periodic scheduled integration between two systems? Are their queues for data to be migrated?

What backup and restore operations can you do? The ability to Backup and restore data is vital.

What integration application or tool are you going to use or is available? SCRIBE, SQL Server Integration Services, Web Services (Microsoft .NET Web Services), console applications, plugins? What SDK will you need? CRM SDK and CRM API for example?

The Environment structure: How many physical servers? Where are these servers located? Where is the integration tool or application installed?

How and When can I get access to the environment? Access to all servers is required including access to all databases and to all applications. For example: Access to Microsoft Dynamics CRM application (via webclient) is essential to confirm that data imported to a CRM has been migrated successfully.

Where will the extracts be imported? directly using the tool or via an FTP server? Is an SFTP server required?

Are their duplicates? If so where, and what classifies a duplicate?

Are there data entry standards for each application in the overall integrated system?

Are there fields that are required in each system part of this integration?

Are there fields that aren’t used?

Are there any fields with null values?

What relationship does the data has? are there fields which are dependant on others?

What are the primary and forign keys of all tables in each system that will be part of the integrated system? Any field that does not allow null, business required (and preferably business recommended) must have a data upon migration (Defauls can be used then).

Overall high level mapping between the different systems.

What is the value, length, and format of fields/columns in the source system? What is the corresponding value, length and format in the target system?

Are there any Pick Lists? A cross reference is required to map source and target values.

What Data validation is required and is acceptable by the client and the project stakeholders?

Differencing: What are the business rules for differencing? What data does not need to be updated and when? what data is needed to be updated based on the business requirements?

using Default values for all required fields and columns in the target system to avoid causing any errors.

This is the list I have thought of so far. I will keep on updating this list as and when I think of something important that needs to be considered.

Let me know if you have any comments or feedback on these questions and tell me whether or not are these questions helpful.