Laravel：生成的 SQL 仅在特定表上引发错误

Laravel: Generated SQL throws error only on specific tables

提问by FrankSchrank

I am accessing MS SQLServer through Laravel, using Eloquent ORM or Query Builder. This works fine on all tables, but one particular table throws this error:

我正在通过 Laravel 访问 MS SQLServer，使用 Eloquent ORM 或查询生成器。这适用于所有表，但一个特定的表会引发此错误：

production.ERROR: exception 'Illuminate\Database\QueryException' 
with message 'SQLSTATE [HY000]: General error: 4004 General SQL Server error: 
Check messages from the SQL Server [4004] (severity 16) [(null)] 
(SQL: select * from [tblLeistung])

I executed the given SQL query in MS SQLServer Management Studio and it works fine. I also tried adding the DB name and schema name in the ORM protected $table = 'DBName.dbo.tblLeistung';which properly generates (SQL: select * from [DBName].[dbo].[tblLeistung])but throws the same error, too.

我在 MS SQLServer Management Studio 中执行了给定的 SQL 查询，它工作正常。我还尝试在 ORM 中添加 DB 名称和模式名称，protected $table = 'DBName.dbo.tblLeistung';这会正确生成(SQL: select * from [DBName].[dbo].[tblLeistung])但也会引发相同的错误。

The issue seems to be with the schema, since it is a rather large table (23 columns, 2160 records). Dropping it and recreating it with the same schema still causes the error. Dropping it and creating a simple table with the same name solves the error (however I need to make it work with the given schema).
Anyone has an idea whether there are specific table designs that are unsupported by a remote access of a SQLServer through Laravel?
Are there other (simple) ways to test, whether the problem is with Laravel or with the Server?

问题似乎与模式有关，因为它是一个相当大的表（23 列，2160 条记录）。删除它并使用相同的架构重新创建它仍然会导致错误。删除它并创建一个具有相同名称的简单表可以解决错误（但是我需要使其与给定的架构一起使用）。
任何人都知道是否存在通过 Laravel 远程访问 SQLServer 不支持的特定表设计？
是否有其他（简单的）方法来测试，问题是出在 Laravel 上还是出在服务器上？

I've been at this for some time now and appreciate any help. Thanks in advance!

我在这方面已经有一段时间了，感谢您的帮助。提前致谢！

A generated CREATE TABLEstatement of the problematic table looks like this:

CREATE TABLE问题表的生成语句如下所示：

CREATE TABLE [dbo].[tblLeistung](
    [LeistungID] [int] IDENTITY(1,1) NOT NULL,
    [LeistungskatalogID] [int] NULL,
    [SchlagID] [int] NOT NULL,
    [StatusInternID] [int] NULL,
    [StatusExternID] [int] NULL,
    [VertragID] [int] NULL,
    [Menge] [float] NULL DEFAULT ((0)),
    [SperreLeistung] [bit] NULL DEFAULT ((0)),
    [SperreAusfuehrung] [bit] NULL DEFAULT ((0)),
    [RechnungEinkID] [int] NULL,
    [RechnungVerkID] [int] NULL,
    [LeistungsunterkatID] [int] NULL,
    [LeistungsBeschreibung] [nvarchar](max) NULL,
    [InterneNotiz] [nvarchar](max) NULL,
    [AbrechungsdatenVorhanden] [bit] NULL DEFAULT ((0)),
    [AuftragID] [smallint] NULL,
    [UeberleistungID] [int] NULL,
    [AuftraggeberID] [int] NULL,
    [SSMA_TimeStamp] [timestamp] NOT NULL,
    [DatumFrist] [datetime] NULL,
    [DatumGeplant] [datetime] NULL,
    [DatumAusgefuehrt] [datetime] NULL,
    [DatumAuftragfreigabe] [datetime] NULL,
 CONSTRAINT [tblLeistung$PrimaryKey] PRIMARY KEY CLUSTERED 
(
    [LeistungID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[tblLeistung]  WITH NOCHECK ADD  CONSTRAINT [tblLeistung$tbRechnungVerktblLeistung] FOREIGN KEY([RechnungVerkID])
REFERENCES [dbo].[tblRechnungVerk] ([RechnungVerkID])
ON UPDATE CASCADE

ALTER TABLE [dbo].[tblLeistung] CHECK CONSTRAINT [tblLeistung$tbRechnungVerktblLeistung]

edit:

编辑：

• removing the FOREIGN KEYdidn't solve it
• the same error occurs if I access a table that doesn't exist. However after creating the same table on several DB instances with different configurations, I am pretty confident it exists.

• 删除FOREIGN KEY没有解决它
• 如果我访问一个不存在的表，也会发生同样的错误。但是，在具有不同配置的多个数据库实例上创建相同的表后，我非常确信它存在。

采纳答案by jithujose

Laravel uses db-library (if it's available) to connect to Sql Server which cannot receive unicode data from MSSQL. (1,2)

Laravel 使用 db-library（如果可用）连接到无法从 MSSQL 接收 unicode 数据的 Sql Server。(1,2)

If you don't have to save unicode data in 'LeistungsBeschreibung', change the column datatype to varchar.

如果您不必在“LeistungsBeschreibung”中保存 unicode 数据，请将列数据类型更改为 varchar。

You may want to checkout the accepted answer to a similar question: MSSQL Query issue in PHP and querying text data

您可能想查看类似问题的已接受答案： MSSQL Query issue in PHP and querying text data

Links

链接

• http://msdn.microsoft.com/en-us/library/aa937590(v=sql.80).aspx(1)
• https://github.com/laravel/framework/blob/master/src/Illuminate/Database/Connectors/SqlServerConnector.php#L47(2)

• http://msdn.microsoft.com/en-us/library/aa937590(v=sql.80).aspx(1)
• https://github.com/laravel/framework/blob/master/src/Illuminate/Database/Connectors/SqlServerConnector.php#L47(2)

回答by VISHNU Radhakrishnan

Please try :

请尝试 ：

$handle = getHandle();
    $handle->exec('SET QUOTED_IDENTIFIER ON');
     $handle->exec('SET ANSI_WARNINGS ON');
     $handle->exec('SET ANSI_PADDING ON');
     $handle->exec('SET ANSI_NULLS ON');
     $handle->exec('SET CONCAT_NULL_YIELDS_NULL ON');

OR

或者

You can also check the server configuration: check the "/etc/freetds.conf " file and change the tds version and add client charset then in php.ini please check mssql.charset and default_charset

您还可以检查服务器配置：检查“/etc/freetds.conf”文件并更改tds版本并添加客户端字符集然后在php.ini中请检查mssql.charset和default_charset

in /etc/freetds.conf :

在 /etc/freetds.conf 中：

;tds version = 4.2
tds version = 8.0
client charset = UTF-8

In php.ini :

在 php.ini 中：

mssql.charset = "UTF-8"
default_charset = "utf-8"

Thanks.

谢谢。