Saving Changes is not permitted.The changes you have made require the following tables to be dropped and recreated..Sql Server 2008 Management Studio Express Error

If you have just installed Sql Server 2008 Express Edition and you have your default settings then you probably might get this error when your want to change some table and then save changes. In the default settings “Prevent Saving changes that require table recreation” is checked which prevents to save the changes which you make in the design of the table. So in this article i will talk less and give you a walkthrough on how to remove this ugly error and to keep it explanatory i will use images rather then simple text. So the error looks like this   Now the stepwise solution is :-

  1. Click Cancel on this screen and then click OK on the next screen.
  2. On the top menu bar select the Tools option and inside tools select the Options menuitem.

    3.Inside options select the “Designer” and in the designer context form “uncheck” the option which says “Prevent Saving changes which requires recreation”.   4.Click OK and you are done and now save changes to your table and your error should no vanish. Happy Programming!!!!!!!!!!!!

by Aashish Kumar

Unable to connect to Sql Server 2008 Express Edition Remotely using the IP and the named instance

If you are trying to connect your sql server 2008 express edition instance remotely and facing problems while connecting it although you have tried all the possible solutions in the book then the problem can be a really very silly thing and what’s that thing , i will describe it shortly. If you are getting the below mentioned error then there might be many reasons for that error. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (.Net SqlClient Data Provider) So in the list of probable reasons is :-

  • Check if the sql server 2008 express edition service and sql server browser services are running or not, if not then please run them first.

  • First check whether TCP/IP is enabled in the Sql Server Configuration Manager Tool and select the instance of sql server 2008 express edition for that, if not enabled enable it.

  • If TCP/IP is enabled then check your firewall settings open two ports 1433 and 1434 on the firewall, generally 1433 is meant for Sql server and 1434 is meant for sql server browser. 1433 is TCP port and 1434 is UDP port
  • The silly mistake which happens when working with sql server 2008 express edition is that by default it runs on dynamic ports so you can’t run it remotely with all the above configuration and it will lead to frustration.So what to do then, the solution is just go to properties of the TCP/IP and in the TCP Port just give whatever port you want your sql server to be run on.That will do the trick for you.

 

  • After doing the port settings now check the firewall rules and open the respective ports which you have just configured and your sql server instance will run remotely.

Happy Programming!!!!!!!!!!!!!!!!!!!!

by Aashish Kumar

Can't Uninstall SQL Server 2005 Express Tools:- Error while installing Sql Server Management Studio Express 2008

The SQL Server 2005 Express Tools are installed. To continue, remove the SQL Server 2005 Express Tools. If you are getting the above error while installing Sql Server Management Studio 2008 then probably you might try the below given solutions one by one to fix this error. I m not certain what will work for you but i have added all the options which i found after spending some time on the net which various people have given and for me the most common one worked which also i have listed below.

  1. Rename the key “HKLMSOFTWAREMicrosoftMicrosoft SQL Server90”. This worked for me i renamed the whole folder to “old” and then the installation started passing all the test. For x64 Windows Server 2008, the reg key needed is: “HKLMSOFTWAREWow6432NodeMicrosoftMicrosoft SQL Server90” ( Thanks to adam).

Once Sql server Management Studio is installed successfully, then restart your system sql server installation will automatically ask you for a restart. After restarting you will see that your sql server 2005 instance will stop working because you played with the 90 directory and if you try to rename old to 90 then the system will not allow you to do so as it already created an entry name 90 for you having “Tools” and “Shell”. So now we are in deep trouble what to do next, what i did was i again renamed the 90 entry which it created to 70 and the entry for sql server 2005 which we renamed as old, again back to 90 and voila everything works fine now. Both sql server installations are running side by side and both management studios are also running side by side anyways we can open old database in SSMS 2008 so there is no need to have SSMS 2005 unless and until there is some special requirement. This was the hit and trial method and it worked for me for the first time but i would not suggest this method to be done on any full fledged install although you may test this on your virtual pc or vmware installation as it might corrupt your sql server 2005 installation in some cases. 2.Try uninstalling any redgate products if you have installed any especially Red Gate Sql Prompt, this also caused issues to some users. 3.Try uninstalling sql server Management Studio 2005 before installing SSMS 2008 Above listed three ways will do the task for you, if you have resolved the problem in some other easy and concrete way then please do post your comment. Happy Programming!!!!!!!!!!!!!!

by Aashish Kumar

Sending List of Complex objects to Sql server 2005 and persisting it in the database

There are situations when you have to do multiple inserts on a single event there are multiple ways to handle this situation.The simplest and which most of the developers do and make a blunder is making individual calls to the database. One common example can be a grid which have facility of multiple Insert in such a situation on a single add button click you have to make hundreds of insert or even thousands,there are more genuine scenarios such as, suppose you are making a Silverlight CMS system and you have to save the state of the page containing various sections such as Header,MenuBar,MainContent and Footer which have all the things customizable, in such a scenario you have to save hundreds of settings on a single save button click and you again have to show the user his settings when he again loads the website. For such situations involving hundreds or thousands of db inserts and updates making individual calls over the network is not at all good,instead i will say that its the worst approach. There are other approaches,most common of them is sending a list of comma separated values.Ideally this can be said to be a workaround if you are sending just an array or a list of one or two field objects.But for complex objects its the worst solution which i have ever seen and this solution very well persist over the internet. But its has various disadvantages first one is that there is complex string manipulation associated with this approach,you will be sending something like this “5,6,9,8,45,78,5,6” and then splitting this string on the comma’s(,).Another disadvantage is that this approach is sql injection prone and above all string manipulation is never considered a good design paradigm. So here in this article i will discuss about the way which i think is the best way to persist changes to the database in such a situation. We will be making use of xml support of the sql server 2005 and xml serialization of objects in C# to achieve our goal. Example Description:-This example is an application in Silverlight 2.0 with WCF,Linq,

[Serializable]
    [DataContract]
    public class WebsiteContainer
    {
        [DataMember]
        public int WebsiteContainerID { get; set; }

        [DataMember]
        public int UserID { get; set; }
        [DataMember]
        public double LogoHeight { get; set; }
        [DataMember]
        public double LogoWidth { get; set; }
        [DataMember]
        public double LogoTop { get; set; }
        [DataMember]
        public double LogoLeft { get; set; }
        [DataMember]
        public string LogoFileName { get; set; }
        [DataMember]
        public string BgColor { get; set; }
        [DataMember]
        public string Text { get; set; }
        [DataMember]
        public double TextFontSize { get; set; }
        [DataMember]
        public string TextFontFamily { get; set; }
        [DataMember]
        public double TextLeft { get; set; }
        [DataMember]
        public double TextTop { get; set; }
        [DataMember]
        public string TextColor { get; set; }
        [DataMember]
        public double TextScale { get; set; }
        [DataMember]
        public double Padding { get; set; }
        [DataMember]
        public int ContainerTypeID { get; set; }
        [DataMember]
        public int WebsiteID { get; set; }
        [DataMember]
        public double ContainerHeight { get; set; }
        [DataMember]
        public double ContainerWidth { get; set; }

    }

 Its important to note that you have to place a serializable attribute on top of the class declaration so that this class’s object can be serialized to XML while sending it to Sql server.

