Having issue inserting large text colum into DB

I have a large text colum I am trying to insert into a DB
This colum is about 800 chars longs

I have set the colum type in the table to text

I have set the table option for text in row to on

I have set the table option for text in row to 1000

But it is still chopping the text at the 256 char mark on insert.

Anyone have any ideas ?? This is SQL 2000.

ChrisAre you sure it does? How do you check for the length of the inserted value? By doing SELECT?

Try this:

select datalength(<your_text_field>) from <your_table>

And why do you need TEXT IN ROW setting? Are you searching on that field? If that's the case, - you should implement Full-Text Search.|||In Query Analyzer:

1) press Shift-Ctrl-O to bring up the Options dialog.
2) Click on the Results tab
3) Check the value of the Maximum Characters per Column

If it is too small, make it larger, but keep in mind that this is a VERY RAM expensive operation in the GUI. Don't make it any larger than 255 unless you really need it!

-PatP|||In Query Analyzer:

1) press Shift-Ctrl-O to bring up the Options dialog.
2) Click on the Results tab
3) Check the value of the Maximum Characters per Column

If it is too small, make it larger, but keep in mind that this is a VERY RAM expensive operation in the GUI. Don't make it any larger than 255 unless you really need it!

-PatP
That's why I suggested to use DATALENGTH, because it does not rely on this setting. Besides, what if the value that is inserted greater than 8192? Or you'd think that you inserted 8192 characters?|||That's why I suggested to use DATALENGTH, because it does not rely on this setting. Besides, what if the value that is inserted greater than 8192? Or you'd think that you inserted 8192 characters?You did fine, as far as giving them what they needed to figure out the answer. I just like my solution better because then they can SEE the answer, which is often better than being able to deduce it.

-PatP

having difficulty inserting into the database table

I created a web form where the user fills in some data and when he submits the form, I do an insert into he database table. The problem is, how can I get the data from the form into the insert statement?. here is the code:
Dim Message As String
Dim connStr As String
Dim myConnection As SqlConnection
Dim mySqlCommand As SqlCommand
connStr = "server=SIMI\VSdotNET;Trusted_Connection=yes;database=AeroSea"
myConnection = New SqlConnection(connStr)
mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,name.text)", myConnection)

If I execute the above code, then nothing gets updated. When I change the insert staement into the following,
mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,'myname')", myConnection) then,
value 1 for customerID field and myname in the namefield is added.
I know I am doing something stupid, but can't figure it out.
I am confused. please help me.One possibility is this (presuming textbox is name):

mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,'" + name.text + "')", myConnection)

Better,use parameters, as the code above is subject to SQL Injection attacks, and as written will fail if the name.Text is "O'Reilly".|||Thanks, I used the parameters and it works fine.

Having a problem inserting products

I am trying to write a bit of code that I can pass a brand name to. If the brand name exists I want to return the brandid to the calling middle tier. If the brand id does not exist I want to insert and then return the new brand id. The code below works unless the brand does not exist. Then it inserts, and I get an application exception. Next time I run the code it continues on until the next time it has to do an insert. So the inserts are working, but getting the value back is resulting in an application excetio.

Middle Tier Function (

privatestaticint GetBrandForProduct(clsProduct o)

{

int brandid = -1;// If the brand name comes in blank use the first word of the overstock product

o.BrandName = o.BrandName.Trim();

// if we do not have a brand for this productif (o.BrandName.Length == 0)return -1;Database db =CommonManager.GetDatabase();;try

{

// Get the brand id for this brand name// If it does not exist we will add it and STILL return a brand idobject obj = db.ExecuteScalar("BrandIDGetOrInsert", o.BrandName);string catid = obj.ToString(); *** FAILING LINE ***returnConvert.ToInt32(obj.ToString());

}

catch (Exception ex)

{

throw ex;return -1;

}

return brandid;

}

Stored Procedure: --------------------------------------------------

ALTER

PROCEDURE [dbo].[BrandIDGetOrInsert]-- Add the parameters for the stored procedure here

@.brandnameparm

varchar(50)

AS

BEGIN

-- SET NOCOUNT ONSELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm)-- If we found a record, exit

if@.@.rowcount> 0return-- We did not find a record, so add a new one.

begin

insertinto brands(Brandname)values(@.brandnameparm)

end

SELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm)

END

Hi Dear,

You have not mention that What is the Exception Message? I will be in better position to answer if you share Exception Message also...

but the One thing that seems wrong in your SP is

if@.@.rowcount> 0return

-- We did not find a record, so add a new one.

begin

insertinto brands(Brandname)values(@.brandnameparm)

end

@.@.rowcount return 0 if select statement didn't find any result......

so i think this check will be like this

if@.@.rowcount = 0return

-- We did not find a record, so add a new one.

begin

insertinto brands(Brandname)values(@.brandnameparm)

end

change this thing in your Store Procedure , if problem doesn't solve ..then post the Exception message...

Thank You

Best Regards,

Muhammad AKhtar Shiekh

|||

Why would I do that? If the rowcount > 0 then I am happy with the first select and I want the SP to exit. It will have returned the brandid that I need. If the rowcount = 0 then I want to do the insert.

The ASP.NET codes an 'object not defined' exception. The brandid is not being returned after the insert - possible two rows are being returned also which I think an executescalar would not be happy with. How do I get only row to return in either case?

|||

patrick24601:

Why would I do that? If the rowcount > 0 then I am happy with the first select and I want the SP to exit. It will have returned the brandid that I need. If the rowcount = 0 then I want to do the insert.

That's what i am saying but there is contradiction in Your SP...it is doing this

if@.@.rowcount> 0return

-- We did not find a record, so add a new one.

begin

insertinto brands(Brandname)values(@.brandnameparm)

end

It is actually inserting when RowCount is greater then 0 ( Not equal to 0)

......

patrick24601:

The ASP.NET codes an 'object not defined' exception. The brandid is not being returned after the insert - possible two rows are being returned also which I think an executescalar would not be happy with. How do I get only row to return in either case?

You can try this code,

ALTER

PROCEDURE [dbo].[BrandIDGetOrInsert]-- Add the parameters for the stored procedure here

@.brandnameparm

varchar(50)ASBEGIN-- SET NOCOUNT ONIFnotexists(SELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm))begininsertinto brands(Brandname)values(@.brandnameparm)endelseSELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm)END

Thanks

Best Regards,

Muhammad AKhtar Shiekh

|||

sorry SP is no correct in above post, Remove the else part in the sp

ALTERPROCEDURE [dbo].[BrandIDGetOrInsert]

-- Add the parameters for the stored procedure here

@.brandnameparm

varchar(50)ASBEGIN-- SET NOCOUNT ONIFnotexists(SELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm))begininsertinto brands(Brandname)values(@.brandnameparm)endSELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm)END|||

i think the problem lies with this:

if (o.BrandName.Length == 0)return -1;

Database db =CommonManager.GetDatabase();

;

Notice the stray ; also the if statement is missing some braces

if (o.BrandName.Length == 0)
{
return -1;
}

Thats what looks wrong to me

having a constraint on a adding a record

how can i make a stored procedure for inserting a record in the database where it detect if the title is already present and then disregard insertion and just update the number of copies in that specific record?

i would be so thankful for any hel out there...tnx!

insert into yourtable(pk, col1, col2, col3)
select @.pk, @.col1, @.col2, @.col3
where not exists (select * from yourtable x where x.pk = @.pk)

if @.@.rowcount = 0 -- nothing inserted
begin
-- so do the update
update yourtable
set col1 = @.col1,
col2 = @.col2,
col3 = @.col3
where pk = @.pk
end|||

It would help to post the schema of the table or relevant columns. You can do something like below:

begin tran

if not exists( select * from titles with(updlock) where title = @.title )

insert into titles

values(....)

else

update titles

set numcopies = numcopies + 1

where title = @.title

commit

You need to add error handling and other necessary checks to the code.

|||Arguably the best way to do this would be using an isntead of INSERT trigger. In the trigger, run the if exists statement in the above posts, and if so, update, otherwise insert. That way you can rely on the code running when anything is inserted without having to update all your stored procedures to make that change.|||Sure, this is one way to do it. But you will have to watch out for performance issues. INSTEAD OF trigger requires materialization of the rows in the inserted/deleted tables. And this can be expensive depending on the number of rows being inserted. And for single row inserts the overhead of the trigger is probably unnecessary and it is easier to modify the SP that performs the insert into the base table.sql

Handling a double or float value for inserting into DataTime field.

Hi,

I was trying to enter the non-normalised exponential format of double or float value into the DataTime field in my data base. It is allowing to store any kind of data passed to this field. If the same non-normalised exponential value for eg: 4.235E-329 is passed to float or double field we are getting a TDS error but when same thing is used to store in DateTime field it is simply inserting the value.

Now my concern is that SQL Server 2005 should give me such kind of exception when I am trying to insert in-valid double or float value to DateTime field. Is this a bug in SQL Server 2005? Please kindly help me how to implement this one.

Thanks,

I get the same results on SQL 2000 and SQL 2005 when I run the script below.

I get
Server: Msg 168, Level 16, State 1, Line 2
The floating point value '4.235E-329' is out of the range of computer representation (8 bytes).

and the value 1900-01-01 00:00:00.000 is inserted into the datetime field and the value 0.0 gets inserted into the float field.

Can you please clarify your question or provide a repro?

Thanks!
use tempdb
go

create table t1(d datetime)
go

create table t2(f float)
go

insert into t1 (d) values (4.235E-329)
go

insert into t2 (f) values (4.235E-329)
go

select * from t1
go

select * from t2
go

drop table t1
go

drop table t2
go

|||

Unfortunately, SQL Server is a bit inconsistent in its compliance with the IEEE floating point standards. The value 4.235E-329 is less than 2^(-1023), and it can only be represented as a reduced-precision "denormalized" value. In some situations, these values will be understood and used correctly, and in others they will generate errors. The best advice I can offer is to be cautious with them, unfortunately. In SQL Server 2000, for example (and I expect in 2005 as well), the first code snippet here will succeed, but the second will fail:

declare @.f float
set @.f = 1e-307
set @.f = @.f/100000000000000
select @.f
go

declare @.f float
set @.f = 1e-322
select @.f
go

Steve Kass
Drew University

|||Problem Repro steps:

Create table tblTest1(id int, fld1 float, dtfld datatime)
Go

insert into tblTest1 values(10, 4.235E-329, '10/29/2005')
Go

This insert will give an error
Server: Msg 168, Level 16, State 1, Line 2
The floating point value '4.235E-329' is out of the range of computer representation (8 bytes).

If we selected the data then we will see there will be no record inserted.

now change the insert statment as

insert into tblTest1 values(10, 4.23, 4.235E-329)
Go

Then this will say that 1 record inserted and when we select the table to display the rows we will see that the record inserted. My Question is that if we insert a wrong non-normalised (Exponential) double or float value into float or double column it is raising an error and the trasaction is rolled back, but if we have given the same value to a datatime field, it is not raising any error and the record is inserted. We want to know is that a Bug in SQL Server 2000 / 2005.

Our main concern is that we want to get a such a kind of exception when we try to insert a wrong non-normalised (Exponential) value into datatime field and the transaction should be rolled back. Is there any work around to get rid of this issue, please mention me

Thanks|||When I ran your scripts on SQL 2005, both insert statements would throw a warning (msg 168), and then the insert would continue and insert one row into the table. The warning indicates that there is an UNDERFLOW for the floating point value, and the value is turned into 0. Is this not the result you are seeing?

Because floating point is imprecise itself, we chose to map denormalized values to zero. Note that the values inserted into the table are actually 0, not the denormalized value. I understand that you might want to see an error, but such behavior would potentially break other existing applications.

Regards,
Jun
|||Hi Jun Fang,

According to your message you said that a warning(msg 168) is thrown when the transaction is happening, please kindly help us how to trap this warning message from the ADO.Net 2.0 (front end application) so that we can show the message and roll back the entire transaction for this kind of scenario.

Thanks

handle errors in trigger

How to handle error in trigger.
i m inserting record in other table in after insert trigger. but if insert
statement in trigger has error, procedure quits with error and i cannot use
@.@.error to check itThats an artifical error, but that the way you could handle it:
Create Table SomeTable
(
SomeColumn INT
)
Go
ALTER TRIGGER SomeTrigger
on SomeTable
FOR INSERT
AS
BEGIN
RAISERROR('SomeError',16,1)
PRINT @.@.ERROR
IF @.@.ERROR <> 0
PRINT 'Seems that an error occured.'
END
INSERT INTO SomeTable VALUES (1)
DELETE FROM SomeTable
SELECT * FROM SomeTable
Keep in mind that some error aren=B4catchable due to the severity.
I suggest taking a look in Erland=B4s Error handling script
http://www.sommarskog.se/error-hand...triggercontext.
HTH, jens Suessmeyer.|||I don't know exactly what you're doing but there are a couple of things
you may want to check before you do an insert.
If you're using variables in your values list check them before you
insert if they aren't null in case your table has colums with not null
defined.
But in general do have a look at Erland's article.|||Ya variable is null and table does not allow null, but error should be
trapable isnt it ?
"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1137149721.665336.187300@.g44g2000cwa.googlegroups.com...
> I don't know exactly what you're doing but there are a couple of things
> you may want to check before you do an insert.
> If you're using variables in your values list check them before you
> insert if they aren't null in case your table has colums with not null
> defined.
> But in general do have a look at Erland's article.
>|||Error handling in sql server 2000 within triggers is tricky business,
have a look at Erland's article.
http://www.sommarskog.se/error-hand...triggercontext.
But since you know that the variable is null then something like this
may avoid the error:
DECLARE @.var int
SET @.var = ISNULL(@.var, 1) /* the variable will be assigned the value
of 1 if it is null
or
IF(@.var) IS NULL
BEGIN
-- write all variables to a log table
INSERT INTO ...
RETURN
END|||The AFTER trigger fires *after* the insert - hence the name. In case of a
constraint violation (such as the nullability constraint) the the AFTER
trigger will not fire at all, since the insert is aborted.
Consider using an INSTEAD OF trigger or better yet - set values properly
before inserting them or use a deafult.
ML
http://milambda.blogspot.com/|||who says the variable is coming from inserted?|||I was guessing based on OP's narrative.
ML
http://milambda.blogspot.com/|||from OP
> but if insert statement in trigger|||> but if insert statement in trigger