Thursday, November 19, 2009

How to return data from a table column with white spaces.

Some time data table contains some records with with spaces in starting, if you trying to get all those records of that then you can use bellow mentioned queries.

1) SELECT * FROM table_name WHERE column_name LIKE ' %'

2) SELECT COUNT(*) FROM table_name WHERE PATINDEX(' %', column_name)>0

How to get count of records of any table?

we can get record count using bellow three types:

1) SELECT COUNT(*) FROM table_Name
Output: 306784

1) SELECT COUNT_BIG(*) FROM table_Name
Output: 306784

2) SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_Name') AND indid < 2
Output: rows:306784

3)exec sp_spaceused 'table_Name',true
Output: table_Name 306784 97112 KB 67328 KB 29384 KB 400 KB

Monday, October 26, 2009

sp_spaceused

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

Syntax:

sp_spaceused [[ @objname = ] 'objname' ]
[,[ @updateusage = ] 'updateusage' ]

Arguments:
[ @objname =] 'objname'
Is the qualified or nonqualified name of the table, indexed view, or queue for which space usage information is requested. Quotation marks are required only if a qualified object name is specified. If a fully qualified object name (including a database name) is provided, the database name must be the name of the current database.
If objname is not specified, results are returned for the whole database.
objname is nvarchar(776), with a default of NULL.
[ @updateusage =] 'updateusage'
Indicates DBCC UPDATEUSAGE should be run to update space usage information. When objname is not specified, the statement is run on the whole database; otherwise, the statement is run on objname. Values can be true or false. updateusage is varchar(5), with a default of false.

Xp_cmdshell

Executes a given command string as an operating-system command shell and returns any output as rows of text. Grants nonadministrative users permissions to execute xp_cmdshell.

Note When executing xp_cmdshell with the Microsoft® Windows® 98 operating systems, the return code from xp_cmdshell will not be set to the process exit code of the invoked executable. The return code will always be 0.

Syntax

xp_cmdshell {'command_string'} [, no_output]


Arguments

'command_string'

Is the command string to execute at the operating-system command shell. command_string is varchar(8000) or nvarchar(4000), with no default. command_string cannot contain more than one set of double quotation marks. A single pair of quotation marks is necessary if any spaces are present in the file paths or program names referenced by command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.

no_output

Is an optional parameter executing the given command_string, and does not return any output to the client.

Return Code Values

0 (success) or 1 (failure)

Saturday, October 3, 2009

Comparing SQL Server INTERSECT and EXCEPT operators.

With SQL Server 2005, Microsoft introduced the INTERSECT and EXCEPT operators to further extend what you could already do with the UNION and UNION ALL operators.

INTERSECT - gives you the final result set where values in both of the tables match
EXCEPT - gives you the final result set where data exists in the first dataset and not in the second dataset
The advantage of these commands is that it allows you to get a distinct listing across all of the columns such as the UNION and UNION ALL operators do without having to do a group by or do a comparison of every single column.

Like the UNION and UNION ALL operators the table structures need to be consistent as well as the columns need to have compatible data types.

for more info visit the link INTERSECT and EXCEPT

Wednesday, September 30, 2009

Microsoft Dynamics CRM Interview Questions

Following are the some of the questions which are asked most frequently in the Microsoft CRM interviews.
1. What is a Plug-in?
2. What is a Workflow?
3. What are the differences between Plug-in and a Workflow?
4. What are the differences between Asynchronous Plug-in and a Workflow?
5. When will you use a workflow and when will you use a Plug-in? Give some Real-life scenario.
6. What is an Email-Router?
7. What are the steps to configure an Email router?
8. How the Plug-in and Workflow will behave in case of Off-line client?
9. What is Metadata?
10. What is CRM Discovery Service?
11. What is Sales and Marketing life cycle in MSCRM?
12. What is Queue entity in MSCRM?
13. What is 1:1, 1:N and N:N relationship in Microsoft Dynamics CRM?
14. How a Plug-in is different from a Call-out?
15. What is 'Append' and 'Append To' privilege in MSCRM? Give one example of it?

Ans: 'Append' and 'Append To' priviledges works together. 'Append To' priviledge will allow other entities to get attached with the entity. 'Append' priviledge will allow the entity to attach the records to the entity with 'Append To' privildege.

Let us understand this with simple example:
Let us say that you want to attach a note to a case then note entity should have 'Append' access right and case entity should have 'Append To' access right.

Let us take one more example to understand this. Suppose you have two custom entities called 'TestCustomEntity1' and 'TestCustomEntity2'. You want to attach the 'TestCustomeEntity2' records to 'TestCustomEntity1'records. For this you need to have 'Append' access right on 'TestCustomEntity1' entity and 'Append To' access right on 'TestCustomEntity2'.
Now guess will I be able to attach the records? Answer is "NO" because we need to create a 1:N relationship between 'TestCustomEntity1' and 'TestCustomEntity2'.
Now the user who has above mentioned access right in his security role will only be able to add 'TestCustomEntity2' records to 'TestCustomEntity1'.
16. How to create a Custom Entity record using SDK?
Ans: Using Dynamic Entity.
17. How to join two table using Query Expression?
Ans: Using Linked entity. You should always try to minimize the number of SWS calls that we make in the database. Often during code review it is explored that the number of Microsoft CRM web-service could have been reduced by making use of the Linked-entity concept. So we should always look for the opportunity to minimize the effort.
18. Can we modify the name of Root Business Unit?
Ans: No; We will have to re-install MSCRM.
19. Suppose if I have 20 user license and I have created 20users. What will happen if I create 21st User?
Ans: The 21st User will get created in MSCRM but that user will be in disabled state.
20. What are the maximum number of tabs allowed on a Microsoft Dynamics CRM 4.0 form?
Ans: 8
21. How to enable/disable the form assistant? How to make sure the form assitant is expanded/cllapsed on a form?
Ans: Navigate to Customization >> Open the Entity >> Open Forms and Views >> Open Form >> Select Form Properties >> Open Display Tab >> Check/Uncheck the "Enable the Form Assistant" and "Expanded by Default".

The interviewer will always try to figure-out whether one is comfortable with the basic concepts of Microsoft CRM (MS CRM) or not and after that; questions will be asked from your previous experience (if you have any experience in CRM). Those questions will be something like this:
1. What was you role in the MSCRM implementation project that you have worked on?
You should be honest while giving answer to this question and should give a brief overview of the project and your role. This is very important question because the answer of this question will tigger so many questions. You should highlight the key skills you have; this way you will divert the attention of the interviewer to your key skills and try not to expose the area in which you are less confident.
2. What was the most challenging task you have faced till now?
Here you should give answer that exihibit your positive attiude . e.g. for a techincal consultant it may be something like ... "I was new to the suppport and during this experience i faced challenging issue related to plug-in that impoved my debugging skills. Email-to-case plug-in was really diffcult as we had to take care of so many conditions. I have learnt one thing during my previos assignment and that is 'Never give-up'".
3. What was the size of the implementation? (i.e. the number of user-licenses)


for more information please visit mscrminterviewquestions

Friday, September 25, 2009

How to Enable Development Errors in CRM?

Steps those are required are given in bellow-
CRM by default tends to throw a General error for any error that occurs.
If you need to have a more specific error, enable DevErrors in Web.config. To do it do the following:

Step 1: Goto the CRM Web Server to the Web Installation path.
Step 2: Open the Web.config file
Step 3: Change the value of DevErrors Key to 'On'
Step 4: Save the Web.config file.
Now CRM should show up more specific errors.

How to enable scheduled tracing
1. Set up the trace. When you set up the trace, enter all the required values. However, leave the TraceEnabled registry entry set to 0.

2. Create a registry file that enables the trace. To do this, start Notepad, copy the following information to the document in Notepad, and then save the document as a .reg file:
Windows Registry Editor Version 5.00 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM] “TraceEnabled”=dword:00000001

3. Create a batch file that calls the .reg file. To do this, open a new document in Notepad, and then copy the following lines to the document in Notepad.

Note- In the lines that you copy, replace the placeholder with the path and the file name of the actual .reg file that you created in step 2.
@echo off
regedit /s ""
exit

4. In Control Panel, add a new scheduled task that runs the new batch file that you created in step 3. Schedule the task for the time at which you want the trace to run.

====================================================================================

How to disable scheduled tracing
1. Create a registry file that disables the trace. To do this, start Notepad, copy the following information to the document in Notepad, and then save the document as a .reg file:
Windows Registry Editor Version 5.00 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM] “TraceEnabled”=dword:00000000 "TraceRefresh"=dword:00000003
Note You must change the value of the TraceRefresh registry entry. If you do not change the value of the TraceRefresh registry entry, the trace will not be disabled.

2. Create a batch file that calls the .reg file. To do this, open a new document in Notepad, and then copy the following lines to the document in Notepad.

Note- In the lines that you copy, replace the placeholder with the path and the file name of the actual .reg file that you created in step 2.
@echo off
regedit /s ""
exit

3. In Control Panel, add a new scheduled task that runs the new batch file that you created in step 3. Schedule the task for the time at which you want the trace to be disabled.

for more details please use the bellow link-
Enable trace in CRM

Thursday, September 17, 2009

General keyboard shortcuts

Remember these keys when ur mouse is not working this is very helpful.

General keyboard shortcuts

* CTRL+C (Copy)
* CTRL+X (Cut)
* CTRL+V (Paste)
* CTRL+Z (Undo)
* DELETE (Delete)
* SHIFT+DELETE (Delete the selected item permanently without placing the item in the Recycle Bin)
* CTRL while dragging an item (Copy the selected item)
* CTRL+SHIFT while dragging an item (Create a shortcut to the selected item)
* F2 key (Rename the selected item)
* CTRL+RIGHT ARROW (Move the insertion point to the beginning of the next word)
* CTRL+LEFT ARROW (Move the insertion point to the beginning of the previous word)
* CTRL+DOWN ARROW (Move the insertion point to the beginning of the next paragraph)
* CTRL+UP ARROW (Move the insertion point to the beginning of the previous paragraph)
* CTRL+SHIFT with any of the arrow keys (Highlight a block of text)
* SHIFT with any of the arrow keys (Select more than one item in a window or on the desktop, or select text in a document)
* CTRL+A (Select all)
* F3 key (Search for a file or a folder)
* ALT+ENTER (View the properties for the selected item)
* ALT+F4 (Close the active item, or quit the active program)
* ALT+ENTER (Display the properties of the selected object)
* ALT+SPACEBAR (Open the shortcut menu for the active window)
* CTRL+F4 (Close the active document in programs that enable you to have multiple documents open simultaneously)
* ALT+TAB (Switch between the open items)
* ALT+ESC (Cycle through items in the order that they had been opened)
* F6 key (Cycle through the screen elements in a window or on the desktop)
* F4 key (Display the Address bar list in My Computer or Windows Explorer)
* SHIFT+F10 (Display the shortcut menu for the selected item)
* ALT+SPACEBAR (Display the System menu for the active window)
* CTRL+ESC (Display the Start menu)
* ALT+Underlined letter in a menu name (Display the corresponding menu)
* Underlined letter in a command name on an open menu (Perform the corresponding command)
* F10 key (Activate the menu bar in the active program)
* RIGHT ARROW (Open the next menu to the right, or open a submenu)
* LEFT ARROW (Open the next menu to the left, or close a submenu)
* F5 key (Update the active window)
* BACKSPACE (View the folder one level up in My Computer or Windows Explorer)
* ESC (Cancel the current task)
* SHIFT when you insert a CD-ROM into the CD-ROM drive (Prevent the CD-ROM from automatically playing)
* CTRL+SHIFT+ESC (Open Task Manager)


Dialog box keyboard shortcuts

If you press SHIFT+F8 in extended selection list boxes, you enable extended selection mode. In this mode, you can use an arrow key to move a cursor without changing the selection. You can press CTRL+SPACEBAR or SHIFT+SPACEBAR to adjust the selection. To cancel extended selection mode, press SHIFT+F8 again. Extended selection mode cancels itself when you move the focus to another control.

* CTRL+TAB (Move forward through the tabs)
* CTRL+SHIFT+TAB (Move backward through the tabs)
* TAB (Move forward through the options)
* SHIFT+TAB (Move backward through the options)
* ALT+Underlined letter (Perform the corresponding command or select the corresponding option)
* ENTER (Perform the command for the active option or button)
* SPACEBAR (Select or clear the check box if the active option is a check box)
* Arrow keys (Select a button if the active option is a group of option buttons)
* F1 key (Display Help)
* F4 key (Display the items in the active list)
* BACKSPACE (Open a folder one level up if a folder is selected in the Save As or Open dialog box)


Microsoft natural keyboard shortcuts

* Windows Logo (Display or hide the Start menu)
* Windows Logo+BREAK (Display the System Properties dialog box)
* Windows Logo+D (Display the desktop)
* Windows Logo+M (Minimize all of the windows)
* Windows Logo+SHIFT+M (Restore the minimized windows)
* Windows Logo+E (Open My Computer)
* Windows Logo+F (Search for a file or a folder)
* CTRL+Windows Logo+F (Search for computers)
* Windows Logo+F1 (Display Windows Help)
* Windows Logo+ L (Lock the keyboard)
* Windows Logo+R (Open the Run dialog box)
* Windows Logo+U (Open Utility Manager)


Accessibility keyboard shortcuts

* Right SHIFT for eight seconds (Switch FilterKeys either on or off)
* Left ALT+left SHIFT+PRINT SCREEN (Switch High Contrast either on or off)
* Left ALT+left SHIFT+NUM LOCK (Switch the MouseKeys either on or off)
* SHIFT five times (Switch the StickyKeys either on or off)
* NUM LOCK for five seconds (Switch the ToggleKeys either on or off)
* Windows Logo +U (Open Utility Manager)


Windows Explorer keyboard shortcuts

* END (Display the bottom of the active window)
* HOME (Display the top of the active window)
* NUM LOCK+Asterisk sign (*) (Display all of the subfolders that are under the selected folder)
* NUM LOCK+Plus sign (+) (Display the contents of the selected folder)
* NUM LOCK+Minus sign (-) (Collapse the selected folder)
* LEFT ARROW (Collapse the current selection if it is expanded, or select the parent folder)
* RIGHT ARROW (Display the current selection if it is collapsed, or select the first subfolder)


Shortcut keys for Character Map

After you double-click a character on the grid of characters, you can move through the grid by using the keyboard shortcuts:

* RIGHT ARROW (Move to the right or to the beginning of the next line)
* LEFT ARROW (Move to the left or to the end of the previous line)
* UP ARROW (Move up one row)
* DOWN ARROW (Move down one row)
* PAGE UP (Move up one screen at a time)
* PAGE DOWN (Move down one screen at a time)
* HOME (Move to the beginning of the line)
* END (Move to the end of the line)
* CTRL+HOME (Move to the first character)
* CTRL+END (Move to the last character)
* SPACEBAR (Switch between Enlarged and Normal mode when a character is selected)


Microsoft Management Console (MMC) main window keyboard shortcuts

* CTRL+O (Open a saved console)
* CTRL+N (Open a new console)
* CTRL+S (Save the open console)
* CTRL+M (Add or remove a console item)
* CTRL+W (Open a new window)
* F5 key (Update the content of all console windows)
* ALT+SPACEBAR (Display the MMC window menu)
* ALT+F4 (Close the console)
* ALT+A (Display the Action menu)
* ALT+V (Display the View menu)
* ALT+F (Display the File menu)
* ALT+O (Display the Favorites menu)


MMC console window keyboard shortcuts

* CTRL+P (Print the current page or active pane)
* ALT+Minus sign (-) (Display the window menu for the active console window)
* SHIFT+F10 (Display the Action shortcut menu for the selected item)
* F1 key (Open the Help topic, if any, for the selected item)
* F5 key (Update the content of all console windows)
* CTRL+F10 (Maximize the active console window)
* CTRL+F5 (Restore the active console window)
* ALT+ENTER (Display the Properties dialog box, if any, for the selected item)
* F2 key (Rename the selected item)
* CTRL+F4 (Close the active console window. When a console has only one console window, this shortcut closes the console)


Remote desktop connection navigation

* CTRL+ALT+END (Open the Microsoft Windows NT Security dialog box)
* ALT+PAGE UP (Switch between programs from left to right)
* ALT+PAGE DOWN (Switch between programs from right to left)
* ALT+INSERT (Cycle through the programs in most recently used order)
* ALT+HOME (Display the Start menu)
* CTRL+ALT+BREAK (Switch the client computer between a window and a full screen)
* ALT+DELETE (Display the Windows menu)
* CTRL+ALT+Minus sign (-) (Place a snapshot of the entire client window area on the Terminal server clipboard and provide the same functionality as pressing ALT+PRINT SCREEN on a local computer.)
* CTRL+ALT+Plus sign (+) (Place a snapshot of the active window in the client on the Terminal server clipboard and provide the same functionality as pressing PRINT SCREEN on a local computer.)

Microsoft Internet Explorer navigation

* CTRL+B (Open the Organize Favorites dialog box)
* CTRL+E (Open the Search bar)
* CTRL+F (Start the Find utility)
* CTRL+H (Open the History bar)
* CTRL+I (Open the Favorites bar)
* CTRL+L (Open the Open dialog box)
* CTRL+N (Start another instance of the browser with the same Web address)
* CTRL+O (Open the Open dialog box, the same as CTRL+L)
* CTRL+P (Open the Print dialog box)
* CTRL+R (Update the current Web page)
* CTRL+W (Close the current window)

An Interesting Fact

If
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
is equal to
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

Then,

Hard Work
H+A+R+D+W+O+R+K
8+1+18+4+23+15+18+11 = 98%

Knowledge K+N+O+W+L+E+D+G+E
11+14+15+23+12+5+4+7+5 = 96%

Love
L+O+V+E
12+15+22+5 = 54%

Luck L+U+C+K
12+21+3+11 = 47%

most of us think this is the most important ??? )

Then what makes 100% ?
Is it Money ? .. NO ! ! !
M+O+N+E+Y
13+15+14+5+25 = 72%

Leadership ? .... NO ! ! !
L+E+A+D+E+R+S+H+I+P
12+5+1+4+5+18+19+9+16 = 89%

Every problem has a solution, only if we perhaps change our attitude.

To go to the top,
to that 100% ,
what we really need to go further... a bit more...

ATTITUDE A+T+T+I+T+U+D+E 1+20+20+9+20+21+4+5 = 100%

It is OUR ATTITUDE towards Life and Hard Work that makes OUR Life very close to 100% Success! ! !
ATTITUDE matter...

EventLog Cycle (Provides interaction with Windows event logs.)



Introduction
Following are the important classes in Diagnostics namespace.
System.Diagnostics.EventLog
This component provides functionality to write to event logs, read event log entries, and create and delete event logs and event sources on the network.

Some related classes:
EventLog: Provides interaction with Windows event logs
EventLogEntry: Encapsulates a single record in the event log. This class cannot be inherited.
EventLogEntryCollection: Defines size and enumerators for a collection of EventLogEntry instances
EventLogInstaller: Allows you to install and configure an event log that your application reads from or writes to when running. This class is called by the installation utility, for example, InstallUtil.exe, when installing an event log.
EventLogPermission: Allows control of code access permissions for event logging.
• EventLogPermissionAttribute: Allows declarative permission checks for event logging
EventLogPermissionEntry: Defines the smallest unit of a code access security permission that is set for an EventLog
• EventLogPermissionEntryCollection: Contains a strongly typed collection of EventLogPermissionEntry objects
EventLogTraceListener: Provides a simple listener that directs tracing or debugging output to an EventLog

Examples: follow steps those are required to complete the eventlog process

1)Initialize Log component
private System.Diagnostics.EventLog eventLog1;
this.eventLog1 = new System.Diagnostics.EventLog();
((System.ComponentModel.ISupportInitialize)(this.eventLog1)).BeginInit();

this.ServiceName = "Your Service Name";
((System.ComponentModel.ISupportInitialize)(this.eventLog1)).EndInit();

2)Create a source name and log name, which showing in system event log
if (!System.Diagnostics.EventLog.SourceExists("Log Source Name"))
{
System.Diagnostics.EventLog.CreateEventSource("Log Source Name", "Log Root Name");
}
eventLog1.Source = "Log Source Name";
eventLog1.Log = "Log Root Name";

3)How to write an error in defined source log
catch (Exception ex)
{
eventLog1.WriteEntry("Error :" +
ex.Message + "--" +
ex.StackTrace, System.Diagnostics.EventLogEntryType.Error);
Alert(ex);
}

Tuesday, September 15, 2009

Delay Signing an Assembly

Introduction:
An organization can have a closely guarded key pair that developers do not have access to on a daily basis. The public key is often available, but access to the private key is restricted to only a few individuals. When developing assemblies with strong names, each assembly that references the strong-named target assembly contains the token of the public key used to give the target assembly a strong name. This requires that the public key be available during the development process.

When we talk about the Assembly then the first thing comes into our mind is the security for high level development. Delayed signing is the terminology when we are certifying the assembly which will prevent hi-jacking of that assembly.

Delayed signing refers to a technique of partially signing assemblies while they are in development phase. So, signing an assembly basically certifies that assembly by the manufacturer and prevents tampering and hi-jacking of that assembly. This is achievable by using public key/private key encoding of parts of the assembly. The public key is embedded in the assembly and will be used by third-parties who want to reference the assembly. There are many more benefits to signing an assembly, but the main purpose of delayed signing is to allow a company to protect and control its private key and only use it during the packaging process. A delayed signed assembly can still be used like a signed assembly, you just can't package and ship it.

Steps to certify the Assembly:
Delays sign a .NET app:

sn -k keypair.snk

1)sn -p keypair.snk public.snk
2)Build assembly with:
[assembly: AssemblyDelaySign("false")]
[assembly: AssemblyKeyFile("..\\..\\keypair.snk")]
3)sn -Vr AssemblyName.dll
4)This step is critical and is not mentioned anywhere. Exit and restart every instance of VisualStudio running. Until you do this Visual Studio will not know of the sn -Vr from step 4 and you will get
"COM Interop registration failed. The check of the signature failed for assembly AssemblyName.dll"

Tuesday, June 30, 2009

Date function that determines date range based on weekday

Example select dbo.DAYSEARCH('monday',2,-1,getdate())

CREATE FUNCTION dbo.DAYSEARCH(@day_name VARCHAR(9), @step_count INT, @direction smallint,@dt DATETIME)
RETURNS datetime
AS
BEGIN
/*
Returns a date based upon criteria to find a specific day-of-week
for a specific number of "steps" forward or backward.
For instance, "last Wednesday" or "two Thursdays from today".
@day_name = day of week to find ie. Monday, Tuesday...
@step_count = number of iterations back for a specific day:
--------> "1 Last Monday " = 1
--------> "3 Thursdays from now" = 3
@direction:
--------> -1 if Past
--------> 1 if Future
*/

--declare @day_name VARCHAR(9), @step_count INT, @direction smallint

DECLARE @daysearch datetime
DECLARE @counter smallint
DECLARE @hits smallint
DECLARE @day_name_calc VARCHAR(9)

SELECT @counter = @direction
SELECT @hits = 0

WHILE @hits < @step_count
BEGIN
SELECT @day_name_calc = DATENAME(weekday , DATEADD(d, @counter, @dt))

IF @day_name_calc = @day_name
BEGIN
SELECT @hits = @hits + 1
SELECT @daysearch = DATEADD(d, @counter, @dt)
END

SELECT @counter = (@counter + (1 * @direction))
END
RETURN @daysearch
END

Tuesday, April 28, 2009

JavaScript check uncheck with ASP.Net GridView Control

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CSharp.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Scrollable Grid</title>
<style type ="text/css" >
.header
{
background-color:Green;
}
</style>
<script type = "text/javascript">
function Check_Click(objRef)
{
//Get the Row based on checkbox
var row = objRef.parentNode.parentNode;
if(objRef.checked)
{
//If checked change color to Aqua
row.style.backgroundColor = "aqua";
}
else
{
//If not checked change back to original color
if(row.rowIndex % 2 == 0)
{
//Alternating Row Color
row.style.backgroundColor = "#C2D69B";
}
else
{
row.style.backgroundColor = "white";
}
}

//Get the reference of GridView
var GridView = row.parentNode;

//Get all input elements in Gridview
var inputList = GridView.getElementsByTagName("input");

for (var i=0;i<inputList.length;i++)
{
//The First element is the Header Checkbox
var headerCheckBox = inputList[0];

//Based on all or none checkboxes
//are checked check/uncheck Header Checkbox
var checked = true;
if(inputList[i].type == "checkbox" && inputList[i] != headerCheckBox)
{
if(!inputList[i].checked)
{
checked = false;
break;
}
}
}
headerCheckBox.checked = checked;

}
</script>
<script type = "text/javascript">
function checkAll(objRef)
{
var GridView = objRef.parentNode.parentNode.parentNode;
var inputList = GridView.getElementsByTagName("input");
for (var i=0;i<inputList.length;i++)
{
//Get the Cell To find out ColumnIndex
var row = inputList[i].parentNode.parentNode;
if(inputList[i].type == "checkbox" && objRef != inputList[i])
{
if (objRef.checked)
{
//If the header checkbox is checked
//check all checkboxes
//and highlight all rows
row.style.backgroundColor = "aqua";
inputList[i].checked=true;
}
else
{
//If the header checkbox is checked
//uncheck all checkboxes
//and change rowcolor back to original
if(row.rowIndex % 2 == 0)
{
//Alternating Row Color
row.style.backgroundColor = "#C2D69B";
}
else
{
row.style.backgroundColor = "white";
}
inputList[i].checked=false;
}
}
}
}
</script>
<script type = "text/javascript">
function MouseEvents(objRef, evt)
{
var checkbox = objRef.getElementsByTagName("input")[0];
if (evt.type == "mouseover")
{
objRef.style.backgroundColor = "orange";
}
else
{
if (checkbox.checked)
{
objRef.style.backgroundColor = "aqua";
}
else if(evt.type == "mouseout")
{
if(objRef.rowIndex % 2 == 0)
{
//Alternating Row Color
objRef.style.backgroundColor = "#C2D69B";
}
else
{
objRef.style.backgroundColor = "white";
}
}
}
}
</script>
</head>
<body>
<form id="form1" runat="server">
<asp:GridView ID="GridView1" runat="server" HeaderStyle-CssClass = "header"
AutoGenerateColumns = "false" Font-Names = "Arial" OnRowDataBound = "RowDataBound"
Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B" >
<Columns>
<asp:TemplateField>
<HeaderTemplate>
<asp:CheckBox ID="checkAll" runat="server" onclick = "checkAll(this);" />
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" onclick = "Check_Click(this)" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField ItemStyle-Width="150px" DataField="CustomerID" HeaderText="CustomerID" />
<asp:BoundField ItemStyle-Width="150px" DataField="City" HeaderText="City" />
<asp:BoundField ItemStyle-Width="150px" DataField="Country" HeaderText="Country"/>
<asp:BoundField ItemStyle-Width="150px" DataField="PostalCode" HeaderText= "PostalCode"/>
</Columns>
</asp:GridView>
</form>
</body>
</html>

for more information please visit : http://www.aspsnippets.com/post/2009/03/13/Using-JavaScript-with-ASPNet-GridView-Control.aspx

Thursday, January 15, 2009

How to find all the stored procedures that reference a specific object ?

Usually, people tries to find all the stored procedures that reference a specific object. This object could be any table, view or even any text that is placed in the procedure, In all such cases this post will be really of much help to all of them.

SQL SERVER 2000

Let's say you are searching for 'objectName' in all your stored procedures then all you have to do is :

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM
INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%objectName%'
AND ROUTINE_TYPE='PROCEDURE'

Another way to perform a search is through the system table syscomments:

SELECT OBJECT_NAME(id) FROM syscomments
WHERE [text] LIKE '%objectName%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)

Now, why to use GROUP BY? Well, there is a curious distribution of the procedure text in system tables if the procedure is greater than 8KB. So, the above makes sure that any procedure name is only returned once, even if multiple rows in or syscomments draw a match. But, that begs the question, what happens when the text you are looking for crosses the boundary between rows? Here is a method to create a simple stored procedure that will do this, by placing the search term (in this case, 'objectName') at around character 7997 in the procedure. This will force the procedure to span more than one row in syscomments, and will break the word 'objectName' up across rows.

Run the following query in Query Analyzer, with results to text (CTRL+T):

