Sql Query throws Identifier is too long. Maximum length is 128

4
votes

I am working on a simple update query and i see the below error while executing query. I am very much clear that this should not be a length issue at all. What may be the problem.

Error:

The identifier that starts with identifier is too long. Maximum length is 128

My Query:

update dbo.DataSettings set 
Query ="/Details?$filter=(Status ne 'yes' and Status ne 'ok')&$expand=name,Address/street,phone/mobile&$orderby=details/Id desc"
where id=5
3
sqlsql-serversql-update
This will not be a duplicate question because error may be the same but the issue is different. – Kurkula
Actually, the issue is the same, have a look at it. – potashin

3 Answers

21
votes

Use single quotes and escape your quotes in the text with two single quotes:

update dbo.DataSettings set
set Query= '/Details?$filter=(Status ne ''yes'' and Status ne ''ok'')&$expand=name,Address/street,phone/mobile&$orderby=details/Id desc'
where id=5
2
votes

You should use single quotes '(and escape those that are in your string with backslash \), because now you are assigning Query to the identifier (in that case, column name) and if it was even the right size for the identifier, you would probably get error like invalid column name :

UPDATE dbo.DataSettings
SET Query ='/Details?$filter=(Status ne \'yes\' and Status ne \'ok\')&$expand=name,Address/street,phone/mobile&$orderby=details/Id desc'
WHERE id = 5
0
votes

Just FYI I have once run into same issue and the above answers could't help my situation.Then I realize the length of a Variable I used to reserve a full qualified name of a linked server was not enough.I changed the length of the Variable to equal the length of the full qualified name and my query worked quite well.Sometimes it is as simple as that