SQL Server to MySQL thru PHP 
Author Message
 SQL Server to MySQL thru PHP

Hi
Can anyone offer any suggestions to this task I have been given. The
situation is this, I have a apache web server running PHP3, on a
seperate server there is MySQL. The web server has read access to a
3rd parties SQL Server (Due to firewall restrictions the to DB servers
can't be connected).

On a nightly basis I have to download a database (Structure & Data)
from the SQL Server and upload it in to the MySQL server. I have to
assume that the structure of the Database may change, so I have to
bring everything across.

I can see that using Dynamic SQL queries I can get the data across,
but I am unsure how to get the structure.

 Anybody got any ideas?

Thanks
Rob



Sat, 14 May 2005 04:57:15 GMT  
 SQL Server to MySQL thru PHP


Quote:
> I can see that using Dynamic SQL queries I can get the data across,
> but I am unsure how to get the structure.

Hmmm, can you try using Microsoft's DTS?  It can export/import tables based
on rules you specify.  You maybe able to create a DTS package on the remote
server and use that to export the data successfuly to mySQL.

--
  __   ______    

/____/\/_/\_\/ | Please delete "REMOVE" from the e-mail address
\____\/\_\/    | when replying.



Sat, 14 May 2005 06:35:57 GMT  
 SQL Server to MySQL thru PHP

Quote:

>On a nightly basis I have to download a database (Structure & Data)
>from the SQL Server and upload it in to the MySQL server. I have to
>assume that the structure of the Database may change, so I have to
>bring everything across.

>I can see that using Dynamic SQL queries I can get the data across,
>but I am unsure how to get the structure.

Warning: some REALLY ugly-ass SQL follows.

The Transact-SQL equivalent to MySQL's "SHOW TABLES":

  "select name from sysobjects where type='U' or type='V' and (name
   not in ('sysallocations','syscolumns','syscomments','sysdepends',
   'sysfilegroups','sysfiles','sysfiles1','sysforeignkeys',
   'sysfulltextcatalogs','sysindexes','sysindexkeys','sysmembers',
   'sysobjects','sy{*filter*}issions','sysprotects','sysreferences',
   'systypes','sysusers','sysalternates','sysconstraints',
   'syssegments','REFERENTIAL_CONSTRAINTS','CHECK_CONSTRAINTS',
   'CONSTRAINT_TABLE_USAGE','CONSTRAINT_COLUMN_USAGE','VIEWS',
   'VIEW_TABLE_USAGE','VIEW_COLUMN_USAGE','SCHEMATA','TABLES',
   'TABLE_CONSTRAINTS','TABLE_PRIVILEGES','COLUMNS',
   'COLUMN_DOMAIN_USAGE','COLUMN_PRIVILEGES','DOMAINS',
   'DOMAIN_CONSTRAINTS','KEY_COLUMN_USAGE'))"

The Transact-SQL equivalent to MySQL's "SHOW COLUMNS":

  "select c.name,t.name,c.length from syscolumns c join systypes
   t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id
   where o.name='%s'"

Those code snippets were taken from the ADODB mssql driver
( http://www.*-*-*.com/ ).

Good luck,

Dianne



Sat, 14 May 2005 07:52:19 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Newbie needs help setting up user MySQL thru PHP

2. SQL injection - PHP, MySQL, magic_quotes = on

3. PHP, mySQL and connection to a SQL Database

4. Using remote MySQL server with PHP via HTTP

5. MySQL/PHP with redundant slave servers

6. PHP can't connect to MySQL server

7. PHP, MYSQL, IIS5 and Win2k Server in a Corporate Environment

8. ColdFusion, MySQL, SQL Server, ASP.Net Hosting

9. MS SQL SERVER 2000 + PHP + IIS 5.0

10. PHP and MS SQL Server

11. php mysql sample code php shareware like KB knowledgebase

12. PHP+MySQL vs. PHP+PostgreSQL

 

 
Powered by phpBB® Forum Software