SQL Server – Managing huge number of Server connections

My daily work involves connecting to several number of database servers and this involves me to remember server name or IP, port number, User Id and Passwords of those servers.  Number of servers I connect is more than 20 and due to this huge number I tend to forget the IP address, password etc. I can use the option of remember password, which will remember and list all the required server details with user id and password.

But the problem starts once you need to connect to a particular database server using more than one User Id. Once you use 2nd user id, SQL Server Management Studio fails to remember any of the passwords for that server.

So what is the solution?

SQL Server Management Studio has an option to manage server details, i.e. Registered Servers. By registering the server connection in the Registered Servers component of SQL Server Management Studio you can save the connection information for servers that are accessed frequently.
This option can be found under View menu.

A separate window will appear on the left hand side of the SSMS along with Object explorer. On right-clicking on Local Server Groups, option to register new server or to create new server group will appear. Creating Server group is optional but it will help in managing large number of server connections by logical grouping.

When Server Registration is opted, below window will appear. Opt for remember password option to remember the server connection.

In the above window you will find another tab which can be used to customize the connection further.

  1. Default database can be set here, so that every time you connect to a particular server from the registered server option, it will by default connect to the default database.
  2. Choose custom color for each server connections, so that one can easily differentiate between each connection.

Advertisements

How to download software from MSDN with Windows 7 64bit OS

images Windows7-logo

I had 32 bit version of Windows 7 running in my PC with 4 GB of RAM. To improve my machine performance my organization gave another 4 GB of RAM. I added these new RAM to machine but my machine did not recognize newly added extra RAM. Bing on this issue revealed the fact that Windows 7 or Vista can support only up to 3.6 GB of main memory.

This required me to upgrade my OS to Windows 7 64 bit. Than I had to download few dev softwares form MSDN. As usual logged into MSDN page and tried to download. I could not download any software instead each try I was getting below pop-up message.


VBScript: Microsoft File Transfer Manager
=====================================
There was an error launching File Transfer Manager.
If you are running Windows XP with Service Pack 2 or Windows Server 2003 with Service Pack 1, this installation may have been blocked. If the gold IE Information Bar is Present above, please click the bar and select the option to “Install ActiveX”.
For additional assistance, please visit the web site https://transfer.ds.microsoft.com, or contact your help provider.

So I had no clue why this is appearing. So did a Bing again and found that this issue caused because I was using 64bit version of Internet Explorer. So than I used IE 32 bit version and able to download softwares from MSDN.

Since I have 64 bit version of OS thought of installing all 64 bit version software available. So I downloaded Office 2010 64 bit version and installed. Now the new trouble started. None of your old Office add-ins worked with new Office. My Office communicator could not find any mail client installed and every time I open either Office communicator or Outlook I started getting below message box.

So again did some Bing. But for my bad luck there is no solution available for my problem yet. Answer to the problem is none of my old Office add-ins will work in the 64-bit world. They have to be rewritten and recompiled specifically for 64-bit Office 2010. In some cases, programming controls commonly used in the 32-bit world aren’t even available in 64-bit. And moreover only benefit of 64 bit version of office is ability to handle spread-sheet size larger than 2 GB which in any near future I will not need. So the recommendation is avoid installing 64 bit version of Office 2010.

SQL Server – Select, Update and Select in a Single statement

I came across a scenario where I had to optimize the stored procedure which was lengthy and using multiple locks to do the required operation. Objective of the stored procedure is to allocate slots based on some conditions.   Operations involved:

  1. Select few top rows based on slot status and some other conditions.
  2. Update its status as allocated.
  3. Return allocated slot ids to the user.

Below is the dress downed version of actual lengthy code.

SELECT TOP 3 * INTO #tempslot FROM dbo.ExamSlot IES WITH (NOLOCK)
WHERE IES.slot_status = 0
ORDER BY 1 DESC


UPDATE dbo.ExamSlot
SET slot_status = 1
FROM dbo.ExamSlot ES WITH (ROWLOCK)
INNER JOIN #tempslot TS ON ES.slot_id = TS.slot_id


SELECT ES.slot_id FROM dbo.ExamSlot ES WITH (ROWLOCK)
INNER JOIN #tempslot TS ON ES.slot_id = TS.slot_id

Below is the single statement to achieve above objective.

UPDATE SLT
SET SLT.slot_status =0
OUTPUT INSERTED.slot_id,
FROM (SELECT TOP 3 * FROM dbo.ExamSlot IES WITH (HOLDLOCK)
WHERE IES.slot_status = 1
ORDER BY 1 DESC) AS SLT

Pass JSON object from Javascript code to MVC controller

I made a lot of search to know how to pass JSON object from client code to MVC control. But I could not find any proper example or guide. So finally I made some research and I found that by serializing the JSON object using json.js, it is possible to send serialized data to MVC control from client side.

Let me elaborate these steps:

1st it requires json.js included in your project.

In Step 1 I created an array of literals containing object literals. This is the client code with three fields’ id, name and email which we want to send to server. Step by step explanation on this structure could be found here.

In Step 2 we are serializing this object into a string with help of json library method $.toJSON().

In Step 3 we are sending this serialized data to MVC control action via AJAX post.

Step 1:
function SaveData() {
var dobj = [{ id: 1, name: 'ABCD', email: 'abcd@one.com' },
{ id: 2, name: 'EFGH', email: 'efgh@one.com' },
{ id: 3, name: 'IJKL', email: 'ijkl@one.com' },
{ id: 4, name: 'MNOP', email: 'mnop@one.com'}];

Step 2:
var jlst = $.toJSON(dobj);

Step 3:
$.post("/DistributionList/SaveMyData/", { jsonData: jlst },
function(data, textStatus) {
if (textStatus != "success") {
result = "false";
}
});
}

With this our client side task is complete.

Next is to consume this data at the server side, i.e. at MVC control.

At server control side we have an action which accepts string data. Serialized json object will be routed here. To deserialize this data you require an object of JavaScriptSerializer which is available in System.Web.Script.Serialization library.

Create a model class with excat number of fileds to hold the deserialized data.

public class PersonData
{
public int id { get; set; }
public string name { get; set; }
public string email { get; set; }
}

In the below code we have created an object of List of type Person to hold the multiple person information. With the help of JavaScriptSerializer object we are able to deserialize the JSON object.

public ActionResult SaveMyData(string jsonData)
{
bool result = true;
List<PersonData> personData;
JavaScriptSerializer jss = new JavaScriptSerializer();
personData= jss.Deserialize<List<PersonData>>(jsonData);

// DO YOUR OPERATION ON LIST OBJECT

return Content(result.ToString());
}

Conclusion: Hope this post would be helpful those who are find difficult to pass JSON data from client side to any server side control. If you find anything wrong or better way to do the thing please let me know.

UPDATE: JSONP made easy in ASP.NET AJAX

COMMIT in large distributed database system

In Large distributed database system, simple transaction commit will not ensure the perseverance of ACID properties of RDBMS. In order to achieve this, distributed database system introduces two or three phase commit. Read more about this in following links:

Two phase commit
Three phase commit
Two-phase commit vs Three-phase commit