Post reply

Dynamic sql 4000 character limit workaround problem.

  • November 7, 2006 at 10:56 am

    #170993

    Dynamic sql 4000 character limit workaround problem.

    So here is the problem, I have a dynamic sql query(inside a stored proc) that can be over 4000 characters long that is running on a sql 2000 server

    here is an example of my execute

     EXEC sp_executesql  @strSql, N'@hDoc int, @p_blnTracking bit, @p_blnActive bit', @hDoc, @p_blnTracking, @p_blnActive

    this works until my sql gets too long. So i tried to work around it by wrapping the whole thing in another exec

     EXEC ('EXEC sp_executesql N'''+@strSql+@strSql2+''', N''@hDoc int, @p_blnTracking bit, @p_blnActive bit'',' +@hDoc+','+@p_blnTracking+','+ @p_blnActive+'')

    Strange thing is that this works when i run it in the query analyzer. but in my asp.net application it gives the error:

     Line 1: Incorrect syntax near '/'.

    I cannot figure out why query analyzer can run it but my app cannot-when it can run the first one fine(until it gets too long)

    Any ideas?

    Thanks dudes

  • November 7, 2006 at 1:50 pm

    #670399

    Did you try to enclose your string with []?

  • November 7, 2006 at 2:07 pm

    #670408

    Can't you break the sql into chunks, send each chunk and then tell it to execute through ASP?

    Live to Throw
    Throw to Live
    Will Summers

  • November 7, 2006 at 2:53 pm

    #670420

    I have to use stored procedures, so i cannot send the sql string from asp. it is assembled within the procedure.

    Are you suggesting I put the sql string enclosed in []?

    I think i might have got it working by trimming my sql down a bit(removed all the nice formatting so its a mess) but it appears to be under 4k characters now.

    but i would love to know the solution.

  • November 8, 2006 at 9:10 am

    #670615

    I was under the impression that you could exec any size command like this:

    DECLARE @sql1 nvarchar(4000),

    @sql2 nvarchar(4000),

    @sql3 nvarchar(4000),

    EXEC sp_executesql @sql1 + @sql2 + @sql3, N'@hDoc int, @p_blnTracking bit, @p_blnActive bit', @hDoc, @p_blnTracking, @p_blnActive

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 8, 2006 at 9:36 am

    #670629

    I do not know what is inside your stored procedure. But be careful when you set nvarchar(4000). There is a limit for a row in a table.

  • November 8, 2006 at 3:19 pm

    #670727

    I think that with SQL 2000 sp_executeSQL has a limit of 4000 characters on the first parameter, so you cannot concatenate strings that will exceed this in total.

    EXEC statement will allow you to concatenate multiple strings of 4000 characters together, providing any parameters of stored procedures called within the string are valid, but does not support parameter substition. I don't think that your N'''+@strSql+@strSql2+''' is valid in this case.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply