@@Error on Large Text Field Insert

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)

@@Error on Large Text Field Insert

Author

Topic

drsloat
Starting Member

45 Posts

Posted - 2002-05-16 : 11:06:44

I have a table in a SQL 2000 database that has multiple text columns. In one stored procedure I perform a simple insert into this table and then select @@Error and @@Identity as output parameters.

If I insert small values into the text fields everything works as expected, but when I insert values w/ approx. 15000 characters there is a problem. The row is inserted into the table, but @@Error and @@Identity return values like:

40012984
40076152

These values change each time the procedure is run. Any ideas on what is causing this error or what the error means? I'm executing the procedure through AD0 2.6 and am not sure if I should blame ADO. I have experienced buggy behavior w/ ADO and large text fields before, so this wouldn't surprise me.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-16 : 11:16:15

mind showing me the storedproc? can't tell if it's a problem with your procedure without seeing it. Although I definately know what you mean with ADO and large text values.

drsloat
Starting Member

45 Posts

Posted - 2002-05-16 : 11:32:40

I think I have found some resolution.

Here's what didn't work (minus some fields for easy reading):

@CampaignName varchar(255),
@GeneratedHTML text,
@GeneratedText text,
@CampaignLogID int OUTPUT
@ErrorCode int OUTPUT

Insert Into CampaignLog (CampaignName, GeneratedHTML, GeneratedText,...LaunchStatus)
Values(@CampaignName, @GeneratedHTML,@GeneratedText,...@LaunchStatus)

Set @ErrorCode = @@ERROR
Set @CampaignLogID = @@Identity

So I took the output params out of the procedure and instead selected the values at the end:

Select @ErrorCode as 'ErrorCode', @CampaignLogID as 'CampaignLogID'

Now when I inspect the returned values in my recordset I see the correct results. So I figure I'm right to blame ADO and I'll add this technique to my bad of tricks.

drsloat
Starting Member

45 Posts

Posted - 2002-05-16 : 12:25:07

that's my "bag" of tricks

(How do I put in those cool beer smileys?)
:B

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-16 : 12:34:12

I asked the

to see if it would let me tell you how to make a

. It said

. It told me if I told you it'd give me a

and eventually make me

, that

little

Edited by - M.E. on 05/16/2002 12:35:18

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-16 : 12:45:17

The beer smiley's are an initiation ritual, we can't tell ya cause, well, it wouldn't mean anything to ya if we did!

I just hope that nobody SNIPES me on this thread...

drsloat
Starting Member

45 Posts

Posted - 2002-05-16 : 16:59:03

I figured it out. I haven't posted very much, but I do read through interesting looking posts fairly often. I remembered reading the secret a while back! Keep up the good work, this site rocks!

If the initiation involves

, I'm sorry I missed it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources