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

What’s the difference between a 301 Redirect and a 302 Redirect?

301 Redirect
Redirects to the site you specified in the Forward To field using a “301 Moved Permanently” HTTP response. The HTTP 301 response code tells user-agents (including search engines) that the location has permanently moved.

302 Redirect
Redirects to the site you specified in the Forward To field using a “302 Moved Temporarily” HTTP response. The HTTP 302 response code tells user-agents (including search engines) that the location has temporarily moved.

ASP.Net – Global.asax not firing

“I am experiencing problem with global.asax; events are not firing, I cant debug global.asax” One of my reader asked me.

Simple and reliable solution is.

Remove global.asax from your project and add again and leave in on the root of the project.

Since we are using ASP.Net 2.0 there is no Global.asax file in visual studio solution by default.

This will solve the problem, Global.asax

Rana

flowchart shapes, ERD,Visio

We normally use visio to build documents which helps developer to understand the flow of the modules and projects. These are common used shapes that’s why I am just taking the definition from Microsoft itself so it would be consistent for everyone.  

Its just a copy paste from Microsoft visio but the main concept behind is to share

What the flowchart shapes represent

When you open the Basic Flowchart template, the Basic Flowchart Shapes stencil opens too. Each shape on the stencil represents a different step in a process.

Of all of the shapes on the Basic Flowchart Shapes stencil, only a few are commonly used. Those commonly used shapes are described here.

  • Terminator  Use this shape for the first and last step of your process.
    Terminator shape
  • Process  This shape represents a step in your process.
    Process shape
  • Predefined process  Use this shape for a set of steps that combine to create a sub-process that is defined elsewhere, often on another page of the same drawing.
    Predefined Process shape
  • Decision  This shape indicates a point where the outcome of a decision dictates the next step. There can be multiple outcomes, but often there are just two —yes and no.
    Decision shape
    Document  This shape represents a step that results in a document.
    Document shape
  • Data  This shape indicates that information is coming into the process from outside, or leaving the process. This shape can also be used to represent materials and is sometimes called an Input/Output shape.
    Data shape
  • Flowchart shapes  You can right-click this multi-shape to set to any of the following shapes: Process, Decision, Document, or Data. Any text you type onto the shape, or information you add to its Shape Data, remains with the shape.This is what the shape looks like on the stencil:Flowchart shapesThis is what you see when you drag the shape onto the drawing page and right-click it:Flowchart shapes with right-click menu
  • Stored data  Use this shape for a step that results in information being stored.
    Stored data shape
  • On-page reference  This small circle indicates that the next (or previous) step is somewhere else on the drawing. This is particularly useful for large flowcharts where you would otherwise have to use a long connector, which can be hard to follow.
    On-page refference shape
  • Off-page reference  When you drop this shape onto your drawing page, a dialog box opens where you can create a set of hyperlinks between two pages of a flowchart or between a sub-process shape and a separate flowchart page that shows the steps in that sub-process.
    Off-page reference shape

HideLess frequently used flowchart shapes

  • Dynamic connector  This connector draws a path around shapes it encounters.
    Dynamic connector shape
  • Line-curve connector  This connector has adjustable curvature.
    Line-curve connector
  • Auto-height box   This is a bordered text box that adjusts to accommodate the amount of text you type. You can set the width by dragging the sides of the shape. Although this shape doesn’t represent a step in a process, it is a handy way to add a text box to your flowchart.
    Auto-height box
  • Annotation   This bracketed text box adjusts to accommodate the amount of text you type. You can set the width by dragging the sides of the shape. Like the Auto-height box shape, this shape doesn’t represent a step in a process. Use this to add comments about your flowchart shapes.
    Annotation shape
  • Manual input  This is a step where a person provides information to the process.
    Manual input shape
  • Manual operation  This is a step that must be performed by a person.
    Manual opperation shape
  • Internal storage  This shape represents information stored on a computer.
    Internal storage shape
  • Direct data  This shape represents information stored so that any single record can be accessed directly. This represents how a computer hard-drive stores data.
    Direct data shape
  • Sequential data  This shape represents information stored in sequence, such ad data on a magnetic tape. When data is stored in sequence, it must be retrieved in sequence. For example, in order to access record 7 you would have to first go through records 1 through 6.
    Sequential data shape
  • Card and Paper tape  This shape represents a physical card or paper tape. Early computer systems used a system of punch cards and paper tape to store and retrieve data and to store and run programs.
    Card shape
  • Display  This shape represents information that is displayed to a person, usually on a computer screen.
    Display shape
  • Preparation  This shape indicates where variables are initialized in preparation for a procedure.Preparation shape
  • Parallel mode  This shape shows where two different processes can operate simultaneously.Parallel mode shape
  • Loop limit  This shape marks the maximum number of times a loop can run before it must go on to the next step.
    Loop limit shape
  • Control transfer  This shape indicates a step that goes to a step other than the typical next step when certain conditions are met.
    Control transfer shape

 

Please visit my official blog for future.

http://blog.joggee.com

 

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