Now the second step is to write the C# code to serialize this object to XML to transmit over the network.

  public static string SerializeObject<T>(List<T> objects)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var obj in objects)
            {
                try
                {
                    XmlWriterSettings writerSettings = new XmlWriterSettings();
                    writerSettings.OmitXmlDeclaration = true;
                    writerSettings.Indent = true;
                    
                    using (XmlWriter xmlWriter = XmlWriter.Create(sb, writerSettings))
                    {
                        XmlSerializer xs = new XmlSerializer(typeof(T));
                        XmlSerializerNamespaces ns = new XmlSerializerNamespaces();
                        ns.Add(String.Empty, String.Empty);
                        xs.Serialize(xmlWriter, obj, ns);
                    }
                }
                catch
                {
                    return string.Empty;
                }
            }
            return sb.ToString();
        }

Above function will take a list of type T and then iterate through each object inside the list and serialize the object and append the serialized object string to a stringbuilder ‘sb’

Here i would like to mention the real thing which you might miss is that , by default the writer settings are set to bring namespaces and xml dtd information which will result in error saying “XML parsing: line 1, character 841, text/xmldecl not at the beginning of input”

To rectify this error you have to add the writerSetting.OmitXmlDeclaration = true and also add empty namespaces using the XmlSerializerNamespaces this will give you a clean xml which can be parsed by the sql server 2005

Finally we return the string builder object after converting it to string.

After this step we have a serialized string of the whole list of objects as shown in given figure

Now the next step is to get this xml serialized object list into the stored proc.Given below is the code on how can you retrieve this list in sql server 2005

Create PROCEDURE [dbo].[SaveWebsite]
@WebsiteContainerXML XML

AS
BEGIN
DECLARE @WebsiteID INT

	SET NOCOUNT ON;
	
SET @WebsiteID = (SELECT TOP 1 WebsiteContainerValues.ID.value('.','INT') FROM @WebsiteContainerXML.nodes('/WebsiteContainer/WebsiteID') as WebsiteContainerValues(ID) )

		BEGIN TRANSACTION
		DELETE FROM dbo.WebsiteContainer WHERE WebsiteID = @WebsiteID	
		IF @@ERROR <> 0
 BEGIN
    ROLLBACK
    RAISERROR ('Error deleting records', 16, 1)
    RETURN
 END
		INSERT INTO dbo.WebsiteContainer (
			UserID,
			LogoHeight,
			LogoWidth,
			LogoTop,
			LogoLeft,
			LogoFileName,
			BgColor,
			[Text],
			TextFontSize,
			TextFontFamily,
			TextTop,
			TextLeft,
			TextColor,
			TextScale,
			Padding,
			ContainerTypeID,
			WebsiteID,
			ContainerHeight,
			ContainerWidth,
			IsRightAligned
			
		) 
		SELECT 
				WebsiteContainerTab.WebsiteContainerCol.value('UserID[1]','int') AS UserID,
                WebsiteContainerTab.WebsiteContainerCol.value('LogoHeight[1]','float') AS LogoHeight,  
                WebsiteContainerTab.WebsiteContainerCol.value('LogoWidth[1]','float') AS LogoWidth,  
                WebsiteContainerTab.WebsiteContainerCol.value('LogoTop[1]','float') AS LogoTop,  
                WebsiteContainerTab.WebsiteContainerCol.value('LogoLeft[1]','float') AS LogoLeft,  
                WebsiteContainerTab.WebsiteContainerCol.value('LogoFileName[1]','varchar(50)') AS LogoFileName,  
                WebsiteContainerTab.WebsiteContainerCol.value('BgColor[1]','varchar(50)') AS BgColor,  
                WebsiteContainerTab.WebsiteContainerCol.value('Text[1]','varchar(50)') AS TEXT,  
                WebsiteContainerTab.WebsiteContainerCol.value('TextFontSize[1]','float') AS TextFontSize,  
                WebsiteContainerTab.WebsiteContainerCol.value('TextFontFamily[1]','varchar(50)') AS TextFontFamily,  
                WebsiteContainerTab.WebsiteContainerCol.value('TextTop[1]','float') AS TextTop,  
                WebsiteContainerTab.WebsiteContainerCol.value('TextLeft[1]','float') AS TextLeft,  
				WebsiteContainerTab.WebsiteContainerCol.value('TextColor[1]','varchar(50)') AS TextColor,  
				WebsiteContainerTab.WebsiteContainerCol.value('TextScale[1]','float') AS TextScale,  
				WebsiteContainerTab.WebsiteContainerCol.value('Padding[1]','float') AS Padding,
				WebsiteContainerTab.WebsiteContainerCol.value('ContainerTypeID[1]','int') AS ContainerTypeID,  
				WebsiteContainerTab.WebsiteContainerCol.value('WebsiteID[1]','int') AS WebsiteID,  
				WebsiteContainerTab.WebsiteContainerCol.value('ContainerHeight[1]','float') AS ContainerHeight,  
				WebsiteContainerTab.WebsiteContainerCol.value('ContainerWidth[1]','float') AS ContainerWidth,
				WebsiteContainerTab.WebsiteContainerCol.value('IsRightAligned[1]','bit') AS IsRightAligned    
          FROM @WebsiteContainerXML.nodes('//WebsiteContainer') AS WebsiteContainerTab(WebsiteContainerCol) 
		

IF @@ERROR <> 0
 BEGIN

    ROLLBACK


    RAISERROR ('Error Inserting in WebContainers', 16, 1)
    RETURN
 END
 COMMIT 

END

You can refer the syntax to retrieve and iterate through the serialized list and then inserting into the database.

Important to make a note that you don’t have to manually iterate through to get the count and then again run some loop inside the stored proc for storing the above syntax will automatically iterate through all the nodes and insert each record.

Happy Programming!!!!!!!!!!!!!!!!!!

by Aashish Kumar

Part 1:- Some useful scripts which come handy while working with sql server 2005

    1. Getting all the primary key and foreign keys in a given database

select OBJECT_NAME(PARENT_OBJ) TABLE_NAME, 
CASE WHEN XTYPE ='F' 
THEN 'FORIEGN KEY' 
ELSE 'PRIMARY KEY' END KEY_TYPE , NAME KEY_NAME
from sysobjects 
where Xtype in ('F' , 'pK') 
ORDER BY XTYPE DESC

This script can be used to get all the primary key and foreign key relationships within a given database.It sometimes can be helpful on a project with a complex database having hundreds and thousands of relationships and in such a database if you have to perform some major changes so you can have a quick look and you can know which all tables and relationship will be effected by these changes.

2. Delete All the Stored Procs for a particular database 

Alter Procedure dbo.DeleteAllProcedures
As
      declare @procName varchar(500)
      declare cur cursor
            for select [name] from sys.objects where type = 'p'
      open cur
      fetch next from cur into @procName
      while @@fetch_status = 0
      begin
            if @procName &lt;&gt; 'DeleteAllProcedures'
                  exec('drop procedure ' + @procName)
                  fetch next from cur into @procName
      end
      close cur
      deallocate cur
Go

      Grant Execute On dbo.DeleteAllProcedures To Public

Go

This can only be handy when you want to delete all the user defined stored procs for a particular database

3.Change the default owner of a stored proc

USE YourDataBaseName
GO
DECLARE
  @OldOwner sysname,
  @NewOwner sysname

 SET @OldOwner = 'dbo'
 SET @NewOwner = 'dev'

DECLARE CURS CURSOR FOR
SELECT  name
FROM sysobjects
WHERE  type = 'p' 
AND
  uid = (SELECT uid FROM sysusers WHERE name = @OldOwner) 
AND 
  NOT name LIKE 'dt%' FOR READ ONLY  

DECLARE @ProcName sysname

OPEN CURS
FETCH CURS INTO @ProcName
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @@VERSION &gt;= 'Microsoft SQL Server 2005'
    BEGIN
        EXEC('alter schema ' + @NewOwner + ' transfer ' + @OldOwner + '.' + @ProcName)
        exec('alter authorization on ' + @NewOwner + '.' + @ProcName + ' to schema owner')
    END
    ELSE
        EXEC('sp_changeobjectowner ''' + @OldOwner + '.' + @ProcName + ''', ''' + @NewOwner + '''')

    FETCH CURS INTO @ProcName
END
CLOSE CURS
DEALLOCATE CURS

4.Finding any stored proc containing the text of your choice

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

5.Reducing the size of your transaction log

dbcc shrinkfile (mydatabasename_log, 10);

This command will reduce the size of your database transaction log to 10MB. But if you are on production then you should not use this method to shrink your database, you should avoid shrinking the database without finding the probable reason why is it growing so fast.

by Aashish Kumar

jQuery & asp.net :- How to implement paging,sorting and filtering on a client side grid using asp.net,jTemplates and JSON

This article will concentrate only on high performance grid implementation using jQuery,JSON, .net,Linq.Most of us in our career face a situation when we want performance intensive i.e solutions where high performance is the topmost priority.

In such scenarios Gridview,Repeater and all other server side controls are far too much of a thing and above all if you want paging,sorting and filtering functionality also in them, then instead of becoming high performance it just becomes a creepy solution which we want to say as high performing because we couldn't find out any other solution in the give amount of time.

In this article i will cover almost every aspect which is important i.e "Paging" , "Sorting" , "Filtering" data on basis of columns.Although many of us are using already available handy solutions whose family include Telerik Rad Controls,Infragistics Controls or DevExpress on the server side and other solutions such as Ingrid and FlexiGrid  which are very popular as client side data grids.So the goal of this article is to achieve the same functionalities  which other client side grids achieve. Having your own code with all the scripts written by you and every stored proc which you can understand will give you a lot more confidence in achieving what you want.This little effort will prove far more better then the tedious task in what others have implemented.Here i would like to be as simple as i can although some stored procs might seem a little complex but mind you if you consider yourself a so-so kind of a guy for SQL and stored procs then they are just a piece of cake.

Disclaimer

This implementation is very simple and just act as a beginning to others who want to implement their own logic.First step is always the hard step, so i m trying to provide that first step,although very basic but this article will solve really complex problems.Also this article is not  intended to compete or compare with any of the above mentioned controls, it just speaks of how simple things can be achieved in simple ways.

Content

  1. Creating a stored proc which will support paging.
  2. Creating a stored proc which will support sorting(Here it is implemented with limited functionality,you can extend and make it generic)
  3. Creating a stored proc which supports filtering (Here it is implemented with limited functionality,you can extend and make it generic)
  4. Creating the business objects to retrieve the paged results and also total records this will be a little complex object for JSON to understand but there are very easy ways which we will discuss to achieve this.
  5. Creating a DAO or Data Access Layer which simply consists of a DataContext based call to this stored proc.
  6. Creating a Service Layer function to call this DAO implementation of the Stored Proc.
  7. Calling this Service Layer Function from our Page.Here we are using WebMethods to demonstrate this example, i would prefer you to use WCF Restful services but here i m using this just because there are very few examples using WebMethods and several examples using Web Services,although WebService way is far more better but if you want things to be simple and not want the hassle of extra security for the WebService layer , or your client does not want a web service model then WebMethods approach would be the only other good alternative.
  8. Calling this WebMethod from built in jQuery ajax functions to get a JSON response from the server.
  9. This step will include plugging in your jTemplate or any other templating engine which suits your need and rendering the data with some tweaks using jQuery
  10. That's all what you need to do while implementing your own grid.

1.Creating a stored proc which will support paging,sorting and filtering

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Aashish Gupta
-- Create date: Feb 14
-- Description:	Retrieves campaigns for advertiser having paging,sorting and filtering facility
-- =============================================
ALTER PROCEDURE [dbo].[GetCampaignByAdvId]
@pageNum INT ,
@pageSize INT,
@sortColumnName VARCHAR(200),
@sortDirection INT	,
@filterStatusId INT,
@advertiserAccId INT,
@totalRecords INT out	
AS
BEGIN
DECLARE @campaigns TABLE
(CampaignID int,row_num int);
	
	SET NOCOUNT ON;
IF @filterStatusId = 0
BEGIN
	INSERT INTO @campaigns
	SELECT CampaignID,ROW_NUMBER() OVER(
	ORDER BY
	CASE WHEN @sortDirection = 1 THEN NULL			
			WHEN @sortColumnName = 'CampaignName' THEN CONVERT(VARCHAR(200),CampaignName)
			WHEN @sortColumnName = 'CampaignStatusName' THEN CONVERT(VARCHAR(100),CampaignStatusName)
			WHEN @sortColumnName = 'UtcStartDate' THEN CONVERT(VARCHAR(100),UtcStartDate)
			WHEN @sortColumnName = 'UtcEndDate' THEN CONVERT(VARCHAR(100),UtcStartDate)
			
			ELSE NULL END DESC,
			CASE WHEN @sortDirection <> 1 THEN NULL
			WHEN @sortColumnName = 'CampaignName' THEN CONVERT(VARCHAR(200),CampaignName)
			WHEN @sortColumnName = 'CampaignStatusName' THEN CONVERT(VARCHAR(100),CampaignStatusName)
			WHEN @sortColumnName = 'UtcStartDate' THEN CONVERT(VARCHAR(100),UtcStartDate)
			WHEN @sortColumnName = 'UtcEndDate' THEN CONVERT(VARCHAR(100),UtcStartDate)
			ELSE NULL END ASC			
	) AS [row_num] FROM dbo.CampaignDetailsView c WHERE c.AdvertiserAccountID = @advertiserAccId
	SET @totalRecords = @@ROWCOUNT
	SELECT c.* FROM dbo.CampaignDetailsView AS c JOIN @campaigns AS tc ON c.CampaignID = tc.CampaignID
	WHERE tc.row_num BETWEEN (((@PageNum - 1) * @PageSize) + 1) 
AND (@PageNum * @PageSize)
ORDER BY tc.row_num

Return @totalRecords
END
ELSE
BEGIN
	INSERT INTO @campaigns
	SELECT CampaignID,ROW_NUMBER() OVER(
	ORDER BY
	CASE WHEN @sortDirection = 1 THEN NULL			
			WHEN @sortColumnName = 'CampaignName' THEN CONVERT(VARCHAR(200),CampaignName)
			WHEN @sortColumnName = 'CampaignStatusName' THEN CONVERT(VARCHAR(100),CampaignStatusName)
			WHEN @sortColumnName = 'UtcStartDate' THEN CONVERT(VARCHAR(100),UtcStartDate)
			WHEN @sortColumnName = 'UtcEndDate' THEN CONVERT(VARCHAR(100),UtcStartDate)
			
			ELSE NULL END DESC,
			CASE WHEN @sortDirection <> 1 THEN NULL
			WHEN @sortColumnName = 'CampaignName' THEN CONVERT(VARCHAR(200),CampaignName)
			WHEN @sortColumnName = 'CampaignStatusName' THEN CONVERT(VARCHAR(100),CampaignStatusName)
			WHEN @sortColumnName = 'UtcStartDate' THEN CONVERT(VARCHAR(100),UtcStartDate)
			WHEN @sortColumnName = 'UtcEndDate' THEN CONVERT(VARCHAR(100),UtcStartDate)
			ELSE NULL END ASC			
			) AS [row_num] FROM dbo.CampaignDetailsView c WHERE c.AdvertiserAccountID = @advertiserAccId AND c.CampaignStatusID = @filterStatusId
	SET @totalRecords = @@ROWCOUNT
	SELECT c.* FROM dbo.CampaignDetailsView AS c JOIN @campaigns AS tc ON c.CampaignID = tc.CampaignID
	WHERE tc.row_num BETWEEN (((@PageNum - 1) * @PageSize) + 1) 
AND (@PageNum * @PageSize)
ORDER BY tc.row_num

Return @totalRecords
END
END

This is the whole stored proc actually what you need for implementing paging,sorting and filtering.

Let me go deep and explain what it does.It takes 6 input parameters and one output parameters.

 

@pageNum INT ,
@pageSize INT,
@sortColumnName VARCHAR(200),
@sortDirection INT	,
@filterStatusId INT,
@advertiserAccId INT,
@totalRecords INT out
  • @pageNum refers to the current page no which we will pass every time from the UI.If our current pageSize is 10 on every call we will fetch only 10 records which is very good as far as performance is considered.Another approach here could be to fetch every record from the Database and then use Linq in your application level to fetch 10 records only at your client end.This second approach can be very handy if you are trying to make a generic client side control with this implementation,but if you have full control over your Database and stored procs i.e if you are developing whole application for your client from UI to Database then the approach i have discussed would be the best but if you dont have access to modifying and creating the stored procs then you can use direct Linq to get the limited amount of records from application layer to the client.In both cases the data brought to the client side would be the same but in direct linq case there will be time lapse in bringing huge data from Database Layer to application layer.So you can also use this second i.e linq approach if your application and DB both are running on the same server.But anyways i prefer the first modifying the stored proc approach because it is the best.
  • @pageSize is the total no of records you want to be displayed per page.
  • @sortColumnName is the name of the actual column which you want to sort.From the UI  we will pass this name by catching which column was actually clicked.
  • @sortDirection is the SortDirection either ASC or DESC
  • @filterStatusId this is my custom implementation which is very specific you can extend it very easily,i couldn't get time to implement this may be one of you can implement it and share the code here.
  • @advertiserAccountId this is a custom parameter which is passed to retrieve the campaigns related to an advertiser.
  • @totalRecords this is an output type parameter which always returns the total no of records which are required for paging.
DECLARE @campaigns TABLE
(CampaignID int,row_num int);

This declares a temporary table which will hold all the actual sorted and filtered records which we will join with the original table and implement paging by providing the pageSize and pageCount logic and retrieving only required amount of records.

 

INSERT INTO @campaigns
	SELECT CampaignID,ROW_NUMBER() OVER(
	ORDER BY
	CASE WHEN @sortDirection = 1 THEN NULL			
			WHEN @sortColumnName = 'CampaignName' THEN CONVERT(VARCHAR(200),CampaignName)
			WHEN @sortColumnName = 'CampaignStatusName' THEN CONVERT(VARCHAR(100),CampaignStatusName)
			WHEN @sortColumnName = 'UtcStartDate' THEN CONVERT(VARCHAR(100),UtcStartDate)
			WHEN @sortColumnName = 'UtcEndDate' THEN CONVERT(VARCHAR(100),UtcStartDate)
			
			ELSE NULL END DESC,
			CASE WHEN @sortDirection <> 1 THEN NULL
			WHEN @sortColumnName = 'CampaignName' THEN CONVERT(VARCHAR(200),CampaignName)
			WHEN @sortColumnName = 'CampaignStatusName' THEN CONVERT(VARCHAR(100),CampaignStatusName)
			WHEN @sortColumnName = 'UtcStartDate' THEN CONVERT(VARCHAR(100),UtcStartDate)
			WHEN @sortColumnName = 'UtcEndDate' THEN CONVERT(VARCHAR(100),UtcStartDate)
			ELSE NULL END ASC			
	) AS [row_num] FROM dbo.CampaignDetailsView c WHERE c.AdvertiserAccountID = @advertiserAccId

2. This includes selection of all records in temporary table based on sorting inside the order by clause.Here we are passing sortDirection based on which the records are sorted either ascending or descending.This is the most important part of this stored proc.

SET @totalRecords = @@ROWCOUNT
	SELECT c.* FROM dbo.CampaignDetailsView AS c JOIN @campaigns AS tc ON c.CampaignID = tc.CampaignID
	WHERE tc.row_num BETWEEN (((@PageNum - 1) * @PageSize) + 1) 
AND (@PageNum * @PageSize)
ORDER BY tc.row_num

Return @totalRecords

This is the main step where paging is actually implemented.Its very simple join of temp and actual table and then retrieving the pageSize amount of records between the currentPage and the nextPage

AS [row_num] FROM dbo.CampaignDetailsView c WHERE c.AdvertiserAccountID = @advertiserAccId AND c.CampaignStatusID = @filterStatusId

3. Filtering is pretty straight forward you can just get only the records by passing a parameter here we are filtering records on the filterStatusId passed from the UI

Filtering is straight forward but sorting is little typical because you cannot write like this Order by @passedparameter @passedSortDirection.This will result in errors.

4. Actually my business object creation required two objects according to my model you can very well make changes according to your requirements.I have created two BO one having simply the private members and properties of the CampaignDetails and also named as CampaignDetails and other is a complex object although merely simple, it just holds total records which are passed by our stored proc and the list of records passed by our stored proc

public class CampaignDetails
    {
        private int _CampaignID;

        private string _CampaignName;

        private int _AdvertiserAccountID;

        private System.Nullable<int> _BrandProfileID;

        private int _AudienceProfileID;

        private decimal _BudgetAmount;

        private System.DateTime _UtcStartDate;

        private System.DateTime _UtcEndDate;

        private int _CampaignStatusID;

        private int _BuyTypeID;

        private int _PacingTypeID;

        private System.DateTime _CreatedDateTime;

        private string _AudienceProfileName;

        private string _BrandProfilename;

        private string _BrandName;

        private int _BusinessCategoryID;

        private string _BusinessCategoryName;

        private string _CampaignStatusDescription;

        private string _CampaignStatusName;

        private string _SalesChannelName;

        private System.Nullable<int> _GenderID;

        [Column(Storage = "_CampaignID", DbType = "Int NOT NULL")]
        public int CampaignID
        {
            get
            {
                return this._CampaignID;
            }
            set
            {
                if ((this._CampaignID != value))
                {
                    this._CampaignID = value;
                }
            }
        }

        [Column(Storage = "_CampaignName", DbType = "NVarChar(200) NOT NULL", CanBeNull = false)]
        public string CampaignName
        {
            get
            {
                return this._CampaignName;
            }
            set
            {
                if ((this._CampaignName != value))
                {
                    this._CampaignName = value;
                }
            }
        }

        [Column(Storage = "_AdvertiserAccountID", DbType = "Int NOT NULL")]
        public int AdvertiserAccountID
        {
            get
            {
                return this._AdvertiserAccountID;
            }
            set
            {
                if ((this._AdvertiserAccountID != value))
                {
                    this._AdvertiserAccountID = value;
                }
            }
        }

        [Column(Storage = "_BrandProfileID", DbType = "Int")]
        public System.Nullable<int> BrandProfileID
        {
            get
            {
                return this._BrandProfileID;
            }
            set
            {
                if ((this._BrandProfileID != value))
                {
                    this._BrandProfileID = value;
                }
            }
        }

        [Column(Storage = "_AudienceProfileID", DbType = "Int NOT NULL")]
        public int AudienceProfileID
        {
            get
            {
                return this._AudienceProfileID;
            }
            set
            {
                if ((this._AudienceProfileID != value))
                {
                    this._AudienceProfileID = value;
                }
            }
        }

        [Column(Storage = "_BudgetAmount", DbType = "Money NOT NULL")]
        public decimal BudgetAmount
        {
            get
            {
                return this._BudgetAmount;
            }
            set
            {
                if ((this._BudgetAmount != value))
                {
                    this._BudgetAmount = value;
                }
            }
        }

        [Column(Storage = "_UtcStartDate", DbType = "DateTime NOT NULL")]
        public System.DateTime UtcStartDate
        {
            get
            {
                return this._UtcStartDate;
            }
            set
            {
                if ((this._UtcStartDate != value))
                {
                    this._UtcStartDate = value;
                }
            }
        }

        [Column(Storage = "_UtcEndDate", DbType = "DateTime NOT NULL")]
        public System.DateTime UtcEndDate
        {
            get
            {
                return this._UtcEndDate;
            }
            set
            {
                if ((this._UtcEndDate != value))
                {
                    this._UtcEndDate = value;
                }
            }
        }

        [Column(Storage = "_CampaignStatusID", DbType = "Int NOT NULL")]
        public int CampaignStatusID
        {
            get
            {
                return this._CampaignStatusID;
            }
            set
            {
                if ((this._CampaignStatusID != value))
                {
                    this._CampaignStatusID = value;
                }
            }
        }

        [Column(Storage = "_BuyTypeID", DbType = "Int NOT NULL")]
        public int BuyTypeID
        {
            get
            {
                return this._BuyTypeID;
            }
            set
            {
                if ((this._BuyTypeID != value))
                {
                    this._BuyTypeID = value;
                }
            }
        }

        [Column(Storage = "_PacingTypeID", DbType = "Int NOT NULL")]
        public int PacingTypeID
        {
            get
            {
                return this._PacingTypeID;
            }
            set
            {
                if ((this._PacingTypeID != value))
                {
                    this._PacingTypeID = value;
                }
            }
        }

        [Column(Storage = "_CreatedDateTime", DbType = "DateTime NOT NULL")]
        public System.DateTime CreatedDateTime
        {
            get
            {
                return this._CreatedDateTime;
            }
            set
            {
                if ((this._CreatedDateTime != value))
                {
                    this._CreatedDateTime = value;
                }
            }
        }

        [Column(Storage = "_AudienceProfileName", DbType = "NVarChar(200) NOT NULL", CanBeNull = false)]
        public string AudienceProfileName
        {
            get
            {
                return this._AudienceProfileName;
            }
            set
            {
                if ((this._AudienceProfileName != value))
                {
                    this._AudienceProfileName = value;
                }
            }
        }

        [Column(Storage = "_BrandProfilename", DbType = "NVarChar(200) NOT NULL", CanBeNull = false)]
        public string BrandProfilename
        {
            get
            {
                return this._BrandProfilename;
            }
            set
            {
                if ((this._BrandProfilename != value))
                {
                    this._BrandProfilename = value;
                }
            }
        }

        [Column(Storage = "_BrandName", DbType = "NVarChar(200) NOT NULL", CanBeNull = false)]
        public string BrandName
        {
            get
            {
                return this._BrandName;
            }
            set
            {
                if ((this._BrandName != value))
                {
                    this._BrandName = value;
                }
            }
        }

        [Column(Storage = "_BusinessCategoryID", DbType = "Int NOT NULL")]
        public int BusinessCategoryID
        {
            get
            {
                return this._BusinessCategoryID;
            }
            set
            {
                if ((this._BusinessCategoryID != value))
                {
                    this._BusinessCategoryID = value;
                }
            }
        }

        [Column(Storage = "_BusinessCategoryName", DbType = "NVarChar(200)")]
        public string BusinessCategoryName
        {
            get
            {
                return this._BusinessCategoryName;
            }
            set
            {
                if ((this._BusinessCategoryName != value))
                {
                    this._BusinessCategoryName = value;
                }
            }
        }

        [Column(Storage = "_CampaignStatusDescription", DbType = "NVarChar(500)")]
        public string CampaignStatusDescription
        {
            get
            {
                return this._CampaignStatusDescription;
            }
            set
            {
                if ((this._CampaignStatusDescription != value))
                {
                    this._CampaignStatusDescription = value;
                }
            }
        }

        [Column(Storage = "_CampaignStatusName", DbType = "NVarChar(50) NOT NULL", CanBeNull = false)]
        public string CampaignStatusName
        {
            get
            {
                return this._CampaignStatusName;
            }
            set
            {
                if ((this._CampaignStatusName != value))
                {
                    this._CampaignStatusName = value;
                }
            }
        }

        [Column(Storage = "_SalesChannelName", DbType = "NVarChar(200) NOT NULL", CanBeNull = false)]
        public string SalesChannelName
        {
            get
            {
                return this._SalesChannelName;
            }
            set
            {
                if ((this._SalesChannelName != value))
                {
                    this._SalesChannelName = value;
                }
            }
        }

        [Column(Storage = "_GenderID", DbType = "Int")]
        public System.Nullable<int> GenderID
        {
            get
            {
                return this._GenderID;
            }
            set
            {
                if ((this._GenderID != value))
                {
                    this._GenderID = value;
                }
            }
        }
    }

Now the other object holding the list of CampaignDetails and total records is CampaignDetailsPaged

public class CampaignDetailsPaged
    {
        private List<CampaignDetails> campaignDetailsAdvertiser;

        public int TotalRecords { get; set; }

        public List<CampaignDetails> CampaignDetailsAdvertiser
        {
            get
            {
                if(campaignDetailsAdvertiser == null)
                {
                    campaignDetailsAdvertiser = new List<CampaignDetails>();
                }
                return campaignDetailsAdvertiser;
            }
        }

    }

5. Creating the DAO Layer function

This is fairly simple, if you are lazy just drag and drop your stored proc in your dbml file and see the designer.cs file for the code.Actually here i am having different data context and i m not using the Linq dbml file at all , i only mentioned it to just help in easing and creating the required code for the stored proc.

[Function(Name = "dbo.GetCampaignByAdvId")]
        public ISingleResult<CampaignDetails> GetCampaignByAdvId([Parameter(DbType = "Int")] System.Nullable<int> pageNum, [Parameter(DbType = "Int")] System.Nullable<int> pageSize, [Parameter(DbType = "VarChar(200)")] string sortColumnName, [Parameter(DbType = "Int")] System.Nullable<int> sortDirection, [Parameter(DbType = "Int")] System.Nullable<int> filterStatusId, [Parameter(DbType = "Int")] System.Nullable<int> advertiserAccId, [Parameter(DbType = "Int")] ref System.Nullable<int> totalRecords)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), pageNum, pageSize, sortColumnName, sortDirection, filterStatusId, advertiserAccId, totalRecords);
            totalRecords = ((System.Nullable<int>)(result.GetParameterValue(6)));
            return ((ISingleResult<CampaignDetails>)(result.ReturnValue));
        }

6. Creating the Service Layer function

Actually speaking we already have completed all the difficult parts and now very simple things are left, now we are just connecting the dots and nothing else

public CampaignDetailsPaged GetCampaignsByAdvertiserId(int pageNum, int pageSize, int advertiserAccId, string sortColumnName,
 int sortDirection, int filterStatusId)
    {
        AdBuyerDataContext adBuyerDataContext = new AdBuyerDataContext(dbConnString);
        int? totalRecords = 0;
        CampaignDetailsPaged campaignDetailsPaged = new CampaignDetailsPaged();
        var ret = adBuyerDataContext.GetCampaignByAdvId(pageNum, pageSize, sortColumnName, sortDirection, filterStatusId,
            advertiserAccId, ref totalRecords);
        List<CampaignDetails> campaignDetails = ret.ToList<CampaignDetails>();
        campaignDetailsPaged.TotalRecords = totalRecords.Value;
        if (campaignDetails.Count > 0)
        {
            foreach (CampaignDetails campaigns in campaignDetails)
            {
                campaignDetailsPaged.CampaignDetailsAdvertiser.Add(campaigns);
            }
        }
        return campaignDetailsPaged;
    }

Here totalrecords returned from the stored proc out parameter are assigned to the TotalRecords property of CampaignDetailsPaged BO and List of campaignDetails is also generated and BO CampaignDetails is returned

7. Calling this Service Layer Function from our Page

Calling this service layer function from the page is also pretty straightforward you just have to include a reference in your code behind file to System.Web.Service and write your code as given below

[WebMethod]
   public static CampaignDetailsPaged GetCampaignsForAdvertiser(string currPage, string pageSize, string advertiserAccId,
       string sortColumnName, int sortDirection, int filterStatusId)
   {
       YourService yourService = new YourService();
       int currentPage = Int32.Parse(currPage);
       int pagesize = Int32.Parse(pageSize);
       int advertiserid = Int32.Parse(advertiserAccId);
       return yourService.GetCampaignsByAdvertiserId(currentPage, pagesize, advertiserid, sortColumnName, sortDirection,
           filterStatusId);

   }

Here we are passing all the required parameters from the UI.One disadvantage of using WebMethods is that the method needs to be static so you cannot access non-static members of the page in this static web method that means you cannot access any server side controls on the page inside this static method, but in our implementation we are not using any server side controls that's why we can use WebMethods

The better approach would have been to use WCF Restful services which can return result in both XML and JSON formats.

I am using WebMethods for particular reason that there  are not so many clear examples available for the WebMethod to be used with jQuery,JSON

Another reason is in your specific implementation your client does not want to use the WebService way then these WebMethods can come handy.

8. Calling this WebMethod from built in jQuery ajax functions to get a JSON response from the server.

function ClientProxy(methodName, paramArray, successFunction, errorFunction) {
    var pagePath = window.location.pathname;
    var paramList = '';

    if (paramArray.length > 0) {
        for (var i = 0; i < paramArray.length; i += 2) {
            if (paramList.length > 0) paramList += ',';
            paramList += '"' + paramArray[i] + '"' + ':' + '"' + paramArray[i + 1] + '"';
        }
    }
    paramList = '{' + paramList + '}';

    $.ajax({
        type: "POST",
        url: pagePath + "/" + methodName,
        contentType: "application/json; charset=utf-8",
        data: paramList,
        dataType: "json",
        success: function(msg) {
            successFunction(msg);
        },
        error: function() {
            errorFunction();
        }
    });
    return false;
}

I have wrapped the $.ajax() jQuery function for Generalizing it to be used with any method and you need not to duplicate it every time.

In this method i receive the

methodName i.e name of WebMethod to be called,

paramArray i.e an array having key value pairs in form of JSON string,

successFunction i.e the function to be called on successful completion of the ajax call

errorFunction i.e the function to be called when an error occurs while retrieving the results
9. Plugging in your jTemplate or any other templating engine

jTemplate is a really very cool and interesting templating engine available out there only problem with this is that its python style syntax may confuse you.But i found it really very powerful.

If you are not using jQuery and using MicorosoftAjax then you can also give try to inbuilt templating features in the new release of Microsoft Ajax Framework

Bertrand Le Roy's has a very good entry explaining Microsoft Ajax templating here :-

http://weblogs.asp.net/bleroy/archive/2009/02/05/how-to-choose-a-client-template-engine.aspx

I have personally chose jTemplates as i was using jQuery and i was not using Microsoft Ajax at all in my approach.But ups and downs are always there,anyways you can choose anything.But if you are following a approach in which you don't use Microsoft Ajax Framework then jTemplates is the best approach to go.

Also you can choose John Resig's micro template they are also a better alternative small and robust and can be found here

http://ejohn.org/blog/javascript-micro-templating/

Major Benefits of using templates is that you can have full control over your rendered HTML and you call only data on the page not the html from the server.

This actually is the main drawback of using the update panel as such in your applications update panel by default brings all your markup as well as data again from the server.So if someone out there is using Update Panels be careful while putting the controls and other content inside the update panel.

Coming to our case we will receive the pure JSON response as

There is a cool way of inserting templates inside your markup, you can achieve this by using

<script type="text/html">

Call the ClientProxy Function to call the webmethod as we have already discussed and pass the required parameters

function LoadCampaign() {
                $("#divProcessing").insertAfter("#content").show();
                ClientProxy("GetCampaignsForAdvertiser", ["currPage", currCampaignPage, "pageSize", numPerPage, "advertiserAccId", advertiserAccId, "sortColumnName", sortColumnName, "sortDirection", sortDirection, "filterStatusId", filterId],
            function(msg) { ApplyTemplateCampaign(msg); }, function() { errorApplyTemplate(); });
            }

Now to implement paging you need a little code

LoadPaging actually initializes the the paging functionality and here we set the pageCount and call the setCampaignPaging() function which checks if the currentPage is the first page or the last page this is to disable and enable the previous and next buttons.

CampaignNextPage and CampaignPreviousPage are used to navigate to previous and next pages in the list.

function loadCampaignPaging(totalRecords) {
           campaignPageCount = Math.ceil(totalRecords / numPerPage);
           setCampaignPaging();
       }
       function setCampaignPaging() {
           if (currCampaignPage === 1) {
               $('#prevCampaign').attr('disabled', 'disabled');
           } else {
               $('#prevCampaign').attr('disabled', '');
               $('#prevCampaign').click(CampaignPrevPage);
           }

           if (currCampaignPage === campaignPageCount) {
               $('#nextCampaign').attr('disabled', 'disabled');
           } else {
               $('#nextCampaign').attr('disabled', '');
               $('#nextCampaign').click(CampaignNextPage);
           }
       }

       function CampaignNextPage() {
           currCampaignPage = currCampaignPage + 1;
           ClientProxyDateFormatted("GetCampaignsForAdvertiser", ["currPage", currCampaignPage, "pageSize", numPerPage, "advertiserAccId", advertiserAccId, "sortColumnName", sortColumnName, "sortDirection", sortDirection, "filterStatusId", filterId],
           function(msg) { ApplyTemplateCampaign(msg); }, function() { errorApplyTemplate(); });
           setCampaignPaging();

       }

       function CampaignPrevPage() {
           currCampaignPage = currCampaignPage - 1;
           ClientProxyDateFormatted("GetCampaignsForAdvertiser", ["currPage", currCampaignPage, "pageSize", numPerPage, "advertiserAccId", advertiserAccId, "sortColumnName", sortColumnName, "sortDirection", sortDirection, "filterStatusId", filterId],
           function(msg) { ApplyTemplateCampaign(msg); }, function() { errorApplyTemplate(); });
           setCampaignPaging();
       }

ApplyTemplateCampaign(msg) by doing this we are calling a function ApplyTemplateCampaign(msg) and in this function we are passing the results received from the server.

Actually ApplyTemplateCampaign function calls the SetTemplate and processTemplate methods which actually fill and load data from the page to the actual UI

function ApplyTemplateCampaign(msg) {
            var totalRecords = msg.d.TotalRecords;
            loadCampaignPaging(totalRecords);
            $("#campaignDetailsContent").setTemplate($("#CampaignDetailsAdvertiser").html(),
            null, { filter_data: false });
            $("#campaignDetailsContent").processTemplate(msg);

#campaignDetailsContent is the name of the actual empty div on which the template will be applied and #CampaignDetailsAdvertiser is the id of the template which will be applied

 <script id="CampaignDetailsAdvertiser" type="text/html">
    <table id="campaignDetailsTable">
    <thead>
        <tr>
            <th>
               Name
            </th>
            <th>
                Status
            </th>
            <th>
                Budget Used
            </th>
            <th>
            Budget Remaining
            </th>
            <th>
            Impressions Goal
            </th>
            <th>
            Impressions Delivered
            </th>
            <th>
            Clicks Delivered
            </th>
            <th>
            CTR(clicks/imps)
            </th>
            <th>
            Start Date
            </th>
            <th>
            End Date
            </th>
        </tr>
    </thead>
    <tbody>
        {#foreach $T.d.CampaignDetailsAdvertiser as campaign}
        <tr>
            <td class="id">
                <a class="lnkCampaignName" href="CampaignDetails.aspx?campaignId={$T.campaign.CampaignID}">{$T.campaign.CampaignName}</a>
                <div class="infoContainerCampaign">
                    <div class="shoutContainerCampaign">
                    </div>
                    <div class="infoContentCampaign">
                        <ul>
                        <li>
                        <h2>{$T.campaign.CampaignName}</h2>
                        <hr/>
                        </li>
                        <li><span class="rollOverHeading" >Budget :</span>{$T.campaign.BudgetAmount}</li>
                        <li><span class="rollOverHeading" >Duration :</span>{$T.campaign.UtcStartDate}-
                        {$T.campaign.UtcEndDate}</li>
                        <br/>
                        <li><span class="rollOverHeading" >Brand Profile :</span>{$T.campaign.BrandProfilename}</li>
                        <li><span class="rollOverHeading" >Name :</span>{$T.campaign.BrandProfilename}</li>
                        <li><span class="rollOverHeading" >Category :</span>{$T.campaign.BrandProfilename}</li>
                        <li><span class="rollOverHeading" >Channel :</span>{$T.campaign.BrandProfilename}</li>
                        <br/>
                        <li><span class="rollOverHeading" >Audience Profile :</span>{$T.campaign.AudienceProfileName}</li>
                        <li><span class="rollOverHeading" >Gender :</span>{$T.campaign.BrandProfilename}</li>
                        <li><span class="rollOverHeading" >Age Group :</span>{$T.campaign.BrandProfilename}</li>
                        <li><span class="rollOverHeading" >Location :</span>{$T.campaign.BrandProfilename}</li>
                        <br/>
                        <li><span class="rollOverHeading" >Status :</span>{$T.campaign.CampaignStatusName}</li>
                    </div>
                </div>
            </td>
            <td>
                {$T.campaign.CampaignStatusName}
            </td>
            <td>
            </td>
            <td>
            </td>
            <td>
            </td>
            <td>
            </td>
            <td>
            </td>
            <td>
            </td>
            <td>
            {$T.campaign.UtcStartDate}
            </td>
            <td>
            {$T.campaign.UtcEndDate}
            </td>
        </tr>
        {#/for}
    </tbody>
</table>

    </script>

Given above is the actual implementation of the template which will be rendered inside the div.In above code i have shown how to read the complex object returned from JSON.

10.  That's all what you need to do while implementing your own client side grid with paging,sorting and filtering functionality.

In Progress.........

  • Live Demo Page for the implementations shown here - i am working on a live demo page for the implementations shown here.
  • Source Code with whole solution and all the layers - i am working on making whole solution to be available as download
  • More generic form of this control which i will make once i get time.
  • Advanced filters and multi column sorting
  • Making the NextPage and PrevPage Functions more generic to accept the function name to be called and then calling that function only(if any of you implement this plz share here), i will do all these but it may take some time for me as i m very busy with my schedule.

If any of you have time and implemented the functionalities do share here as it will help many more people.

Stay tuned for more Action..........

Happy Programming!!!!!!!!!!!!!!!!!!!!!!!!!

Update:-

You can have a look at the working demo at http://www.effectlabs.com/projects/jqnetgrid/

by Aashish Kumar

Script to delete all data from Sql Server database

Sometimes while developing we have situations when  earlier fill some dummy data in our database in earlier stages of development using some data generation tools such as RedGate Sql Data Generator or EMS Data Generator.

This approach is good when we are in initial stages of kick off stages of a project, also after sometime we want to check some related and sensible data so we want to fill the data ourselves, thus now we want to delete all old data from the databas.

Another situation where this can be helpful is when our client wants to clear all his database for previous years and start a new or a fresh copy(This is a rare case but it's sometimes the demand of business)

Given below is the script which will let you delete all the records in the database and also preserve your referential integrity and also can reseed each table to their initial values.

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
  DELETE FROM ?
 else 
  TRUNCATE TABLE ?
'
GO

This piece of script disables the referential integrity and deletes the data in all the tables.

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

This script again enables the referential integrity on all the tables.

EXEC sp_MSForEachTable ' 
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1 
DBCC CHECKIDENT (''?'', RESEED, 0) 
' 
GO

This script reset all the seed to their initial values.If you don't want to reseed your tables to initial values just skip this script.

Given below is the code put together for your convenience to use.

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
  DELETE FROM ?
 else 
  TRUNCATE TABLE ?
'
GO


EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO


EXEC sp_MSForEachTable ' 
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1 
DBCC CHECKIDENT (''?'', RESEED, 0) 
' 
GO

“If someone wants to purchase Sql Server 2008 at a discounted price, you can purchase at a low price from eCostSoftware.”

by Aashish Kumar

How to install Sql Server 2005 SP1 on existing SQL Server 2005 SP2 Install:- If you are getting "A recently applied update, KB913090, failed to install".

  If you are getting this error then one probable reason can be that you are trying to install Sql Server 2005 SP1 on a machine already having Sql Server 2005 SP2 Installed on it. This error might be followed by another error telling "SqlSupport.msp is not digitally signed" The best way to fix this error is by following these steps:-

  1. Save the data in the following Registry key HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server90BootstrapMSIRefCountUninstall

  2.Copy this value data in a text file and save it for future use. 3.Go to control panel , add/remove program and remove the Microsoft Sql Server Setup Support Files.While uninstalling you might get some error message or warning that this uninstall may effect some other Sql Server Components which are already installed. 4.Click Yes to uninstall on the error message. 5.From the installation CD browse to the folder ToolsSetup and start the SqlSupport.msi to again install the Sql Server Setup Support Files 6.After this step again browse to the registry location mentioned earlier  and change the data value to the one we saved in the text file. 7.Now you can successfully start the install of Sql Server 2005 SP1

by Aashish Kumar

Reporting Server Not Starting:Starting Report Server Failed

How to tackle this situation as it may be really frightening

  1. Find your SQL server installation folder it might be in C:Program FilesMicrosoft SQL ServerMSSQL.xReporting ServicesReportManager.Here MSSQL.x can be anything where you Reporting Service is installed, generally if you have installed every thing including Notification Service,Analysis Service,Integration Service and DataBase service then it is MSSQL.4

  2. Use the All Programs menu to open the Report Server Status page.

From here you can locate the instance id ie. is it MSSQL.2 or MSSQL.4 etc...
3.Now open the ReportServer.config File and find the line containing <WebServiceAccount></WebServiceAccount>
4. Add IWAM_ComputerName like this <WebServiceAccount>IWAM_ComputerName</WebServiceAccount>   5. Save and then close the RSreportserver.config file. 6. Restart the SQL Server Reporting Services service.

by Aashish Kumar

Returning Values using AdHoc Sql Queries And using Stored Procedures

Here is a very good article by Scott Mitchell http://aspnet.4guysfromrolla.com/articles/100108-1.aspx

by Aashish Kumar