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

Advertisements

Cannot load the DLL xpstar.dll

My database server giving me hard time after displaying this error.When I tried to search on the INTERNET, I was amazed every third person have the same error. Its so common.

“Cannot load the DLL xpstar.dll”

This error message happens because of many reason.

  • If you install some application on the database server.
  • got rebutted because of serge problem.
  • some how directly shutdown

Here is the solution.

Click here to Download this file and paste in the /BIN/ folder in the respective path.

another way to resolve it, repair sql server using installation CD.

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

CLR data type ,hierarchyid

The heirarchyid is a system data type available in SQL Server 2008 to represent hierarchies. It is based on a CLR data type, but is always available, whether the CLR is enabled or not.

The hierarchyid data type is a variable length, system data type. Use hierarchyid to represent position in a hierarchy. A column of type hierarchyid does not automatically represent a tree. It is up to the application to generate and assign hierarchyid values in such a way that the desired relationship between rows is reflected in the values.

  • /
  • /1/
  • /0.3.-7/
  • /1/3/
  • /0.1/0.2/
  • for more detail

    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