Laravel:生成的SQL仅在特定表上引发错误 [英] Laravel: Generated SQL throws error only on specific tables

问题描述

我正在使用雄辩的ORM或查询生成器通过Laravel访问MS SQLServer.在所有表上都可以正常工作，但是一个特定的表会抛出此错误:

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:

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])

我在MS SQLServer Management Studio中执行了给定的SQL查询，并且工作正常. 我还尝试在ORM protected $table = 'DBName.dbo.tblLeistung';中添加数据库名称和架构名称，这会正确生成(SQL: select * from [DBName].[dbo].[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.

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

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?

我已经来过一段时间了，感谢您的帮助.预先感谢！

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

问题表生成的CREATE TABLE语句如下:

A generated CREATE TABLE statement of the problematic table looks like this:

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]

• 删除FOREIGN KEY并不能解决问题
• 如果我访问一个不存在的表，则会发生相同的错误.但是，在具有不同配置的多个数据库实例上创建相同的表之后，我非常有信心它存在.

• removing the FOREIGN KEY didn'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.

推荐答案

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

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

如果您不必将Unicode数据保存在"LeistungsBeschreibung"中，请将列数据类型更改为varchar.

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

您可能想要签出接受的类似问题的答案: PHP中的MSSQL查询问题和查询文本数据

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

链接

• 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)

这篇关于Laravel:生成的SQL仅在特定表上引发错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！