Search Results

Search found 3379 results on 136 pages for 'datetime'.

Page 46/136 | < Previous Page | 42 43 44 45 46 47 48 49 50 51 52 53  | Next Page >

• Use Linq to SQL to generate sales report

  - by Richard Reddy

  I currently have the following code to generate a sales report over the last 30 days. I'd like to know if it would be possible to use linq to generate this report in one step instead of the rather basic loop I have here. For my requirement, every day needs to return a value to me so if there are no sales for any day then a 0 is returned. Any of the Sum linq examples out there don't explain how it would be possible to include a where filter so I am confused on how to get the total amount per day, or a 0 if no sales, for the last days I pass through. Thanks for your help, Rich //setup date ranges to use DateTime startDate = DateTime.Now.AddDays(-29); DateTime endDate = DateTime.Now.AddDays(1); TimeSpan startTS = new TimeSpan(0, 0, 0); TimeSpan endTS = new TimeSpan(23, 59, 59); using (var dc = new DataContext()) { //get database sales from 29 days ago at midnight to the end of today var salesForDay = dc.Orders.Where(b => b.OrderDateTime > Convert.ToDateTime(startDate.Date + startTS) && b.OrderDateTime <= Convert.ToDateTime(endDate.Date + endTS)); //loop through each day and sum up the total orders, if none then set to 0 while (startDate != endDate) { decimal totalSales = 0m; DateTime startDay = startDate.Date + startTS; DateTime endDay = startDate.Date + endTS; foreach (var sale in salesForDay.Where(b => b.OrderDateTime > startDay && b.OrderDateTime <= endDay)) { totalSales += (decimal)sale.OrderPrice; } Response.Write("From Date: " + startDay + " - To Date: " + endDay + ". Sales: " + String.Format("{0:0.00}", totalSales) + "<br>"); //move to next day startDate = startDate.AddDays(1); } }

  Read the article

• '<=' operator is not working in sql server 2000

  - by Lalit

  Hello, Scenario is, database is in the maintenance phase. this database is not developed by ours developer. it is an existing database developed by the 'xyz' company in sql server 2000. This is real time database, where i am working now. I wanted to write the stored procedure which will retrieve me the records From date1 to date 2.so query is : Select * from MyTableName Where colDate>= '3-May-2010' and colDate<= '5-Oct-2010' and colName='xyzName' whereas my understanding I must get data including upper bound date as well as lower bound date. but somehow I am getting records from '3-May-2010' (which is fine but) to '10-Oct-2010' As i observe in table design , for ColDate, developer had used 'varchar' to store the date. i know this is wrong remedy by them. so in my stored procedure I have also used varchar parameters as @FromDate1 and @ToDate to get inputs for SP. this is giving me result which i have explained. i tried to take the parameter type as 'Datetime' but it is showing error while saving/altering the stored procedure that "@FromDate1 has invalid datatype", same for "@ToDate". situation is that, I can not change the table design at all. what i have to do here ? i know we can use user defined table in sql server 2008 , but there is version sql server 2000. which does not support the same. Please guide me for this scenario. **Edited** I am trying to write like this SP: CREATE PROCEDURE USP_Data (@Location varchar(100), @FromDate DATETIME, @ToDate DATETIME) AS SELECT * FROM dbo.TableName Where CAST(Dt AS DATETIME) >=@fromDate and CAST(Dt AS DATETIME)<=@ToDate and Location=@Location GO but getting Error: Arithmetic overflow error converting expression to data type datetime. in sql server 2000 What should be that ? is i am wrong some where ? also (202 row(s) affected) is changes every time in circular manner means first time sayin (122 row(s) affected) run again saying (80 row(s) affected) if again (202 row(s) affected) if again (122 row(s) affected) I can not understand what is going on ?

  Read the article

• How to sort a ListView control by a column in Visual C#

  - by bconlon

  Microsoft provide an article of the same name (previously published as Q319401) and it shows a nice class 'ListViewColumnSorter ' for sorting a standard ListView when the user clicks the column header. This is very useful for String values, however for Numeric or DateTime data it gives odd results. E.g. 100 would come before 99 in an ascending sort as the string compare sees 1 < 9. So my challenge was to allow other types to be sorted. This turned out to be fairly simple as I just needed to create an inner class in ListViewColumnSorter which extends the .Net CaseInsensitiveComparer class, and then use this as the ObjectCompare member's type. Note: Ideally we would be able to use IComparer as the member's type, but the Compare method is not virtual in CaseInsensitiveComparer , so we have to create an exact type: public class ListViewColumnSorter : IComparer {     private CaseInsensitiveComparer ObjectCompare;     private MyComparer ObjectCompare;     ... rest of Microsofts class implementation... } Here is my private inner comparer class, note the 'new int Compare' as Compare is not virtual, and also note we pass the values to the base compare as the correct type (e.g. Decimal, DateTime) so they compare correctly: private class MyComparer : CaseInsensitiveComparer {     public new int Compare(object x, object y)     {         try         {             string s1 = x.ToString();             string s2 = y.ToString();               // check for a numeric column             decimal n1, n2 = 0;             if (Decimal.TryParse(s1, out n1) && Decimal.TryParse(s2, out n2))                 return base.Compare(n1, n2);             else             {                 // check for a date column                 DateTime d1, d2;                 if (DateTime.TryParse(s1, out d1) && DateTime.TryParse(s2, out d2))                     return base.Compare(d1, d2);             }         }         catch (ArgumentException) { }           // just use base string compare         return base.Compare(x, y);     } } You could extend this for other types, even custom classes as long as they support ICompare. Microsoft also have another article How to: Sort a GridView Column When a Header Is Clicked that shows this for WPF, which looks conceptually very similar. I need to test it out to see if it handles non-string types. #

  Read the article

• Why is Double.Parse so slow?

  - by alexhildyard

  I was recently investigating a bottleneck in one of my applications, which read a CSV file from disk using a TextReader a line at a time, split the tokens, called Double.Parse on each one, then shunted the results into an object list. I was surprised to find it was actually the Double.Parse which seemed to be taking up most of the time.Googling turned up this, which is a little unfocused in places but throws out some excellent ideas:It makes more sense to work with binary format directly, rather than coerce strings into doublesThere is a significant performance improvement in composing doubles directly from the byte stream via long intermediariesString.Split is inefficient on fixed length recordsIn fact it turned out that my problem was more insidious and also more mundane -- a simple case of bad data in, bad data out. Since I had been serialising my Doubles as strings, when I inadvertently divided by zero and produced a "NaN", this of course was serialised as well without error. And because I was reading in using Double.Parse, these "NaN" fields were also (correctly) populating real Double objects without error. The issue is that Double.Parse("NaN") is incredibly slow. In fact, it is of the order of 2000x slower than parsing a valid double. For example, the code below gave me results of 357ms to parse 1000 NaNs, versus 15ms to parse 100,000 valid doubles.            const int invalid_iterations = 1000;            const int valid_iterations = invalid_iterations * 100;            const string invalid_string = "NaN";            const string valid_string = "3.14159265";            DateTime start = DateTime.Now;                        for (int i = 0; i < invalid_iterations; i++)            {                double invalid_double = Double.Parse(invalid_string);            }            Console.WriteLine(String.Format("{0} iterations of invalid double, time taken (ms): {1}",                invalid_iterations,                ((TimeSpan)DateTime.Now.Subtract(start)).Milliseconds            ));            start = DateTime.Now;            for (int i = 0; i < valid_iterations; i++)            {                double valid_double = Double.Parse(valid_string);            }            Console.WriteLine(String.Format("{0} iterations of valid double, time taken (ms): {1}",                valid_iterations,                ((TimeSpan)DateTime.Now.Subtract(start)).Milliseconds            )); I think the moral is to look at the context -- specifically the data -- as well as the code itself. Once I had corrected my data, the performance of Double.Parse was perfectly acceptable, and while clearly it could have been improved, it was now sufficient to my needs.

  Read the article

• ON DELETE RESTRICT Causing Error 150

  - by Levi Hackwith

  CREATE TABLE project ( id INTEGER NOT NULL AUTO_INCREMENT, created_at DATETIME NOT NULL, name VARCHAR(75) NOT NULL, description LONGTEXT NOT NULL, is_active TINYINT NOT NULL DEFAULT '1', PRIMARY KEY (id), INDEX(name, created_at) ) ENGINE = INNODB; CREATE TABLE role ( id INTEGER NOT NULL, name VARCHAR(50) NOT NULL, description LONGTEXT NOT NULL, PRIMARY KEY (id) ) ENGINE = INNODB; CREATE TABLE organization ( id INTEGER NOT NULL AUTO_INCREMENT, created_at DATETIME NOT NULL, name VARCHAR(100) NOT NULL, is_active TINYINT NOT NULL DEFAULT '1', PRIMARY KEY (id) ) ENGINE = INNODB; CREATE TABLE user ( id INTEGER NOT NULL AUTO_INCREMENT, created_at DATETIME NOT NULL, role_id INTEGER NOT NULL, organization_id INTEGER NOT NULL, last_login_at DATETIME NOT NULL, last_ip_address VARCHAR(25) NOT NULL, username VARCHAR(45) NOT NULL, password CHAR(32) NOT NULL, email_address VARCHAR(255) NOT NULL, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, address_1 VARCHAR(100) NOT NULL, address_2 VARCHAR(25) NULL, city VARCHAR(25) NOT NULL, state CHAR(2) NOT NULL, zip_code VARCHAR(10) NOT NULL, primary_phone_number VARCHAR(10) NOT NULL, secondary_phone_number VARCHAR(10) NOT NULL, is_primary_organization_contact TINYINT NOT NULL DEFAULT '0', is_active TINYINT NOT NULL DEFAULT '1', PRIMARY KEY (id), CONSTRAINT fk_user_role_id FOREIGN KEY (role_id) REFERENCES role (id) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fk_user_organization_id FOREIGN KEY (organization_id) REFERENCES organization (id) ON UPDATE RESTRICT ON DELETE RESTRICT ) ENGINE = INNODB; CREATE TABLE project_user ( user_id INTEGER NOT NULL, project_id INTEGER NOT NULL, PRIMARY KEY (user_id, project_id), CONSTRAINT fk_project_user_user_id FOREIGN KEY (user_id) REFERENCES user (id) ON UPDATE RESTRICT ON DELETE CASCADE, CONSTRAINT fk_project_user_project_id FOREIGN KEY (project_id) REFERENCES project (id) ON UPDATE RESTRICT ON DELETE RESTRICT ) ENGINE = INNODB; CREATE TABLE ticket_category ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL, description LONGTEXT NOT NULL, PRIMARY KEY (id) ) ENGINE = INNODB; CREATE TABLE ticket_type ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL, description LONGTEXT NOT NULL, PRIMARY KEY (id) ) ENGINE = INNODB; CREATE TABLE ticket_status ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL, description LONGTEXT NOT NULL, PRIMARY KEY (id) ) ENGINE = INNODB; CREATE TABLE ticket ( id INTEGER NOT NULL AUTO_INCREMENT, created_at DATETIME NOT NULL, project_id INTEGER NOT NULL, created_by INTEGER NOT NULL, submitted_by INTEGER NOT NULL, assigned_to INTEGER NULL, category_id INTEGER NOT NULL, type_id INTEGER NOT NULL, title VARCHAR(75) NOT NULL, description LONGTEXT NOT NULL, contact_type_id TINYINT NOT NULL, affects_all_clients TINYINT NOT NULL DEFAULT '0', is_billable TINYINT NOT NULL DEFAULT '1', esimated_hours DECIMAL(4, 1) NOT NULL DEFAULT '0', hours_worked DECIMAL (4, 1) NOT NULL DEFAULT '0', status_id TINYINT NOT NULL, PRIMARY KEY (id), CONSTRAINT fk_ticket_project_id FOREIGN KEY (project_id) REFERENCES project (id) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fk_ticket_created_by FOREIGN KEY (created_by) REFERENCES user (id) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fk_ticket_submitted_by FOREIGN KEY (submitted_by) REFERENCES user (id) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fk_ticket_assigned_to FOREIGN KEY (assigned_to) REFERENCES user (id) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fk_ticket_category_id FOREIGN KEY (category_id) REFERENCES ticket_category (id) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fk_ticket_type_id FOREIGN KEY (type_id) REFERENCES ticket_type (id) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fk_ticket_status_id FOREIGN KEY (status_id) REFERENCES ticket_status (id) ON UPDATE RESTRICT ON DELETE RESTRICT ) ENGINE = INNODB; CREATE TABLE ticket_time_entry ( id INTEGER NOT NULL AUTO_INCREMENT, user_id INTEGER NOT NULL, ticket_id INTEGER NOT NULL, started_at DATETIME NOT NULL, ended_at DATETIME NOT NULL, PRIMARY KEY (id), CONSTRAINT fk_ticket_time_entry_user_id FOREIGN KEY (user_id) REFERENCES user (id) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fk_ticket_time_entry_ticket_id FOREIGN KEY (ticket_id) REFERENCES ticket (id) ON UPDATE RESTRICT ON DELETE RESTRICT ) ENGINE = INNODB; The ticket table's create statement causes an error 150. I have no clue why. When I remove the ON DELETE RESTRICT statements from the table declaration, it works. Why is that?

  Read the article

• Difficulty adding widgets to django form.

  - by codingJoe

  I have a django app that tracks activities that can benefit a classroom. Using the django examples, I was able to build a form to enter this data. But when I try to add widgets to that form, things get tricky. What I want is a calendar widget that lets the user enter the 'activity_date' field using a widget. If I use Admin interface. The AdminDateWidget works fine. however. This particular user isn't allowed access to the admin interface so I need a different way to present this widget. Also I couldn't figure out how to make the bring the admin widget over into non-admin pages. So I tried a custom widget. This is the first custom widget I've built, so I'm not quite sure what is supposed to be going on here. Any Expert Advice? How do I get my date widget to work? # The Model class Activity(models.Model): activity_date = models.DateField() activity_type = models.CharField(max_length=50, choices=ACTIVITY_TYPES) activity_description = models.CharField(max_length=200) activity_duration= models.DecimalField(decimal_places=2, max_digits=4) est_attendance = models.IntegerField("Estimated attendance") # The Form class ActivityForm(forms.ModelForm): # The following line causes lockup if enabled. # With the DateTimeWidget removed, the form functions correctly except that there is no widget. #activity_date = forms.DateField(label=_('Date'), widget=DateTimeWidget) ##!!! Point of Error !!! class Meta: model = Activity fields = ('activity_date', 'activity_type', 'activity_description', 'activity_duration', 'est_attendance') def __init__(self, *args, **kwargs): super(ActivityForm, self).__init__(*args, **kwargs) instance = getattr(self, 'instance', None) edit_aid = kwargs.get('edit_aid', False) # On a different approach, the following also didn't work. #self.fields['activity_date'].widget = widgets.AdminDateWidget() # The Widget # Example referenced: http://djangosnippets.org/snippets/391/ calbtn = u""" <button id="calendar-trigger">...</button> <img src="%s/site_media/images/icon_calendar.gif" alt="calendar" id="%s_btn" style="cursor: pointer; border: 1px solid #8888aa;" title="Select date and time" onmouseover="this.style.background='#444444';" onmouseout="this.style.background=''" /> <script type="text/javascript"> Calendar.setup({ trigger : "calendar-trigger", inputField : "%s" }); </script>""" class DateTimeWidget(forms.widgets.TextInput): dformat = '%Y-%m-%d %H:%M' def render(self, name, value, attrs=None): print "DTWgt render name=%s, value=%s" % name, value if value is None: value = '' final_attrs = self.build_attrs(attrs, type=self.input_type, name=name) if value != '': try: final_attrs['value'] = \ force_unicode(value.strftime(self.dformat)) except: final_attrs['value'] = \ force_unicode(value) if not final_attrs.has_key('id'): final_attrs['id'] = u'%s_id' % (name) id = final_attrs['id'] jsdformat = self.dformat #.replace('%', '%%') cal = calbtn % (settings.MEDIA_URL, id, id, jsdformat, id) a = u'<input%s />%s' % (forms.util.flatatt(final_attrs), cal) print "render return %s " % a return mark_safe(a) def value_from_datadict(self, data, files, name): print "DTWgt value_from_datadict" dtf = forms.fields.DEFAULT_DATETIME_INPUT_FORMATS empty_values = forms.fields.EMPTY_VALUES value = data.get(name, None) if value in empty_values: return None if isinstance(value, datetime.datetime): return value if isinstance(value, datetime.date): return datetime.datetime(value.year, value.month, value.day) for format in dtf: try: return datetime.datetime(*time.strptime(value, format)[:6]) except ValueError: continue return None

  Read the article

• What is the best WebControl to create this

  - by balexandre

  current output wanted output current code public partial class test : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) populateData(); } private void populateData() { List<temp> ls = new List<temp>(); ls.Add(new temp { a = "AAA", b = "aa", c = "a", dt = DateTime.Now }); ls.Add(new temp { a = "BBB", b = "bb", c = "b", dt = DateTime.Now }); ls.Add(new temp { a = "CCC", b = "cc", c = "c", dt = DateTime.Now.AddDays(1) }); ls.Add(new temp { a = "DDD", b = "dd", c = "d", dt = DateTime.Now.AddDays(1) }); ls.Add(new temp { a = "EEE", b = "ee", c = "e", dt = DateTime.Now.AddDays(2) }); ls.Add(new temp { a = "FFF", b = "ff", c = "f", dt = DateTime.Now.AddDays(2) }); TemplateField tc = (TemplateField)gv.Columns[0]; // <-- want to assign here just day gv.Columns.Add(tc); // <-- want to assign here just day + 1 gv.Columns.Add(tc); // <-- want to assign here just day + 2 gv.DataSource = ls; gv.DataBind(); } } public class temp { public temp() { } public string a { get; set; } public string b { get; set; } public string c { get; set; } public DateTime dt { get; set; } } and in HTML <asp:GridView ID="gv" runat="server" AutoGenerateColumns="false"> <Columns> <asp:TemplateField> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("a") %>' Font-Bold="true" /><br /> <asp:Label ID="Label2" runat="server" Text='<%# Eval("b") %>' Font-Italic="true" /><br /> <asp:Label ID="Label3" runat="server" Text='<%# Eval("dt") %>' /> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> What I'm trying to avoid is repeat code so I can only use one unique TemplateField I can accomplish this with 3 x GridView, one per each day, but I'm really trying to simplify code as the Grid will be exactly the same (as the HTML code goes), just the DataSource changes. Any help is greatly appreciated, Thank you.

  Read the article

• Can I use a single DateTime field on the Entity Framework model side when the value is stored in a set of Int fields in the actual database?

  - by Ivan

  The actual table in the database has separate integer fields for storing year, month, day, hour and minute values (all in UTC) (seconds and milliseconds are irrelevant for my task and considered equal to zero). Needless to say it would be of great convenience to have just one field of DateTime type on the application side and hide all the conversion under the cover of the Entity Framework model code. Any directions on how to do that? I am not very experienced with Entity Framework yet.

  Read the article

• When running UPDATE ... datetime = NOW(); will all rows updated have the same date/time?

  - by Darryl Hein

  When you run something similar to: UPDATE table SET datetime = NOW(); on a table with 1 000 000 000 records and the query takes 10 seconds to run, will all the rows have the exact same time (minutes and seconds) or will they have different times? In other words, will the time be when the query started or when each row is updated? I'm running MySQL, but I'm thinking this applies to all dbs.

  Read the article

• List/remove files, with filenames containing string that's "more than a month ago"?

  - by Martin Tóth

  I store some data in files which follow this naming convention: /interesting/data/filename-YYYY-MM-DD-HH-MM How do I look for the ones with date in file name < now - 1 month and delete them? Files may have changed since they were created, so searching according to last modification date is not good. What I'm doing now, is filter-ing them in python: prefix = '/interesting/data/filename-' import commands names = commands.getoutput('ls {0}*'.format(prefix)).splitlines() from datetime import datetime, timedelta all_files = map(lambda name: { 'name': name, 'date': datetime.strptime(name, '{0}%Y-%m-%d-%H-%M'.format(prefix)) }, names) month = datetime.now() - timedelta(days = 30) to_delete = filter(lambda item: item['date'] < month, all_files) import os map(os.remove, to_delete) Is there a (oneliner) bash solution for this?

  Read the article

• How to change the data in Telerik's RadGrid based on Calendar's selected dates?

  - by Jronny

  I was creating another usercontrol with Telerik's RadGrid and Calendar. <%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %> <table class="style1"> <tr> <td>From</td> <td>To</td> </tr> <tr> <td><asp:Calendar ID="Calendar1" runat="server" SelectionMode="Day"></asp:Calendar></td> <td><asp:Calendar ID="Calendar2" runat="server" SelectionMode="Day"></asp:Calendar></td> </tr> <tr> <td><asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" /></td> <td><asp:Button ID="btnClear" runat="server" Text="Clear" OnClick="btnClear_Click" /></td> </tr> </table> <telerik:RadGrid ID="RadGrid1" runat="server"> <MasterTableView CommandItemDisplay="Top"></MasterTableView> </telerik:RadGrid> and I am using Linq in code-behind: Entities1 entities = new Entities1(); public static object DataSource = null; protected void Page_Load(object sender, EventArgs e) { if (DataSource == null) { DataSource = (from entity in entities.nsc_moneytransaction select new { date = entity.transaction_date.Value, username = entity.username, cashbalance = entity.cash_balance }).OrderByDescending(a => a.date); } BindData(); } public void BindData() { RadGrid1.DataSource = DataSource; } protected void btnSubmit_Click(object sender, EventArgs e) { DateTime startdate = new DateTime(); DateTime enddatedate = new DateTime(); if (Calendar1.SelectedDate != null && Calendar2.SelectedDate != null) { startdate = Calendar1.SelectedDate; enddatedate = Calendar2.SelectedDate; var queryDateRange = from entity in entities.nsc_moneytransaction where DateTime.Parse(entity.transaction_date.Value.ToShortDateString()) >= DateTime.Parse(startdate.ToShortDateString()) && DateTime.Parse(entity.transaction_date.Value.ToShortDateString()) <= DateTime.Parse(enddatedate.ToShortDateString()) select new { date = entity.transaction_date.Value, username = entity.username, cashbalance = entity.cash_balance }; DataSource = queryDateRange.OrderByDescending(a => a.date); } else if (Calendar1.SelectedDate != null) { startdate = Calendar1.SelectedDate; var querySetDate = from entity in entities.nsc_moneytransaction where entity.transaction_date.Value == startdate select new { date = entity.transaction_date.Value, username = entity.username, cashbalance = entity.cash_balance }; DataSource = querySetDate.OrderByDescending(a => a.date); ; } BindData(); } protected void btnClear_Click(object sender, EventArgs e) { Calendar1.SelectedDates.Clear(); Calendar2.SelectedDates.Clear(); } The problems are, (1) when I click the submit button. the data in the RadGrid is not changed. (2) how can we check if there is nothing selected in the Calendar controls, because there is a date (01/01/0001) set even if we do not select anything from that calendar, thus Calendar1.SelectedDate != null is not enough. =( Thanks.

  Read the article

• SqlDataAdapter Update is not working in C# wih Sql Server

  - by Ahmed

  I am trying to save data from C# form to Sql server Northwind Orders database, I am only using CustomerID, OrderDate and ShippedDate for data entry. Following is the code to Form load and save button: private void Form1_Load(object sender, EventArgs e) { SetComb(); connectionString = ConfigurationManager.AppSettings["connectionString"]; sqlConnection = new SqlConnection(connectionString); String sqlSelect = "Select OrderID, CustomerID, OrderDate, ShippedDate from Orders"; sqlDataMaster = new SqlDataAdapter(sqlSelect, sqlConnection); sqlConnection.Open(); //=============================================================================== //--- Set up the INSERT Command //=============================================================================== sInsProcName = "prInsert_Order"; insertcommand = new SqlCommand(sInsProcName, sqlConnection); insertcommand.CommandType = CommandType.StoredProcedure; insertcommand.Parameters.Add(new SqlParameter("@nNewID", SqlDbType.Int, 0, ParameterDirection.Output, false, 0, 0, "OrderID", DataRowVersion.Default, null)); insertcommand.UpdatedRowSource = UpdateRowSource.OutputParameters; insertcommand.Parameters.Add(new SqlParameter("@sCustomerID", SqlDbType.NChar, 5,"CustomerID")); insertcommand.Parameters["@sCustomerID"].Value = cmbCust.SelectedValue; insertcommand.Parameters.Add(new SqlParameter("@dtOrderDate", SqlDbType.DateTime, 8,"OrderDate")); insertcommand.Parameters["@dtOrderDate"].Value = dtOrdDt.Text; insertcommand.Parameters.Add(new SqlParameter("@dtShipDate", SqlDbType.DateTime, 8,"ShippedDate")); insertcommand.Parameters["@dtShipDate"].Value = dtShipDt.Text; sqlDataMaster.InsertCommand = insertcommand; //=============================================================================== //--- Set up the UPDATE Command //=============================================================================== sUpdProcName = "prUpdate_Order"; updatecommand = new SqlCommand(sUpdProcName, sqlConnection); updatecommand.CommandType = CommandType.StoredProcedure; updatecommand.Parameters.Add(new SqlParameter("@nOrderID", SqlDbType.Int, 4, "OrderID")); updatecommand.Parameters.Add(new SqlParameter("@dtOrderDate", SqlDbType.DateTime, 8, "OrderDate")); updatecommand.Parameters.Add(new SqlParameter("@dtShipDate", SqlDbType.DateTime, 8, "ShippedDate")); sqlDataMaster.UpdateCommand = updatecommand; //=============================================================================== //--- Set up the DELETE Command //=============================================================================== sDelProcName = "prDelete_Order"; deletecommand = new SqlCommand(sDelProcName, sqlConnection); deletecommand.CommandType = CommandType.StoredProcedure; deletecommand.Parameters.Add(new SqlParameter("@nOrderID", SqlDbType.Int, 4, "OrderID")); sqlDataMaster.DeleteCommand = deletecommand; dt = new DataTable(); sqlDataMaster.FillSchema(dt, SchemaType.Source); ds = new DataSet(); ds.Tables.Add(dt); bs = new BindingSource(); bs.DataSource = ds.Tables[0]; } public void SetComb() { cmbCust.DataSource = dm.GetData("Select * from Customers order by CompanyName"); cmbCust.DisplayMember = "CompanyName"; cmbCust.ValueMember = "CustomerId"; cmbCust.Text = ""; } private void btnSave_Click(object sender, EventArgs e) { sqlDataMaster.Update((DataTable) bs.DataSource); } and Stored Procedures for Insert/Update/Delete set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[prInsert_Order] -- ALTER PROCEDURE prInsert_Order @sCustomerID CHAR(5), @dtOrderDate DATETIME, @dtShipDate DATETIME, @nNewID INT OUTPUT AS SET NOCOUNT ON INSERT INTO Orders (CustomerID, OrderDate, ShippedDate) VALUES (@sCustomerID, @dtOrderDate, @dtShipDate) SELECT @nNewID = SCOPE_IDENTITY() set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[prUpdate_Order] -- ALTER PROCEDURE prUpdate_Order @nOrderID INT, @dtOrderDate DATETIME, @dtShipDate DATETIME AS UPDATE Orders SET OrderDate = @dtOrderDate, ShippedDate = @dtShipDate WHERE OrderID = @nOrderID set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[prDelete_Order] -- ALTER PROCEDURE prDelete_Order @nOrderID INT AS DELETE Orders WHERE OrderID = @nOrderID In the form CustomerID is selected via combobox which has Display property of CustomerName and Value property of CustomerID. But when clicking save button it shows no error, but it also don't save anything in Orders Table of Northwind....dm.GetData is the method of my Data Access Layer class to just get the info and populate CustomerID combobox. Any help with the code is highly appreciated... Thanks Ahmed

  Read the article

• How can I keep my MVC Views, models, and model binders as clean as possible?

  - by MBonig

  I'm rather new to MVC and as I'm getting into the whole framework more and more I'm finding the modelbinders are becoming tough to maintain. Let me explain... I am writing a basic CRUD-over-database app. My domain models are going to be very rich. In an attempt to keep my controllers as thin as possible I've set it up so that on Create/Edit commands the parameter for the action is a richly populated instance of my domain model. To do this I've implemented a custom model binder. As a result, though, this custom model binder is very specific to the view and the model. I've decided to just override the DefaultModelBinder that ships with MVC 2. In the case where the field being bound to my model is just a textbox (or something as simple), I just delegate to the base method. However, when I'm working with a dropdown or something more complex (the UI dictates that date and time are separate data entry fields but for the model it is one Property), I have to perform some checks and some manual data munging. The end result of this is that I have some pretty tight ties between the View and Binder. I'm architecturally fine with this but from a code maintenance standpoint, it's a nightmare. For example, my model I'm binding here is of type Log (this is the object I will get as a parameter on my Action). The "ServiceStateTime" is a property on Log. The form values of "log.ServiceStartDate" and "log.ServiceStartTime" are totally arbitrary and come from two textboxes on the form (Html.TextBox("log.ServiceStartTime",...)) protected override object GetPropertyValue(ControllerContext controllerContext, ModelBindingContext bindingContext, PropertyDescriptor propertyDescriptor, IModelBinder propertyBinder) { if (propertyDescriptor.Name == "ServiceStartTime") { string date = bindingContext.ValueProvider.GetValue("log.ServiceStartDate").ConvertTo(typeof (string)) as string; string time = bindingContext.ValueProvider.GetValue("log.ServiceStartTime").ConvertTo(typeof (string)) as string; DateTime dateTime = DateTime.Parse(date + " " + time); return dateTime; } if (propertyDescriptor.Name == "ServiceEndTime") { string date = bindingContext.ValueProvider.GetValue("log.ServiceEndDate").ConvertTo(typeof(string)) as string; string time = bindingContext.ValueProvider.GetValue("log.ServiceEndTime").ConvertTo(typeof(string)) as string; DateTime dateTime = DateTime.Parse(date + " " + time); return dateTime; } The Log.ServiceEndTime is a similar field. This doesn't feel very DRY to me. First, if I refactor the ServiceStartTime or ServiceEndTime into different field names, the text strings may get missed (although my refactoring tool of choice, R#, is pretty good at this sort of thing, it wouldn't cause a build-time failure and would only get caught by manual testing). Second, if I decided to arbitrarily change the descriptors "log.ServiceStartDate" and "log.ServiceStartTime", I would run into the same problem. To me, runtime silent errors are the worst kind of error out there. So, I see a couple of options to help here and would love to get some input from people who have come across some of these issues: Refactor any text strings in common between the view and model binders out into const strings attached to the ViewModel object I pass from controller to the aspx/ascx view. This pollutes the ViewModel object, though. Provide unit tests around all of the interactions. I'm a big proponent of unit tests and haven't started fleshing this option out but I've got a gut feeling that it won't save me from foot-shootings. If it matters, the Log and other entities in the system are persisted to the database using Fluent NHibernate. I really want to keep my controllers as thin as possible. So, any suggestions here are greatly welcomed! Thanks

  Read the article

• Adding picture in symfony 2 from symfony form?

  - by user2833510

  How do I add a picture in symfony2 from form to a database. I want to make a logo as a picture field and store project picture in database from form. How do I do this? Here is my form: <?php namespace Projects\ProjectsBundle\Form; use Symfony\Component\Form\AbstractType; use Symfony\Component\Form\FormBuilderInterface; use Symfony\Component\OptionsResolver\OptionsResolverInterface; class ProjectsType extends AbstractType { public function buildForm(FormBuilderInterface $builder, array $options) { $builder ->add('name') ->add('description') ->add('priority','choice', array( 'choices' => array('high' => 'high', 'low' => 'low', 'medium' => 'medium'))) ->add('logo') ->add('startedAt','datetime',array('label' => false,'data'=>new \DateTime(),'attr'=>array('style'=>'display:none;'))) ->add('completedOn','datetime',array('label' => false,'data'=>new \DateTime(),'attr'=>array('style'=>'display:none;'))) ->add('createdDatetime','datetime',array('label' => false,'data'=>new \DateTime(),'attr'=>array('style'=>'display:none;'))) ->add('updatedDatetime','datetime',array('label' => false,'data'=>new \DateTime(),'attr'=>array('style'=>'display:none;'))) ; } public function setDefaultOptions(OptionsResolverInterface $resolver) { $resolver->setDefaults(array( 'data_class' => 'Projects\ProjectsBundle\Entity\Projects' )); } public function getName() { return 'projects_projectsbundle_projectstype'; } } and here is my controller: public function createAction(Request $request) { $user = $this->get('security.context')->getToken()->getUser(); $userId = $user->getId(); $entity = new Projects(); $form = $this->createForm(new ProjectsType(), $entity); $form->bind($request); $entity->setCreatedBy($userId); $entity->setUpdatedBy($userId); $entity->setCompletedBy($userId); if ($form->isValid()) { $em = $this->getDoctrine()->getManager(); $em->persist($entity); $em->flush(); $_SESSION['projectid'] =$entity->getId(); if($request->isXmlHttpRequest()) { $response = new Response(); $output = array('success' => true, 'description' => $entity->getdescription(), 'id' => $entity->getId(), 'name' => $entity->getname(), 'priority' => $entity->getpriority(), 'logo' => $entity->getlogo(), 'startedat' => $entity->getstartedat(),'completedon' => $entity->getcompletedon(),'completedby' => $entity->getCompletedBy(), 'createdby' => $entity->getcreatedby(), 'updatedby' => $entity->getupdatedby(), 'createddatetime' => $entity->getcreateddatetime(), 'updateddatetime' => $entity->getupdateddatetime()); $response->headers->set('Content-Type', 'application/json'); $response->setContent(json_encode($output)); return $response; } return $this->redirect($this->generateUrl('projects_show', array('id' => $entity->getId()))); } return $this->render('ProjectsProjectsBundle:Projects:new.html.twig', array( 'entity' => $entity, 'form' => $form->createView(), )); }

  Read the article

• Normalizing a table

  - by Alex

  I have a legacy table, which I can't change. The values in it can be modified from legacy application (application also can't be changed). Due to a lot of access to the table from new application (new requirement), I'd like to create a temporary table, which would hopefully speed up the queries. The actual requirement, is to calculate number of business days from X to Y. For example, give me all business days from Jan 1'st 2001 until Dec 24'th 2004. The table is used to mark which days are off, as different companies may have different days off - it isn't just Saturday + Sunday) The temporary table would be created from a .NET program, each time user enters the screen for this query (user may run query multiple times, with different values, table is created once), so I'd like it to be as fast as possible. Approach below runs in under a second, but I only tested it with a small dataset, and still it takes probably close to half a second, which isn't great for UI - even though it's just the overhead for first query. The legacy table looks like this: CREATE TABLE [business_days]( [country_code] [char](3) , [state_code] [varchar](4) , [calendar_year] [int] , [calendar_month] [varchar](31) , [calendar_month2] [varchar](31) , [calendar_month3] [varchar](31) , [calendar_month4] [varchar](31) , [calendar_month5] [varchar](31) , [calendar_month6] [varchar](31) , [calendar_month7] [varchar](31) , [calendar_month8] [varchar](31) , [calendar_month9] [varchar](31) , [calendar_month10] [varchar](31) , [calendar_month11] [varchar](31) , [calendar_month12] [varchar](31) , misc. ) Each month has 31 characters, and any day off (Saturday + Sunday + holiday) is marked with X. Each half day is marked with an 'H'. For example, if a month starts on a Thursday, than it will look like (Thursday+Friday workdays, Saturday+Sunday marked with X): ' XX XX ..' I'd like the new table to look like so: create table #Temp (country varchar(3), state varchar(4), date datetime, hours int) And I'd like to only have rows for days which are off (marked with X or H from previous query) What I ended up doing, so far is this: Create a temporary-intermediate table, that looks like this: create table #Temp_2 (country_code varchar(3), state_code varchar(4), calendar_year int, calendar_month varchar(31), month_code int) To populate it, I have a union which basically unions calendar_month, calendar_month2, calendar_month3, etc. Than I have a loop which loops through all the rows in #Temp_2, after each row is processed, it is removed from #Temp_2. To process the row there is a loop from 1 to 31, and substring(calendar_month, counter, 1) is checked for either X or H, in which case there is an insert into #Temp table. [edit added code] Declare @country_code char(3) Declare @state_code varchar(4) Declare @calendar_year int Declare @calendar_month varchar(31) Declare @month_code int Declare @calendar_date datetime Declare @day_code int WHILE EXISTS(SELECT * From #Temp_2) -- where processed = 0) BEGIN Select Top 1 @country_code = t2.country_code, @state_code = t2.state_code, @calendar_year = t2.calendar_year, @calendar_month = t2.calendar_month, @month_code = t2.month_code From #Temp_2 t2 -- where processed = 0 set @day_code = 1 while @day_code <= 31 begin if substring(@calendar_month, @day_code, 1) = 'X' begin set @calendar_date = convert(datetime, (cast(@month_code as varchar) + '/' + cast(@day_code as varchar) + '/' + cast(@calendar_year as varchar))) insert into #Temp (country, state, date, hours) values (@country_code, @state_code, @calendar_date, 8) end if substring(@calendar_month, @day_code, 1) = 'H' begin set @calendar_date = convert(datetime, (cast(@month_code as varchar) + '/' + cast(@day_code as varchar) + '/' + cast(@calendar_year as varchar))) insert into #Temp (country, state, date, hours) values (@country_code, @state_code, @calendar_date, 4) end set @day_code = @day_code + 1 end delete from #Temp_2 where @country_code = country_code AND @state_code = state_code AND @calendar_year = calendar_year AND @calendar_month = calendar_month AND @month_code = month_code --update #Temp_2 set processed = 1 where @country_code = country_code AND @state_code = state_code AND @calendar_year = calendar_year AND @calendar_month = calendar_month AND @month_code = month_code END I am not an expert in SQL, so I'd like to get some input on my approach, and maybe even a much better approach suggestion. After having the temp table, I'm planning to do (dates would be coming from a table): select cast(convert(datetime, ('01/31/2012'), 101) -convert(datetime, ('01/17/2012'), 101) as int) - ((select sum(hours) from #Temp where date between convert(datetime, ('01/17/2012'), 101) and convert(datetime, ('01/31/2012'), 101)) / 8) Besides the solution of normalizing the table, the other solution I implemented for now, is a function which does all this logic of getting the business days by scanning the current table. It runs pretty fast, but I'm hesitant to call a function, if I can instead add a simpler query to get result. (I'm currently trying this on MSSQL, but I would need to do same for Sybase ASE and Oracle)

  Read the article

• What does "error reading login count from pmvarrun" mean?

  - by n3rd

  I have the above mentioned error in my /var/log/auth.log file and just try to figure out if this is a harmelss statement. As far as I understand does pmvarrun tells the system how many active session (e.g. logins) a user has on the system. Full output of auth.log Jan 24 17:44:42 P835 lightdm: pam_unix(lightdm:session): session opened for user lightdm by (uid=0) Jan 24 17:44:42 P835 lightdm: pam_ck_connector(lightdm:session): nox11 mode, ignoring PAM_TTY :0 Jan 24 17:44:49 P835 lightdm: pam_succeed_if(lightdm:auth): requirement "user ingroup nopasswdlogin" not met by user "user" Jan 24 17:44:51 P835 dbus[1289]: [system] Rejected send message, 2 matched rules; type="method_call", sender=":1.31" (uid=104 pid=1882 comm="/usr/lib/indicator-datetime/indicator-datetime-ser") interface="org.freedesktop.DBus.Properties" member="GetAll" error name="(unset)" requested_reply="0" destination=":1.17" (uid=0 pid=1561 comm="/usr/sbin/console-kit-daemon --no-daemon ") Jan 24 17:45:04 P835 lightdm: pam_unix(lightdm:session): session closed for user lightdm Jan 24 17:45:04 P835 lightdm: pam_mount(pam_mount.c:691): received order to close things Jan 24 17:45:04 P835 lightdm: pam_mount(pam_mount.c:693): No volumes to umount Jan 24 17:45:04 P835 lightdm: command: 'pmvarrun' '-u' 'user' '-o' '-1' Jan 24 17:45:04 P835 lightdm: pam_mount(misc.c:38): set_myuid<pre>: (ruid/rgid=0/0, e=0/0) Jan 24 17:45:04 P835 lightdm: pam_mount(misc.c:38): set_myuid<post>: (ruid/rgid=0/0, e=0/0) Jan 24 17:45:04 P835 lightdm: pam_mount(pam_mount.c:438): error reading login count from pmvarrun Jan 24 17:45:04 P835 lightdm: pam_mount(pam_mount.c:728): pam_mount execution complete Jan 24 17:45:08 P835 lightdm: pam_unix(lightdm:session): session opened for user user by (uid=0) Jan 24 17:45:08 P835 lightdm: pam_ck_connector(lightdm:session): nox11 mode, ignoring PAM_TTY :0 Jan 24 17:45:25 P835 polkitd(authority=local): Registered Authentication Agent for unix-session:/org/freedesktop/ConsoleKit/Session2 (system bus name :1.54 [/usr/lib/policykit-1-gnome/polkit-gnome-authentication-agent-1], object path /org/gnome/PolicyKit1/AuthenticationAgent, locale en_US.UTF-8) Jan 24 17:45:47 P835 dbus[1289]: [system] Rejected send message, 2 matched rules; type="method_call", sender=":1.59" (uid=1000 pid=4748 comm="/usr/lib/indicator-datetime/indicator-datetime-ser") interface="org.freedesktop.DBus.Properties" member="GetAll" error name="(unset)" requested_reply="0" destination=":1.17" (uid=0 pid=1561 comm="/usr/sbin/console-kit-daemon --no-daemon ") Thanks for any help

  Read the article

• SQL SERVER – Import CSV into Database – Transferring File Content into a Database Table using CSVexpress

  - by pinaldave

  One of the most common data integration tasks I run into is a desire to move data from a file into a database table.  Generally the user is familiar with his data, the structure of the file, and the database table, but is unfamiliar with data integration tools and therefore views this task as something that is difficult.  What these users really need is a point and click approach that minimizes the learning curve for the data integration tool.  This is what CSVexpress (www.CSVexpress.com) is all about!  It is based on expressor Studio, a data integration tool I’ve been reviewing over the last several months. With CSVexpress, moving data between data sources can be as simple as providing the database connection details, describing the structure of the incoming and outgoing data and then connecting two pre-programmed operators.   There’s no need to learn the intricacies of the data integration tool or to write code.  Let’s look at an example. Suppose I have a comma separated value data file with data similar to the following, which is a listing of terminated employees that includes their hiring and termination date, department, job description, and final salary. EMP_ID,STRT_DATE,END_DATE,JOB_ID,DEPT_ID,SALARY 102,13-JAN-93,24-JUL-98 17:00,Programmer,60,"$85,000" 101,21-SEP-89,27-OCT-93 17:00,Account Representative,110,"$65,000" 103,28-OCT-93,15-MAR-97 17:00,Account Manager,110,"$75,000" 304,17-FEB-96,19-DEC-99 17:00,Marketing,20,"$45,000" 333,24-MAR-98,31-DEC-99 17:00,Data Entry Clerk,50,"$35,000" 100,17-SEP-87,17-JUN-93 17:00,Administrative Assistant,90,"$40,000" 334,24-MAR-98,31-DEC-98 17:00,Sales Representative,80,"$40,000" 400,01-JAN-99,31-DEC-99 17:00,Sales Manager,80,"$55,000" Notice the concise format used for the date values, the fact that the termination date includes both date and time information, and that the salary is clearly identified as money by the dollar sign and digit grouping.  In moving this data to a database table I want to express the dates using a format that includes the century since it’s obvious that this listing could include employees who left the company in both the 20th and 21st centuries, and I want the salary to be stored as a decimal value without the currency symbol and grouping character.  Most data integration tools would require coding within a transformation operation to effect these changes, but not expressor Studio.  Directives for these modifications are included in the description of the incoming data. Besides starting the expressor Studio tool and opening a project, the first step is to create connection artifacts, which describe to expressor where data is stored.  For this example, two connection artifacts are required: a file connection, which encapsulates the file system location of my file; and a database connection, which encapsulates the database connection information.  With expressor Studio, I use wizards to create these artifacts. First click New Connection > File Connection in the Home tab of expressor Studio’s ribbon bar, which starts the File Connection wizard.  In the first window, I enter the path to the directory that contains the input file.  Note that the file connection artifact only specifies the file system location, not the name of the file. Then I click Next and enter a meaningful name for this connection artifact; clicking Finish closes the wizard and saves the artifact. To create the Database Connection artifact, I must know the location of, or instance name, of the target database and have the credentials of an account with sufficient privileges to write to the target table.  To use expressor Studio’s features to the fullest, this account should also have the authority to create a table. I click the New Connection > Database Connection in the Home tab of expressor Studio’s ribbon bar, which starts the Database Connection wizard.  expressor Studio includes high-performance drivers for many relational database management systems, so I can simply make a selection from the “Supplied database drivers” drop down control.  If my desired RDBMS isn’t listed, I can optionally use an existing ODBC DSN by selecting the “Existing DSN” radio button. In the following window, I enter the connection details.  With Microsoft SQL Server, I may choose to use Windows Authentication rather than rather than account credentials.  After clicking Next, I enter a meaningful name for this connection artifact and clicking Finish closes the wizard and saves the artifact. Now I create a schema artifact, which describes the structure of the file data.  When expressor reads a file, all data fields are typed as strings.  In some use cases this may be exactly what is needed and there is no need to edit the schema artifact.  But in this example, editing the schema artifact will be used to specify how the data should be transformed; that is, reformat the dates to include century designations, change the employee and job ID’s to integers, and convert the salary to a decimal value. Again a wizard is used to create the schema artifact.  I click New Schema > Delimited Schema in the Home tab of expressor Studio’s ribbon bar, which starts the Database Connection wizard.  In the first window, I click Get Data from File, which then displays a listing of the file connections in the project.  When I click on the file connection I previously created, a browse window opens to this file system location; I then select the file and click Open, which imports 10 lines from the file into the wizard. I now view the file’s content and confirm that the appropriate delimiter characters are selected in the “Field Delimiter” and “Record Delimiter” drop down controls; then I click Next. Since the input file includes a header row, I can easily indicate that fields in the file should be identified through the corresponding header value by clicking “Set All Names from Selected Row. “ Alternatively, I could enter a different identifier into the Field Details > Name text box.  I click Next and enter a meaningful name for this schema artifact; clicking Finish closes the wizard and saves the artifact. Now I open the schema artifact in the schema editor.  When I first view the schema’s content, I note that the types of all attributes in the Semantic Type (the right-hand panel) are strings and that the attribute names are the same as the field names in the data file.  To change an attribute’s name and type, I highlight the attribute and click Edit in the Attributes grouping on the Schema > Edit tab of the editor’s ribbon bar.  This opens the Edit Attribute window; I can change the attribute name and select the desired type from the “Data type” drop down control.  In this example, I change the name of each attribute to the name of the corresponding database table column (EmployeeID, StartingDate, TerminationDate, JobDescription, DepartmentID, and FinalSalary).  Then for the EmployeeID and DepartmentID attributes, I select Integer as the data type, for the StartingDate and TerminationDate attributes, I select Datetime as the data type, and for the FinalSalary attribute, I select the Decimal type. But I can do much more in the schema editor.  For the datetime attributes, I can set a constraint that ensures that the data adheres to some predetermined specifications; a starting date must be later than January 1, 1980 (the date on which the company began operations) and a termination date must be earlier than 11:59 PM on December 31, 1999.  I simply select the appropriate constraint and enter the value (1980-01-01 00:00 as the starting date and 1999-12-31 11:59 as the termination date). As a last step in setting up these datetime conversions, I edit the mapping, describing the format of each datetime type in the source file. I highlight the mapping line for the StartingDate attribute and click Edit Mapping in the Mappings grouping on the Schema > Edit tab of the editor’s ribbon bar.  This opens the Edit Mapping window in which I either enter, or select, a format that describes how the datetime values are represented in the file.  Note the use of Y01 as the syntax for the year.  This syntax is the indicator to expressor Studio to derive the century by setting any year later than 01 to the 20th century and any year before 01 to the 21st century.  As each datetime value is read from the file, the year values are transformed into century and year values. For the TerminationDate attribute, my format also indicates that the datetime value includes hours and minutes. And now to the Salary attribute. I open its mapping and in the Edit Mapping window select the Currency tab and the “Use currency” check box.  This indicates that the file data will include the dollar sign (or in Europe the Pound or Euro sign), which should be removed. And on the Grouping tab, I select the “Use grouping” checkbox and enter 3 into the “Group size” text box, a comma into the “Grouping character” text box, and a decimal point into the “Decimal separator” character text box. These entries allow the string to be properly converted into a decimal value. By making these entries into the schema that describes my input file, I’ve specified how I want the data transformed prior to writing to the database table and completely removed the requirement for coding within the data integration application itself. Assembling the data integration application is simple.  Onto the canvas I drag the Read File and Write Table operators, connecting the output of the Read File operator to the input of the Write Table operator. Next, I select the Read File operator and its Properties panel opens on the right-hand side of expressor Studio.  For each property, I can select an appropriate entry from the corresponding drop down control.  Clicking on the button to the right of the “File name” text box opens the file system location specified in the file connection artifact, allowing me to select the appropriate input file.  I indicate also that the first row in the file, the header row, should be skipped, and that any record that fails one of the datetime constraints should be skipped. I then select the Write Table operator and in its Properties panel specify the database connection, normal for the “Mode,” and the “Truncate” and “Create Missing Table” options.  If my target table does not yet exist, expressor will create the table using the information encapsulated in the schema artifact assigned to the operator. The last task needed to complete the application is to create the schema artifact used by the Write Table operator.  This is extremely easy as another wizard is capable of using the schema artifact assigned to the Read Table operator to create a schema artifact for the Write Table operator.  In the Write Table Properties panel, I click the drop down control to the right of the “Schema” property and select “New Table Schema from Upstream Output…” from the drop down menu. The wizard first displays the table description and in its second screen asks me to select the database connection artifact that specifies the RDBMS in which the target table will exist.  The wizard then connects to the RDBMS and retrieves a list of database schemas from which I make a selection.  The fourth screen gives me the opportunity to fine tune the table’s description.  In this example, I set the width of the JobDescription column to a maximum of 40 characters and select money as the type of the LastSalary column.  I also provide the name for the table. This completes development of the application.  The entire application was created through the use of wizards and the required data transformations specified through simple constraints and specifications rather than through coding.  To develop this application, I only needed a basic understanding of expressor Studio, a level of expertise that can be gained by working through a few introductory tutorials.  expressor Studio is as close to a point and click data integration tool as one could want and I urge you to try this product if you have a need to move data between files or from files to database tables. Check out CSVexpress in more detail.  It offers a few basic video tutorials and a preview of expressor Studio 3.5, which will support the reading and writing of data into Salesforce.com. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Query, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology

  Read the article

• Is it better to load up a class with methods or extend member functionality in a local subclass?

  - by Calvin Fisher

  Which is better? Class #1: public class SearchClass { public SearchClass (string ProgramName) { /* Searches LocalFile objects, handles exceptions, and puts results into m_Results. */ } DateTime TimeExecuted; bool OperationSuccessful; protected List<LocalFile> m_Results; public ReadOnlyCollection<LocalFile> Results { get { return new ReadOnlyCollection<LocalFile>(m_Results); } } #region Results Filters public DateTime OldestFileModified { get { /* Does what it says. */ } } public ReadOnlyCollection<LocalFile> ResultsWithoutProcessFiles() { return new ReadOnlyCollection<LocalFile> ((from x in m_Results where x.FileTypeID != FileTypeIDs.ProcessFile select x).ToList()); } #endregion } Or class #2: public class SearchClass { public SearchClass (string ProgramName) { /* Searches LocalFile objects, handles exceptions, and puts results into m_Results. */ } DateTime TimeExecuted; bool OperationSuccessful; protected List<LocalFile> m_Results; public ReadOnlyCollection<LocalFile> Results { get { return new ReadOnlyCollection<LocalFile>(m_Results); } } public class SearchResults : ReadOnlyCollection<LocalFile> { public SearchResults(IList<LocalFile> iList) : base(iList) { } #region Results Filters public DateTime OldestFileModified { get { /* Does what it says. */ } } public ReadOnlyCollection<LocalFile> ResultsWithoutProcessFiles() { return new ReadOnlyCollection<LocalFile> ((from x in this where x.FileTypeID != FileTypeIDs.ProcessFile select x).ToList()); } #endregion } } ...with the implication that OperationSuccessful is accompanied by a number of more interesting properties on how the operation went, and OldestFileModified and ResultsWithoutProcessFiles() also have several more siblings in the Results Filters section.

  Read the article

• Reading a large SQL Errorlog

  - by steveh99999

  I came across an interesting situation recently where a SQL instance had been configured with the Audit of successful and failed logins being written to the errorlog. ie This meant… every time a user or the application connected to the SQL instance – an entry was written to the errorlog. This meant…  huge SQL Server errorlogs. Opening an errorlog in the usual way, using SQL management studio, was extremely slow… Luckily, I was able to use xp_readerrorlog to work around this – here’s some example queries..   To show errorlog entries from the currently active log, just for today :- DECLARE @now DATETIME DECLARE @midnight DATETIME SET @now = GETDATE() SET @midnight =  DATEADD(d, DATEDIFF(d, 0, getdate()), 0) EXEC xp_readerrorlog 0,1,NULL,NULL,@midnight,@now   To find out how big the current errorlog actually is, and what the earliest and most recent entries are in the errorlog :- CREATE TABLE #temp_errorlog (Logdate DATETIME, ProcessInfo VARCHAR(20),Text VARCHAR(4000)) INSERT INTO #temp_errorlog EXEC xp_readerrorlog 0 -- for current errorlog SELECT COUNT(*) AS 'Number of entries in errorlog', MIN(logdate) AS 'ErrorLog Starts', MAX(logdate) AS 'ErrorLog Ends' FROM #temp_errorlog DROP TABLE #temp_errorlog To show just DBCC history  information in the current errorlog :- EXEC xp_readerrorlog 0,1,'dbcc'   To show backup errorlog entries in the current errorlog :- CREATE TABLE #temp_errorlog (Logdate DATETIME, ProcessInfo VARCHAR(20),Text VARCHAR(4000)) INSERT INTO #temp_errorlog EXEC xp_readerrorlog 0 -- for current errorlog SELECT * from #temp_errorlog WHERE ProcessInfo = 'Backup' ORDER BY Logdate DROP TABLE #temp_errorlog XP_Errorlog is an undocumented system stored procedure – so no official Microsoft link describing the parameters it takes – however,  there’s a good blog on this here And, if you do have a problem with huge errorlogs – please consider running system stored procedure  sp_cycle_errorlog on a nightly or regular basis.  But if you do this,  remember to change the amount of errorlogs you do retain – the default of 6 might not be sufficient for you….

  Read the article

• Redirecting to a dynamic page

  - by binarydev

  I have a page displaying blog posts (latest_posts.php) and another page that display single blog posts (blog.php) . I intend to link the image title in latest_posts.php so that it redirects to blog.php where it would display the particular post that was clicked. latest_posts.php: <!-- Header --> <h2 class="underline"> <span>What&#039;s new</span> <span></span> </h2> <!-- /Header --> <!-- Posts list --> <ul class="post-list post-list-1"> <?php /* Fetches Date/Time, Post Content and title */ include 'dbconnect.php'; $sql = "SELECT * FROM wp_posts"; $res = mysql_query($sql); while ( $row = mysql_fetch_array($res) ) { ?> <!-- Post #1 --> <li class="clear-fix"> <!-- Date --> <div class="post-list-date"> <div class="post-date-box"> <?php //Timestamp broken down to show accordingly $timestamp = $row['post_date']; $datetime = new DateTime($timestamp); $date = $datetime->format("d"); $month = $datetime->format("M"); ?> <h3> <?php echo $date; ?> </h3> <span> <?php echo $month; ?> </span> </div> </div> <!-- /Date --> <!-- Image + comments count --> <div class="post-list-image"> <!-- Image --> <div class="image image-overlay-url image-fancybox-url"> <a href="post.php" class="preloader-image"> <?php echo '<img src="', $row['image'], '" alt="' , $row['post_title'] , '\'s Blog Image" />'; ?> </a> </div> <!-- /Image --> </div> <!-- /Image + comments count --> <!-- Content --> <div class="post-list-content"> <div> <!-- Header --> <h4> <a href="post.php? . $row['ID'] . "> <?php echo $row['post_title']; ?> </a> </h4> <!-- /Header --> <!-- Excerpt --> <p> <?php echo $row ['post_content']; }?> </p> <!-- /Excerpt --> </div> </div> <!-- /Content --> </li> <!-- /Post #1 --> </ul> <!-- /Posts list --> <a href="blog.php" class="button-browse">Browse All Posts</a> </div> <?php require_once('include/twitter_user_timeline.php'); ?> blog.php: <?php require_once('include/header.php'); ?> <body class="blog"> <?php require_once('include/navigation_bar_blog.php'); ?> <div class="blog"> <div class="main"> <!-- Header --> <h2 class="underline"> <span>What&#039;s new</span> <span></span> </h2> <!-- /Header --> <!-- Layout 66x33 --> <div class="layout-p-66x33 clear-fix"> <!-- Left column --> <!-- <div class="column-left"> --> <!-- Posts list --> <ul class="post-list post-list-2"> <?php /* Fetches Date/Time, Post Content and title with Pagination */ include 'dbconnect.php'; //sets to default page if(empty($_GET['pn'])){ $page=1; } else { $page = $_GET['pn']; } // Index of the page $index = ($page-1)*3; $sql = "SELECT * FROM `wp_posts` ORDER BY `post_date` DESC LIMIT " . $index . " ,3"; $res = mysql_query($sql); //Loops through the values while ( $row = mysql_fetch_array($res) ) { ?> <!-- Post #1 --> <li class="clear-fix"> <!-- Date --> <div class="post-list-date"> <div class="post-date-box"> <?php //Timestamp broken down to show accordingly $timestamp = $row['post_date']; $datetime = new DateTime($timestamp); $date = $datetime->format("d"); $month = $datetime->format("M"); ?> <h3> <?php echo $date; ?> </h3> <span> <?php echo $month; ?> </span> </div> </div> <!-- /Date --> <!-- Image + comments count --> <div class="post-list-image"> <!-- Image --> <div class="image image-overlay-url image-fancybox-url"> <a href="post.php" class="preloader-image"> <?php echo '<img src="', $row['image'], '" alt="' , $row['post_title'] , '\'s Blog Image" />'; ?> </a> </div> <!-- /Image --> </div> <!-- /Image + comments count --> <!-- Content --> <div class="post-list-content"> <div> <?php $id = $_GET['ID']; $post = lookup_post_somehow($id); if($post) { // render post } else { echo 'blog post not found..'; } ?> <!-- Header --> <h4> <a href="post.php"> <?php echo $row['post_title']; ?> </a> </h4> <!-- /Header --> <!-- Excerpt --> <p> <?php echo $row ['post_content']; ?> </p> <!-- /Excerpt --> </div> </div> <!-- /Content --> </li> <!-- /Post #1 --> <?php } // close while loop ?> </ul> <!-- /Posts list --> <div><!-- Pagination --> <ul class="blog-pagination clear-fix"> <?php //Count the number of rows $numberofrows = mysql_query("SELECT COUNT(ID) FROM `wp_posts`"); //Do ciel() to round the result according to number of posts $postsperpage = 4; $numOfPages = ceil($numberofrows / $postsperpage); for($i=1; $i < $numOfPages; $i++) { //echos links for each page $paginationDisplay = '<li><a href="blog.php?pn=' . $i . '">' . $i . '</a></li>'; echo $paginationDisplay; } ?> <!-- <li><a href="#" class="selected">1</a></li> <li><a href="#">2</a></li> <li><a href="#">3</a></li> <li><a href="#">4</a></li> --> </ul> </div><!-- /Pagination --> <!-- /div> --> <!-- Left column --> </div> <!-- /Layout 66x33 --> </div> </div> <?php require_once('include/twitter_user_timeline.php'); ?> <?php require_once('include/footer_blog.php'); ?> How do I render?

  Read the article

• How can I design my classes to include calendar events stored in a database?

  - by Gianluca78

  I'm developing a web calendar in php (using Symfony2) inspired by iCal for a project of mine. At this moment, I have two classes: a class "Calendar" and a class "CalendarCell". Here you are the two classes properties and method declarations. class Calendar { private $month; private $monthName; private $year; private $calendarCellList = array(); private $translator; public function __construct($month, $year, $translator) {} public function getCalendarCellList() {} public function getMonth() {} public function getMonthName() {} public function getNextMonth() {} public function getNextYear() {} public function getPreviousMonth() {} public function getPreviousYear() {} public function getYear() {} private function calculateDaysPreviousMonth() {} private function calculateNumericDayOfTheFirstDayOfTheWeek() {} private function isCurrentDay(\DateTime $dateTime) {} private function isDifferentMonth(\DateTime $dateTime) {} } class CalendarCell { private $day; private $month; private $dayNameAbbreviation; private $numericDayOfTheWeek; private $isCurrentDay; private $isDifferentMonth; private $translator; public function __construct(array $parameters) {} public function getDay() {} public function getMonth() {} public function getDayNameAbbreviation() {} public function isCurrentDay() {} public function isDifferentMonth() {} } Each calendar day can includes many calendar events (such as appointments or schedules) stored in a database. My question is: which is the best way to manage these calendar events in my classes? I think to add a eventList property in CalendarCell and populate it with an array of CalendarEvent objects fetched by the database. This kind of solution doesn't allow other coders to reuse the classes without db (because I should inject at least a repository services also) just to create and visualize a calendar... so maybe it could be better to extend CalendarCell (for instance in CalendarCellEvent) and add the database features? I feel like I'm missing some crucial design pattern! Any suggestion will be very appreciated!

  Read the article

• How can I design my classes for a calendar based on database events?

  - by Gianluca78

  I'm developing a web calendar in php (using Symfony2) inspired by iCal for a project of mine. At this moment, I have two classes: a class "Calendar" and a class "CalendarCell". Here you are the two classes properties and method declarations. class Calendar { private $month; private $monthName; private $year; private $calendarCellList = array(); private $translator; public function __construct($month, $year, $translator) {} public function getCalendarCells() {} public function getMonth() {} public function getMonthName() {} public function getNextMonth() {} public function getNextYear() {} public function getPreviousMonth() {} public function getPreviousYear() {} public function getYear() {} private function calculateDaysPreviousMonth() {} private function calculateNumericDayOfTheFirstDayOfTheWeek() {} private function isCurrentDay(\DateTime $dateTime) {} private function isDifferentMonth(\DateTime $dateTime) {} } class CalendarCell { private $day; private $month; private $dayNameAbbreviation; private $numericDayOfTheWeek; private $isCurrentDay; private $isDifferentMonth; private $translator; public function __construct(array $parameters) {} public function getDay() {} public function getMonth() {} public function getDayNameAbbreviation() {} public function isCurrentDay() {} public function isDifferentMonth() {} } Each calendar day can includes many events stored in a database. My question is: which is the best way to manage these events in my classes? I think to add a eventList property in CalendarCell and populate it with an array of CalendarEvent objects fetched by the database. This kind of solution doesn't allow other coders to reuse the classes without db (because I should inject at least a repository services also) just to create and visualize a calendar... so maybe it could be better to extend CalendarCell (for instance in CalendarCellEvent) and add the database features? I feel like I'm missing some crucial design pattern! Any suggestion will be very appreciated!

  Read the article

• Understanding the passing of data/life of a script in web development/CodeIgniter

  - by Pete Jodo

  I hope I worded the title accurately enough but I typically use Java and don't have much experience in Web Development/PHP/CodeIgniter. I have a difficult time understanding the life cycle of a script as I found out trying to implement a certain feature to a website I am developing (as a means of learning how to). I'll first describe the feature I tried implementing and then the problem I ran into that made me question my fundamental understanding of how scripts work since I'm used to typical OOP. Ok so here goes... I have a webpage that has 2 basic tasks a user can do, create and delete an entry. What I attempted to implement was a way to time a user how long it takes them to complete a certain task. The way I did this was have a homepage where there would be a list of tasks a user to choose from (in this case 2, create and delete). A user would click a task which would link to the 'true' homepage where the user then would be expected to complete the task. My script looks like this: <?php class Site extends CI_Controller { var $task1; var $tasks = array( "task1" => NULL, "date1" => 0, "date2" => 0, "diff" => 0); function __construct() { parent::__construct(); include 'timetask.php'; $this->task1 = new TimeTask("create"); } function index() { $this->tasks['task1'] = $this->task1->getTask(); $this->tasks['diff'] = $this->task1->getTimeDiff(); if($this->tasks['diff'] == NULL) { $this->tasks['diff'] = 0; } $this->load->view('usability_test', $this->tasks); } function origIndex() { $this->task1->setDate1(new DateTime()); $this->tasks['date1'] = $this->task1->getDate1()->getTimestamp(); $data = array(); if($q = $this->site_model->get_records()) { $data['records'] = $q; } $this->load->view('options_view', $data); } function create() { $this->task1->setDate2(new DateTime()); $this->tasks['date2'] = $this->task1->getDate2()->getTimestamp(); $data = array( 'author' => $this->input->post('author'), 'title' => $this->input->post('title'), 'contents' => $this->input->post('contents') ); $this->site_model->add_record($data); $this->index(); } I only included create to keep it short. Then I also have the TimeTask class, that actually another StackOverflow so kindly helped me with: <?php class TimeTask { private $task; /** * @var DateTime */ private $date1, $date2; function __construct($currTask) { $this->task = $currTask; } public function getTimeDiff() { $hasDiff = $this->date1 && $this->date2; if ($hasDiff) { return $this->date2->getTimestamp() - $this->date1->getTimestamp(); } else { return NULL; } } public function __toString() { return (string) $this->getTimeDiff(); } /** * @return \DateTime */ public function getDate1() { return $this->date1; } /** * @param \DateTime $date1 */ public function setDate1(DateTime $date1) { $this->date1 = $date1; } /** * @return \DateTime */ public function getDate2() { return $this->date2; } /** * @param \DateTime $date2 */ public function setDate2(DateTime $date2) { $this->date2 = $date2; } /** * @return get current task */ public function getTask() { return $this->task; } } ?> I don't think posting the views is necessary for the question but here is atleast how the links are made. ...and... id", $row-title); ? Now there's no error in the code but it doesn't do what I expect of it and the reason I assume why is because that each time a function of the script is called via a new page it is NOT the same instance of the script called previously so any previously created objects are no longer there. This confuses me and leaves me quite unsure of how to implement this gracefully. Some ways I would guess of how to do this is by passing the necessary data through the URL or have data saved in a database and retrieve it later to compare the times. What would be a recommended way to do, not just this, but anything that needs previously created data? Also, am I correct to think that a script is only 'alive' for one webpage at a time? Thanks!

  Read the article

• How to use jQuery Date Range Picker plugin in asp.net

  - by alaa9jo

  I stepped by this page: http://www.filamentgroup.com/lab/date_range_picker_using_jquery_ui_16_and_jquery_ui_css_framework/ and let me tell you,this is one of the best and coolest daterangepicker in the web in my opinion,they did a great job with extending the original jQuery UI DatePicker.Of course I made enhancements to the original plugin (fixed few bugs) and added a new option (Clear) to clear the textbox. In this article I well use that updated plugin and show you how to use it in asp.net..you will definitely like it. So,What do I need? 1- jQuery library : you can use 1.3.2 or 1.4.2 which is the latest version so far,in my article I will use the latest version. 2- jQuery UI library (1.8): As I mentioned earlier,daterangepicker plugin is based on the original jQuery UI DatePicker so that library should be included into your page. 3- jQuery DateRangePicker plugin : you can go to the author page or use the modified one (it's included in the attachment),in this article I will use the modified one. 4- Visual Studio 2005 or later : very funny :D,in my article I will use VS 2008. Note: in the attachment,I included all CSS and JS files so don't worry. How to use it? First thing,you will have to include all of the CSS and JS files into your page like this: <script src="Scripts/jquery-1.4.2.min.js" type="text/javascript"></script> <script src="Scripts/jquery-ui-1.8.custom.min.js" type="text/javascript"></script> <script src="Scripts/daterangepicker.jQuery.js" type="text/javascript"></script> <link href="CSS/redmond/jquery-ui-1.8.custom.css" rel="stylesheet" type="text/css" /> <link href="CSS/ui.daterangepicker.css" rel="stylesheet" type="text/css" /> <style type="text/css"> .ui-daterangepicker { font-size: 10px; } </style> Then add this html: <asp:TextBox ID="TextBox1" runat="server" Font-Size="10px"></asp:TextBox><asp:Button ID="SubmitButton" runat="server" Text="Submit" OnClick="SubmitButton_Click" /> <span>First Date:</span><asp:Label ID="FirstDate" runat="server"></asp:Label> <span>Second Date:</span><asp:Label ID="SecondDate" runat="server"></asp:Label> As you can see,it includes TextBox1 which we are going to attach the daterangepicker to it,2 labels to show you later on by code on how to read the date from the textbox and set it to the labels Now we have to attach the daterangepicker to the textbox by using jQuery (Note:visit the author's website for more info on daterangerpicker's options and how to use them): <script type="text/javascript"> $(function() { $("#<%= TextBox1.ClientID %>").attr("readonly", "readonly"); $("#<%= TextBox1.ClientID %>").attr("unselectable", "on"); $("#<%= TextBox1.ClientID %>").daterangepicker({ presetRanges: [], arrows: true, dateFormat: 'd M, yy', clearValue: '', datepickerOptions: { changeMonth: true, changeYear: true} }); }); </script> Finally,add this C# code: protected void SubmitButton_Click(object sender, EventArgs e) { if (TextBox1.Text.Trim().Length == 0) { return; } string selectedDate = TextBox1.Text; if (selectedDate.Contains("-")) { DateTime startDate; DateTime endDate; string[] splittedDates = selectedDate.Split("-".ToCharArray(), StringSplitOptions.RemoveEmptyEntries); if (splittedDates.Count() == 2 && DateTime.TryParse(splittedDates[0], out startDate) && DateTime.TryParse(splittedDates[1], out endDate)) { FirstDate.Text = startDate.ToShortDateString(); SecondDate.Text = endDate.ToShortDateString(); } else { //maybe the client has modified/altered the input i.e. hacking tools } } else { DateTime selectedDateObj; if (DateTime.TryParse(selectedDate, out selectedDateObj)) { FirstDate.Text = selectedDateObj.ToShortDateString(); SecondDate.Text = string.Empty; } else { //maybe the client has modified/altered the input i.e. hacking tools } } } This is the way on how to read from the textbox,That's it!. FAQ: 1-Why did you add this code?: <style type="text/css"> .ui-daterangepicker { font-size: 10px; } </style> A:For two reasons: 1)To show the Daterangepicker in a smaller size because it's original size is huge 2)To show you how to control the size of it. 2- Can I change the theme? A: yes you can,you will notice that I'm using Redmond theme which you will find it in jQuery UI website,visit their website and download a different theme,you may also have to make modifications to the css of daterangepicker,it's all yours. 3- Why did you add a font size to the textbox? A: To make the design look better,try to remove it and see by your self. 4- Can I register the script at codebehind? A: yes you can 5- I see you have added these two lines,what they do? $("#<%= TextBox1.ClientID %>").attr("readonly", "readonly"); $("#<%= TextBox1.ClientID %>").attr("unselectable", "on"); A:The first line will make the textbox not editable by the user,the second will block the blinking typing cursor from appearing if the user clicked on the textbox,you will notice that both lines are necessary to be used together,you can't just use one of them...for logical reasons of course. Finally,I hope everyone liked the article and as always,your feedbacks are always welcomed and if anyone have any suggestions or made any modifications that might be useful for anyone else then please post it at at the author's website and post a reference to your post here.

  Read the article

• SQL SERVER – Disabled Index and Update Statistics

  - by pinaldave

  When we try to update the statistics, it throws an error as if the clustered index is disabled. Now let us enable the clustered index only and attempt to update the statistics of the table right after that. Have you ever come across the situation where a conversation never gets over and it continues even though original point of discussion has passed. I am facing the same situation in the case of Disabled Index. Here is the link to original conversations. SQL SERVER – Disable Clustered Index and Data Insert – Reader had a issue here with Disabled Index SQL SERVER – Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index – Reader asked the effect of Rebuilding Indexes The same reader asked me today – “I understood what the disabled indexes do; what is their effect on statistics. Is it true that even though indexes are disabled, they continue updating the statistics?“ The answer is very interesting: If you have disabled clustered index, you will be not able to update the statistics at all for any index. If you have enabled clustered index and disabled non clustered index when you update the statistics of the table, it automatically updates the statistics of the ALL (disabled and enabled – both) the indexes on the table. If you are not satisfied with the answer, let us go over a simple example. I have written necessary comments in the code itself to have a clear idea. USE tempdb GO -- Drop Table if Exists IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[TableName]') AND type IN (N'U')) DROP TABLE [dbo].[TableName] GO -- Create Table CREATE TABLE [dbo].[TableName]( [ID] [int] NOT NULL, [FirstCol] [varchar](50) NULL ) GO -- Insert Some data INSERT INTO TableName SELECT 1, 'First' UNION ALL SELECT 2, 'Second' UNION ALL SELECT 3, 'Third' UNION ALL SELECT 4, 'Fourth' UNION ALL SELECT 5, 'Five' GO -- Create Clustered Index ALTER TABLE [TableName] ADD CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED ([ID] ASC) GO -- Create Nonclustered Index CREATE UNIQUE NONCLUSTERED INDEX [IX_NonClustered_TableName] ON [dbo].[TableName] ([FirstCol] ASC) GO -- Check that all the indexes are enabled SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled FROM sys.indexes WHERE OBJECT_NAME(OBJECT_ID) = 'TableName' GO Now let us update the statistics of the table and check the statistics update date. -- Update the stats of table UPDATE STATISTICS TableName WITH FULLSCAN GO -- Check Statistics Last Updated Datetime SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('TableName') GO Now let us disable the indexes and check if they are disabled using sys.indexes. -- Disable Indexes -- Disable Nonclustered Index ALTER INDEX [IX_NonClustered_TableName] ON [dbo].[TableName] DISABLE GO -- Disable Clustered Index ALTER INDEX [PK_TableName] ON [dbo].[TableName] DISABLE GO -- Check that all the indexes are disabled SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled FROM sys.indexes WHERE OBJECT_NAME(OBJECT_ID) = 'TableName' GO Let us try to update the statistics of the table. -- Update the stats of table UPDATE STATISTICS TableName WITH FULLSCAN GO /* -- Above operation should thrown following error Msg 1974, Level 16, State 1, Line 1 Cannot perform the specified operation on table 'TableName' because its clustered index 'PK_TableName' is disabled. */ When we try to update the statistics it throws an error as it clustered index is disabled. Now let us enable the clustered index only and attempt to update the statistics of the table right after that. -- Now let us rebuild clustered index only ALTER INDEX [PK_TableName] ON [dbo].[TableName] REBUILD GO -- Check that all the indexes status SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled FROM sys.indexes WHERE OBJECT_NAME(OBJECT_ID) = 'TableName' GO -- Check Statistics Last Updated Datetime SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('TableName') GO -- Update the stats of table UPDATE STATISTICS TableName WITH FULLSCAN GO -- Check Statistics Last Updated Datetime SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('TableName') GO We can clearly see that even though the nonclustered index is disabled it is also updated. If you do not need a nonclustered index, I suggest you to drop it as keeping them disabled is an overhead on your system. This is because every time the statistics are updated for system all the statistics for disabled indexesare also updated. -- Clean up DROP TABLE [TableName] GO The complete script is given below for easy reference. USE tempdb GO -- Drop Table if Exists IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[TableName]') AND type IN (N'U')) DROP TABLE [dbo].[TableName] GO -- Create Table CREATE TABLE [dbo].[TableName]( [ID] [int] NOT NULL, [FirstCol] [varchar](50) NULL ) GO -- Insert Some data INSERT INTO TableName SELECT 1, 'First' UNION ALL SELECT 2, 'Second' UNION ALL SELECT 3, 'Third' UNION ALL SELECT 4, 'Fourth' UNION ALL SELECT 5, 'Five' GO -- Create Clustered Index ALTER TABLE [TableName] ADD CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED ([ID] ASC) GO -- Create Nonclustered Index CREATE UNIQUE NONCLUSTERED INDEX [IX_NonClustered_TableName] ON [dbo].[TableName] ([FirstCol] ASC) GO -- Check that all the indexes are enabled SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled FROM sys.indexes WHERE OBJECT_NAME(OBJECT_ID) = 'TableName' GO -- Update the stats of table UPDATE STATISTICS TableName WITH FULLSCAN GO -- Check Statistics Last Updated Datetime SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('TableName') GO -- Disable Indexes -- Disable Nonclustered Index ALTER INDEX [IX_NonClustered_TableName] ON [dbo].[TableName] DISABLE GO -- Disable Clustered Index ALTER INDEX [PK_TableName] ON [dbo].[TableName] DISABLE GO -- Check that all the indexes are disabled SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled FROM sys.indexes WHERE OBJECT_NAME(OBJECT_ID) = 'TableName' GO -- Update the stats of table UPDATE STATISTICS TableName WITH FULLSCAN GO /* -- Above operation should thrown following error Msg 1974, Level 16, State 1, Line 1 Cannot perform the specified operation on table 'TableName' because its clustered index 'PK_TableName' is disabled. */ -- Now let us rebuild clustered index only ALTER INDEX [PK_TableName] ON [dbo].[TableName] REBUILD GO -- Check that all the indexes status SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled FROM sys.indexes WHERE OBJECT_NAME(OBJECT_ID) = 'TableName' GO -- Check Statistics Last Updated Datetime SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('TableName') GO -- Update the stats of table UPDATE STATISTICS TableName WITH FULLSCAN GO -- Check Statistics Last Updated Datetime SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('TableName') GO -- Clean up DROP TABLE [TableName] GO Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL Index, SQL Optimization, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: SQL Statistics

  Read the article

< Previous Page | 42 43 44 45 46 47 48 49 50 51 52 53  | Next Page >