Exporting Data from MS SQL SERVER to MySQL

Readers, always ask me to write on CURSOR, with their customize problem, But I normally write which will help
every one. One of reader asked me long back about the cursor and exporting data from MS SQL Server to MySQL.I had the same scenario, I need to export data from MS SQL to MySQL, and I have to do it quickly.
I didnt find on internet a better solution because of the time constraint.
I have acheived my task by using CURSOR and generate customize queries, So I can execute them at MySQL database and populate required data.

My main focus is to share how to use cursor and also one of the way to export data from MS SQL Server to MySQL. 

You can customize and replace your queries with one written below.

Lets Start. 

–insert records in a temp table

Select
username
,
‘e50b13c57937586c4c6c6fa30f0bce24’ as ‘password’,
’33VPTHTe’ as ‘salt’,
‘dkRZvhwOMrXLwL8mSJA2PneuMkBCXD9aRLuWjgIF2grtYNW6bQ’ as ‘loginkey’,
user_email as ’email’,
‘1206339144’ as ‘regdate’,
‘1206423077’ as ‘lastvisit’,
‘0’ as ‘lastpost’,
snull(User_website,) as ‘website’,
isnull(user_icq,) as ‘icq’,
isnull(user_aim,) as ‘aim’,
isnull(user_yim,) as ‘yahoo’,
isnull(user_msnm,) as ‘msn’,
isnull(user_sig,) as ‘signature’,
isnull(user_allow_pm,‘0’) as ‘receivepms’,
isnull(user_notify_pm,‘0’) as ‘pmnotify’,
isnull(user_allowavatar,‘0’) as ‘showavatars’,
‘2’ as ‘usergroup’
into #temp
Fromphpbb_users
Where user_session_page<>0 

–Declaring variables to insert column values later
Declare @username varchar(1000)
Declare @password varchar(500)
Declare @salt varchar (250)
Declare @loginkey varchar(500)
Declare @email varchar(500)
Declare @regdate varchar(500)
Declare @lastvisit varchar(500)
Declare @lastpost varchar(500)
Declare @website varchar(500)
Declare @icq varchar(255)
Declare @aim varchar(255)
Declare @yahoo varchar(255)
Declare @msn varchar(255)
Declare @signature varchar(255)
Declare @receivepms varchar(255)
Declare @pmnotify varchar(255)
Declare @showavatars varchar(255)
Declare @usergroup varchar(10)

–declaration of the cursor
DECLARE @getBB CURSOR

–Initializing of cursor for particular query result.
SET @getBB = CURSOR FOR SELECT * FROM #TEMP

OPEN @getBB –opening a cursor
FETCH NEXT –Syntax of cursor to move next
FROM @getBB INTO — Inserting single row data as a column to variables

@username ,@password,@salt,@loginkey ,@email,@regdate,@lastvisit,@lastpost,
@website,@icq,@aim ,@yahoo,@msn,@signature,@receivepms,@pmnotify,@showavatars ,@usergroup

WHILE @@FETCH_STATUS = 0 — condition execute until record end

BEGIN 

— Here you can write your customize result or further execution.

Print ‘ INSERT INTO users(username, password, salt,loginkey,email, regdate, lastvisit,
lastpost, website, icq, aim, yahoo, msn, signature, receivepms,
pmnotify,showavatars,usergroup
)VALUES

(

”’

+ @username + ”’,”’+ @password + ”’,”’+ @salt + ”’,
”’
+ @loginkey + ”’,”’+ @email + ”’,”’+ @regdate + ”’,
”’
+ @lastvisit + ”’,’+ @lastpost + ‘,”’+ @website + ”’,
”’
+ @icq + ”’,”’+ @aim + ”’,”’+ @yahoo + ”’,”’+ @msn + ”’,
”’
+ @signature + ”’,”’+ @receivepms + ”’,”’+ @pmnotify + ”’,
”’
+ @showavatars +”’,”’ + @usergroup + ”’);’
 
FETCH NEXT — Moving next
FROM @getBB INTO — Insert next record to the variables.
@username ,@password,@salt,@loginkey,
@email,@regdate,@lastvisit,@lastpost,@website,@icq,
@aim ,@yahoo,@msn,@signature,@receivepms,@pmnotify,@showavatars ,@usergroup
ENDCLOSE @getBB — Closing variable
DEALLOCATE @getBB — removing from the memory
GO–droping table.

DROP TABLE #TEMP
—————————————————————————————–
Hope this will solve Export data from MS Sql Server to MySQL and also explain a cursor.

Rana

SQL SERVER – Find any expression in the database

My colleagues asked me hundred times, how to find a particular columns, table name, stored procedure, views in the database.

Always forgot to remember one query. So for them only 🙂

Below query you can any table, stored procedure or views who ever have that expression as a column or object name.

SELECT * FROM SYSOBJECTS WHERE ID IN (SELECT ID FROM SYSCOMMENTS WHERE TEXT LIKE ‘%PRODUC%’)

The query produced all the object names. where ever it finds expression like ‘PRODUC

If you wanted to find any table name only then

SELECT * FROM SYSOBJECTS WHERE NAME LIKE ‘%TA%’ and xtype=‘u’

result

For stored procedure  pass  xtype=‘p’ and for views xtype=‘v’

Hope next time my friends will visit from my blog to get this query 😉

Rana

Fulltext Searching,Fulltext Indexing, Installing Microsoft Search Service

I received some emails from several groups, people were asking about the fulltext search, so I decided to write about fulltext indexing. I had consolidated a lot of information in one article and these includes Installation of fulltext,MSSearch Services and Noisy words which should be eliminated before searching.

I hope this would help my reader and you are always free to ask me for further questions.

Full Text Searching is a free,optional component of MS SQL 2000. When installed, it offers a vast array of additional string querying abilities. Full Text Searching allows for string comparisons similar to internet search engines, returning both results and a matching score or weight. With regular TSQL, string matching is usually limited to an exact match, or a wildcard match with the keyword “LIKE.” Full Text Searching exceeds this by searching for phrases, groups of words, words near one another, or different tenses of words, such as run, running, and ran.

Full Text Searching is accomplished by installing a new service (Microsoft Search), and using key words in TSQL designed specifically for text searching.

Some steps to install these services.

Microsoft Search

Unlike other SQL operations, Full Text Searching is not managed by the MSSQL Server service. Instead, a new service, Microsoft Search, must be installed and started. A primary reason for this is that Full Text Searching is done against a special index contained in a Catalog.
Catalogs that are used to support searching are not part of the regular SQL server installation; they are kept outside the MDF, and are stored in separate physical files.
The Microsoft Search service has two primary jobs, indexing support and querying support.
Indexing support includes the tasks of defining the Catalogs, and indexes they contain, creating them and keeping the data up to date as changes take place. When search queries are issued, the second job of the Microsoft Search service begins, determining which Catalog index meets the query requirements. Once determined, Microsoft Search returns the identity of selected rows, along with a ranking value if requested, back to the SQL Server service for query completion.
This brings up two requirements that the Microsoft Search service imposes. First, there must be a unique index on the base table to which the Catalog refers. This is required because the Search service only reports the identity of the selected rows back to the SQL service, not the record set itself. Second, Full Text Searching is only supported on Windows Server. Either NT 4 or 2000 Server can be used, but not Workstation, Windows 98, or XP. These other clients can issue quires, but not host the Search service.

Installing Search

Check in the services list for “Microsoft Search.” If not listed, insert the SQL Server media, and select “Upgrade, Add or Remove Components.” Click next for “Add Components.” On the next screen, select “Server Components,” Full-Text Search, and then continue with the rest of installation.

Creating a Catalog

In this first example, we will be creating a Full Text Search on the pubs database. To begin, open Enterprise Manager and highlight the local server.

In Sql Server 2005 the steps are little different but concept is same.

1.  From the top menu, select Tools, Full Text Indexing.2.  Click next on the opening screen.3.  On the “Select a Database” screen, choose pubs.4.  For the next screen, select “titles” as the table and click next.5.  Keep the default unique index; there is only one on the pubs table.6.  In the next screen, select Title and Notes as the fields we would like indexed.

7.  On the “Select a Catalog” screen, because this is the first Catalog on the server, we are prompted for a name. In this example, “demo” is used as the Catalog name.

8.  This next screen “Populate Catalogs,” leave blank and click next.

9.  Click Finish, and the following success screen should appear:

10.  From Enterprise Manager, expand Full-Text Catalogs, the “demo” catalog should appear in the right window.
11.  Right click and select “Start Full Population.”12.  The catalog is now complete.13.  To verify in creation, open Query Analyzer and select the pubs database.14.  Execute sp_help_fulltext_catalogs, the new Catalog should appear.
15.  Issue a test query of:

SELECT title, notes FROM titles WHERE CONTAINS (notes, ' "quick easy" ')

This should return one record with a title of “Silicon Valley Gastronomic Treats.”

One of the first items to notice about Full Text Searching is that it is not automatic. Even after Microsoft Search is installed, it still must be configured for each database, table, and field we want to search. If a field will need to be searched, it must first be included in a Catalog. Steps 1 through 4 and 6 accomplished this.

Step 5 demonstrates one the Full Text Search requirements, that each table made available for searching contains a unique index. When a search query is issued, Microsoft Search will determine which records meet the query, and will report their unique index key back to the MSSQL Server service for completion of the query.

The result of Step 7, “Select a Catalog,” is the creation of an entire file structure to support the Catalog. To find the name of structure from Enterprise Manager, expand Full-Text Search, then right click the “demo” Catalog and select properties. By default, Catalogs will be saved under the default server directory, MSSQL, FTDATA.

For the “demo” Catalog, a new directory structure named “SQL00000500005” as shown in the “Physical catalog” field, has been created. This structure will store the indexes and data files needed by the Search service. They are not text readable. The “Item count” field shows the sum of the tables and rows indexed.

Conclusion

Full Text Searching brings advanced string querying support to SQL. In this first article, a new Catalog containing an index to search on two fields of the pubs database has been created. Future articles will expand this Catalog to include searching for words near each other, returning the rank or weight of the search, and indexing Microsoft Office documents. A number of housekeeping tasks will also be introduced, such as keeping the Catalog up to date when data on the base table changes, and determining whether a word should be indexed or not (Noise words). Although Full Text Searching is far from automatic, the results it provides make it worth the effort.

Here are few Noise Words which should exclude from the search.

“a”, “a’s”, “able”, “about”, “above”, “according”, “accordingly”, “across”, “actually”, “after”, “afterwards”, “again”, “against”, “ain’t”, “all”, “allow”, “allows”, “almost”, “alone”, “along”, “already”, “also”, “although”, “always”, “am”, “among”, “amongst”, “an”, “and”, “another”, “any”, “anybody”, “anyhow”, “anyone”, “anything”, “anyway”, “anyways”, “anywhere”, “apart”, “appear”, “appreciate”, “appropriate”, “are”, “aren’t”, “around”, “as”, “aside”, “ask”, “asking”, “associated”, “at”, “available”, “away”, “awfully”, “b”, “be”, “became”, “because”, “become”, “becomes”, “becoming”, “been”, “before”, “beforehand”, “behind”, “being”, “believe”, “below”, “beside”, “besides”, “best”, “better”, “between”, “beyond”, “both”, “brief”, “but”, “by”, “c”, “c’mon”, “c’s”, “came”, “can”, “can’t”, “cannot”, “cant”, “cause”, “causes”, “certain”, “certainly”, “changes”, “clearly”, “co”, “com”, “come”, “comes”, “concerning”, “consequently”, “consider”, “considering”, “contain”, “containing”, “contains”, “corresponding”, “could”, “couldn’t”, “course”, “currently”, “d”, “definitely”, “described”, “despite”, “did”, “didn’t”, “different”, “do”, “does”, “doesn’t”, “doing”, “don’t”, “done”, “down”, “downwards”, “during”, “e”, “each”, “edu”, “eg”, “eight”, “either”, “else”, “elsewhere”, “enough”, “entirely”, “especially”, “et”, “etc”, “even”, “ever”, “every”, “everybody”, “everyone”, “everything”, “everywhere”, “ex”, “exactly”, “example”, “except”, “f”, “far”, “few”, “fifth”, “first”, “five”, “followed”, “following”, “follows”, “for”, “former”, “formerly”, “forth”, “four”, “from”, “further”, “furthermore”, “g”, “get”, “gets”, “getting”, “given”, “gives”, “go”, “goes”, “going”, “gone”, “got”, “gotten”, “greetings”, “h”, “had”, “hadn’t”, “happens”, “hardly”, “has”, “hasn’t”, “have”, “haven’t”, “having”, “he”, “he’s”, “hello”, “help”, “hence”, “her”, “here”, “here’s”, “hereafter”, “hereby”, “herein”, “hereupon”, “hers”, “herself”, “hi”, “him”, “himself”, “his”, “hither”, “hopefully”, “how”, “howbeit”, “however”, “i”, “i’d”, “i’ll”, “i’m”, “i’ve”, “ie”, “if”, “ignored”, “immediate”, “in”, “inasmuch”, “inc”, “indeed”, “indicate”, “indicated”, “indicates”, “inner”, “insofar”, “instead”, “into”, “inward”, “is”, “isn’t”, “it”, “it’d”, “it’ll”, “it’s”, “its”, “itself”, “j”, “just”, “k”, “keep”, “keeps”, “kept”, “know”, “knows”, “known”, “l”, “last”, “lately”, “later”, “latter”, “latterly”, “least”, “less”, “lest”, “let”, “let’s”, “like”, “liked”, “likely”, “little”, “look”, “looking”, “looks”, “ltd”, “m”, “mainly”, “many”, “may”, “maybe”, “me”, “mean”, “meanwhile”, “merely”, “might”, “more”, “moreover”, “most”, “mostly”, “much”, “must”, “my”, “myself”, “n”, “name”, “namely”, “nd”, “near”, “nearly”, “necessary”, “need”, “needs”, “neither”, “never”, “nevertheless”, “new”, “next”, “nine”, “no”, “nobody”, “non”, “none”, “noone”, “nor”, “normally”, “not”, “nothing”, “novel”, “now”, “nowhere”, “o”, “obviously”, “of”, “off”, “often”, “oh”, “ok”, “okay”, “old”, “on”, “once”, “one”, “ones”, “only”, “onto”, “or”, “other”, “others”, “otherwise”, “ought”, “our”, “ours”, “ourselves”, “out”, “outside”, “over”, “overall”, “own”, “p”, “particular”, “particularly”, “per”, “perhaps”, “placed”, “please”, “plus”, “possible”, “presumably”, “probably”, “provides”, “q”, “que”, “quite”, “qv”, “r”, “rather”, “rd”, “re”, “really”, “reasonably”, “regarding”, “regardless”, “regards”, “relatively”, “respectively”, “right”, “s”, “said”, “same”, “saw”, “say”, “saying”, “says”, “second”, “secondly”, “see”, “seeing”, “seem”, “seemed”, “seeming”, “seems”, “seen”, “self”, “selves”, “sensible”, “sent”, “serious”, “seriously”, “seven”, “several”, “shall”, “she”, “should”, “shouldn’t”, “since”, “six”, “so”, “some”, “somebody”, “somehow”, “someone”, “something”, “sometime”, “sometimes”, “somewhat”, “somewhere”, “soon”, “sorry”, “specified”, “specify”, “specifying”, “still”, “sub”, “such”, “sup”, “sure”, “t”, “t’s”, “take”, “taken”, “tell”, “tends”, “th”, “than”, “thank”, “thanks”, “thanx”, “that”, “that’s”, “thats”, “the”, “their”, “theirs”, “them”, “themselves”, “then”, “thence”, “there”, “there’s”, “thereafter”, “thereby”, “therefore”, “therein”, “theres”, “thereupon”, “these”, “they”, “they’d”, “they’ll”, “they’re”, “they’ve”, “think”, “third”, “this”, “thorough”, “thoroughly”, “those”, “though”, “three”, “through”, “throughout”, “thru”, “thus”, “to”, “together”, “too”, “took”, “toward”, “towards”, “tried”, “tries”, “truly”, “try”, “trying”, “twice”, “two”, “u”, “un”, “under”, “unfortunately”, “unless”, “unlikely”, “until”, “unto”, “up”, “upon”, “us”, “use”, “used”, “useful”, “uses”, “using”, “usually”, “v”, “value”, “various”, “very”, “via”, “viz”, “vs”, “w”, “want”, “wants”, “was”, “wasn’t”, “way”, “we”, “we’d”, “we’ll”, “we’re”, “we’ve”, “welcome”, “well”, “went”, “were”, “weren’t”, “what”, “what’s”, “whatever”, “when”, “whence”, “whenever”, “where”, “where’s”, “whereafter”, “whereas”, “whereby”, “wherein”, “whereupon”, “wherever”, “whether”, “which”, “while”, “whither”, “who”, “who’s”, “whoever”, “whole”, “whom”, “whose”, “why”, “will”, “willing”, “wish”, “with”, “within”, “without”, “won’t”, “wonder”, “would”, “would”, “wouldn’t”, “x”, “y”, “yes”, “yet”, “you”, “you’d”, “you’ll”, “you’re”, “you’ve”, “your”, “yours”, “yourself”, “yourselves”, “z”, “zero”.

SQL SERVER – Full-text indexing

Initializes full-text indexing or removes all full-text catalogs from the current database. By default, all user created databases in Microsoft SQL Server 2005 are enabled for full-text indexing, unless they are created using SQL Server Management Studio. To enable a database for full-text search

Use [DatabaseName]
Go
exec sp_fulltext_database ‘enable’

for more detail click here

SQL SERVER What is DML, DDL, DCL and TCL

DML
DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database. Examples: SELECT, UPDATE, INSERT statements

DDL
DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database. Examples: CREATE, ALTER, DROP statements

DCL
DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it. Examples: GRANT, REVOKE statements

TCL
TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

Thanks to Pinal Dave. 

SQL Server 2005- How to prevent Execution – Goto (T-SQL)

I am familiar with Goto Command since i was using Visual Basic 6.0.

I wanted to prevent or bypass the execution in a stored procedure and i used the same command in Sql Server 2005.

Some details:

GOTO skip and processing continues at the label.

GOTO statements can be used any where in the procedure and statement block.

EXAMPLEDECLARE @CLASS INT

SET @CLASS = 4
IF @CLASS = 4
GOTO Class4;

Class1:
SELECT ‘Jumping To Class1.’

Class2:
SELECT ‘Jumping To Class2.’
GOTO Class5;

Class3:
SELECT ‘Jumping To Class3.’

Class4:
SELECT ‘Jumping To Class4.’
GOTO Class2;

Class5:
SELECT ‘Jumping To Class5.’

SQL SERVER 2005 – How to get processes of database

To get all the processes of the database. 

select spid, status, blocked, open_tran, waitresource, waittype, waittime, cmd, lastwaittype, cpu, physical_io,

memusage, last_batch=convert(varchar(26), last_batch,121),

login_time=convert(varchar(26), login_time,121),net_address,

net_library, dbid, ecid, kpid, hostname, hostprocess,

loginame, program_name, nt_domain, nt_username, uid, sid,

sql_handle, stmt_start, stmt_end

from master.dbo.sysprocesses

where blocked!=0 or waittype != 0x0000

 result