Forums Register Login

Identifier too long exception in SQL server 7

+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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 ]
Space seems cool in the movies, but once you get out there, it is super boring. Now for a fascinating tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 1271 times.
Similar Threads
Hibernate exception - could not insert
Prepared Statements Limited in Size??
How to convert from byte[] to string in form of 0x000000000
SQL Error in exporting db schema
MySQLSyntaxErrorException
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 02:56:10.