Chào ngày mới !: tháng 10 2011

Thứ Hai, 31 tháng 10, 2011

ASP: Validating User Input to Avoid Attacks

  If ValidateInput(MyUrl) Then   
    Response.Redirect (myURL) 
  Else 
    Response.Write("URL was invalid.") 
  End If 

  Function ValidateInput(sInput) 
    Dim reValid 
    Set reValid = New RegExp 

    reValid.Pattern = "^[\w\.:\?&=/]*$" 
    reValid.MultiLine = False 
    reValid.Global = True 

    ValidateInput = reValid.Test(sInput) 
  End Function

(http://msdn.microsoft.com/en-us/library/ms525361%28v=vs.90%29.aspx)

ASP: Removing Harmful Characters from User Input

  Response.Write("Hello, " & RemoveBadCharacters(Request.Form("UserName"))) 
  Response.Write("<BR>This is why you received an error:") 

  Function RemoveBadCharacters(strTemp) 
    Dim regEx 
    Set regEx = New RegExp 
    regEx.Pattern = "[^A-Za-z0-9_ ]" 
    regEx.Global = True 
    RemoveBadCharacters = regEx.Replace(strTemp, "") 
  End Function

(http://msdn.microsoft.com/en-us/library/ms526004%28v=VS.90%29.aspx)

Joomla

URL:

JRoute::_( 'index.php?option=com_user&view=login' );
JURI::base();
JPATH_ADMINISTRATOR  The path to the administrator folder.
JPATH_BASE  The path to the installed Joomla! site.
JPATH_CACHE  The path to the cache folder.
JPATH_COMPONENT  The path to the current component being executed.
JPATH_COMPONENT_ADMINISTRATOR  The path to the administration folder of the current component being executed.
JPATH_COMPONENT_SITE  The path to the site folder of the current component being executed.
JPATH_CONFIGURATION  The path to folder containing the configuration.php file.
JPATH_INSTALLATION  The path to the installation folder.
JPATH_LIBRARIES  The path to the libraries folder.
JPATH_PLUGINS  The path to the plugins folder.
JPATH_ROOT  The path to the installed Joomla! site.
JPATH_SITE  The path to the installed Joomla! site.
JPATH_THEMES  The path to the templates folder.
JPATH_XMLRPC  The path to the XML-RPC Web service folder.(1.5 only)

Language:

JLanguage::load($extension= 'joomla'
        $basePath=JPATH_BASE
        $lang=null
        $reload=false)
(http://docs.joomla.org/JLanguage::load/1.5)

Add script:

// Add a reference to a Javascript file
// The default path is 'media/system/js/'
JHTML::script($filename, $path, $mootools);

// Add a reference to a CSS file
// The default path is 'media/system/css/'
JHTML::stylesheet($filename, $path);

If your script requires Mootools, include the line:
JHTML::_("behavior.mootools");

$document =& JFactory::getDocument();
$document->addScript($url);
$document->addStyleSheet($url);
(http://docs.joomla.org/Adding_JavaScript_and_CSS_to_the_page)

Query:

$db =& JFactory::getDBO();
$db->setQuery($query, 0, $items);
$rows = $db->loadObjectList();

User:

$user =& JFactory::getUser();
$aid = $user->get('aid', 0);

ini_set:

ini_set('max_execution_time', 180);
ini_set('post_max_size', '64M');
ini_set('memory_limit', '64M');

Xem vị trí các module trong Joomla:

Trên url thêm ?tp=1

Fatal error: Maximum execution time of 30 seconds exceeded ...

ini_set('max_execution_time', 180);

Thứ Sáu, 28 tháng 10, 2011

Connection Strings

(http://www.carlprothman.net/Default.aspx?tabid=81)

MySQL is a perfect database solution for small to medium websites. If your backend MySQL database is well optimized and properly structured it can serve thousands of visitors daily, without degrading your server performance. In this article I'll show you how to connect to MySQL database from ASP. You will have to install MySQL ODBC Driver-MyODBC 3.51 if you don't have it on your server yet. You can download it here: http://www.mysql.com/downloads/api-myodbc-3.51.html

<%
Dim sConnection, objConn , objRS

sConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=Your_Mysql_DB; UID=mysql_username;PASSWORD=mysql_password; OPTION=3"

Set objConn = Server.CreateObject("ADODB.Connection")

objConn.Open(sConnection)

Set objRS = objConn.Execute("SELECT FirstName, LastName FROM tblUsers")

While Not objRS.EOF
Response.Write objRS.Fields("LastName") & ", " & objRS.Fields("FirstName") & "<br>"
Response.Write & " "
objRS.MoveNext
Wend

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

(http://www.aspdev.org/asp/asp-mysql-connect/)

ASP: Eval() and Execute()

Microsoft has released 2 very useful functions with Microsoft Visual Basic Scripting Edition 5.0, namely Eval() and Execute() functions. We will compare those 2 functions and give examples of their use in this article.

The Eval() VBScript function evaluates an expression and returns the result. Consider the following line of VBScript code:

Var1 = Var2

You can interpret this statement in 2 completely different ways. The first one is "the value of Var2 is assigned to Var1” and the second one is "Var1 is compared to Var2”. The Eval() VBScript function always uses the second interpretation and returns Boolean value - True or False. For example consider the following ASP code:

<%
Var1 = 1
Var2 = 2
Response.Write(Eval("Var1 = Var2 + 1")) ' Prints False
Response.Write(Var1) ' Prints 1, even after the Eval() function execution on the previous line
Response.Write(Eval("Var1 = Var2 - 1")) ' Prints True
%>

The Execute() VBScript function uses the first interpretation we talked about earlier, which actually evaluates the expression parameter. For example the following ASP/VBScript code will print 5 in the browser:

<%
Var1 = 1
Var2 = 2
Execute("Var1 = Var2 + 3")
Response.Write (Var1) ' Prints 5
%>

(http://www.aspdev.org/asp/asp-eval-execute/)

Thứ Hai, 24 tháng 10, 2011

ASP: Write file utf8


Sub LogEvent(message, filename)
    sDate = Now()
 
 'filename
 if filename <> "" then
  filename = filename & "_" & year(sDate)&month(sDate)&day(sDate)
 else
  filename = year(sDate)&month(sDate)&day(sDate)
 end if
 filename = server.mappath(".") & "/log/" & filename
 
 'check file exists
 Set fs = CreateObject("Scripting.FileSystemObject")
 If fs.FileExists(filename) Then
  modeFile = 2
 else
  modeFile = 1
 end if
 set fs = nothing
 
 'write file
 Set objStream = server.CreateObject("ADODB.Stream")
 objStream.Open 
 objStream.CharSet = "UTF-8"
 
 if modeFile = 2 then 
  objStream.LoadFromFile filename
  objStream.ReadText
 end if
 
 objStream.WriteText(sDate & vbTab & message & vbNewLine)

 objStream.SaveToFile filename , modeFile
 objStream.Close
 set objStream = nothing
End Sub

(http://gchandra.wordpress.com/2004/08/19/creating-utf-8-files-using-asp/, http://www.w3schools.com/ado/ado_ref_stream.asp)

Thứ Sáu, 21 tháng 10, 2011

Save a DataTable to Excel Format

Users of my applications like to get reports directly in native Excel so they can use them quickly. I typically save files in CSV, which is fast and reliable but Excel doesn't support it as thoroughly as it should.

On the other hand, using Excel automation to populate a file takes an eternity and doesn't work well on a server. So here's a nice middleground - I can save the file to an XML/HTML format that Excel supports natively.

This strategy solves a few problems:

1) Performance - This format is just as fast to create as CSV.
2) Dependence - Does not require using any Excel libraries.
3) Compatibility - Excel opens the file and it looks properly formatted and usable.
4) Data Integrity - CSV files tend to damage numeric or date values if they are formatted in a way Excel does not expect; this format converts everything to text which means the values are represented exactly.

You also have the option of using some of Microsoft Excel's number formatting tools. These are surprisingly hard to find documented anywhere! But, here they are:

http://www.ozgrid.com/Excel/CustomFormats.htm

Without further ado, here's the code:

/// 
/// Save a DataTable to an XML file
/// 

/// 
/// 
public static void SaveDataTableToExcelCompatibleFile(string filename, DataTable dt)
{
   StreamWriter sw = null;
   try {

       // Create the file and write the header
       sw = new StreamWriter(filename);
       sw.WriteLine(@"");

       // Write column names
       sw.Write("");
       for (int i = 0; i < dt.Columns.Count; i++) {
           sw.Write("");
       }
       sw.Write("");

       // Write data
       for (int i = 0; i < dt.Rows.Count; i++) {
           DataRow dr = dt.Rows[ i ];
           sw.Write("");
           for (int j = 0; j < dt.Columns.Count; j++) {
               sw.Write("");
           }
           sw.Write("");
       }

       // Write footer
       sw.WriteLine(@"");
           sw.Write(dt.Columns[ i ].ColumnName);
           sw.Write("
");
               sw.Write(dr[j].ToString());
               sw.Write("



Confidential Information - Do Not Distribute");

   } finally {
       if (sw != null) sw.Close();
   }
}

"); sw.Write(dt.Columns[ i ].ColumnName); sw.Write("
"); sw.Write(dr[j].ToString()); sw.Write("

(http://www.tedspence.com/index.php?entry=entry090724-095142)

SUM() Strings in SQL server

It's really easy to sum numeric values. You write this:

SELECT SUM(revenue) FROM orders

Every so often I wish I could write a similar query for strings. For example, wouldn't it be neat if we could get a list of employees by department like this?

SELECT TO_LIST(name), department FROM employees GROUP BY department

As it turns out, there's a way to do this, but it's a little bit tricky. You have to use an XML feature to do something it wasn't really designed to do. By converting results into XML and back, you can produce lists exactly the way you might want. Here's what the query looks like:

SELECT department
, SUBSTRING(
(SELECT ', ' + name
FROM employees e2
  WHERE e2.department = e1.department
  FOR XML PATH(''))
, 2, 999) AS list_of_names
FROM employees e1
GROUP BY department

Why does this work? First, you're doing a main query that just lists all the departments. So you get a list like this:

department
-----------
Sales
Accounting
Development

Then, for each record in this list, we do a subquery listing "', ' + name" for each record. That produces these results internally:

field1
-----------
, Bob
, Chris
, Alex

The XML path with a blank parameter turns that into a gigantic text string, like so:

field1
------------------
, Bob, Chris, Alex

And of course the Substring removes the first two characters. The end result is that you get exactly the list you expect!

(http://www.tedspence.com/index.php?entry=entry090724-095142)

Thứ Năm, 20 tháng 10, 2011

Compress & Encode Javascript

http://javascriptcompressor.com/

Thứ Tư, 19 tháng 10, 2011

SQL Server: Full-Text Indexing

Full-text search là một cơ chế mà sql server cho phép bạn tìm kiếm một từ hay một cụm từ một cách hiệu quả và nhanh chóng. Chúng ta thường hay dùng toán tử like để tìm kiếm một chuỗi, ví dụ: TenWebsite like N'%CiOne', nghĩa là chúng ta đang muốn tìm danh sách tên các website nào miễn là có từ cuối cùng là CiOne. Việc tìm kiếm như vậy chỉ giúp cho chúng ta tìm hiệu quả được 1 từ hoặc nhiều từ nhưng phải đúng vị trí thứ tự.

Trên thực tế việc tìm kiếm của chúng ta phức tạp hơn rất nhiều, cái mà ta cần có thể là những từ đồng nghĩa, trái nghĩa hoặc nó không cần chính xác vị trí mà ở cạnh nhau hoặc đảo vị trí cũng cần phải xuất hiện, ví dụ các bạn muốn tìm những website nào mà có đào tạo cả tin học và ngoại ngữ, như vậy danh sách website bạn cần sẽ gồm có từ tin học và từ ngoại ngữ, không cần biết vị trí từ nào đứng trước và chúng cũng chẳng cần phải đứng sát nhau. Yêu cầu trên sẽ được full-text search giải quyết cho bạn. Bằng việc một vài thao tác cấu hình và cài đặt bằng tool hoặc dòng lệnh T-SQL và sau đó sử dụng hàm được hỗ trợ riêng cho full-text search (Contain và FreeText) là bạn có thể thực hiện đầy đủ các nhu cầu tìm kiếm ngay cả việc tìm những từ đồng nghĩa và trái nghĩa với từ khoá bạn cho vào. Tuyệt vời hơn nữa là full-text cho phép bạn loại bỏ những từ không còn thiết trong câu từ khoá bạn cần tìm (được hiểu là những từ noise), ví dụ bạn gõ câu từ khoá là full-text search là gì, thì full-text search sẽ tự động loại bỏ 2 từ "là" và "gì" để chỉ tìm cho bạn danh sách nào có chứa từ full-text search là từ khoá mà thực sự mình muốn tìm.

Trên đây là tổng quan về full-text search để bạn hiểu mục đích sử dụng, còn việc cấu hình và sử dụng thế nào thì bạn tham khảo website này để làm nhé. Tôi lưu ý một phần quan trọng mà bạn cần phải tìm hiểu là: full-text catalog, full-text index, các từ noise, 2 hàm contain và freetext. Website tham khảo::
link 1: simple-talk
link 2: databasejournal

(http://cione.com.vn/Questions/AnswerQuestion?Id=236)

SQL Server Replication

These notes assume SQL Server 7, but may apply to other versions as well.

Capabilities of SQL Server Replication

SQL Server 6.5 only allows read-only copies of data to be replicated. However, with SQL Server 7 updateable copies of data can be replicated. This means that copies of the same data can be available on two different servers and should the data on one server be updated then that change will be replicated to (i.e. duplicated on) the other server.

SQL Server Replication Terminology

An understanding of SQL Server Replication terminology is useful, but not essential, for setting up and maintaining a replicated system. The following is a list of the terms used with SQL Server Replication.

Article: An article can be an entire table, select rows from a table, specified columns from a table or a stored procedure. An article is what is to be replicated. See also publication.
Distribution Agent: The distribution agent is a process that moves transactions and snapshots held in the distribution database to subscribers. See also Snapshot replication and Transaction replication.
Distribution Database: A distribution database tracks the changes (to publications) that need to be replicated (from publishers). A publisher can act as its own distributor, or a remote database server can be used to hold the distribution database.; For transactional replication, the distribution database tracks changes made to publications.; For merge replication it stores only synchronization history.
Distributor: The server that contains the distribution database.
Immediate Transactional Consistency: All sites are guaranteed to always see the same data as the publisher, with no time lag. With immediate transactional consistency each site must simultaneously commit the change. Immediate Transactional Consistency therefore has a performance impact, because of which it is unlikely to be suitable for high performance databases or where replicating over a slow LAN or a WAN.
Merge Agent: The merge agent is a process that merges changes between publication and subscription databases where merge replication is used. See also Merge Replication.
Merge Replication: Type of replication that allows changes to the data to be made at any site. Changes to publications are then merged with the copies of that publication held at the other server(s). Merge replication cannot guarantee transactional consistency because the same or related records can be updated on different servers at the same time. See also snapshot replication and transactional replication.
Publication: The data to be replicated is contained with a publication. A publication can contain a selection of tables and stored procedures. A table included in a publication is called an article. A publication therefore defines the set of data that is to be replicated. Each publication can be replicated as a snapshot publication (using snapshot replication), a transactional publication (using transactional replication) or a merge publication (using merge replication). See also article.
Publisher: A publisher is a server that makes data available for other servers, i.e. it is the server that makes data available for replication.
Pull Subscription: With pull subscription the subscriber asks the publisher for periodic updates. With pull subscription each subscriber can specify the best time for the update. Pull subscription is generally considered the better form of replication when a large number of subscribers are involved, or where the load on the LAN needs to be distributed.
Push Subscription: With push subscription the publisher pushes any changes made to the data out to the subscribers. Typically push subscription is used where changes need to be replicated to the subscribers shortly after the change occurs, i.e. where the the replication of changes is required in near real time.
Replication: The capability to copy (i.e. replicate) data and changes to that data from one database to another.
Snapshot replication: Type of replication where a snapshot is taken of the current publication data. The subscribers copy of the data is then replaced with the entire contents of that snapshot. Snapshot replication requires less processor overhead than either merge or transaction replication because it does not require continuous monitoring of data changes. Probably not suitable for replicating large volumes of data (i.e. large tables) because of the network traffic involved. See also transaction replication and merge replication.
Subscriber: A subscriber is a server that receives updates to the data. Each subscriber is associated with a publisher.
Transactional Consistency: With transactional consistency all sites are guaranteed to have the same data as the publisher. Sites may lag behind the publisher, but the view at each subscriber will be the same as that at the publisher at some point in time.
Transactional Replication: Type of replication where the copies of the transactions are replicated to each subscriber. Transactional replication uses the transaction log to capture changes. These changes are then sent to subscribers and applied in the same order. This guarantees transactional consistency. Transactional replication is well suited where near real-time updates are required. See also snapshot replication and merge replication.

Which type(s) of replication do you need?

The three different types of replication (merge, snapshot and transaction) cannot be mixed within a publication, i.e. for each publication only one type of replication can be used. However, given that there is no limit on the number of publications that can be defined this should not be a problem (if you need a table to participate in a different type of replication from other tables simply place it in another publication).

Each type of replication is suited to different requirements, as illustrated below:

	Merge	Snapshot	Transaction
Edit anywhere	Yes	No	No
Only changes replicated	Yes	No	Yes
Suited for large quantities of data	Yes	No	Yes
Replicate stored procedures	No	Yes	Yes
Read only at subscriber	No	Yes	Yes
Consistency guaranteed	No	Yes	Yes
Complete refresh of data	No	Yes	No

It would be wise to plan for each article/table which form of replication would be most appropriate. As a general rule, I would recommend merge replication for tables that need to be updateable at any server and transaction replication where updates are only to be made at the publisher.

How to set up a distribution database

The distribution database tracks what information needs to be replicated from the publisher to the subscribers. It can be held on a separate server to the publisher or on the same server. (Personally I think it makes more sense for it to be on the same server as the publisher.)

Be aware that replication requires heavy use of the distribution database. It is suggested that the initial size of the data device should be at least 30MB and for the log device at least 15MB.

To set up a distribution database:

Run Enterprise Manager
From the server manager window select a server that is to hold the distribution database.
From the ‘Tools’ menu select ‘Replication’, then from the drop down menu that appears select ‘Configure Publishing and Subscribers...’.
If distribution database has not already been created then the ‘Configure Publishing and Distribution Wizard’ will now run. Click '[Next >]' on the first screen.
On the window ‘Choose Distributor’ - I would recommend using the publisher-server as its own distributor, but another server may be selected at this point. Click ‘[Next >]’.
On the window ‘Use Default Configuration’ - I would recommend using the default settings. Any servers already registered with the server will be listed as potential subscribers. Subscribing servers can be added later if need be, but if you already know what servers you want to replicate to then it would be as well to register them with Enterprise Manager prior to creating the distribution database. If you are not happy with the default list of servers or the location for the distribution database then do not choose the default settings. Click ‘[Next >]’ to progress to the next screen.
If you selected the default configuration then you can select ‘[Finish]’ now. Otherwise you can configure the distribution database name and location, which server is to act as publisher and which databases are to participate in replication.

How to set publication options

The publication options cover all the options relating to the publication of data, short of defining the data itself. It provides a means of setting or changing:

The distribution database, and its properties.
The publishers
The databases to be published
The subscribers - including the subscription schedule, i.e. the frequency at which replication will take place.

An initial set of publication options will normally have been set up when the distribution database was configured. To change or reconfigure the publication options:

Run Enterprise Manager
From the server manager window select the publisher-server.
From the ‘Tools’ menu select ‘Replication’ , and from the sub-menu select ‘Configure Publishing, Subscribers and Distribution...’

The distributor, publisher, published database and subscriber options are each held on different tabs.

To configure when or the frequency at which each subscriber will receive any updates, select the 'Subscribers' tab. Double clicking the subscriber of interest will show its subscription properties. One of the tabs is 'schedules'. On this the timing and frequency of updates for replication can be set.

If the publisher and subscriber are in different NT domains then some thought must be given to which login account the replication agents are to use on the subscriber. To show or configure which accounts are used, select ‘Replication’ from the ‘Tools’ menu, from the sub-menu select ‘Configure Publishing, Subscribers and Distribution...’, click the ‘Subscribers’ tab and double click the subscriber. If the account used by the replication agents must be a valid account at the subscriber. If only transaction (or snapshot) replication is to be used then it must have ‘sysadmin’ or ‘db_owner’ roles for the database being replicated. If merge replication is to be used then it must have the 'sysadmin' role for the database being replicated.

How to create a new publication

A publication defines the data that is to be replicated, and the method of replication. Before a publication is created the distribution database must already have been setup and configured.

To create (or modify) a publication:

Run Enterprise manager
From the server manager window select the publication server.
From the ‘Tools’ menu select ‘Replication’, and from the sub-menu select ‘Create and Manage Publications’. The ‘Create and Manage Publications on {server}’ dialog box should now appear.
To create a new publication first select the database that contains the data to publish and then click ‘[Create Publication...]’. The 'Create Publication Wizard' should now run.

Note:

Unless you absolutely need every server to be completely in step with every other don’t use ‘immediate-updating subscriptions’. This option will cripple performance, because no transaction can complete unless it can complete on every other server.

How to subscribe to a publication

To subscribe to a publication:

Run Enterprise Manager
From the server manager window select the publisher-server.
From the ‘Tools’ menu select ‘Replication’, and from the sub-menu select ‘Create and Manage Publications...’.
Expand the database of interest and select the publication that is to be subscribed to.
Click ‘[Properties and Subscriptions]’
Select the ‘Subscriptions’ tab.
Click the ‘[Push New...]’ button to add a new subscriber. This will cause the ‘Push Subscription Wizard’ to run.

(http://www.cryer.co.uk/brian/sqlserver/howtoreplication.htm)

SQL Server Replication- The Crib Sheet

Introduction

Replication is intended to be a way of distributing data automatically from a source database to one or more recipient databases. As such, it can have obvious uses in a distributed system. It has also been used to implement high-availability systems. It is not useful for one-off synchronization, or for simply copying data. It is intended as a long-term data relationship between databases. Typical uses are in:

Data warehousing and reporting
Integrating data from several, possibly only partially connected, sites
Improving scalability and availability
Integrating heterogeneous data from other databases via OLE DB, integrating data from mobile users, getting data to and from Point-Of-Sale systems
Offloading/delegating batch processing tasks.

Examples of the use of replication within an application could be

Distributing data 'owned' by a particular application to other applications that are 'consumers' of that data. (For example, sales records to reporting services, manufacturing stock levels to purchasing systems.)
Creating several instances of a database to distribute load on it
Updating a central database with information from a number of remote Laptops that might be only partially connected, and to resynchronise the laptops.

There are three methods of replication: Snapshot, Transactional, and Merge. These are provided to try to meet the wide range of business requirements for replication.

Replication uses a 'Magazine Publishing' vocabulary. Anything from an 'Article' to an entire database can be replicated. An Article is the smallest component of distribution, and can be a table, procedure or function. If it is a table, then a filter can be applied to it so that only certain rows or columns are replicated. This 'Magazine Publishing' analogy can be confusing because, in replication, a Subscriber can sometimes make updates, and a Publisher usually sends out incremental changes to the articles in a publication.

A 'Publisher' maintains the original copy of the data. It holds the definition of the 'Publication', which defines the 'articles' that are to be 'published'. (The database with the original location of the data determines what is to be distributed).

A 'Subscriber' receives the articles from a publisher. It can subscribe to one or more publications. Any database can take on either role or even both roles at once.

A Distributor is a specialist database that runs the 'Replication agents'.

Replication is not part of the SQL Server engine, but an external application. This makes it much easier to involve other database systems in replication. Any SQL Server database, or other database system with an OLE DB provider, can be a publisher or subscriber in snapshot or transactional replication.

It is essential to plan out the replication in detail as a first stage, and to be very certain of the type of replication you wish to implement. A common mistake is to use replication in cases where a much less complex solution is possible.

A problem with production systems using replication is the difficulty of restoring the topology after a disaster.. This requires a fully documented recovery strategy, which has to be periodically tested and practiced. This means that the whole replication topology and configuration must be scripted so it can be re-created in an emergency, even if the system was originally built using the GUI tools. The Object Browser (or Enterprise Manager) makes the initial deployment relatively simple to do, and there are plenty of step-by-step guides, but it is not the best option when trying to restore an existing topology, and settings, in order to achieve a recovery from major failures.

Problems often follow from developers adding or dropping articles, changing publication properties, and changing schema on published databases. It is therefore best to create the replication once the design of the publisher is relatively stable.

Replication topologies

In most topologies, it makes sense for publishers, distributors, and subscribers to be on separate physical hardware.

Central Publisher

The commonest form of replication is to have a single publisher with the source data, with one or more subscribers. The Distributor database can be on the same, or preferably different, server.

Central Subscriber

Often, where the data from several databases need to be 'warehoused' centrally in an OLAP or reporting database, one will find a single 'reporting' database subscribing to several publications.

One can come across other topologies such as 'bi-directional' and 'peer to peer' which are really special cases of Central Publisher or Central Subscriber and use transactional replication

Publishing Subscriber

The distribution of data can be relayed to other subscribers via a publishing subscriber. This allows replication to be implemented over low-bandwidth WANs to a subscriber that, in turn, distributes it to other servers within its high-bandwidth LAN

Replication Methods

How Replication Works

Replication begins with the initial synchronization of the published objects between the Publisher and Subscribers, using a snapshot. A snapshot is a copy of all of the objects and data specified by a publication. After the snapshot is created on the publisher, it is delivered to the Subscribers via the distributor.

For Snapshot replication, this is sufficient. For other types of replication, all subsequent data changes to the publication flow to the Subscriber as they happen, in a queue, or on request.

Snapshot Replication

The snapshot replication process provides the initial synchronization for transactional and merge publications. However, in several cases, this initial synchronization is all that is necessary. This would include circumstances where data hardly changes, or if the latest version of the data is not essential to the subscriber, where the amount of data is small, or if a large number of changes takes place rapidly.

Snapshot replication involves copying the articles that make up the publication. Normally, if they exist already on the subscriber, they are over-written, though this behavior can be changed. Snapshot replication is more expensive in terms of overhead and network traffic and only takes place at intervals. Because locks are held during snapshot replication, this can impact other users of the subscriber database. It is therefore more suitable for static data and enumerations. In SQL Server 2005, several articles can be processed in parallel, and interrupted snapshots can be recommenced from the point of interruption. Snapshots can be queued or immediate.

Data changes are not tracked for snapshot replication; each time a snapshot is applied, it completely overwrites the existing data.

Transactional Replication

Transactional replication is used if:

Changes to the data must be propagated immediately
The database application taking out a subscription needs to react to every change
The Publisher has a very high volume of insert, update, and delete activity
The Publisher or Subscriber is a different database application reached via OLE DB.

Essentially, Transaction replication distributes data in one direction, but transactional replication does offer options that allow updates at the Subscriber. Once a snapshot replication has synchronized the subscribers with the publisher, all committed transactions on the publisher are then propagated to the subscribers in sequence, via distributed transactions. One can select a queued update or immediate, depending on requirements.

Peer-to-peer Replication

This is a special type of transactional replication in which every participant is both a publisher and subscriber (2005 Enterprise only) and is most useful for up to ten databases in a load-balancing or high-availability group.

Bidirectional Replication

This is where two databases replicate the same articles to each other via a distributor. There must be loopback detection. Data conflicts aren't handled and the replication must be implemented in code, since the GUI doesn't support it.

Transactional replication tracks changes through the SQL Server transaction log

Merge Replication

Merge replication allows various sites to work autonomously and later merge updates into a single, uniform result.

Merge Replication is complex, but provides the means to implement part of a high-availability system, as well as its original purpose of serving mobile and disconnected users. It is designed for cases where the publishers are not in constant communication with the subscribers. After the initial snapshot synchronization, subsequent changes are tracked locally with triggers, and the databases are merged when in contact, using a series of rules to resolve all possible conflicts.

Merge replication is used when several Subscribers might need to update the same data at various times and propagate those changes back to the Publisher and thence to other Subscribers. It is also required in applications that involve Subscribers receiving data, making changes offline, and finally reconnecting with the publisher to synchronize changes with the Publisher and other Subscribers.

To make this possible, each Subscriber requires a different partition of data and there has to be a set of rules to determine how every conflict that takes place in the update of the data is detected and resolved. These conflicts occur when the data is merged because there can be no 'locking' and so the same data may have been updated by the Publisher and by more than one Subscriber.

Merge Replication does not use transactions. Merge replication uses a set of conflict-resolution rules to deal with all the problems that occur when two databases alter the same data in different ways, before updating the subscribers with a 'consensus' version. It normally works on a row-by-row basis but can group rows of related information into a logical record. One can specify the order in which 'articles' are processed during synchronisation.

Merge replication tracks changes through triggers and metadata tables.

Replication Agents

Replication is done by several different agents, which are separate applications each responsible for part of the process. The replication agents should not be run under the SQL Server Agent account in a production system. Instead, they need the minimal permissions necessary to perform their function.

SQL Server Agent

This manages the overall replication process via SQL Server Agent jobs.

The Snapshot agent

Snapshot.exe executes on the Distributor. It extracts the schema and data defined by the publication, which is then sent to the subscriber via a 'snapshot folder'. It also updates status information on the distribution database. . It is used in all forms of replication

The Log Reader Agent

LogRead.exe is used in transactional replication to extract relevant committed transactions from the publisher's log, repackage them and send them to the distributor in the correct sequence.

Distribution Agent

Distrib.exe takes the snapshots, and log entries from the agents we've described, and dispatches them to the subscribers.

Merge Agent

ReplMer.exe is used only in Merge Replication to send a snapshot when the subscriber is initialized, and also exchanges transactions between publisher and subscriber

Queue Reader Agent

QrDrSvc.exe is used to queue the updates in transactional or snapshot replication when queuing has been specified.

Monitoring Replication

Many problems associated with replication can be avoided by .regular checks. The most obvious check is to make sure that the data has been transferred as expected. Periodic checks with SQL Compare and SQL Data Compare can be very useful in addition to the tools that come with Replication. Additionally the replication processes and jobs need to be checked to make sure they are working.

Checking throughput

The performance of replication must be regularly monitored, and performance-tuned as necessary.

The Replication Monitor is used to check on the operational state of publications, and inspect the history, and errors. Right-clicking the replication node in Object Explorer will gain access to it.

One of the most important concerns is the time delay, or latency, of transactions from the publications appearing in the subscriber database. At times of high transaction throughput on the publisher database, bottlenecks can occur. Whereas the stored procedure sp_browseReplCmds on the distribution database can tell you how far behind the synchronisation is at any particular time, one cannot determine where the problems lies just from the data. Tracer tokens are now used to measure the actual throughput of the replication architecture at any particular time to help diagnose such bottlenecks.

Validating

There is always an element of doubt as to whether the replication has entirely worked. There are stored procedures provided to compare the 'articles' on the publisher and subscribers to make sure they are the same.

The sp_publication_validation stored procedure validates the data associated with each article by calling sp_article_validation (after the articles associated with a publication have been activated). The sp_article_validation stored procedure invokes sp_table_validation stored procedure, which calculates the number of lines and, optionally, the checksum of the published table. It is considered good practice to perform a daily row-count and weekly checksum. SQL Data Compare is ideal for mending a broken replication.

The Distribution Agent raises the '20574' system message if validation fails, or the '20575' system message if it passes. The Distribution Agent will replicate changes to a subscriber even if the validation shows that the subscriber is out of synchronization. It is a good policy to configure the Replication Alert on the '20574' message so as to send E-Mail, Pager, or Network notification.

This validation approach will only work within certain restrictions. For example, it will not work if certain filters have been applied. They should be used with caution.

Changing the settings

It is best to use the default replication settings unless there are clear performance gains to be made, or if the application design forces the issue. However, one cannot assume that the changes will be generally beneficial to the entire topology without comprehensive testing.

Articles

Articles are the smallest unit of a publication. An article can be a table, view, stored Procedure or function. Where an article is based on a table or view, it can contain all the data or just part of it. These filters of two types.: More common are the static 'WHERE' clauses, but filters can be used dynamically in Merge Replication to publish different 'content' (rows) to different 'subscribers' (databases receiving data). These latter Filters are called 'Dynamic' and can be simple Row Filters, or Join Filters, where the selection of rows to publish is based on a join with other tables, rather than a simple WHERE clause.

Normally, any alteration to an article that is a table is propagated to all the subscribers. You can also opt to propagate schema objects associated with the article such as indexes, constraints, triggers, collation and extended properties.

Updating articles

In Merge replication, the subscriber can update the article. This is, of course, a recipe for conflict, and these have to be resolved automatically. When the Merge Agent comes across a row that might have changed recently, it examines the history or 'lineage' of each site's version of the row to see if there is a conflict. If so, then the update that is finally used. Has to be based on either

A "first wins" resolution,
a user-specified priority scheme to determine the update to select,
a customised resolution, using COM and stored procedures.

The 'lineage' is a history of changes in a table row in MSmerge_contents, which is maintained automatically when a user updates a row. Each column contains one entry for each site that has updated the row.

Conflicts to the data in the base table can occur within a column or a row. Most usual are column-tracked articles this means that, within any row, updates are only recognized as conflicts if the same column is updated by more than one subscriber. Occasionally, however, the business rules of the application may treat simultaneous changes to the any column within the row as a conflict, in which case row-level tracking is used.

Programming Replication Topologies

Replication agents and replication topologies can be administered and monitored remotely via SQL Scripts or RMO scripts. The task of building and maintaining replication topologies is made much easier of all parts of the deployments are scripted, even if this is done after the other methods such as wizards or RMO are used for the initial operation.

There are other uses for a replication script. It will be required in end-user application where, for example, such as s a pull subscription is synchronized when the user clicks a button, or where a routine administration task such as monitoring replication throughput is performed from a custom console. It is also generally used for writing customized business rules that are executed when a merge subscription is synchronized.

One can use the Object Explorer in SSMS, or the Enterprise Manager in earlier versions of SQL Server, to set up replication. BOL provide worked examples. Alternatively, RMO can be used with VB or C# to script the replication. Whatever system you use, it is a good idea to use the Transact SQL script as the reference for the replication topology you create

Ultimately, the functionality in a replication topology is provided by system stored procedures. The easiest approach is to use Transact-SQL script files to perform a logical sequence of replication tasks, because it provides a permanent, repeatable, copy of the steps used to deploy the replication topology that can, for example, be used to configure more than one subscriber. It also provides a measure of documentation and disaster-recovery. A script can be stored as a query object in a SQL Server Management Studio project.

Replication scripts can be created by hand, by the script generation of the replication wizards in SQL Server Management Studio, or by using Replication Management Objects (RMOs) to programmatically generate the script to create an RMO object.

When creating scripts to configure replication, it is best to use Windows Authentication so as to avoid storing security credentials in the script file. Otherwise you must secure the script file

Temporary Tables

The simple answer is yes you can. Let look at a simple CREATE TABLE statement:

CREATE TABLE #Yaks (
YakID int,
YakName char(30) )

You'll notice I prefixed the table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table. The others are covered in Books Online.

Temporary tables are created in tempdb. If you run this query:

CREATE TABLE #Yaks (
YakID int,
YakName char(30) )

select name
from tempdb..sysobjects
where name like '#yak%'

drop table #yaks

You'll get something like this:

name
------------------------------------------------------------------------------------
#Yaks_________________________ . . . ___________________________________00000000001D

(1 row(s) affected)

except that I took about fifty underscores out to make it readable. SQL Server stores the object with a some type of unique number appended on the end of the name. It does all this for you automatically. You just have to refer to #Yaks in your code.

If two different users both create a #Yaks table each will have their own copy of it. The exact same code will run properly on both connections. Any temporary table created inside a stored procedure is automatically dropped when the stored procedure finishes executing. If stored procedure A creates a temporary table and calls stored procedure B, then B will be able to use the temporary table that A created. It's generally considered good coding practice to explicitly drop every temporary table you create. If you are running scripts through SQL Server Management Studio or Query Analyzer the temporary tables are kept until you explicitly drop them or until you close the session.

Now let's get back to your question. The best way to use a temporary table is to create it and then fill it with data. This goes something like this:

CREATE TABLE #TibetanYaks(
YakID int,
YakName char(30) )

INSERT INTO #TibetanYaks (YakID, YakName)
SELECT  YakID, YakName
FROM  dbo.Yaks
WHERE  YakType = 'Tibetan'

-- Do some stuff with the table

drop table #TibetanYaks

Obviously, this DBA knows their yaks as they're selecting the famed Tibetan yaks, the Cadillac of yaks. Temporary tables are usually pretty quick. Since you are creating and deleting them on the fly, they are usually only cached in memory.

Table Variables

If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory. The code above using a table variable might look like this:

DECLARE @TibetanYaks TABLE (
YakID int,
YakName char(30) )

INSERT INTO @TibetanYaks (YakID, YakName)
SELECT  YakID, YakName
FROM  dbo.Yaks
WHERE  YakType = 'Tibetan'

-- Do some stuff with the table

Table variables don't need to be dropped when you are done with them.

Which to Use

If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables.
If you need to create indexes on it then you must use a temporary table.
When using temporary tables always create them and create any indexes and then use them. This will help reduce recompilations. The impact of this is reduced starting in SQL Server 2005 but it's still a good idea.

Answering the Question

And all this brings us back to your question. The final answer to your question might look something like this:

DECLARE @TibetanYaks TABLE (
YakID int,
YakName char(30) )

INSERT INTO @TibetanYaks (YakID, YakName)
SELECT  YakID, YakName
FROM  dbo.Yaks
WHERE  YakType = 'Tibetan'

UPDATE  @TibetanYaks
SET  YakName = UPPER(YakName)

SELECT *
FROM @TibetanYaks

Global Temporary Tables

You can also create global temporary tables. These are named with two pound signs. For example, ##YakHerders is a global temporary table. Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it. These are rarely used in SQL Server.

Summary

That shows you an example of creating a temporary table, modifying it, and returning the values to the calling program. I hope this gives you what you were looking for.

(http://www.sqlteam.com/article/temporary-tables)

Thứ Ba, 11 tháng 10, 2011

Table width in ie

table-layout takes two values:

1. auto: the table is layed out normally (see below). This is the default value.
2. fixed: the table obeys the width, even if it normally wouldn't.

Let's give the test table a width. This width is far too narrow to normally show all content, and therefore the table stretches up beyond the 100px. This is normal behaviour: widths on tables have always been more like a polite advice than a strict rule. If the table needs more space than the width allows, it takes more space.

< table style="width: 100px;" >

If we add table-layout: fixed, however, the table obeys the width, even if that results in unreadable content.

< table style="table-layout: fixed; width: 100px;" >

(http://www.quirksmode.org/css/tables.html#tablelayout)

Thứ Hai, 10 tháng 10, 2011

Check email is valid

ScrollTo with jQuery

$('html,body').animate({scrollTop: $("#"+id).offset().top},'slow');

(http://djpate.com/2009/10/07/animated-scroll-to-anchorid-function-with-jquery/)

Thứ Tư, 5 tháng 10, 2011

Reset form with jQuery

$('#formId').reset(); // error

$('#'+id).each(function(){this.reset();}); //ok
or
$('#'+id)[0].reset();

(http://simple.procoding.net/2008/11/22/how-to-reset-form-with-jquery/)