XmlSerializer is running does not have sufficient permission to access the temp directory

Error might be comes if you are using proxy account credential and try to execute xp_cmdshell procedure with non-sysadmin SQL Server account. You might be get below error that your windows domain user does not have sufficient permission.

“Access to the temp directory is denied. Identity ‘Domain\UserAccount’ under which XmlSerializer is running does not have sufficient permission to access the temp directory. CodeDom will use the user account the process is using to do the compilation, so if the user doesnt have access to system temp directory, you will not be able to compile. Use Path.GetTempPath() API to find out the temp directory location. “

To resolve this error I tried so much googling but didn’t found any thing helpful which tell that what permission to be needed to resolve above error and on which path we have to provide the permission on temp folder.

Than I found Process Monitor tool which is used to monitor running process on system. Click here to download Process Monitor tool.

I ran this utility and track the process activity and found that below path and Modify, Read, Write Permission required on Temp folder.

C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp
Actually process internally used to perform read write operation “IRP_MJ_CREATE” on above Temp folder which gives “ACCESS DENIED”.

How to check CURSOR Status in SQL Server?

We can check the cursor status using bellow scalar function. It will return smallint value

SELECT CURSOR_STATUS('global','cursor_name')

It will return single value and each value have different meaning. Below we can find the meaning of returned value

Return value	Cursor name	Cursor variable
1	The result set of the cursor has at least one row.For insensitive and keyset cursors, the result set has at least one row. For dynamic cursors, the result set can have zero, one, or more rows.	The cursor allocated to this variable is open. For insensitive and keyset cursors, the result set has at least one row. For dynamic cursors, the result set can have zero, one, or more rows.
0	The result set of the cursor is empty.*	The cursor allocated to this variable is open, but the result set is definitely empty.*
-1	The cursor is closed.	The cursor allocated to this variable is closed.
-2	Not applicable.	Can be:No cursor was assigned to this OUTPUT variable by the previously called procedure. A cursor was assigned to this OUTPUT variable by the previously called procedure, but it was in a closed state upon completion of the procedure. Therefore, the cursor is deallocated and not returned to the calling procedure. There is no cursor assigned to a declared cursor variable.
-3	A cursor with the specified name does not exist.	A cursor variable with the specified name does not exist, or if one exists it has not yet had a cursor allocated to it.

Reference URL: http://bit.ly/KDcw3O

Find out the modified objects in SQL Server

Sometime we need to find out who made the change in SQL Server objects if we are using common Login to connect to the SQL server. For that we can use below query which returns the details of Altered, Deleted etc…

DECLARE @object varchar(5000)
SET @object ='objectname'
SELECT E.name,ObjectName, I.*
FROM sys.traces T
CROSS Apply ::fn_trace_gettable(T.path, T.max_files) I
Join sys.trace_events E On I.eventclass = E.trace_event_id
Where T.id = 1 And E.name = 'Object:Altered' and ObjectName like '%'+@object+'%'

Here we need to set the Object name which we need to find out. We can also find out create, delete object using these “Object:Created” and “Object:Deleted” in where clause where we used “Object:Altered”.

Did you ever accidentally close SQL Server Management Studio?

Did you ever accidentally close SQL Server Management Studio? And, in closing SSMS, did you get the prompt that says “Save changes to the following items?” And did you, completely unthinkingly, with a query you had just been working on, hit Cancel?

Try bellow query to get the executed query or procedure

 SELECT dest.text
 FROM sys.dm_exec_query_stats AS deqs 
 CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest 
 WHERE deqs.last_execution_time > '6/3/2011 07:00' 
       AND dest.text LIKE 'xyz%';

You can get back your query. But you have to do a little formatting work, but it was immediately available.

Reference:
http://bit.ly/k9xVBb

Get Distinct from DataTable

Most of the time we require to get Distinct data from DataTable. But there is no method in existing Framework. So, If you want to select distinct data for selected columns you can use bellow code snippets

private DataTable SelectDistinct(DataTable SourceTable, params string[] FieldNames)
{
     object[] lastValues;
     DataTable newTable;
     DataRow[] orderedRows;

     if (FieldNames == null || FieldNames.Length == 0)
          throw new ArgumentNullException("FieldNames");

     lastValues = new object[FieldNames.Length];
     newTable = new DataTable();

     foreach (string fieldName in FieldNames)
          newTable.Columns.Add(fieldName, SourceTable.Columns[fieldName].DataType);

     orderedRows = SourceTable.Select("", string.Join(", ", FieldNames));

     foreach (DataRow row in orderedRows)
     {
          if (!fieldValuesAreEqual(lastValues, row, FieldNames))
          {
               newTable.Rows.Add(createRowClone(row, newTable.NewRow(), FieldNames));

               setLastValues(lastValues, row, FieldNames);
          }
     }

     return newTable;
}

private bool fieldValuesAreEqual(object[] lastValues, DataRow currentRow, string[] fieldNames)
{
     bool areEqual = true;

     for (int i = 0; i < fieldNames.Length; i++)
     {
          if (lastValues[i] == null || !lastValues[i].Equals(currentRow[fieldNames[i]]))
          {
               areEqual = false;
               break;
          }
     }

     return areEqual;
}

