Export data from MSSQL into MySQL

[Adam Rifat]

Hi All,

In my bid to slow down the Joomla exodus ;-) I am currently porting all my company's data and CMS into Joomla! :-)

I need to export data from MSSQL db. I have an import routine based on a CSV file but I have to handle several large HTML fields. Copying and pasting this from MSSM is no longer an option to generate the CSV. 

I've got no idea how to change the default driver via PHP via the framework. I can see sqlsrv in the system settings via admin but I don't really want to do that globally.

How do I switch to sqlsrv driver via the framework - issue a query and then switch back to mysql? Anyone have any idea/experience?

Thanks in advance,

Adam

[Michael Babker]

Your best bet might be to instantiate a second JDatabase instance instead of using JFactory to get the global config's database object.  Look into JDatabase::getInstance() and use it to start a session with SQL Server to do what you need.

-Michael

Please pardon any errors, this message was sent from my iPhone.

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To view this discussion on the web, visit https://groups.google.com/d/msg/joomla-dev-general/-/m9_BfCJiPmQJ.
To post to this group, send an email to joomla-de...@googlegroups.com.
To unsubscribe from this group, send email to joomla-dev-gene...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/joomla-dev-general?hl=en-GB.

[elin]

So you want to switch to sqlsrv for some tables but not others?

In principle you use the importer and exporter but unfortunately sqlsrv does not have an importer.  Also the exporters don't export data just structure.  I did some research on this a couple of months ago and it seemed like doing the importer/exporter in sqlsrv was a lot more complex but it certainly has the built in capability to read a correctly formatted xml file.

Elin

[Adam Rifat]

Thanks both, 

I got as far as:

$sqlsrv = JDatabaseDriver::getInstance($options);

but the query is failing - I think this is due to the username not being correct though. 

All I really need to do is run a query against the MS SQL db and then export it as a csv. I could run each row through my custom import routine I guess, but really just need to get the query working...

 500 Database sqlsrv_connect failed

:-) 

[Brad Gies]

Do you really need to do this with PHP?

SQLServer Management Studio has all kinds of options to export data, including CSV (with many, many setup options).

Failing that I would get a full version of Navicat (with both SQLServer and MySQL). 

Why put yourself through pain when you can buy tools?

Brad.

On 2012-11-09 1:22 AM, Adam Rifat wrote:

[Adam Rifat]

Initially I was just copying and pasting the result set to a spreadsheet. But for some reason this stopped working when I added a large varchar field to the query. 

Anyway, turns out that if I copy and paste the problem column into the spreadsheet then it works fine. For some reason I can't copy/paste all at once.

I will be very happy when I no longer need to use MS SQL server manager, that's for sure.

Adam