incorrect syntax near 'DATA'

ttoomey · December 14th, 2006, 01:00 PM

I just moved an asp/mysql site over to a new windows server and converted to an mssql database. I have changed the paths in my files and reuploaded all necessary database info and all appears to be working fine, except for when I try to execute a few certain SQL statements. Below is the error I get:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'DATA'.

/ap2336519589/admin/report_upload_action.asp, line 144

Now I know this means something is wrong with my MSSQL statement in my asp so here is my asp (I would like to point out this was in perfect working order on the previous server)

Code:

SQL="LOAD DATA INFILE 'd:/inetpub/virtual/ap2336519589/WWWROOT/data/other/031990.TXT' INTO TABLE weekly_report FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '""' LINES TERMINATED BY '\r\n'"
Set RSSQL = MyConn.Execute(SQL)

That .TXT file exists as far as I can because I type in the http address of the file and it works just fine.

Any help is appreciated.

dparsons · December 14th, 2006, 02:10 PM

I think load data infile can only be executed on the MySQL server itself and not remotely.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

ttoomey · December 14th, 2006, 03:27 PM

Is there any equivalent command that I can use or a workaround for this?

dparsons · December 14th, 2006, 04:35 PM

No, i don't believe so. Unlike is MSSQL server where you can call a DTS via a Stored Procedure and import a text file that way, i know of no way to do the equivelant in MySQL. Check out www.mysql.com and see if they can help you further.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

ttoomey · December 14th, 2006, 04:41 PM

BULK INSERT

http://msdn.microsoft.com/library/de...ba-bz_4fec.asp

Is that what I'm looking for?

dparsons · December 15th, 2006, 10:09 AM

You can not use MS T-SQL code with MySQL. While both MSSQL Server and MySQL understand SQL, they both implement it differently. And I have never used the BULK INSERT method to insert vast amounts of data into a datatable, I use DTS Process's to pull in a text file and transform the data as needed and send that data off to the necessary tables.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature