View Single Post
  #3 (permalink)  
Old May 12th, 2006, 09:36 AM
Phrozt Phrozt is offline
Registered User
Join Date: May 2006
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts

Ok.. I'll give you the quick and skinny of everything that went on to correct this problem.

Basically, I didn't want to modify the ASP page. I wanted some way to have the SQL output what it needed to so that the ASP would get exactly what it expected, since its code was already correct.

Clearly, the problem lay in that the varchar variable type only allows up to 8k characters. The next step was to try blobs, which we found out that stored procs do not like. I started to go off the idea of temp tables, but I used the 'table' variable type instead, so that I wouldn't have to drop it after the stored proc finished executing. Then I set the columns in the table variable to text, and I tried to set the column equal to a select of what was already in the table + the new value. This did not work. I tried
a different method called updatetext which also didn't work. SQL was like "you're still trying to use a blob, and that's not happening." It simply would not let me subquery the table, concatenate on the next value, and submit it back to the table.

I then switched over to a temp table and tried a few options, but I still was not able to create a single, comma delimited string of values. I finally ended up populating columns in a temp table. Because I only needed the first two columns to carry the values, I let the last 2 columns fill with null through the whole table. The last record, I left the first two columns null, and populated the last 2 with their values, which were based on however many percentage values were populated in the first two columns. It looked like this:

22.22 | date | NULL | NULL
22.22 | date | NULL | NULL
22.22 | date | NULL | NULL
22.22 | date | NULL | NULL
NULL | NULL | valu | valu

I did have to change the ASP page, but it was only a minor one. I looped through my record set, and said that if the first column was null, look for the last two values, otherwise append strings with values from the first 2 columns. It was a 5 minute change to the ASP file.
Reply With Quote