Problem with INSERT INTO with string literal

Problem with INSERT INTO with string literal

Post by Dann » Wed, 03 Mar 2004 03:32:42



Hi.

I am running SQL Server 2000 and I have a table with a PK column named Url.
I get a timeout when I try and insert the following url into the table:

http://www.msdn.microsoft.com/architecture/application/default.aspx?p...
Here is the full command:

INSERT INTO UrlTable (Url) VALUES
('http://www.msdn.microsoft.com/architecture/application/default.aspx?p...
library/en-us/security/security/cryptography__cryptoapi__and_capicom.asp')

Note that I surround the string with quotes to indicate that it is a string
literal. Still, the operation is timing out for some unknown reason.

Does the string contain special characters that might be causing problems
with the INSERT statement? Do I need to canonicalize the string? If so, how?

Any help would be much appreciated!

Thanks!

-Danny-

 
 
 

Problem with INSERT INTO with string literal

Post by Simo » Wed, 03 Mar 2004 04:36:33


This may be a case where the use of a natural primary key is not a good
idea.

Perhaps using an identity column for a primary key would be a better choice?


Quote:> Hi.

> I am running SQL Server 2000 and I have a table with a PK column named
Url.
> I get a timeout when I try and insert the following url into the table:

http://www.msdn.microsoft.com/architecture/application/default.aspx?p...
Quote:> Here is the full command:

> INSERT INTO UrlTable (Url) VALUES

('http://www.msdn.microsoft.com/architecture/application/default.aspx?p...
Quote:> library/en-us/security/security/cryptography__cryptoapi__and_capicom.asp')

> Note that I surround the string with quotes to indicate that it is a
string
> literal. Still, the operation is timing out for some unknown reason.

> Does the string contain special characters that might be causing problems
> with the INSERT statement? Do I need to canonicalize the string? If so,
how?

> Any help would be much appreciated!

> Thanks!

> -Danny-


 
 
 

Problem with INSERT INTO with string literal

Post by Quentin Ra » Wed, 03 Mar 2004 08:33:37


Danny,

your insert itself does not have a problem, neither does it timeout by
itself.  Create a new table and run the code, you will see it.

What I suspect is that you have too many indexes in the same table, possibly
with not well assigned fill factors, so that the insert would cause too much
index update related time.

Nevertheless, PK for such long value is not too good an idea as the other
post pointed out.

Quentin


Quote:> Hi.

> I am running SQL Server 2000 and I have a table with a PK column named
Url.
> I get a timeout when I try and insert the following url into the table:

http://www.msdn.microsoft.com/architecture/application/default.aspx?p...
brary/en-us/security/security/cryptography__cryptoapi__and_capicom.asp
Quote:> Here is the full command:

> INSERT INTO UrlTable (Url) VALUES

('http://www.msdn.microsoft.com/architecture/application/default.aspx?p...
Quote:> library/en-us/security/security/cryptography__cryptoapi__and_capicom.asp')

> Note that I surround the string with quotes to indicate that it is a
string
> literal. Still, the operation is timing out for some unknown reason.

> Does the string contain special characters that might be causing problems
> with the INSERT statement? Do I need to canonicalize the string? If so,
how?

> Any help would be much appreciated!

> Thanks!

> -Danny-

 
 
 

Problem with INSERT INTO with string literal

Post by Louis Davidso » Wed, 03 Mar 2004 11:12:54


Is this really a table that just holds a single column URL?  Or is there
more to it.  Can you post the entire DDL?  How many rows are in the table?
Billions?  Or just a few?   What is the average size of a value in the
table?  What if you insert another string?

This string shouldn't be a problem, and really, unless your values are
really really large, shouldn't be too much trouble to index.

--
----------------------------------------------------------------------------
-----------

Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)


Quote:> Hi.

> I am running SQL Server 2000 and I have a table with a PK column named
Url.
> I get a timeout when I try and insert the following url into the table:

http://www.msdn.microsoft.com/architecture/application/default.aspx?p...
Quote:> Here is the full command:

> INSERT INTO UrlTable (Url) VALUES

('http://www.msdn.microsoft.com/architecture/application/default.aspx?p...
Quote:> library/en-us/security/security/cryptography__cryptoapi__and_capicom.asp')

> Note that I surround the string with quotes to indicate that it is a
string
> literal. Still, the operation is timing out for some unknown reason.

> Does the string contain special characters that might be causing problems
> with the INSERT statement? Do I need to canonicalize the string? If so,
how?

> Any help would be much appreciated!

> Thanks!

> -Danny-

 
 
 

Problem with INSERT INTO with string literal

Post by Dann » Wed, 03 Mar 2004 12:26:19


Thanks for all your very helpful comments and tips!

1.) The table holds 4 columns: Url - nvarchar(450) - designed to hold
Unicode URLs, and three smallints (identifiers and flags).

2.) When the insert started giving this problem, the table had about 14000
rows.

3.) The average size of a row in the table is about 150 bytes.

I just changed the table such that the Url column is no longer a PK - I now
create another index specifically for the Url column. The database is
indexing as I write and I will post my findings if the problem repros (or
hopefully not).

Thanks!

-Danny-


> Is this really a table that just holds a single column URL?  Or is there
> more to it.  Can you post the entire DDL?  How many rows are in the table?
> Billions?  Or just a few?   What is the average size of a value in the
> table?  What if you insert another string?

> This string shouldn't be a problem, and really, unless your values are
> really really large, shouldn't be too much trouble to index.

> --
> --------------------------------------------------------------------------
--
> -----------

> Compass Technology Management

> Pro SQL Server 2000 Database Design
> http://www.apress.com/book/bookDisplay.html?bID=266

> Note: Please reply to the newsgroups only unless you are
> interested in consulting services.  All other replies will be ignored :)



> > Hi.

> > I am running SQL Server 2000 and I have a table with a PK column named
> Url.
> > I get a timeout when I try and insert the following url into the table:

http://www.msdn.microsoft.com/architecture/application/default.aspx?p...
Quote:> > Here is the full command:

> > INSERT INTO UrlTable (Url) VALUES

('http://www.msdn.microsoft.com/architecture/application/default.aspx?p...
library/en-us/security/security/cryptography__cryptoapi__and_capicom.asp')

- Show quoted text -

Quote:

> > Note that I surround the string with quotes to indicate that it is a
> string
> > literal. Still, the operation is timing out for some unknown reason.

> > Does the string contain special characters that might be causing
problems
> > with the INSERT statement? Do I need to canonicalize the string? If so,
> how?

> > Any help would be much appreciated!

> > Thanks!

> > -Danny-

 
 
 

1. Insert a long value ORA-01704: string literal too long

hi everyone,
i've defined a table with a LONG column to store text containing
more than 4000 car.
The problem is that when i do an insert like :

insert into my_table (long_value) values ('patati.... patata')

i have the oracle error : ORA-01704: string literal too long...

if 'patati....patata' has less than 4000 car. : it's ok.

What's the problem ? is it that i use quotes ?
it seems that 'patati....patata' is considered like a VARCHAR2 data.

thank u for help

Cyril

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Repost: Checking for error: "A clause of the query contained only ignored words"

3. string literal error

4. Help needed.

5. How to Change literal strings in OLE DB?

6. Time Question

7. oracle - string literal to long

8. Oracle tables - native format

9. java.sql.SQLException: ORA-01861: literal does not match format string

10. ORA-01704: string literal too long

11. ODBC Error: String Literal to long?

12. ORA-01704: string literal too long