Maximum length is 128 error message

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Maximum length is 128 error message

Author

Topic

Chopps
Starting Member

9 Posts

Posted - 2001-12-19 : 15:29:29

All,

I receive this error:
The identifier that starts with 'Where Prospect.Create_Date_Time >= '1/1/1991' AND Prospect.Create_Date_Time <= '12/1/2001' AND Prospect.Mail_Country ='US' ORDER' is too long. Maximum length is 128.

I have a Proc where I pass in a dynamic SQl statement that veries in length according to search criteria. I receive the above error when trying to process the request.

this is what bombs in the proc:
SELECT @SQL = 'Select * from Prospect '
SELECT @SQL = @SQL + @strWhereSQL

I have "SET QUOTED_IDENTIFIER OFF" at the beginning of the proc to handle the single qoutes but im still having problems.

Im totally stumped. Any ideas?

Chopps

barmalej
Starting Member

40 Posts

Posted - 2001-12-19 : 15:42:50

Possibly your variables @SQL and @strWhereSQL are themselves too long. Apply rtrim() to both of them but add also some spaces: ' ' between.

Chopps
Starting Member

9 Posts

Posted - 2001-12-19 : 15:50:36

I gave that a try and it still bombs. Any other ideas?

Chopps

quote:

Possibly your variables @SQL and @strWhereSQL are themselves too long. Apply rtrim() to both of them but add also some spaces: ' ' between.

barmalej
Starting Member

40 Posts

Posted - 2001-12-19 : 15:57:29

OK. Try Print your string instead of executing. Possibly append something to the end of your string (for testing) to see if it is not so long and contains what you expectto get.

Chopps
Starting Member

9 Posts

Posted - 2001-12-19 : 17:07:36

Everything checks out when i try to print out the value

quote:

OK. Try Print your string instead of executing. Possibly append something to the end of your string (for testing) to see if it is not so long and contains what you expectto get.

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-20 : 14:54:54

This is sying that your statement is an identifier (like a table name) which has a maximum length of 128 chars.
This usually happens if you use " instead of ' as a string terminator.

It is probably not failing on the statements given here.
Try commenting out statements until you find which it is.
Probably the exec (@SQL)
if you put a select @SQL in there you should spot it.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Chopps
Starting Member

9 Posts

Posted - 2001-12-20 : 16:03:36

it would break on the variable @strWhere since the variable could hold items such as - createdate = '12/01/01' and ...etc

I think this is where it is happening since single qoutes are used within it. How should this be addressed when i build my Where statement?

Chopps

quote:

This is sying that your statement is an identifier (like a table name) which has a maximum length of 128 chars.
This usually happens if you use " instead of ' as a string terminator.

It is probably not failing on the statements given here.
Try commenting out statements until you find which it is.
Probably the exec (@SQL)
if you put a select @SQL in there you should spot it.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-20 : 16:13:52

Single quotes are OK within a variable - it's getting them in that you need to replace with double quotes.
Are you sure you have a table name after the from clause and there are no unprintable characters at the start of the where variable?

Generate some simplified code which gives the error and post that.
something like

proc spexec
@where varchar(128)
as
declare @sql varchar(2000)
select @sql = 'select * from sysobjects '
select @sql = @sql + @where
exec(@sql)
go
exec spexec 'where crdate > ''1 jan 1900'''

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Chopps
Starting Member

9 Posts

Posted - 2001-12-20 : 16:40:47

here is an example of what im passing to the proc.
Where Prospect.Create_Date_Time >= '1/1/1991' AND Prospect.Create_Date_Time <= '12/15/2001' AND Prospect.Mail_Country ='US' ORDER BY prospect.last_name

in the proc this is what i do:

SELECT @SQL = 'Select ProsPect.Day_Phone, prospect.Evening_Phone, Prospect_Dept.Dept_Code, Prospect.Create_Date_Time, Prospect.Mail_Country, Prospect.Prospect_Id, Prospect.First_Name, Prospect.Last_Name, Prospect.Mail_City, Prospect.Current_College, Prospect_Dept.Interest_Level_Code, Prospect_Dept.Dept_Classification, Prospect.Mail_Street_1, Prospect.Mail_Street_2, Prospect.Email, Prospect.Mail_State FROM Prospect INNER JOIN Prospect_Dept ON Prospect.Prospect_Id = Prospect_Dept.Prospect_Id '
SELECT @SQL = @SQL + rtrim(@strWhereSQL)

