mysql to ms sql

Need a little help. I am a newbie with both databases (mysql and mssql). I
am transferring from mysql to ms sql. There are very few tables and data.
I am basically transferring structure by hand.
I am finding the syntax to be difficult to deal with. I have a txt file
with several tables and their attributes from mysql but I am not finding the
exact same thing in ms sql. I wonder if someone can look at the code and
give me a hand. I am using the EM to put this on ms sql online.
Here is an example of one table and the trouble I am having with it. This
is the mysql code to be changed. Please see my comments under this code.
CREATE TABLE room_action (
id varchar(100) NOT NULL default '',
room tinyint(3) unsigned NOT NULL default '0',
user tinyint(3) unsigned NOT NULL default '0',
name varchar(50) NOT NULL default '',
cmd varchar(10) NOT NULL default '',
x mediumint(9) defaulnamet '0',
y mediumint(9) default '0',
msg varchar(255) default NULL,
avatar tinyint(3) unsigned default NULL,
viewed tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;
I am creating the table in the EM. I am assuming that the numbers are the
field length. For example:
room tinyint(3) unsigned NOT NULL default '0'
I am attempting to put 3 in the length but it will only use 1.
I also am confused about unsigned. Also what is TYPE=MyISAM
Can anyone give me a hand?
Thanks
Laura KLaura,
Try this:
CREATE TABLE room_action (
id varchar(100) NOT NULL default '',
room smallint NOT NULL default 0,
userID smallint NOT NULL default 0, --user is a keyword, change to
userID
name varchar(50) NOT NULL default '',
cmd varchar(10) NOT NULL default '',
x integer default 0,
y integer default 0,
msg varchar(255) default NULL,
avatar smallint default NULL, --use a default of 0 instead of null if
possible, will prevent aggreatation warnings
viewed smallint NOT NULL default 0,
PRIMARY KEY (id)
)
Also, please consider removing the default '' on your not null columns. It
is contradictory and a pain in the rear to maintain.
Take some time to review Books On Line, especially data types, as many of
your conversion questions will be answered.
--Morgan
"Laura K" <klkazanAT@.ATcharter.net> wrote in message
news:%23Gz90CleDHA.1824@.TK2MSFTNGP10.phx.gbl...
> Need a little help. I am a newbie with both databases (mysql and mssql).
I
> am transferring from mysql to ms sql. There are very few tables and data.
> I am basically transferring structure by hand.
> I am finding the syntax to be difficult to deal with. I have a txt file
> with several tables and their attributes from mysql but I am not finding
the
> exact same thing in ms sql. I wonder if someone can look at the code and
> give me a hand. I am using the EM to put this on ms sql online.
> Here is an example of one table and the trouble I am having with it. This
> is the mysql code to be changed. Please see my comments under this code.
> CREATE TABLE room_action (
> id varchar(100) NOT NULL default '',
> room tinyint(3) unsigned NOT NULL default '0',
> user tinyint(3) unsigned NOT NULL default '0',
> name varchar(50) NOT NULL default '',
> cmd varchar(10) NOT NULL default '',
> x mediumint(9) defaulnamet '0',
> y mediumint(9) default '0',
> msg varchar(255) default NULL,
> avatar tinyint(3) unsigned default NULL,
> viewed tinyint(3) unsigned NOT NULL default '0',
> PRIMARY KEY (id)
> ) TYPE=MyISAM;
>
> I am creating the table in the EM. I am assuming that the numbers are the
> field length. For example:
> room tinyint(3) unsigned NOT NULL default '0'
> I am attempting to put 3 in the length but it will only use 1.
> I also am confused about unsigned. Also what is TYPE=MyISAM
> Can anyone give me a hand?
> Thanks
> Laura K
>|||Thanks but I don't want to do mySQL. The original program was written for a
mysql database but we want to run it from MS sql. I am writing the tables
by hand in ms sql but I do not know much about the differences.
Laura
"Groucho" <rog11228@.aol.com> wrote in message
news:eFKpYqleDHA.4024@.TK2MSFTNGP11.phx.gbl...
> "Laura K" wrote in message
> > Need a little help. I am a newbie with both databases (mysql and
mssql).
> .
> .
> Don't expect to much help in the way of MySql here.
> It's not considered a *real* rdbms in these parts:)
> There are many differences/similarities between the two.
> You'll get Mysql help on google at:
> mailing.database.mysql
> mailing.database.mysql-win32
> You can also let MySql utilities help you,especially the ones that
> import and export data.Check out:
> SQLyog @.
> www.webyog.com
> and
> MySql GUI tool @.
> www.mysqlgui.net
> Install the MySql odbc driver and you can use the
> Server dts utility to transfer data and see how the
> table structure(s) turn out.
> A type MyISAM is a particular type of MySql table.
> In MySql there are different types of tables you can use.
> This concept does not exist in Server.See the MySql
> docs for details.
> It also matters what version of MySql your working with.
> Check their website for details.(You should be using 4.0.xx).
> If you want subqueries,derived tables and some other
> fancy server like constructs check out 4.1 alpha.
> Using both Server and MySql will probably become
> pretty common as time goes on.You can pick the one
> you want to pay for:).
> RAC v2.2 and QALite for Sql Server released.
> www.rac4sql.net
>
>|||Thanks so much. I am beginning the understand the differences.
I did a search for conversion but all I got all kinds of non helpful things
instead from the search engines. I will take a look at books online.
Thanks again for the detailed help.
Laura
"Morgan" <mfears@.spamcop.net> wrote in message
news:OaAQiGmeDHA.3528@.tk2msftngp13.phx.gbl...
> Laura,
> Try this:
> CREATE TABLE room_action (
> id varchar(100) NOT NULL default '',
> room smallint NOT NULL default 0,
> userID smallint NOT NULL default 0, --user is a keyword, change to
> userID
> name varchar(50) NOT NULL default '',
> cmd varchar(10) NOT NULL default '',
> x integer default 0,
> y integer default 0,
> msg varchar(255) default NULL,
> avatar smallint default NULL, --use a default of 0 instead of null if
> possible, will prevent aggreatation warnings
> viewed smallint NOT NULL default 0,
> PRIMARY KEY (id)
> )
> Also, please consider removing the default '' on your not null columns. It
> is contradictory and a pain in the rear to maintain.
> Take some time to review Books On Line, especially data types, as many of
> your conversion questions will be answered.
> --Morgan
>
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:%23Gz90CleDHA.1824@.TK2MSFTNGP10.phx.gbl...
> > Need a little help. I am a newbie with both databases (mysql and
mssql).
> I
> > am transferring from mysql to ms sql. There are very few tables and
data.
> > I am basically transferring structure by hand.
> >
> > I am finding the syntax to be difficult to deal with. I have a txt file
> > with several tables and their attributes from mysql but I am not finding
> the
> > exact same thing in ms sql. I wonder if someone can look at the code
and
> > give me a hand. I am using the EM to put this on ms sql online.
> >
> > Here is an example of one table and the trouble I am having with it.
This
> > is the mysql code to be changed. Please see my comments under this code.
> >
> > CREATE TABLE room_action (
> > id varchar(100) NOT NULL default '',
> > room tinyint(3) unsigned NOT NULL default '0',
> > user tinyint(3) unsigned NOT NULL default '0',
> > name varchar(50) NOT NULL default '',
> > cmd varchar(10) NOT NULL default '',
> > x mediumint(9) defaulnamet '0',
> > y mediumint(9) default '0',
> > msg varchar(255) default NULL,
> > avatar tinyint(3) unsigned default NULL,
> > viewed tinyint(3) unsigned NOT NULL default '0',
> > PRIMARY KEY (id)
> > ) TYPE=MyISAM;
> >
> >
> > I am creating the table in the EM. I am assuming that the numbers are
the
> > field length. For example:
> > room tinyint(3) unsigned NOT NULL default '0'
> >
> > I am attempting to put 3 in the length but it will only use 1.
> >
> > I also am confused about unsigned. Also what is TYPE=MyISAM
> >
> > Can anyone give me a hand?
> >
> > Thanks
> >
> > Laura K
> >
> >
>

My whole diskspace is used by a .LDF file?

As a newbie I work with MS SQL 2005. On this server I suddently ran
out of diskspace and found out that a .LDF file uses 55GB+ ?!? The
database is just 2-3GB.
I think I have learned that I need to backup the database and this
logfile on a daily basis to free the diskspace (or at least to let it
be reused).
But a this moment....
I don't have enough diskspace to backup the logfile and futher more I
don't have tape or DVD or anything else, that could be usefull for
this purpose.
Can anyone give me a workaround, so I can shrink this logfile?
After that I promise to do a daily backup :-)
Thanks
What recovery mode are you in? Do you ever perform backups?
<finn@.boendergaard-jakobsen.dk> wrote in message
news:1171377134.744446.116340@.q2g2000cwa.googlegro ups.com...
> As a newbie I work with MS SQL 2005. On this server I suddently ran
> out of diskspace and found out that a .LDF file uses 55GB+ ?!? The
> database is just 2-3GB.
> I think I have learned that I need to backup the database and this
> logfile on a daily basis to free the diskspace (or at least to let it
> be reused).
> But a this moment....
> I don't have enough diskspace to backup the logfile and futher more I
> don't have tape or DVD or anything else, that could be usefull for
> this purpose.
> Can anyone give me a workaround, so I can shrink this logfile?
> After that I promise to do a daily backup :-)
> Thanks
>
|||On Feb 13, 8:32 am, f...@.boendergaard-jakobsen.dk wrote:
> As a newbie I work with MS SQL 2005. On this server I suddently ran
> out of diskspace and found out that a .LDF file uses 55GB+ ?!? The
> database is just 2-3GB.
> I think I have learned that I need to backup the database and this
> logfile on a daily basis to free the diskspace (or at least to let it
> be reused).
> But a this moment....
> I don't have enough diskspace to backup the logfile and futher more I
> don't have tape or DVD or anything else, that could be usefull for
> this purpose.
> Can anyone give me a workaround, so I can shrink this logfile?
> After that I promise to do a daily backup :-)
> Thanks
To fix your immediate problem, do the following:
1. Run this command - BACKUP LOG dbname WITH TRUNCATE_ONLY
2. DBCC SHRINKFILE logfilename
3. Do a full database backup - BACKUP DATABASE dbname TO
DISK=backupfilename
Now you need to decide what to do to prevent this from happening
again. Backing up the log file once per day is sort of pointless.
The intent behind transaction log backups is to allow you to restore
to a specific point in time, reducing your chances of data loss.
Backing the log up once per day gives you the potential to lose an
entire day's worth of data - if that risk is acceptable to you, just
forget the log file backups, put the database in Simple mode and do
daily full backups. I would suggest doing log backups AT LEAST once
per hour, we do them every five minutes here. Consider the following
scenario:
- nightly full backup at 9:00pm, no log backups
- database fails at 3:00pm
- only option for recovery is to restore the 9:00pm backup, losing all
work done today
Compare to this scenario:
- nightly full backup at 9:00pm
- log backup every 15 minutes
- database fails at 3:00pm
- recovery of all data up to 2:45pm is possible, by restoring the full
backup (WITH NORECOVERY) followed by each subsequent log file backup.
Which would you prefer?

My whole diskspace is used by a .LDF file?

As a newbie I work with MS SQL 2005. On this server I suddently ran
out of diskspace and found out that a .LDF file uses 55GB+ ?!? The
database is just 2-3GB.
I think I have learned that I need to backup the database and this
logfile on a daily basis to free the diskspace (or at least to let it
be reused).
But a this moment....
I don't have enough diskspace to backup the logfile and futher more I
don't have tape or DVD or anything else, that could be usefull for
this purpose.
Can anyone give me a workaround, so I can shrink this logfile?
After that I promise to do a daily backup :-)
ThanksWhat recovery mode are you in? Do you ever perform backups?
<finn@.boendergaard-jakobsen.dk> wrote in message
news:1171377134.744446.116340@.q2g2000cwa.googlegroups.com...
> As a newbie I work with MS SQL 2005. On this server I suddently ran
> out of diskspace and found out that a .LDF file uses 55GB+ ?!? The
> database is just 2-3GB.
> I think I have learned that I need to backup the database and this
> logfile on a daily basis to free the diskspace (or at least to let it
> be reused).
> But a this moment....
> I don't have enough diskspace to backup the logfile and futher more I
> don't have tape or DVD or anything else, that could be usefull for
> this purpose.
> Can anyone give me a workaround, so I can shrink this logfile?
> After that I promise to do a daily backup :-)
> Thanks
>|||On Feb 13, 8:32 am, f...@.boendergaard-jakobsen.dk wrote:
> As a newbie I work with MS SQL 2005. On this server I suddently ran
> out of diskspace and found out that a .LDF file uses 55GB+ ?!? The
> database is just 2-3GB.
> I think I have learned that I need to backup the database and this
> logfile on a daily basis to free the diskspace (or at least to let it
> be reused).
> But a this moment....
> I don't have enough diskspace to backup the logfile and futher more I
> don't have tape or DVD or anything else, that could be usefull for
> this purpose.
> Can anyone give me a workaround, so I can shrink this logfile?
> After that I promise to do a daily backup :-)
> Thanks
To fix your immediate problem, do the following:
1. Run this command - BACKUP LOG dbname WITH TRUNCATE_ONLY
2. DBCC SHRINKFILE logfilename
3. Do a full database backup - BACKUP DATABASE dbname TO
DISK=backupfilename
Now you need to decide what to do to prevent this from happening
again. Backing up the log file once per day is sort of pointless.
The intent behind transaction log backups is to allow you to restore
to a specific point in time, reducing your chances of data loss.
Backing the log up once per day gives you the potential to lose an
entire day's worth of data - if that risk is acceptable to you, just
forget the log file backups, put the database in Simple mode and do
daily full backups. I would suggest doing log backups AT LEAST once
per hour, we do them every five minutes here. Consider the following
scenario:
- nightly full backup at 9:00pm, no log backups
- database fails at 3:00pm
- only option for recovery is to restore the 9:00pm backup, losing all
work done today
Compare to this scenario:
- nightly full backup at 9:00pm
- log backup every 15 minutes
- database fails at 3:00pm
- recovery of all data up to 2:45pm is possible, by restoring the full
backup (WITH NORECOVERY) followed by each subsequent log file backup.
Which would you prefer?

My whole diskspace is used by a .LDF file?

As a newbie I work with MS SQL 2005. On this server I suddently ran
out of diskspace and found out that a .LDF file uses 55GB+ ?!? The
database is just 2-3GB.
I think I have learned that I need to backup the database and this
logfile on a daily basis to free the diskspace (or at least to let it
be reused).
But a this moment....
I don't have enough diskspace to backup the logfile and futher more I
don't have tape or DVD or anything else, that could be usefull for
this purpose.
Can anyone give me a workaround, so I can shrink this logfile?
After that I promise to do a daily backup :-)
ThanksWhat recovery mode are you in? Do you ever perform backups?
<finn@.boendergaard-jakobsen.dk> wrote in message
news:1171377134.744446.116340@.q2g2000cwa.googlegroups.com...
> As a newbie I work with MS SQL 2005. On this server I suddently ran
> out of diskspace and found out that a .LDF file uses 55GB+ ?!? The
> database is just 2-3GB.
> I think I have learned that I need to backup the database and this
> logfile on a daily basis to free the diskspace (or at least to let it
> be reused).
> But a this moment....
> I don't have enough diskspace to backup the logfile and futher more I
> don't have tape or DVD or anything else, that could be usefull for
> this purpose.
> Can anyone give me a workaround, so I can shrink this logfile?
> After that I promise to do a daily backup :-)
> Thanks
>|||On Feb 13, 8:32 am, f...@.boendergaard-jakobsen.dk wrote:
> As a newbie I work with MS SQL 2005. On this server I suddently ran
> out of diskspace and found out that a .LDF file uses 55GB+ ?!? The
> database is just 2-3GB.
> I think I have learned that I need to backup the database and this
> logfile on a daily basis to free the diskspace (or at least to let it
> be reused).
> But a this moment....
> I don't have enough diskspace to backup the logfile and futher more I
> don't have tape or DVD or anything else, that could be usefull for
> this purpose.
> Can anyone give me a workaround, so I can shrink this logfile?
> After that I promise to do a daily backup :-)
> Thanks
To fix your immediate problem, do the following:
1. Run this command - BACKUP LOG dbname WITH TRUNCATE_ONLY
2. DBCC SHRINKFILE logfilename
3. Do a full database backup - BACKUP DATABASE dbname TO
DISK=backupfilename
Now you need to decide what to do to prevent this from happening
again. Backing up the log file once per day is sort of pointless.
The intent behind transaction log backups is to allow you to restore
to a specific point in time, reducing your chances of data loss.
Backing the log up once per day gives you the potential to lose an
entire day's worth of data - if that risk is acceptable to you, just
forget the log file backups, put the database in Simple mode and do
daily full backups. I would suggest doing log backups AT LEAST once
per hour, we do them every five minutes here. Consider the following
scenario:
- nightly full backup at 9:00pm, no log backups
- database fails at 3:00pm
- only option for recovery is to restore the 9:00pm backup, losing all
work done today
Compare to this scenario:
- nightly full backup at 9:00pm
- log backup every 15 minutes
- database fails at 3:00pm
- recovery of all data up to 2:45pm is possible, by restoring the full
backup (WITH NORECOVERY) followed by each subsequent log file backup.
Which would you prefer?