Enable Agent XP in SQL Server

By default Agent XP is disabled in the Sql Server. It is required to create different jobs on the Sql Server. Any attempt to access it might result in the following error:

Agent XP can be enabled by the following 2 ways:

1) SQL Server Configuration

To enable it rt click the SQL Server and follow these steps

2)SQL Query


****************************************************************
--Query:
****************************************************************
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
****************************************************************
--http://technet.microsoft.com/en-us/library/ms178127.aspx
****************************************************************



Now you can see in the following image, that Agent xp has been enabled.

Modify the design of the table that requires the table to be re-created.

Usually, by default, Sql Server doesn’t allow you to modify the definition of a table which requires the table to be re-created. It gives the following error (say for table empMasterCopy):

It means that you have to manually drop & create the table. However, that’s merely a validation put up by the Sql Server as it can be done without manually dropping & creating the table. See the steps below.

Go to Tools -> Options -> Designers
Uncheck the option highlighted in the image below.

Now the Sql Server will re-create the table for you whenever it’s required.

Convert SQL Time to get only Hour, Min & Sec

I want to convert a Column value whose type is Time to get only the time in the format HH:MM:SS. By default, for, 19:10:00, it was returning, 19:10:00 0000000. Which was obviously not required. So in the SQL query I converted the Time Field to MilitaryTime

SELECT CONVERT(VARCHAR(8), ColumnNameWhoseTypeIsTime, 108) AS MilitaryTime FROM TableName

Happy Coding …

Execute DataBase Query for Inserted, Modified, & Deleted Rows only with the help of DataSet

Introduction

Here, what I am trying to do is that there will be a textbox in which the User will enter a unique CustomerId and then when he’ll click the Add button, the value should be displayed in a GridView (The GridView will be displaying the existing Customers for that User which, will be loaded from the database).

Similarly, if he wishes, he can add multiple Customers which will be displayed in the GridView.

 

And, he can edit the existing Customer Details also.

Finally, when he’ll click the update button the database query should be executed for only the Added, Modified, & Deleted rows.

Solution

When the Page is loaded for the very first time I will get the Customers details for the current User in a DataSet (which will be the source of the GridView) which I have written the DataSet in the ViewState of that page.


ViewState["customertData"] = ds;

Now whenever user is adding a new Customer by giving its Customer Id I will do the following


DataSet ds = new DataSet();
DataTable dt;

if (ViewState["customertData"] == null)  //If the ViewState is not defined
{
	dt = new DataTable();
	DataColumn uniqueID = new DataColumn("uniqueID");
	DataColumn customerId = new DataColumn("customerId");
	DataColumn excluded = new DataColumn("IsExcluded");
	dt.Columns.Add(customerId);
	dt.Columns.Add(excluded);
	ds.Tables.Add(dt);
}
else
{
	ds = ViewState["customertData"] as DataSet;
	dt = ds.Tables[0];
}

DataRow dRow = dt.NewRow();
dRow["uniqueID"] = System.Guid.NewGuid();
dRow["customerId"] = txtcustomerId.Text;
dRow["IsExcluded"] = false;

dt.Rows.Add(dRow);
grdDsClient.DataSource = ds;
grdDsClient.DataBind();

//store the current changes in the ViewState
ViewState["customertData"] = ds;
txtClientNo.Text = String.Empty;

Similarly In the RowUpdating & RowDeleting event of the gridView, I am modyfying the DataSet value with the updated records & accordingly writing it into the ViewState. NOTE: To Delete a row from the DataSet I am using the DataRow.Delete() method & not DataRow.Remove() OR DataRow.RemoveAt() methods.

RowDeleting 


protected void grdDsClient_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
  DataSet ds = ViewState["customertData"] as DataSet;
  //To get the deleted row in the DataSet.GetChanges method we need to use Delete method
  ds.Tables[0].Rows[e.RowIndex].Delete();

  grdDsClient.DataSource = ds;
  grdDsClient.DataBind();
  ViewState["customertData"] = ds;
  hdnAllowRedirect.Value = "false";
}

 

RowUpdating 


protected void grdDsClient_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
  string key = e.Keys["uniqueID"].ToString();
  string dsClient = ((TextBox)(grdDsClient.SelectedRow.FindControl("txtCustomerId"))).Text;
  DataSet ds = ViewState["customertData"] as DataSet;

  DataView dv = ds.Tables[0].DefaultView;
  dv.Sort = "customerId";
  DataRowView[] drv = dv.FindRows(dsClient);

  if (drv.Count() > 0)
  {
	foreach(DataRowView dvTemp in drv)
	{
	  if (dvTemp["uniqueID"].ToString() != key)
	  {
		lblMessage.Text = "Duplicate value for the field DS-Client# is not allowed.";
		lblMessage.ForeColor = System.Drawing.Color.FromArgb(170, 46, 46);//red
		return;
	  }
	}
  }

  dv.Sort = "uniqueID";
  drv = dv.FindRows(key);
  drv = dv.FindRows(key);

  if (drv.Count() > 0)
  {
	drv[0]["customerId"] = ((TextBox)(grdDsClient.SelectedRow.FindControl("txtCustomerId"))).Text;
	drv[0]["IsExcluded"] = ((CheckBox)(grdDsClient.SelectedRow.FindControl("chkIsExcluded"))).Checked;
  }

  grdDsClient.EditIndex = -1;
  grdDsClient.SelectedIndex = -1;
  grdDsClient.DataSource = ds;
  grdDsClient.DataBind();

  ViewState["customertData"] = ds;
  hdnAllowRedirect.Value = "false";
}

Now finally, when the user clicks the Update Button, the application will fire the database query for only those rows of the DataSet which has been either added, modified, or deleted.


try
{
	foreach (DataRow dr in dsClient.Tables[0].Rows)
	{
		if (dr.RowState == DataRowState.Deleted)
		{
			cmd = new SqlCommand();
			cmd.CommandText = "Update [ManageCustomer] SET IsDeleted = 1, ModifiedBy = @ModifiedByDel, ModifiedOn = @ModifiedOnDel WHERE customerId = @customerIdDel";
			cmd.Connection = con;
			cmd.Transaction = tran;
			cmd.Parameters.Add("@customerIdDel", SqlDbType.VarChar, 255).Value = dr["customerId", DataRowVersion.Original];
			cmd.Parameters.Add("@ModifiedByDel", SqlDbType.UniqueIdentifier).Value = varProviderUserKey;
			cmd.Parameters.Add("@ModifiedOnDel", SqlDbType.DateTime).Value = DateTime.Now;cmd.ExecuteNonQuery();RecordUserActivity(varProviderUserKey, "VX", dr["customerId",DataRowVersion.Original].ToString(), con, tran);
		}
		else if (dr.RowState == DataRowState.Added)
		{
			cmd = new SqlCommand();
			cmd.CommandText = "INSERT INTO [ManageCustomer] (uniqueID, customerId, VarID, [IsExcluded], [AddedBy], [AddedOn]) VALUES (@uniqueIDAdd, @customerIdAdd, @VarIDAdd, @IsExcludedAdd, @AddedByAdd, @AddedOnAdd)";
			cmd.Connection = con;
			cmd.Transaction = tran;
			cmd.Parameters.Add(new SqlParameter("@uniqueIDAdd", dr["uniqueID"]));
			cmd.Parameters.Add(new SqlParameter("@customerIdAdd", dr["customerId"]));
			cmd.Parameters.Add(new SqlParameter("@VarIDAdd", varProviderUserKey.ToString()));
			cmd.Parameters.Add(new SqlParameter("@IsExcludedAdd", dr["IsExcluded"]));
			cmd.Parameters.Add(new SqlParameter("@AddedByAdd", varProviderUserKey.ToString()));
			cmd.Parameters.Add(new SqlParameter("@AddedOnAdd", DateTime.Now));cmd.ExecuteNonQuery();
			RecordUserActivity(varProviderUserKey, "VA", dr["customerId"].ToString(), con, tran);
		}
		else if (dr.RowState == DataRowState.Modified)
		{
			cmd = new SqlCommand();
			cmd.CommandText = "Update [ManageCustomer] SET customerId = @customerIdNew, IsExcluded = @IsExcludedNew, ModifiedBy = @ModifiedByUpdt, ModifiedOn = @ModifiedOnUpdt WHERE uniqueID = @uniqueIDUpdt";
			cmd.Connection = con;
			cmd.Transaction = tran;
			cmd.Parameters.Add("@customerIdNew", SqlDbType.VarChar, 255).Value = dr["customerId", DataRowVersion.Current];
			cmd.Parameters.Add("@IsExcludedNew", SqlDbType.Bit).Value = dr["IsExcluded"];
			cmd.Parameters.Add("@ModifiedByUpdt", SqlDbType.UniqueIdentifier).Value = varProviderUserKey;
			cmd.Parameters.Add("@ModifiedOnUpdt", SqlDbType.DateTime).Value = DateTime.Now;
			cmd.Parameters.Add("@uniqueIDUpdt", SqlDbType.UniqueIdentifier).Value = dr["uniqueID", DataRowVersion.Original];

			cmd.ExecuteNonQuery();

			if(dr["IsExcluded",DataRowVersion.Original] != dr["IsExcluded",DataRowVersion.Current] &&
			((bool)(dr["IsExcluded",DataRowVersion.Current])) == true)
				RecordUserActivity(varProviderUserKey, "VE", dr["customerId"].ToString(), con, tran);
		}
	}
	tran.Commit();
}
catch (Exception ex)
{
	tran.Rollback();
}
finally
{
	con.Close();
}

 

Explanation

Here, when the page is loaded for the very first time, I have loaded the information from the DataBase into a DataSet. This DataSet is then getting added in the ViewState of the page. Now whenever the User is doing some modifications, it will get updated in the ViewState of that Page only. Once the User clicks the Update button, I am checking, inside a seperate function(which is being called from the click event of the Update button), the RowState of each DataRow from the DataSet. More info regarding the enum DataRowState can be found here.

So for the modified rows only I am firing the database query. I have also kept the entire queries in the same transaction tran, so that I can Roll Back all the changes for any errors.

Finally in the click event of the Update Button, I am calling the DataBase.AcceptChanges() method to clear the RowState of all the rows of the DataBase before adding it again to the ViewState of the page.

SQL Server FileStream Data Type and Error : the remote procedure call failed. [0x800706be]

FILESTREAM

SQL Server is great for storing relational data in a highly structured format, but it has never been particularly good at storing unstructured data, such as videos, graphic files, Word documents, Excel spreadsheets, and so on. In the past, when developers wanted to use SQL Server to manage such unstructured data, developers essentially had two choices. They could store unstructured data in  VARBINARY(MAX) columns inside the database; or they could store the data outside of the database as part of the file system, and include pointers inside a column that pointed to the file’s location. This allowed an application that needed access to the file to find it by looking up the file’s location from inside a SQL Server table.

Neither of these options was a perfect solution. Storing unstructured data in VARBINARY(MAX) columns offers less than ideal performance, has a 2 GB size limit, and can dramatically increase the size of a database.

Storing unstructured data in the file system requires that the files have a unique naming system that allows hundreds, if not thousands of files to be keep track of; it requires managing folders to store the data; security is a problem and often requires using NTFS permissions to keep people from accessing the files inappropriately; it requires separate backups of the database and the files; and it doesn’t prevent problems that arise when outside files are modified or moved and the database is not updated to reflect this.

To help resolve these problems, SQL Server 2008 has introduced what is called FILESTREAM storage, which is essentially a hybrid approach that combines the best features of the previous two options.

FILESTREAM storage is implemented in SQL Server 2008 by storing VARBINARY(MAX) binary large objects (BLOBs) outside of the database and in the NTFS file system. While this sounds very similar to the older method of storing unstructured data in the file system and pointing to it from a column, it is much more sophisticated. Instead of a simple link from a column to an outside file, the SQL Server Database Engine has been integrated with the NTFS file system for optimum performance and ease of administration. For example, FILESTREAM data uses the Windows OS system cache for caching data instead of the SQL Server buffer pool. This allows SQL Server to do what it does best: manage structured data; and allows the Windows OS to do what is does best: manage large files. In addition, SQL Server handles all of the links between database columns and the files, so we don’t have to.

ERROR: 0x800706be

This FILESTREAM option is disabled by default. One has to enable it from the SQL Server Configuration Manager and that’s where I came across this problem. For some reason SQL Server Services was throwing this error. In one line I would say that the wrong answer to this problem would be to try to start the Configuration Manager in administrator mode!

Don’t know why this answer kept popping up again & again when I was trying to search for a solution to this problem. If I am correct then one opens the Configuration Manager in admin mode by default (at least that’s the case for Win7). So as far as I am concerned, opening the Configuration Manager in admin mode is not the solution.

So what’s the solution?  Well its pretty simple, just install the SQL Server 2008 SP1. I don’t know if its a generic problem or what but it seems that updating your SQL Server installs the missing components into it.

SOLUTION

Install the SQL Server 2008 R2 Service Pack 1. It will automatically get updated if your Windows Update option is enabled otherwise, you can download it from the link given above.

For more information regarding new data types of SQL Server 2008 you can visit to the following address http://www.simple-talk.com/sql/learn-sql-server/sql-server-2008-the-new-data-types/