Post reply

Limited length of VarChar in stored procedure

  • August 16, 2005 at 7:38 am

    #89504

    I am writing a stored procedure to access the database multiple times and create an asp page by creating a variable with datatype varchar and then passing that back to the web server and displaying it. My problem is the limitation of the size of the varchar datatype. Does anyone know any work arounds for this problem.

  • August 16, 2005 at 7:44 am

    #582443

    Bit of an odd way to to do it but try "text" datatype.

    But then you have the problem in that the functions and operations you can use are a limited.

    So, you want IIS via an ASP page, to call a db, generate an ASP page, pass this back to IIS, and then have IIS render the page into HTML...

     

  • August 16, 2005 at 7:54 am

    #582447

    I am using SQL Server 2000 and it will not let me assingn text datatype to local variables in a stored procedure.

    Your assumptions are correct about what I am trying to do. I was having a speed issue and thought this solution would help. It helped tremendously until I ran into the limited length problem.

     

  • August 16, 2005 at 7:57 am

    #582449

    I'd say this would be slower. Or are your SQL and IIS on the same box, always a no-no

  • August 16, 2005 at 8:06 am

    #582455

    I think it is faster because I have reduced the round trips to the server by adding all the queries to the stored procedure. I should probably create the sp without the added code to create the asp page. Just thought I would try it.

    Our SQL and IIS are on seperate servers, but just out of curiousity, why do you think it should be slower(I am a newbie).

  • August 16, 2005 at 8:12 am

    #582465

    Because of the extra layers of rendering.

    One stored proc can return multiple result sets, did you know

    asp.net can handle it, I dunno about asp.

    There are articles on it, but I don't have VS.NET installed to look at what I've done before

    Do this: have usp_CoveringStoredProc calling usp_ResultSet1 and usp_ResultSet2 etc

    This will improve SQL sp performance (by reducing recompiles) and reduced round trips and rendering...

    Any more ideas, anyone?

     

  • August 16, 2005 at 8:24 am

    #582471

    Those are good ideas, and I appreciate the input. I should have also mentioned I was using Remote scripting and DHTML, which is also one of the reasons I think the page is slower. I am using these to make the pages appear and function more like a desktop application without all of the refreshes. The application I am working on collects a lot of data from the user.

  • August 16, 2005 at 9:12 am

    #582500

    you should always try to separate data from presentation !! use sql for what is good at : Retrieve data (not mixed with Metadata) and ASP for dynamic Page building


    * Noel

  • August 16, 2005 at 9:59 am

    #582530

    This is a very good point, and one I will have to probably take because it does not appear that it is possible(or proper technique) to do what I am trying to do.

    Thanks a lot for the reponses.

    Probably not a proper question for this thread but,is anyone familiar with remote scripting and the pros and cons of using it.

  • August 16, 2005 at 10:50 am

    #582561

    I think I might have found a way to accomplish this using a table. Does anyone else have any feedback on wether they think this approach is a good or bad idea. It really speeds up the the page. I don't think multiple result sets will work because of the order I need to call the queries.

    Thanks

     

Viewing 10 posts - 1 through 9 (of 9 total)

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