|
Subject:
|
incorrect syntax near 'DATA'
|
|
Posted By:
|
ttoomey
|
Post Date:
|
12/14/2006 12:00:51 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)
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.
|
|
Reply By:
|
dparsons
|
Reply Date:
|
12/14/2006 1:10:03 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
|
|
Reply By:
|
ttoomey
|
Reply Date:
|
12/14/2006 2:27:06 PM
|
Is there any equivalent command that I can use or a workaround for this?
|
|
Reply By:
|
dparsons
|
Reply Date:
|
12/14/2006 3:35:25 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
|
|
Reply By:
|
ttoomey
|
Reply Date:
|
12/14/2006 3:41:47 PM
|
BULK INSERT
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp
Is that what I'm looking for?
|
|
Reply By:
|
dparsons
|
Reply Date:
|
12/15/2006 9:09:17 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
|
