I am in the process of convering my code from using MySQL to MS SQL
I have queries of the form:
Code:
INSERT INTO <table> (params) VALUES (values)
ON DUPLICATE KEY UPDATE <param1>=<value1>, <param1>=<value1>... <param n>=<value n>
My question is:
Is there an equivalent syntax for such query in MS SQL?
I am looking for a syntax that does this in a single atomic query.
I can always break it down to 2 queries (select + insert/update), but I will do so only after I know there is no equivalent way for it in MS SQL
Also, is there an equivalent syntax for LIMIT X,Y in MS SQL
for quries in the form:
Code:
SELECT <params>
FROM <tables>
WHERE <conditions>
LIMIT X, Y
Working on trying to support mutliple backend db's against a custom VB6 app. Right now the db is SqlExpress. It's relatively uncomplicated and I just want to move table structures and data over. Using the MySql Migration tool, I am able to authenticate as SA to a server-based instance of SqlExpress, however, only the MS-supplied databases appear as available databases; my databases don't appear. Can't seem to authenticate at all to any local instance of the database, either. Anyone done any successful migrations to MySql through their migration tool?
For those of you who are able to assist, I'd like to thank you in advance right now. This is a pretty big problem for me.
First let me setup what it is I'm trying to do before I describe the problem in detail. This is part of a semester-long Software Engineering project for my SE class at school. Now, I've got a month for this project, but the database part of it is something I'm trying to get done by this week. Our team is using GoDaddy to host our account and so to simplify our problems with using Visual Studio and MySQL we're switching over to MS SQL, where our code works.
Okay, onto the specifics: I'm used to using a database modeling program called DBDesigner4. Unfortunately, they're support forums have been closed down (fabFORCE.net) and I've spoken with a GoDaddy representative and their servers don't allow me to directly connect with my modeling program and create the schema/tables using the program. However, the program does export to a MySQL table creation script or even an MDB XML file (MSAcess I believe). I'm trying to hand convert the creation script over to MS SQL syntax and I'm having alot of problems (please bear with me, I've never messed with MS SQL before).
For your reference, I'm going to display the actual MySQL script here (just skip this section if you'd like to see the actual problem below):
Code:
CREATE TABLE Addresses ( address_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, address_line1 VARCHAR(30) NOT NULL, address_line2 VARCHAR(30) NULL, address_city VARCHAR(30) NOT NULL, address_state VARCHAR(2) NOT NULL, address_zip MEDIUMINT UNSIGNED NOT NULL, PRIMARY KEY(address_id) ) TYPE=InnoDB;
CREATE TABLE Broadcasts ( broadcast_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, address_id INTEGER UNSIGNED NOT NULL, broadcast_title VARCHAR(30) NOT NULL, broadcast_message VARCHAR(255) NOT NULL, broadcast_image VARCHAR(255) NOT NULL, broadcast_date_posted DATETIME NOT NULL, broadcast_date_expires DATETIME NOT NULL, broadcast_date_archived DATETIME NULL, broadcast_clicks BIGINT NOT NULL DEFAULT 0, broadcast_type ENUM('promo', 'announcement', 'other') NOT NULL DEFAULT 'promo', broadcast_link VARCHAR(255) NULL, PRIMARY KEY(broadcast_id), INDEX Broadcasts_FKIndex1(address_id) ) TYPE=InnoDB;
CREATE TABLE Classes ( user_id INTEGER UNSIGNED NOT NULL, subject_id INTEGER UNSIGNED NOT NULL, class_grade INTEGER UNSIGNED NOT NULL DEFAULT 100, class_tardies INTEGER UNSIGNED NOT NULL DEFAULT 0, class_absences INTEGER UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY(user_id, subject_id), INDEX Users_has_Sections_FKIndex1(user_id), INDEX Classes_FKIndex2(subject_id) );
CREATE TABLE Classes_have_Grades ( subject_id INTEGER UNSIGNED NOT NULL, user_id INTEGER UNSIGNED NOT NULL, grade_id INTEGER UNSIGNED NOT NULL, PRIMARY KEY(subject_id, user_id, grade_id), INDEX Grades_has_Classes_FKIndex1(grade_id), INDEX Grades_has_Classes_FKIndex2(user_id, subject_id) );
CREATE TABLE Grades ( grade_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, grade INTEGER UNSIGNED NOT NULL, grade_type ENUM('h', 'q', 't') NOT NULL, grade_desc VARCHAR(50) NOT NULL, PRIMARY KEY(grade_id) );
CREATE TABLE Locations ( loc_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, address_id INTEGER UNSIGNED NOT NULL, location_id VARCHAR(20) NOT NULL, location_name VARCHAR(25) NOT NULL, PRIMARY KEY(loc_id), INDEX Locations_FKIndex1(address_id) ) TYPE=InnoDB;
CREATE TABLE Passports ( passport_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, user_id INTEGER UNSIGNED NOT NULL, location_id INTEGER UNSIGNED NOT NULL, passport_user VARCHAR(7) NOT NULL, passport_code VARCHAR(32) NOT NULL, passport_access ENUM('student', 'faculty', 'admin') NOT NULL DEFAULT 'student', passport_tries TINYINT UNSIGNED NOT NULL DEFAULT 0, passport_locked BOOL NOT NULL DEFAULT 'false', passport_lastaccess DATETIME NULL, PRIMARY KEY(passport_id), INDEX Passports_FKIndex1(location_id), INDEX Passports_FKIndex2(user_id) ) TYPE=InnoDB;
CREATE TABLE Subjects ( subject_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, subject_name VARCHAR(50) NOT NULL, subject_offered BOOL NOT NULL, subject_grade ENUM('1', '2', '3', '4', '5', '6') NOT NULL, PRIMARY KEY(subject_id) ) TYPE=InnoDB;
CREATE TABLE Users ( user_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, address_id INTEGER UNSIGNED NOT NULL, user_fname VARCHAR(20) NOT NULL, user_mletter VARCHAR(1) NULL, user_lname VARCHAR(20) NOT NULL, user_grade FLOAT NOT NULL, user_type ENUM('student', 'faculty') NOT NULL DEFAULT 'student', user_balance FLOAT NOT NULL, user_phone CHAR(15) NOT NULL, user_email VARCHAR(50) NOT NULL, PRIMARY KEY(user_id), INDEX Users_FKIndex1(address_id) ) TYPE=InnoDB;
That script runs just fine on MySQL and generates my application's tables with the correct relationships intact, indexes, etc.
However, as I said, I need this setup in MS SQL with a few additional checks and I've hand converted most of this, not knowing how to properly maintain primary keys or foreign keys:
Code:
CREATE TABLE Addresses ( address_id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
address_line1 VARCHAR(30) NOT NULL,
address_line2 VARCHAR(30) NULL,
address_city VARCHAR(30) NOT NULL,
address_state VARCHAR(2) NOT NULL,
address_zip ZIPCODE CONSTRAINT CK_address_zip CHECK (address_zip LIKE '[0-9][0-9][0-9][0-9][0-9] ') )
CREATE TABLE Broadcasts ( broadcast_id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
address_id INT NOT NULL,
broadcast_title VARCHAR(30) NOT NULL,
broadcast_message VARCHAR(255) NOT NULL,
broadcast_image VARCHAR(255) NOT NULL,
broadcast_date_posted DATETIME NOT NULL,
broadcast_date_expires DATETIME NOT NULL,
broadcast_date_archived DATETIME NULL,
broadcast_clicks INT NOT NULL DEFAULT 0,
broadcast_type CHAR(1) NOT NULL DEFAULT(‘a’),
broadcast_link VARCHAR(255) NULL )
CREATE TABLE Classes ( user_id INT PRIMARY KEY NOT NULL,
subject_id INT NOT NULL,
class_grade INT NOT NULL DEFAULT 100,
class_tardies INT NOT NULL DEFAULT 0,
class_absences INT NOT NULL DEFAULT 0 )
CREATE TABLE Classes_have_Grades ( subject_id INT NOT NULL,
user_id INT PRIMARY KEY NOT NULL,
grade_id INT NOT NULL )
CREATE TABLE Grades ( grade_id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
Grade INT NOT NULL,
grade_type CHAR(1) NOT NULL DEFAULT(‘h’),
grade_desc VARCHAR(50) NOT NULL )
CREATE TABLE Locations ( loc_id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
address_id INT NOT NULL,
location_id VARCHAR(20) NOT NULL,
location_name VARCHAR(25) NOT NULL )
CREATE TABLE Passports ( passport_id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
user_id USERID NOT NULL CONSTRAINT CK_user_id CHECK (user_id LIKE '[A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9]'),
location_id INT NOT NULL,
passport_user VARCHAR(7) NOT NULL,
passport_code VARCHAR(32) NOT NULL,
passport_access CHAR(1) NOT NULL DEFAULT(‘s’),
passport_tries TINYINT NOT NULL DEFAULT(0),
passport_locked BOOL NOT NULL DEFAULT(false),
passport_lastaccess DATETIME NULL )
CREATE TABLE Subjects ( subject_id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
subject_name VARCHAR(50) NOT NULL,
subject_offered BOOL NOT NULL,
subject_grade SMALLINT NOT NULL DEFAULT 6 )
CREATE TABLE Users ( user_id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
address_id INT NOT NULL,
user_fname VARCHAR(20) NOT NULL,
user_mletter CHAR(1) NULL,
user_lname VARCHAR(20) NOT NULL,
user_grade FLOAT NOT NULL,
user_type CHAR(1) NOT NULL DEFAULT(‘s’),
user_balance FLOAT NOT NULL,
user_phone CHAR(15) NOT NULL,
user_email VARCHAR(50) NOT NULL )
Unfortunately, Query Analyzer seems to have a problem and says I have an error in my syntax (on line 74) near "'".
I've googled around and found out a couple of things: TSQL doesn't support enumerations (that I'm aware of - therefore I converted my enum fields to CHAR(1)s or INTs) and that the single quotes surrounding default values is the proper way to do DEFAULT values. I can't figure out for the life of me what's wrong with my syntax and I've been going at this for about 4 hours now.
If you can help me out by explaining how I should properly do the PKs/FKs (I believe there's a keyword REFERENCE(field1, field2) for FKs, but I'm not sure where to place it, etc.) I'll go through the SQL script and try to implement it, but I have a feeling this is going to be a long thread if I do get someone willing to help.
Thanks to everyone who took the time to read this, Ahad L. Amdani
Does anyone know of a reference site where I can find a reference table to get a better idea of data type conversions that I should be using?
I have a MySQL 5.0 database which had a lot of tables (mostly empty) that I already have gotten transferred to SQL Server 2005. However, I am suspicious of some of the data type conversions that SQL Server did.
I would really like a good web site to bookmark to help me with this if there is such a reference. Can anyone help?
If not, the most specific example I have right now is a MySQL column that is expecting to accept currency and the MySQL data type is "Double". SQL Server 2005 translated this as a "float" data type. I normally use a "decimal" data type.
Has anyone successfully used cherry's oledb provider for MYSQL to create a linked server from MS SQLserver 2005 to a Linux red hat platform running MYSQL.
I can not get it to work.
I've created a UDL which tests fine. it looks like this
[oledb]
; Everything after this line is an OLE DB initstring
I have an internal Project Management and Scheduling app that I wrote internally for my company. It was written to use MySQL running on a Debian server, but I am going to move it to SQL Server 2000 and integrate it with our Accounting software. The part I am having trouble with is the user login portion. I previously used this:
PHP Code:
$sql = "SELECT * FROM users WHERE username = "$username" AND user_password = password("$password")";
Apparently the password() function is not available when accessing SQL Server via ODBC. Is there an equivalent function I could use isntead so the passwords arent plaintext in the database? I only have 15 people using the system so a blank pwd reset wouldn't be too much trouble.
I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".
The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.
If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.
I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.
Hello,I'd noticed when looking at the prices of hosting ASP websites that it was much cheaper if you useda mysql database instead of mssql so this is the way that I coded everything. I've been using the logincontrol too though so does this mean... 1) that I *need* a mssql database?2) that this database will take up my 1 allocated mssql database with the webhost?3) can I use mysql in the same website; it seems to be working ok with the developer server... Many thanks folks,Adam
HiHow is SQL Server 2000 better than MySQL? I need to convince my boss as weare contemplating doing a website with either SQL Server 2000 or MySQL asbackend.ThanksRegards
Hi all, i've just set up an mysql server on my computer and now i want toconnect to a mysql database with an ASP page but i can't find the correctconnection string. I always get an error that the datasource can not befound.ConnString ="Driver={MySQL};SERVER=mysqlhost;DATABASE=myDB;UID= userID;PASSWORD=pasword"What's wrong with it?
hellosomeone could tell me how could I insert SQL statement in a C++ code ?If you know online documents easy to read about this subject, it will bevery welcome(in english or in french)..Thanks
hola! alguno sabe como uso el top en el mysql??? debo hacer lasiguiente consulta y no le gusta al mysqlSELECT TOP 10 * From ControlDeCaja WHERE CodUsuario = "xxx"alguien sabe? Gracias de todas maneras!!
im trying to convert this table for a friend 1 CREATE TABLE adminmessage ( 2 adminmessageid INT UNSIGNED NOT NULL AUTO_INCREMENT, 3 varname varchar(250) NOT NULL DEFAULT '', 4 dismissable SMALLINT UNSIGNED NOT NULL DEFAULT '0', 5 script varchar(50) NOT NULL DEFAULT '', 6 action varchar(20) NOT NULL DEFAULT '', 7 execurl mediumtext NOT NULL, 8 method enum('get','post') NOT NULL DEFAULT 'post', 9 dateline INT UNSIGNED NOT NULL DEFAULT '0', 10 status enum('undone','done','dismissed') NOT NULL default 'undone', 11 statususerid INT UNSIGNED NOT NULL DEFAULT '0', 12 PRIMARY KEY (adminmessageid), 13 KEY script_action (script, action), 14 KEY varname (varname) 15 ) and was wondering how to do it when mssql doesn't have enums :(
Hello all, I spent the last two days trying to find an application that can export a mySQL database into msSQL syntax so that I can then use that to just create my msSQL database. I have had no such luck, though I could find a bunch to do msSQL to mySQL. Please let me know if anyone has one or knows of one that works because I really do not want to hand port my 70 table database from mySQL to msSQL Thanks in advance, Anthony F Greco
i have dumped a mysql database to my my documents folder on my server, how can i convert this to a mssql database.
the database came from a site that was in php and of course using mysql, the database has all the users logon information and other information in there personal profile. i would like to convert this to a mssql database and connect it to the new site i am doing in .net/C#
Hi, I've been coding in C#/ASP.NET for a little while now, but I've only ever used MS SQL.I'm starting a new project at work soon, and our host provides free MySQL databases but charges for MS SQL databases. What are the pros/cons for each of these databases, and how much conversion would be required in an existing C#/ASP.NET application if I wanted to change from MS SQL to MySQL? I assume the syntax/commands to operate a C# app with MS SQL are different to the ones required to communicate with a MySQL database? I'll probably stick with MS SQL because it's what I know, but I'd like to get familiar with MySQL as it might come in handy one day.Thanks, any information is greatly appreciated.
hi.. may i know how to convert from mysql to microsoft sql server ?? is it very troublesome ?? coz its quite a big project.. i need to convert frm mysql to sql.. is there any example or reference? thanks
I just started developing my site locally using SQL Server and am planning to move it to our work's MySQL server. Is there anything that I should be aware of before I continue any furthur? Will I lose any data or is it even an option to move from SQL Server to My SQL???
I am working for a client who needs to move database from MySQL application to MS SQL SERVER 7.0. Is there anybody who has done it before? I need some help about how to load data from one system to another system.
I was wondering if I could get some assiatance on a matter:
I have a userdatabase in flatfile format. The user info is divided into many files:
First the main file where all the usernames are listed in one column. Then one separeate file for each user, named [username].dat containing their userinfo with encrypted password.
Is there a way I can import all these files (almost 300 of them) into a single MySQL table? If so, could someone please explain this to me in easy terms? Thanks.
Hello there... Well i want to ask that.... is it posible to Convert MS Sql server Tables/Views + Database to MySql server.....? or is there any software which can automatically convert MS Sql Server database into MySql database. I'll be REALLY GLAD if someone can please help me asap... take care... Regards Nabeel Qaisar
I have been running Apache 2.2 and PHP 5.0 with MySQL 5.0 on my XP system. I installed MS SQL today, an I'm unable to connect to the MS SQL server through PHP. When I installed MS SQL I:
added extension=php_mssql.dll to php.ini, and I copied ntwdblib.dll into the windows/system32 directory. I still can't access the server.
The server is running, when I start Windows. I can open the SQL Server Service Manager, and it shows Server: XP-LINUX, Services: SQL Server. I can also open the Web Data Administrator tool at: http://localhost:8080/webadmin/default.aspx, and work with databases. I can open it on port 80, because Apache is using that port.
My friend has a business website http://www.autonetworkinc.com he is concerned about his hosting. He's wonder if it would be a smart move to have his databases converted from FoxPro to something else. I'm wondering if it would be best to have that foxpro converted to mysql (or any other database program i.e. Cache, or something else) or is foxpro where he should stay?
Hi all, via ODBC, how to convert the command 'SHOW COLUMNS FROM ...' from MySQL to SQL Server? Is there any document explaining the differences between those DBs?
I was asked to program a site in coldfusion using mssql, but i have only used mysql in the past. i heard mssql has all the functions mysql has, but mysql does not have everything mssql does.. so if i programmed it in mysql, it should work? right? is there a free download to test mssql or only a buy version?
