This week's book giveaway is in the Big Data forum.
We're giving away four copies of Elasticsearch in Action and have Radu Gheorghe & Matthew Lee Hinman on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes Identifier too long exception in SQL server 7 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Elasticsearch in Action this week in the Big Data forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Identifier too long exception in SQL server 7" Watch "Identifier too long exception in SQL server 7" New topic
Author

Identifier too long exception in SQL server 7

sreenath reddy
Ranch Hand

Joined: Sep 21, 2003
Posts: 415
Hi

I have a stored procedure in SQL server 7 which takes a parameter and executes the query . It looks like

BEGIN
EXEC('DELETE FROM #TMPTable WHERE NEDevice NOT IN ('+@DeviceFilter+')')
END

and the DeviceFilter value will be passed as 'value1','value2',.............

But if the length of that parameter exceeds 128 , its throwing an exception saying Identier is too long , Maximul length is 128

i am using jtds driver

cn any one let me know the problem
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Just curious, why have you written this as a Stored Procedure instead of a simple piece of SQL in your application? I don't know if it will help if you change it though, but a Stored Procedure feels like an unecessary over-complication here.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
sreenath reddy
Ranch Hand

Joined: Sep 21, 2003
Posts: 415
Hi paul

Thats just an part of the stored procedure and it has many more SQL commands after this statement

Let me know if u have any idea

I can give some more hints on how its behaving

if i execute from query analyzer using

<SP_NAME> "'value1','Value2','Value3'" . It works fine but as soon as that paramter values seperated by comma exceeds 128 characters , that gives a problem
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Ah. Well, I should have thought of this earlier, but I don't think T-SQL allows you to pass multiple values for a single parameter. I think the normal way round it is to call a procedure repeatedly for each value, or to pass all the values as CSV and do some string processing in your procedure.
sreenath reddy
Ranch Hand

Joined: Sep 21, 2003
Posts: 415
hi paul

I am passing the values as CSV only . U might have seen that "'Value1','Value2'..."

and what is this string processing u were talking abt in the procedure ?? what do i need to do there
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Not quite, this would be handling the parameter as CSV:

Notice the difference in the type of quotes I use. SQL server understands the contents of double quotes as something different then single quotes. All literal string values used in T-SQL/SQL (as far as SQL Server is concerned) should be defined in single quotes.

The string manipulation stuff I mention are T-SQL functions like SUBSTRING. This is one way of breaking your single-value parameter up into the multiple values you need. However its far better to call the procedure repeatedly rather than trying to interpret a single parameter value like this. You would just be introducing another point where the code could go wrong.
[ June 15, 2005: Message edited by: Paul Sturrock ]
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Identifier too long exception in SQL server 7