The identifier that starts with

When I try to execute the SQL statement below when pointed to an SQL Server Express I get an error

The identifier that starts with ‘Select * FROM AvailabilityBlocks LEFT JOIN Location ON AvailabilityBlocks.LocationID=Location.LocationID WHERE ((AvailabilityBlo’ is too long. Maximum length is 128.» Error.

This SQL works fine when executed against an Access DB. Can anyone offer any suggestions?

Thanks in advance

2 Answers 2

Trending sort

Trending sort is based off of the default sorting method — by highest score — but it boosts votes that have happened recently, helping to surface more up-to-date answers.

It falls back to sorting by highest score if no posts are trending.

Switch to Trending sort

You were using [ instead of ( for the named subquery. I fixed it in the below and added a little structure for readability.

There a few things off with your query. I will give you what I think your query should look like. But I believe you need to also be able to figure out how to break up your query so as to figure out what it wrong

First thing first, make sure the inner query in your left join actually works :

Now once you verify that the upper query works, you can embed that inner working query into your left join (I assume that most of the columns are within availability blocks if ResourceId is not then add it via Loc.ResourceID).

Lastly within the «order by» clause use the appropriate alias rather than table names

Being able to break out your queries to see what is causing you problems is key to figuring out what other problems exist (meaning don’t expect peoples’ solutions to give you the answer, but rather guide you to figuring out the solution on your own)

I was using SQL Server 2014 earlier. When I am trying to execute the following query

but I get this error:

The identifier that starts with ‘SELECT TOP (100) DateTime, VariableID, VariableName,VariableValue_NumValue as VariableValue, VariableLabel as VariableLab’ is too long. Maximum length is 128

I searched for it a lot, but could not find the solution.

2 Answers 2

Trending sort

Trending sort is based off of the default sorting method — by highest score — but it boosts votes that have happened recently, helping to surface more up-to-date answers.

It falls back to sorting by highest score if no posts are trending.

Switch to Trending sort

As HoneyBadger said, you are using the double quotes where you should be using single quotes. You are also escaping the single quotes within your @query incorrectly, not qualifying your DateTime column name as it is a SQL keyword nor are you specifying your dates correctly.

It appears you have come from another development language and are trying to apply the same principles to your SQL code. I would recommend doing some research on SQL syntax and best practice:

Try using the following query

Consider that Datetime is not a good idea for a column name;

Not the answer you’re looking for? Browse other questions tagged sql-server or ask your own question.

Related

Hot Network Questions

Subscribe to RSS

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Записать строку длиннее 128 символов в базу MS SQL

Записываю большой текст в базу.

1 В 1874 г. в Корсаковском округе заболевшие относились к общему числу,
как 227,2:100. Синцовский, вра’ is too long. Maximum length is 128.
The identifier that starts with ‘, 1.875 г., э 16.

Между прочим, я встречал тут такие диагнозы, как неумеренное питье от
груди, неразвитость к жизни, ‘ is too long. Maximum length is 128.
The identifier that starts with ‘, 1870 г., э 2. Против зуда при varicella
гиляки употребляют топленый тюлений жир, которым намазывают все тело. Оттого
что гил’ is too long. Maximum length is 128.
The identifier that starts with ‘.
7 Д-р Васильев часто встречал на Сахалине гиляков, страдавших кровавым
поносом.
8 Припоминаю читателю, что эти в’ is too long. Maximum length is 128.
The identifier that starts with ‘, 1885 г., э 30.
11 Каторжного с мигренью или ишиасом легко заподозрить в симуляции и не
пустить в лазарет; однажды я вид’ is too long. Maximum length is 128.
The identifier that starts with ‘ на целые партии, так
что каторжные идут ощупью, держась друг за друга.
14 Составитель отчета комментирует эти слу’ is too long. Maximum length is 128.
The identifier that starts with ‘. Некоторые каторжные, чтоб их не
посылали на работы, увечат себя, например отрубают себе на правой руке
пальцы. Ос’ is too long. Maximum length is 128.
The identifier that starts with ‘, вентиляций нет, и суп, который
при мне в Александровске готовили для больных, был очень соленого вкуса, так
как варили его’ is too long. Maximum length is 128.
The identifier that starts with ‘, продовольствие больных
производилось из общего тюремного котла (приказ начальника острова 1890 г.,
э 66).

Currently I’m encountering an issue when loading (a selection of) data residing inside an Oracle database to SQL Server Management Studio (SSMS).

When running the following query in SQL Developer (Oracle) it runs fine:

When I use this query in the import wizard (in SSMS), it says the SQL statement is valid. When I continue and preview the data (in the next window in the wizard), it also gives me a correct preview of the data (similar to SQL Developer). However, when I execute the statement, it gives an error, namely:

I looked up several other similar questions, however most of them involve the (mis)use of double quotes instead of single quotes, such as here. Or they involve (mis)use of square brackets instead of parentheses, such as here.

I guess the error has something to do with (final) column names being too long? Is there a way to overcome this? Or is the error caused by something else?

Help is much appreciated!

1 Answer 1

Your projection contains scalar subqueries. That is probably fine (I believe SQL Server supports them) but you haven’t given them column aliases.

Now identifier SELECTBEW_WAARDE_NUMFROMV_BAE_ELEMENT_WAARDENBWHEREB.BOT_ID=BEW.BOT_IDANDB.BET_ID=740000000000638ANDB.BEW_DATUM_INGANG is the start of your scalar cursor without spaces. So I’m guessing SSMS requires columns to be named and, as you haven’t provided an alias, is generating them using the whole cursor statement. Which is longer than the permitted 128 characters and hence the error.

(It seems SQL Developer is clever enough to apply a substring() to the generated names.)

Anyway, if my guess is correct all you need to do is provide those columns with aliases, such as

There may be other issues with porting Oracle queries to run on SQL Server, such as the use of Oracle specific trunc(SYSDATE) instead of their MSSQL analogues CONVERT (date, GETDATE())

The identifier that starts. Maximum length is 128

April 15, 2002 at 8:58 pm

I recently installed SQL 2000 on my Desktop. I kept the existing SQL 7 installation.

I run a stored procedure daily for data releases. The stored procedure runs fine in SQL Query Analyzer 7, But after 2000 was installed it fails to run with the following message.

Server: Msg 103, Level 15, State 7, Line 12

The identifier that starts with »TEXT’, ‘TEXT’, ‘TEXT’, ‘TEXT’, ‘TEXT’, ‘TEXT’, ‘TEXT’, ‘TEXT’, ‘TEXT’ is too long. Maximum length is 128.

April 15, 2002 at 9:06 pm

They havent that I know of. Could you post the first part of the proc?

April 15, 2002 at 9:15 pm

CREATE PROC DBO.Copydb

@TARGET_DB VARCHAR (100) = ‘SDS093.COPYTEST’,

DECLARE @TSQLSTMT VARCHAR (3000)

— NOTE TRUNCATE TABLE cannot be used on the table in linked server

SET @TSQLSTMT = ‘DELETE FROM ‘ + @TARGET_DB + ‘.DBO.TABLE ‘

SET @TSQLSTMT = NULL

April 15, 2002 at 11:12 pm

The Procedure is being run against a sql 7 Database. The only thing different is that is that its now being run using SQL 2000 Analyzer.

April 16, 2002 at 12:17 am

Quoted identifiers are used by default in SQL Server 2000, that is, they are set to ON. This is different from SQL Server 7.0 where they were set to OFF by default.

April 16, 2002 at 4:49 am

FROM «System Configuration» in BOL

The QUOTED_IDENTIFIER setting determines what meaning Microsoft SQL Server gives to double quotation marks («). When QUOTED_IDENTIFIER is set to OFF, double quotation marks delimit a character string, just as single quotation marks do. When QUOTED_IDENTIFIER is set to ON, double quotation marks delimit an identifier, such as a column name. An identifier must be enclosed in double quotation marks; for example, if its name contains characters that are otherwise not allowed in an identifier, including spaces and punctuation, or if the name conflicts with a reserved word in Transact-SQL. Regardless of the QUOTED_IDENTIFIER setting, an identifier can also be delimited by square brackets.

The meaning of the following statement, for example, depends on whether QUOTED_IDENTIFIER is set to ON or OFF:

SELECT «x» FROM T

If QUOTED_IDENTIFIER is set to ON, «x» is interpreted to mean the column named x. If it is set to OFF, «x» is the constant string x and is equivalent to the letter x.

If the previous SELECT statement example were part of a stored procedure created when QUOTED_IDENTIFIER was set to ON, then «x» would always mean the column named x. Even if the QUOTED_IDENTIFIER setting was later switched, and set to OFF, the stored procedure would respond as if it were set to ON and treat «x» as the column named x.

You are right about the » being your problem, with Quoted Identifiers On the «‘-‘» is treadted as column name ‘-‘ and not a string value. To get around and make easiest on yourself change » to », so «‘-‘» is »’-»’, which always works.

«Don’t roll your eyes at me. I will tape them in place.» (Teacher on Boston Public)