Azure, Installation, SQL, Windows

SQL Server VC++ Installation voes

I’ve installed SQL Server any number of times over any number of versions, but I have never had this problem before, and I am not sure why I got it now. However, since searching the web gave me very little in the way of a direct, working, solution I thought I’d write mine down. I was using Windows Server 2008 R2 Datacenter edition, SP1, patched to May 2012 standard, aka The Windows Server 2008 R2 image available in the Windows Azure Virtual Machine preview and installing SQL Server 2008 R2 Developer edition onto it. Now, I don’t think they are related, but I am not ruling it out that there is an issue in some way with that image. Since I am not doing anything but starting the image and running the installation which I have previous downloaded and un-packed from its ISO on a separately attached data drive.

The error I am getting is this:

The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log or use the command-line sxstrace.exe tool for more detail. (Exception from HRESULT: 0x800736B1).

I tried several times, and often this error would occur during install, but on the fifth (or so) attempt the install was successful and all looked to have installed fine, until I tried opening SQL Server Management Studio (SSMS). And got the exception there instead.

Now following the instructions in the exception text I did two thing, first – check the event log, where I found this:

Activation context generation failed for "C:Program Files (x86)Microsoft SQL Server100ToolsBinnVSShellCommon7IDESsms.exe".Error in manifest or policy file "C:WindowsWinSxSmanifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1.manifest" on line 0. Invalid Xml syntax.

Now looking through the event log I could see that I got this error for a number of other applications and services as well, and that ssms wasn’t alone in this.

Next, I ran sxstrace, ie (from an elevated command prompt):

sxstrace trace –logfile:trace.log

The I tried to start ssms to produce the error, which it did. So then I ran:

sxstrace parse –logfile:trace.log –outfile:trace.txt

(More on the sxstrace tool here).

The trace file, among other things, gave me this (similar) information:

INFO: Parsing Manifest File C:WindowsWinSxSmanifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1.manifest.
    INFO: Manifest Definition Identity is (null).
    ERROR: Line 0: XML Syntax error.
ERROR: Activation Context generation failed.

This file is from the Visual Studio C++ 2005 Service Pack (SP) 1 Redistributable Package. So I proceeded to download and install both the original 2005 Redistributable (x86, x64) and SP1 (x86, x64), hoping that would fix the problem and correct the manifest file. Not so for me.

I still wanted to see if the error could be fixed by “normal” procedures so I ran System File Checker (SFC). It produced the following result:

sfc /scannow

Beginning system scan.  This process will take some time.

Beginning verification phase of system scan.
Verification 100% complete.
Windows Resource Protection found corrupt files but was unable to fix some of th
em.
Details are included in the CBS.Log windirLogsCBSCBS.log. For example
C:WindowsLogsCBSCBS.log

The log file contain this (snipped somewhat for readability):

Manifest hash for component [ml:280{140},l:152{76}]"x86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1" does not match expected value.
Expected:{l:32 b:43e8b1d9f404eb67105ab15282fd01f5bf4cd30f7f0c5d1250d11e9384ae9cc5}
Found:{l:32 b:d47fec989a9ad0351d4effd5984343181925f15919245da2a0609e1c5d68f280}.
Unable to load manifest for component [ml:280{140},l:152{76}]"x86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1"
[SR] Cannot verify component files for Microsoft.VC80.ATL, Version = 8.0.50727.4053, pA = PROCESSOR_ARCHITECTURE_INTEL (0), Culture neutral, VersionScope neutral, PublicKeyToken = {l:8 b:1fc8b3b9a1e18e3b}, Type = [l:10{5}]"win32", TypeName neutral, PublicKey neutral, manifest is damaged (TRUE)

At this point I gave up on any form of allowing installers or the system to fix the problem for me and went at the file myself using Advanced guidelines for diagnosing and fixing servicing corruption. The file is readable (although empty), but I cannot edit it (even if I am an administrator). Only SYSTEM has access to the file. So to be able to edit it I must first take ownership of it and grant ACLs:

>takeown /f C:WindowswinsxsManifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.1833_none_d1c5318643596706.manifest

SUCCESS: The file (or folder): "C:WindowswinsxsManifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.1833_none_d1c5318643596706.manifest" now owned by user "JEHBTS5Administrator".

>icacls C:WindowswinsxsManifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.1833_none_d1c5318643596706.manifest /grant administrators:F
processed file: C:WindowswinsxsManifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.1833_none_d1c5318643596706.manifest
Successfully processed 1 files; Failed processing 0 files

>takeown /f C:WindowswinsxsManifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1.manifest

SUCCESS: The file (or folder): "C:WindowswinsxsManifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1.manifest" now owned by user "JEHBTS5Administrator".

>icacls C:WindowswinsxsManifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1.manifest /grant administrators:F
processed file: C:WindowswinsxsManifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1.manifest
Successfully processed 1 files; Failed processing 0 files

Now I can edit the file. As for the content I simply took it of another machine in which it existed and did not seem to have any issues. The content is this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<!-- Copyright © 1981-2001 Microsoft Corporation -->
<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0">
    <noInheritable/>
    <assemblyIdentity type="win32" name="Microsoft.VC80.ATL" version="8.0.50727.4053" processorArchitecture="x86" publicKeyToken="1fc8b3b9a1e18e3b"/>
    <file name="ATL80.dll" hash="6d7ce37b5753aa3f8b6c2c8170011b000bbed2e9" hashalg="SHA1"/>
</assembly>

After saving the file I am (at least seemingly to this point) rid of the problems.

BizTalk, Maintenance, SQL

Used and unused hosts sql script

This is just a log post of a script I put together. When setting up a plattform we are not always certain what hosts and handlers the customer wants. We usually set it up according to common requirements and best practices. In that case it’s also interesting to see what hosts are indeed used and not used once the solution is deployed, so you know which ones can be removed if requested. This is a sql script for that. Although this information can be found out using the Administration Console these kind of reports are not easy to get out and there is no one view for it. It’s much easier to access the database BizTalkMgmtDb directly for these things.


select h1.Name from adm_Host h1 where h1.Name not in(
select distinct h.Name as Host –, a.Name as Adapter, rp.nvcName as Port
from adm_host h
join adm_receivehandler rh on h.id = rh.HostId
join adm_receivelocation rl on rl.ReceiveHandlerId = rh.Id
join bts_receiveport rp on rp.nID = rl.ReceivePortId
join adm_adapter a on a.id = rh.AdapterId
UNION
select
distinct h.Name as Host –, a.Name as Adapter, sp.nvcName as Port
from adm_Host h
join adm_SendHandler sh on h.Id = sh.HostId
join bts_sendport_transport spt on spt.nSendHandlerID = sh.Id
join bts_sendport sp on sp.nID = spt.nSendPortID
join adm_Adapter a on sh.AdapterId = a.Id
UNION
select
distinct h.Name as Host –, ‘*Orchestration’ as Adapter, o.nvcName as Port
from adm_Host h
join bts_orchestration o on h.Id = o.nAdminHostID
–UNION
–select distinct h.Name as Host, * –, ‘*Tracking’ as Adapter, NULL as Port
–from adm_Host h
–where h.HostTracking = 1
)


HTH
/Johan

BizTalk, SOAP, SQL

Send Failures, NACKS and SOAP Faults

(or why my previous post doesn’t apply to all adapters)

In a previous post I identified how if you had the WCF-BasicHttp adapter on the backend send and the WCF-NetTcp adapter on the frontend receive you need a transformation for the client to interpret the backend Fault as a Fault since there is a SOAP version mismatch.

As was evident by a comment, this does not apply to all adapters, but only to the base WCF adapters. for example it does not work with the WCF-SQL adapter.

Why?

In contrast to the base WCF adapters, which when you enable the Propagate fault message treats a SOAP Fault coming from the recipient service as an acceptable message and returns it to the receive port as a valid SOAP Fault message that has Fault as the root element, the WCF-SQL adapter returns a NACK message (if there is an error raised in SQL) as the message. The NACK message has not got the Fault element as it’s root, but instead has Envelope, and looks like this:

<?xml version="1.0" encoding="utf-8"?>
<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/" SOAP:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
  <SOAP:Body>
    <SOAP:Fault>
      <faultcode>Microsoft BizTalk Server Negative Acknowledgment </faultcode>
      <faultstring>An error occurred while processing the message, refer to the details section for more information </faultstring>
      <faultactor>C:ProjectsSampleLocationsResponseFM_%MessageID%.xml</faultactor>
      <detail>
        <ns0:NACK Type="NACK" xmlns:ns0="http://schema.microsoft.com/BizTalk/2003/NACKMessage.xsd">
          <NAckID>{FFB1A60B-E593-4620-8897-4E9C7030A937}</NAckID>
          <ErrorCode>0xc0c01658</ErrorCode>
          <ErrorCategory>0</ErrorCategory>
          <ErrorDescription>There was a failure executing the send pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLTransmit, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "XML assembler" Send Port: "Failed Message" URI: "C:ProjectsSampleLocationsResponseFM_%MessageID%.xml" Reason: This Assembler cannot retrieve a document specification using this type: "http://Sample#Unknown".  </ErrorDescription>
        </ns0:NACK>
      </detail>
    </SOAP:Fault>
  </SOAP:Body>
