Update errors with SQL Server Compact 4

stojo303 · Post by **stojo303** » Mon 30 May 2011 17:30

Hi,

I'm trying to perform the following UPDATE SQL statement:

UPDATE tblOrder_x_Detail SET Order_ID = 200 WHERE DetailName = 'Test & Test2'

When executing this SQL statement, I'm met by a error message:

Code: Select all

exception class EUniError with message 'The data area passed to a system call is too small.'

It seems like the & character is causing this problem, any ideas how to solve it?

(I'm using the most recent UniDac version)

Thanks in advance.

AndreyZ · Post by **AndreyZ** » Tue 31 May 2011 07:36

Hello,

I cannot reproduce the problem. The 'The data area passed to a system call is too small.' error is an OS error. Please try executing this query in Microsoft SQL Server Management Studio. If the problem persists in Management Studio, please write about this problem to the Microsoft support.

stojo303 · Post by **stojo303** » Tue 31 May 2011 08:04

Hello,

Please try executing this query in Microsoft SQL Server Management Studio.

This is unfortunately easier said than done, since Management Studio does not properly support SQL Server Compact 4 databases.

If it helps, I can send you a demo application (with full source code) which will raise the exception.
According to the error, it seems like it is somehow "caught" by UniDac so maybe you can review it further with an example?

Please advise.

Thanks-

AndreyZ · Post by **AndreyZ** » Tue 31 May 2011 08:56

Ok, send your sample to andreyz*devart*com. Also please specify your Windows version.

stojo303 · Post by **stojo303** » Tue 31 May 2011 12:08

Thanks. Sample with full source and testdatabase is now send.
My Windows version is Windows 7 x64.

AndreyZ · Post by **AndreyZ** » Wed 01 Jun 2011 10:36

We have reproduced the problem. This problem is connected with the specifity of SQL Server Compact 4 work and not with SDAC. Also note that the '&' symbol doesn't cause this problem. For example, you can try executing the following SQL code:

Code: Select all

UPDATE tblArtist_x_Detail SET Artist_ID = 200 WHERE artist = 'Dave Stewart  The Spiritual Cowboys'

, and the same error will occur.
We don't know why SQL Server Compact 4 doesn't work with such queries. You can solve the problem by using parameters. For example, in your sample you can use the following code without any errors:

Code: Select all

UniSQL1.SQL.Text := 'UPDATE tblArtist_x_Detail SET Artist_ID = :Artist_ID WHERE artist = :artist';
UniSQL1.ParamByName('Artist_ID').AsInteger := 200;
UniSQL1.ParamByName('artist').AsString := 'Dave Stewart & The Spiritual Cowboys';
UniSQL1.Execute;

Also you can try to recreate your database and tables. I had created a new database with the new tblArtist_x_Detail table and successfully executed queries that cause the problem with your database.

stojo303 · Post by **stojo303** » Wed 01 Jun 2011 16:14

Thanks, working with parameters instead solved it. Strange bug though