aspose file tools*
The moose likes JDBC 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
JavaRanch » Java Forums » Databases » JDBC
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