MSSQL + ODBC + PHP = 0行 [英] MSSQL+ODBC+PHP = 0 rows in result
问题描述
我正在通过php + linux ODBC使用远程MSSQL库.一切都很好，但是今天我用SET + PIVOT编写了查询，但报错:
I'm using remote MSSQL base via php + linux ODBC. All was fine, but today i wrote query with SET + PIVOT and got error:
[unixODBC][FreeTDS][SQL Server]SELECT failed because the following SET options have
incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL,
ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views
and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or
XML data type methods and/or spatial index operations.
我试图写SET ANSI_NULLS ON等，但是它不起作用，结果中有0个字段.
通过MSSQL Studio在服务器上直接执行的同一查询效果很好.
知道哪里有问题吗?
谢谢.
I tried to write SET ANSI_NULLS ON etc but it wont works, there is 0 fields in result.
The same query executed directly on server via MSSQL studio works perfectly.
Any idea where can be a problem?
Thanks.
更新:
freetds.conf
UPDATE:
freetds.conf
[syserver]
host =192.168.1.200
port = 1433
tds version = 7.0
AnsiNPW=YES
QuotedID=YES
odbc.ini
[sy_odbc]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = syserver
Database = Insy
odbcinst.ini
odbcinst.ini
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1
在got tds version=6.0之前，以及将其更改为7.0之后，所有ANSI_NULLS之类的错误都消失了，但结果仍然有0行. AnsiNPW=YES和QuotedID=YES对我无济于事.如果尝试通过简单查询将版本更改为8.0，则显示??????????. odbc.ini中的TDS_version=7.0也不执行任何操作.
Before I got tds version=6.0, and after changing it up to 7.0 all errors such ANSI_NULLS gone, but I still have 0 rows in result. AnsiNPW=YES and QuotedID=YES do nothing for me. If try to change version to 8.0 in result of simple query i have ??????????. TDS_version=7.0 in odbc.ini also do nothing.
我的查询此处.
我的连接字符串是:$conn=odbc_connect('sy_odbc','t','t');
My query here.
And my connection string is: $conn=odbc_connect('sy_odbc','t','t');
更新2:
当我更改了TDS_version to 7.0或7.2时，我的某些php页面停止工作，因此我将其恢复为6.0.
您建议的连接字符串对我不起作用.完全没有. (在此问题之前，我使用我的工具已经超过一年了，这很好.)
此处是用于6.0版本问题查询的错误日志(此处有ANSI_NULLS等警告)
此处是用于7.0版本的问题查询的错误日志(没有警告，但结果为0行)
Update 2:
Some of my php pages stop working when i had changed TDS_version to 7.0 or 7.2, so i return it back to 6.0.
Connection string as you proposed don't work for me. At all. (I'm using mine more than year, and it was fine, before that issue)
Here is errorlog for problem query with 6.0 version (here we have ANSI_NULLS etc warnings)
Here is errorlog for problem query with 7.0 version (There is no warnings, but 0 rows in result)
推荐答案
我还可以想到Sergii还有两件事:
There are two more things I can think of Sergii:
1)您的连接字符串. 手册说，您的连接字符串应如下所示:
1) Your connection string. The manual says your connection string should look like this:
$conn = odbc_connect('Driver={sy_odbc};Server=192.168.1.200;Database=Insy', $username, $password);
所以我先尝试一下，看看是否有帮助.
So I'd give that a try first and see if it helps.
2)每当ODBC连接出现问题时，都应打开日志记录，以便捕获错误消息.为此，将以下行添加到您的/etc/odbcinst.ini文件中:
2) Any time you are having problems with ODBC connections you should turn on logging so you can capture error messages. To do this add the following lines to your /etc/odbcinst.ini file:
[ODBC]
Trace = yes
TraceFile = /tmp/odbc.log
如果查询仍然不起作用，请编辑问题并添加odbc.log文件的输出(假设其中包含错误消息).
If your query still doesn't work, edit your question and add the output from the odbc.log file (assuming there are error messages in it).
这篇关于MSSQL + ODBC + PHP = 0行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
