[BizTalk]Could not store transport type date for Receive location

I got this error message when trying to deploy BTS assembly and to Stop/Start existing applicaiton.

Could not store transport type data for Receive Location ‘Recv.Loc’ to config store. Primary SSO Server ‘Sql-server’ failed. The external credentials in the SSO database are more recent.

This error occurs due to the system date is not in correct date, I did change the system date for some tests, so to correct SSO db, execute the following script against SSO db.

update SSODB..SSOX_ExternalCredentials 
set  ec_timestamp = dateadd(m,-1,ec_timestamp) 
where datediff(m,ec_timestamp,getdate())<>0 

update SSODB..SSOX_ExternalCredentials 
set  ec_timestamp = dateadd(year,-1,ec_timestamp) 
where datediff(year,ec_timestamp,getdate())<>0

[WCF]CommunicationException when invoking Web Service via WCF Client

I added aweb service as a Service Reference in VS2008, while invoking, a CommunicationException was thrown indicate that WCF could not serialize response message due to XmlNameTable legth exceed maxinum allowed.

CommunicationException can be throw in various scenario. In my case, the exception message has indicate that XmlNameTalbe name has exceed the max length.

We can fix this by modifying app.config.

Open the app.config, find the basicHttpBinding section(since this is a regula web service, it has to be basicHttpBinding). Find related attribute and change the value to an acceptable value. In my case the attribute will be [maxNameTableCharCount]

    <bindings>
      <basicHttpBinding>
        <binding name="WebService_SIPSoap" closeTimeout="00:01:00" openTimeout="00:01:00"
          receiveTimeout="00:10:00" sendTimeout="00:01:00" allowCookies="false"
          bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
          maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"
          messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
          useDefaultWebProxy="true">
          <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
            maxBytesPerRead="4096" maxNameTableCharCount="2147483647" />
          <security mode="None">
            <transport clientCredentialType="None" proxyCredentialType="None"
              realm="" />
            <message clientCredentialType="UserName" algorithmSuite="Default" />
          </security>
        </binding>
      </basicHttpBinding>
    </bindings>

[WCF][VS2008] How to add web reference in VS2008

Right click on [Server Reference] then [Add Service Reference…]

On the Popup dialog, Click on [Advenced…]

Click on [Add Web Reference…]

Now you can add Web Reference here

[SQL] Could not open a connection to SQL Server Error 53 while setting up Linked Server on SQL 2005

When trying to setup Linked Server on SQL 2005, I got [Name Pipe Provider: Could not open a connection to SQL Server [53]] error message.

Error 53 is basically a OS level error which maps to ERROR_BAD_NETPATH (network path not found). Which usually means that the SQL process have no access to remote server you are trying to connect. So here are some thing you may want to check first.

1. Check if your SQL service account has network access.
2. Check if your SQL server is able to resolve the remote server name. If not, add a record to host file, or add an alias for the remote server in SQL configuration Manager.
3. Force using TCP/IP procotol instead of named pipes by prefixing the server name with “tcp:” or by setting protocol order via SQL Configuration Manager

2009/4/29 - There could be an error occur while adding a 32 bit SQL Server to a 64 bit SQL Server as a linked server, in that case, please refer to this kb article.

[VPC] Disable host time sync

I happen to have a PDC (Windows Server 2003 R2) inside a VM running on Virtual Server 2005. For testing purpose, we have to forward it’s time clock to 1 month after. To fully disable timesync feature on the VM. We have to  disable time sync feature of the OS and of the Virtual Server.

To disable time sync of Virtual Server 2005, open the Virtual Server 2005 management console, there’s a checkbox on the [Virtual Machine Additionals] tab, simply shutdown the OS, check this option and restart the VM.

To disable time sync of a PDC, which is more cpmplicate since a root PDC is also a time server for it’s AD. Follow the instruction to configure time service on the PDC.

In additional, to disable host time sync in VPC 2007, open the .vmc file via notepad, it’s a xml formatted document, add the following elements to the file or modify existing element.

<integration> 
    <microsoft> 
        <mouse> 
            <allow type="boolean">true</allow> 
        </mouse> 
        <components> 
            <host_time_sync> 
                <enabled type="boolean">false</enabled> <!—- add here… --> 
            </host_time_sync> 
        </components> 
<!-- other settings -->
    </microsoft> 
</integration>

[SQL] FOR XML Clause and ExecuteScalar()

When reterive data via ExecuteScalar() against a SQL statement with FOR XML Clause, you may find that return value is truncate at 2033 characters.

SQL Server 2005 seperates xml result into multi rows of 2033 characters each when FOR XML clause creates xml data greater than 2033 characters.

To avoid this behavior from impact you code, use other Execute() method instead and concate each row in code, or, in your stored procedure declare a variable in xml type, select this variable instead of select directly.

Declare @xml xml
set @xml = (Select …… for Xml raw,Elements)
select @xml