MS SQL Server 2005 - Linked Servers One Has to Know

A linked server is a fantastic feature provided by Microsoft with Microsoft SQL Server to talk to a remote Microsoft SQL Server. This can be set up using the SQL Enterprise Manager interface or by using the sp_addlinkedserver stored procedure.
You can find the detailed information at
http://msdn2.microsoft.com/en-us/library/ms188279.aspx
http://msdn2.microsoft.com/en-us/library/ms188477.aspx

Now let’s talk about the some of the things that I learned about Linked Servers;
In one of my projects we have many interrelated application running on MS SQL 2005 server. And for various reasons these servers share lot of information among themselves by using [Database Name].[Schema Name].[Object Name]. These application are running fine for last couple of years but since the load contributed by these various application is increasing day-by-day, so we decided to have one more MS SQL Sever and move few databases from present one to new one. But there were hundreds of stored procedures, triggers, views, and function using the database objects as [Database Name].[Schema Name].[Object Name]. So, for us it was a big challenge to separate them without disturbing their coupling.

This is where we thought using linked servers and wherever we are using [Database Name].[Schema Name].[Object Name] we decided to use [Server Name].[Database Name].[Schema Name].[Object Name]

But hang on techies, though it looks simple and straight forward which actually it is, there is a hidden cost which you have to pay if your application is falling in one of the following scenarios

• Using remote server objects in transaction,
• Using remote views which reference multiple tables
• Using remote server objects in cursors
• Using Identity output variables which references remote server objects ,
• And last but not the least if you miss MSDTC settings;
  

Thanks to Microsoft support techies and Google who gave me whole lot of information on handling these issues.
Before you get into debugging any issues, use this DTSPING utility to check whether your both the DB servers are communicating properly.
http://blogs.msdn.com/florinlazar/archive/2005/09/16/469064.aspx

Then probably have a look at this article on How to troubleshoot error 7391 that occurs when you use a linked server in SQL Server
http://support.microsoft.com/kb/306212

Then if you face any errors listed below, I have few pointer to MS KB.

• Server: Msg 7391, Level 16, State 1, Line 1The operation could not be performed because OLE DB provider "%ls" for linked server "%ls" was unable to begin a distributed transaction.
• Distributed transaction aborted by MSDTC.

Resolution
http://support.microsoft.com/kb/306212

• Server: Msg 8180, Level 16, State 1, Line 2 Statement(s) could not be prepared.

Resolution
Many places we use cursors and in sometimes in the cursors we use linked server objects/remote objects in those cases you might get this kind of error.
To resolve this you may follow the methods specified by Microsoft. Or even you can use while loop instead of cursor (Please note that it may not remove your problem in all cases)
http://support.microsoft.com/kb/826860

• Msg 1206, Level 18, State 167, Line 3 The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

You use the SQL Native Client OLE DB provider (SQLNCLI) in SQL Server 2005 to create a linked server. You create a distributed transaction. The distributed transaction contains a query that uses the linked server to retrieve data from a table. When you commit the distributed transaction, you may receive the following error message
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
Sometimes
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

Resolution
http://support.microsoft.com/kb/937517

• Server: Msg 16955, Level 16, State 2, Line 0Could not create an acceptable cursor.
• Server: Msg 16945, Level 16, State 1, Procedure sp_cursoropen, Line 57The cursor was not declared.

Resolution
http://support.microsoft.com/kb/302477

• You have two instances of Microsoft SQL Server 2005 that are named Instance1 and Instance2.
• In Instance1, you have a table that has the following characteristics:
  § The table has a column of the VARCHAR data type.
  § The table has no clustered index.
  § The column of the VARCHAR data type is not indexed.
  § The table has many rows. For example, the table has 100,000 or more rows.
• In Instance2, you perform the following operations:
  § You create a linked server to Instance1.
  § You run an UPDATE statement against the linked server to update the column of the VARCHAR data type in the table in Instance1.
  In this scenario, the update operation may be performed on incorrect rows

Resolution
http://support.microsoft.com/?id=951185

• In your application if you create a linked server that is linked to the local server itself. This configuration is also known as a loopback linked server. Then, you create a stored procedure that contains a statement that resembles the following statement:
  o INSERT EXEC .
  You will receive an error message as
  Msg 3910, Level 16, State 2, Line 1 Transaction context in use by another session.

Resolution
http://support.microsoft.com/?id=947486

• In Microsoft SQL Server 2005, you run a transaction from a remote server by using a linked server. The transaction contains a DML statement that is followed by a data definition language (DDL) statement. For example, the transaction contains an INSERT statement that is followed by a DROP TABLE statement. In this scenario, you receive the following error message when you run the transaction:
  Server: Msg 3970, Level 16, State 2, Line 1 This operation conflicts with another pending operation on this transaction. The operation failed.

Resolution
http://support.microsoft.com/kb/949687

• In some scenarios the errors disappeared by enabling network Distributed Transaction Coordinator access
  This article describes the procedure that you follow to enable network Distributed Transaction Coordinator (DTC) access in Microsoft Windows Server 2003.
  http://support.microsoft.com/kb/817064

Apart from all the above here are few more related articles.

• How to enable network DTC access in Windows Server 2003
• How to configure Microsoft Distributed Transaction Coordinator on a Windows Server 2003 cluster
• Configuring Microsoft Distributed Transaction Coordinator (DTC) to work through a firewall
• Troubleshooting RPC Endpoint Mapper errors using the Windows Server 2003 Support Tools from the product CD
• How to troubleshoot connectivity issues that are caused by RPC client protocol registry entries

Hope this information helps you.
In case if you find any information here is wrong please feel free to write to me Bharat.Mane@gmail.com
Thank You
Bharat Mane

ASP.NET Using MaxLength, One has to know.

One of the basic validations that we do in our day-to-day programming is checking the length of the data. In web application, in ASP.NET we have a textbox control and there is a property called MaxLength. The maxLength property sets or returns the maximum number of characters in a text field. Say for example if we have set the value for the maxlength is 20 and user is trying to enter data which is more than 20 characters then it will not allow to do the same, it works well even if user paste a text which is more than 20 characters.
There is one scenario where it fails, actually I shall not say it fails rather it is not meant for such scenario. The scenario is when we set maxlength to 20 as shown below


And in code behind if we set text property of textbox programmatically as shown belowIn this case the resulting screen would be
This means, in this case it does not restrict maximum number of characters to 20, the reason is, if you see the source of the page (the rendered HTML)
Here the MaxLength property of asp.net control gets converted to maxlength property of HTML and this is meant for client validation and not for server validation. And we are trying to set the value at the server side, which leads to a wrong validation.
Here one has to be aware of this while using MaxLength property.


Another interesting fact about the MaxLength property is, it does not work when you set TextMode="MultiLine"
Say for example we have scenario as shown below
Now if you see source of this page it will be as show below
As we know when we set TextMode="MultiLine", it gets rendered as textarea and surprisingly there is no equivalent attribute/property available for textaera for MaxLength
So in case of TextMode="MultiLine" or when we use HTML textarea we do not have any property which will restrict maxlength. In this case we can write our own javascript function which will check the length on every key press in the textarea. There are many sample available on internet for this.
Few of them are at
http://www.dynamicdrive.com/dynamicindex16/limitinput.htm
http://www.quirksmode.org/dom/maxlength.html

Hope this information helps you.
In case if you find any information here is wrong please feel free to write to me Bharat.Mane@gmail.com
Thank You
Bharat Mane