private DataRow createRowClone(DataRow sourceRow, DataRow newRow, string[] fieldNames)
{
     foreach (string field in fieldNames)
          newRow[field] = sourceRow[field];

     return newRow;
}

private void setLastValues(object[] lastValues, DataRow sourceRow, string[] fieldNames)
{
     for (int i = 0; i < fieldNames.Length; i++)
          lastValues[i] = sourceRow[fieldNames[i]];
}

How to use? It’s very simple…

You have to just pass the DataTable in SelectDistinct function and array of selected columns that you want to get in new datatable.

string[] arr1 = { "Column1", "Column2", "Column3" }; 

DataTable dt=SelectDistinct(dtExisting,arr1);

Show loader on page load in asp.net

To show loader while page is loading we have to use bellow code snippet. put this code on your master page or page. And overrride the OnLoad and Render methods.

protected override void OnLoad(EventArgs e)
    {
        if (!IsPostBack)
        {
            Response.Buffer = false;
            Response.Write("<div id='divImage'><div style='float: right;'><img src='loader.gif' />Please wait...</div></div>");
            Response.Flush();
        }
        base.OnLoad(e);
    }
    protected override void Render(HtmlTextWriter writer)
    {
        if (!IsPostBack)
        {
            Response.Clear();
            Response.ClearContent();
        }
        base.Render(writer);
    }
   

and to hide loader we uses bellow javascript code snippet. Put this code at the end of page

 
<script language="javascript" type="text/javascript">
        try
          {
                var divLImage =  document.getElementById("divImage")
                if (divLImage != null && typeof(divLImage ) != 'undefined')
                   {
                        divLImage .style.display="none";
                        divLImage .parentNode.removeChild(divLoadingMessage);
                   }
         }
        catch(e)
         {
         }
    
    </script>

css for loader div

#divImage {
     position: absolute;
    padding: 4px;
    top: 0px;
    right: 0px;
    color: gray;
    font-size: 13px;
    font-family: Verdana, Helvetica, Arial, sans-serif;
    width: 150px;
    font-weight: bold;
    height:20px;
}

Whenever you request any page it’ll show loader on that page during load.
That’s it

Show loader during postback with ajax in asp.net

To show loader during post back using ajax. We can do this very easy manner.

First, we have to create css for loader.

#divImage {
    position: absolute;
    padding: 4px;
    top: 0px;
    right: 0px;
    color: gray;
    font-size: 13px;
    font-family: Verdana, Helvetica, Arial, sans-serif;
    width: 150px;
    height:20px;
 }

After this we have to include the scriptmanager on page or masterpage.

<asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>

Then we use bellow javascript function for async postback. We added two events InitializeRequest and EndREquest these events handles the loader to show/hide.

<script type="text/javascript">
             // Get the instance of PageRequestManager.
             var prm = Sys.WebForms.PageRequestManager.getInstance();
             // Add initializeRequest and endRequest
             prm.add_initializeRequest(prm_InitializeRequest);
             prm.add_endRequest(prm_EndRequest);
            
             // Called when async postback begins
             function prm_InitializeRequest(sender, args) {
                 // get the divImage and set it to visible
                 var panelProg = $get('divImage');                
                 panelProg.style.display = '';
             }
 
             // Called when async postback ends
             function prm_EndRequest(sender, args) {
                 // get the divImage and hide it again
                 var panelProg = $get('divImage');                
                 panelProg.style.display = 'none';
             }
    </script>

Now, we define the loader div in UpdatePanel.

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>
            <div id="divImage" style="display: none">
                <div style="float: right;">
                    <img src="loader.gif" height="20px" />Please wait...</div>
            </div>
        </ContentTemplate>
    </asp:UpdatePanel>

Whenever the async post back occurs the loader div will show and hide when request ends.

That’s it…

To generate loader image we can use bellow links
ajaxload.info
preloaders.net

Pass Eval value in javascript function from GridView

In Gridview, some times we need to pass (Eval(“Column”)) value in javascript function on LinkButton or HyperLink. But you can not directly pass eval value in javascript function like this

 
<asp:LinkButton id='lnk' runat='server' OnClientClick='javascript:somefun('<%# Eval("columnname") %>')' />

This will give error. To resolve this problem we need to use bellow syntax..

 
<asp:LinkButton id='lnk' runat='server' OnClientClick='<%#Eval("columnname","javascript:somefun({0});")%>' />

This is the best way to pass eval value in javascript function from GridView’s LinkButton / HyperLink.

enjoy

Reference: http://bit.ly/hSP1LI

Disable Right Click using jQuery

To disable right click on web page you can do this using jQuery.

You can download jQuery Library and include it and use bellow code of snippet.

<script src="jquery.min.js" type="text/javascript"></script>

$(function() {
            $(this).bind("contextmenu", function(e) {
                e.preventDefault();
            });
        }); 

That it

Repair Network using .bat file

In LAN, some times got network error (TCP/IP error 26) while accessing the SQL server. To resolve this error we have to repair our network connection using Right Click on network icon -> Repair.

While using Repair Option sometimes the repair option not work and repair dialog still appear. To resolve that problem I created the .bat script to repair LAN without using Repair Option.

Here is the Repair script

arp -d *

nbtstat -R

ipconfig /flushdns

nbttstat -RR

ipconfig /registerdns

Open notepad and copy/past above script and save it as .bat extention.

Thats it..