@Local_Variable 8k Limit
-
Hi,
I manage a web site where the 8k per row limit in SQL has never been a major problem, however, I would now like to increase the amount of information I give to users, and suddenly that 8k is becoming a pain.
To increase the amount of text I can store/display, I have created an extra column in one of my tables and set it to Ntext. By default, all Web (ASP) pages, interact with the DB through Stored Procedures (If find it easier/tidier) and all stored procedures use input paramaters. So, to store my data I use something similar to this in the ASP
Exec RFG_Mng_Prod_Update @Prodid='"&ProdId&"', @Osid='"&Osid&"', @Notes='"&Notes&"''
And the stored procedure looks like
CREATE PROCEDURE [Dbo].[RFG_Mng_Prod_Update]
(
@ProdId Varchar(5)='0',
@Osid Varchar(5)='0',
@Notes Varchar(32000)='',
 
Set @Qry = 'Insert Into Drivers (Osid, Notes)
Values('+@Osid+','''+@Notes +''')'
Exec(@Qry)
Set @Thid= @@Identity
And there is the problem, the Local Variable @Notes can only be 8k, not 32k. I know that Ntext can hold up to 2 gigabytes, but how do I access that sort of volume in a stored procedure?
The reason for relying on a "Set @Qry" then executing it, is because this is only a small part of a larger SP and there are various IF statements.
If anyone can shed any light, I'd be grateful.
Cheers
Conway
-
You were almost there!
CREATE PROCEDURE [Dbo].[RFG_Mng_Prod_Update] @ProdId VARCHAR(5)='0', @Osid VARCHAR(5)='0', @Notes nText='' AS DECLARE @Qry1 VARCHAR(2000) DECLARE @Qry3 VARCHAR(2000) SET @Qry1 = 'Insert Into Drivers (Osid, Notes) Values('+@Osid+',''' Set @Qry3='')' EXEC(@Qry1+@Notes+@Qry3)Best wishes,
Phil Factor -
Hi Phil,
See, I knew the answer had to be simple... I went through the helpfiles but missed that one little gem.
Thanks, I shall give it a try later and see what happens.
-
I started out writing something to the effect of it being mighty difficult but I thought I'd try the simple approach. The problem is, of course, what happens when they put a single-quote into the text but I'm assuming that this can be 'Escaped' with a '' in the application. It is very tricky to escape the single quotes from within the stored procedure as you can't use the REPLACE function on text.
I must admit, I didn't realise one could concatenate a text variable within an exec (string) command in SQL Server 2000 until I tried it, so there may be other snags! In SQL Server 2005 you can do it all magically with the Varchar(MAX)
Best wishes,
Phil Factor -
Hi Phil,
Single quotes are taken care of in the application, but they are a pain and I need to re-visit how they are handled.
The inputting and udating of information is in a strictly controlled environment for this site so I hope any glitches will only be seen by a very limited number of people (that'd be mainly me then).
Thanks again
Conway
-
If you don't need double-byte characters (in SQL2000) use TEXT rather than NTEXT.
Phil, have you tried sp_executeSQL as a possible alternative to EXEC, it might give you a bit more flexibility regarding the use of parameters and escaping the single quotes.
-
Thanks, David. Definitely an improvement, and takes care of the single quotes and SQL Injection risks. I was so tickled to get the original example working that I forgot about sp_executeSQL!
ALTER PROCEDURE [Dbo].[RFG_Mng_Prod_Update] @ProdId VARCHAR(5)='0', @Osid VARCHAR(5)='0', @Notes nText='' AS DECLARE @Qry NVARCHAR(2000) SET @Qry = 'Insert Into #Drivers (Osid, Notes) select @Osid,@notes' EXECUTE sp_ExecuteSQL @Qry, N'@osid int, @notes nText', @Osid, @Notes
Best wishes,
Phil Factor -
BTW, surprisingly but conveniently, "+" used in the EXEC() function (unlike, say, the LEN() function) is a param delimiter, not a concatenation operator!
- declare
@NULL varchar
- exec('select 1' + @NULL)
- select len(replicate('x',4001)+replicate('x',4000))
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
I don't understand. I get 1 and 8,000 as resultsets from above code.
Changing code to
declare @NULL varchar
set @null = '2'
exec('select 1' + @NULL)
select len(replicate('x',15456)+replicate('x',160000))
shows 12 and 8,000 for me. Maybe it is a difference between SQL Server 2000 (as I use) AND SQL Server 2005?
N 56°04'39.16"
E 12°55'05.25" -
- declare
@NULL varchar, @concat varchar(8000)
- --
- exec
('select ''first try''' + @NULL)
- --
- select
@concat = 'select ''second try''' + @NULL
- exec
(@concat)
- --
- select
right(replicate('1',8000)+replicate('0',1),1) [ConcatOverflow]
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Can anybody explain what's a point of dynamic SQL here?
_____________
Code for TallyGenerator -
The reason given was:
>The reason for relying on a "Set @Qry" then executing it, is because this is only a small part of a larger SP and there are various IF statements.
I'm not saying it's a good or bad reason, just reporting it!
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
IF inside of SQL statement?
Or it's just dynamic WHERE clause?
Should we open permanrnt classes how to avoid dynamic WHERE clauses or how to work it out when it's unavoidable?
_____________
Code for TallyGenerator -
Hi,
If someone can tell me where the IF..THEN..ELSE clauses should be, and can prove that by putting them there I get better server performance I'd be glad to give it a try, as long as we can agree on the word "Better".
The two programmes I use are Notepad, to write all the ASP(VbScript)/HTML code, and SQL server. Both VbScript and SQL server can handle the programming required, but SQL server is far more efficient in terms of required server resource to do so (VbScript has to create server objects, populate them and drop them).
I am not a DB specialist, instead I am employed to manage/maintain a web application and (touch wood) it works well, although some of you DB experts might bust a blood vessel if you could see how it is done.
All Best
Conway
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply