retrieve table name type field name field type

The following sql command gets you the Table Names, and table names’ field names, field names’ datatype and datatype’s length from a DATABASE

SELECT table_name=sysobjects.name,
         column_name=syscolumns.name,
         datatype=systypes.name,
         length=syscolumns.length
    FROM sysobjects 
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U'
ORDER BY sysobjects.name,syscolumns.colid

named pipes provider no process is on the other end of the pipe error 233

Can anyone tell me what the cause of the error below is?

I have the client tools installed on XP Pro Svc Pack 2.0. When I try to
connect to the server on the remote machine I get the error message below.

I have set the logins up correctly for the user id I'm using on the client
machine and the remote SQL Server 2005 is setup to accept remote connections.
Naturally, when I go to the info site the error has no information available.

the SQL Server 2005 installation is on a Server 2003 OS.

Thanks...

TITLE: Browse Server for Database
------------------------------

A connection was successfully established with the server, but then an error
occurred during the pre-login handshake. When connecting to SQL Server 2005,
this failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections. (provider: Named Pipes Provider,
error: 0 - No process is on the other end of the pipe.) (Microsoft SQL
Server, Error: 233)

==================================

In SQL Server 2005,
1. use the SQL Server Configuration Manager.
2. Go to the SQL Server 2005 Network Configuration node
3. and under that you will find the protocols.
4. Enable TCP/IP

In SQL Server 2000, use the Server Network Utility. On the
general tab, you select the protocols to enable.
You need to restart the service after enabling protocols.

-Sue

No process is on the other end of pipe

"No process is on the other end of pipe" caused by bad certificate
In this blog, I am focusing on one specific error message which I think a little confusing for you to troubleshoot the connectivity issue against SQL Server 2005.

To be clear, please make sure you saw the following exact same symptom before you apply the resolution that this blog provide since the same error message could also caused by other reasons which is out of scope of this blog.

1) OS on the server could be WinXP/Win 2K/ Win 2K3/ Vista.

2) SQL Server 2005

3) Server was started and from Server ERRORLOG, you can see "The certificate was successfully loaded for encryption."

4) Make local or remote connection over SNAC(SQL 2005 Client) or SqlClient(ADO.NET 2.0) provider, you see any of following error message depends on what protocols enabled on your sql server:

Shared Memory Provider: No process is on the other end of the pipe.


Named Pipes Provider: No process is on the other end of the pipe.


TCP Provider: An existing connection was forcibly closed by the remote host.

5) The problem is consistently not intermitently whenever you make a fresh connection.

6) If you try connection over MDAC( SQLOLEDB.1 provider or {SQL Server} driver) you would get consistent and more descriptable error message like:

[DBMSLPCN]SSL Security error
[DBMSLPCN]ConnectionOpen (SECDoClientHandshake()).

The cause behind would be various such as SQL Server loads the bad cert due to different OS behavior such as between XP/2K and 2K3, later on connection fail since certificate validation does not pass on the server based on validation flag provided by the client.

The resolution here is:

1) If you are not trying encryption, first, see whether you explicitly specify a cert? if so, then delete it; otherwise, go to local machine or user certificate personal store, delete all certificates, and restart sql server, doulbe check Server ERRORLOG, see " A self-signed certificate was successfully loaded for encryption.", then retry connection.

2) If you must use encrypted connection, you need a good certificate for SQL Server 2005. Please see blog: http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx

to understand what kind of certificate is qualified for SQL 2k5. Also, you can go to SQL Server Configuration Manager, right click properties for the sql instance, and choose *Certificate* tab, choose any certificate in the drop down list which has already been validated by SQL 2k5, then restarted the sql instance and retry connection.

The error you may hit more frequently when you did not choose a cert and SQL Server load the bad cert from certificate store. There are various bad cert that cause the problem, see kB:http://support.microsoft.com/kb/919710/en-us for one example.

select all table name all field name in one database

select all table name all field name in one database

use your_db_name
go

SELECT  
o.name, c.name, t.name  
--o.name
FROM  sys.objects o    
JOIN  sys.columns c ON o.object_id = c.object_id    
JOIN  sys.types t ON c.system_type_id = t.system_type_id    
WHERE  
t.name not like 'sysname'
and o.name not like 'sys%'
and o.name not like 'queue_messages%'

--group by o.name
ORDER BY o.name  
go

Argument data type ntext is invalid for argument 1 of replace function.

update table_name set [content] = replace(cast([content]AS NVARCHAR(MAX)), '.pdf.pdf', '.pdf') 

changing varchar to nvarchar - Erland Sommarskog

changing varchar to nvarchar - Erland Sommarskog
11-Nov-07 10:24:28


Better Article - change database columns from varchar to nvarchar if not already nvarchar

Hans - DiaGraphIT - (HansDiaGraphIT@) writes:

Run any of these SELECT:

-- SQL 2005
SELECT 'ALTER TABLE ' + quotename(o.name) +
' ALTER COLUMN ' + quotename(c.name) +
' nvarchar(' + ltrim(str(c.max_length)) + ') ' +
CASE c.is_nullable
WHEN 1 THEN ' NULL'
WHEN 0 THEN ' NOT NULL'
END
FROM  sys.objects o
JOIN  sys.columns c ON o.object_id = c.object_id
JOIN  sys.types t ON c.system_type_id = t.system_type_id
WHERE t.name = 'varchar'
ORDER BY o.name, c.name

-- SQL 2000
SELECT 'ALTER TABLE ' + quotename(o.name) +
' ALTER COLUMN ' + quotename(c.name) +
' nvarchar(' + ltrim(str(c.length)) + ') ' +
CASE columnproperty(o.id, c.name, 'AllowsNull')
WHEN 1 THEN ' NULL'
WHEN 0 THEN ' NOT NULL'
END
FROM  sysobjects o
JOIN  syscolumns c ON o.id = c.id
JOIN  systypes t ON c.xtype = t.xtype
WHERE t.name = 'varchar'
AND o.xtype = 'U'
ORDER BY o.name, c.name

Copy and past the result. However, there is a reason that I recommended
rebuilding from scripts. ALTER TABLE only takes one ALTER COLUMN, so
if there are several varchar columns in the same table, SQL Server
have to rebuild the tables several times. Since nvarchar takes up twice
the space of varchar, this is no simple metadata change. For large
tables, this could take a long time. Building a new database may be
faster route.


But maybe some of the columns are indexed? In such case, you need to
drop these indexes first.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

change database columns from varchar to nvarchar if not already nvarchar

change database columns from varchar to nvarchar if not already nvarchar

Hello, I am in a situation where I must update an existing database structure from varchar to nvarchar using a script. Since this script is run everytime a configuration application is run, I would rather determine if a column has already been changed to nvarchar and not perform an alter on the table. The databases which I must support are SQL Server 2000, 2005 and 2008.



The following query should get you what you need:

IF EXISTS   (SELECT *   FROM sysobjects syo   JOIN syscolumns syc ON     syc.id = syo.id   JOIN systypes syt ON     syt.xtype = syc.xtype   WHERE      syt.name = 'nvarchar' AND     syo.name = 'MY TABLE NAME' AND     syc.name = 'MY COLUMN NAME')BEGIN   ALTER ...END

You can run the following script which will give you a set of ALTER commands:

SELECT 
'ALTER TABLE ' + isnull(schema_name(syo.id), 'dbo') + '.' + syo.name + 
' ALTER COLUMN ' + syc.name + 
' NVARCHAR(' + case when syc.length >= 4000 Or syc.length = -1 then 'MAX' ELSE convert(nvarchar(10),syc.length) end + ');' 
FROM sysobjects syo 
JOIN syscolumns syc ON syc.id = syo.id 
JOIN systypes syt ON syt.xtype = syc.xtype 
WHERE syt.name = 'varchar' AND syo.xtype='U'

-- Note: You will need another script for converting text to ntext !!!!

There are, however, a couple of quick caveats for you.

1. This will only do tables. You'll want to scan all of your sprocs and functions to make sure they are changed to NVARCHAR as well.

2. If you have a VARCHAR > 4000 you will need to modify it to be NVARCHAR(MAX)
But those should be easily doable with this template.

If you want this to run automagically you can set it in a WHILE clause.

======================================

when you alter the field from varchar to nvarchar, you might encounter following error message:

Cannot alter or drop column 'title' because it is enabled for Full-Text Search

Use following script to select all the fields that have full text index enabled.

select 
c.name as 'Table',b.name as 'Field'
--c.*, a.*, b.*
from sys.fulltext_index_columns a 
join sys.columns b
on a.object_id=b.object_id
and a.column_id=b.column_id
join sys.objects c
on a.object_id=c.object_id

Alter table script: This script is for generating disable full text search scripts.

select 
'ALTER FULLTEXT INDEX ON dbo.' + c.name + ' DROP (' +  b.name + ') WITH  NO POPULATION;'
--c.*, a.*, b.*
from sys.fulltext_index_columns a 
join sys.columns b
on a.object_id=b.object_id
and a.column_id=b.column_id
join sys.objects c
on a.object_id=c.object_id

MySQL or SQL Server: Look beyond politics and hype when deciding which to use

Takeaway: MySQL may be free, but what if money isn't the only determining factor? Find out how these two database heavyweights stack up against each other and how to decide which one to use as your database system.


Two of the most popular back-end data stores Web developers work with today are MySQL and SQL Server. They are fundamentally similar in that both are data storage and retrieval systems. You can use SQL to retrieve data with either because both claim support for ANSI-SQL. Both database systems support primary keys and key indices, so you can also create indices used simply to speed up queries and for constraining input. Further, both provide some form of XML support.

Aside from the obvious difference of price, what distinguishes these two products from each other, and how do you choose between them? Let's take a look at the core differences between these two products, including licensing costs, performance, and security.

Core principles are the root differences
The differences begin with principles: open vs. proprietary. SQL Server with its closed, proprietary storage engine is fundamentally different from MySQL’s extensible, open storage engine. You’re stuck with the SQL Server’s Sybase-derived engine for better or worse, while MySQL provides multiple choices such as MyISAM, Heap, InnoDB, and Berkeley DB.

This open vs. closed difference is, by itself, enough reason for some folks to choose one over the other. But, there are some technical differences as well. To begin with, MySQL doesn't fully support foreign keys, making it less of a relational database than SQL Server, which has full relational features. Also, MySQL has previously lacked support for stored procedures, and the default MyISAM engine doesn't support transactions.

Licensing costs: MySQL isn't always free, but it is cheap
When it comes to licensing costs, both products use a two-tiered scheme. With SQL Server, the most popular way to get a free developer license is to purchase a license for Microsoft Office Developer or Microsoft Visual Studio, both of which give you a free "development use only” SQL Server license. If, however, you want to use it in a commercial production environment, you'll have to pay for at least the SQL Server Standard Edition, which will set you back around $1,400 for five client connections.

On the other hand, MySQL is open source and licensed through the GNU General Public License (GPL). For developers, this means that as long as the project you are working on is also open source, you don't have to pay to use MySQL. If, however, you plan to sell your software as a closed-source product, you'll need to pick up a commercial license, which currently costs $440 for up to nine clients. Schools and nonprofits are exempt from this commercial licensing requirement.

Performance: Advantage MySQL
In terms of pure performance, MySQL is the leader, mostly due to its default table format, MyISAM. MyISAM databases are very compact on disk and place little demand on CPU cycles and memory. MySQL can run on Windows without complaint but performs better on UNIX and UNIX-like systems. You can experience additional performance gains by using MySQL on a 64-bit processor (e.g., one of those sweet SPARCstations), because MySQL uses an abundance of 64-bit integers internally. Much of the very busy Yahoo! Finance portal uses MySQL as a back-end database.

As I mentioned, with MySQL, you have a choice of table formats, but generally, these nondefault choices exact a cost in increased resource usage over MyISAM. Typically, though, these alternative table formats provide some additional functionality. For example, Berkeley DB supports transactions and actually has better performance with indexed fields than MyISAM.

When it comes to performance, SQL Server's strength—providing many more features than its competitors—is also its weakness. Granted, many of these features are geared toward performance tuning, but being a feature-rich environment means sacrificing something else. In this case, the cost is additional complexity, disk storage, memory requirements, and poorer performance. If you can't afford to support SQL Server with powerful hardware and trained expertise, you should definitely look elsewhere for a DBMS because you likely won’t be happy with the results.

It's worth noting that both systems will work well within either a .NET or J2EE architecture. Similarly, both will benefit from RAID, and both will perform best if the data store is on a hard drive or array that is solely dedicated to that purpose.

Replication and scalability: A dead heat
MySQL keeps a binary log of all SQL statements that change data. Because it’s binary, this log can be used to replicate data from the master to the storage on one or more slaves very quickly. Even if the server goes down, the binary record is still intact, and replication can take place. For query-heavy databases systems, MySQL scales easily into large data farms.

In SQL Server, you can also record every SQL statement, but doing so can be costly. I know of one development shop that had to do this because of other architectural issues, and the sheer volume of data that they were storing on tape was quite remarkable. Instead, SQL Server relies on elaborate mechanisms of record and transaction locking, cursor manipulation, and dynamic replication of data to keep database servers synchronized. If you're skilled at juggling these mechanisms, replication is pretty easy.

Security: Also tough to call
Both products have perfectly acceptable default security mechanisms, as long as you follow the manufacturer's directions and keep up with your security patches. Both products operate over well-known default IP ports, and, unfortunately, those ports draw intruders like flies to honey. My firewall logs are always chock-full of folks trying to contact nonexistent database instances on my machine over the default ports. Fortunately, both SQL Server and MySQL allow you to change that port should the default become too popular a destination for your taste.

Recovery: Advantage SQL Server
Failsafe and recovery is one area where MySQL, in its default MyISAM configuration, falls a little short. With MyISAM, a UPS is absolutely mandatory because MyISAM assumes uninterrupted operation. If it is shut down unexpectedly, the result could be corruption of the entire data store and loss of all your data. SQL Server, on the other hand, is far more resistant to data corruption. SQL Server’s data goes through multiple checkpoints as it passes from the keyboard to the hard disk and back out to the monitor. And SQL Server remembers where it was in the process even if it happens to be shut down without warning.

The best choice depends on the situation
If you were hoping to get an ironclad recommendation that one database is better than the other, I’m going to disappoint you. From my point of view, any database that helps you do your job is a good database; one that doesn’t is a bad database. I can tell you that to make a good decision about which of SQL Server and MySQL will help you most, you’ll need to look beyond politics and hype and instead look at function and mission. What do you want to accomplish?

If you're trying to build a .NET services architecture, synchronizing data between multiple disparate platforms, or learning the loftier precepts of database management, SQL Server will help you most. If you're building a third-party-hosted Web site, pushing a lot of data out to a lot of clients, or have a budget in the neighborhood of free, then MySQL will be your best bet.

--------------------------------------------------------------------------------
Which do you use, and why?
Is your Web app or site powered by MySQL or SQL Server? What led you to make this decision, and why was it the right choice for you? Tell us about it in the discussion below.

1. MySQL Performance fulsomepraise - 03/17/03
I am a little confused as to why you say that MySQL has the advantage in performance. How does this assertion relate to the database benchmarks listed by the Transaction Processing Performance Council (www.tpc.org)?

The TPC find SQL Server 2000 to comparable to Oracle 9i and, thus, at the top of scale in terms of database performance.
Reply 1.1. Is this relevant? uricon - 03/17/03
Yes, but if you look at the TPC members, MySQL AB do not feature, therefore MySQL stats aren't included. Not really a compelling argument!
Reply 1.1.1. Yes but why aren't they in there? Bunce - 03/17/03
From what I read in a recent article, TPC is basically a 'Pay-to-be-listed' type of rating.

Therefore if your latest benchmark is better than the rest, then you give your permission for the benchmark to be published (if you read the EULA of most RDBMS they prohibit benchmarks to be published).

In other words, mySQL either doesn't pay or give permission to be published because its not fast enough...

Check this out:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=38348

Cheers,
Andrew
Reply 1.1.1.1. Perhaps too expensive? irongut - 03/17/03
In my experience 'pay to be listed' ratings of this kind tend to be very expensive. Perhaps mySQL is not listed not because they feel their performance is lacking but because the product is essentially free or cheap and so doesn't have the financialmight of MS, Oracle, etc behind it.

I've always found mySQl to be efficient but I haven't run any reliable benchmarks.
Reply 1.1.1.2. Have you tried MySQL? KeeBored - 03/17/03
My dear friends, I have used both MYSQL and MSSQL to build database driven websites, and I have to say, MYSQL is remarkably faster than MSSQL. For example (for those of you who have not had the chance to try them both) a standard page that loads a data recordset of say 1,000 records will take around 4 seconds to load when using MSSQL while it takes 1 second to load in MYSQL. Not that is what I call PERFORMANCE.
I still use MSSQL for other applications though because of its failsafe/recovery features.
I bet Mr Bounce from Message #3 on this forum has probably not experienced it first hand.

Laters
Reply 1.1.1.2.1. Yes I have. 1) If your 1000 record set t... Bunce - 03/18/03
Yes I have.

1) If your 1000 record set takes 4 seconds to load then your coding is floored, not the database.

2) Read the article I posted you dill.
Reply 1.1.1.2.2. Nice code echava - 06/12/03
You are most likely having problems with the way your code is accessing the database because a 1000 rowset result takes much less than a second in SQL server. There is no way that in a DATABASE speed comparison you're going to find that MySQL is faster. You're testing the database through your web page. That's the problem. Four seconds to return 1000 rows? Nice try.
Reply 1.1.1.2.3. How not to compare MS SQL and MySQL D_Gilmour_1978 - 10/20/03
Measuring how long it takes for records to appear on a web page is not a fair test.
There are many factors that can effect this.
Eg. Apache vs IIS is the database on the same box as the web server etc.

I have used both products on large projects and have found MySQL to be lacking in features such as sub queries. This causes me to have to write application code to deal with this short comming. So for large complex applications I would have to say SQL Server is more suited to the job. However for web development i often choose MySQL as it is cheaper and does the job as good as anything as web applications tend to be less complex than n-tier 300 user applications (In my experience anyway).

I get sick of 'Microsoft Bashing' as SQL Server is a very good product. Compared to any other commercial product (DB2,Oracle etc)it is very good value for money.
Reply 1.1.1.2.3.1. PostgreSQL felipe.alfaro@... - 05/17/08
And people always forget of PostgreSQL, or Firebird. As if
MySQL was the only open-source damn-good RDBMS.
Reply 1.1.2. what about some performance benchmarks fulsomepraise - 03/18/03
It's not really an argument, just a question.

If MySQL is not included in TPC benchmarks for whatever reason then fine but at least the article should give us some other performance benchmarks instead of just asserting that MySQL beats SQL Serveron performance.
Reply 1.1.2.1. Qualitative Bucky Kaufman (MCSD) - 03/19/03
That's an excellent point - that comparisons should be backed up with actual metric values, but this piece was intended more as a qualitative analysis than a quantitative one... to help architects determine which tool to use for a job.

Quantitative performance metrics, especially between dissimilar systems, are often misleading. If I say that Product A supports 100 tics per timeslice, and Product B supports 100 times that - it would infer that Product B performs better. But if Product A requires 1000 events to perform the same function that Product B does in 1 event - it would flip the calculation upside down... because Product B can perform that one function faster, even though Product A performs functions in general faster. It all gets too weird.

That's why I focused on a qualitative analysis. I wanted to showcase the qualities of each. In other articles, where products that are more comparable are analyzed I do rely heavily on benchmarks to support quantitative claims.
Reply 1.1.2.2. Politics of benchmarks charlie.garry@... - 04/10/03
It is unlikely thatwe will see MySQL used in any TPC (e.g. TPC-C, TPC-H) benchmarks because these are controled by the commercial vendors (both hardware and database) who make the rules. You may want to check out eWeek which did a benchmark of all the major relational databases and included MySQL 4.0 (Server Databases Clash by Timothy ****, eWeek, February 25, 2002 http://www.eweek.com/article2/0,3959,293,00.asp)
Reply 1.2. Performance Metrics Bucky Kaufman (MCSD) - 03/18/03
There are a wide variety of benchmarks that you can use to compare MySQL and MS SQL, but "Transaction Performance" is not one of them. MySQL uses an extended ISAM format that is not transactional

My claim that MySQL has better performance than MS SQL is based on the fact that MySQL queries on large tables perform much faster than MS SQL queries on similar tables.

MS SQL is much more feature-rich and fault-tolerant, but those extended features come at the cost of performance.
Reply 1.2.1. Your mileage may vary JeriMorris - 03/24/03
There are so many factors that may affect performance that I find it hard to place much value in a blanket statement like "In terms of pure performance, MySQL is the leader."

Are you doing heavy-duty transaction processing? What about reporting and decision support requirements? Are you modifying existing data or inserting new data? Wide or narrow rows? Simple data types or images/blobs? What role does indexing play? How does performance change when the number of users changes?

Which is faster, MySQL or MS SQL? I have to think that the answer varies, depending on your needs.
Reply 1.3. TPC-C felipe.alfaro@... - 05/17/08
TPC-C is just propaganda. There is not way to compare two
different systems' TPC-C score as the hardware can be way
too different: one system can be a NUMA system using 2048
CPUs and the other one a federated database across a cluster
of 1,000 machines. Reply 2. Licensing uricon - 03/17/03
A couple of points about the licensing.
If you are using MySQL for development of even a commercial product, and then distribute your software WITHOUT MySQL server (or drivers / library code) then you do not seem to incur any license costs.
Also, educational users who wish to distribute the software must do so under the GPL, so there's no specific exclusion for them.
Clearly, if you are developing within an organization and wish to use the finished software with a copy of MySQL without making it GPL, a commercial license is applicable.
Many people choose to pay the commercial license fees so that they do not run the risk of making their software GPL by the implication of not buying a commercial license, and to have access to support and the latest functionality.

Details of licensing are included on the MySQL web site at http://www.mysql.com/products/licensing.html - it's very clearly explained and lacking the usual legalese that makes you roll your eyes and despair!
Reply 2.1. More about licensing petskippy@... - 03/25/03
Something the author of the original article missed.

With MySQL, the license is NOT per-client as he stated. Instead, it is PER-SERVER!.

Taken from the MySQL Licensing page:
"The MySQL Server license is per database server (single installed MySQL binary). There are no restrictions on the number of connections, number of CPUs, memory or disks to that one database server."

Therefore, whereas the author mentions that for $440 you get a single server with up to 9 clients. That is in error. The licensing is really such that your pay $440 per server if you are licensing 1 to 9 servers.

I really would appreciate if the Builder.com authors would spend more time researching their articles. I've seen articles with misleading informationseveral times. Reply 3. Political paragmehta - 03/17/03
Well this article is politics in itself. When the author talks about benefits of one product, without considering any benefits of other, then it's a political article in itself.

You say, performance of MySQL is better, have you done any tests yourself? Have you tested MySQL database on 200GB Database? Needless to say, this author has preference towards MySQL and his trying to force it to others.
Reply 3.1. apples and oranges Troy T. - 03/17/03
It's hard to compare MySQL and SQL Server performance when they are so different in features. Besides, sometimes real referential integrity, transaction logs, record level locking, are more important that pure speed.
Reply 3.1.1. SQL Server =Features and MySQL =speed wayland@... - 03/20/03
If you can develop easily within MySQL features then speed could be the difference between practical and impractical. If you have a spare bit of hardware and cash to run NT and SQL Server then this is probably the easiest road. However if the only hardware available is the Raq4 you rent at your ISP then MySQL is superb. Simply adding an NT server to your hosting fee would increase costs. Setting this up with the correct licences for SQL Server will really cost. The more money you spend the lessyou keep.
Reply 3.2. MYSQL Tendencies KeeBored - 03/17/03
Although I posted some other comments up there, I do agree with paragmehta's point. Evidently the author of this article has a tendency for MYSQL. There were many features mentioned on the article about MYSQL, but there was not much about MSSQL, although MSSQL is fully loaded with features.
Reply 3.2.1. Features felipe.alfaro@... - 05/17/08
How many of those features are you using?
Reply 3.3. Not that Political mattohare@... - 03/18/03
We all have our opinions. However, being a mostly-SQL Server developer/manager myself, I did not see his article as biased against SQL Server. In deed, he did point out places where SQL Server was the better choice.

To me, if the author had a bias, I just didn't see it in this article. Maybe that's because MySQL has never made it past preliminary evaluations with me.

I still hold that SQL Server is a better business management tool. It has full foreign key support. In my years of experience, that is _very_ important. Most of the problems I've had to resolve are around ghosts left in others' applications. ("A $100,000 loan is on my report, and nobody has one!")

I do see MySQL as being a fairly good datamart image of the maindatabase, for use to feed web pages. It could also be good for fast data collection.
Reply 3.4. MS Bashing Bucky Kaufman (MCSD) - 03/18/03
In reply to:
Needless to say, this author has preference towards MySQL and his trying to force it to others.
----- ----- ----- ----- ----- -----

Actually, the author (me) is an MCSD and a HUGE Microsoft fan. My preference is to use MS products regardless of the alternatives.

SQL Server is, in many ways, a superior product. But the simplicity of MySQL makes it the ideal choice for many distributed applications.

But more often than not, a '74 Ford Pickup is a better choice than a train for transporting freight.
Reply 3.5. Politics? felipe.alfaro@... - 05/17/08
Is not about politics. It's about freedom. Go the SQL Server
way and you will depend on Microsoft for the rest of your
life. This means you will be tied to a hardware platform
(PC), a software platform (Windows), and a single vendor
(Microsoft).

This is very, very bad. One day, you will want to migrate
to other system (hardware, software, vendor) and you will
be stuck, and you will regret, and the cost of migration
will be huge. And then you will realize why having truly
free, open-source systems (implementation and design) is
just what you business need to solve your business
problems.

The only caveat here is that some features you think you
need are only supported by one of those extremely
proprietary RDBMs. But people can live with BigTablle's
extremely simple semantics, so it should not be that
difficult to ignore those fancy, vendor-specific features of
SQL Server or DB2 or Oracle. Reply 4. Used for Mid-Large Systems? keHogan - 03/17/03
What is the best usage of each? For instance, I don't see critical systems using MySQL. Why would that be?
Reply 4.1. Critical Systems Brant Fitzsimmons - 03/17/03
In the article it stated that Yahoo was using it for their Yahoo! Finance portal.

Yahoo, having the 3rd most visited site on the Internet, with over 39 million unique users (according to Nielson//Netratings), qualifies as a very large, critical application. Wouldn't you agree?
Reply 4.1.1. Amazon mattohare@... - 03/18/03
Seems Amazon.com uses them too.
Reply 4.1.2. Yahoo Finance, not yahoo itself newm0004@... - 03/18/03
The article said that yahoo finance uses is, but your comment alludes to ALL of yahoo using mysql. I doubt that is the case.

Yahoo finance probably is not even in the top 100 sites.

Not bashing, just pointing out a minor flaw in logic.S
Reply 4.1.2.1. not really coryj - 01/05/04
Yahoo Finance is a massive site on its own. It's by far the largest finance on the web and I'd guess that it contributes a large portion of the overall Yahoo traffic.
Reply 4.1.2.2. Before you go pointing out flaws in logic... Brant Fitzsimmons - 11/15/04
...please re-read my post. I never said that Yahoo! was using it for their entire operation. In the very first sentence of my post I state, "In the article it stated that Yahoo was using it for their Yahoo! Finance portal."

Seems pretty clear.

Also, given that Yahoo is in the top 5, it's probably a good bet that their finance portal is hit really, really hard. If MySQL wasn't able to hold up to that kind of load it wouldn't have been chosen to do the job.
Reply 4.2. Really? felipe.alfaro@... - 05/17/08
I know of at least two of them. Google and Amazon. Google
has a huge (and when I say huge y mean really huge) MySQL
cluster. A cluster that is probably bigger than anything
you've seen before. Reply 5. Other Open Source Databases agingerich@... - 03/18/03
Why not include other open source databases like Firebird or PostgreSQL in your comparison. Both look like they're enterprise ready now.
Reply 5.1. Firebird? mattohare@... - 03/18/03
I evaluated PostgreSQL at one point. They were not ready for a windows platform, and I could not set up a Linux system at the time.

Is Firebird multi-platform? Reply 6. MySQL limited functionality m0ti - 03/18/03
MySQL suffers in terms of its power because it simply does not offer functionalities that even a moderate sized system are likely to use such as sub-queries (it's a pain to set up temp tables, fill -em up, join to 'em , and then tear them down).

Sure, it's fast, especially for very simple databases.

But MySQL doesn't come to the scale of SQL Server. It mops the floor with Access, but SQL Server's another story.

I would love to see a PostgreSQL vs. SQL Server comparison.
Reply 6.1. Good idea: Postgre v/s MS SQL privately_owed - 03/19/03
If anyone would be interested in writing an article comparing the virtues of Postgre with SQL Server, send me an e-mail: lamont.adams@cnet.com
Reply 6.2. MYSQL functionality and peformance JeriMorris - 03/24/03
mySQL doesn't enforce referential integrity constraints--or at least it didn't when I investigated it two months ago.

The mySQL documentation says that enforcing referential integrity in the database server isn't important, since the client software can do it. However, not all clients do it, so whether or not referential integrity enforcement (or any other feature) is important really depends on your own particular (present and future) needs.
Reply 6.3. Postgres vs MS SQL Industrial_Controller - 04/16/03
We did just such a comparison for a database migration project. PostgreSQL is a full SQL implementation and stacks up well against MS SQL in features. Cost made the critical difference in our choice. Postgres is free and that allows us to have aproduction database server as well as a testing/development server for only the cost of the hardware since we can run it on Linux, which is also free. Reply 7. MSDE option By_Jr - 03/20/03
in the article it states the way to get SQL, for developers but not comercial use, and for comercial use at retail price (not cheap), but the author forget about MSDE, wich is royalty-free to distribute with comercial applications, it does have restrictions (2GB database size with no restrictions on the number of DBs, 5 concurrent connections, and replication has some limitations, check out details at http://www.microsoft.com/sql/techinfo/development/2000/msde2000.asp )
With MSDE you can develop application for SQL and not being limited to sell them to mid/large bussines who can afford to buy SQL, and when they out grow MSDE, they can buy SQL standard. The benefits are for you and your clients. Reply 8. Forced to use SQL Server 2000 wayland@... - 03/20/03
Purchasing the software was a bit of an issue since you had o prove who the licenced users would be. As far as I can tell the 5 user licence issue is not implemented in software so I am not sure what happens when 10 users connect. The front end to this database is written in Access 97 for the supplied back end. There are a number of views implemented in SQL server which I believe are not available in MySQL. SQL Server looks like a much more capable RDMS but I feel MySQL would be fine for a stright MS Accss back end. I once used Oracle for simply storing MS Access tables, the result was terrible. A powerful Oracle server could not compete with a 486 based file server holding a 100MB data.mdb file. It's important to use the facilities of theserver if you expect a performance improvement over using an Access MDB file. Queries should be converted into views and stored procedures where possible. Anything not to drag the data over the network. I have read a review of PostgreSQL which looksa lot more comparable o SQL Server.
Reply 8.1. 5 connections means 5 concurrent connections as... D_Gilmour_1978 - 10/20/03
5 connections means 5 concurrent connections as far as i'm aware. I also believe that the requests are queued so it all just slows down.

MySQL does work ok as a backend to access however there are some very strange issues like having to have a timestamp in every table you want to be able to update. MSDE is a better option as it is intergrated very well (special project that uses it in MS Access 2000 and higher. I suspect the Jet engine will be a thing of the past very shortly
Reply 8.2. SQLitle felipe.alfaro@... - 05/17/08
SQLite is Microsoft's Access replacement. Calling MySQL an
Access replacement is just plain felony. Reply 9. Slanted Article wrohrbach@... - 03/20/03
The author purports to presenting an objective view of database choices and yet he presents only two choices. As far as I know there are many choices for a relational database other than MySQL and SQL Server. And to state that MySQL has greater performance when it does not even adhere to all of the properties of a true relational system is idiocy. Yeah, I'll use a database that cannot even support referential integrity and transactions for critical systems! The author seems to think referentialintegrity is just one of those nice-to-haves. I think I'll go with a database that at least offers the rudimentary features of a true relational database system. A better comparison would have been MySQL versus MS Access and MS Excel.
Reply 9.1. Scope Creep Bucky Kaufman (MCSD) - 03/21/03
I think I'm very objective, but with MS leanings. I compared MS SQL to MySQL because that's what the assignment called for. It's an important topic because for many development projects, those are the only choices you presented with.

About referential integrity - it's a wonderful feature, and when using InnoDB it is supported in MySQL, contrary to your statement. However, many projects are built and will continue to be built, in which data integrity is enforced in the application itself, rather than in the data store. Foreign Key support adds a level of overhead to the basic process of data storage and retrieval - which is the primary duty of a database server.

In Repy to, "A better comparison would have been MySQL versus MS Access and MS Excel." Those are data base clients. They bog down quite quickly when you try to use them as server apps.
Reply 9.1.1. The final nail in the coffin.. byrmol@... - 03/23/03
The second paragraph in your reply is absolutely disgraceful!

The only reason for a RDBMS is data integrity.

Do you honestly believe that the application code that "checks" that a row is valid for a given FK will be faster than what the DB engine could? You will issue 2 database statements (SELECT and then INSERT), while a RDBMS that has FK support issues just the INSERT.

I repeat.. absolutely disgraceful...
Reply 9.1.1.1. what the... stoecker@... - 03/23/03
some of you guys don't seem to have any clue about dbms and data storage and retrieval.

the best systems are _always_ using app logic to enforce integrity. if you're using the dbms it's simply extremly unprofessional and clearly tells you're never done big/critical jobs.
You can't just kill (for instance) a record because of a integrity rule! wohoooo!! hello! where did this come from? why was it saved/assigned? why the f* could this happen?

If you're just killing the $100k loan record this is great flub! extremly great flub!
Couln't it be, that some budget account was decreased by this value (even if you log it, even if you use thansactions, that won't help) - the questions is: WHY did this happen? What are the influences on _business level_ ?

Oh god - the're just killing the record... oh my...

//Marc
Reply 9.1.1.1.1. Opinions ESchlangen - 03/23/03
That's just your opinion. If you have a database that incorrectly deletes records due to referential integrity rules, you haven't set up your rules correctly. My experience with large database apps that try to control everything through the app coding is that they are slow and somewhere there is a bug in the code that either does what you've just described or leaves you with 500,000 orphaned records that do nothing except take up disk space. But that's just my opinion.
Reply 9.1.1.1.1.1. Not Just Space Taking mattohare@... - 03/24/03
Those records also tend to show up on reports, eventually. All you need is a select that does not link the records in an inner join to the parent table.
Reply 9.1.1.1.2. More garbage.... byrmol@... - 03/24/03
I can't believe I am hearing this from so called "professionals".

Your reply makes absoultely no-sense. You don't have any idea of what integrity means. You resort to some pathetically poor example with no context.

Obviously it is you who have never done any "big/critical" systems or you would know that these systems have many entry points of which some you don't control (ie. Many organisations intereacting with DBMS.. including competitors).

Your "best system" would last about 2 clock-cycles before irreversible damage was done.
Reply 9.1.1.1.3. Stick to the topic --peter - 03/24/03
I am sad to see such aggressive, yet uninformed opinions appear in what should a professional forum.

In my opinion, Troy stated it best on March 18 when he said: "It's hard to compare MySQL and SQL Server performance when they are so different in features."

This topic has gotten off on a big tangent. The point of the article was to determine which would be right for you. I do believe that most applications require the standard RDBMS features that MS SQL Server offers and MySQL does not,including transactions, foreign keys and referential integrity (henceforth RI) and stored procedure support. I have never designed, developed or consulted on a system that did not require most (if not all) of these standard features.

In other words, if you do not want any robustness to your data integrity, use MySQL. For web reporting, it is a great, inexpensive DBMS, but if you are developing a full-scale, enterprise level application, there is no comparison - you need to be using a real RDBMS such as MS SQL Server, Oracle, etc.... Yes these products cost money, but until a open-source DBMS supports these very necessary features, it cannot serve as the DB Server in mission critical applications.
Reply 9.1.1.1.3.1. TCO and ROI Bucky Kaufman (MCSD) - 05/18/03
In reply to:
I do believe that most applications require the standard RDBMS features that MS SQL Server offers and MySQL does not, including transactions, foreign keys and referential integrity (henceforth RI) and stored procedure support.
----- ----- ----- ----- -----

I have to agree - an app that doesn't use those optimizations is an app that probably doesn't do much anyway. But that $0 licensing fee on most MySQL installations shoots ROI through the roof, and TCO through the floor.I'm a Microsofty, but I LOVE building LAMP (Linux/Apache/MySQL/PHP) solutions, so Microsoft has been relegated to "big iron" status - useful mostly in large-scale solutions. With LAMP, a bare-bones PC and whatever development time I need, I can build a solid Intranet solution that the customer can just plug into their existing network and start using. Half of anything past the $500 for the PC that I can get out of the customer goes right back into my pocket (the other half goes to George Bush).
Reply 9.1.1.1.4. The truth... --peter - 03/24/03
As for things stated in this thread, I'd like to clear up a few issues starting with this one: Unlike MS Access, MSSQL does not, by default, do cascade deletions and updates (although I think stoecker believes this to be the case), nor would I recommend this without a lot of careful consideration.

Next, I think Bucky understated Data and Referential Integrity's (henceforth RI) importance. Data integrity should be done in the DB as much as possible because data integrity is part of the data.RI is one of the most important features of an RDBMS. Heck, the R in RDBMS stands for "Relational". Isn't RI helping maintain these relations?

Sure, if you want to provide a reporting database for the web, you could remove RI from that data store, although it doesn't gain you any performance since no data is being modified anyways. You also do not need transactional support for the web, so you could get a performance boost there.

Integrity belongs in the DB because rules that always apply to the data belong with the data. Not only is this better for performance, but safer for the data. What happens if your app developer forgets about the data integrity for a table or two. You have major problems that a good database design could have prevented. Business and app rules belong in the business layer and app, respectively. The app should never maintain data integrity.

I'm not saying that you cannot have a DB without data integrity. This surely can and will happen. I have seen databases that are collecting data that didn't require the data integrity. But most applications require data to be clean and relatable, and thus, the RDBMS should do this. To state otherwise is like stating that a book's Table of Contents and index donot have to match the actual contents of the book itself.
Reply 9.1.1.1.5. WHAT!?!? ccunningham@... - 03/26/03

You enforce referential integrity via constraints because there's no guarantee that all the code that accesses the data won't violate referential integrity rules.


Wow, I can't believe I read a statement like...well like your ENTIRE POST. I hope I never have to maintain an application that you've developed.

Most programmers I've stepped in after have had the same lackadaisical attitude toward database design and implementation, with crap results. Parent records without required children, child records without a parent, the list could go on, values outside of their domain... And the excuse was always "I'm doing it in code", but their code didn't handle all the situations that needed a constraint check, and then there's a big nasty mess of data that's found when someone finds crazy sh|t on a report, and then someone else has to go in there and figure out a way to clean everything up. It's pretty obvious you've never learned much about implementing and application using an RDBMS.
Reply 9.1.1.1.6. a note from a professional? julian@... - 04/11/03
i hate to be a flamer but what planet are you on?

a database that does not protect itself will corrupt. period.

ok, you're a single developer and you're handling all of the logic yourself in the middle tier, client code, whatever. so you're thinking you're safe?

are you perfect?

i certainly know that logic errors get into everybodys code. if you're not protecting at the rdbms you've suddenly killed you data store.

i have seen a lot of systems written by people with your opinion.they all suck. seriously. you should really get some experince with large production systems with medium to large teams that don't implement rdbms integrity checking. maybe that will help you formulate your opinions.
Reply 9.1.1.1.7. Case-by-Case Bucky Kaufman (MCSD) - 05/18/03
In Reply to:
"the best systems are _always_ using app logic to enforce integrity. if you're using the dbms it's simply extremly unprofessional and clearly tells you're never done big/critical jobs."
----- ----- ----- ----- -----

There's that word "always" again, that gets so many folks in trouble. Deciding where to put the business logic (app logic?) has many architectural condisderations - not the least of which is the resource availabilities of the end administrators. Then there's security - you might not want all of your logic in the same place as all of your data. Then there's the functionality - an Oracle web app might best be run on an sp-rich Oracle server, but a PHP app works best with MySQL - sans stored procedures.

... and that crack about the poster not having worked on large projects was off-topic, tacky and a pathetic attempt at bullying. Knock it off.
Reply 9.1.1.1.8. Where's the logic? dkehring - 01/05/04
You seem to have the terminology down but certainly not the concepts with regards to application levels. Any single application level is responsible for protecting itself based on its function. For example, the user interface layer is responsible for handling I/O interaction with the user. The business layer handles all logic about the "business" problem and the database layer handles data storage and data integrity issues. Your suggestion that the business layer is responsible for handling data integrity logic is just plain wrong. Yes, the business layer must perform some integrity checks _based on the relationships between business objects_ which are not necessarily the same as _relationships between database objects_. This is a classic problem with differences between object and relational mapping.

The database itself is responsible for protecting its data where referential integrity is an integral part. What happens if a completely separate application with different business rules wants to use the same database? Do we have to duplicate the database integrity logic in that application too so that application B does not corrupt data for application A?

Finally, the self-protection each layer must include is essential. Let's take a non-database approach. When one builds a business object, you add in rules to each property to check for valid values such as range or length, otherwise the data is rejected. Let's say a field can be only 10 characters long. Do you just put this kind of logic in the UI's text box control by setting the maximum length property of the text box? Of course not! The business layer must contain logic that protects the data it holds and maintains. What if the "user" of the business object is not a text box but another application? Adding such logic to the UI control just adds an extra level of protection but should certainly not be the definitive rule.
Reply 9.1.1.2. Never and Always Bucky Kaufman (MCSD) - 05/18/03
In reply to:
"The only reason for a RDBMS is data integrity."
----- ----- ----- ----- -----

Watch how you throw that word "only" around. using it like this makes you sound silly.
Reply 9.1.1.2.1. Yes ntcse@... - 09/30/03
Never use never.
Reply 9.1.2. MS-Access, Client or Server mattohare@... - 03/24/03
I wonder how many millions of dollars are wasted on the "less expensive" option of using Access/Jet instead of SQL Server. I know of a company here locally that had a wiz-kid Access programmer that took Access to a level way beyond design.

Nowthey spend thousands of dollars to maintain, not expand, their system.
Reply 9.1.2.1. Access on the Desktop Rules Bucky Kaufman (MCSD) - 05/18/03
In reply to:
I wonder how many millions of dollars are wasted on the "less expensive" option of using Access/Jet instead of SQL Server.
----- ----- ----- ----- -----

Access/Jet is fine for many things. The situation you mention is a problem of not having a long-term plan. Even the best software can't overcome that short-fall.

The most common situation I see Access used beneficially, and skillfully, in is clerical workers' desktop applications. They'll slap together an .mdb that works for them and use it for 10 years without changing hardly a thing.
Reply 9.1.3. I wish I had a dollar johnc@... - 09/23/03
"However, many projects are built and will continue to be built, in which data integrity is enforced in the application itself, rather than in the data store."

For every orphaned record I have fixed in a database with no Ref Integrity.. I would be a rich, rich man.. Reply 10. Tech support is also an important issue jmn - 03/21/03

The issue of technical support should also play a major factor in any major project. There can be little doubt that the community support provided by the Open Source community and the paid-for support provided by MySQL AB is equal to if not better than that which one can find for SQL Server by its vendors.

Furthermore, with access to the database source code a professional software developer can find out precise objective answers to questions rather than have to endure the spin-doctoring and evasion involved in the technical support provided by certain SQL Server vendors.

The cost of such support is a secondary issue, the quality of the support is far more important. This is the real strength of Open Source development tools.

Kind regards,

J.M. Nicholls
London
Reply 10.1. Open Sores Bucky Kaufman (MCSD) - 03/24/03
In re:
Furthermore, with access to the database source code a professional software developer can find out precise objective answers to questions rather than have to endure the spin-doctoring and evasion involved in the technical support provided by certain SQL Server vendors.
----- ----- ----- ----- ----- ----- ----- -----

Open Source is a double-edged sword. Although it fosters a community of experts, I find that Open Source developers are reluctant to certify or guarantee their own work with any teeth. They'll provide a tweak, but can't guarantee that it'll work with the 1000 or so other "tweaks" in the Open Source application. Also, if asked for documentation they'll often say it's "self-documenting".

These are problems that ANY software dev project can run into, but they are more pronounced in Open Source projects.
Reply 10.2. Amen felipe.alfaro@... - 05/17/08
Amen == ?Ol?! Reply 11. Pretty good article johnmeyer_1978@... - 03/24/03
Nice article about the relative merits of both. I have used both, and I can say that I go for mysql, mostly for cost and freedom reasons, but I do like some of the advanced tools of SQL Server. Reply 12. JOIN and get back to me jeffyjones@... - 03/24/03
Wow... look at all of the well documented research. [/sarcasm]

Throw in some complex joins, views and talk about stored procedures and triggers, then get back to me about which database has a performance advantage.
Reply 12.1. jeffy is right that there hasn't been a lot of ... kforslun@... - 04/02/03
jeffy is right that there hasn't been a lot of well documented testing and comparison.

I can refer you to an Infoworld article I've read:
http://www.infoworld.com/article/03/03/07/10osource_1.html

mysql's site has also linked to articles on performance comparisons.

In all the comparisons I've seen MySQL won over MSSQL.

That said, we need to clarify Jeffy's comment on seeing it tested with views, stored procedures and triggers. Quite simply, MySQL has none.

I've been developing with MySQL and PHP for 3 years. It works great. Everytime my stuff has been slow, it's been the way we did our code and how fast the CPU is. The database itself works great. Probably the biggest slow-down I've seen is that the longer the query string, the longer the query takes (I'm talking 3-4 screens full of auto-generated select statement here). The size of the data returned has almost no impact. It does not seem to take any longer to return 1 row, than a 1000. This of course is abstract, but with the tests we ran on our own system, it was always within 1 second. Reply 13. poor bad article, full of misconceptions glarriera@... - 09/20/03
I really found this article unaccurate and full of misconceptions about SQL Server. MySQL is no way comparable to SQL Server in performance, scalability and high availability. The paragraph talking about log transactions is full of errors. Note to the editor: please avoid this kind of poorly written articles.
Reply 13.1. Agree somewhat ntcse@... - 09/30/03
Looks like I'm not the only one that thought this article was slightly biased towards MySQL. I disagree with both the performance and the complexity assumptions.

SQL Server is AT LEAST comparable to MySQL as far as performance is concerned. I'm currently working with a 1GB database at work, and I have no complaint with SQL Server. I wouldn't worry too much even if MySQL was 10% or even 25% faster.

Funny, I picked up SQL Server pretty fast - I don't think it's overly complex. I mean you open Enterprise Manager, right click, New Table.... how simple do we need? I'd rather create tables and views using GUIs than taking the time to write the SQL out (which my friend needs to do with his MySQL databases on Unix servers). If you want to stick to pure ANSI SQL, go ahead and ignore the other advanced Transact SQL features.

I remember this author... he wrote another DB article a few months ago that spawned a whack of threads just like this one. See http://builder.com.com/5100-6388-1046266.html
Reply 13.1.1. Get a copy of MySQLFront as a MySQL GUI lauriek - 01/13/04
Suggest your friend downloads a copy of MySQLFront which is a nice windows front end which connects to any local or remote mysql database, its a lovely piece of work (and free!), like mysql itself...
Laurie
Reply 13.1.2. Performance Basis - Query or Update? Bucky Kaufman (MCSD) - 02/24/04
re:
SQL Server is AT LEAST comparable to MySQL as far as performance is concerned. I'm currently working with a 1GB database at work, and I have no complaint with SQL Server. I wouldn't worry too much even if MySQL was 10% or even 25% faster.
----- ----- ----- ----- -----

It's worth pointing out that MySQL performs much better on queries, but is LOUSY with updates. Furthermore, updating really large MySQL tables repeatedly over time is almost guaranteed to result in a corrupted table that needs to be rebuilt.

Whenever you say that "X" performs better than "Y" without specifying what performance metric is being used - you are just babbling.
Reply 13.1.3. 1GB? felipe.alfaro@... - 05/17/08
C'mon. I've used MySQL with 10TB+ databases and MySQL
does an amazing job. Not sure what the cost of running SQL
Server for such a database would be in comparison (only
licensing cost for all the 4-CPU machines is just huge). Reply 14. I'd like to prefer MySQL but I do prefer MS SQL o-mas@... - 04/09/04
Hi. I build muy applications with MS SQL beacuse it has more features, like sub-queries, triggers, transactions and stored procedures, and overall because it's easier to use. I know that the new version 4.1 of MySQL has sub-queries but the standard hosting don`t offer this version.

I think great part of the future of the software is in the open source so I will switch to any open database when it becomes possible to me. I'd like specially to know more about FireBird, wich is another open database

Setting Up Transactional Replication synchronization : A Step-by-step Guide

Setting Up Transactional Replication synchronization : A Step-by-step Guide
By Alexander Chigrik





General concepts
Checking necessary conditions
Checking connect
Example
Literature


General concepts
Replication is the process whereby data is copied between databases on the same server or different servers connected by LANs, WANs, or the Internet. Microsoft SQL Server replication uses a publish, distributor and subscribe metaphor.
Publisher is the server or database that sends its data to another server or database.

Subscriber is the server or database that receives data from another server or database.

Distributor is the server that manages the flow of data through the replication system. This server contains the distribution database.

Publisher contain publication/publications. Publication is a collection of one or more articles that is sent to a subscriber server or database. Article is the basic unit of replication and can be a table or a subset of a table.

Subscription is the group of data that a server or database will receive. This can be one or more publications. There are push and pull subscriptions. Push subscription is subscription when the publishing server will periodically push transactions out to the subscribing server or database. Pull subscription is subscription when the subscribing server will periodically connect to the distribution database and pull information.

Microsoft SQL Server support the following replication topologies:

Central publisher
Central subscriber
Central publisher with remote distributor
Publishing subscriber
Central publisher

This is one of the most common replication topologies. One server is configured as Publisher and Distributor and another server/servers is configured as Subscriber/Subscribers.







Central subscriber

This is a common topology in data warehousing. Many servers or databases replicate their data to a single central server or database.







Central publisher with remote distributor

In this topology distribution database resides on the another server than publisher. This topology uses for performance reasons when the level of replication activity increases or the server or network resources become constrained.
This topology requires separate Microsoft SQL Server installations, one for the Publisher and one for the Distributor.








Publishing subscriber

This is a dual role topology. In this topology, the publishing server or database also subscribes to another server or database. In this scenario two servers publish the same data. This is useful when a Publisher must send data to Subscribers over a slow or expensive communications link.









--------------------------------------------------------------------------------


Checking necessary conditions
Check the following before setting up transactional replication:

1. Remember that Microsoft SQL Desktop Edition supports only subscriptions to transaction replications. So you must use Microsoft SQL Standard, Enterprise or Small Business Server (SBS) editions to publish transaction replications. So for publish transaction replications you must have Windows NT Server, Windows NT Server Enterprise Edition or Windows NT Small Business Server (on Windows NT WorkStation and Windows 95/98 you may install only Microsoft SQL Desktop Edition version).
2. The Localsystem account has no access to shares on the network as it isn't an authenticated network account. So if you want to setting up replication you must change the account the MSSQLServer and SQLServerAgent services runs under to a account with the Windows NT administrator's rights.

3. Only members of the sysadmin server role can set up and configure replication, so if you have not this rights, you can not set up replication.

4. You must uncheck trunc. log on chkpt option, if you want to set up transactional replication.

5. Allocate adequate log space for each database that will be published in transactional replication.

6. Allocate adequate disk space for the distribution database.

7. Ensure the server that is being replicated to is defined as a remote server.



--------------------------------------------------------------------------------


Checking connect
1. Check TCP/IP with ping utility.
2. Check odbc connection with odbcping utility.

It's full syntax of odbcping utility: odbcping [-S Server | -D DSN] [-U Login Id] [-P Password]

If you receive this message, then you ms sql server start and work okay:

"
CONNECTED TO SQL SERVER

ODBC SQL Server Driver Version: 03.70.0623

SQL Server Version: Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
Nov 23 1998 21:08:09
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 4)
"
If you receive this message, then connection could not be established:
"
COULD NOT CONNECT TO SQL SERVER

SQLState: 08001 Native Error: 6
Info. Message: [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found.

SQLState: 01000 Native Error: 2
Info. Message: [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()).
"

FreeBSD PHP MS SQL FreeTDS Howto

When you install php5-extensions, make sure you select the "sybase_ct" option.

After insllation,

# cd /usr/local/etc
# cp freetds.conf.dist freetds.conf

# vi freetds.conf
[global]
# TDS protocol version
tds version = 8.0
[MyMSSQL]
host = 192.168.200.119
port = 1433
tds version = 8.0
client charset = UTF-8

test.php

    $adodb_dbdriver = "mssql";
    $adodb_server = "MyMSSQL";
    $adodb_user = "danny";
    $adodb_password = "123456";
    $adodb_database = "my_member";

    $conn = ADONewConnection($adodb_dbdriver); # eg 'mysql' or 'postgres'

    $conn->Connect($adodb_server, $adodb_user, $adodb_password, $adodb_database);

    $conn->debug = true;
    $conn->SetFetchMode(ADODB_FETCH_ASSOC);

    $strSQL = "SELECT TOP 10 ";
    $strSQL .= "id, title, dateadded ";
    $strSQL .= "FROM news ";
    $strSQL .= "ORDER BY id DESC ";

    $rs = $conn->Execute($strSQL);

    $rowCount = 0;

    if ($rs) {
      while($row = $rs->FetchRow()) {
        $title = $row['title'];
        //$title = iconv("", "UTF-8", $title); // only used when the field is varchar. No need for nvarchar field
      }
    }

How to make SELECT LIMIT (from, to) in MSSQL 2005

How to make SELECT LIMIT (from, to) in MSSQL 2005

Lots of developers complain, that Microsoft SQL hasn't something similar to LIMIT(from, to) as MySQL has. In past time there was a solution by SELECT TOP n and inserted SELECT TOP n- With new T-SQL commands in Miscrosoft SQL Server 2005 is situation simpler.

DECLARE @rowsperpage INT
DECLARE @start INT

SET @start = 10002
SET @rowsperpage = 5

SELECT * 
FROM (
 SELECT row_number() 
 OVER (ORDER BY id desc) AS rownum, *
 FROM   my_member
) AS A
WHERE A.rownum BETWEEN (@start) AND (@start + @rowsperpage - 1)
go

Another example:

DECLARE @rowsperpage INT
DECLARE @start INT

SET @start = 10002
SET @rowsperpage = 5

SELECT * 
FROM (
 SELECT ROW_NUMBER() 
 OVER (ORDER BY id DESC) AS Row, * 
 FROM my_member
) AS LogWithRowNumbers 
WHERE Row >= @start AND Row <=(@start + @rowsperpage - 1)  

Installing MSSQL for PHP

Installing MSSQL for PHP
From MoodleDocs
Jump to: navigation, search
Moodle1.7
Contents[hide]
1 Introduction
2 Installation overview
3 Using FreeTDS on Unix
4 Using FreeTDS on Windows
4.1 Troubleshooting
5 Using ODBTP on Unix or Windows
6 Using ODBC on Windows
7 Related links
if (window.showTocToggle) { var tocShowText = "show"; var tocHideText = "hide"; showTocToggle(); }

Introduction
This short manual is suitable if you are trying to run Moodle 1.7 (and upwards) using the SQL*Server (MSSQL) RDBMS. Steps detailed below must be performed before installing Moodle itself.
First of all, minimum required version of MSSQL has been stabilised to MSSQL 2005 (v.9), although it might work with MSSQL 2000 (v.8) or newer. All the development process has been performed using MSSQL 2005 and there could be some unknown problems with previous releases.
While PHP comes with one, more or less, standard extension (mssql) that provides access to MSSQL databases, early we found some hard limits on it. Basically such default extension has some limits that prevent us to use it at all (you can find more info about these problems here).
So, in order to allow PHP (i.e. Moodle) to access to MSSQL DBs properly we have to install a mssql extension alternative to save us from the problems related above. See the sections below for details about the various options.
Installation overview
1. Get MSSQL Server installed and running. (A free limited version, SQL Server Express Edition is available for testing.)
Make sure that you choose mixed authentication (Windows and local accounts) to keep things simpler later. You'll be asked to define the "sa" account password (it's the default System Administrator account which has full access to all databases by default).
2. Make sure MS SQL Server can accept incoming TCP/IP connections on port 1433 (the standard one).
You might need to explicitly allow this in your Windows firewall (see the Control Panel). You may also need to edit options in the :SQL Server Configuration Manager -> Network Configuration -> Protocols -> TCP/IP enabled
3. Open the "SQL Server Management Studio" and create a new empty database. If you are using the "sa" account then you don't need to do anything else here.
4. Configure these settings in your created (and still empty) database:
ANSI NULLS Enabled = true (ALTER DATABASE mdl_HEAD SET ANSI_NULLS ON GO)
Quoted Identifiers Enabled = true (ALTER DATABASE mdl_HEAD SET QUOTED_IDENTIFIER ON GO)
5. Get PHP installed with a web server. Unless you want to do it under IIS or some other way, the packages on the Moodle download page are a good solution.
6. Choose one of the following specific sections for your server to install the mssql extension alternative installed and running properly on your PHP box.
7. Set the following settings in your php.ini file
mssql.textlimit = 20971520
mssql.textsize = 20971520
Also, don't forget to set one of the following alternatives, in order to get all the data properly "slashed":
magic_quotes_gpc = Off or
magic_quotes_gpc = On and magic_quotes_sybase = On
8. With all this properly configured, you can continue with a standard Moodle installation.
Using FreeTDS on Unix
Important Note 1: Due to one bug in PHP it's highly recommendable to use PHP > 5.1.6 with FreeTDS (more info).
Important Note 2: Due to one bug in how FreeTDS handles nulls and empty values for some text types it's highly recommendable to use a recent version of FreeTDS (0.64 + official patches) (more info).
If you web server is on Linux or some other flavour of Unix, try FreeTDS, http://www.freetds.org/ (documentation at http://www.freetds.org/docs.html)
Note that the download link above is a source download, so you will need to install and compile it properly.
Once downloaded and uncompressed you must "configure, make, make install" it. This will deploy some stuff in the "/usr/local" directory of your machine, mainly:
/usr/local/etc: where the freetds conf files will reside.
/usr/local/lib: where compiled libraries will reside.
/usr/local/bin: where some executables will reside.
Then, you must configure FreeTDS to point to your MSSQL DB server. To do so, edit (or create) the /usr/local/etc/freetds.conf file and put in there exclusively these lines: [global]
host = xxx.xxx.xxx.xxx (ip of the MSSQL server)
port = 1433
client charset = UTF-8
tds version = 7.0
text size = 20971520
At this point, and previously to build the mssql extension alternative, you can test conectivity with your MSSQL DB using the "/usr/local/bin/tsql" executable. Just do this: tsql -S serverhost -U dbowner -P dbpassword
If everything is ok, you'll get this output: locale is "es_ES.UTF-8"
locale charset is "UTF-8"
1>
just type, for example: sp_help sysobjects
and you might get some output from DB. Finally type: exit
and you'll be out from the "tsql" command line interpreter.
Now that you've successfully built, configured and tested FreeTDS it is time to create the mssql extension alternative that will provide us with the capacity of handling MSSQL DBs from within Moodle. To do so, you'll need configure your PHP server adding this new option to the usual ones: --with-mssql=/usr/local/
then, after the standard "make and make install" steps, your PHP server will be built with MSSQL support provided by FreeTDS.
Finally, configure your Moodle config.php with this DB related info and continue with a normal Moodle install: $CFG->dbtype = 'mssql_n'; // Required
$CFG->dbhost = 'xxx.xxx.xxx.xxx'; // IP of the MSSQL server (also proper hostname is allowed)
$CFG->dbname = 'moodle'; // or whatever you called the database you created
$CFG->dbuser = 'yourusername'; // I usually use the 'sa' account (dbowner perms are enough)
$CFG->dbpass = 'yourpassword';
$CFG->dbpersist = false;
$CFG->prefix = 'mdl_'; //Prefix, you can change it, but never leave it blank.

Using FreeTDS on Windows
If your web server is on Windows, use php_dblib.dll from Frank Kromann (original details here). Despite the name, it's FreeTDS compiled for Windows. Many thanks to Frank for providing this DLL. Here's how to set it up:
1. Download the appropriate copy of php_dblib.dll from the list below, and save it into your /PHP/ext directory. (php_dblib.dll does not appear to be available for PHP 4.x at this time.)
php_dblib.dll for PHP 5.1.x
php_dblib.dll for PHP 5.2.x
php_dblib.dll for PHP 6.x
(here you can find some instructions to build those freetds extensions under win32 yourself)
2. FreeTDS requires the .NET Framework v1.1 to be installed. You can download it from the Microsoft website along with its service pack. Alternatively, if you do not wish to install this framework, you can download the required DLL from Frank's site, and save it into your /PHP root directory.
3. Edit your /PHP/php.ini file and add this line: extension=php_dblib.dll
Make sure that any lines referring to the php_mssql.dll extension are DISABLED (commented out).
4. Create a file called C:\freetds.conf with: [global]
host = xxx.xxx.xxx.xxx (ip of the MSSQL server)
port = 1433
client charset = UTF-8
tds version = 7.0
text size = 20971520
5. Your Moodle config.php should include lines like these: $CFG->dbtype = 'mssql_n'; // Required
$CFG->dbhost = 'localhost'; // assuming MS SQL is on the same server, otherwise use an IP
$CFG->dbname = 'moodle'; // or whatever you called the database you created
$CFG->dbuser = 'yourusername'; // I usually use the 'sa' account (dbowner perms are enough)
$CFG->dbpass = 'yourpassword';
$CFG->dbpersist = false;
$CFG->prefix = 'mdl_'; //Prefix, you can change it, but never leave it blank.
If you don't have a config.php file yet, it can be generated as normal from the Moodle installer.
6. Restart or start your web server. If Moodle still cannot communicate with the database server, please turn display_startup_errors to "On" in your /PHP/php.ini file, then restart the web server and check for any errors that may indicate incorrect DLL versions or missing dependencies. These error reports, turned off by default in PHP, can be vital in locating a problem with new extension installations.
7. Install Moodle as usual. Good luck!

Troubleshooting
If you encounter some problems you can try:
check that you have DotNet framework 1.1 installed (later version are installed on Vista, but you could need this specific one)
enable TCP/IP for MSSQL: SQL Server 2005 Network Configuration -> Protocols for MSSQLSERVER -> TCP/IP (Enable) -> Properties -> Ip Addresses -> 127.0.0.1 (Active+Enable)
if you are using SQL Server 2005 and you have the error 4004: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier, try the ODBTP method (next chapter). The SQL Server complaining that it doesn't support pure Unicode via TDS or older versions of ODBC. Microsoft has deprecated DB-Library a long ago, in favor of ODBC, OLE DB, or SQL Native Client. Many new features of SQL 2005 aren't accessible via DB-Library so if you need them, you could have to switch away from tools based on TDS and DB-Library :(
Using ODBTP on Unix or Windows
You can download ODBTP from http://odbtp.sourceforge.net/. Also you will access to the documentation from the same page.
The downloaded package includes both the source code and some binaries to be installed in the server and some ready-to-use mssql extension alternatives for some platforms/PHP versions (so you won't need to compile it if your PHP server/version binary package is present).
First of all, we have to install the Win32 service that comes with the package. Let's assume that it's going to run in the same Win32 machine where your MSSQL server is running (although it can run in any other Win32 server in your network).
To do do, following the instructions present in http://odbtp.sourceforge.net/install.html, you must:
Create a directory on the Windows host where the service program files will reside, i.e., md odbtp.
Copy the files odbtpctl.exe, odbtpsrv.exe and odbtpsrv.ini files from the winservice directory into the directory created in step 1.
Edit the file odbtpsrv.ini of the previous step and this line: MaxRequestSize=20971520
Open a command prompt (cmd) window on the Windows host.
Change to the directory to which the service program files were copied, i.e., cd odbtp.
Run the following commands to install and start the service:
odbtpctl install (NOTE: at the date this note is written -23/06/08- the ODBTP service can only be installed on Windows NT 4.0, 2000 or XP Pro. The service cannot be installed on Microsoft Windows Vista)
odbtpctl start
With these steps you should have one new service running in your host called "odbtp". Verify it's present and running in the "Services" control panel.
Don't forget to enable TCP/IP incoming connections to port 2799 in the host you have installed the service!
Now it's time to build the mssql extension alternative. First of all, verify if, in the downloaded package, under the "php" dir, there is one extension suitable for your PHP server/version. If it's present, you can simply copy it to the php/extensions dir in your PHP server and skip next points about compiling it from source. It's important to point that, inside each directory, you'll find two different libraries/dll files. The one that must be copied to the extensions dir is the one called "php_odbtp_mssql.xxx"!
If in the downloaded package isn't present the extension matching your PHP platform/version, you should build if from source files. To do that, just "configure, make, make install". That will create some stuff under "/usr/local".
Now that you've successfully built ODBTP is time to create the mssql extension alternative that will provide us with the capacity of handling MSSQL DBs from within Moodle. To do so, just configure your PHP server adding this new option to the usual ones: --with-odbtp-mssql
then, after the standard "make and make install" steps, your PHP server will be built with MSSQL support provided by ODBTP.
Finally, independently if we are using the binary extension provided in the download or if you have built it from source files, it's time to configure the extension. To do so, add this lines, if no present, to your php.ini file: extension=php_odbtp.dll
(only for Win32 PHP servers!)
And, for all the server platforms: [odbtp]
odbtp.interface_file = "/path/to/your/odbtp.conf"
odbtp.datetime_format = mdyhmsf
odbtp.detach_default_queries = yes
(where /path/to/your/odbtp.conf" is usually "/usr/local/etc/odbtp.conf"" for Unix systems and "C:\odbtp\odbtp.conf" for Windows systems)
Then, edit such "odbtp.conf" file and put there these contents: [global]
odbtp host = xxx.xxx.xxx (ip or hostname of the Win32 box running the ODBTP service)
type = mssql
unicode sql = yes
use row cache = yes
right trim text = yes
var data size = 20971520
With this, your PHP server will be able to connect with the MSSQL DB server using ODBTP. From here, just continue with the installation.
Finally, if you find the ODBTP executables and mssql extension alternative in binary formats, it only will be necessary to install them in your server (binary packages...) without the need to recompile anything (just the php.ini and odbtp.conf edition steps above will be necessary). Of course, it will be really welcome to have all those binary alternatives documented here.
Once ODBTP is working, Moodle config.php should include lines like these: $CFG->dbtype = 'mssql_n'; // Required
$CFG->dbhost = 'localhost'; // assuming MS SQL is on the same server, otherwise use an IP
$CFG->dbname = 'moodle'; // or whatever you called the database you created
$CFG->dbuser = 'yourusername'; // I usually use the 'sa' account (dbowner perms are enough)
$CFG->dbpass = 'yourpassword';
$CFG->dbpersist = false;
$CFG->prefix = 'mdl_'; //Prefix, you can change it, but never leave it blank.
If you don't have a config.php file yet, it can be generated as normal from the Moodle installer.
Using ODBC on Windows
This functionality is EXPERIMENTAL and must not be used on production environments!
1. Go to the Administrative Tools control panel, then the Data Sources (ODBC) panel.
2. Configure one new System/User DSN (call it, for example "moodle"). Dont forget to enable these options if the driver asks for them:
ANSI NULLS Enabled = true
Quoted Identifiers Enabled = true
3. Your Moodle config.php should include lines like these: $CFG->dbtype = 'odbc_mssql'; // Note this is different to all the other configs on this page!
$CFG->dbhost = 'moodle'; // Where this matches the Data source name you chose above
$CFG->dbname = ''; // Keep it blank!!
$CFG->dbuser = 'yourusername'; // I usually use the 'sa' account (dbowner perms are enough)
$CFG->dbpass = 'yourpassword';
$CFG->dbpersist = false;
$CFG->prefix = 'mdl_'; //Prefix, you can change it, but never leave it blank.
4. Install Moodle as usual. Good luck!
Related links
Installing Oracle for PHP
Retrieved from "http://docs.moodle.org/en/Installing_MSSQL_for_PHP"
Categories: Moodle 1.7 Experimental Features Installation Developer XMLDB

Linux/PHP with Windows/MSSQLServer Howto

Linux/PHP with Windows/MSSQLServer Howto

Made on Fedora Core 6. Suppose other distros work the same. Good luck!
- Install freetds, the unixODBC-kde gui and php-odbc, for making php work with odbc

[rodolfoap] /root # yum install freetds unixODBC-kde php-odbc

- Connectivity test, port 1433TCP. If you get "1>", ok. Elsewhere, its a firewall, ports, routing, etc. issue.

[rodolfoap] /root # tsql -S 192.168.1.20 -U sa -P sjasdad
locale is "en_US.UTF-8"
locale charset is "UTF-8"
1>

- Create an entry in /etc/freetds.conf:
[192.168.1.20]
host = 192.168.1.20
port = 1433
tds version = 8.0

- Add the entry in /etc/odbcinst.ini
[MSSQLServer]
Description = MSSQLServer
Driver = MSSQLServer
Servername = 192.168.1.20
Database = Bienes
UID = sa
PWD = sjasdad
Port = 1433

- And /etc/odbcinst.ini
[MSSQLServer]
Description =
Driver = /usr/lib/libtdsodbc.so.0
Driver64 =
Setup = /usr/lib/libtdsS.so.1
Setup64 =
UsageCount = 1
CPTimeout =
CPReuse =

- Now, you should get "SQL>"
[rodolfoap] /root # isql -v MSSQLServer sa sjasdad
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
(There you can perform SQL queries)

- The odbc php test page (for my database, of course):
[rodolfoap] /root # cat /var/www/html/odbc.php

<?
$connect = odbc_connect("MSSQLServer", "sa", "sjasdad");
odbc_exec($connect, "use Bienes");
$result = odbc_exec($connect, "SELECT * FROM Personas");
while(odbc_fetch_row($result)){
print(odbc_result($result, "CodPersona").' '.odbc_result($result, "Apellidos") . "<br>\n");
}
odbc_free_result($result);
odbc_close($connect);
?>

- If you want to use mssql functions, install php-mssql from http://remi.collet.free.fr/ , adding the repository and issueing "yum install php-mssq". And then,

[rodolfoap] /root # cat /var/www/html/mssql.php
<?
$connect = mssql_connect("192.168.1.20", "sa", "sjasdad");
mssql_select_db("Bienes", $connect);
$result = mssql_query("SELECT * FROM Personas");
$numRows = mssql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";
while($row = mssql_fetch_array($result))
echo $row["IdPersona"] . " - " . $row["Apellidos"];
mssql_free_result($result);
mssql_close($connect);
?>

To load / save data from SQL database to file

sqlcmd -Q "use danny_testing;select top 10 * from my_member" -o c:\output.txt

Error: The data types text and varchar are incompatible in the not equal to operator.

Error: The data types text and varchar are incompatible in the not equal to operator.

From books online, index entry Boolean expressions:

"Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types. The following table lists the Transact-SQL comparison operators."

= (Equals)
> (Greater Than)
< (Less Than)
>= (Greater Than or Equal To)
<= (Less Than or Equal To)
<> (Not Equal To)
!= (Not Equal To)
!< (Not Less Than)
!> (Not Greater Than)


If your description are always (or even usually) under 8000 characters, you need to change to a varchar type. Or you might want to implement two columns, one a smaller description, the other a text column that has long values.

for text column, change:

where field2 != ''

to:

where datalength(field2)

Insert utf-8 unicode data with PHP and MS SQL Server

The n- columns offer the best solution. I would give ADODB another
shot. COM is problematic in PHP4. Support might have improved in PHP5.
In theory, if you pass CP_UTF8 as codepage to COM(), you'd get UTF-8
text out of the resultsets. I remember that when I tried it though,
nothing happened. This was a couple years ago. Maybe newer versions of
ADODB would work better.

If you're using PHP on a Windows platform you can use the PHP COM
extension to communicate with SQL Server via ADO. The PHP COM extension
is capable of translating UTF-8 to UCS-2 and back if you specify so as
the third parameter:

  $oDb = new COM('ADODB.Connection', NULL, CP_UTF8);

This way you can use Unicode UTF-8 within PHP and Unicode UCS-2 within
SQL Server with all the translations done for you automatically.

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
</head>
<body>
<form action="p.php" method="POST">
 password<input type="text" name="password">

 note<input type="text" name="note">

 <input type="hidden" name="submist_info" value="yes">
 <input type="submit">
</form>
<?php
$myServer = "localhost";
$myUser = "";
$myPass = "";
$myDB = "";

//create an instance of the  ADO connection object
$conn = new COM ("ADODB.Connection", NULL, CP_UTF8)
  or die("Cannot start ADO");

//define connection string, specify database driver
$connStr = "PROVIDER=SQLOLEDB;SERVER=".$myServer.";UID=".$myUser.";PWD=".$myPass.";DATABASE=".$myDB;
  $conn->open($connStr); //Open the connection to the database

//declare the SQL statement that will query the database
$query = "SELECT TOP 1 * FROM my_member ORDER by ID desc";

//execute the SQL statement and return records
$rs = $conn->execute($query);

$num_columns = $rs->Fields->Count();
echo $num_columns . " Fields
"; 

for ($i=0; $i < $num_columns; $i++) {     $fld[$i] = $rs->Fields($i);
}

echo "";

while (!$rs->EOF)  //carry on looping through while there are records
{
    echo "";
    for ($i=0; $i < $num_columns; $i++) {         echo "";
    }
    echo "";
    $rs->MoveNext(); //move on to the next record
}


echo "
" . $fld[$i]->value . "
";

if( $_POST['submist_info'] == "yes" ){
 $password = $_POST['password'];
 $note = $_POST['note'];

 //$password = mb_convert_encoding($password,"UCS2","UTF-8");
 //$note = mb_convert_encoding($note,"UCS2","UTF-8");

  // Note: the N before the value data. N stands for National Character for inserting unicode utf-8 to SQL server
 $query = "INSERT INTO cwn_member ( name, password, note2 ) VALUES ( 'dan".$i."', N'".$password."', N'".$note."' ) ";
 $conn->execute($query);

}

//close the connection and recordset objects freeing up resources
$rs->Close();

$conn->Close();

$rs = null;
$conn = null;
?>

In MySQL,

on top of the script:

mb_internal_encoding("UTF-8");

before querying data:

SET NAMES 'utf8';
SET CHARACTER SET 'utf8';