INSERT INTO #TempItems (Day_Phone, Evening_Phone, Dept_Code, Create_Date_Time, Mail_Country, Prospect_Id, First_Name, Last_Name, Mail_City, Current_College, Interest_Level_Code, Dept_Classification, Prospect.Mail_Street_1, Prospect.Mail_Street_2, Email, Mail_State)
exec(@SQL)

this is the error message

Microsoft OLE DB Provider for SQL Server error '80040e14'

The identifier that starts with 'Where Prospect.Create_Date_Time >= '1/1/1991' AND Prospect.Create_Date_Time <= '12/15/2001' AND Prospect.Mail_Country ='US' ORDE' is too long. Maximum length is 128.

I have tried doing select * to shorten the length of code and it had no effect since this had no qoutes in it. Its the dynamic SQl that is the offender

quote:

Single quotes are OK within a variable - it's getting them in that you need to replace with double quotes.
Are you sure you have a table name after the from clause and there are no unprintable characters at the start of the where variable?

Generate some simplified code which gives the error and post that.
something like

proc spexec
@where varchar(128)
as
declare @sql varchar(2000)
select @sql = 'select * from sysobjects '
select @sql = @sql + @where
exec(@sql)
go
exec spexec 'where crdate > ''1 jan 1900'''

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-20 : 17:19:27

Try commenting out the exec clause and seeing if you still get the error.

Also try calling it from query analyser from query analyser.
Also try with a shorter where clause (you can do that by aliasing Prospect to p).
Log the statement to a table so that you can look at it.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Chopps
Starting Member

9 Posts

Posted - 2001-12-20 : 17:45:51

This is how I called it in Analyser:
DECLARE @STR varchar(100)
SELECT @STR = "Where Prospect.Create_Date_Time >= '1/2/1991' AND Prospect.Create_Date_Time <= '12/9/2001' ORDER BY prospect.last_name"

exec sp_pageditems @STR, 1, 10

RESULTS:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name ''Where Prospect.Create_Date_Time >= '1/2/1991' AND Prospect.Create_Date_Time <= '12/9/2001' ORDER BY prospect.last_name''.

quote:

Try commenting out the exec clause and seeing if you still get the error.

Also try calling it from query analyser from query analyser.
Also try with a shorter where clause (you can do that by aliasing Prospect to p).
Log the statement to a table so that you can look at it.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

sica
Posting Yak Master

143 Posts

Posted - 2001-12-20 : 18:36:10

Try this:

DECLARE @STR varchar(100)
SELECT @STR = 'Where Prospect.Create_Date_Time >= ''1/2/1991'' AND Prospect.Create_Date_Time <= ''12/9/2001'' ORDER BY prospect.last_name'

Sica

Chopps
Starting Member

9 Posts

Posted - 2001-12-21 : 10:27:47

I tried what you suggested and this is the result:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'BY'.

Theres got to be a solution to this that im over looking.

Chopps

quote:

Try this:

DECLARE @STR varchar(100)
SELECT @STR = 'Where Prospect.Create_Date_Time >= ''1/2/1991'' AND Prospect.Create_Date_Time <= ''12/9/2001'' ORDER BY prospect.last_name'

Sica

izaltsman
A custom title

1139 Posts

Posted - 2001-12-21 : 10:38:40

Try making @STR a bit longer -- right now it is declared as VARCHAR(100) and the length of WHERE/ORDER BY is 118 characters...

Chopps
Starting Member

9 Posts

Posted - 2001-12-21 : 10:47:08

that worked.

Ill try to add '' to areas that need it in my ASP page.

Lets see what happens

quote:

Try making @STR a bit longer -- right now it is declared as VARCHAR(100) and the length of WHERE/ORDER BY is 118 characters...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources