Error message "the name b is not permitted in this context"

[expired user #9231]'s profile image [expired user #9231] posted 9 years ago in General Permalink
Dear Support Communicty,
I have a strange Problem: When I want to add a new field in a table of a Microsoft SQL Database,
by clicking the + button and filling out the form, I then get this error message (see screenshot).
HeidiSQL adds a single "b" to the SQL Statement and I do not know why.
Does anyhone have a solution?

Kind regards,
Henning
1 attachment(s):
  • Screenshot-HeidiSQL-Problem-when-enterin-a-new-field
ansgar's profile image ansgar posted 9 years ago Permalink
That b'' is MySQL syntax, and is used to express a bit string. This is not valid in MSSQL I guess and therefore you get that error.
[expired user #9231]'s profile image [expired user #9231] posted 9 years ago Permalink
Dear Ansgar,
thanks for the fast Reply. In the Connection Manager I defined that my database is a MS SQL Database, see screenshot. Do I have to make other Settings to make sure HeidiSQL is creating the correct Syntax?
Kind regards,
Henning
1 attachment(s):
  • Screenshot-MS-SQL
ansgar's profile image ansgar posted 9 years ago Permalink
No, you cannot do anything to fix that. I need to fix that in the ALTER TABLE code, which is automatically executed by HeidiSQL when you modify a table structure.
[expired user #9231]'s profile image [expired user #9231] posted 9 years ago Permalink
There is a similar problem with the UPDATE function: When I want to change the content of a field, I also get an error message. This SQL Statement is created bei HeidiSQL:

UPDATE TOP(1) "gmg"."web1180"."tabUser" SET "Username"=N'Elisabeth' WHERE "ID"=67 AND "Username"='ChristianLenzinger' AND "Wohnungsnummer"=62 AND "ZugriffErlaubt"=b'1' AND "Bemerkung"='';

It throws this error message:
/* SQL Fehler (102): Incorrect syntax near '1'. */


Apperently HeidiSQL always adds that "b" when refering to BIT fields even when talking to a MS SQL Server.

Please also fix,
thanks in advance.

Kind regards,
Henning
[expired user #9231]'s profile image [expired user #9231] posted 9 years ago Permalink
Similar Problem with INSERT and SELECT statements:

INSERT INTO "gmg"."web1180"."tabUser" ("Username", "Wohnungsnummer", "ZugriffErlaubt") VALUES (N'MaPl', 55, 1);

SELECT TOP 1 "ID", "Username", "Wohnungsnummer", "ZugriffErlaubt", "Bemerkung" FROM "gmg"."web1180"."tabUser" WHERE "ID"=0 AND "Username"='MaPl' AND "Wohnungsnummer"=55 AND "ZugriffErlaubt"=b'1' AND "Bemerkung"='';

This post Claims that MSSQL Support already works regarding INSERT UPDATE and DELETE:
http://www.heidisql.com/forum.php?t=8065

Thanks for your help in advance.
Henning


/* SQL Fehler (102): Incorrect syntax near '1'. */
[expired user #9231]'s profile image [expired user #9231] posted 9 years ago Permalink
Same Problem with DELETE command:

DELETE FROM "gmg"."web1180"."tabUser" WHERE "ID"=2 AND "Username"='anastasiaefimenko' AND "Wohnungsnummer"=1 AND "ZugriffErlaubt"=b'1' AND "Bemerkung"='';

/* SQL Fehler (102): Incorrect syntax near '1'. */
1 attachment(s):
  • Screenshot-on-Delete
ansgar's profile image ansgar posted 9 years ago Permalink
The problem is the BIT column, which is treated like MySQL would want it to be treated. If you use an integer-type or text-type column, HeidiSQL should not run into such errors.

MSSQL support in HeidiSQL is quite usable, but far from being complete. It's also very difficult to track the differences of all MSSQL server versions. Things that are supported on a MSSQL 2000 server are not necessairily supported in MSSQL 2008 and other versions. And vice versa.

Please login to leave a reply, or register at first.