SQL Server: The instance name must be the same as computer name

Saturday, 11 October 2008
Posted by David Jennaway at 10:11

This is something I’ve posted about on newsgroups, but one of my colleagues encountered it recently, and I think it deserves a blog entry.
The CRM Environment Diagnostics Wizard may throw the error ‘The instance name must be the same as computer name’. The most common cause of this is if the SQL Server has been renamed after SQL Server was installed. The reason is that, at installation time, SQL Server stores the computer name in a system table, sysservers. This information is not updated when the computer is renamed, and the error from the CRM Environment Diagnostics Wizard indicates that the entry in sysservers does not match the current computer name.
You can diagnose and resolve this by using some SQL system stored procedures. One of them lists the data in sysservers, the other 2 allow you to modify the data to reflect the current machine name.
To check if this is the issue, use SQL Management Studio (or Query Analyzer for SQL 2000) to execute the following query:
sp_helpserver
This will return output like the following:
Name,network_name,status,id,collation_name,connect_timeout,query_timeout
ORGNAME,ORIGNAME,rpc,rpc out,use remote collation,0,null,0,0
If the value in the name column does not match the current computer name, then you have to use the following SQL stored procedures to fix the problem. Note that sp_helpserver normally returns one record, but can return more records if you have configured linked servers. If this is the case, it is the row with id=0 that matters.
To change the information you have to first remove the incorrect record, then add the correct one, with the following queries:
sp_dropserver ‘ORIGNAME’ -- where ORIGNAME is the name returned by sp_helpserver
sp_addserver ‘CURRENTNAME’, ‘LOCAL’ – where CURRENTNAME is the current computer name
If you use named instances, refer to them in the form SERVERNAME\INSTANCENAME. It may then be necessary to restart SQL Server after these changes, but I'm not sure of this. It can't harm though if you can.
There is a KB article about this here. This descibes a similar solution, but be warned of a couple of minor issues with the solution - it fails to specify that quotes are required around the parameters to sp_dropserver and sp_addserver, and I have a feeling (though can't provide concrete evidence) that running sp_helpserver is more reliable than select @@servername.

Debugging CRM Plug-ins, Stored Procedures & Custom Workflow Activities

by Danny Varghese 02.24.09

Whether it's developing CRM plug-ins, custom workflow activities, or writing stored procedures against the CRM database, the most useful tool I've used is the Microsoft Visual Studio Debugger. The debugger allows developers to step through the code for the above mentioned scenarios and has saved me hours!

Microsoft has phenomenal documentation on how to setup remote debugging:

http://msdn.microsoft.com/en-us/library/bt727f1t.aspx

The biggest issues I've had trying to setup the debugger has always been with permissions. I would recommend paying especially close attention to this section. The next biggest issue I had was trying to attach the debugger to a running process: http://msdn.microsoft.com/en-us/library/c6wf8e4z.aspx

Once you've setup Visual Studio debugger, you can attach the following processes for the following CRM components:

1. For plug-ins, attach the debugger to the w3wp.exe process.
2. For custom workflow activities, attach the debugger to MSCRMAsyncService.exe
3. For stored procedures, attach the debugger to sqlserver.exe

Once the debugger has been attached, you must set a breakpoint in the code (either the .NET or T-SQL code). After the breakpoint is set, to test, do the following:

1. For plug-ins, login to CRM and execute actions that will trigger the plug-in, such as create/update/assign a record.
2. For custom workflow activities, login to CRM and perform actions that will trigger the workflow. With this, being that it's an asynchronous service, you'll have to wait until that service runs, and then Visual Studio will let you step through the code.
3. For the stored procedure, just execute the stored procedure in Visual Studio and it will go right to the breakpoint.

I hope this post will help CRM developers save time and effort. I use the debugger every time I develop now to test and it's saved me tremendous amount of maintenance time and effort after the code has been deployed. Happy debugging!

SQL Server: The instance name must be the same as computer name

Posted by David Jennaway on Saturday, 11 October 2008

---

This is something I’ve posted about on newsgroups, but one of my colleagues encountered it recently, and I think it deserves a blog entry.


The CRM Environment Diagnostics Wizard may throw the error ‘The instance name must be the same as computer name’. The most common cause of this is if the SQL Server has been renamed after SQL Server was installed. The reason is that, at installation time, SQL Server stores the computer name in a system table, sysservers. This information is not updated when the computer is renamed, and the error from the CRM Environment Diagnostics Wizard indicates that the entry in sysservers does not match the current computer name.


You can diagnose and resolve this by using some SQL system stored procedures. One of them lists the data in sysservers, the other 2 allow you to modify the data to reflect the current machine name.


To check if this is the issue, use SQL Management Studio (or Query Analyzer for SQL 2000) to execute the following query:
sp_helpserver
This will return output like the following:
Name,network_name,status,id,collation_name,connect_timeout,query_timeout
ORGNAME,ORIGNAME,rpc,rpc out,use remote collation,0,null,0,0


If the value in the name column does not match the current computer name, then you have to use the following SQL stored procedures to fix the problem. Note that sp_helpserver normally returns one record, but can return more records if you have configured linked servers. If this is the case, it is the row with id=0 that matters.


To change the information you have to first remove the incorrect record, then add the correct one, with the following queries:
sp_dropserver ‘ORIGNAME’ -- where ORIGNAME is the name returned by sp_helpserver
sp_addserver ‘CURRENTNAME’, ‘LOCAL’ – where CURRENTNAME is the current computer name



If you use named instances, refer to them in the form SERVERNAME\INSTANCENAME. It may then be necessary to restart SQL Server after these changes, but I'm not sure of this. It can't harm though if you can.



There is a KB article about this here. This descibes a similar solution, but be warned of a couple of minor issues with the solution - it fails to specify that quotes are required around the parameters to sp_dropserver and sp_addserver, and I have a feeling (though can't provide concrete evidence) that running sp_helpserver is more reliable than select @@servername.

SQL Timeouts in CRM - Generic SQL Error

David Jennaway - Microsoft Dynamics CRM

---

I often find myself answering forum posts about SQL errors (the most common error is 'Generic SQL Error'). By far the most likely cause of this error is a timeout when accessing SQL server. If this is a case your options are to increase the timeout, or to try and ensure the query does not take so long.

Increase the Timeout
The SQL timeout is controlled by a registry value on each CRM server:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM\OLEDBTimeout

This value does not exist by default, and if it does not exist then the default value will be 30 seconds. To change it, add the registry value (of type DWORD), and put in the value you want (measured in seconds). Then you have to recycle the CrmAppPool application pool for the change to take effect (this is a step most Microsoft documentation omits to mention); do this we IISReset, or less drastically via iisapp.vbs

Reduce the time taken by the query
This may not be so simple, as you may have little control over the query. If the query is run as a result of your code (e.g. through a RetrieveMultiple request), then you may be able to make useful changes. For example, RetrieveMultiple requests on activities are not necessarily processed very efficiently by CRM (the problem is the way that is accesses the activity parties), and I've been able to make significant improvements by using a FetchXml query instead, which gives closer control over the joins used.

Otherwise, the other query optimisation option is to add indexes in SQL Server. This is a massive topic in its own right (I used to deliver 5 day training courses just on this topic), so I'm not going to go into detail here. The general steps are:

1. Identify the SQL query that takes the time - I use CRM tracing for this - http://support.microsoft.com/kb/907490
2. Use the SQL Management Studio and SQL Profiler to identify the query execution plan and to get recommendations about possible indexes

There are 2 important things to take into account:

1. It is unsupported to add SQL indexes to the MSCRM database. My view on this is that, as long as the index does not implement any constraints (i.e. it's not a UNIQUE index) then you will affect the stability of CRM; you may however need to drop the index prior to upgrading CRM.
2. Although adding an index may improve the performance of one query, it can adversely affect other SQL operations - most obviously data updates. There is no easy solution to this, though the SQL Profiler can help you if you capture and analyse a representative sample of SQL operations

Integrating CRM using SQL Integration Services (SSIS)

Darren Liu of the Microsoft Dynamics Team has an alternative approach of integrating with MSCRM using the new SQL Integration Services which replaced the SQL 2000's DTS. If you are good with SQL and a C# programmer this is an interesting approach. Read the article.