3

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

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.

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!

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]

edit:

  • 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.
Improve this question
3
  • Do you've to save unicode data in 'LeistungsBeschreibung'? If not try changing the datatype to varchar. Commented May 27, 2014 at 20:20
  • wow, that is ingenious - thanks a lot! that indeed seems to be the problem. Recreating the table with varchar in columns 'LeistungsBeschreibung' and 'InterneNotiz' seems to work. however simply inserting the data into the new schema gives conversion errors (from nvarchar to datetime). I'll have to talk to the DB admin. Is that the answer to my question then? Laravel + MS SQL Server can't handle unicode data in nvarchar(MAX) columns? Are there settings to circumvent this and make querying possible without changing the schema? Commented May 27, 2014 at 22:41
  • adding new columns with VARCHAR(MAX) and internally copying the data worked. you may want to add this as answer, @jithujose. However this is not an answer for cases in which changing the DB schema is not an answer. I found more on this issue here (@2013-11-08/20:38) and here. doesn't seem solved anywhere. Commented May 28, 2014 at 14:08

2 Answers 2

Reset to default
2

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

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

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

Links

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

thank you for the comprehensive answer - this solved this particular problem. Eventually due to other things being difficult with Laravel + MS SQL Server I now switched to wrapping MS SQL Server with dreamfactory to provide a RESTful API with JSON objects.
0

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

in /etc/freetds.conf :

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

In php.ini :

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

Thanks.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.