I have an application written in Yii where I’ve been developing towards a MySQL database. The queries can look something like this:
The queries are written using standard SQL so that the application can be run on any database, or at least so I thought.
Now I want to use it on MSSQL but am running into a problem where the queries are not being executed properly and generating errors. MSSQL is requiring me to have a syntax like this:
Is there any way for me to get around this without having to rewrite the SQL queries? Perhaps there is a setting in MSSQL or is there something in Yii which I can define?
MSSQL Server should understand your standard SQL without too many troubles.
If the queries only run when adding the database name and owner, then check your connection settings.
The "" brackets are not needed unless you have some weird characters or spaces in your table names.
The problem turned out to be the usage of LIMIT in SQL, which works in MySQL but not in MSSQL.
I am using MSSQL 2008 R2 - do you know if there is anything similar to LIMIT without having to completely rewrite all queries?
Yes, it is
select top x … then the rest of your sql.
(x is a number)
That does not work well with pagination.
Using LIMIT I could get the results between 1-10, 10-20, 20-30 etc.
Using TOP I can only get 1-10, 1-20, 1-30 etc.
Is there any way to achieve this for pagination?
Only SQL Server 2012 can do that natively.
See here for some ideas how to do pagination in earlier versions.