MySQL To SQL Server 2005 Conversion Table
Mar 20, 2007
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.
- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://www.servicerank.com/
View 2 Replies
ADVERTISEMENT
Nov 30, 2005
Hello
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
any help would be appreciated
Thanks
View 2 Replies
View Related
Jan 25, 2007
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?
Rick
View 10 Replies
View Related
Apr 9, 2006
Hello all,
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
View 2 Replies
View Related
Apr 26, 2007
I've been asked to migrate a db in MySQL to SQL Server 2005. Since I'm only familiar with sql server 2000 and before, I dont' know where to begin looking for the information I need.
Can someone point me to what I would search for in SQL Server 05 for this (is there any new features here I should be aware of that might make this easier?)
If anyone has done this, what are the gotcha's or things I should be aware of.
Thanks for any input
View 1 Replies
View Related
Jan 19, 2007
Hello. I have the following problem. I have a database in mysql and i want to migrate in ms sql server 2005. How can i do that? I have downloaded and installed MySQL Connector/ODBC .
View 8 Replies
View Related
Feb 20, 2007
Hi guys,
im trying to develop a web application that uses a backend database, however im not quite sure what db backend to use. What are the diffrence between sql server express and mysql.. which is better to use in terms of small enterprises.? what are their limits..?
thanks you very much
View 4 Replies
View Related
Aug 29, 2007
I am having trouble connecting to a MySQL database in SQL Server 2005. How can this be done in the easiest fashion?
View 7 Replies
View Related
Jul 27, 2007
Can anyone recommend me how I can convert MySQL database to MS SQL server 2005 database? I am new to the SQL server 2005. Is there any ODBC I can use? Thanks in advance
View 3 Replies
View Related
Feb 12, 2007
Good Morning
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
Provider=OleMySql.MySqlSource.1;Persist Security Info=False;User ID=testuser;
Data Source=databridge;Location="";Mode=Read;Trace="""""""""""""""""""""""""""""";
Initial Catalog=riverford_rhdx_20060822
Can any on help me convert this to corrrect syntax for sql stored procedure
sp_addlinkedserver
I've tried this below but it does not work I just get an error saying it can not create an instance of OleMySql.MySqlSource.
I used SQL server management studio to create the linked server then just scripted this out below.
I seem to be missing the user ID, but don't know where to put it in.
EXEC master.dbo.sp_addlinkedserver @server = N'DATABRIDGE_OLEDB', @srvproduct=N'mysql', @provider=N'OleMySql.MySqlSource', @datasrc=N'databridge', @catalog=N'riverford_rhdx_20060822'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'use remote collation', @optvalue=N'false'
Many Thanks
David Hills
View 7 Replies
View Related
Apr 5, 2007
Hi all,I am developing a web application and the back end is MySQL database.
Now I want to shift all the data to SQL Server 2005.
Is there any method to do it?
Thanks
Tomy
View 1 Replies
View Related
Jun 28, 2007
Hello,
Finally making the move to ASP.NET! I've been advised that to begin with it is probably best to start using SQL Server 2005 with ASP.NET. All my sites are currently using MySQL. Can anyone advise a way for me to import all the data from a MySQL database to a SQL Server 2005 database. Apologies if this isn't directly related to ASP.NET but any help would be greatly appreciated.
Thanks
View 2 Replies
View Related
Aug 1, 2007
hey all,
i want to know Equivalent of "LIMIT" of MySQL in SQL Server 2005?
in mysql we can direclty get data using LIMIT clause.
my question is how to do this in sql server 2005?
thanks.
View 6 Replies
View Related
Oct 3, 2007
I have a database that is in MySQL and would like to import it into SQL Server 2005 Express.
Can this be done? Any tutorials anywhere?
Thanks.
View 7 Replies
View Related
Aug 20, 2007
i've got a database stored on my computer using mysql server 2005, and I've just registered for hosting which uses the Plesk control panel.
In plesk I've setup a database and relevant user. What the easiest way of exporting the whole database on my computer and getting it onto my hosting account?
View 5 Replies
View Related
Mar 18, 2006
Normally I'd go and write a bunch of code for doing it manually... But instead of that, maybe there is a smarter way?
What I know is that you can get some kind of dump out of MySQL but I would be very surprised if that could be directly read into SQL Express ??
So if you have a few step guide to migrate from MySQL to SQL Express, I am all ears (and eyes)!
Thanks!
(Yes I am aware there is some DTS thingy for this but could not find it in the Express folders)
View 6 Replies
View Related
Nov 9, 2006
Problem: Moving data from mysql to sql server 2005
I am trying to pull data over from mysql to sql server. First the import wizard greys out so I have to put in 1 query at a time which is pain. and second it does not even work! it takes me through the end of the wizard for me to click finish and then says oops it does not work. there was an error!
Anyway i tried going through the ssis route cuz its going to be a nightly job. i used the ado.net odbc connection. It worked but the performance is really not acceptable. it took 5 mins to import 24000 rows where as dts was taking 1 sec to do this. i wish i could use the native mysql odbc 3.51 connector and import. can some one give me step by step instructions on how to do that ?
I hear someone mentioned of using excute sql task which can use mysql odbc 3.51 driver. but since i am new how do i get it to work. say for example in the excute sql task i run a statement like select * from addr. then what?
cuz eventually i want the result to be saved in a sql server table called addr. How can i get the result from that excute sql task and put it inside of an addr table in sql server. should i save the result to a variable of type object. but then how do i get the data from object and tell sql server in the designer that the result contains these columns and it needs to map to these columns in the addr table of sql server.
Very confused. i wish the first option would have given me results which an enterprise ETL gives. but apparently it is too slow that it wont be acceptable in a production envrioment. when i will have millions of rows coming in .
Please anyone can help me in this.
Thanks for the help!
View 1 Replies
View Related
Aug 1, 2007
hey all,
i want to know Equivalent of Load Data Infile of MySQL in SQL Server 2005?
in mysql we can direclty load file with Load Data infile query.
my question is how to do this in sql server 2005?
thanks.
View 6 Replies
View Related
Feb 19, 2008
Hi Guys,
well as you can see from my thread SQLServer is new to me i am used to mysql and i c'ant find the equivalent anywhere on the net... Seams easy enough but i have been at it for 6 hrs and i give up... I am just making a quick database view tool. So please tell me.
How do i load the all tables of a database via an sql command?
Since it took me a while to find this i thought migth as well slap ip somewhere so here is the command to load all data bases... In any case worked for me so...
- > select * from master.dbo.sysdatabases;
And where in the ... can you find a reference to all sql server commands? ... Please.
Thanks for your help
View 4 Replies
View Related
Jan 22, 2008
HI All,
I want to pull data from mysql dbms to sql servr 2005, i have wrote the following codes but it takes more than an hour and half which is not visible. Thus is there any consideration to consider and reduced time it takes. For your information i am going to use SSIS packags, there is no any tranformation, it is direct dump.
Here is the code i am using,
SELECT *
FROM
OPENQUERY (Server_1,'
SELECT
t3.Column11 as Column1,
Column12 as Column2,
Column13 as Column3,
Column14 as Column4,
Column15 as Column5,
Column16 as Column6,
Column17 as Column7,
Column18/1000 as Column8
FROM
table1 t1
INNER JOIN
table2 t2
ON t1.ColumnId = t2.columnID
INNER JOIN
Table3 t3
ON t2.columnId = t3.columnID
WHERE t1.Column4 > Sometime ')
View 11 Replies
View Related
Dec 29, 2003
Can anybody tell how to import a table with the text column from SQL Server 2000 to MySQL 4.0.17?
I tried this using ODBC connection but got an error message saying, "Query-based Insertion or updating of BLOB values is not supported".
View 9 Replies
View Related
Jan 10, 2007
Hi,
In MySQL,
"GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.1' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;"
This gives the permission to 'root' user from '192.168.1.1'. Another user or another ip couldn't connect to this server.
In SQL Server 2005 Express, can I implement like above query statement?
View 1 Replies
View Related
Oct 27, 2007
I'm trying to use linked server to import big amount of data(around 9 million rows) from mysql to a sql 2005 table.
I plan to create a stored procedure and use job agent to run it at off-peak time everyday. The SP is like:
.....
Truncate table sqltblA;
Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB')
.....
But when I tried to CREATE the above SP from management studio, seems the sql server was trying to get all data from table mytblB and ended up with failure after several minutes hang. The error message is sth. like "running out memeory". BTW, during that several minutes hang, I can't query mytblB using mysql's tool as well. it seems to me that table got locked.
However if i try to change the returned result to 100,000 rows by changing script to
Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB Limit 100000')
The SP could be created without any problem and running the SP could get the data in table sqltblA.
But that's not what I want, I need all rows instead of a certain amount of rows.
Does anyone know what's going on there when I try to CREATE that SP and any solution to it?
Plus I tried to use SSIS as well. When I'm configuring the DataReader source, which uses ADO.NET's ODBC option to connect to mysql, if its sqlcommand is "select * from mytblB" without the Limit key word, then the configuration page will hang there as well and table mytblB is not accessible by using mysql tool. I'm just sick of wasting time on that and that's why I chose to use SP istead of SSIS. But same result. :-(
View 1 Replies
View Related
Apr 25, 2008
Hello Everyone, thank you for taking the time to read my post. I'm creating a view in SQL Server 2005 to base a report on Crystal Reports XI. I've been trying to figure out how I can convert a date field in the format YYYYMMDD to MM/DD/YYYY. I'm not quite sure about the steps I need to take to accomplish this since I'm pretty new to this. The date is stored as an Int on the database, and I've tried converting it directly to the DATETIME data type like this: CONVERT(DATETIME, datefield,101) but I'm getting an error saying illegal data type conversion. Thanks a lot for your help, I really appreciate it.
MS
View 7 Replies
View Related
Mar 29, 2006
Hi all
I am migrating some DTS2000 packages to SSIS via the migration wizard. However there is an activex script in each DTS that connects to an AS400 to retrieve record count for integrity checking. Under SQL server 2000 this worked fine but now it errors with "The OLE DB provider "MSADSQL" has not been registered" how do i get this to work i suspect its because this is a 32 bit dll and my new system is a 64bit sql server on windows 2003. The odbc driver for the as400 has been installed also. The script is as follows:
dim cn 'sql connection
dim rs 'sql recordset for the insertion of new as400 records
dim insertstr 'sql insert string
dim rs1 'sql recordset for the insertion of new as400 records
dim insertstr1 'sql insert string
set cn = createobject("ADODB.Connection")
set rs = createobject("ADODB.Recordset")
Function Main()
'set up connection for sql
cn.provider="sqloledb"
cn.commandtimeout = 3600 'timeout in seconds
cn.open %sqlserver05%, %user%, %password%
'populate temporary table in sql with recordset from as400 odbc
insertstr1 = "Insert into db.dbo.%table% select 'table name',a.* from openrowset ('MSDASQL','DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=%system name%;UID=%username%;PWD=%password%','SELECT count(*) from %table%') as a"
set rs1 = cn.execute (insertstr1)
'close all objects
cn.close
Main = DTSTaskExecResult_Success
End Function
Does anyone know how i change this to work under sql server 2005. i need to resolve this quickly in order to deliver a project on time. Many thanks
Chris
View 6 Replies
View Related
Aug 1, 2006
hello friends,
i am facing a problem in SQL Server 2005 Express Edition. The database created in the SQL Server 2005 Express is when opened in SQL Server 2000, it changes the data type of all the columns of all the table to text type. can anybody help me?
View 4 Replies
View Related
Nov 30, 2006
We downloaded and installed the trial software of SQL Server 2005 Enterprise edition a month ago. We have purchased a fully licensed version of SQL Server 2005 Standard Edition and would like to apply the licensed version to our workstation clients since their SQL software will eventually expire. Our SQL Server is not an issue since we purchased a new Server and installed the new licensed version of SQL on it.
Is there an easy way to accomplish this or is an uninstall & reinstall of SQL on every workstation required?
Thanks,
ChrisB
View 1 Replies
View Related
Mar 3, 2005
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.
View 7 Replies
View Related
Sep 26, 2007
I have a SQL Server 2005 and am trying to create an OPENDATASOURCE with MYSQL.
I downloaded the MYSQL 3.51 driver and installed it on the SQL Server, but am unable to connect. I keep getting
the OLE DB provider "MySQLProv" has not been registered.
Any help would be greatly appreciated.
Thanks
Susan
View 1 Replies
View Related
Sep 8, 2007
I Have developed a windows application with an SQL server 2005 database.
Now I want to publish that application BUT I want to make that database an SQL CE one.
In other words, I want to make the users able to download the application with the database without having to download SQL EXPRESS from the net or so....But instead have the Database embedded with the instllation package together with the SQL CE.
1. What is the best way i can transfere the data (Tables and their data) from ordinary SQL server 2005 .mdf files to some place where it can work with SQL CE ?
2. How to embedd the data base and the SQL CE within the installtion package ?
View 1 Replies
View Related
Nov 5, 2007
Hi!
How can i convert this code to work with MsSQL 2005?
/Tomas
Partial Class skaalb
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim strConnectionString As String
Dim strQuery As String
Dim MyConnection As OdbcConnection
Dim myCommand As OdbcCommand
Dim path As String = Server.MapPath("~/album") & "/"
Dim albName As String = Trim(Replace(txtAlbum.Text, "'", "''"))
Dim folderName As String = Trim(Replace(txtAlbum.Text, "'", "''"))
folderName = Replace(folderName, " ", "_")
Try
If Not My.Computer.FileSystem.DirectoryExists(path & folderName) Then
My.Computer.FileSystem.CreateDirectory(path & folderName)
labelStatus.Text = "Folder <b>" & folderName & "</b> created!"
Dim Beskrivning As String = Trim(Replace(txtBeskrivning.Text, "'", "''"))
strConnectionString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=xxxxxxxx; DATABASE=xxxxxxx; UID=xxxxxxxx; PASSWORD=xxxxxxxxx; OPTION=3"
MyConnection = New OdbcConnection(strConnectionString)
MyConnection.Open()
strQuery = "INSERT INTO tbl_albumet(alb_Namn, alb_Beskrivning, alb_Mapp) VALUES (?, ?, ?)"
myCommand = New OdbcCommand(strQuery, MyConnection)
myCommand.Parameters.AddWithValue("?", albName)
myCommand.Parameters.AddWithValue("?", Beskrivning)
myCommand.Parameters.AddWithValue("?", folderName)
myCommand.ExecuteNonQuery()
MyConnection.Close()
Else
labelStatus.Text = "Folder excist, pick another name!"
End If
Catch ex As Exception
labelStatus.Text = "Unable to create folder!"
End Try
End Sub
End Class
View 1 Replies
View Related
Jul 18, 2006
Hi,
Posted this at the tail end of another thread but that one appears to have died. Thought I'd try again since my question was a little distinct from that one anyway, but apologies if I've committed forum sacrilege.
I am having a heck of a time trying to migrate a fairly simple MySQL DB to SQL Server I am using SQL Server 2005 Standard Edition SP1.
I tried following MS' instructions in this article:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx
hoping that what worked for 2000 would work for 2005, since the principles seem sound. No luck. I can create the ODBC link, but SQL Management Studio, the '.NET provider for ODBC' option doesn't give me the choice to copy tables - only to write a query.
I tried making sense of the on-line help and was able to create the ODBC connection in the 'Business Intelligence Development Studio' but I have no idea what I am looking at with respect to creating control & data flow in an SSIS package. All I know is that I could do what I am trying to do in SQL Server 2000 quite easily - the MySQL database in question is quite simple (but not small). I am pretty amateurish when it comes to SQL administration, but this seems to me unnecessarily difficult.
I tried the MySQL OLE connector from sourceforge and was able to properly set up a test connection and get the 'copy tables' option, but then when I hit 'next' I get:
MYSqlProv 3.9 failed with no error message available, result code E_ABORT(0x80004004) (System.Data)
Am I missing some stupendously easy way around this, or did I torpedo this client when I promoted moving them from MySQL to MS SQL Server on account of my not being a full-time SQL Server DBA who knows how to use the Business Intelligence Development Studio?
View 5 Replies
View Related
May 19, 2008
Hello
Hope this is an easy one..
Can i install and run SQL Express 2005 sp2 without interrupt the mysql thingy i got on my webserver???
Its on a Win XP sp 2
//Cheers
Jim
View 4 Replies
View Related
