Database field length problem

Hi everyone
I had an access database running as the source for a website but it
has become too large to run correctly so it has been ported to MS-SQL
the problem is that 4 of the fields were Memo fields in access and as
such are 5000+ characters long each this overflows the allowed size on
the SQL server (8192)

Is there a way round without splitting those 4 fields into seperate
tales?? as this would cause a truly major re-write of the website

Thanks for any help
Further details available if requiredIf you use VarChar can you not set the max field length to 8000 characters?

I can't believe SQL has such limits, surely there's a way to automatically
use two rows for one record, or does this require additional programming in
ASP?

Cheers, Ash

"Peter" <peter@.iib.ws> wrote in message
news:81307dbc.0406250528.ae1d3d@.posting.google.com ...
> Hi everyone
> I had an access database running as the source for a website but it
> has become too large to run correctly so it has been ported to MS-SQL
> the problem is that 4 of the fields were Memo fields in access and as
> such are 5000+ characters long each this overflows the allowed size on
> the SQL server (8192)
> Is there a way round without splitting those 4 fields into seperate
> tales?? as this would cause a truly major re-write of the website
> Thanks for any help
> Further details available if required|||Hi there
When i try to import a flat text file where i have used the transform
tool to delare field sizes of 6000 for the four fields i get the
following error if they are varchar

cannot create a row of size 8366 which is greater than the allowable
maximum of 8060

If i map them as ntext i dont get an error but the data is truncated

Any ideas
Peter

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Peter Winning (peter@.iib.ws) writes:
> When i try to import a flat text file where i have used the transform
> tool to delare field sizes of 6000 for the four fields i get the
> following error if they are varchar
> cannot create a row of size 8366 which is greater than the allowable
> maximum of 8060
> If i map them as ntext i dont get an error but the data is truncated

ntext is probably the way to go, since that is the only way to have
more than 8060 bytes of data on one row.

Why your ntext data is truncated I don't know, but then again I don't
know how import the data. A CREATE TABLE definition and a sample data
file could help. (You would have to pack the data file into a zip
file, since it surely would be wrecked by news transport, if you
posted it as text.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi
I am using the enterprise manager to upload the database and so far all
attempts no matter how the fields are transformed are still failing
I have tried mapping the fields as vchar, nvchar and ntext with the same
results in every case ie failure to complete or data truncation this has
also been attempted on the server itself
The upsizing wizard in access also failed to do the job

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Peter Winning (peter@.iib.ws) writes:
> I am using the enterprise manager to upload the database and so far all
> attempts no matter how the fields are transformed are still failing
> I have tried mapping the fields as vchar, nvchar and ntext with the same
> results in every case ie failure to complete or data truncation this has
> also been attempted on the server itself
> The upsizing wizard in access also failed to do the job

I have no idea what Enterprise Manager is up to when it imports data;
I didn't even know that it had a function for it, and even less have I
used it.

My general experience of EM, though, is that it tends to occlude some
syntax in order to be helpful, when things go over its head, it leaves
you alone in the dark.

I would try to import the file with BCP, but since I don't know how your
text file looks like, I cannot suggest the exact command line. I repeat
from my previous posting:

A CREATE TABLE definition and a sample data file could help. (You would
have to pack the data file into a zip file, since it surely would be
wrecked by news transport, if you posted it as text.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you
I have to confess that i am a complete novice with sql what is "BCP" and
where would i find some help in how to use it.
The database is a flat field one with about 30 fields 4 of which are
memo fields with very large amounts of data in each

Regards
Peter

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Peter Winning (peter@.iib.ws) writes:
> I have to confess that i am a complete novice with sql what is "BCP" and
> where would i find some help in how to use it.

BCP is a command line tool that permits you to load large amount of
data from files. The files can be text files or binary. BCP is a bit
restricted in that the file has to be fairly square. That is, it not able
to sort out headers, unless you can find a square hole to put them in.

You can read more about BCP in Books Online.

Another alternative is DTS (Data Transformation Service), which is a more
versatile load tool, which I have never used my self though.

> The database is a flat field one with about 30 fields 4 of which are
> memo fields with very large amounts of data in each

Well, it is up to you. If you don't want to post a CREATE TABLE statement
for your table and a sample data file, you don't have to. But then you will
have to find out how to load your file with BCP on your own, because I
don't really feel like guessing your table and data.

If you look in the SQL Server Program group, there is "Import and
Export Data". This takes you to the DTS wizard, which may be able to
guide all the way. But as I said, I have not used DTS. Then again,
there are some nice people in microsoft.public.sqlserver.dts who might
be able to help you if you go that way. But they, too, might want the
table definition and sample data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

I don't think the DTS Import wizard will truncate the data if you import it
from an Access database! It could be that you are selecting the data in
Query Analyser which has a configurable value (Tools/Options/Results/Maximum
characters per column).

If you have a table such as

CREATE TABLE MyAccessTable ( id int, Memo1 ntext, Memo2 ntext, Memo3 ntext,
Memo4 ntext )

The you can see the number of characters using:
SELECT id,
datalength(memo1)/2,datalength(memo2)/2,datalength(memo3)/2,datalength(memo4
)/2 FROM MyAccessTable

John

"Peter Winning" <peter@.iib.ws> wrote in message
news:40dc5ecd$0$16435$c397aba@.news.newsgroups.ws.. .
> Hi there
> When i try to import a flat text file where i have used the transform
> tool to delare field sizes of 6000 for the four fields i get the
> following error if they are varchar
> cannot create a row of size 8366 which is greater than the allowable
> maximum of 8060
> If i map them as ntext i dont get an error but the data is truncated
> Any ideas
> Peter
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||I've just looked at our WebMail application we're using, it uses a SQL
database to store the messages which generally are over 8000 characters - it
uses NTEXT as the datatype, with a length of '16', how on EARTH does that
relate to a VARCHAR field that has to be set to 8000??

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9514A53A31CFFYazorman@.127.0.0.1...
> Peter Winning (peter@.iib.ws) writes:
> > When i try to import a flat text file where i have used the transform
> > tool to delare field sizes of 6000 for the four fields i get the
> > following error if they are varchar
> > cannot create a row of size 8366 which is greater than the allowable
> > maximum of 8060
> > If i map them as ntext i dont get an error but the data is truncated
> ntext is probably the way to go, since that is the only way to have
> more than 8060 bytes of data on one row.
> Why your ntext data is truncated I don't know, but then again I don't
> know how import the data. A CREATE TABLE definition and a sample data
> file could help. (You would have to pack the data file into a zip
> file, since it surely would be wrecked by news transport, if you
> posted it as text.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||J. Hall (remove_this_ash@.a-hall.com) writes:
> I've just looked at our WebMail application we're using, it uses a SQL
> database to store the messages which generally are over 8000 characters
> - it uses NTEXT as the datatype, with a length of '16', how on EARTH
> does that relate to a VARCHAR field that has to be set to 8000??

16 is the length of the pointer that is stored within the row. The data
itself is stored on separate pages. A varchar value on the other hand is
stored within the row, and since a row can not host more than 8060 bytes
of data, there is an upper limit.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Excellent thanks for clearing that up.

Many thanks,

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9517834BC4F9AYazorman@.127.0.0.1...
> J. Hall (remove_this_ash@.a-hall.com) writes:
> > I've just looked at our WebMail application we're using, it uses a SQL
> > database to store the messages which generally are over 8000 characters
> > - it uses NTEXT as the datatype, with a length of '16', how on EARTH
> > does that relate to a VARCHAR field that has to be set to 8000??
> 16 is the length of the pointer that is stored within the row. The data
> itself is stored on separate pages. A varchar value on the other hand is
> stored within the row, and since a row can not host more than 8060 bytes
> of data, there is an upper limit.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Database error help

I am working on a website that is currently being hosted. The website was configured for admin,guest and members. Everything was working fine.

I made no changes to code. When I log in as admin I am able to navigate to most of the pages however I keep getting this error.

From the error it seems to say that some of the required fields are null but they are not.

---

Some of the required attributes of Person are NULL in the Persons table

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.InvalidOperationException: Some of the required attributes of Person are NULL in the Persons table

Source Error:

Line 141: While r.Read()
Line 142: If TypeOf r("id") Is DBNull Or TypeOf r("visible") Is DBNull Or TypeOf r("firstName") Is DBNull Or TypeOf r("lastName") Is DBNull Then
Line 143: Throw New InvalidOperationException(Messages.PersonRequiredAttributesMissing)
Line 144: End If
Line 145:

Source File:E:\web\cabletrades\htdocs\App_Code\People\SqlPeopleProvider.vb Line:143

Stack Trace:

[InvalidOperationException: Some of the required attributes of Person are NULL in the Persons table]
 SqlPeopleProvider.GetPersonsForApproval(String transactionTypeId) in E:\web\cabletrades\htdocs\App_Code\People\SqlPeopleProvider.vb:143
 AdminAccountApproval.Page_Load(Object sender, EventArgs e) in E:\web\cabletrades\htdocs\AdminAccountApproval.aspx.vb:42
 System.Web.UI.Control.OnLoad(EventArgs e) +99
 System.Web.UI.Control.LoadRecursive() +47
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

---

Instead of If TypeOf r("id") is dbNull or ....

A person could try this

If r("id") is nothing or .....

hope this helps

DK

Database Efficiency

Hello all,
I am developing a website which may be used by a large number of people in the future and I am concerned about performance.

Is it better to have one table with 50, 000 rows or 5,000 tables with 10 rows each?Is there a way to divide a table in two if the table reaches a certain size?Is there a limit on the size of tables?Is there a limit on the number of tables?Is it possible to create tables from vb.net?Is it possible to program checks into sql server? For example, could I delete data that has passed a certain date or send an automated email when a time is reached?

Thanks for your time,
Padraic

From your questions, it's clear that you have absolutely no idea how to do the task you are set out to do.
You need to spend some time learning the basics. Pick up a copy of INTRODUCTION TO DATABASE SYSTEMS by DATE. It's a text book,. so you can get an older edition for a few bux.
This is not something you can teach yourself by clicking around with wizards in sql server.|||you may want to ask these questions in smaller chunks with more detail. The answer to the last question is yes. Just write a stored procedure that checks a datetime column and deletes anything older than X date (You will want to use the datediff function). You can schedule it to run daily as a job. The answer to your second to last question is yes. You can execute any query statement through vb.net, including:

create table tablename
(
column1 varchar(50)
column2 varchar(50)
)

Database Driven Website using SAN

We want to develop database driven website. because of enormous amount
of data (300GB)
customers's tech advisor is insisting on using SAN instead of direct
attached storage method.
I want to know what issues need to be considered while using SAN in
web solutions.? Which database
server/ product is best suited for such kind of job ? Can anyone
suggests
good informative articles on this subject , available on web.
Thanks in advance
ssp2000"ssp2000" <ssp2000@.hotmail.com> wrote in message
news:78a9f1a5.0309290535.322076fb@.posting.google.com...
> We want to develop database driven website. because of enormous amount
> of data (300GB)
> customers's tech advisor is insisting on using SAN instead of direct
> attached storage method.
> I want to know what issues need to be considered while using SAN in
> web solutions.? Which database
> server/ product is best suited for such kind of job ? Can anyone
> suggests
> good informative articles on this subject , available on web.
Basically any full featured RDBMS product like Oracle or MS SQL Server will
be fine. As far as using a SAN with those, you'll need to follow-up in the
appropriate RDBMS forums ...
--
Tom Kaminski IIS MVP
http://www.iistoolshed.com/ - tools, scripts, and utilities for running IIS
http://mvp.support.microsoft.com/
http://www.microsoft.com/windowsserver2003/community/centers/iis/|||In addition to what Tom posted, there should be no issues in a web solution,
or any other application, directly related to using a SAN. The SAN
basically handles IO and storage transparently. Most SAN products are
designed to support IO-intensive environments and have features (sometimes
additional $) for managing large volumes of data, particularly
backup/restore and disaster recovery.
--
Bob
Microsoft Consulting Services
--
This posting is provided AS IS with no warranties, and confers no rights.|||In addition to what Tom and Bob's comments, I just want to add that, 300GB
isn't really that large these days. However, you also need to consider its
backups, and most likely you would need a Dev/QA environment as well.
Perhaps, tomorrow you'll be asked to support a reporting server. Very
quickly and easily, you can find yourself having to deal with near or over
one terabyte of data.
SAN gives you more flexibility in handling these varying factors in disk
storage requirements.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"ssp2000" <ssp2000@.hotmail.com> wrote in message
news:78a9f1a5.0309290535.322076fb@.posting.google.com...
> We want to develop database driven website. because of enormous amount
> of data (300GB)
> customers's tech advisor is insisting on using SAN instead of direct
> attached storage method.
> I want to know what issues need to be considered while using SAN in
> web solutions.? Which database
> server/ product is best suited for such kind of job ? Can anyone
> suggests
> good informative articles on this subject , available on web.
> Thanks in advance
> ssp2000

database design question

I am attempting to develop a forum. I have the pages I need and was just
curious if anyone here knows, or if there is a website, how to setup a
database for a forum? Basically, the columns is what I need.Nathan
<http://www.databaseanswers.com/data_models/index.htm> -- examples
database design
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:658A4F34-2AAC-4DAD-A42D-5FED367E48FA@.microsoft.com...
> I am attempting to develop a forum. I have the pages I need and was just
> curious if anyone here knows, or if there is a website, how to setup a
> database for a forum? Basically, the columns is what I need.|||If you are wanting to build your own database from scratch, the best advice
is to make a list of everything you want to include in your forums.
Everything. Then fill each item into a database design. Normalize it, and
you will have what you want. It is probably not as easy as it sounds, but
it is not all that hard either.
If you are looking for things to put into a forum design, look at the
website Uri gave you, and then hit lots of other forums to help make your
list of features that require data (and those that don't)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:658A4F34-2AAC-4DAD-A42D-5FED367E48FA@.microsoft.com...
>I am attempting to develop a forum. I have the pages I need and was just
> curious if anyone here knows, or if there is a website, how to setup a
> database for a forum? Basically, the columns is what I need.

Database Design of Categories

I am creating a website that has categories such as:

Sports -> Soccer -> Soccer Shoes

I can not think of an effectively way to implement this into a database. Please help.

Here is a website that has a bunch of sample database designs. If you don't find exactly what you want, the process of looking thru the other designs should help you.

http://www.databaseanswers.org/data_models/

A good data modeling book is by David Hay, Data Modeling Patterns: Conventions of Thought. He gives sample high-level data designs and talks thru different modeling alternatives so you fully understand the implications of subtle modeling differences.

|||

If you post what you've tried and why you didn't like it, I'll be happy to give you ideas on how to improve it.

|||

Thanks, that is a really cool site! I haven't looked at all the database designs because there are a lot of them but one that I found is #39 Books and Libraries. They have a "categories" table but all it stores is the CategoryID and the CategoryName. With just that information how to do you know where the category is in the hierarchy of categories? For example, you have Literature -> Books -> Fiction, say Fiction has CategoryID 20, how do you know where Fiction is in the hierarchy just by knowing the CategoryID?

I came across aneBay developer site that explains how they store their categories. What they do is they store the CategoryID but also the ID of it's parent. This way you know where it exists in the hierarchy. This is what the table would look like:

Category
CategoryID
ParentID
CategoryName
IsLeafCategory

What does everyone think of this solution? Does anyone have any alternative solutions?

|||

A better column name for ParentID would be ParentCategoryID. It's always better to be very clear.

This is a more powerful model than Literature->Books->Fiction because it can handle a "infinite" number of hierarchical levels.

It is also a more complicated one to use than a model in which you force the same number of levels in the hierarchy by using a different table for each level in the hierarchy.

IsLeafCategory is a redundant piece of information in that it can be deduced when required. I would not make use of it unless you have a specific need for it (because then you have to write code to make sure it is always correct).

Database Design Issue

Hello Friends,
I have to develop a database for website where Advertisements are displayed, Now i have to maintain number of clicks on an Advertisement on hourly basis so that report can be generated. That is number of click on Advertisement 789 between 10 AM and 11 AM.
What i am trying to do now is that i have a seperate tabel for clicks on Ad. where i store Ad_ID and nuber of clicks, and time duration.
Now my challenge is that is i store like that than for every ad there will be 24 rows per day. There can be as many as 1000 ads on that site so there will be 24*1000 rows added per day on that table alone.
Is there a better way of designing it. Because If the database becomes bulky it will take lots of time while retrieving the data.

thanks in advance
saddysanYou might consider putting this table in it's own database.
After each hour the rows for that row will be static so you could have an active table and an archive table. During slow times on the server copy the expired hours to the archive table to keep the active table small. This will give less likelyhood of corruption too.

You could make single rows with 24 fields for the hours but that wouldn't give much benefit and would cause more difficult coding.

Instead of updating the row you could insert an entry in a table then create the aggregate overnight.
This should cause less contention over locks as an update is relatively slow compared to insert.

Your current design will give around 8 million very small static rows per year which isn't too bad.
Just make sure that you create aggregate tables for any queries and don't try to query the active table.|||Do you really want data for each click? Think about what the final goal is. Maybe you can collect / evaluate the data as it comes in and store it in a summary row. Less data, more information--maybe.

--jfp|||Dumping data into a database is not nearly as recource-comsuming as reading data out from the database so you can easily fill it with plenty of rows without meeting any performance-problems. However, when you need your reporst on this you might want to look into some datawarehousing-techniques (i.e. look up OLAP in BOL). And I would also suggest that you only store Ad_ID and the date/time (GETDATE()) it was hit in the database, if you do that then you have all you need. Then you can aggregate the data into day-by-day and hour-by-hour reports say on a weekly or a monthly basis...