Migrating from MySQL to MS SQL

Question

I have a PHP (CODEIGNITER) application which is generally deployed on Apache/MySQL combination. I recently deployed it on IIS8 and MS SQL 11.0.2100.60

I migrated the tables and data by using ODBC connection to migrate to Access database and then again another ODBC connection to migrate to MS SQL. I modified the configurations of my PHP application (PHP.ini, database.php, db_driver.php) to make sure it connects properly and works on IIS.

I am having problem with SQL Syntax now. When I try to run the application it does not give me database connection error (which it was giving earlier) but when I try to log in to the application (it has user authentication) - I get the following error:

Error Number: 42000

[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '`'.

SELECT * from ctbl_events WHERE 2017-01-11 <= startdate AND `enddate` >= 2017-01-11 and status=0 ORDER BY `ctbl_events`.`id` 

Filename: C:\inetpub\wwwroot\GMS\system\database\DB_driver.php

Line Number: 330

does this mean problem is with ` symbol and if yes would I have to manually go and modify all the SQL queries in my application (would be a gigantic task) or is there any way to handle this.

In both databases, date constants should be enclosed in single quotes. Backticks are not needed for this query. — Gordon Linoff
– Gordon Linoff, Commented Jan 11, 2017 at 12:51

Neville Kuyt · Accepted Answer · 2017-01-11 13:24:26Z

As far as I know, SQL Server does not support using backticks to escape table or column names (you can use brackets instead). You can easily try this by running a simple query like

select * from `ctbl_events`

If that doesn't work, you will almost certainly have to update all the queries to replace the backticks with brackets.

Second reason the query is probably failing is the dates in your query must be enclosed in quotes, and match SQL Server's date format (this is configurable, so you may need to experiment a little).

So, the query you're trying to run should look a little like this:

SELECT * from ctbl_events 
WHERE '2017-01-11' <= startdate 
AND [enddate] >= '2017-01-11' 
and status=0 
ORDER BY [ctbl_events].[id]

Though in this case, you don't really need the brackets around table or column names - it's best to agree a standard for this and stick to it.

Collectives™ on Stack Overflow

Migrating from MySQL to MS SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related