Clever Geek Handbook

PHP: SQL syntax error on equivalent server

Our development and production servers are identical except for the capacity (RAM, Disk, etc.): MS Win Server 2008 R2 Ent / MS SQL Server 2005 / Apache2.2 / PHP 5.213. In our dev machine, (sqmed) sql works fine:

SELECT somecol collate database_default 
FROM localtable
UNION
SELECT somecol collate database_default 
FROM linkedserver.remotedb.dbo.table

Note. The linked server is the same on both dev / prod servers.

The prod server displays the following error:

[Microsoft] [SQL Server Native Client 10.0] [SQL Server] Incorrect syntax near 'DATABASE_DEFAULT'.

If I remove the dabatase_default class clauses, I get this error (as expected):

[Microsoft] [SQL Server Native Client 10.0] [SQL Server] Unable to resolve mapping conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in UNION operation.

If you put the prod code in the dev database, it will work. On the other hand, if I point the dev code to the prod database, it doesn't work.

What's happening?

Thank.

+3


source to share


2 answers


I believe you have different default collation on the servers and this is causing the problem.

select somecol collate database_default from localtable
union
select somecol collate SQL_Latin1_General_CP1_CI_AS from linkedserver.remotedb.dbo.table


Specify what column sort is.

0


source


Thanks to everyone who tried to help.

It was our mistake: we were connecting to a third database with a compatibility level of 70. Therefore, SQL Server interpreted the query in this parameter. We switched the compatibility level to 80 and it works as expected.



It is interesting to note that the compatibility level of the data involved in the query is irrelevant. SQL Server looks at the level of the database to which you are connected.

0


source






More articles:


All Articles