Limited length of VarChar in stored procedure

Question

Post reply

Limited length of VarChar in stored procedure

Robert Wilson-203725

Old Hand

Points: 358
More actions
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.

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

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

gbn SSCertifiable Points: 6429 More actions · Answer 1

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...

Robert Wilson-203725 Old Hand Points: 358 More actions · Answer 2

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.

gbn SSCertifiable Points: 6429 More actions · Answer 3

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

Robert Wilson-203725 Old Hand Points: 358 More actions · Answer 4

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).

gbn SSCertifiable Points: 6429 More actions · Answer 5

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?

Robert Wilson-203725 Old Hand Points: 358 More actions · Answer 6

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.

noeld SSC Guru Points: 96590 More actions · Answer 7

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

Robert Wilson-203725 Old Hand Points: 358 More actions · Answer 8

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.

Robert Wilson-203725 Old Hand Points: 358 More actions · Answer 9

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