April 2, 2009 at 2:13 am
Hi,
Please help me about this problem. I am trying to run stored procedure but when argument is long. it gives error The identifier that starts with is too long. Maximum length is 128.
I have rounded my parameters with [] characters because I have strings inside of the parameter values Like [UnitSno LIKE 'AZX%']. And I dont know what chracaters to round the parameter value if the parameter contains some strings inside of the paprameter like [UnitSno LIKE 'AZX%']
If I am trying to pass below command to SP it gives error
[SP_ASD_LIMITED_RESULT] [UNIT_REP], [UnitRepairKey], '*', 'UnitSno LIKE 'AZX%'', 20, 20
Below call of the SP is working fine and gives expected results
execute [SP_ASD_LIMITED_RESULT] [UNIT_REP], [UnitRepairKey], '*', [UnitSno LIKE 'AZX%'], 20, 20
If I put inside of the [] characters long enough statement then this
"The identifier that starts with is too long. Maximum length is 128."
error is coming. Like below
execute 'SP_ASD_LIMITED_RESULT' 'UNIT_REP', 'UnitRepairKey', '*', [UnitSno LIKE 'AZX%', abcde = 'balaa blaa%', wer= 'balaa blaa%', tyh= 'balaa blaa%', fret= 'balaa blaa%', abcythjghde = 'balaa blaa%', abcfdfde = 'balaa blaa%', erdf= 'balaa blaa%', abdfdfcde = 'balaa blaa%', abcde = 'balaa blaa%', rtytu= 'balaa blaa%', abwetgdwcde = 'balaa blaa%', abcde = 'balaa blaa%', kjui= 'balaa blaa%', nyhbg= 'balaa blaa%', uujiu= 'balaa blaa%', etgyhy= 'balaa blaa%',], 20, 20
So question is what characters to use if I want to pass long statement to SP and it's contains parameters what includes --> 'asd%'
Br,
SQLMike
April 2, 2009 at 2:30 am
I realy dint understand the actual probelm....
but still, you should enclose ONLY the column names with "[]".
some thing like below..
[UnitSno] LIKE 'AZX%'
and NOT [UnitSno LIKE 'AZX%'] - which is wrong....
pls clarify the problem.....
April 2, 2009 at 3:03 am
Thanks GilaMonster for your reply.
So the problem is when I am trying to execute SP with long parameter (more than 128) values, then I am getting this error.
I have to deliver to my stored procedure
following parameter which contains ' characters I get the error
'PRODUCT.A = UN_ABCD.B AND
(UN_ABCD.C = 'XXX')
AND (UN_ABCD.D IN (6)) AND (UN_ABCD.scrap = 0)
AND (UN_ABCD.E LIKE 'XXX%')
AND (UN_ABCD.F <= '30.11.2008')
AND (UN_ABCD.G NOT IN (1, 3))
AND (UN_ABCD.H LIKE 'XXX%')
AND (UN_ABCD.I LIKE 'XXX%')
AND (UN_ABCD.J LIKE 'XXX%')'
then I have get this error.
April 2, 2009 at 3:17 am
Assuming that the long parameter forms a part of a where clause in one of your queries in the procedure, you have first get rid of the single identifier (i.e. "[" & "]" characters) enclosing the long parameter and then use double quotes instead of single quotes withing the long parameter. For e.g.
EXECUTE SP_ASD_LIMITED_RESULT 'UNIT_REP', 'UnitRepairKey', '*', 'UnitSno LIKE ''AZX%''', 20, 20
[/code]
--Ramesh
April 2, 2009 at 10:42 am
it looks like you are passing dynamic WHERE condition and hitting the boundary on the length. So why dont you break you conditions and pass it smalll chunks like first 5 where conditions in 1st parameter and next 5 where conditions in 2nd parameter and so on.
April 2, 2009 at 1:23 pm
Hi
Is it possible that your procedure parameter is restricted to length of 128?
I just tried with a 10,000 characters long VARCHAR and it works fine.
Greets
Flo
April 3, 2009 at 3:46 am
Hi all who helped me,
Case is this I have now fixed the problem of this length. Problem was with the ' signs.
You have put two '' signs front and end of the variable if it's inside of parameters what is sent to SP, like this way
execute SP_AWER_LIMITED_RESULT
' UNIT_Rooster AS [UN_REP], PRERERRE AS [PROD] ',
'[UN_REP].receive, [UN_REP].un',
' [UN_REP].receive, [UN_REP].un ,
'[PROD].prd = [UN_REP].vbn AND ([UN_REP].ert = ''INTSITE'')
AND ([UN_REP].jkl LIKE ''S3%'') ', 4, 20
But now these parameters are send successfully to SP I am getting another error about
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "UN_REP.receive" could not be bound.
So somehow system it's not regognizing aliases even it's working if I print the
variable data and use this printed result in sql query analyzer.
Here is the code and error is coming from that.
SET @Statement = 'SELECT @TotalNumRows = COUNT(1)
FROM ' + @tables + '
WHERE ' + @whereStatement
PRINT @Statement -- bebug
EXEC sp_executeSQL @Statement , @params = N'@TotalNumRows INT OUTPUT', @TotalNumRows = @TotalNumRows OUTPUT
Br,
SQLMike
April 3, 2009 at 4:44 am
The error indicates the the formed query either does not have a table or column with the specified alias.
Can you post the exact query text that has been executed by sp_executesql procedure?
--Ramesh
April 3, 2009 at 7:16 am
Hi,
here is the statement what is executed in this statement.It's working quite fine if I am running that in query analyzer. But when it's executed inside of the SP it's creates errors
SELECT TotalNumRows = COUNT(1)
FROM UUNUREPAIR AS [UN_REP], PRODERUE AS [PROD]
WHERE
[PROD].prMaCode = [UN_REP].itemC
AND ([UN_REP].siteid = 'IN')
AND ([UN_REP].rep IN (2,4)) AND ([UN_REP].sc = 0) AND
([UN_REP].pono LIKE '%') AND ([UN_REP].receive <= '11/30/2008')
AND ([UN_REP].ServT NOT IN (1, 3)) AND ([UN_REP].CustID LIKE '%')
AND ([UN_REP].Vendor LIKE '%')
AND ([UN_REP].ItemC LIKE 'S3%')
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "UN_REP.itemC" could not be bound.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply