From Mysql To Mssql

Yii 1.1 General Discussion
1

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?

2

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.

3

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?

4

Yes, it is

select top x … then the rest of your sql.

(x is a number)

5

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?

6

Only SQL Server 2012 can do that natively.

See here for some ideas how to do pagination in earlier versions.