</SOAP:Envelope>

Therefore it cannot be mapped by the map presented in that post, although the Fault it contains is a SOAP 1.1 Fault.

Read more about ACK and NACK messages here.

BizTalk, Maintenance, SQL

How-to: Use BizTalkMgmtDb to get referenced assemblies

Sometimes when you are trying to remove an assembly from within the Administration Console you get and error saying that it’s being used. Most often you’ll get information on where it’s being used at the same time, but not always. These queries are for those times. With the help of these queries, and the knowledge gained from working with these tables you could also lookup things like assemblies that aren’t being used or on which ports a particular map or pipeline is used – information that is not easily accesible through the Administration Console.


Get Assemblies referenced by Maps on ReceivePorts

Select    ass.nvcName as Assembly,
itm.Name as Map,
rcv.nvcName as ReceivePort
from bts_receiveport_transform tr
join bt_MapSpec map on tr.uidTransformGUID = map.id
join bts_item itm on map.itemid = itm.id
join bts_assembly ass on map.assemblyid = ass.nID
join bts_receiveport rcv on rcv.nID = tr.nReceivePortID
order by Assembly, Map, ReceivePort

Get Assemblies referenced by Maps on SendPorts

Select    ass.nvcName as Assembly,
itm.Name as Map,
snd.nvcName as SendPort
from bts_sendport_transform tr
join bt_MapSpec map on tr.uidTransformGUID = map.id
join bts_item itm on map.itemid = itm.id
join bts_assembly ass on map.assemblyid = ass.nID
join bts_sendport snd on snd.nID = tr.nSendPortID
order by Assembly, Map, SendPort

Get Assemblies referenced by Pipelines on ReceiveLocations

select    ass.nvcName as Assembly,
pipe.Name as Pipeline,
loc.Name as ReceiveLocation
from adm_receiveLocation loc
join bts_pipeline pipe on pipe.ID = loc.ReceivePipelineId
join bts_assembly ass on ass.nID = nAssemblyID

Get Assemblies referenced by Pipelines on SendPorts

select    ass.nvcName as Assembly,
pipe.Name as Pipeline,
snd.nvcName as SendPort
from bts_sendport snd
join bts_pipeline pipe on pipe.ID = snd.nSendPipelineId
join bts_assembly ass on ass.nID = nAssemblyID

BizTalk, Maintenance, SQL

Clean up the MsgBox

Do you sometimes end up with instances in the admin console that just wont go away? That are pending to be suspended, but doesn’t seem to ever get there. I did just the other day. To solve this I used the SQL way of terminating these messages, which always seems to do the trick. But it’s not without risk. Thats one of the many reason why there is emphasis on not running this against production. As the technet how-to describes this procedure is empty by default, so remember to add the logic by running the msgbox_cleanup_logic.sql script. There is also an issue with this procedure and tracking but there is a kb hotfix available that solves that. Presumably it’s correct from the start in R2 but I haven’t compared the two.


In short the SQL way of doing it involves running the procedure bts_CleanupMsgbox (once it contains logic). This procedure has an optional parameter named @fLeaveActSubs which by default is 1. Running it with the optional parameter set to something other then 1 will cause all subscriptions to be removed. Not something you’d typically want. A special note here is that since SQL checks for <> 0, passing in null will cause it to behave as if you sent in 0, since null compared to anything in SQL is always false. This is an issue because if you do Script As Execute in SQL the code that gets generated will default to you passing in null:

DECLARE @RC int
DECLARE @fLeaveActSubs int
— TODO: Set parameter values here.
EXECUTE @RC = [BizTalkMsgBoxDb].[dbo].[bts_CleanupMsgbox] @fLeaveActSubs

Your ports and orchestrations will still look like their running, and in a way they are, but there are no subscriptions, so you’re likely to get a routing failure. For you to get your subscriptions back you need to unenlist the send ports and then restart them. For orchestrations this isn’t enough, here you have to go the additional length of un-binding them and then re-configure and start them.


The BizTalk Core Engine’s WebLog mentions in this post that it might also be a good idea to run bts_PurgeSubscriptions directly after, but if your SQL Agent and the PurgeSubscriptionsJob is enabled (and it should be) then this will be done as part of that job.