VarChar Size Limitation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

VarChar Size Limitation

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-01 : 09:14:56

Kevin writes "I am writing a stored procedure in which I create strings of SQL statements, and then after creating them, I "EXEC (myString)" them. However, at certain times, I need more than the allotted amount available in a VarChar(8000). I tried using the "text" Data Type, but I get the error message "The text, ntext, and image data types are invalid for local variables." I really need the extra space. Also, in my procedure, efficiency is not important.

How can I store more than 8000 characters in a variable in a stored procedure?"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-01 : 09:44:47

You can't declare or manipulate a text variable, you can only pass it to a stored procedure. However, you CAN use multiple varchar(8000) parameters and exectute them like this:

EXEC (@sql1 + @sql2 + @sql3)

motokevin
Starting Member

36 Posts

Posted - 2002-04-02 : 12:00:00

I know about Exec (@sql1 + @sql2 + ... )
However, the problem still stands. I need to save a string of more than 8000 characters inside a variable in a stored procedure. Does SQL provide any way to do this? SQL doesn't allow the "text" data type to be used as a variable in a stored procedure. (they do allow it to be passed as a parameter, but you can't modify it or set it's value)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-02 : 12:06:42

You'll need to explain your situation in more detail, because I don't understand how this does NOT work for you.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-02 : 12:06:48

obviously, if there were, rob would have mentioned it rather than offer the fore mentioned solution.

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-02 : 12:09:37

Is there a reason why you have to store your massive string into one variable? You should easily be able to use more than one variable and work with that to manipulate your data. It should be no problem to store your massive data string into chunks of 8000 characters.

Why don't you post your stroed procedure so that we can help you out.

motokevin
Starting Member

36 Posts

Posted - 2002-04-04 : 12:41:05

You guys are missing the point. As a programmer, there will always be more than one way to do something, and there will always be a way to get around a particular problem. Many times, using a method that goes around the problem requires more complicated code. I have already found a way around my problem that works. However, it requires much more code than I think should be necessary.

With that aside, my question is STILL unanswered: How can I store and manipulate more than 8000 characters in a variable in a stored procedure?

If it appeases you, I am spooling my string up inside a cursor loop. I have no way of knowing before entering the loop how many records the cursor will have and how many characters the query will produce, and no way of dynamically declaring another varchar(8000) inside the loop each time I need it. Since my loop requires more than 8000 characters, it seems to me that I must have a SINGLE place of storage for this string.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-04 : 13:22:07

quote:
You guys are missing the point

No, we have clearly addressed your question and provided a solution that works within the capabilities of the software. If you find those capabilities lacking, use another product, or complain to Microsoft about it.

quote:
As a programmer, there will always be more than one way to do something, and there will always be a way to get around a particular problem....I have already found a way around my problem that works...However, it requires much more code than I think should be necessary

Why are you belaboring the issue then? You have a solution, it works, use it. Complaining about the shortcomings of the software doesn't accomplish anything. And somehow, a number of very accomplished SQL Team members get by very well with the multiple varchar variable technique. We can't all be wrong for using it.

quote:
With that aside, my question is STILL unanswered: How can I store and manipulate more than 8000 characters in a variable in a stored procedure?

Your question HAS BEEN ANSWERED: you cannot manipulate text variables in SQL Server, you need to use multiple varchar(8000) variables instead (lo and behold, it says this in the VERY FIRST reply!) Like I said, if that's not acceptable to you, there's Oracle, MySQL, PostGres, DB2, Informix, whatever floats your boat.

quote:
If it appeases you, I am spooling my string up inside a cursor loop. I have no way of knowing before entering the loop how many records the cursor will have and how many characters the query will produce, and no way of dynamically declaring another varchar(8000) inside the loop each time I need it. Since my loop requires more than 8000 characters, it seems to me that I must have a SINGLE place of storage for this string.

Have you tried pouring the multiple strings into a table, either as multiple rows of varchar(8000), or in a single text column (using WRITETEXT), using bcp to output the table into a .sql text file (using no delimiters), and then executing that file using isql or osql? I've tested this, and it works pretty well...the performance isn't bad at all in fact. The only drawbacks are permissions and file maintenance; you can quickly clutter up the drive with .sql files, and you need to have rights to execute xp_cmdshell and to create files on the server. Diligent housekeeping code alleviates this problem. If these are not obstacles for you, you might want to consider it (although my suggestions so far have continually fallen flat) You can even reimport the results back into your stored procedure using BULK INSERT or bcp.

Sounds too elaborate? I agree, it would be nice if it was easier. It would also be nice if Ashley Judd would...well, that ain't gonna happen! Regardless, I see so much more potential for this technique that whatever niggling problems need to be overcome, the effort is worth it. And it's still worth pursuing even if the text variable limitation DIDN'T exist. This approach has already been explored for the purposes of creating XML files:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9336

It can easily be modified to generate large SQL statements.

I suggested this before, and I'll say it clearly now: focus on what the software CAN do, instead of what it CAN'T, and you can accomplish anything with it. You can only become a better programmer for trying.

monkeybite
Posting Yak Master

152 Posts

Posted - 2002-04-04 : 13:49:41

Maybe try a table variable with a single text column?

DECLARE @tbl table (Field1 text NULL)
. . .
SELECT Field1 FROM @tbl

-- monkey

ppuar
Starting Member

28 Posts

Posted - 2002-04-08 : 02:56:59

Ok guys - I don't understand why you guys are giving up on this topic. I to was looking for a way to store huge amounts of text into a single sql 7 column. I went through alot of grief to get my solution solved. I know there are others ways to do it, but think why did Microsoft include ntext and text as datatypes in sql 7? Do you think they were making it up - NO

Here is my solution and I hope it helps:

CREATE PROCEDURE test
@vchOption1 varchar(255) = null,
@vchOption2 varchar(255) = null,
@vchOption3 ntext = null,
@vchOption4 varchar(255) = null
AS
begin
declare @a int

begin
select @a = iCategoryId from lookuptable
where vchCategory = @vchOption2

insert into sometable(iCompanyId,
iCategoryId,
tNotes,
dtInsertDate,
dtUpdateDate)
values(cast(@vchOption1 as int), @a, '', getdate(), getdate())

EXEC sp_dboption '????', 'select into/bulkcopy', 'true'

declare @tpointer binary(16)

select @tpointer = textptr(tNotes) from
sometable where iNoteId = @@IDENTITY

writetext sometable.tNotes @tpointer @vchOption3

EXEC sp_dboption '????', 'select into/bulkcopy', 'false'

end

On the client side:

Dim dbConnection As New ADODB.Connection
Dim dbCommand As New ADODB.Command
Dim dbRecordSet As New ADODB.Recordset

dbConnection.Provider = "sqloledb"
dbConnection.Properties("Data Source").Value = "??????"
dbConnection.Properties("Initial Catalog").Value = "????"
dbConnection.Properties("User Id").Value = "????"
dbConnection.Properties("Password").Value = "??????"
dbConnection.ConnectionTimeout = 300
dbConnection.Open

dbCommand.ActiveConnection = dbConnection
dbCommand.CommandTimeout = 300
dbCommand.CommandText = "test"
dbCommand.CommandType = 4

dbCommand.Parameters.Append dbCommand.CreateParameter("RETURN_VALUE", 3, 4, 0)
dbCommand.Parameters.Append dbCommand.CreateParameter("@vchOption1", 200, 1, 255)
dbCommand.Parameters.Append dbCommand.CreateParameter("@vchOption2", 200, 1, 255)
dbCommand.Parameters.Append dbCommand.CreateParameter("@vchOption3", 202, 1, 1073741823)
dbCommand.Parameters.Append dbCommand.CreateParameter("@vchOption4", 200, 1, 255)

dbCommand.Parameters("@iCommand") = 2
dbCommand.Parameters("@vchOption1") = mContactId
dbCommand.Parameters("@vchOption2") = Combo1.Text
dbCommand.Parameters("@vchOption3") = Text1.Text

dbCommand.Execute

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-08 : 09:36:16

While there's nothing wrong with your approach, it doesn't address the meat of the question. You're just inserting large text values into a table...which you could also do like this:

CREATE PROCEDURE test
@vchOption1 varchar(255) = null,
@vchOption2 varchar(255) = null,
@vchOption3 ntext = null,
@vchOption4 varchar(255) = null
AS
declare @a int

select @a = iCategoryId from lookuptable
where vchCategory = @vchOption2

insert into sometable(iCompanyId,
iCategoryId,
tNotes,
dtInsertDate,
dtUpdateDate)
values(cast(@vchOption1 as int), @a, @vchOption3, getdate(), getdate())

That should work, I've done something similar a number of times with no problems:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=14134

You can directly INSERT a text/ntext value into a table. It's MANIPULATING a text value that's difficult...you're not actually doing that in your code. The other thing about the request is the ability to EXECUTE large text, which your code doesn't do either.

Secondly, this execution needs to happen on the server, not the client. The SQL statement is being dynamically generated for the purposes of executing it. The client really has no interaction with this.

Theoretically the way you're doing this can be modified to work within a SQL Server stored procedure, but it requires using the sp_OA methods to create an ADO object to connect to the server....which is kinda strange to do since it's already running in a stored procedure!

And having to use COM calls for this is unnecessary overhead, and really complicates the process (just as much as the generate sql-bcp out-run file using osql approach does) I'm also not to sure that sp_OA procedures can handle a string larger than 255 characters....meaning that you couldn't even PASS a large text string to a stored procedure!

It's not that anyone here has given up, far from it. This is not the first time that this has been discussed in the forums:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10245

The discussion in this particular thread is pretty involved and very innovative, but unfortunately doesn't quite nip the problem in the bud. Ilya has the best solution so far, and you should look at his code to see if it will work for you (Kevin)

ppuar
Starting Member

28 Posts

Posted - 2002-04-08 : 10:22:38

I don't understand what you are talking about? Maybe, I am missing the point, but I thought this solves the problem. I insert text from the client side using ADO and I use an ADO select statement to grab the text on the client side. If there are any changes, I send the complete text back to sp and it over-write the current field with updated data. Does this approach not work? Otherwise, please explain what is the point I am missing?

Thanks
P

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-08 : 11:19:10

Two things: where in your VB/ADO code are you executing the returned SQL statement? All I see is the call to the stored procedure, which only inserts a SQL command, but doesn't execute it.

The other thing is that this REQUIRES a client connection of some kind, using ADO. For a process that generates dynamic SQL and executes it on the server, this is less than optimal. It creates network traffic that is unnecessary, and it can't be reliably automated (like in a SQL Server job, for instance) In effect, the client is creating the dynamic SQL and just passing it on to the server.

Take a look at this article:

http://www.sqlteam.com/item.asp?ItemID=2955

One of the shortcomings of the technique it uses is that a cross-tab with hundreds of columns will exceed the 8,000 character limit. Kevin is trying to find a way around that (don't know if he's creating cross-tabs, but the idea is the same) It really doesn't make sense to generate the SQL, put it into a table, have a client connection read it using ADO, just to SEND IT RIGHT BACK to the server for execution.

It makes more sense to use ADO on the server side, it would at least remove the need for any network transport. However, you then have to contend with the fact that the sp_OA procedures that work with COM are limited in the type of data they can pass and receive. I don't believe they can handle strings larger than 255 characters, so it's actually a step backwards. And even THEN, you cannot manipulate, modify, or declare a text variable! (is there an echo in here?) so you can't put the text from the table into it anyway.

Let me clarify my earlier statement: your solution WORKS, but it doesn't work within the confines of the situation Kevin needs it to work in.

Edited by - robvolk on 04/08/2002 11:20:36

ppuar
Starting Member

28 Posts

Posted - 2002-04-08 : 11:36:43

The following is what executes my stored proc:

dbCommand.Execute

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-08 : 12:09:53

OK, where does the the SQL get executed? That's what I'm talking about. From what I can tell, the stored procedure you're passing to only ACCEPTS a text parameter, it does not EXECUTE it as a SQL statement. The last two lines are:

writetext sometable.tNotes @tpointer @vchOption3
EXEC sp_dboption '????', 'select into/bulkcopy', 'false'

WRITETEXT doesn't execute a SQL statement, and the following EXEC runs sp_dboption, not the SQL contained in the text parameter. There's nothing I can see that says "EXECUTE (@textVar)", which is what Kevin is trying to do.

ppuar
Starting Member

28 Posts

Posted - 2002-04-08 : 12:46:01

EXEC sp_dboption '????', 'select into/bulkcopy', 'true'

declare @tpointer binary(16)

select @tpointer = textptr(tNotes) from
test where iNoteId = @@IDENTITY

writetext test.tNotes @tpointer @vchOption3

EXEC sp_dboption '????', 'select into/bulkcopy', 'false'

This works - First I insert a row into the test table with text column being ''. Next I grab a pointer to this text column and use the writetext command to insert the text that is passed to vchOption3. I have tried this with 5 pages of text and it works great. I don't know what else to explain, but I can say it works for me and I don't have problems inserting huge amounts of text. The writetext command does not need a execute in front of it.

P

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-08 : 13:11:10

Well, sorry, but you did miss the point from before. We're not trying to STORE large text, we are trying to EXECUTE a SQL statement that's longer than 8000 characters. You can do this by constructing a SQL string and then doing something like this:

CREATE PROCEDURE RunSQL @sqlstring text AS
EXEC (@sqlstring)
GO
EXECUTE RunSQL 'SELECT * FROM pubs..authors'

That's just a basic example. If you look at the cross-tab article you'll see how it generates a large SQL statement that is executed using this method. BUT it is limited to 8000 characters. This is because only text variables can hold more than 8000 characters, but text variables cannot be modified, therefore they cannot be built dynamically. This limitation makes them useless for building large SQL statement strings that exceed 8000 characters.

The only workaround for that is to break the SQL string into multiple 8000 character chunks (@sql1, @sql2, etc.) and then EXEC (@sql1 + @sql2 + @sql3).

Secondly, I posted that you don't have to do it the way you are now, the following code will also work:

CREATE PROCEDURE test
@vchOption1 varchar(255) = null,
@vchOption2 varchar(255) = null,
@vchOption3 ntext = null,
@vchOption4 varchar(255) = null
AS
declare @a int

select @a = iCategoryId from lookuptable
where vchCategory = @vchOption2

insert into sometable(iCompanyId,
iCategoryId,
tNotes,
dtInsertDate,
dtUpdateDate)
values(cast(@vchOption1 as int), @a, @vchOption3, getdate(), getdate())

Since all you are doing is inserting the value, this syntax should work fine. Unless you've tested this and it doesn't work; when I tested it it ran fine for me on my setup. It doesn't require a text pointer at all.

ppuar
Starting Member

28 Posts

Posted - 2002-04-08 : 13:14:51

Ok - thanks for correcting me

p

motokevin
Starting Member

36 Posts

Posted - 2002-04-09 : 14:06:16

Here's how I was able to work around the VarChar(8000) limitation and manipulate text to create a dynamic SQL statement and execute it:

My only beef with this solution is that I have to keep a text file (c:\DoNotDeleteThisFile.txt) sitting on the server ( I would prefer to create it, use it, and then delete it )

Declare @TextFileName varchar(100)
Set @TextFileName = 'c:\DoNotDeleteThisFile.txt' -- on server

--A bunch more declarations

Declare MyCursor Cursor Local Static
For
Select Stuff
From TheTable

Open MyCursor

Fetch First From MyCursor
Into @SomeVariables

While @@Fetch_Status = 0

Begin-----------------------------------------------------

-- code
exec sp_AppendToFile @TextFileName, @VarChar1
-- code
exec sp_AppendToFile @TextFileName, @VarChar2
-- code
exec sp_AppendToFile @TextFileName, @VarChar3
-- etc...

Fetch Next From MyCursor
Into @SomeVariables

End-----------------------------------------------------------------------------------------------------------

Close MyCursor
Deallocate MyCursor

exec xp_cmdshell 'isql /U username /P password /i c:\DoNotDeleteThisFile.txt'

I had to get these procedures from the net:

quote:
CREATE PROCEDURE sp_AppendToFile(@FileName varchar(8000), @Text1 varchar(8000)) AS
DECLARE @FS int, @OLEResult int, @FileID int

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
IF @OLEResult <> 0 PRINT 'OpenTextFile'

--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'WriteLine'

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

quote:
CREATE PROCEDURE xp_cmdshell(@cmd VARCHAR(255), @Wait INT = 0) AS
--Create WScript.Shell object
DECLARE @result INT, @OLEResult INT, @RunResult INT
DECLARE @ShellID INT

EXECUTE @OLEResult = sp_OACreate 'WScript.Shell', @ShellID OUT
IF @OLEResult <> 0 SELECT @result = @OLEResult
IF @OLEResult <> 0 RAISERROR ('CreateObject %0X', 14, 1, @OLEResult)

EXECUTE @OLEResult = sp_OAMethod @ShellID, 'Run', NULL, @cmd, 0, @Wait
IF @OLEResult <> 0 SELECT @result = @OLEResult
IF @OLEResult <> 0 RAISERROR ('Run %0X', 14, 1, @OLEResult)
--If @OLEResult <> 0 EXEC sp_displayoaerrorinfo @ShellID, @OLEResult

EXECUTE @OLEResult = sp_OADestroy @ShellID

RETURN @result

** Rob, Where can I find Ilya's solution?

quote:
Ilya has the best solution so far

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-09 : 14:48:03

It's in this thread, near the end:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10245

It'll carve up a text value into smaller pieces and insert it into a table.

Just out of curiosity, is this a typo:

-- code
exec sp_AppendToFile @TextFileName, @VarChar1
-- code
exec sp_AppendToFile @TextFileName, @VarChar2
-- code
exec sp_AppendToFile @TextFileName, @VarChar3

I'm wondering only because there are three different varchar variables, and if the string is built like this, why not do EXEC (@VarChar1 + @VarChar2 + @VarChar3)?

If you use Ilya's technique, and insert SQL statements into a table, you can output the SQL file using bcp, and won't need to use any COM calls.

Give me a couple minutes, I think I have an idea I need to test. Stay tuned.

motokevin
Starting Member

36 Posts

Posted - 2002-04-09 : 15:00:21

There were no typo's ( but alot of code has been removed ).

If you'll notice though, it is in a loop. So, the problem I ran into with EXEC (@VarChar1 + @VarChar2 + @VarChar3) is that I cant EXEC part now (first time through the loop), and part later (next time through the loop.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources