Help on storing big text ?

Dear all,
I am building a news web site for oneof my customers and actually I have
question regarding the way to store huge big text document.
The web site is build using ASP.NET 1.1
I need to update or store time to time news information in an sql server
database.
For that I was wondering how this kind of information are store in fields.
For instance I have a content of a word document which can be big.
I can I store that document content in the database in order that when my
client browse to the proper page news information cames from that document.
Any sample to test it or tips ?
Do I have to store in database only the path for the document or the full
content ?
thnaks for your help
regards
sergea.. Try not to use TEXT or NTEXT datatypes for storing large textual data.
The TEXT datatype has some inherent problems associated with it. For
example, you cannot directly write or update text data using the INSERT or
UPDATE statements. Instead, you have to use special statements like
READTEXT, WRITETEXT and UPDATETEXT. There are also a lot of bugs associated
with replicating tables containing text columns. So, if you don't have to
store more than 8KB of text, use CHAR(8000) or VARCHAR(8000) datatypes
instead.
a.. If you have a choice, do not store binary or image files (Binary Large
Objects or BLOBs) inside the database. Instead, store the path to the binary
or image file in the database and use that as a pointer to the actual binary
file stored elsewhere on a server. Retrieving and manipulating these large
binary files is better performed outside the database, and after all, a
database is not meant for storing files.
Extract from http://www.sql-server-performance.c...t_practices.asp
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:10BB4A34-AD64-46C6-8714-D5D032BEF7E2@.microsoft.com...
> Dear all,
> I am building a news web site for oneof my customers and actually I have
> question regarding the way to store huge big text document.
> The web site is build using ASP.NET 1.1
> I need to update or store time to time news information in an sql server
> database.
> For that I was wondering how this kind of information are store in fields.
> For instance I have a content of a word document which can be big.
> I can I store that document content in the database in order that when my
> client browse to the proper page news information cames from that
document.
> Any sample to test it or tips ?
> Do I have to store in database only the path for the document or the full
> content ?
> thnaks for your help
> regards
> serge|||Thanks for your reply
How do I add text in those fields, directly typing in without formatting.?
Or can I open word, use copy and paste the content in the field ?
regards
serge
"Jonathan Chong" wrote:

> a.. Try not to use TEXT or NTEXT datatypes for storing large textual data.
> The TEXT datatype has some inherent problems associated with it. For
> example, you cannot directly write or update text data using the INSERT or
> UPDATE statements. Instead, you have to use special statements like
> READTEXT, WRITETEXT and UPDATETEXT. There are also a lot of bugs associate
d
> with replicating tables containing text columns. So, if you don't have to
> store more than 8KB of text, use CHAR(8000) or VARCHAR(8000) datatypes
> instead.
>
> a.. If you have a choice, do not store binary or image files (Binary Large
> Objects or BLOBs) inside the database. Instead, store the path to the bina
ry
> or image file in the database and use that as a pointer to the actual bina
ry
> file stored elsewhere on a server. Retrieving and manipulating these large
> binary files is better performed outside the database, and after all, a
> database is not meant for storing files.
> Extract from [url]http://www.sql-server-performance.com/vk_sql_best_practices.asp[/ur
l]
>
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:10BB4A34-AD64-46C6-8714-D5D032BEF7E2@.microsoft.com...
> document.
>
>

help on SELECT

Help on creating correct select query on the following table where customer = multi-race

( that is, customers that have value ‘1’ on more than one race category)

Thanks!

CustomerID

Black

AmIndian

Asian

White

PacIslander

Hispanic

NoRaceDisc

32501

1

1

32677

1

35062

1

1

35261

1

36490

1

41026

1

41412

1

42488

1

1

1

45471

1

47083

1

1

50066

1

Okay, first off, you should probably change your table structure if you get the chance. The way you designed things, you actually have to go through a schema change if you ever want to add a new race. I suggest you go to a table structure that has a table for race types, and another table which contains your customer_id and the race_id. In this case, your query would look something like this:

select customer_id, count(*)
from customer_races
group by customer_id
having count(*) > 1

In your current table structure, it gets a lot more complicated. If you cannot change the table structure, I'd suggest you follow a method similar to the one I outlined above. Create a temp table / table variable with the following structure...

declare table @.customer_race_count (
customer_id int, race_count int)

Then, you'll have to construct a series of statements like this...

insert into @.customer_race_count (customer_id, race_count)
select customer_id, 1 AS race_count
from customer
where black is not null

insert into @.customer_race_count (customer_id, race_count)
select customer_id, 1 AS race_count
from customer
where white is not null

then do something similar to this...

select customer_id, count(*)
from race_count
group by customer_id
having count(*) > 1

I hope that helps!

|||oh yeah, if you're using sql 2005, you might be able to take advantage of pivot / unpivot, but I've been working all day and no longer have the brain power to conjure some sample code for that.|||

well, if the race category is a numerical value (ie int) of some sort, this would work:

select CustomerID from MyTable
where (coalesce(Black,0) + coalesce(AmIndian,0) + coalesce(Asian,0) + coalesce(White,0) + (PacIslander,0) + (Hispanic,0)) > 1

If this is 2005, you may want to look at PIVOT

|||

First off, I agree completely with the person who said change the table structure. This should be a very easy query, but it isn't like you have it.

Second, if not numbers, or values aren't actually null, just change to something like

case when Black = '1' then 1 else 0 end +
case when AmIndian = '1' then 1 else 0 end + ...

and you can handle any datatype

|||

Thanks to all that responded.
I did change the structure. Created RaceTypeID 1(White),2(Black),3(Asian),4(AmIndian),and 5(Hispanic). a customer can supply more than 1 race type id so in the race table there can be multiple instances of rows with the same customerid but different race type id.

now i run a query as follows:

SELECT DISTINCT
dbo.Customers.CustomerID
,CASE WHEN EXISTS
(SELECT DISTINCT
dbo.Customers.CustomerID,
COUNT(*)
FROM dbo.Customers
INNER JOIN dbo.Race ON dbo.Customers.CustomerID = dbo.Race.CustomerID
GROUP BY dbo.Customers.CustomerID
HAVING COUNT (*) > 1) THEN 'yes' ELSE 'no' END as MultiRace
FROM dbo.Customers
WHERE
dbo.Customers.LastName NOT LIKE 'test'
AND dbo.Customers.LastName NOT LIKE 'training%'

in my result set i am getting 'yes' on all customers though only 7 are actually muti-race. Help please?

|||

Try this...

select
Customers.CustomerId,
CASE WHEN d_Race.CustomerID IS NOT NULL THEN 'YES'
ELSE 'NO'
END As MultiRace
FROM Customers
LEFT OUTER JOIN
(select customerid, count(*)
from race
group by customerid
having count(*) > 1) AS d_Race
ON d_Race.Customer_Id = Customers.Customer_Id

|||thank you very much...i only had to assign a column name for the count (*) and i finally was able to get the result i wanted. thanks a lot for all your help and to others who pitched in as well.|||whoops, yeah, you're right. I didn't alias that column. That's what happens when you develop pseudo-code. :) Glad I was able to steer you in the right direction.

Help needed - Parameter driven extract

Hi All,

I am designing a data migration tool using SSIS. As part of it, within a package I need to get a list of of customers from a SQL Server database table and extract the data for those customers from a seperate Sybase database. How do I make my SQL command to extract the data parameter driven? If I store the list of customer ID's in a package variable can I access it in the SQL command? I am using an ODBC connection for Sybase.

Any help would be greatly apreciated.

Nadella

If I were you; I would create a staging table to load the Sybase data that is accesible via Query-join from the SQL Server database. That way you could write single query joining the 2 tables.

I don't know how you could implement this via SSIS variable...