.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Santhakumar Munuswamy
Asad Ali
Fauzul Azmi
Post New Web Links

Storing and retrieving historic data

Posted By:      Posted Date: September 13, 2010    Points: 0   Category :Sql Server
 
Hi, I am looking at making my database historic and would like some pointers. Data older than 1 year does not need to be kept so every 12 months, data is "recycled". So far I have come up with the following using DATEPART and all data is in the same table (tables simplified): 1. When I insert records: INSERT INTO [Table] VALUES (newid(), @FKID, @Value, GETDATE()) 1. When I update records: UPDATE [Table] SET [Column] = @Value WHERE [FK] = @FKID AND (DATEPART(month, GETDATE()) = DATEPART(month, [TimeStamp])) 2. When I select records: SELECT * FROM [Table] WHERE [FK] = @FKID AND (DATEPART(month, GETDATE()) = DATEPART(month, [TimeStamp])) 3. WHEN I delete records: DELETE FROM [Table] WHERE [FK] = @FKID AND (DATEPART(month, GETDATE()) = DATEPART(month, [TimeStamp]))   Is this efficient? I was looking at partitioning, but I am not sure that it would be feasible as I want to recycle data every 12 months. Or is there a better way to do it? Regards, James


View Complete Post


More Related Resource Links

Storing User Data

  

Hello,

I'm trying to get the current logged in UserId Value, using this code


Protected Sub UserProfileDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles UserProfileDataSource.Selecting      ' Get a reference to the currently logged on user      Dim currentUser As MembershipUser = Membership.GetUser()      ' Determine the currently logged on user's UserId value      Dim currentUserId As Guid = CType(currentUser.ProviderUserKey, Guid)      ' Assign the currently logged on user's UserId to the @UserId parameter      e.Command.Parameters("@UserId").Value = currentUserId End Sub


But, I getting an error (NullReferenceException), I did set a url authorization on the page that i try to get the logged in user information, witch is info.aspx, and I made the login.aspx control to be redirected to the info.aspx after the login process, But the info.aspx  only shows me (access in denied), how can i make this page knows that i am already logged in?




Web Q&A: Storing SQL Data, URL Query Length, and More

  

Find out the best way to store large amounts of XML data in SQL Server, along with the performance implications. What's the maximum length of an XML query to SQL Server in a URL?

Edited by Nancy Michell

MSDN Magazine May 2003


"An error occurred while retrieving data from Oracle Instance..."

  

I have succeffuly Imported the ADF and have also successfully created a Business Data Column. But when I try to query data , I get the following error

An error occurred while retrieving data from Oracle Instance. Administrator, see the server log for more information

And this is what I found in the Application Logs
A Metadata Exception was constructed in App Domain '/LM/W3SVC/81256521/ROOT-1-129217212004078822'. The full exception text is: LobSystem could not be found using criteria 'id=476'.


Storing data into property

  
Hi, I have written a property globally on page load i am storing setting some value into that property but on button click i am not able to retrieve the data. Is there any alternate way to save the data other than viewstate or sessionstate. Ex: Partial Class _Default Inherits System.Web.UI.Page Private s As String Public Property Test() As String Get Return s End Get Set(ByVal value As String) s = value End Set End Property Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not IsPostBack Then s = "Test" End If End Sub Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click s = "Champ" End Sub Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click MsgBox(s.ToString()) End Sub End Class

Storing multiple data types in a class?

  
I need help writing a class that will contain three values per item.  I'll be passing parameters to a report: Parameter Name, Data type (int, string, date, etc), and Value. When I pass those values to the report using the class they should be in original form (i.e. an integer should be a numeric, a string should be a string, etc.).  I can do it converting the data's value to strings on one end, and reconverting to the original data type on the other, which seems like a lot of runaround. Is there a simple way?  Also, if I'm able to store & retrieve the data in it's native format I don't need the "Data type" stored. Any help or tips will be appreciated.

Problem while retrieving data from a lookup column from sharepoint linq.

  
Hi I am working with sharepoint linq concept. When I am giving a query with lookup column It's working fine.  But when i am trying to retrieve the data from a look up column to spgridview. I was unable to get the output. Here is the above code which i have worked.   string strTitle = string.Empty; string strStatus=string.Empty; var context = new LinqSampleDataContext(SPContext.Current.Web.Url); EntityList<SharePoint2010ConceptsItem> Concept = context.GetList<SharePoint2010ConceptsItem>("SharePoint 2010 Concepts"); var query = from cs in Concept where cs.Status.Title.ToString()!="Completed" select new { cs.Title, cs.Id, //cs.Status }; sgvConcepts.DataSource =query; sgvConcepts.DataBind();   Any help me plz how to retrieve the data from a lockup column.bvnprasad

Problem with Retrieving data from lookup column using SharePoint 2010 Linq Concept

  
Hi I am working with sharepoint linq concept. When I am giving a query with lookup column It's working fine.  But when i am trying to retrieve the data from a look up column to spgridview. I was unable to get the output. Here is the above code which i have worked.   string strTitle = string.Empty; string strStatus=string.Empty; var context = new LinqSampleDataContext(SPContext.Current.Web.Url); EntityList<SharePoint2010ConceptsItem> Concept = context.GetList<SharePoint2010ConceptsItem>("SharePoint 2010 Concepts"); var query = from cs in Concept where cs.Status.Title.ToString()!="Completed" select new { cs.Title, cs.Id, //cs.Status }; sgvConcepts.DataSource =query; sgvConcepts.DataBind();   Any help me plz how to retrieve the data from a lockup column.bvnprasad

Storing and retrieving in scientific notation

  
Hi, I am using SQL Server 2005 Express edition. I need to store and retrieve values in a scientific notation in the database. For that purpose I have taken the column datatype as Float. But to my surprise when I insert values which have scientific notation in the table, it rounds off the value and stores and thus while retrieving the values I get the rounded value and not the scientific notation value. e.g. When in an Insert statement, I give a value -5.31E+02, it stores the value in the database as -531. Even a simple select statement like "SELECT -5.31E+02" gives the result as -531. Am I missing anything here ? Can anybody please tell me how do I store and retrieve value in the same notation I give while inserting the data ?

Dealing with Historic Data

  
I am running through a problem here, I have the following information, say we have a shop with the following information:   We have a table in the database with all the ads we have and all the customers in another table.   TableAds: ID DateAdded AdDescription   Customers: CustomerID SignUpDate         So.. In June we do (select count(*) from TableAds) and (select count(*) from Customers where signupdate between 'june' and 'june'): 20,000 Ads 300 customers We averaged a 1.5% (300/20000)   In July we decided to add more Ads  (select count(*) from TableAds) and (select count(*) from Customers where signupdate between 'july' and 'july'): 30,000 Ads 400 customers We averaged a 1.3% (400/30,000)     On the 7th of August we decided to add more ads, so for August what would we have? I can't do the count(*) from TableAds because for the month of august, we had a different count of ads from the beginning of the month till the 7th.     How would I list the month of august?  also.. what if I want all these 3 months in one report, how should I be writing my query?      

Getting error while retrieving data from sql server 2008 to infopath 2007 in sharepoint 2007

  
Hi, I have created an InfoPath form in InfoPath 2007. In this form I am getting data from SQL Server. To do this I have created data source for SQL Server 2008’s Tables. I have created 4 connections for 4 tables. When I am previewing this form from InfoPath Designer 2007, it is working. But when I am publishing this, it is giving me an error ‘Can not update content type’. If I skip this error then my form published but when I open this form from Share Point 2007. It gives me an error:   There has been an error while processing the form. Click Continue to resume filling out the form. You may want to check your form data for errors. Click Start Over to load a new copy of the form.   An error occurred accessing a data source. An entry has been added to the Windows event log of the server. Log ID: 6932 G. Goyal

Suggestion on retrieving data from a database

  
hi everyone, I have a page which will retrieve data base on drop down lists and place the data onto  a gridview. When the data was entered, each field was entered with multi-selected and delmited by a comma. In the database, some fields will have a string with multi information delmited by the comma, for example, Chicago, Atlanta, NYC will be recorded and place in the field in the database. I'm trying to search now but with a dropdown list with Chicago, Atlanta, and NYC being an individual record. If the person selects NYC, all of the records from NYC will appear even though Chicago and Atlanta are part of the string. Can someone show the right direction to do this? Thank you, Carlos The following is the code for the current page:<%@ Page Language="VB" AutoEventWireup="false" CodeFile="ExportContacts1.aspx.vb" EnableEventValidation = "false" Inherits="ExportContacts1" %> <%@ Import Namespace="System.Web.Mail" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server"> Protected Sub exportcsv_Click(ByVal sender As Object, ByVal e As EventArgs) Dim objStreamWriter As IO.StreamWriter objStreamWriter = New IO.

Is sharepoint good for storing relation data ?

  
Hi I would like to start discussion or find your opinion around "if sharepoint 2007 is a good platform to store relational data"? I found that quite often bussness doesn't feel that and push dev to create solution for relation data on top of sharepoint infrastructe. On the end we get huge customization with make everybody frustrated and those poor dev gays are blame for that. I believe that needs for relation data in particular solution draws clear line between apps build on top of Sharepoint or apps build with use of good old SQL database. Can I get your opinion around that ? I have three years expieriance with SharePoint and this is my personal opinion. If you could share your thoughts then for sure this would be helpfull for me and for other dev to show to the bussness why we somethimes suggesting them to go diffrent way. To build this discussion on some example let say that we need to build solution where: we have product table (with details around products) with contain two relation fields : user by country, owner by country we have country table (dictionary for countries), which contain field pointing to the region we have region table (dictionary for regions) we have few diffrents tables describing diffrent types of docs associated to products (like specyfication, safty documents, test documents) and let say there there goanna be a lot

Q about retrieving data from database

  
Hi, I'm having a question about retrieving data. I'm using VS 2008 and I have to create a new consultation record for a patient and I've already created a database with 3 tables, which are doctor, patient and consultation. I have 10 records saved in consultation table, and I've made a aspx form which contains a DetailsView that ask for new consultation details to be added. But I have to be able to get the doctor and patient information from the database and put in the DetailsView, then fill in the rest of the consultation details. The information entered have to be written to the database in the consultation table. But I don't know how to get doctor and patient information from database and apply them into the DetailsView so that doctor who wants to add new consultation details can select patient and doctor information from the database.I was thinking maybe I need to do a query with insert statement and apply into the DetailViewthis is the query i have:INSERT INTO [Consultation] ([ConsultationID], [GPname], [PatientFirstName], [PatientLastName], [Height], [Weight], [IllnessDescription], [Diagnosis], [Date]) VALUES (@ConsultationID, @GPname, @PatientFirstName, @PatientLastName, @Height, @Weight, @IllnessDescription, @Diagnosis, @Date)as you can see, I need to get the GPname, PatientFirstName and PatientLastName from the database tables and the rest will be fi

Storing large data in Session State

  
Hi, We have a scenario where we need to store large tables of data on Session in asp.net page. on a highlevel we have to store following data  1> A table (table 1) with 1000 rows and 8 to 10 columns. 2> A table(table 2) with 500 rows and 8 to 10 columns (actually user can add the data from table 1 to table 2, so as user keeps on adding from table 1 we remove the data and put it in table 2) 3> Another table(table 3) with 1000-1200 rows each having 3 columns. We are having a webfarm, so we need to store it in either SateServer of Oracle DB, please advise which one is better. What would be the performance implications of storing such huge data on the server. One more thing is at any point of time max of 70 -80 people will be accessing our website, so will this cause any performance degradation? Thanks in anticipation Harsha

Storing Data in database

  

Hi everyone,

I'm try to create a news site in my website. How can i store this kind of large text below in the database.

8 Questions Every Business Analyst Should Ask

It does not matter what project you are going to undertake. It is not important what industry you are going to be assessing. What is important is you know what you are going to do. You must as questions. You must find what it is the client wants. Presented is a list of obvious questions every good business analyst should know the answer to when starting a project.

1. What problem is this business having that you hope to solve by developing this project? It should be obvious as to why you would ask that question. If you do not understand what the problem is then you can not help to solve it. Also, when reading the project program it may not be clear as to what the client actually wants. The scope may only tell you what they would like to see happen. It could and often times is not focused on what the true issues are.

2. What is the business doing at present to alleviate or solve the issue? What has been tried in the past? You must understand what the client is doing in order to understand what mus

Storing configuration data

  

Hello!

I'm developing an EventHandler that requires configuration data for each list.

I created  a CustomAction in List ViewToolbar and redirect users to a HandlerConfigurationPage.aspx. 

My question is, where can I save configuration data ?  One option is creating a hidden config.xml in the list and reading values from EventHandler. 

 

any ideas ? 

thanks!

 

 

 

 


Issue in the Format of the Data while retrieving from Cube

  

Hi,

I have an MDX which returns me certain columns like "Sales Value", "Sales Units" etc. Now I need to find the average price which will be Sales Value/ Sales Units. But when I write this in MDX then sometimes I either get empty space or any gibberish value. I want to know what format type I need to select to fetch the actual value. My value would come something like, 0.00231.

The MDX that I have written is,

with

Member

 

[Measures].[Current Year Sales Value] as ' ( [Measures].[SalesValue], [PERIOD].[All PERIOD].[MAT1]) '

Member

 

[Measures].[Previous Year Sales Value]

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend