MySQL FULLTEXT Indexing and Searching

MySQL FULLTEXT Indexing and Searching

MySQL has supported FULLTEXT indexes since version 3.23.23. VARCHAR and TEXT Columns that have been indexed with FULLTEXT can be used with special SQL statements that perform the full text search in MySQL.
To get started you need to define the FULLTEXT index on some columns. Like other indexes, FULLTEXT indexes can contain multiple columns. Here's how you might add a FULLTEXT index to some table columns:ALTER TABLE news ADD FULLTEXT(headline, story);
Once you have a FULLTEXT index, you can search it using MATCH and AGAINST statements. For example:SELECT headline, story FROM news
WHERE MATCH (headline,story) AGAINST ('Hurricane');
The result of this query is automatically sorted by relevancy.
MATCH
The MATCH function is used to specify the column names that identify your FULLTEXT collection. The column list inside the MATCH function must exactly match that of the FULLTEXT index definition, unless your search in boolean mode (see below).
AGAINST
The AGAINST function is where your full text search query goes. Besides the default natural language search mode, you can perform boolean mode searches, and use query expansion.
Boolean Mode SearchesSELECT headline, story FROM news
WHERE MATCH (headline,story)
AGAINST ('+Hurricane -Katrina' IN BOOLEAN MODE);
The above statement would match news stories about hurricanes but not those that mention hurricane katrina.
See the MySQL documentation on Boolean Mode searches for more info.
Query Expansion
The Blind Query Expansion (or automatic relevance feedback) feature can be used to expand the results of the search. This often includes much more noise, and makes for a very fuzzy search.
In most cases you would use this operation if the users query returned just a few results, you try it again WITH QUERY EXPANSION and it will add words that are commonly found with the words in the query.SELECT headline, story FROM news
WHERE MATCH (headline,story)
AGAINST ('Katrina' WITH QUERY EXPANSION);
The above query might return all news stories about hurricanes, not just ones containing Katrina.
A couple points about Full-Text searching in MySQL:
Searches are not case sensitive
Short words are ignored, the default minimum length is 4 characters. You can change the min and max word length with the variables ft_min_word_len and ft_max_word_len
Words called stopwords are ignored, you can specify your own stopwords, but default words include the, have, some - see default stopwords list.
You can disable stopwords by setting the variable ft_stopword_file to an empty string.
Full Text searching is only supported by the MyISAM storage engine.
If a word is present in more than 50% of the rows it will have a weight of zero. This has advantages on large datasets, but can make testing difficult on small ones.
Do you have any other good tips for fulltext searching and indexing in MySQL?

Sphinx - Free open-source SQL full-text search engine

Sphinx - Free open-source SQL full-text search engine


Features
high indexing speed (upto 10 MB/sec on modern CPUs)
high search speed (avg query is under 0.1 sec on 2-4 GB text collections)
high scalability (upto 100 GB of text, upto 100 M documents on a single CPU)
supports distributed searching (since v.0.9.6)
supports MySQL natively (MyISAM and InnoDB tables are both supported)
supports phrase searching
supports phrase proximity ranking, providing good relevance
supports English and Russian stemming
supports any number of document fields (weights can be changed on the fly)
supports document groups
supports stopwords
supports different search modes ("match all", "match phrase" and "match any" as of v.0.9.5)
generic XML interface which grealy simplifies custom integration
pure-PHP (ie. NO module compiling etc) searchd client API

Sphinx 0.9.7
source code: sphinx-0.9.7.tar.gz (344 KB)
Win32 release binaries: sphinx-0.9.7-win32-release.zip (408 KB)
Win32 debug binaries: sphinx-0.9.7-win32-debug.zip (920 KB)
Ruby API update 0.3.0: sphinxapi-ruby-0.3.0.zip (26K), official site


http://www.sphinxsearch.com/doc.html

Why MySQL could be slow with large tables ?

MySQL Performance Blog » Why MySQL could be slow with large tables ?



If you've been reading enough database related forums, mailing lists or blogs you probably heard complains about MySQL being unable to handle more than 1.000.000 (or select any other number) rows by some of the users. On other hand it is well known with customers like Google, Yahoo, LiveJournal,Technocarati MySQL has installations with many billions of rows and delivers great performance. What could be the reason ?
The reason is normally table design and understanding inner works of MySQL. If you design your data wisely considering what MySQL can do and what it can't you will get great perfomance if not, you might become upset and become one of thouse bloggers. Note - any database management system is different in some respect and what works well for Oracle,MS SQL, PostgreSQL may not work well for MySQL and other way around. Even storage engines have very important differences which can affect performance dramatically.
The three main issues you should be concerned if you're dealing with very large data sets are Buffers, Indexes and Joins.

Buffers

First thing you need to take into account is the fact - situation when data fits in memory and when it does not are very different. If you started from in-memory data size and expect gradual performance decrease as database size grows you may be surprised by serve drop in performance. This especially apples to index lookus and joins which we cover later. As everything usually slows down a lot once it does not fit in memory the good solution is to make sure your data fits in memory as good as possible. This could be done by data partitioning (ie old and rarely accessed data stored in different servers), multi-server partitioning to use combined memory and a lot of other technics which I should cover at some later time.
So you understand how much having data in memory changed things here is small example with numbers. If you have your data fully in memory you could perform over 300.000 of random lookups per second from single thread depending on system and table structure. Now if you data fully on disk (both data and index) you would need 2+ IOs to retrieve the row which means you get about 100 rows/sec. Note multiple drives do not really help a lot as we're speaking about single thread/query here. So difference is 3.000 times! It might be a bit too much as there are few completely uncached workloads but 100+ times difference is quite frequent.

Indexes

What everyone knows about indexes is the fact they are good to speed up accesses to database. Some people would also remember if indexes are helpful or not depends on index selectivity - how large proportion of rows matches to particular index value or range. What is often forgotten about is - depending if workload is cached or not different selectivity might show benefit from using indexes. In fact even MySQL optimizer currently does not take it into account. For In memory workload index accesses might be faster even if 50% of rows are accessed, while for disk IO bound accessess we might be better of doing full table scan even if only few percent or rows are accessed.
Lets do some computations again. Consider table which has 100 byte rows. With decent SCSI drive we can get 100MB/sec read speed which gives us about 1.000.000 rows per second for fully sequential access, jam packed rows - quite possible scenario for MyISAM tables. Now if we take the same hard drive for fully IO bound workload it will be able to provide just 100 row lookups by index pr second. The difference is 10.000 times for our worse case scenario. It might be not that bad in practice but again it is not hard to reach 100 times difference.
Here is little illustration I've created the table with over 30 millions of rows. "val" column in this table has 10000 distinct value, so range 1..100 selects about 1% of the table. The times for full table scan vs range scan by index:
PLAIN TEXT

SQL:
mysql> SELECT count(pad) FROM large;
+------------+
count(pad)
+------------+
31457280
+------------+
1 row IN SET (4 min 58.63 sec)

mysql> SELECT count(pad) FROM large WHERE val BETWEEN 1 AND 100;
+------------+
count(pad)
+------------+
314008
+------------+
1 row IN SET (29 min 53.01 sec)

Also remember - not all indexes are created equal. Some indexes may be placed in sorted way or pages placed in random places - this may affect index scan/range scan speed dramatically. The rows referenced by indexes also could be located sequentially or require radom IO if index ranges are scanned. There are also clustered keys in Innodb which combine index access with data access, saving you IO for completely disk bound workloads.
There are certain optimizations in works which would improve performance of index accesses/index scans. For example retrieving index values first and then accessing rows in sorted order can be a lot of help for big scans. This will reduce the gap but I doubt it will be closed.

Joins

Joins are used to compose the complex object which was previously normalized to several tables or perform complex queries finding relationships between objects. Normalized structure and a lot of joins is right way to design your database as textbooks teach you... but when dealing with large data sets it could be recepie to disaster. The problem is not the data size - normalized data normally becomes smaller, but dramatically increased number of index lookups which could be random accesses. This problem exists for all kinds of applications, however for OLTP applications with queries examining only few rows it is less of the problem. Data retrieval, search, DSS, business intelligence applications which need to analyze a lot of rows run aggregates etc is when this problem is the most dramatic.
Some joins are also better than others. For example if you have star join with dimention tables being small it would not slow things down too much. On other hand join of few large tables, which is completely disk bound can be very slow.
One of the reasons elevating this problem in MySQL is lack of advanced join methods at this point (the work is on a way) - MySQL can't do hash join or sort merge join - it only can do nested loops method which requires a lot of index lookups which may be random.
Here is good example. As we saw my 30mil rows (12GB) table was scanned in less than 5 minutes. Now if we would do eq join of the table to other 30mil rows table and it will be completely random. We'll need to perform 30 millions of random row reads, which gives us 300.000 seconds with 100 rows/sec rate. So we would go from 5 minutes to almost 4 days if we need to do the join. Some people assume join would be close to two full table scans (as 60mil of rows need to be read) - this is way wrong.
Do not take me as going against normalization or joins. It is great principle and should be used when possible. Just do not forget about performance implications designing the system and do not expect joins to be be free.
Finally I should mention one more MySQL limitation which requires you to be extra careful working with large data sets. In MySQL single query runs as single thread (with exeption of MySQL Cluster) and MySQL issues IO requests one by one for query execution, which means if single query execution time is your concern many hard drives and large number of CPUs will not help. Sometimes it is good idea to manually split query into several, run in parallel and aggregate result sets.
So if you're dealing with large data sets and complex queries here are few tips

Try to fit data set you're working with in memory - Processing in memory is so much faster and you have whole bunch of problems solved just doing so. Use multiple servers to host portions of data set. Store portion of data you're going to work with in temporary table etc.

Prefer full table scans to index accesses - For large data sets full table scans are often faster than range scans and other types of index lookups. Even if you look at 1% or rows or less full table scan may be faster.
Avoid joins to large tables Joining of large data sets using nested loops is very expensive. Try to avoid it. Joins to smaller tables is OK but you might want to preload them to memory before join so there is no random IO needed to populate the caches.
With proper application architecture and table design you can build applications operating with very large data sets based on MySQL.

System performance benchmark

System performance benchmark

SysBench - benchmark tool
from MySQL Performance Blog by Vadim
Sysbench is benchmark developed by Alexey Kopytov (software engineer @ MySQL AB)- http://sysbench.sourceforge.net/ and I want to write a short intro about this tool as sysbench is one of software for my everyday use. For example, SUN published their Solaris vs RedHat stuff based on sysbench's results (Peter and me provided performance consutling for this publishing).Sysbench has a lot of options and details so my goal is describe common usage of benchmark.Sysbench allows to test:
file I/O performance
scheduler performance
memory allocation and transfer speed
POSIX threads implementation performance
database server performance
First four is useful for the platform evalution, for example if you want to compare speed of file I/O and implementation of threads on different servers - I will write about in further notes. Regarding database benchmarks - in 0.4.7 and earlier versions Sysbench supported only predefined set of queries, but that will change in version 0.5 where sysbench is scriptable (Lua http://www.lua.org/ as scripting language) , and you can use your own tables and queries. 0.5 should be realesed soon. Originally Sysbench supported only MySQL, but later Oracle and PostgreSQL (and deviations, e.g EnterpriseDB) were added - it is not clear from Docs page, but it is . Sysbench uses only a native API, not middle layers like ODBC, that is why list of supported DB is not wide. Other restriction Sysbench can't be complided on Windows - well you can try and write about your experience. In next notes I'll write how to setup and perform database benchmarks.

MySQL - Import files into database with LOAD_FILE()

LOAD_FILE(file_name)
Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full pathname to the file, and you must have the FILE privilege.

Example1:

UPDATE t
SET blob_col=LOAD_FILE('/tmp/picture')
WHERE id=1;

Example2:

use rgr;
create table netleih1 (fn varchar(255) not null primary key,data text);
insert into netleih1(fn,data) select 'title_34504.html', LOAD_FILE('Z:/Downloads/netleih-detail/title_34504.html');
select * from netleih1;

base64.sql - MySQL base64 encoding/decoding functions

base64.sql - MySQL base64 encoding/decoding functions
http://firestuff.org/wordpress/wp-content/uploads/2006/03/base64.sql

FUNCTION BASE64_DECODE (input BLOB) RETURNS BLOB
FUNCTION BASE64_ENCODE (input BLOB) RETURNS BLOB

-- base64.sql - MySQL base64 encoding/decoding functions
-- Copyright (C) 2006 Ian Gulliver
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of version 2 of the GNU General Public License as
-- published by the Free Software Foundation.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
delimiter
DROP TABLE IF EXISTS base64_data
CREATE TABLE base64_data (c CHAR(1) BINARY, val TINYINT)
INSERT INTO base64_data VALUES
('A',0), ('B',1), ('C',2), ('D',3), ('E',4), ('F',5), ('G',6), ('H',7), ('I',8), ('J',9),
('K',10), ('L',11), ('M',12), ('N',13), ('O',14), ('P',15), ('Q',16), ('R',17), ('S',18), ('T',19),
('U',20), ('V',21), ('W',22), ('X',23), ('Y',24), ('Z',25), ('a',26), ('b',27), ('c',28), ('d',29),
('e',30), ('f',31), ('g',32), ('h',33), ('i',34), ('j',35), ('k',36), ('l',37), ('m',38), ('n',39),
('o',40), ('p',41), ('q',42), ('r',43), ('s',44), ('t',45), ('u',46), ('v',47), ('w',48), ('x',49),
('y',50), ('z',51), ('0',52), ('1',53), ('2',54), ('3',55), ('4',56), ('5',57), ('6',58), ('7',59),
('8',60), ('9',61), ('+',62), ('/',63), ('=',0)
DROP FUNCTION IF EXISTS BASE64_DECODE
CREATE FUNCTION BASE64_DECODE (input BLOB)
RETURNS BLOB
CONTAINS SQL
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
DECLARE ret BLOB DEFAULT '';
DECLARE done TINYINT DEFAULT 0;
IF input IS NULL THEN
RETURN NULL;
END IF;
each_block:
WHILE NOT done DO BEGIN
DECLARE accum_value BIGINT UNSIGNED DEFAULT 0;
DECLARE in_count TINYINT DEFAULT 0;
DECLARE out_count TINYINT DEFAULT 3;
each_input_char:
WHILE in_count < first_char =" SUBSTRING(input,1,1);" input =" SUBSTRING(input,2);" c =" first_char;" error =" 1;" accum_value =" (accum_value" in_count =" in_count" first_char =" '=" done =" 1;" out_count =" out_count"> 0 DO BEGIN
SET ret = CONCAT(ret,CHAR((accum_value & 0xff0000) >> 16));
SET out_count = out_count - 1;
SET accum_value = (accum_value <<>CREATE FUNCTION BASE64_ENCODE (input BLOB)
RETURNS BLOB
CONTAINS SQL
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
DECLARE ret BLOB DEFAULT '';
DECLARE done TINYINT DEFAULT 0;
IF input IS NULL THEN
RETURN NULL;
END IF;
each_block:
WHILE NOT done DO BEGIN
DECLARE accum_value BIGINT UNSIGNED DEFAULT 0;
DECLARE in_count TINYINT DEFAULT 0;
DECLARE out_count TINYINT;
each_input_char:
WHILE in_count < done =" 1;" accum_value =" accum_value" first_char =" SUBSTRING(input,1,1);" input =" SUBSTRING(input,2);" accum_value =" (accum_value" in_count =" in_count" in_count =" 3" out_count =" 4;" in_count =" 2" out_count =" 3;" in_count =" 1" out_count =" 2;"> 0 DO BEGIN
BEGIN
DECLARE out_char CHAR(1);
DECLARE base64_getval CURSOR FOR SELECT c FROM base64_data WHERE val = (accum_value >> 18);
OPEN base64_getval;
FETCH base64_getval INTO out_char;
CLOSE base64_getval;
SET ret = CONCAT(ret,out_char);
SET out_count = out_count - 1;
SET accum_value = accum_value << in_count =" 2" ret =" CONCAT(ret,'=" in_count =" 1" ret =" CONCAT(ret,'=">

mssqlserver.com: SQL Server FAQ

mssqlserver.com: SQL Server FAQ


Access
I'm getting #deleted entries in resultsets from ODBC since installing SQL 7. What is going on?
How can I migrate from Access to SQL Server?
Is there an Access upsizing wizard to SQL Server 7.0?
Administration
How can I change the owner of an object? (all versions)
How can I add a language to SQL Server so that it knows about my localised date format? (all versions)
How do I audit what has been updated in a SQL Server? (all versions)
Should I amend the SQL Server "Backup Buffer Size"? (all versions)
Should I set "boost priority" to 1 or "SMP" to -1 on SQL Server? (all versions)
How can I change the default location for database files that SQL creates? (Version 7.0)
How do I change the name of SQL Server? (all versions)
Why do my SQL Server identity values get out of synch causing gaps/duplicates etc.? (Version 6.5 and below)
How can I clear SQL Server's procedure cache? (all versions)
How can I compare that the contents of two tables are identical? (all versions)
How can I see how many objects are actually open in SQL Server? (all versions)
Why does the SQL Server Database Maintenance Wizard warn about using it on databases greater than 400Mb in size? (Version 6.5)
What can I do about handling blocking and deadlocks in SQL Server? (all versions)
Can I do an NT defrag on a SQL Server .DAT device/file? Will it cause any problems or do any good? (all versions)
My SQL Server errorlog is filling up with entries. How can I switch or truncate it? (all versions)
My SQL Server errorlog has switched several times and I've lost the evidence of a crash problem. How can I increase the number of errorlogs to prevent this? (all versions)
Why does my transaction-log fill up when I use fast-bcp, select into or the transfer tool?. I thought this didn't log anything. (all versions)
When would I use dbcc gaminit in SQL Server? (Version 6.5 and below)
How can I insert the output of a dbcc command into a SQL Server table? (all versions)
I am missing the whole of MSDB, or just some tables - how do I create them? (all versions)
How can I move a SQL Server device from one disk to another, or rename it? (all versions)
How can I programmatically get the next free device number (vdevno) in SQL 6.5 and below? (Version 6.5 and below)
Why do I see page faults/sec above 0 when I have a dedicated SQL Server machine? (all versions)
What are the *.DMP files that appear in the SQL Server log directory. I've tried printing them with PRINTDMP.EXE but it doesn't work. (all versions)
Why can't I use RCMD.EXE via xp_cmdshell from SQL Server? (all versions)
I have a corrupt log file - can I rebuild it? (Version 6.5)
How can I clear a table and reset the identity back to 0? (all versions)
Why do I have problems revoking permissions on some tables with SQL Server? The command works ok, but the users still have permissions (as shown by sp_helprotect) (all versions)
I've forgotten the sa password for SQL Server - what can I do? (all versions)
I have a query that seems to lock other users out of the system. Especially with tempdb. What is going on? (Version 6.5)
Why can't I shrink my database any smaller than Mb? (all versions)
How do I change the sort-order or character set for a SQL Server database? (all versions)
Why am I being blocked by spid -1? I am not able to kill it and have to restart SQL Server to get around it. (Version 6.5 SP4 and SP5a)
How can I see what line a stored-procedure is on? (Version 6.5)
I have a corrupt SQL Server table that I can't drop. What can I do? (all versions)
Why has my tempdb in SQL Server filled up? (Version 6.5)
I've put tempdb in ram and now I can't restart my SQL Server. (Version 6.5)
Should I use tempdb in ram? (all versions)
How do I remove the tempdb database from master/default devices? (all versions)
I've accidentally set my SQL Server tempdb to single-user mode. I cannot connect to put it back in multi-user mode. What can I do? (all versions)
What does -y mean as a SQL Server start-up option? (all versions)
Backup and Restore
I can't use a tape-drive with SQL Server, but it works ok with NT Backup. Why? (all versions)
Why can't I backup/restore my SQL Server database to a share on another server? (all versions)
How can I programmatically kick off a SQL dump and get the stats back on how far through it is? (Version 7.0)
How can I overwrite expired backups on a disk device to save on space and still keep a rolling days there? (all versions)
If I lose my data file (MDF) in SQL 7 I can't backup the log to recover to a point in time - why not? (all versions)
Why can't I backup/restore my SQL Server database to JAZ/ZIP drive from SQL EM? (all versions)
How can I recover a SQL Server database when all I have left is the original .DAT devices or files? (all versions)
Why am I getting an error "The RAID set for database is missing member number . Backup or restore operation terminating abnormally"? (all versions)
Can I do a SQL backup to a tape drive on another server? (all versions)
Why is a SQL Server restore (LOAD DATABASE) so much slower than a dump database? (all versions)
How can I restore a single table in SQL 7? (Version 7.0)
Is it safe to clear down the msdb..sysbackuphistory table in SQL Server as it is filling up the database? (all versions)
Does SQL Server support tape-loaders? (all versions)
Clustering
Connectivity
What is a net-lib? (all versions)
How do I connect to SQL Server from a non-microsoft machine? E.g. Unix, Macintosh, OS/2 etc. (all versions)
How can I restrict access to my SQL Server so that it only allows certain programs to connect? (all versions)
How do I configure multi-protocol net-lib to force encryption of packets in SQL Server? (all versions)
Why can I connect to SQL Server with the tcp-ip sockets netlib and not named-pipes over IP/multiprotocol? (all versions)
I'm having problems getting more than connections from a 16-bit client to SQL Server. (all versions)
I'm getting an "access denied", "CreateFile" or 1326 message when connecting to SQL Server. (all versions)
How can I restrict access to my SQL Server so that it only allows certain machines to connect? (all versions)
How does SQL Server clear up orphaned connections? (all versions)
How can I connect from Delphi to SQL 7? (all versions)
I've just changed NT domain for my SQL Server/clients - why can't I connect to the server any more? (all versions)
How can I access Exchange stores from SQL Server? (all versions)
Which SQL net-lib is the fastest? (all versions)
How do I connect to SQL Server through a firewall? (all versions)
How can I connect to SQL Server from a Macintosh? (all versions)
How can I configure SQL Server to listen on another net-lib? How can I tell what net-libs are being used? (all versions)
How do I connect to SQL Server from PERL? (all versions)
How do I configure a connection to use tcp-ip sockets with SQL Server? (all versions)
Does SQL 7 support db-lib? What about 16-bit clients - DOS etc.? (Version 7.0)
Why do I get an error "CANNOT GENERATE SSPI CONTEXT"? (Version 7.0)
Why does the tcp-ip net-lib not appear as an option in SQL Setup? (Version 7.0)
Development
How do I...
How do I get ADSI to work with SQL Server? (Version 7.0)
How can I do a crosstab function using standard TSQL in SQL Server? (all versions)
How can I issue a SQL command that uses a variable for the tablename, columns etc. How can I return values from some dynamic SQL? (all versions)
How can I tell if my code is ANSI-92 compliant? (Version 7.0)
How can I pass an array of values to a SQL Server stored-procedure? (all versions)
How can I calculate someone's age in SQL Server? (all versions)
How can I add/amend/delete columns in SQL Server? E.g. Int to Char, char(5) to char(15), change NULL to NOT NULL etc. (all versions)
How can I do a case-sensitive comparison on a SQL Server installed with a case-insensitive sort-order? (all versions)
How can I check with SQL whether a table is already there or not? (all versions)
How can I tell if a column for a table already exists? (all versions)
How can I delete duplicate rows from a SQL Server table? (all versions)
How do I drop the index related to a primary key? DROP INDEX doesn't work. (all versions)
How can I find duplicate rows in SQL Server? (all versions)
How do I store/retrieve text and image data in SQL Server? (all versions)
How can I turn an IP address held as a string to the four separate integers? (all versions)
How can I ORDER BY different fields based on a variable in SQL Server? (all versions)
How can I pad an integer value in SQL Server with zeroes? (all versions)
How can I convert a string to "proper" case in SQL Server? i.e. Turn "andrew" into "Andrew" (all versions)
How do I configure the client query time-out for SQL Server? (all versions)
How can I embed single quotes in a string and get SQL Server to store it correctly? (all versions)
How can I get random numbers from SQL Server? Using rand() I always get the same sequence. (all versions)
How can I select random rows from a SQL Server table? (all versions)
How can I replace/change/delete characters in a SQL Server column? (all versions)
How can I restrict the number of rows returned to my query in SQL Server? (all versions)
How can I get a count for all tables in a database? (all versions)
How can I suppress the column headings that always come out in ISQL output on SQL Server? (all versions)
How do I view the contents of a TIMESTAMP field in SQL Server? (all versions)
General
Are there any examples of heterogeneous data queries from SQL 7 to other sources? (Version 7.0)
Why does my SQL Server code sometimes respond slower than normal when I haven't changed anything? (all versions)
What is the limit on the number of tables in a query in SQL Server? (Version 6.5 and up)
In SQL 6.5 why does passing a 256 byte character string to an SP expecting a TEXT field produce a NULL when checked? (Version 6.5 and up)
Where are the cascade update/delete functions in SQL Server? (all versions)
Should I use char or varchar? What are the advantages? (all versions)
Why do I get NULL when adding/concatenating a NULL value with SQL 7? I didn't with 6.5. (Version 7.0)
Why can't I drop a SQL Server table? (all versions)
Where can I get Embedded SQL for SQL Server? (all versions)
Why do I get a "variable is not declared" inside my EXEC statement? (all versions)
What is the equivalent of the IIF command in SQL Server? (all versions)
Why does a SELECT ... WHERE col=NULL not work in SQL Server? (all versions)
Does SQL Server have an "ON ERROR RESUME" type function in TSQL to match the VB facility? (all versions)
Can I use an ORDER BY in a view in SQL Server? (all versions)
What is the difference between "old" style SQL Server *= joins and the new ANSI LEFT/RIGHT outer joins? (all versions)
When I change a table definition it often invalidates any views on that table. How can I fix this without re-creating the view? (all versions)
What is the difference between a CHECK constraint and a RULE? (all versions)
I'm getting an error message - "Cannot Load DLL 'SDI' Reason 126 ( The specified Module Cannot be found)" when doing a stored-procedure debug on SQL Server. Or breakpoints just don't fire. (Version 6.5 and above)
What does sp_sdidebug do? (Version 6.5 and above)
What's happened to SQL_PRESERVE_CURSORS in SQL 7.0? (Version 7.0)
Why do I get "SQL Server has run out of locks" error message with SQL 7 - I thought it dynamically allocated them? (Version 7.0)
Why is SQL Server 7 slower than 6.5 at running some of my sp's? (Version 7.0)
I'm running a stored-proc from VB. Why does it return control before the SP has finished? (all versions)
When might I want to use a surrogate key? (all versions)
Why is the week number returned by SQL Server wrong? (all versions)
Why can't I do an Xcopy via xp_cmdshell or a scheduled job? (all versions)
DTS
How can I run a DTS package from within SQL Server - e.g. a stored-procedure? (Version 7.0)
Why do I get an error - 'DTS Wizard Error - CoCreateInstance Class not Registered'? (Version 7.0)
I'm getting the following error with SQL 7 DTS - "The license for the installation of MS SQL Server on your source and destinations connections does not permit the use of DTS to transform data. Refer to your license for more information." (Version 7.0)
What issues are there with SQL 7's DTS function and Oracle? (Version 7.0)
I can run some SQL code or a DTS package myself fine, but when I run it using the SQL Scheduler it doesn't work - why not? (Version 7.0)
How can I transfer DTS packages from one SQL 7 server to another? (Version 7.0)
English Query
Enterprise Manager
Are there any "easter eggs" in SQL Server? (Version 6.5)
Why do my device sizes appear as negative values in SQL EM? (Version 6.5)
Why can't I connect Enterprise Manager to my local copy of SQL Server? It connects ok to other machines. (all versions)
Why do I get an error message when I try to run SQL 7 EM - "MMC - Snap-in failed to initialize"? (Version 7.0)
How can I script my database schema? (all versions)
What happened to the gui for column level permissions in SQL 7? (Version 7.0)
Why do I get a 6.5 group in my SQL 7.0 MMC list of servers? How can I remove it? (Version 7.0)
Why do I get weird messages using SQL 7's MMC on Windows 2000 RC1? (Version 7.0)
I can't run SQL Server Enterprise Manager. I'm getting an error SQLOLE OLE object could not be registered. Class not registered (80040154). (Version 6.5)
What can't I do with SQL Enterprise Manager under Win9x? (Version 6.5)
Why am I getting gpf's/hangs running SQL tools under Windows 9x - I don't get the same problems with Windows NT clients? (all versions)
What does the red zig-zag line next to a server name in SQL Enterprise Manager mean? (all versions)
Full Text Indexing
Why do I get the error "An unknown full-text failure (80004005) occured in function EnumCatalogs on full-text catalog" when I try and enable text indexing? (Version 7.0)
General
How to
How can I decrypt a SQL Server stored-procedure? (Version 6.5)
How do I encrypt fields in SQL Server? What about whole objects or the whole database? (all versions)
I am having problems with SQL Server running bcp from xp_cmdshell - why does it not run or see the files I want it to? (all versions)
Why can't I get at a network file when I run a program with xp_cmdshell from SQL Server? (Version 6.5 and up)
How can I speed up SQL Server applications running over slow links. (Version 6.5 and up)
How can I tell if a stored procedure was created with the ANSI NULLS setting on? (Version 7.0)
How can I automate the scripting/transfer of a database/objects in SQL Server? (all versions)
How do I configure the debugger in Visual Studio to work with SQL 7? (Version 7.0)
How can I get a list of all files in a directory into SQL Server? (Version 7.0)
How can I get file information using SQL Server? (all versions)
How can I run an external (non-SQL) program/dll etc. from within a TSQL SQL Server script? What sort of function can I perform? (all versions)
How can I read/write to a flat file from inside a SQL Server TSQL script/stored-procedure/trigger? (all versions)
How can I clear SQL Server's procedure cache? (all versions)
How can I get the current time in GMT rather than local time? (all versions)
How can I get the hardware spec of a SQL Server remotely? (all versions)
How can I tell what columns in my SQL Server database are identity columns? How can I tell the seed and increment? (all versions)
How can I get ISQL.EXE to return a DOS errorlevel for me to test? (all versions)
How can I re-install SQL Server without losing any data under a new version of NT I have installed? (all versions)
How do I do row-level locking on SQL Server? (all versions)
How many bytes can I fit on a row/page in SQL Server and why? Are there any workarounds? (all versions)
How can I tell if a service is running from SQL? (all versions)
How can I check whether SQL 6.5 or SQl 7.0 is installed without connecting to SQL and logging on? (all versions)
How can I stop and start SQL Server from the command- line? I want to back it up with NT Backup. (all versions)
How can I create a user-defined function in SQL Server? (all versions)

What are all the DBCC commands for SQL Server? (all versions)
What is the precedence of the SET commands, database options, session options etc. in SQL Server? (all versions)
Can I create SQL Server databases on network drives? (all versions)
Is MS SQL Server Y2K compliant? What known issues are there? (all versions)
What are the minimum hardware/software requirements for SQL Server? (all versions)
What hardware specification do I need to run SQL Server for good performance? (all versions)
Do I really need a 166Mhz Pentium processor to run SQL Server 7.0? (Version 7.0)
Can I compress a SQL Server .DAT device/file? Will it cause any problems? Can I compress data inside a table? (all versions)
What is the MS Loopback adapter and why do I need it for SQL Server? (all versions)
Why does only one cpu get used with my SQL 6.5 system for an extremely complex/long running query? (all versions)
Why do I get arithmetic overflows and 'string truncated' errors with SQL 7? I didn't with 6.5. (Version 7.0)
When I use some of the SQL Server GUI tools I get the "dial-up" remote network prompt. How can I disable this? (all versions)
Are triggers fired or constraints checked with BCP on SQL Server? (all versions)
Where does the list of SQL Server's come from in registration/dialog boxes? What reasons are there for not getting a list? (all versions)
What registry entries does SQL Server's client use? (all versions)
Can I add comments to SQL Server's schema like I can with Access? (all versions)
Is there a DATE function for SQL Server? I don't want to hold the time. (All versions)
What is the dllhost.exe process on my machine that seems to use a lot of CPU? (all versions)
Why can't I use a GO in a stored-procedure? (all versions)
Why don't some of my drives appear in SQL Server? (all versions)
What happens when a SQL Server identity field reaches it's maximum value? (all versions)
What is the equivalent of the "INSTR" command in SQL Server? (all versions)
What does MS mean by Internal vs External fragmentation in SQL Server? (all versions)
Where is the pointer to master stored in SQL Server? How can I move the master database? (all versions)
What is the maximum number of rows in a SQL table? What other size limitations are there? (all versions)
Are SQL Server userid's and passwords passed in clear on the network? (all versions)
Where does SQL server store the permissions on tables? (all versions)
What registry entries does SQL Server use? (all versions)
Where can I get more info on the spt_values table? (all versions)
Why am I seeing indices in sysindexes that I haven't created with SQL 7? (Version 7.0)
What is sqlimage.dll? (Version 7.0)
Why can't I stop/start SQL from SQL Enterprise Manager? (all versions)
Why can't I access more than 4K of data in a TEXT field in SQL Server? (all versions)
Why does the new "TOP" command in SQL 7 not work? I get a syntax error. Also ALTER TABLE and other SQL 7 only commands. (Version 7.0)
What does dbcc traceon(208) mean in SQL Server? I see it in SQL's errorlogs, but I'm not issuing it. (Version 6.5)
Why do I get invalid rows returned containing hyphens when my where clause doesn't specify them? (Version 7.0)
Why am I seeing entries in sysindexes (or sp_helpindex) for indexes I have not created? (all versions)
What are the changes/differences between vX and vY of SQL Server? (all versions)
Why could the SQL version switch utility not work? (Version 7.0)
Information
What are good SQL Server books and other sources of reference?
How can I report a SQL Server bug to Microsoft?
What is a reproduction (repro) script that I need to produce for a SQL Server bug?
Why is SQL Server better/worse than Oracle? (all versions)
How much longer will MS support SQL 6.5 for? (all versions)
Where is the SQL Server FAQ? Or other useful SQL information.
What is a "SQL Server MVP" and how do I become one?
As a user of Digital/Compaq Alpha technology how does MS dropping support affect SQL Server? (all versions)
Where can I get the ANSI92 SQL information from.
How can I get on a beta program for new versions of SQL/OLAP/MSEQ etc?
What conferences covering SQL Server are coming up?
Can I run SQL Server on the same machine as Oracle, Exchange etc.? Can it be a PDC/BDC? (all versions)
Where can I download SQL Server utilities, service packs etc.?
With SQL Server should I get more processors or faster processors? (all versions)
Any known issues with IE5 and SQL Server 7.0? (all versions)
How do I know which version of MDAC I am running?
Who are Microsoft Product Support Services and how do I contact them?
SQL Server only runs under NT. What cheap/free database runs under Linux?
Does SQL Server support row numbers like Oracle does? (all versions)
Now that SQL 7.0 is released can I still buy SQL 6.5?
Where can I get a diagram of the SQL 7 system tables? (Version 7.0)
Why is SQL Server slower than Access/FoxPro/DBase etc. (all versions)
Where can I get details on the layout of the TDS protocol that SQL Server uses to talk to clients?
How can I get something added to the SQL Server wishlist?
Does SQL Server run under Win2000/NT5? (Version 7.0)
Does SQL Server 7.0 support XML? (Version 7.0)
Where can I get examples of XP code for SQL Server? (all versions)
What programming languages can I write an extended stored-procedure in? (all versions)
Can I amend the "window" that SQL Server applies for 2-digit dates for Y2K? (all versions)
Can I generate SQL 7.0 scripts in 6.5 format so I can run them against old servers? (Version 7.0)
What is the equivalent of an Oracle synonym in SQL Server? (all versions)
Does SQL Server support before and after (pre and post) triggers? What about row vs set triggers? (all versions)
Does SQL Server support Unicode? (all versions)
Does SQL 7 support tempdb in ram? (Version 7.0)
Can I run SQL 6.5 and SQL 7.0 on the same machine? (Version 7.0)
3rd Party Tools
What tools are available to debug stored-procedures under SQL Server? (All versions)
What tools are available to produce entity relationship diagrams for SQL Server?
Where can I get the SQL Server Programmers Toolkit? (all versions)
What is SQLHDTST.EXE for SQL Server and where do I get it? (Version 7.0)
What tools are available to stress/benchmark SQL Server? (all versions)
What 3rd party tools are available to generate test-data for SQL Server?
Installation, Service Packs, and Hot Fixes
What service pack am I running? (all versions)
I'm having trouble installing SQL Server. What could be going wrong? (all versions)
Should I upgrade to 7.0? (Version 7.0)
Should I apply SP5? (Version 6.5)
I am having problems installing a SQL Service pack. (all versions)
What versions/types of SQL 7 are there and what are the differences? How can I tell which one I am running? (Version 7.0)
I need to move SQL Server to a new NT server - what are my options? (all versions)
Should I apply NT 4.0 SP4 to my SQL Server system? (all versions)
How can I install the 32-bit SQL Server client for NT or Windows 9x? (all versions)
Why does my copy of SQL Server say it is the 120 day eval edition when it is the full commercial copy? (all versions)
How can I upgrade the 120-day evaluation version to the full SQL version? (all versions)
Which sort-order should I choose for my SQL Server for the fastest performance? (all versions)
What are SQL Server hot-fixes and where can I get them? (all versions)
How can I install SQL 7.0 Desktop Edition from the Back Office 4.5 CD's? (all versions)
Why am I getting a message "The Enterprise Edition server component cannot be installed on Windows NT Server using this CD. Only client components will be available for installation." installing SQL 7? (all versions)
I'm having trouble installing SQL Server on Win9x. What could be going wrong? (all versions)
How do I install the SQL Server Internet Connector Software/license? What does it cover? (all versions)
When I run the MDAC setup routine that comes with SQL Server SP5(a) why does it re-invoke the service pack setup? (Version 6.5 SP5a)
How can I re-install just the MMC for SQL Server? (Version 7.0)
Should I apply NT 4.0 SP5 to my SQL Server system?
I am getting a gpf/registry error installing SQL Server - what is happening? (all versions)
Should I apply SP4? Will it cause more problems than it fixes? (Version 6.5)
Why can't I install SP4 for SQL Server to my Win95 machine like the readme says I can? (Version 6.5)
I am getting a "cfgchar" problem installing SQL Server 6.5 SP5 ? (Version 6.5)
Do SQL Service Packs have to be applied in order or can I just apply the latest one? (all versions)
Where can I get SQL 7.0 SP1? (Version 7.0)
Should I apply SP1 for SQL 7? Are there any known issues? (Version 7.0)
How can I modify the SQL 7 unattended install script? (Version 7.0)
Can I install SQL on a Terminal Server machine? (all versions)
How can I install SQL Server client tools in unattended mode? (all versions)
I'm having trouble with SQL Server unattended install. What could be going wrong? (all versions)
How can I completely uninstall SQL Server? (all versions)
How can I un-install a SQL Server servicepack and go back to the original version? (all versions)
Internet and SQL Server
Can I replicate/connect/whatever to SQL Server over the Internet? (all versions)
Logs
Why won't my log shrink in SQL 7? (Version 7.0)
How can I view the transaction log? (all versions)
MSDE
Can I install SQL 7 and MSDE on the same machine? (Version 7.0)
What tools can I use to administer MSDE? (Version 7.0)
I am trying to upgrade MSDE to full SQL Server and getting an error "You cannot install a version which is older (7.00.623) than the version on your machine (7.00.677). Un-install the older version." (Version 7.0)
ODBC
Why does my ODBC v2 application not work with SQL 7? Why do I get a gpf in VBDB300.DLL? (Version 7.0)
I am getting problems with ODBC apps since upgrading to SQL 6.5 SP5a or installing SQL 7. (all versions)
Why do I get an error connecting to a working SQL 7.0 datasource with MSQUERY/Access/other programs? (Version 7.0)
I'm installing SQL Server and am getting a problem with ODBC - error "Setup as detected previous versions of ODBC files...
Why have I got a large file called SQL.LOG that I can't delete? (all versions)
Where can I get an OLE-DB/ODBC/JDBC driver to connect SQL Server to product xyz? (all versions)
I get the following error connecting to SQL Server. "The ODBC catalog stored procedures installed on server are version x.xx.xxx, version yy.yyy.yyyy or later is required to ensure proper operation." (all versions)
Is ODBC thread-safe and therefore ok for a multi-threaded application?
OLAP
Profiler/SQL Trace
When I do a SQL Server trace I see lots of sp_cursoropen and sp_cursorfetch commands being sent that I am not generating. What are they? (all versions)
Query Analyzer/isqlw
Why do I only get 255 characters back in a column in Query Analyzer? (Version 7.0)
Under SQL 6.5 a user of ISQL/W or other tools could only see the databases they had permission to in the drop down boxes. Under 7.0 they can see all the databases even if they don't have permission to go into them? Why? (Version 7.0)
Replication
I'm getting a 28000 error message on SQL Server after applying SP5a/MDAC 2.1. (Version 6.5)
Can I use SQL Replication between machines of different sort-orders/character sets/code-pages? (all versions)
Why am I limited to 6000 bytes per row when using merge replication? (Version 7.0)
Security
Why do I get an error in sqlsspi.c ? (all versions)
SQL Mail
How can e-mail with SQL Server without using an MS-Mail or Exchange server? (all versions)
SQL Mail keeps hanging - meaning I have to restart SQL Server to clear it. How can I resolve this? (all versions)
Why does sp_processmail fail with an error "Supplied datatype for set_user is not allowed, expecting 'varchar'" on SQL 7? (Version 7.0)
How do I install SQL Mail? I've got problems installing SQL Mail on SQL Server. (all versions)
How can I use Lotus Notes for SQL Mail? (all versions)
Mail messages are stuck in my outbox with SQL Mail. They don't get sent until I login as the user locally and open the inbox manually. (all versions)
SQL Server Agent/SQL Executive
When SQL Executive starts I get the error "This version of SQLExecutive requires SQL Server version 6.5 or later" id 109. (Version 6.5 and up)
How can I make sure the SQL Server agent auto-starts when I start SQL Server? (Version 7.0)
What can I do if the SQL Agent/Executive scheduler doesn't support the features I need - like multiple file dependencies, holidays etc.? (Version 7.0)
System tables
How can I tell whether a table is a system (Microsoft) table in SQL 7? (Version 7.0)
How can I amend the system tables in SQL Server? Can I put a trigger on one? (all versions)
Transferring/moving objects/data
How do I transfer data to/from another DBMS/format and SQL Server? (e.g. from FoxPro, Access, AS/400, DB/2, Oracle....) (all versions)
How do I transfer data between SQL Server databases / across servers? (all versions)
How can I transfer SQL Server data between an Intel box and an Alpha? (all versions)
How can I transfer database diagrams from one SQL 7 server to another? (Version 7.0)
I'm doing a transfer using the SQL EM transfer tool, and not only is it not transferring the objects, it is dropping them from the source. What is going on? (Version 6.5)
Why can't I BCP a Unix file into SQL Server? I get "UNEXPECTED EOF" messages. (all versions)
Troubleshooting
I'm getting a 1112 error on SQL Server. (Version 4.x)
I'm getting a 1203 error message on SQL Server. (all versions)
I'm getting a 1501 error message on SQL Server. (Version 6.5)
I'm getting a 1608 error message on SQL Server. (all versions)
I'm getting a 17832 error message on SQL Server. (all versions)
I'm getting a 2503 error message on SQL Server. (Up to version 6.5)
I'm getting a 2521 error message on SQL Server. (Up to version 6.5)
I'm getting a 416 error message on SQL Server. (Up to version 6.5)
I'm getting a 4409 error message on SQL Server. (Version 6.5 SP5a)
I'm blocking against syscolumns which is causing my application to lock up. (Version 6.5)
I'm getting a 602 error message on SQL Server. (Version 6.5)
I am getting an error 603 restoring a database ever since I applied SP5 to SQL 6.5. (Version 6.5 SP5)
I am getting an error 603 on database recovery in a SQL Server 6.0 system. (Version 6.0)
I am getting a 605 error message on SQL Server. (all versions)
I am getting a 610/925 error message on SQL Server. (Version 6.5)
I am getting a 702 error message on SQL Server. (Version 6.5)
I am getting an 80004005 error message. (all versions)
I am getting an 8623 error message on SQL Server. (Version 7.0)
I am getting an access violation or dbprocess dead message. (all versions)
Does SQL Server have memory leaks? How can I tell? Why is SQL Server using so much memory? (all versions)
I can't see the SQL performance counters in PerfMon. What could be wrong? (all versions)
I am getting a message "Protocol error in TDS Datastream" from a SQL Server query. What is going on? (all versions)
My SQL Server database is showing as "recovering". Why and what can I do? (all versions)
I am getting a message GetOverLappedResult() from a SQL Server query. What is going on? (all versions)
I am getting a blue screen / completely hung machine / server restart on my SQL Server/client. (all versions)
What does the SQL Server error message "Lazywriter: WARNING, couldn't find slot, 8/8, scanned 8" mean? (all versions)
My SQL Server database has been marked "suspect" - what can I do? (all versions)
SQL Server hangs and becomes unresponsive - what can I do? (all versions)
Why do I get an error "SQL Server Assertion : File: Line=yyy Failed Assertion='zzz' (all versions)
I'm getting a "bad token" error message from SQL Server - what causes this? (Version 6.5)
Why do I get an "UNEXPECTED EOF" message in BCP? (all versions)
What does the SQL Server error message "sort: bob write not complete after xx seconds." mean? (all versions)
What does "bufdiscard: WARNING, page xx (dbid n, bp 0xzzzzzzzz) has stat0x10080(), skipping" mean? (Version 6.5 and earlier)
What do the bufwait, writelog and buffer latch errors in SQL Server mean? (all versions)
A query produces this error in SQL 7 : - Internal Query Processor Error: The query processor could not produce a query plan. (Version 7.0)
Why do I get the message "WARNING: Process being freed while holding Dataserver semaphore" in SQL Server. (all versions)
Why is the date I get via select getdate() wrong in SQL Server? (Version 6.5 and below)
Why do I get slow or no connections to SQL Server using IPX from my Win3x and Win9x clients? From Windows NT they work fine. (all versions)
What does the error message "Failed to obtain TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE" mean in SQL Server and should I be worried? (all versions)
How can I fix a corruption in a system table? (all versions)
I am trying to load a trace file into the SQL 7 Profiler/Index Tuning Wizard, but all I get is "Error analysing workload (60)". What does this mean? (Version 7.0)
Why does ISQL/W gpf when I move the mouse pointer with an Intellimouse. (Version 7.0)
I've overallocated memory for SQL Server and now it won't start - what can I do? (Version 6.5)
I have filled the model database's log and now SQL won't start with a 1105 error - what can I do? (all versions)
Why am I getting an MtxClu... error after applying SQL 6.5 SP5a? (Version 6.5 SP5a)
Why do I get an error message "Not enough server storage is available to process this request"? (all versions)
I'm getting an error 1117/2543 in SQL Server. Can I rebuild the extents somehow? (all versions)
Why do I get an error "Bad pointer 0xABCDEF encountered while remapping stored procedure" in SQL Server? (all versions)
Why would I get a message "Retrying row fetch:rowoffset entry out of range (pg% obj%, db %)" from SQL Server? (all versions)
Why do I get the error "Cannot use file because it was originally formatted with sector size and is now on a device with sector size " when I move SQL Server's log file? (Version 7.0)
Why do I get an error message "Not enough server storage is available to process this request"? (all versions)
I have a SQL Server process that shows as being in SPINLOOP. What does this mean? (all versions)
Tuning
Upgrades
Can I upgrade SQL 4.x to 7.0 directly? (Version 4.x)
When upgrading from SQL 6.5 to SQL 7.0 between two machines I get the error message "Unable to connect to export server. Please verify that you are an NT administrator on that machine". (Version 4.x)

How To Install MySQL or PostgreSQL on Microsoft Windows

How To Install MySQL or PostgreSQL on Microsoft Windows


http://port25.technet.com/videos/research/MySQL_Windows.pdf

PostgreSQL on Windows: A Primer
http://port25.technet.com/videos/research/postgresql on windows_final (revised).pdf
This paper will cover the installation and initial configuration of PostgreSQL 8.2 on Windows up to a point where a database is created and plpgsql is installed in it. We assume an ability to walk through the install wizard in general, though screens that do require additional information will be covered (with screenshots). Important options in the postgresql.conf and pg_hba.conf will be covered, as will database creation in PgAdmin III.

MySQL OLE DB Provider

MySQL OLE DB Provider:

MySQL probably is the most popular open source database server. To learn more about MySQL Database server, please visit MySQL Website.

MySQL library comes with some API's. You may download MySQL Library and its source code from MySQL Website. The MySQL OLE DB Provider is a thin wrapper of those MySQL library Version 5.0 API's. This provider is being implemented with ATL OLE DB Provider templates so that the provider itself has small footprint even though the provider does not have many dependencies. It only depends on MySQL library along with Windows OS runtime. You may use ADO or ADO.NET 2.0 to access data with MySQL OLE DB Provider"


Why Do You Need MySQL OLE DB Provider
MySQL OLE DB Provider can be used to access MySQL database in your native code and .NET code as well. It has few dependency and is is very easy to use and very easy to deploy as well.
Most importantly, Cherry City Software MySQL OLE DB Provider is the best OLE DB Provider for MySQL database and it does NOT require you to open your source code.

Features
The following are the features that this MySQL OLE DB Provider supports:
Transaction
Record scroll
Parameters - input and output
Bookmarks
Database Schema - tables and their columns
Extended error information
Multiple queries in one single execution*
Transparency - all scripts are transparent to MySQL engine
Command Preparation and Column Information on command object The current release was tested with MySQL 4.1.x and 5.0 and only support Western European charset.I am having difficulties to setup the server to support Unicode. If you have any ideas,please give me your advise. MySQL TIME is mapped to string. MySQL ENUM, SET and GEOMETRY data types are not supported. * - If the SQL statements have parameters, each statement must be executed seperately.

Working with MySQL OLE DB Provider
Using MySQL OLE DB Provider is simple if you have the knowledge of ADO or ADO.NET. The sample code is intended to demostrate how to use MySQL OLE DB Provider. You may find sample code below that demostrate how to use MySQL OLE DB Provider with ADO and ADO.NET. The sample will show you how to use the following features:
Transaction
Parameter input and output
Database schema
Record scroll
Bookmark

VB Sample Code
'define connection used through out in the form
Private mConn As ADODB.Connection

'//Synposis:
'//1. Create an ADO connection
'//2. Enable Read Data button if the table exists
Private Sub Form_Load()
    On Error GoTo trap
    Set mConn = New Connection
    'connect to MySQL server (localhost, port 3306), Database Name: test
    mConn.Open "Provider=OleMySql.MySqlSource.1; " _
        & " Data Source=localhost,3306; Initial Catalog=test", _
  "root", "myPassword"
    If DoesTableExist("AllFields") Then
        Me.Command3.Enabled = True
    End If
    Exit Sub
trap:
    MsgBox Err.Description
End Sub


'//Synposis:
'//1. Create a table named AllFields if it does not exist. If the table exits,
'//delete all records from the table.
'//2. Insert some records into the table
'//3. Update the records using parameters which makes it easy for
'//binary data manipulation
Private Sub Command1_Click()
    On Error GoTo trap
    On Error GoTo trap
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = mConn
   
    'delete all data from the table if table exists
    If DoesTableExist("AllFields") Then
        cmd.CommandText = "DELETE FROM AllFields;"
    Else  'create AllFields table if it does not exists
        cmd.CommandText = "CREATE TABLE AllFields" _
            & "(ID INTEGER PRIMARY KEY, Name VARCHAR(20), " _
            & "LongDoc MEDIUMTEXT, Image BLOB, Date5 DATE, " _
            & "datetime6 DATETIME, time6 TIME, " _
            & "timestamp7 TIMESTAMP, Year8 YEAR);"
    End If
    cmd.Execute
    cmd.CommandText = ""
    
    'insert data using multiple query statements
    Dim n As Long
    Dim I As Long
    For I = 0 To 300
        cmd.CommandText = cmd.CommandText _
            & "INSERT INTO AllFields (ID, Name, LongDoc, Date5, " _
            & "datetime6, time6, timestamp7, Year8) VALUES (" _
            & I & ", 'Sean D', 'this field can hold long long" _
            & " long long ... text string',  '2005-6-10', " _
            & "'2005-6-12 09:01:10', '6:05:00', '2005-6-15 1:22:11', 2005);"
    Next I
    mConn.BeginTrans
    cmd.Execute n
    mConn.CommitTrans
 
    'update records with Parameters
    Dim BA(999) As Byte
    For I = 0 To 999
        BA(I) = (I + 1) Mod 256
    Next I
    Dim params(2) As Parameter
    Set params(0) = New ADODB.Parameter
    params(0).Type = adInteger  'integer
    params(0).Value = 1
    Set params(1) = New ADODB.Parameter
    params(1).Type = adBSTR      'string
    params(1).Value = "Sean Deng"
    Set params(2) = New ADODB.Parameter
    params(2).Type = adBinary
    params(2).Value = BA
    params(2).Size = 1000
    cmd.CommandText = "UPDATE AllFields SET Name=?, Image=? WHERE ID=?;"
    'add parameters according to commend text
    cmd.Parameters.Append params(1)
    cmd.Parameters.Append params(2)
    cmd.Parameters.Append params(0)
    cmd.Execute
    
    'using transaction
    params(0).Value = 2
    mConn.BeginTrans
    cmd.Execute
    mConn.RollbackTrans  'the Name is NOT updated when read back
    params(0).Value = 3
    mConn.BeginTrans
    cmd.Execute
    mConn.CommitTrans  'the Name is updated when read back
   
    Me.Command3.Enabled = True
    Set cmd = Nothing
    MsgBox "Done"
    Exit Sub
trap:
    MsgBox Err.Description   
End Sub


'//Synposis:
'//Create a recordset to get all data from the table and present the
'//data in a data grid.
'//To view the binary data read back from the database, you may open
'//open a watch window in the debug mode
Private Sub Command3_Click()

    On Error GoTo trap
    Dim BA As Variant
    Me.MSHFlexGrid1.Clear
    Dim rs As New ADODB.Recordset
    rs.Open "SELECT * FROM AllFields", mConn, adOpenStatic, adLockReadOnly
    If (rs.RecordCount > 3) Then
        rs.Move 3
        BA = rs("image").Value 'add BA to the watch window to view data
    End If
   
    Set Me.MSHFlexGrid1.Recordset = rs
    Exit Sub
trap:
    MsgBox Err.Description
   
End Sub


'//Synposis:
'//Clear all data from the data grid  
Private Sub Command2_Click()
 Me.MSHFlexGrid1.Clear
End Sub

'//Synposis:
'//Check if the specified table exists. Return true if it does. Return false
'//otherwise. 
Private Function DoesTableExist(ByVal sTable As String) As Boolean
    Dim rs As ADODB.Recordset
    Set rs = mConn.OpenSchema(adSchemaTables)
    While (Not rs.EOF)
        If UCase(rs("table_name")) = UCase(sTable) Then
            DoesTableExist = True
            rs.Close
            Set rs = Nothing
            Exit Function
        End If
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
End Function


'//Synposis: Create a stored procedure and call it
'
Private Sub SPCall()
    On Error GoTo trap
    Dim cmd As ADODB.Command
    Set cmd = New Command
    cmd.ActiveConnection = mConn
   
    cmd.CommandText = "DROP PROCEDURE IF EXISTS MyProc;CREATE PROCEDURE MyProc"
    cmd.CommandText = cmd.CommandText + "(param INT, OUT param1 INT)" _
                    & " BEGIN SET param1 = param*param; END;"
    cmd.Execute
   
    Dim param(1) As New ADODB.Parameter
    param(0).Direction = adParamInput
    param(0).Type = adInteger
    param(0).Value = -1000
    param(1).Direction = adParamOutput
    param(1).Type = adBSTR
    param(1).Value = ""
    param(1).Size = 40

   
    cmd.Parameters.Append param(0)
    cmd.Parameters.Append param(1)
   
    cmd.CommandText = "call MyProc(?, ?)"
    cmd.CommandType = adCmdText 'must use text command type
    cmd.Execute
   
    Exit Sub
trap:
    MsgBox Err.Description


End Sub
  

PrimeBase XT (PBXT) pluggable storage engine for MySQL

PrimeBase XT (PBXT) pluggable storage engine for MySQL

Database - Inserting MySQL Records Via ODBC With Visual Basic .NET

Database - Inserting MySQL Records Via ODBC With Visual Basic .NET

VB mysql API is a wrapper around libmysql.dll to connect Visual Basic applications to mySQL databases. It comes with the translation of the API calls to libmysql.dll and a class wrapper to ease the implementation of mySQL into your application

VB mysql API is a wrapper around libmysql.dll to connect Visual Basic applications to mySQL databases. It comes with the translation of the API calls to libmysql.dll and a class wrapper to ease the implementation of mySQL into your application

MySQL AB :: CursorTypes, LockTypes, and CursorLocations

MySQL AB :: CursorTypes, LockTypes, and CursorLocations

MySQL - Wikipedia, the free encyclopedia

MySQL - Wikipedia, the free encyclopedia

MySQL 5.0 vs. Microsoft SQL Server 2005

MySQL 5.0 vs. Microsoft SQL Server 2005

ONLamp.com -- Ten MySQL Best Practices

ONLamp.com -- Ten MySQL Best Practices

How To Set Up A Load-Balanced MySQL Cluster | HowtoForge - Linux Howtos and Tutorials

How To Set Up A Load-Balanced MySQL Cluster HowtoForge - Linux Howtos and Tutorials

ONLamp.com -- Live Backups of MySQL Using Replication

ONLamp.com -- Live Backups of MySQL Using Replication

MySQL AB :: MySQL Cluster FAQ

MySQL AB :: MySQL Cluster FAQ

MySQL AB :: MySQL 5.0 Reference Manual :: 6.4 How to Set Up Replication

MySQL AB :: MySQL 5.0 Reference Manual :: 6.4 How to Set Up Replication