Search Results

Search found 23613 results on 945 pages for 'query parameters'.

Page 51/945 | < Previous Page | 47 48 49 50 51 52 53 54 55 56 57 58  | Next Page >

• SQL SERVER – Weekly Series – Memory Lane – #037

  - by Pinal Dave

  Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane. 2007 Convert Text to Numbers (Integer) – CAST and CONVERT If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function. List All Stored Procedure Modified in Last N Days If SQL Server suddenly start behaving in un-expectable behavior and if stored procedure were changed recently, following script can be used to check recently modified stored procedure. If a stored procedure was created but never modified afterwards modified date and create a date for that stored procedure are same. Count Duplicate Records – Rows Validate Field For DATE datatype using function ISDATE() We always checked DATETIME field for incorrect data type. One of the user input date as 30/2/2007. The date was sucessfully inserted in the temp table but while inserting from temp table to final table it crashed with error. We had now task to validate incorrect date value before we insert in final table. Jr. Developer asked me how can he do that? We check for incorrect data type (varchar, int, NULL) but this is incorrect date value. Regular expression works fine with them because of mm/dd/yyyy format. 2008 Find Space Used For Any Particular Table It is very simple to find out the space used by any table in the database. Two Convenient Features Inline Assignment – Inline Operations Here is the script which does both – Inline Assignment and Inline Operation DECLARE @idx INT = 0 SET @idx+=1 SELECT @idx Introduction to SPARSE Columns SPARSE column are better at managing NULL and ZERO values in SQL Server. It does not take any space in database at all. If column is created with SPARSE clause with it and it contains ZERO or NULL it will be take lesser space then regular column (without SPARSE clause). SP_CONFIGURE – Displays or Changes Global Configuration Settings If advanced settings are not enabled at configuration level SQL Server will not let user change the advanced features on server. Authorized user can turn on or turn off advance settings. 2009 Standby Servers and Types of Standby Servers Standby Server is a type of server that can be brought online in a situation when Primary Server goes offline and application needs continuous (high) availability of the server. There is always a need to set up a mechanism where data and objects from primary server are moved to secondary (standby) server. BLOB – Pointer to Image, Image in Database, FILESTREAM Storage When it comes to storing images in database there are two common methods. I had previously blogged about the same subject on my visit to Toronto. With SQL Server 2008, we have a new method of FILESTREAM storage. However, the answer on when to use FILESTREAM and when to use other methods is still vague in community. 2010 Upper Case Shortcut SQL Server Management Studio I select the word and hit CTRL+SHIFT+U and it SSMS immediately changes the case of the selected word. Similar way if one want to convert cases to lower case, another short cut CTRL+SHIFT+L is also available. The Self Join – Inner Join and Outer Join Self Join has always been a noteworthy case. It is interesting to ask questions about self join in a room full of developers. I often ask – if there are three kinds of joins, i.e.- Inner Join, Outer Join and Cross Join; what type of join is Self Join? The usual answer is that it is an Inner Join. However, the reality is very different. Parallelism – Row per Processor – Row per Thread – Thread 0  If you look carefully in the Properties window or XML Plan, there is “Thread 0?. What does this “Thread 0” indicate? Well find out from the blog post. How do I Learn and How do I Teach The blog post has raised three very interesting questions. How do you learn? How do you teach? What are you learning or teaching? Let me try to answer the same. 2011 SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 7 of 31 What are Different Types of Locks? What are Pessimistic Lock and Optimistic Lock? When is the use of UPDATE_STATISTICS command? What is the Difference between a HAVING clause and a WHERE clause? What is Connection Pooling and why it is Used? What are the Properties and Different Types of Sub-Queries? What are the Authentication Modes in SQL Server? How can it be Changed? SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 8 of 31 Which Command using Query Analyzer will give you the Version of SQL Server and Operating System? What is an SQL Server Agent? Can a Stored Procedure call itself or a Recursive Stored Procedure? How many levels of SP nesting is possible? What is Log Shipping? Name 3 ways to get an Accurate Count of the Number of Records in a Table? What does it mean to have QUOTED_IDENTIFIER ON? What are the Implications of having it OFF? What is the Difference between a Local and a Global Temporary Table? What is the STUFF Function and How Does it Differ from the REPLACE Function? What is PRIMARY KEY? What is UNIQUE KEY Constraint? What is FOREIGN KEY? SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 9 of 31 What is CHECK Constraint? What is NOT NULL Constraint? What is the difference between UNION and UNION ALL? What is B-Tree? How to get @@ERROR and @@ROWCOUNT at the Same Time? What is a Scheduled Job or What is a Scheduled Task? What are the Advantages of Using Stored Procedures? What is a Table Called, if it has neither Cluster nor Non-cluster Index? What is it Used for? Can SQL Servers Linked to other Servers like Oracle? What is BCP? When is it Used? SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 10 of 31 What Command do we Use to Rename a db, a Table and a Column? What are sp_configure Commands and SET Commands? How to Implement One-to-One, One-to-Many and Many-to-Many Relationships while Designing Tables? What is Difference between Commit and Rollback when Used in Transactions? What is an Execution Plan? When would you Use it? How would you View the Execution Plan? SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 11 of 31 What is Difference between Table Aliases and Column Aliases? Do they Affect Performance? What is the difference between CHAR and VARCHAR Datatypes? What is the Difference between VARCHAR and VARCHAR(MAX) Datatypes? What is the Difference between VARCHAR and NVARCHAR datatypes? Which are the Important Points to Note when Multilanguage Data is Stored in a Table? How to Optimize Stored Procedure Optimization? What is SQL Injection? How to Protect Against SQL Injection Attack? How to Find Out the List Schema Name and Table Name for the Database? What is CHECKPOINT Process in the SQL Server? SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 12 of 31 How does Using a Separate Hard Drive for Several Database Objects Improves Performance Right Away? How to Find the List of Fixed Hard Drive and Free Space on Server? Why can there be only one Clustered Index and not more than one? What is Difference between Line Feed (\n) and Carriage Return (\r)? Is It Possible to have Clustered Index on Separate Drive From Original Table Location? What is a Hint? How to Delete Duplicate Rows? Why the Trigger Fires Multiple Times in Single Login? 2012 CTRL+SHIFT+] Shortcut to Select Code Between Two Parenthesis Shortcut key is CTRL+SHIFT+]. This key can be very useful when dealing with multiple subqueries, CTE or query with multiple parentheses. When exercised this shortcut key it selects T-SQL code between two parentheses. Monday Morning Puzzle – Query Returns Results Sometimes but Not Always I am beginner with SQL Server. I have one query, it sometime returns a result and sometime it does not return me the result. Where should I start looking for a solution and what kind of information I should send to you so you can help me with solving. I have no clue, please guide me. Remove Debug Button in SSMS – SQL in Sixty Seconds #020 – Video Effect of Case Sensitive Collation on Resultset Collation is a very interesting concept but I quite often see it is heavily neglected. I have seen developer and DBA looking for a workaround to fix collation error rather than understanding if the side effect of the workaround. Switch Between Two Parenthesis using Shortcut CTRL+] Earlier this week I wrote a blog post about CTRL+SHIFT+] Shortcut to Select Code Between Two Parenthesis, I received quite a lot of positive feedback from readers. If you are a regular reader of the blog post, you must be aware that I appreciate the learning shared by readers. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Memory Lane, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• How do I break down MySQL query results into categories, each with a specific number of rows?

  - by Mel

  Hello, Problem: I want to list n number of games from each genre (order not important) The following MySQL query resides inside a ColdFusion function. It is meant to list all games under a platform (for example, list all PS3 games; list all Xbox 360 games; etc...). The variable for PlatformID is passed through the URL. I have 9 genres, and I would like to list 10 games from each genre. SELECT games.GameID AS GameID, games.GameReleaseDate AS rDate, titles.TitleName AS tName, titles.TitleShortDescription AS sDesc, genres.GenreName AS gName, platforms.PlatformID, platforms.PlatformName AS pName, platforms.PlatformAbbreviation AS pAbbr FROM (((games join titles on((games.TitleID = titles.TitleID))) join genres on((genres.GenreID = games.GenreID))) join platforms on((platforms.PlatformID = games.PlatformID))) WHERE (games.PlatformID = '#ARGUMENTS.PlatformID#') ORDER BY GenreName ASC, GameReleaseDate DESC Once the query results come back I group them in ColdFusion as follows: <cfoutput query="ListGames" group="gName"> (first loop which lists genres) #ListGames.gName# <cfoutput> (nested loop which lists games) #ListGames.tName# </cfoutput> </cfoutput> The problem is that I only want 10 games from each genre to be listed. If I place a "limit" of 50 in the SQL, I will get ~ 50 games of the same genre (depending on how much games of that genre there are). The second issue is I don't want the overload of querying the database for all games when each person will only look at a few. What is the correct way to do this? Many thanks!

  Read the article

• Does query plan optimizer works well with joined/filtered table-valued functions?

  - by smoothdeveloper

  In SQLSERVER 2005, I'm using table-valued function as a convenient way to perform arbitrary aggregation on subset data from large table (passing date range or such parameters). I'm using theses inside larger queries as joined computations and I'm wondering if the query plan optimizer work well with them in every condition or if I'm better to unnest such computation in my larger queries. Does query plan optimizer unnest table-valued functions if it make sense? If it doesn't, what do you recommend to avoid code duplication that would occur by manually unnesting them? If it does, how do you identify that from the execution plan? code sample: create table dbo.customers ( [key] uniqueidentifier , constraint pk_dbo_customers primary key ([key]) ) go /* assume large amount of data */ create table dbo.point_of_sales ( [key] uniqueidentifier , customer_key uniqueidentifier , constraint pk_dbo_point_of_sales primary key ([key]) ) go create table dbo.product_ranges ( [key] uniqueidentifier , constraint pk_dbo_product_ranges primary key ([key]) ) go create table dbo.products ( [key] uniqueidentifier , product_range_key uniqueidentifier , release_date datetime , constraint pk_dbo_products primary key ([key]) , constraint fk_dbo_products_product_range_key foreign key (product_range_key) references dbo.product_ranges ([key]) ) go . /* assume large amount of data */ create table dbo.sales_history ( [key] uniqueidentifier , product_key uniqueidentifier , point_of_sale_key uniqueidentifier , accounting_date datetime , amount money , quantity int , constraint pk_dbo_sales_history primary key ([key]) , constraint fk_dbo_sales_history_product_key foreign key (product_key) references dbo.products ([key]) , constraint fk_dbo_sales_history_point_of_sale_key foreign key (point_of_sale_key) references dbo.point_of_sales ([key]) ) go create function dbo.f_sales_history_..snip.._date_range ( @accountingdatelowerbound datetime, @accountingdateupperbound datetime ) returns table as return ( select pos.customer_key , sh.product_key , sum(sh.amount) amount , sum(sh.quantity) quantity from dbo.point_of_sales pos inner join dbo.sales_history sh on sh.point_of_sale_key = pos.[key] where sh.accounting_date between @accountingdatelowerbound and @accountingdateupperbound group by pos.customer_key , sh.product_key ) go -- TODO: insert some data -- this is a table containing a selection of product ranges declare @selectedproductranges table([key] uniqueidentifier) -- this is a table containing a selection of customers declare @selectedcustomers table([key] uniqueidentifier) declare @low datetime , @up datetime -- TODO: set top query parameters . select saleshistory.customer_key , saleshistory.product_key , saleshistory.amount , saleshistory.quantity from dbo.products p inner join @selectedproductranges productrangeselection on p.product_range_key = productrangeselection.[key] inner join @selectedcustomers customerselection on 1 = 1 inner join dbo.f_sales_history_..snip.._date_range(@low, @up) saleshistory on saleshistory.product_key = p.[key] and saleshistory.customer_key = customerselection.[key] I hope the sample makes sense. Much thanks for your help!

  Read the article

• SQL SERVER – Detect Virtual Log Files (VLF) in LDF

  - by pinaldave

  In one of the recent training engagements, I was asked if it true that there are multiple small log files in the large log file (LDF). I found this question very interesting as the answer is yes. Multiple small Virtual Log Files commonly known as VLFs together make an LDF file. The writing of the VLF is sequential and resulting in the writing of the LDF file is sequential as well. This leads to another talk that one does not need more than one log file in most cases. However, in short, you can use following DBCC command to know how many Virtual Log Files or VLFs are present in your log file. DBCC LOGINFO You can find the result of above query to something as displayed in following image. You can see the column which is marked as 2 which means it is active VLF and the one with 0 which is inactive VLF. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• Big Data – Buzz Words: What is NewSQL – Day 10 of 21

  - by Pinal Dave

  In yesterday’s blog post we learned the importance of the relational database. In this article we will take a quick look at the what is NewSQL. What is NewSQL? NewSQL stands for new scalable and high performance SQL Database vendors. The products sold by NewSQL vendors are horizontally scalable. NewSQL is not kind of databases but it is about vendors who supports emerging data products with relational database properties (like ACID, Transaction etc.) along with high performance. Products from NewSQL vendors usually follow in memory data for speedy access as well are available immediate scalability. NewSQL term was coined by 451 groups analyst Matthew Aslett in this particular blog post. On the definition of NewSQL, Aslett writes: “NewSQL” is our shorthand for the various new scalable/high performance SQL database vendors. We have previously referred to these products as ‘ScalableSQL‘ to differentiate them from the incumbent relational database products. Since this implies horizontal scalability, which is not necessarily a feature of all the products, we adopted the term ‘NewSQL’ in the new report. And to clarify, like NoSQL, NewSQL is not to be taken too literally: the new thing about the NewSQL vendors is the vendor, not the SQL. In other words - NewSQL incorporates the concepts and principles of Structured Query Language (SQL) and NoSQL languages. It combines reliability of SQL with the speed and performance of NoSQL. Categories of NewSQL There are three major categories of the NewSQL New Architecture – In this framework each node owns a subset of the data and queries are split into smaller query to sent to nodes to process the data. E.g. NuoDB, Clustrix, VoltDB MySQL Engines – Highly Optimized storage engine for SQL with the interface of MySQ Lare the example of such category. E.g. InnoDB, Akiban Transparent Sharding – This system automatically split database across multiple nodes. E.g. Scalearc  Summary In simple words – NewSQL is kind of database following relational database principals and provides scalability like NoSQL. Tomorrow In tomorrow’s blog post we will discuss about the Role of Cloud Computing in Big Data. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Big Data, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL

  Read the article

• Did You Know? More online seminars!

  - by Kalen Delaney

  I am in Tucson again, having just recorded two more online workshops to be broadcast by SSWUG. We haven't set the dates yet, but we are thinking about offering a special package deal for the two of them. The topics really are related and I think they would work well together. They are both on aspects of Query Processing. The first was on how to interpret Query Plans and is an introduction to the topic. However, it only includes a discussion of how SQL Server actually processes your queries. For example,...(read more)

  Read the article

• SQL SERVER – Retrieving Random Rows from Table Using NEWID()

  - by pinaldave

  I have previously written about how to get random rows from SQL Server. SQL SERVER – Generate A Single Random Number for Range of Rows of Any Table – Very interesting Question from Reader SQL SERVER – Random Number Generator Script – SQL Query However, I have not blogged about following trick before. Let me share the trick here as well. You can generate random scripts using following methods as well. USE AdventureWorks2012 GO -- Method 1 SELECT TOP 100 * FROM Sales.SalesOrderDetail ORDER BY NEWID() GO -- Method 2 SELECT TOP 100 * FROM Sales.SalesOrderDetail ORDER BY CHECKSUM(NEWID()) GO You will notice that using NEWID() in the ORDER BY will return random rows in the result set. How many of you knew this trick? You can run above script multiple times and it will give random rows every single time. Reference: Pinal Dave (http://blog.sqlauthority.com)   Filed under: PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• Mysql: ROLLBACK for multiple queries

  - by Raj

  Hi I have more than three MySql queiries in a PHP script triggered by scheduled task. If a query catch an error, script throw an exception and rollback that Mysql query. It works fine. However if first query works fine, but not 2nd query, throw an exception, it rollback 2nd one but not 1st query. I am using begin_trans(), commit and rollback() for individual queries because Sometimes i need to rollback one query, sometimes all queries. Is there any way to rollback one query or all queries? Thanks in advance UPDATE: I got it working, there was no problem with in begin_trans(), commit and rollback(), the database connection config was different for one query from other queries, crazy code without any comments!!!

  Read the article

• Help with MySQL query... Need help ordering a group of rows

  - by user156814

  I can tell it best by explaining the query I have, and what I need. I need to be able to get a group of items from the database, grouped by category, manufacturer, and year made. The groupings need to be sorted based on total amount of items within the group. This part is done with the query below. Secondly, I need to be able to show an image of the most expensive item out of the group, which is why I use MAX(items.current_price). I thought MAX() gets the ENTIRE row corresponding to the largest column value. I was wrong, as MAX only gets the numeric value of the largest price. So the query doesnt work well for that. SELECT items.id, items.year, items.manufacturer, COUNT(items.id) AS total, MAX(items.current_price) AS price, items.gallery_url, FROM ebay AS items WHERE items.primary_category_id = 213 AND items.year <> '' AND items.manufacturer <> '' AND items.bad_item <> 1 GROUP BY items.primary_category_id, items.manufacturer, items.year ORDER BY total DESC, price ASC LIMIT 10 if that doesnt explain it well, the results should be something like this id 10548 year 1989 manufacturer bowman total 451 price 8500.00 (The price of the most expensive item in the table/ not the price of item 10548) gallery_url http://ebay.xxxxx (The image of item 10548) A little help please. Thanks

  Read the article

• how to diffrentiate between same field names of two tables in a select query??

  - by developer

  i have more than two tables in my database and all of them contains same field names like table A table B table C field1 field1 field1 field2 field2 field2 field3 field3 field3 . . . . . . . . . . . . I have to write a SELECT query which gets almost all same fields from these 3 tables.Iam using something like this :- select a.field1,a.field2,a.field3,b.field1,b.field2,b.field3,c.field1,c.field2,c.field3 from table A as a, table B as b,table C as c where so and so. but when i print field1's value it gives me the last table values. How can i get all the values of three tables with the same field names??? do i have to write individual query for every table OR there is any ways of fetching them all in a single query????

  Read the article

• How to remove/hide <div></div> tags only without the content?

  - by candies

  For example I have: <div id ="test">[the content here]</div> The content within the div tags will appear after I called the id of div using ajax. This is the code: function dinamic(add) { var kode = add.value; if (!kode) return; xmlhttp2.open('get', '../template/get_id.php?kode='+kode, true); xmlhttp2.onreadystatechange = function() { if ((xmlhttp2.readyState == 4) && (xmlhttp2.status == 200)) { var add = document.getElementById("test"); add.innerHTML = xmlhttp2.responseText; } return false; } xmlhttp2.send(null); } So it will appear <div id="test">A</div> I'd like to put the content of div - A into mysql query. $test = $_GET['test']; $query = "select * from example where category='$test'"; I've tried to make variable $test of the div id to get the content but result of the query in category is none. I tried again, I put the div in to the query $query = "select * from example where category='<div id=\"test\">A</div>'"; Yes, It works. But when I did query on navicat, no results I got because there's spaces between A that is <div> and </div>. How to remove/hide the div tags only so its only appear the content? > $query = "select * from example where category='A'"; < Edit: If I echo the query on firefox browser will say "$query = "select * from example where category='[space]A[space]'";" And look at the bug(I use firebug), it will say "$query = "select * from example where category='<div id="test">A</div>'";" So my guessing why can't get result after query on navicat is there's spaces between A([space]A[space]), just have no idea how to remove/hide the div tags, I want to get this result only "$query = "select * from example where category='A'";" Thanks.

  Read the article

• Why would using a Temp table be faster than a nested query?

  - by Mongus Pong

  We are trying to optimise some of our queries. One query is doing the following: SELECT t.TaskID, t.Name as Task, '' as Tracker, t.ClientID, (<complex subquery>) Date, INTO [#Gadget] FROM task t SELECT TOP 500 TaskID, Task, Tracker, ClientID, dbo.GetClientDisplayName(ClientID) as Client FROM [#Gadget] order by CASE WHEN Date IS NULL THEN 1 ELSE 0 END , Date ASC DROP TABLE [#Gadget] (I have removed the complex subquery, cos I dont think its relevant other than to explain why this query has been done as a two stage process.) Now I would have thought it would be far more efficient to merge this down into a single query using subqueries as : SELECT TOP 500 TaskID, Task, Tracker, ClientID, dbo.GetClientDisplayName(ClientID) FROM ( SELECT t.TaskID, t.Name as Task, '' as Tracker, t.ClientID, (<complex subquery>) Date, FROM task t ) as sub order by CASE WHEN Date IS NULL THEN 1 ELSE 0 END , Date ASC This would give the optimiser better information to work out what was going on and avoid any temporary tables. It should be faster. But it turns out it is a lot slower. 8 seconds vs under 5 seconds. I cant work out why this would be the case as all my knowledge of databases imply that subqueries would always be faster than using temporary tables. Can anyone explain what could be going on!?!?

  Read the article

• mysql query trying to search by alias involving CASES and aggregate functions UGH!

  - by dqhendricks

  I have two tables left joined. The query is grouped by the left table's ID column. The right table has a date column called close_date. The problem is, if there are any right table records that have not been closed (thus having a close_date of 0000-00-00), then I do not want any of the left table records to be shown, and if there are NO right table records with a close_date of 0000-00-00, I would like only the right table record with the MAX close date to be returned. So for simplicity sake, let's say the tables look like this: Table1 id 1 2 Table2 table1_id | close_date 1 | 0000-00-00 1 | 2010-01-01 2 | 2010-01-01 2 | 2010-01-02 I would like the query to only return this: Table1.id | Table2.close_date 2 | 2010-01-02 I tried to come up with an answer using aliased CASES and aggregate functions, but I could not search by the result, and I was attempting not to make a 3 mile long query to solve the problem. I looked through a few of the related posts on here, but none seem to meet the criteria of this particular case. Any pushes in the right direction would be greatly appreciated. Thanks!

  Read the article

• PHP PDO Parameters from a function returned array

  - by noko

  I've got a function written that runs a query based on parameters passed to the function. I can't seem to figure out why doing the following returns a result: function test($function_returned_array) { $variable = 'Hello World'; $sql = 'SELECT `name`, `pid` FROM `products` WHERE `name` IN (?)'; $found = $this->db->get_array($sql, $variable); } While this doesn't return any results: function test2($function_returned_array) { $sql = 'SELECT `name`, `pid` FROM `products` WHERE `name` IN (?)'; $found = $this->db->get_array($sql, $function_returned_array[0]); } $function_returned_array[0] is also equal to 'Hello World'. Shouldn't they both return the same results? When I echo the values of $variable and $function_returned_array[0], they are both 'Hello World' Here's the relevant parts of my PDO wrapper: public function query(&$query, $params) { $sth = $this->_db->prepare($query); if(is_null($params)) { $sth->execute(); } else if(is_array($params)) { $sth->execute($params); } else { $sth->execute(array($params)); } $this->_rows = $sth->rowCount(); $this->_counter++; return $sth; } public function get_array(&$query, $params, $style = PDO::FETCH_ASSOC) { $q = $this->query($query, $params); return $q->fetchAll($style); } I'm using PHP 5.3.5. Any help would be appreciated.

  Read the article

• Convert from Procedural to Object Oriented Code

  - by Anthony

  I have been reading Working Effectively with Legacy Code and Clean Code with the goal of learning strategies on how to begin cleaning up the existing code-base of a large ASP.NET webforms application. This system has been around since 2005 and since then has undergone a number of enhancements. Originally the code was structured as follows (and is still largely structured this way): ASP.NET (aspx/ascx) Code-behind (c#) Business Logic Layer (c#) Data Access Layer (c#) Database (Oracle) The main issue is that the code is procedural masquerading as object-oriented. It virtually violates all of the guidelines described in both books. This is an example of a typical class in the Business Logic Layer: public class AddressBO { public TransferObject GetAddress(string addressID) { if (StringUtils.IsNull(addressID)) { throw new ValidationException("Address ID must be entered"); } AddressDAO addressDAO = new AddressDAO(); return addressDAO.GetAddress(addressID); } public TransferObject Insert(TransferObject addressDetails) { if (StringUtils.IsNull(addressDetails.GetString("EVENT_ID")) || StringUtils.IsNull(addressDetails.GetString("LOCALITY")) || StringUtils.IsNull(addressDetails.GetString("ADDRESS_TARGET")) || StringUtils.IsNull(addressDetails.GetString("ADDRESS_TYPE_CODE")) || StringUtils.IsNull(addressDetails.GetString("CREATED_BY"))) { throw new ValidationException( "You must enter an Event ID, Locality, Address Target, Address Type Code and Created By."); } string addressID = Sequence.GetNextValue("ADDRESS_ID_SEQ"); addressDetails.SetValue("ADDRESS_ID", addressID); string syncID = Sequence.GetNextValue("SYNC_ID_SEQ"); addressDetails.SetValue("SYNC_ADDRESS_ID", syncID); TransferObject syncDetails = new TransferObject(); Transaction transaction = new Transaction(); try { AddressDAO addressDAO = new AddressDAO(); addressDAO.Insert(addressDetails, transaction); // insert the record for the target TransferObject addressTargetDetails = new TransferObject(); switch (addressDetails.GetString("ADDRESS_TARGET")) { case "PARTY_ADDRESSES": { addressTargetDetails.SetValue("ADDRESS_ID", addressID); addressTargetDetails.SetValue("ADDRESS_TYPE_CODE", addressDetails.GetString("ADDRESS_TYPE_CODE")); addressTargetDetails.SetValue("PARTY_ID", addressDetails.GetString("PARTY_ID")); addressTargetDetails.SetValue("EVENT_ID", addressDetails.GetString("EVENT_ID")); addressTargetDetails.SetValue("CREATED_BY", addressDetails.GetString("CREATED_BY")); addressDAO.InsertPartyAddress(addressTargetDetails, transaction); break; } case "PARTY_CONTACT_ADDRESSES": { addressTargetDetails.SetValue("ADDRESS_ID", addressID); addressTargetDetails.SetValue("ADDRESS_TYPE_CODE", addressDetails.GetString("ADDRESS_TYPE_CODE")); addressTargetDetails.SetValue("PUBLIC_RELEASE_FLAG", addressDetails.GetString("PUBLIC_RELEASE_FLAG")); addressTargetDetails.SetValue("CONTACT_ID", addressDetails.GetString("CONTACT_ID")); addressTargetDetails.SetValue("EVENT_ID", addressDetails.GetString("EVENT_ID")); addressTargetDetails.SetValue("CREATED_BY", addressDetails.GetString("CREATED_BY")); addressDAO.InsertContactAddress(addressTargetDetails, transaction); break; } << many more cases here >> default: { break; } } // synchronise SynchronisationBO synchronisationBO = new SynchronisationBO(); syncDetails = synchronisationBO.Synchronise("I", transaction, "ADDRESSES", addressDetails.GetString("ADDRESS_TARGET"), addressDetails, addressTargetDetails); // commit transaction.Commit(); } catch (Exception) { transaction.Rollback(); throw; } return new TransferObject("ADDRESS_ID", addressID, "SYNC_DETAILS", syncDetails); } << many more methods are here >> } It has a lot of duplication, the class has a number of responsibilities, etc, etc - it is just generally 'un-clean' code. All of the code throughout the system is dependent on concrete implementations. This is an example of a typical class in the Data Access Layer: public class AddressDAO : GenericDAO { public static readonly string BASE_SQL_ADDRESSES = "SELECT " + " a.address_id, " + " a.event_id, " + " a.flat_unit_type_code, " + " fut.description as flat_unit_description, " + " a.flat_unit_num, " + " a.floor_level_code, " + " fl.description as floor_level_description, " + " a.floor_level_num, " + " a.building_name, " + " a.lot_number, " + " a.street_number, " + " a.street_name, " + " a.street_type_code, " + " st.description as street_type_description, " + " a.street_suffix_code, " + " ss.description as street_suffix_description, " + " a.postal_delivery_type_code, " + " pdt.description as postal_delivery_description, " + " a.postal_delivery_num, " + " a.locality, " + " a.state_code, " + " s.description as state_description, " + " a.postcode, " + " a.country, " + " a.lock_num, " + " a.created_by, " + " TO_CHAR(a.created_datetime, '" + SQL_DATETIME_FORMAT + "') as created_datetime, " + " a.last_updated_by, " + " TO_CHAR(a.last_updated_datetime, '" + SQL_DATETIME_FORMAT + "') as last_updated_datetime, " + " a.sync_address_id, " + " a.lat," + " a.lon, " + " a.validation_confidence, " + " a.validation_quality, " + " a.validation_status " + "FROM ADDRESSES a, FLAT_UNIT_TYPES fut, FLOOR_LEVELS fl, STREET_TYPES st, " + " STREET_SUFFIXES ss, POSTAL_DELIVERY_TYPES pdt, STATES s " + "WHERE a.flat_unit_type_code = fut.flat_unit_type_code(+) " + "AND a.floor_level_code = fl.floor_level_code(+) " + "AND a.street_type_code = st.street_type_code(+) " + "AND a.street_suffix_code = ss.street_suffix_code(+) " + "AND a.postal_delivery_type_code = pdt.postal_delivery_type_code(+) " + "AND a.state_code = s.state_code(+) "; public TransferObject GetAddress(string addressID) { //Build the SELECT Statement StringBuilder selectStatement = new StringBuilder(BASE_SQL_ADDRESSES); //Add WHERE condition selectStatement.Append(" AND a.address_id = :addressID"); ArrayList parameters = new ArrayList{DBUtils.CreateOracleParameter("addressID", OracleDbType.Decimal, addressID)}; // Execute the SELECT statement Query query = new Query(); DataSet results = query.Execute(selectStatement.ToString(), parameters); // Check if 0 or more than one rows returned if (results.Tables[0].Rows.Count == 0) { throw new NoDataFoundException(); } if (results.Tables[0].Rows.Count > 1) { throw new TooManyRowsException(); } // Return a TransferObject containing the values return new TransferObject(results); } public void Insert(TransferObject insertValues, Transaction transaction) { // Store Values string addressID = insertValues.GetString("ADDRESS_ID"); string syncAddressID = insertValues.GetString("SYNC_ADDRESS_ID"); string eventID = insertValues.GetString("EVENT_ID"); string createdBy = insertValues.GetString("CREATED_BY"); // postal delivery string postalDeliveryTypeCode = insertValues.GetString("POSTAL_DELIVERY_TYPE_CODE"); string postalDeliveryNum = insertValues.GetString("POSTAL_DELIVERY_NUM"); // unit/building string flatUnitTypeCode = insertValues.GetString("FLAT_UNIT_TYPE_CODE"); string flatUnitNum = insertValues.GetString("FLAT_UNIT_NUM"); string floorLevelCode = insertValues.GetString("FLOOR_LEVEL_CODE"); string floorLevelNum = insertValues.GetString("FLOOR_LEVEL_NUM"); string buildingName = insertValues.GetString("BUILDING_NAME"); // street string lotNumber = insertValues.GetString("LOT_NUMBER"); string streetNumber = insertValues.GetString("STREET_NUMBER"); string streetName = insertValues.GetString("STREET_NAME"); string streetTypeCode = insertValues.GetString("STREET_TYPE_CODE"); string streetSuffixCode = insertValues.GetString("STREET_SUFFIX_CODE"); // locality/state/postcode/country string locality = insertValues.GetString("LOCALITY"); string stateCode = insertValues.GetString("STATE_CODE"); string postcode = insertValues.GetString("POSTCODE"); string country = insertValues.GetString("COUNTRY"); // esms address string esmsAddress = insertValues.GetString("ESMS_ADDRESS"); //address/GPS string lat = insertValues.GetString("LAT"); string lon = insertValues.GetString("LON"); string zoom = insertValues.GetString("ZOOM"); //string validateDate = insertValues.GetString("VALIDATED_DATE"); string validatedBy = insertValues.GetString("VALIDATED_BY"); string confidence = insertValues.GetString("VALIDATION_CONFIDENCE"); string status = insertValues.GetString("VALIDATION_STATUS"); string quality = insertValues.GetString("VALIDATION_QUALITY"); // the insert statement StringBuilder insertStatement = new StringBuilder("INSERT INTO ADDRESSES ("); StringBuilder valuesStatement = new StringBuilder("VALUES ("); ArrayList parameters = new ArrayList(); // build the insert statement insertStatement.Append("ADDRESS_ID, EVENT_ID, CREATED_BY, CREATED_DATETIME, LOCK_NUM "); valuesStatement.Append(":addressID, :eventID, :createdBy, SYSDATE, 1 "); parameters.Add(DBUtils.CreateOracleParameter("addressID", OracleDbType.Decimal, addressID)); parameters.Add(DBUtils.CreateOracleParameter("eventID", OracleDbType.Decimal, eventID)); parameters.Add(DBUtils.CreateOracleParameter("createdBy", OracleDbType.Varchar2, createdBy)); // build the insert statement if (!StringUtils.IsNull(syncAddressID)) { insertStatement.Append(", SYNC_ADDRESS_ID"); valuesStatement.Append(", :syncAddressID"); parameters.Add(DBUtils.CreateOracleParameter("syncAddressID", OracleDbType.Decimal, syncAddressID)); } if (!StringUtils.IsNull(postalDeliveryTypeCode)) { insertStatement.Append(", POSTAL_DELIVERY_TYPE_CODE"); valuesStatement.Append(", :postalDeliveryTypeCode "); parameters.Add(DBUtils.CreateOracleParameter("postalDeliveryTypeCode", OracleDbType.Varchar2, postalDeliveryTypeCode)); } if (!StringUtils.IsNull(postalDeliveryNum)) { insertStatement.Append(", POSTAL_DELIVERY_NUM"); valuesStatement.Append(", :postalDeliveryNum "); parameters.Add(DBUtils.CreateOracleParameter("postalDeliveryNum", OracleDbType.Varchar2, postalDeliveryNum)); } if (!StringUtils.IsNull(flatUnitTypeCode)) { insertStatement.Append(", FLAT_UNIT_TYPE_CODE"); valuesStatement.Append(", :flatUnitTypeCode "); parameters.Add(DBUtils.CreateOracleParameter("flatUnitTypeCode", OracleDbType.Varchar2, flatUnitTypeCode)); } if (!StringUtils.IsNull(lat)) { insertStatement.Append(", LAT"); valuesStatement.Append(", :lat "); parameters.Add(DBUtils.CreateOracleParameter("lat", OracleDbType.Decimal, lat)); } if (!StringUtils.IsNull(lon)) { insertStatement.Append(", LON"); valuesStatement.Append(", :lon "); parameters.Add(DBUtils.CreateOracleParameter("lon", OracleDbType.Decimal, lon)); } if (!StringUtils.IsNull(zoom)) { insertStatement.Append(", ZOOM"); valuesStatement.Append(", :zoom "); parameters.Add(DBUtils.CreateOracleParameter("zoom", OracleDbType.Decimal, zoom)); } if (!StringUtils.IsNull(flatUnitNum)) { insertStatement.Append(", FLAT_UNIT_NUM"); valuesStatement.Append(", :flatUnitNum "); parameters.Add(DBUtils.CreateOracleParameter("flatUnitNum", OracleDbType.Varchar2, flatUnitNum)); } if (!StringUtils.IsNull(floorLevelCode)) { insertStatement.Append(", FLOOR_LEVEL_CODE"); valuesStatement.Append(", :floorLevelCode "); parameters.Add(DBUtils.CreateOracleParameter("floorLevelCode", OracleDbType.Varchar2, floorLevelCode)); } if (!StringUtils.IsNull(floorLevelNum)) { insertStatement.Append(", FLOOR_LEVEL_NUM"); valuesStatement.Append(", :floorLevelNum "); parameters.Add(DBUtils.CreateOracleParameter("floorLevelNum", OracleDbType.Varchar2, floorLevelNum)); } if (!StringUtils.IsNull(buildingName)) { insertStatement.Append(", BUILDING_NAME"); valuesStatement.Append(", :buildingName "); parameters.Add(DBUtils.CreateOracleParameter("buildingName", OracleDbType.Varchar2, buildingName)); } if (!StringUtils.IsNull(lotNumber)) { insertStatement.Append(", LOT_NUMBER"); valuesStatement.Append(", :lotNumber "); parameters.Add(DBUtils.CreateOracleParameter("lotNumber", OracleDbType.Varchar2, lotNumber)); } if (!StringUtils.IsNull(streetNumber)) { insertStatement.Append(", STREET_NUMBER"); valuesStatement.Append(", :streetNumber "); parameters.Add(DBUtils.CreateOracleParameter("streetNumber", OracleDbType.Varchar2, streetNumber)); } if (!StringUtils.IsNull(streetName)) { insertStatement.Append(", STREET_NAME"); valuesStatement.Append(", :streetName "); parameters.Add(DBUtils.CreateOracleParameter("streetName", OracleDbType.Varchar2, streetName)); } if (!StringUtils.IsNull(streetTypeCode)) { insertStatement.Append(", STREET_TYPE_CODE"); valuesStatement.Append(", :streetTypeCode "); parameters.Add(DBUtils.CreateOracleParameter("streetTypeCode", OracleDbType.Varchar2, streetTypeCode)); } if (!StringUtils.IsNull(streetSuffixCode)) { insertStatement.Append(", STREET_SUFFIX_CODE"); valuesStatement.Append(", :streetSuffixCode "); parameters.Add(DBUtils.CreateOracleParameter("streetSuffixCode", OracleDbType.Varchar2, streetSuffixCode)); } if (!StringUtils.IsNull(locality)) { insertStatement.Append(", LOCALITY"); valuesStatement.Append(", :locality"); parameters.Add(DBUtils.CreateOracleParameter("locality", OracleDbType.Varchar2, locality)); } if (!StringUtils.IsNull(stateCode)) { insertStatement.Append(", STATE_CODE"); valuesStatement.Append(", :stateCode"); parameters.Add(DBUtils.CreateOracleParameter("stateCode", OracleDbType.Varchar2, stateCode)); } if (!StringUtils.IsNull(postcode)) { insertStatement.Append(", POSTCODE"); valuesStatement.Append(", :postcode "); parameters.Add(DBUtils.CreateOracleParameter("postcode", OracleDbType.Varchar2, postcode)); } if (!StringUtils.IsNull(country)) { insertStatement.Append(", COUNTRY"); valuesStatement.Append(", :country "); parameters.Add(DBUtils.CreateOracleParameter("country", OracleDbType.Varchar2, country)); } if (!StringUtils.IsNull(esmsAddress)) { insertStatement.Append(", ESMS_ADDRESS"); valuesStatement.Append(", :esmsAddress "); parameters.Add(DBUtils.CreateOracleParameter("esmsAddress", OracleDbType.Varchar2, esmsAddress)); } if (!StringUtils.IsNull(validatedBy)) { insertStatement.Append(", VALIDATED_DATE"); valuesStatement.Append(", SYSDATE "); insertStatement.Append(", VALIDATED_BY"); valuesStatement.Append(", :validatedBy "); parameters.Add(DBUtils.CreateOracleParameter("validatedBy", OracleDbType.Varchar2, validatedBy)); } if (!StringUtils.IsNull(confidence)) { insertStatement.Append(", VALIDATION_CONFIDENCE"); valuesStatement.Append(", :confidence "); parameters.Add(DBUtils.CreateOracleParameter("confidence", OracleDbType.Decimal, confidence)); } if (!StringUtils.IsNull(status)) { insertStatement.Append(", VALIDATION_STATUS"); valuesStatement.Append(", :status "); parameters.Add(DBUtils.CreateOracleParameter("status", OracleDbType.Varchar2, status)); } if (!StringUtils.IsNull(quality)) { insertStatement.Append(", VALIDATION_QUALITY"); valuesStatement.Append(", :quality "); parameters.Add(DBUtils.CreateOracleParameter("quality", OracleDbType.Decimal, quality)); } // finish off the statement insertStatement.Append(") "); valuesStatement.Append(")"); // build the insert statement string sql = insertStatement + valuesStatement.ToString(); // Execute the INSERT Statement Dml dmlDAO = new Dml(); int rowsAffected = dmlDAO.Execute(sql, transaction, parameters); if (rowsAffected == 0) { throw new NoRowsAffectedException(); } } << many more methods go here >> } This system was developed by me and a small team back in 2005 after a 1 week .NET course. Before than my experience was in client-server applications. Over the past 5 years I've come to recognise the benefits of automated unit testing, automated integration testing and automated acceptance testing (using Selenium or equivalent) but the current code-base seems impossible to introduce these concepts. We are now starting to work on a major enhancement project with tight time-frames. The team consists of 5 .NET developers - 2 developers with a few years of .NET experience and 3 others with little or no .NET experience. None of the team (including myself) has experience in using .NET unit testing or mocking frameworks. What strategy would you use to make this code cleaner, more object-oriented, testable and maintainable?

  Read the article

• Slow MySQL query....only sometimes

  - by Shane N

  I have a query that's used in a reporting system of ours that sometimes runs quicker than a second, and other times takes 1 to 10 minutes to run. Here's the entry from the slow query log: # Query_time: 543 Lock_time: 0 Rows_sent: 0 Rows_examined: 124948974 use statsdb; SELECT count(distinct Visits.visitorid) as 'uniques' FROM Visits,Visitors WHERE Visits.visitorid=Visitors.visitorid and candidateid in (32) and visittime>=1275721200 and visittime<=1275807599 and (omit=0 or omit>=1275807599) AND Visitors.segmentid=9 AND Visits.visitorid NOT IN (SELECT Visits.visitorid FROM Visits,Visitors WHERE Visits.visitorid=Visitors.visitorid and candidateid in (32) and visittime<1275721200 and (omit=0 or omit>=1275807599) AND Visitors.segmentid=9); It's basically counting unique visitors, and it's doing that by counting the visitors for today and then substracting those that have been here before. If you know of a better way to do this, let me know. I just don't understand why sometimes it can be so quick, and other times takes so long - even with the same exact query under the same server load. Here's the EXPLAIN on this query. As you can see it's using the indexes I've set up: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY Visits range visittime_visitorid,visitorid visittime_visitorid 4 NULL 82500 Using where; Using index 1 PRIMARY Visitors eq_ref PRIMARY,cand_visitor_omit PRIMARY 8 statsdb.Visits.visitorid 1 Using where 2 DEPENDENT SUBQUERY Visits ref visittime_visitorid,visitorid visitorid 8 func 1 Using where 2 DEPENDENT SUBQUERY Visitors eq_ref PRIMARY,cand_visitor_omit PRIMARY 8 statsdb.Visits.visitorid 1 Using where I tried to optimize the query a few weeks ago and came up with a variation that consistently took about 2 seconds, but in practice it ended up taking more time since 90% of the time the old query returned much quicker. Two seconds per query is too long because we are calling the query up to 50 times per page load, with different time periods. Could the quick behavior be due to the query being saved in the query cache? I tried running 'RESET QUERY CACHE' and 'FLUSH TABLES' between my benchmark tests and I was still getting quick results most of the time. Note: last night while running the query I got an error: Unable to save result set. My initial research shows that may be due to a corrupt table that needs repair. Could this be the reason for the behavior I'm seeing? In case you want server info: Accessing via PHP 4.4.4 MySQL 4.1.22 All tables are InnoDB We run optimize table on all tables weekly The sum of both the tables used in the query is 500 MB MySQL config: key_buffer = 350M max_allowed_packet = 16M thread_stack = 128K sort_buffer = 14M read_buffer = 1M bulk_insert_buffer_size = 400M set-variable = max_connections=150 query_cache_limit = 1048576 query_cache_size = 50777216 query_cache_type = 1 tmp_table_size = 203554432 table_cache = 120 thread_cache_size = 4 wait_timeout = 28800 skip-external-locking innodb_file_per_table innodb_buffer_pool_size = 3512M innodb_log_file_size=100M innodb_log_buffer_size=4M

  Read the article

• Five Query Optimizations in MySQL

  Query optimization is an often overlooked part of applications. Sean Hull encourages at least some attention to query optimization up front and helps you identify some of the more common optimizations you may run across.

  Read the article

• How to build a Query Template Explorer

  Having introduced his cross-platform Query Template solution, Michael now gives us the technical details on how to integrate his .NET controls into applications both simple and complex. With screenshots and code samples, this has everything you need to build your own powerful SQL editor or Query Template explorer.

  Read the article

• Search For a Query in RDL Files with PowerShell

  - by AllenMWhite

  In tracking down poorly performing queries for clients I often encounter the query text in a trace file I've captured, but don't know the source of the query. I've found that many of the poorest performing queries are those written into the reports the business users need to make their decisions. If I can't figure out where they came from, usually years after the queries were written, I can't fix them. First thing I did was find a great utility called RSScripter , which opens up a Windows dialog...(read more)

  Read the article

• SQL Server Prefetch and Query Performance

  Prefetching can make a surprising difference to SQL Server query execution times where there is a high incidence of waiting for disk i/o operations, but the benefits come at a cost. Mostly, the Query Optimizer gets it right, but occasionally there are queries that would benefit from tuning. Get smart with SQL Backup ProGet faster, smaller backups with integrated verification.Quickly and easily DBCC CHECKDB your backups. Learn more.

  Read the article

• Convert JSON query parameters to objects with JAX-RS

  - by deamon

  I have a JAX-RS resource, which gets its paramaters as a JSON string like this: http://some.test/aresource?query={"paramA":"value1", "paramB":"value2"} The reason to use JSON here, is that the query object can be quite complex in real use cases. I'd like to convert the JSON string to a Java object, dto in the example: @GET @Produces("text/plain") public String getIt(@QueryParam("query") DataTransferObject dto ) { ... } Does JAX-RS support such a conversion from JSON passed as a query param to Java objects?

  Read the article

• Wrong DNS query in Active directory network with NetBIOS enabled client

  - by koankoder

  The setup: Active Directory is enabled on the network (abcd.com) We have a single character host name (1.abcd.com) one of the desktop has an old XP with NetBIOS stuff enabled The Problem Whenever we query for any host name from the XP machine, the first character alone is taken for DNS query (one.abcd.com will query for o.abcd.com, two.abcd.com will query for t.abcd.com) Even if we give some IP, the application queries with numeric prefix (10.x.x.x will query for 1.abcd.com).Since we already have 1.abcd.com, all query and traffic ends up in 1.abcd.com After discussion with network guys, it seems netbios DNS queries by having some prefix etc. but none of them is actually sure on what is happening. Is there any docs which can explain this behavior ? Is this valid behavior in NetBIOS environment ?

  Read the article

• Error in asp.net c# code (mysql database connection)

  - by Ishan

  My code is to update a record if it already exists in database else insert as a new record. My code is as follows: protected void Button3_Click(object sender, EventArgs e) { OdbcConnection MyConnection = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=testcase;User=root;Password=root;Option=3;"); MyConnection.Open(); String MyString = "select fil_no,orderdate from temp_save where fil_no=? and orderdate=?"; OdbcCommand MyCmd = new OdbcCommand(MyString, MyConnection); MyCmd.Parameters.AddWithValue("", HiddenField4.Value); MyCmd.Parameters.AddWithValue("", TextBox3.Text); using (OdbcDataReader MyReader4 = MyCmd.ExecuteReader()) { //** if (MyReader4.Read()) { String MyString1 = "UPDATE temp_save SET order=? where fil_no=? AND orderdate=?"; OdbcCommand MyCmd1 = new OdbcCommand(MyString1, MyConnection); MyCmd1.Parameters.AddWithValue("", Editor1.Content.ToString()); MyCmd1.Parameters.AddWithValue("", HiddenField1.Value); MyCmd1.Parameters.AddWithValue("", TextBox3.Text); MyCmd1.ExecuteNonQuery(); } else { // set the SQL string String strSQL = "INSERT INTO temp_save (fil_no,order,orderdate) " + "VALUES (?,?,?)"; // Create the Command and set its properties OdbcCommand objCmd = new OdbcCommand(strSQL, MyConnection); objCmd.Parameters.AddWithValue("", HiddenField4.Value); objCmd.Parameters.AddWithValue("", Editor1.Content.ToString()); objCmd.Parameters.AddWithValue("", TextBox3.Text); // execute the command objCmd.ExecuteNonQuery(); } } } I am getting the error as: ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.1.51-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order,orderdate) VALUES ('04050040272009','&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&' at line 1 The datatype for fields in table temp_save are: fil_no-->INT(15)( to store a 15 digit number) order-->LONGTEXT(to store contents from HTMLEditor(ajax control)) orderdate-->DATE(to store date) Please help me to resolve my error.

  Read the article

• SQL SERVER – 3 Online SQL Courses at Pluralsight and Free Learning Resources

  - by pinaldave

  Usain Bolt is an inspiration for all. He broke his own record multiple times because he wanted to do better! Read more about him on wikipedia. He is great and indeed fastest man on the planet. Usain Bolt – World’s Fastest Man “Can you teach me SQL Server Performance Tuning?” This is one of the most popular questions which I receive all the time. The answer is YES. I would love to do performance tuning training for anyone, anywhere.  It is my favorite thing to do, and it is my favorite thing to train others in.  If possible, I would love to do training 24 hours a day, 7 days a week, 365 days a year.  To me, it doesn’t feel like a job. Of course, as much as I would love to do performance tuning 24/7/365, obviously I am just one human being and can only be in one place t one time.  It is also very difficult to train more than one person at a time, and it is difficult to train two or more people at a time, especially when the two people are at different levels.  I am also limited by geography.  I live in India, and adjust to my own time zone.  Trying to teach a live course from India to someone whose time zone is 12 or more hours off of mine is very difficult.  If I am trying to teach at 2 am, I am sure I am not at my best! There was only one solution to scale – Online Trainings. I have built 3 different courses on SQL Server Performance Tuning with Pluralsight. Now I have no problem – I am 100% scalable and available 24/7 and 365. You can make me say the same things again and again till you find it right. I am in your mobile, PC as well as on XBOX. This is why I am such a big fan of online courses.  I have recorded many performance tuning classes and you can easily access them online, at your own time.  And don’t think that just because these aren’t live classes you won’t be able to get any feedback from me.  I encourage all my viewers to go ahead and ask me questions by e-mail, Twitter, Facebook, or whatever way you can get a hold of me. Here are details of three of my courses with Pluralsight. I suggest you go over the description of the course. As an author of the course, I have few FREE codes for watching the free courses. Please leave a comment with your valid email address, I will send a few of them to random winners. SQL Server Performance: Introduction to Query Tuning  SQL Server performance tuning is an art to master – for developers and DBAs alike. This course takes a systematic approach to planning, analyzing, debugging and troubleshooting common query-related performance problems. This includes an introduction to understanding execution plans inside SQL Server. In this almost four hour course we cover following important concepts. Introduction 10:22 Execution Plan Basics 45:59 Essential Indexing Techniques 20:19 Query Design for Performance 50:16 Performance Tuning Tools 01:15:14 Tips and Tricks 25:53 Checklist: Performance Tuning 07:13 The duration of each module is mentioned besides the name of the module. SQL Server Performance: Indexing Basics This course teaches you how to master the art of performance tuning SQL Server by better understanding indexes. In this almost two hour course we cover following important concepts. Introduction 02:03 Fundamentals of Indexing 22:21 Practical Indexing Implementation Techniques 37:25 Index Maintenance 16:33 Introduction to ColumnstoreIndex 08:06 Indexing Practical Performance Tips and Tricks 24:56 Checklist : Index and Performance 07:29 The duration of each module is mentioned besides the name of the module. SQL Server Questions and Answers This course is designed to help you better understand how to use SQL Server effectively. The course presents many of the common misconceptions about SQL Server, and then carefully debunks those misconceptions with clear explanations and short but compelling demos, showing you how SQL Server really works. In this almost 2 hours and 15 minutes course we cover following important concepts. Introduction 00:54 Retrieving IDENTITY value using @@IDENTITY 08:38 Concepts Related to Identity Values 04:15 Difference between WHERE and HAVING 05:52 Order in WHERE clause 07:29 Concepts Around Temporary Tables and Table Variables 09:03 Are stored procedures pre-compiled? 05:09 UNIQUE INDEX and NULLs problem 06:40 DELETE VS TRUNCATE 06:07 Locks and Duration of Transactions 15:11 Nested Transaction and Rollback 09:16 Understanding Date/Time Datatypes 07:40 Differences between VARCHAR and NVARCHAR datatypes 06:38 Precedence of DENY and GRANT security permissions 05:29 Identify Blocking Process 06:37 NULLS usage with Dynamic SQL 08:03 Appendix Tips and Tricks with Tools 20:44 The duration of each module is mentioned besides the name of the module. SQL in Sixty Seconds You will have to login and to get subscribed to the courses to view them. Here are my free video learning resources SQL in Sixty Seconds. These are 60 second video which I have built on various subjects related to SQL Server. Do let me know what you think about them? Here are three of my latest videos: Identify Most Resource Intensive Queries – SQL in Sixty Seconds #028 Copy Column Headers from Resultset – SQL in Sixty Seconds #027 Effect of Collation on Resultset – SQL in Sixty Seconds #026 You can watch and learn at your own pace.  Then you can easily ask me any questions you have.  E-mail is easiest, but for really tough questions I’m willing to talk on Skype, Gtalk, or even Facebook chat.  Please do watch and then talk with me, I am always available on the internet! Here is the video of the world’s fastest man.Usain St. Leo Bolt inspires us that we all do better than best. We can go the next level of our own record. We all can improve if we have a will and dedication.  Watch the video from 5:00 mark. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Performance, SQL Query, SQL Server, SQL Tips and Tricks, SQL Training, SQLServer, T SQL, Technology, Video

  Read the article

• SQL SERVER – Weekly Series – Memory Lane – #035

  - by Pinal Dave

  Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane. 2007 Row Overflow Data Explanation  In SQL Server 2005 one table row can contain more than one varchar(8000) fields. One more thing, the exclusions has exclusions also the limit of each individual column max width of 8000 bytes does not apply to varchar(max), nvarchar(max), varbinary(max), text, image or xml data type columns. Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005 An old but like a gold article. Talks about lots of concepts related to Index and the difference from earlier version to the newer version. I strongly suggest that everyone should read this article just to understand how SQL Server has moved forward with the technology. Improvements in TempDB SQL Server 2005 had come up with quite a lots of improvements and this blog post describes them and explains the same. If you ask me what is my the most favorite article from early career. I must point out to this article as when I wrote this one I personally have learned a lot of new things. Recompile All The Stored Procedure on Specific TableI prefer to recompile all the stored procedure on the table, which has faced mass insert or update. sp_recompiles marks stored procedures to recompile when they execute next time. This blog post explains the same with the help of a script.  2008 SQLAuthority Download – SQL Server Cheatsheet You can download and print this cheat sheet and use it for your personal reference. If you have any suggestions, please let me know and I will see if I can update this SQL Server cheat sheet. Difference Between DBMS and RDBMS What is the difference between DBMS and RDBMS? DBMS – Data Base Management System RDBMS – Relational Data Base Management System or Relational DBMS High Availability – Hot Add Memory Hot Add CPU and Hot Add Memory are extremely interesting features of the SQL Server, however, personally I have not witness them heavily used. These features also have few restriction as well. I blogged about them in detail. 2009 Delete Duplicate Rows I have demonstrated in this blog post how one can identify and delete duplicate rows. Interesting Observation of Logon Trigger On All Servers – Solution The question I put forth in my previous article was – In single login why the trigger fires multiple times; it should be fired only once. I received numerous answers in thread as well as in my MVP private news group. Now, let us discuss the answer for the same. The answer is – It happens because multiple SQL Server services are running as well as intellisense is turned on. Blog post demonstrates how we can do the same with the help of SQL scripts. Management Studio New Features I have selected my favorite 5 features and blogged about it. IntelliSense for Query Editing Multi Server Query Query Editor Regions Object Explorer Enhancements Activity Monitors Maximum Number of Index per Table One of the questions I asked in my user group was – What is the maximum number of Index per table? I received lots of answers to this question but only two answers are correct. Let us now take a look at them in this blog post. 2010 Default Statistics on Column – Automatic Statistics on Column The truth is, Statistics can be in a table even though there is no Index in it. If you have the auto- create and/or auto-update Statistics feature turned on for SQL Server database, Statistics will be automatically created on the Column based on a few conditions. Please read my previously posted article, SQL SERVER – When are Statistics Updated – What triggers Statistics to Update, for the specific conditions when Statistics is updated. 2011 T-SQL Scripts to Find Maximum between Two Numbers In this blog post there are two different scripts listed which demonstrates way to find the maximum number between two numbers. I need your help, which one of the script do you think is the most accurate way to find maximum number? Find Details for Statistics of Whole Database – DMV – T-SQL Script I was recently asked is there a single script which can provide all the necessary details about statistics for any database. This question made me write following script. I was initially planning to use sp_helpstats command but I remembered that this is marked to be deprecated in future. 2012 Introduction to Function SIGN SIGN Function is very fundamental function. It will return the value 1, -1 or 0. If your value is negative it will return you negative -1 and if it is positive it will return you positive +1. Let us start with a simple small example. Template Browser – A Very Important and Useful Feature of SSMS Templates are like a quick cheat sheet or quick reference. Templates are available to create objects like databases, tables, views, indexes, stored procedures, triggers, statistics, and functions. Templates are also available for Analysis Services as well. The template scripts contain parameters to help you customize the code. You can Replace Template Parameters dialog box to insert values into the script. An invalid floating point operation occurred If you run any of the above functions they will give you an error related to invalid floating point. Honestly there is no workaround except passing the function appropriate values. SQRT of a negative number will give you result in real numbers which is not supported at this point of time as well LOG of a negative number is not possible (because logarithm is the inverse function of an exponential function and the exponential function is NEVER negative). Validating Spatial Object with IsValidDetailed Function SQL Server 2012 has introduced the new function IsValidDetailed(). This function has made my life very easy. In simple words, this function will check if the spatial object passed is valid or not. If it is valid it will give information that it is valid. If the spatial object is not valid it will return the answer that it is not valid and the reason for the same. This makes it very easy to debug the issue and make the necessary correction. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Memory Lane, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

< Previous Page | 47 48 49 50 51 52 53 54 55 56 57 58  | Next Page >