SET NOCOUNT ON
SELECT 'SELECT '''+REPLICATE('x', 7936)+'objectName' SELECT REPLICATE('x', 500)+''''

This will yield two results. Copy them and inject them here:

CREATE PROCEDURE dbo.x
AS
BEGIN
SET NOCOUNT ON
<<>>
END
GO

Now, try and find this stored procedure in INFORMATION_SCHEMA.ROUTINES or syscomments using the same search filter as above. The former will be useless, since only the first 8000 characters are stored here. The latter will be a little more useful, but initially, will return 0 results because the word 'objectName' is broken up across rows, and does not appear in a way that LIKE can easily find it. So, we will have to take a slightly more aggressive approach to make sure we find this procedure. Your need to do this, by the way, will depend partly on your desire for thoroughness, but more so on the ratio of stored procedures you have that are greater than 8KB. In all the systems that I manage, I don't have more than a handful that approach this size, so this isn't something I reach for very often. Maybe for you it will be more useful. First off, to demonstrate a little better (e.g. by having more than one procedure that exceeds 8KB), let's create a second procedure just like above. Let's call it dbo.y, but this time remove the word 'objectName' from the middle of the SELECT line.

CREATE PROCEDURE dbo.y
AS
BEGIN
SET NOCOUNT ON
<<>>
END
GO

Basically, what we're going to do next is loop through a cursor, for all procedures that exceed 8KB. We can get this list as follows:

SELECT OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
HAVING COUNT(*) > 1

We'll need to create a work table to hold the results as we loop through the procedure, and we'll need to use UPDATETEXT to append each row with the new 8000-or-less chunk of the stored procedure code.

-- create temp table
CREATE TABLE #temp ( Proc_id INT, Proc_Name SYSNAME, Definition NTEXT )
-- get the names of the procedures that meet our criteria
INSERT #temp(Proc_id, Proc_Name)
SELECT id, OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY id, OBJECT_NAME(id)
HAVING COUNT(*) > 1

-- initialize the NTEXT column so there is a pointer
UPDATE #temp SET Definition = ' '
-- declare local variables
DECLARE @txtPval binary(16), @txtPidx INT, @curName SYSNAME, @curtext NVARCHAR(4000)

-- set up a cursor, we need to be sure this is in the correct order
-- from syscomments (which orders the 8KB chunks by colid)

DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT OBJECT_NAME(id), text
FROM syscomments s
INNER JOIN #temp t
ON s.id = t.Proc_id
ORDER BY id, colid OPEN c FETCH NEXT FROM c INTO @curName, @curtext

-- start the loop
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- get the pointer for the current procedure name / colid

SELECT @txtPval = TEXTPTR(Definition)
FROM #temp
WHERE Proc_Name = @curName

-- find out where to append the #temp table's value

SELECT @txtPidx = DATALENGTH(Definition)/2
FROM #temp
WHERE Proc_Name = @curName
-- apply the append of the current 8KB chunk
UPDATETEXT #temp.definition @txtPval @txtPidx 0 @curtext
FETCH NEXT FROM c INTO @curName, @curtext
END
-- check what was produced
SELECT Proc_Name, Definition, DATALENGTH(Definition)/2
FROM #temp
-- check our filter
SELECT Proc_Name, Definition
FROM #temp
WHERE definition LIKE '%objectName%'
-- clean up
DROP TABLE #temp
CLOSE c
DEALLOCATE c

SQL SERVER 2005
In SQL Server 2005 there are new functions like OBJECT_DEFINITION, which returns the whole text of the procedure. Also, there is a new catalog view, sys.sql_modules, which also holds the entire text, and INFORMATION_SCHEMA.ROUTINES has been updated so that the ROUTINE_DEFINITION column also contains the full text of the procedure. So, any of the following queries will work to perform this search in SQL Server 2005:

SELECT Name FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id)
LIKE '%objectName%'

SELECT OBJECT_NAME(object_id) FROM sys.sql_modules
WHERE Definition LIKE '%objectName%'
AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%objectName%'
AND ROUTINE_TYPE = 'PROCEDURE'

Split the String values with a special character in MS Flow to convert this into Array

 Many times we have a requirement to prepare the Mailing address for some of the documents, suppose there are Address Line1, Address Line2, ...