Friday, October 26, 2007

SQL Server 2005 sp_helptext change.

In the earlier version of MS SQL Server (Before SQL Server 2005) , the data in the system tables was visible to any user. But always it is good to have some other tools to view meta data.
We were using sps such as to view the meta data/definition/script of the objects.
  • sp_help
  • sp_helptext

But in SQL Server 2005 this will not work to any user unless it is explicitly specified. Here you will receive an error

Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54

If you want it to work like legacy, and does not want to restrict metadata visibility, a new SQL Server 2005 permission can be used. The permission, VIEW DEFINITION, allows a user or role to see the definition of an object or all objects within a particular scope. The below mentioned sql statement can be used to do the same

  • GRANT VIEW ANY DEFINITION TO [user]

But be careful while giving these kind of access to any users. Unless it is required do not GRANT such privileges

For more details please visit
http://www.microsoft.com/technet/technetmag/issues/2006/01/ProtectMetaData/

Bharat Mane

ASP.NET 2.0 Menu Control Problem

ASP.NET 2.0 is bundelled with lots of new features. In older days we use to write lot of user controls now most common ones are coming as built in. Few of them are

  • Master Pages
  • Menu Controls
  • Themes and Skins
  • Login Controls
  • Data Source Controls
  • Sitemap
This has made life of web programmer very easy. But while using them we need to be know about them throughly in order to get best out of it.

We have used all of them listed above. And we have faced minor side effects of them as well.

Here I am gogin to discuss on the Menu Control used in IE (as most of our users are using IE)
Below mentioned problems I have faced in IE 6.0

We have created an ASP.NET 2.0 web application. We have a few drop-down menus on this page. After clicking on the menu item which does a transaction that takes time, if we hover the mouse on other menu, the I.E browser’s progress bar completes immediately. However the transaction is happening in the background and will complete after a few minutes. But the user is in a state of confusion what is happening.For example you clicked on a menu and page is getting loaded then progress bar shows as below

  • But mean time if you move mouse over any menu the progress bar shows as below which indicates that thread aborted or completed. And this happens the moment you take mouse over to any menu it immediately fills the progress bar.

  • But reality is it just fills the progress bar and loding happes as expected. Here there is no problem with the functionality but with the usability.
  • The code responsible for the problem is emitted java script code present in the webresource.axd. Unfortunately we cannot change it. The PopOut_Show is the function causing the issue and it is a part of the call chain that is triggered by the onmouseover event.
    function PopOut_Show(panelId, hideScrollers, data) {
    //Code
    if (parent.tagName.toLowerCase() == "html") {
    document.body.appendChild(childFrame);
    }
    else {
    parent.appendChild(childFrame);
    }
    //Code
    }

In case if anybody has resolved this problem please let me know bharat.mane@gmail.com.

Bharat Mane

Wednesday, October 24, 2007

Moving list Items from a List Box to another and Up or Down in same List Box

In most of the web pages we come accross a requirement of selecting multiple items form an list box. One of the best approach is to provide two list boxes as shown in the figure below



Here user can easily select list items and move them back and forth. If we want improve the usability of your web page then we can provide following options

  1. On DoubleClick of an item move it to the other list box.
  2. On Enter key of an item move it to the other list box.
  3. In some cases selected items may have oders so here we may need move up and down.
  4. An increamental search for a list item can boost the usability to the web page.

Here is a common and simple function to move the list itesm

//Where parameter are
//src - id of Source Listbox Control
//dest - id of Destination Listbox Control
//strMessage - What message need to be displayed when listitem is not selected.
//bFlag - true if all items to be moved and false if only seleted items to be moved




function Move(src,dest,strMessage,bFlag)
{
var opt, o = 0;
src = document.getElementById(src);
dest = document.getElementById(dest);
//Check is list box is empty
if(src.length<1)
{
alert(strMessage + " is empty");
return;
}
//Check are all items to be moved or not
if(bFlag)
{
while (opt = src[0])
{
var obj = new Option( src.options[0].text , src.options[0].value ) ;
dest.options[dest.length] = obj
src[0] = null;
}
return;
}
//Check if only selected items should be moved then whether any listitem is selected
if(src.selectedIndex == -1)
{
alert("Please select item from " + strMessage );
return;
}
//Move all seleced list items
while (opt = src[o++])
{
if (opt.selected)
{
var obj = new Option( src.options[src.selectedIndex].text , src.options[src.selectedIndex].value ) ;
dest.options[dest.length] = obj
src[o-1] = null;
o--;
}
}
}

And here is a function which will handle keypress event




//Where parameter are

//src - id of Source Listbox Control
//dest - id of Destination Listbox Control
//strMessage - What message need to be displayed when listitem is not selected.
function fnHandleKey(evt,src,dest,strMessage)
{

evt = evt window.event;
var keycode = evt.which ? evt.which : evt.keyCode;
if(keycode == 13)
{
Move(src,dest,strMessage,false);
}
}

And here is a function fot moving list items up/down


//Moving Listitems Up/Down

//Parameters
//select - Object as ListBox
//bFlag is true when you want to move down
//bFlag is false when you want to move up
function MoveUpDown(select,strMessage,bFlag)
{
select = document.getElementById(select);
var i=0;
if(bFlag)
{
for(i=select.length-1;i>=0;i--)
{
if(select.options[i].selected == true)
{
var swapOption = new Object();
//check whether selected option is last
if(i!=select.length-1)
{
//Swap the positions of selected option and next option
//Copy all the propertis option to be moved
swapOption.text = select.options[i+1].text;
swapOption.value = select.options[i+1].value;
swapOption.selected = select.options[i+1].selected;
//Replace the properties
for (var property in swapOption)
select.options[i+1][property] = select.options[i][property];
for (var property in swapOption)
select.options[i][property] = swapOption[property];
}
}
}
}
else
{
for(i=0;i<select.length;i++)
{
if(select.options[i].selected == true)
{
//check whether selected option is first
if(i!=0)
{
//Swap the positions of selected option and next option
//Copy all the propertis option to be moved
var swapOption = new Object();
swapOption.text = select.options[i-1].text;
swapOption.value = select.options[i-1].value;
swapOption.selected = select.options[i-1].selected;
//Replace the properties
for (var property in swapOption)
select.options[i-1][property] = select.options[i][property];
for (var property in swapOption)
select.options[i][property] = swapOption[property];
}
}
}
}
}

To provide an incremental search on list box you will have to just add the behaviour to the select html tag or create a style for the select.

This can be achieved by creating one HTC file and the code for the same is as below



<public:component>

<script>
<!--
/*------------------------------------------------
Event Handler Function
---------------------------------------------------*/
function setSelectedIndex()
{
var obj = window.event.srcElement;
if(obj.type.toLowerCase().indexOf('select') == -1)
{
return true;
}
if(window.event.keyCode == 27)
{
obj.selectedString = '';
obj.selectedIndex = -1;
}
else if(window.event.keyCode == 9 window.event.keyCode == 38 window.event.keyCode == 40 window.event.ctrlKey window.event.altKey window.event.type.toLowerCase() == 'blur') // tab, arrow-up, arrow-down, ctrl, or alt keys
{
obj.selectedString = '';
//obj.fireEvent("change");
return true;
}
else
{
if(typeof obj.selectedString == 'undefined') obj.selectedString = '';
if(window.event.keyCode == 8) // backspace
obj.selectedString = (obj.selectedString.length != 0) ? obj.selectedString.substring(0, obj.selectedString.length - 1) : '';
else
obj.selectedString = obj.selectedString + String.fromCharCode(window.event.keyCode);
var newSelectedIndex = -1;
for(var i = 0; i < obj.options.length; i++)
{
if(obj.options[i].text.toLowerCase().indexOf(obj.selectedString.toLowerCase()) == 0)
{
newSelectedIndex = i;
break;
}
}
if(newSelectedIndex != -1)
obj.selectedIndex = newSelectedIndex;
else
{
obj.selectedString = String.fromCharCode(window.event.keyCode);
obj.fireEvent("onchange");
return true;
}
}
obj.fireEvent("onchange");
return true;
}
// -->
</script>
<public:attach event="onkeydown" onevent="setSelectedIndex()"/>
<public:attach event="onblur" onevent="setSelectedIndex()"/>
</public:component>

Now to acctach this behaviour to the select you can use where the above code is palced in "keydown.htc" and the class "selectIncSearch" is specified in the HTML file for the all the html select tags.

.selectIncSearch
{
behavior:url(keydown.htc);
}

If you want to look at the complete sample please click here to download.

In case of any problem or if the code is not functioning properly please feel free to write to me @ bharat.mane@gmail.com

Bharat Mane

Tuesday, October 16, 2007

SQL Object Search

While working with large database where there are hundreds of stored procedures, it is very difficult to do changes in schema. The schema change might cause chages in some or many stored procedures.

In such case if we have simple query that gives all the procedure names that contains the given text.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINESWHERE ROUTINE_DEFINITION LIKE '%give the search string here%' AND ROUTINE_TYPE='PROCEDURE'

The above query will give the list of the stored procedure name (along with the text) that contains the given string.

This can be really handy when working with large database.

Bharat