SQL Server Configuration Manager - Error 0x80041010

When opening the SQL Server Configuration manager (for SQL Server 2008 R2 in my case), I kept getting following error:

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]

The solution to resolve this turned out to be quite simple. Just open a command prompt and execute following command:

C:\Program Files\Microsoft SQL Server\100\Shared>mofcomp "C:\Program Files\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Depending on the SQL version installed the path to the file could also be something like C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof

Blog admin of my companies blog

First of all, I would like to thank all of my regular reader and hope you've al enjoyed my blog posts for almost 2 years now. I already announced in one of my previous posts some time ago that I would join the contributors of the biztalk admin blog at http://www.biztalkadminsblogging.com/ .

Today, I'm proud to announce that there is another brand new blog I will be contributing to on BizTalk and Azure related news, problem solvement, etc.... My companies (Cnext) blog (http://blog.cnext.eu) . Well actually, the blog already existed quite some time (even longer than my own blog :) ), but there wasn't any new posts for over 2 years. Most recently I became the new administrator of the Cnext blog and had the task to revive it. After 1 month, 3 completely new posts, and still some blog posts in the making, it is ready to be shared with all of you... see for yourself at the Cnexts revived blog.

The first thing I did is restyle the entire blog to match the current branding of Cnext. And after publishing some new material, I can say that it is ready to be read!

Now go and check it out.. and keep following all of my blog posts.




More info on cnext can be found on http://www.cnext.eu. And as mentioned the blog will be situated at http://blog.cnext.eu... keep an eye out for all new material to be added soon!

Biztalk 2013 Beta released

Microsoft finally released a beta version of the new biztalk, wich can be found here.
The first thing that should be noticed is that Microsoft decided to change the next versions name from Biztalk 2010R2 to Biztalk 2013. This has some very important consequences… It indicates the next version to be a major release, which implies a much longer support from Microsoft.
Microsoft’s support lifecycle policy says that products will have 5+5 years (mainstream+extended) support. However, that applies to major versions. If the product would still be BizTalk Server 2010 R2, it will not get an extended support lifecycle end date.

So in conclusion, having the next Biztalk version being a full release is an important sign to the market, it implies a much longer support lifecycle for the product.


Biztalk 2013 will have following new features:

• Integration with Cloud Services: messages using different relay endpoints hosted on Azure.


• RESTful services: provides adapters to invoke REST endpoints as well as expose BizTalk Server artifacts as a RESTful service.


• Enhanced SharePoint adapter: the need for dependency on SharePoint farms has been removed, while still providing backward compatibility.


• SFTP adapter: added an SFTP adapter that will enable sending and receiving messages from an SFTP server.


• ESB Toolkit integration: ESB Toolkit is now fully integrated with BizTalk Server. Also, the ESB Toolkit configuration experience is vastly simplified to enable a quick setup.


• Dependency tracking: dependencies between artifacts can now be viewed and navigated in Admin console.


• Improvements in dynamic send ports: the ability to set host handler per adapter, instead of always using the default send handler of the adapters.

Exception handling in BizTalk orchestrations explained

When creating BizTalk orchestrations, we also have some building blocks available to do exception handling. In this blog I'll try to give a very brief explanation of the available building blocks for your orchestration. I won't describe everything in detail, because this would lead us to far (and would give one massive blog post).

For exception handling in orchestration there are 3 shapes available:

• Throw Exception
• Scope
• Compensate

I want go in much details for the Scope and Compensate shapes. If you need more detailed description on how Exception handling can be used with these 2 shapes I would suggest reading this blog from Richard Seroter. And if this still doens't answers all of your questions, let me know... than I can devote another post with a more detailed explanation on some aspects of the exception handling in orchestrations.

Throw Exception

Firstly we have the 'Throw Exception'-shape. this one is the simplest of all shapes for exception handling.
Frankly, it does nothing more than it's name suggests... It just throws an Exception, nothing more fancy. To achieve this you'll need to follow these three simple steps:

1. Define a variable with the type set to any Exception class you prefer. In below example I've created an exception variable of type Microsoft.Biztalk.Message.Interop.BTSException




Note: The Report To Analyst property in the screenshot exists on many shapes. The property however has no influence on the behavior of your orchestration. I merely is a property used to integrate withe the Orchestration Designer for Business Analysts (ODBA) Visio plugin. If set to false, that shape will be considered as a low level detail that won't be visible in the Visio Designer.


2. Instantiate the variable and set any values according to the occured error
3. Now use the Throw Exception shape and configure it ro use the Exception object you've created

Scope

Scopes are used for three reasons

1. to configure transaction (long running or atomic)
2. to handle exceptions
3. to trigger compensating logic

For more info on the transaction types, I'll refer to this blog from Sandro Pereira.

A scope shape can have one or more Exception handling blocks and/or one Compensation Block. The Transaction type you've chosen for your scope will define which of these blocks can be added. An atomic scope can only have a compensation block, and a scope configured with the transaction type non can only have an Exception block. While a long running scope can have both blocks.
For an overview of the possibilities, check the below screenshot.

Note that there could be more than one Exception block, but only one Compensation block added to a Scope

Exception handling block

The Exception handling block is used to catch exceptions that occur inside the scope the block belongs to. The Exception Object Type in the properties of the exception block will define which Exceptions types will be handled by this Exception block.

When an exception occurs, the engine will check for the closest exception block that can handle this type of exception. The consideration of which exception block will be called is determined sequentially, just like in .NET. If no suitable exception handler is found, the default exception handles is triggered, and this means that the compensation blocks for all nested scopes will be called, after which the exception is rethrowed and the orchestration will become suspended.

Compensation block

Compensation is a way to correct or undo logical piece of work that have previously committed. An exception can occur in the orchestration afther succesfully executing all logic in the scope. In this case, the process might be in a state where it is logically incorrect. So the action already performed in a transactional scope need to be compensated... this can be achieved using a compensation block
If no compensation block is added, the default compensation will be performed. this means calling all compensation blocks of the nested scopes, starting with the most recently completed ones, just as with the default exception handler.

Compensate

The compensate shape can only be used inside of an exception handler. The explicit use of this compensate shape, allows us to specify the order of compensation execution ourselfs. So this can be used in cases the default order (starting from te most recently completed ones and working its way back) doesn't fit.
1 compensate shape can only trigger 1 compensation block of a transaction. So the order must be defined using multiple compensate shapes, calling the compensation blocks in the desired order.

Remarks

• Keep in mind that the default Exception handler, if triggered, will initiate the compensation by calling all compensation blocks for the nested scopes. Compensation blocks of the nested scopes will be called starting with the most recently completed scope and work its way back.
  
  But whenever you catch an exception using the Exception handler block, then you must explicitly do the compensation.



• If no custom compensation block has been added, the default compensation will be performed. Just like with the exception handler, this will call the compensation blocks of any nested scopes starting with the most recently completed scope.



• A Compensate shape can only be used inside an Exception handling block or another Comensation Block. When calling the compensate of the current scope, the default compensatino handler will be triggered instead of the compensation code defined by the compensation block.

Error on attaching DB

When trying to reattach a database I kept getting following error:

In the SQL log I could find below error. The same error was given when trying to attach the DB with a T-SQL statement.

Msg 5120, Level 16, State 101, Line 3 Unable to open the physical file "E:\Program Files\Microsoft SQL Server\MSSQL10_50.BTSISDEV1\MSSQL\DATA\BizTalkMsgBoxDb.mdf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".

After several attempts on how to solve this I finally found the solution.
Turned out, all I needed to do was run the SQL Server Management Studio as an Administrator.
How can it be that simple, yet has me looking for a solution for over an hour :).

BizTalk: using macros in file names

BizTalk allows you to use several macros for defining a file name in a biztalk send port.

The following table lists the supported macros and describes how the File send handler replaces them.

Macro name	Substitute value
%datetime%	Coordinated Universal Time (UTC) date time in the format YYYY-MM-DDThhmmss (for example, 1997-07-12T103508).
%datetime_bts2000%	UTC date time in the format YYYYMMDDhhmmsss, where sss means seconds and milliseconds (for example, 199707121035234 means 1997/07/12, 10:35:23 and 400 milliseconds).
%datetime.tz%	Local date time plus time zone from GMT in the format YYYY-MM-DDThhmmssTZD, (for example, 1997-07-12T103508+800).
%DestinationParty%	Name of the destination party. The value comes from the message context property BTS.DestinationParty.
%DestinationPartyQualifier%	Qualifier of the destination party. The value comes from the message context property BTS.DestinationPartyQualifier.
%MessageID%	Globally unique identifier (GUID) of the message in BizTalk Server. The value comes directly from the message context property BTS.MessageID.
%SourceFileName%	Name of the file from which the File adapter read the message. The file name includes the extension and excludes the file path, for example, Sample.xml. When substituting this property, the File adapter extracts the file name from the absolute file path stored in the FILE.ReceivedFileName context property. If the context property does not have a value—for example, if a message was received on an adapter other than the File adapter—the macro will not be substituted and will remain in the file name as is (for example, C:\Drop\%SourceFileName%). Note Correct implementation of this macro requires that the output message is the same message as the received message.
%SourceParty%	Name of the source party from which the File adapter received the message. Note Correct implementation of this macro requires that the output message is the same message as the received message.
%SourcePartyQualifier%	Qualifier of the source party from which the File adapter received the message. Note Correct implementation of this macro requires that the output message is the same message as the received message.
%time%	UTC time in the format hhmmss.
%time.tz%	Local time plus time zone from GMT in the format hhmmssTZD (for example, 124525+530).

NOTE

The File send handler does not replace the macros with a value if any of the following are true:

• The corresponding system property is not set.
• The macro is misspelled.
• The value for the macro contains symbols that are not valid in the file name.

Using Macros in SMTP Headers

There are however some restrictions when using the macros in SMTP headers.
See this post for more details on this matter.

Below is a short overview of the use of macros in SMTP headers.

Macro	Description	For use with To	For use with CC	For use with Subject
%MessageID%	Globally unique identifier (GUID) of the message in BizTalk Server. The value comes from the message context property BTS.MessageID.	No	No	Yes
%datetime_bts2000%	UTC date time in the format YYYYMMDDhhmmsss, where sss means seconds and milliseconds (for example, 199707121035234 means 1997/07/12, 10:35:23 and 400 milliseconds).	No	No	Yes
%datetime%	UTC date time in the format YYYY-MM-DDThhmmss (for example, 1997-07-12T103508).	No	No	Yes
%datetime.tz%	Local date time plus time zone from GMT in the format YYYY-MM-DDThhmmssTZD, (for example, 1997-07-12T103508+800).	No	No	Yes
%time%	UTC time in the format hhmmss.	No	No	Yes
%time.tz%	Local time plus time zone from GMT in the format hhmmssTZD (for example, 124525+530).	No	No	Yes
%SourceParty%	Name of the source party from which the File adapter received the message.	No	No	Yes
%SourcePartyQualifier%	Qualifier of the source party from which the File adapter received the message.	No	No	Yes
%DestinationParty%	Name of the destination party. The value comes from the message context property BTS.DestinationParty.	Yes	Yes	Yes
%DestinationPartyQualifier%	Qualifier of the destination party. The value comes from the message context property BTS.DestinationPartyQualifier.	No	No	Yes

Source: http://msdn.microsoft.com/en-us/library/aa578022(v=bts.20).aspx

Optimize your SQL statements

It is always good practise to optimize your SQL query statements for a better performance. Just making sure your query does what it needs to, isn't enough. You should consider the performance impact of your code and try to optimize it for the best performance.

Below is a short list of steps to take when optimizing your query statements.
More details on the steps can be found in this blog.

1. Check if you're missing any required table joins.
   This could result in a Cartesian join.
   
   
2. Check if you're missing any required WHERE clause to prevent retrieving too much data.
   
   
3. Check if statistics are being created & updated automatically.
   
   
4. Check if the statistics are up-to-date.
   The DBCC SHOW_STATISTICS command can be used to view the last updated date-time, total table rows and the number of rows sampled.
   Outdated statistics can be renewed using  the sp_updatestats stored procedure, or By using the FULLSCAN option to update all statistics of a table.
   
   
5. Check for any missing table or Index scans by using the execution plans.
   you can use these DMV's to check for missing indexes: sys.dm_db_missing_index_details, sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups.
   
   
6. Check for RID Lookups, also by using the execution plans.
   These cannot always be eliminated, but by making use of covering indexes, RID Lookups can be reduced.
   
   
7. Check for any sort operator, again by using the execution plans.
   There are 3 options:
   • Modify the underlining tables to create a CLUSTERED index on the required sort columns. It could be worth trying out creating the CLUSTERED index on another column which is not the Primary Key.
   • Create an Indexed view on the underlining tables and sort the view by creating a CLUSTERED Index.
   • Create a NON CLUSTERED Index on the specified columns and Include all other columns which will be returned.
   
   
   
8. Check for excessive index fragmentation.
   The DMV sys.dm_db_index_physical_stats can be used for this matter.
   
   
9. Check table locks.
   To prevent locking problems, follow these guidelines:
   • Keep transactions as short as possible.
   • Review the transaction isolation level, and consider minimizing locking contention, thus increasing concurrency by changing to 'Read Committed using row versioning' or 'Snapshot'.
   • Specify table hints such as READUNCOMMITTED or READPAST on the select statements. Although both of these table hints do increase concurrency, both have disadvantages such as 'dirty reads' when using the READUNCOMMITTED or returning an incomplete data set when using the READPAST and therefore they may not be acceptable to use in all circumstances.

Source: http://www.sqlservercentral.com/articles/Performance+Tuning/70647/