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 SQL syntax ??

Dear all,
I am using SQL 2000 with vs2003.
I need to create a store procedure which get the LAST record which has been
written in the database.
What is teh way to get that record ?
thnaks for your help
regards
sergeHi
How many table does the database have?
What is at the same time users insert data in many tables?
One option is create a new columns as datetime (DEFAULT GETDATE()) for
inserting .For updating you will have to create a trigger for update to
track the changes.
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:78D429C3-3519-45FA-ADD8-C952D199D47D@.microsoft.com...
> Dear all,
> I am using SQL 2000 with vs2003.
> I need to create a store procedure which get the LAST record which has
> been
> written in the database.
> What is teh way to get that record ?
> thnaks for your help
> regards
> serge|||I only need to return a single row from a database table which already
contains a DateTime field.
I have tried to use the TOP keyword as follow in a strore procedure:
SELECT TOP ID, LEN, START_TIME FROM REELHIST ORDER BY START_TIME DESC
But it return a syntax error message when checking the syntax
Any idea ?
regards
serge
"Uri Dimant" wrote:

> Hi
> How many table does the database have?
> What is at the same time users insert data in many tables?
> One option is create a new columns as datetime (DEFAULT GETDATE()) for
> inserting .For updating you will have to create a trigger for update to
> track the changes.
>
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:78D429C3-3519-45FA-ADD8-C952D199D47D@.microsoft.com...
>
>|||SELECT TOP 1 ID, LEN, START_TIME FROM REELHIST ORDER BY START_TIME DESC
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:486F2059-0EE0-4CF6-8687-3C5F707EE77F@.microsoft.com...
>I only need to return a single row from a database table which already
> contains a DateTime field.
> I have tried to use the TOP keyword as follow in a strore procedure:
> SELECT TOP ID, LEN, START_TIME FROM REELHIST ORDER BY START_TIME DESC
> But it return a syntax error message when checking the syntax
> Any idea ?
> regards
> serge
> "Uri Dimant" wrote:
>|||thnaks,
so simple sometimes :-)
"Uri Dimant" wrote:

> SELECT TOP 1 ID, LEN, START_TIME FROM REELHIST ORDER BY START_TIME DESC
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:486F2059-0EE0-4CF6-8687-3C5F707EE77F@.microsoft.com...
>
>

Help on passing param to store procedure ??

Dear all,
I am calling a store procedure in SQL server 2000 from VS2003
No problem from that side.
The store procedure has an input parameter and define as follow :
ALTER PROCEDURE sp_GetReels (@.LineId varchar) AS
SELECT LINE_ID , ID
FROM REELHIST
WHERE (LINE_ID = @.LineId)
GO
If I execute that procedure from TSQL and pass the parameter value: WSE30
(which is the criteria that I want), then no records gets return and I am
sure they are records.
Then if I hardcode my string in the store procedure as follow :
ALTER PROCEDURE sp_GetReels AS
SELECT LINE_ID , ID
FROM REELHIST
WHERE LINE_ID = 'WSE30'
Then records are returns
What I am doing wrong in my procedure to pass that input parameter ?
Thnaks for your help
regards
sergeYou need to be more specific about the length of the varchar
parameter.
For example
ALTER PROCEDURE sp_GetReels (@.LineId varchar(10)) AS|||You didnt specify the length of the varchar so it assumes it is 1 :).
Put instead
@.LineId varchar (50) or something like that.
At this point your string becomes 'W' in the stored procedure.
MC
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:FDF4F7FB-45B6-4F0D-8535-1CDED2492157@.microsoft.com...
> Dear all,
> I am calling a store procedure in SQL server 2000 from VS2003
> No problem from that side.
> The store procedure has an input parameter and define as follow :
> ALTER PROCEDURE sp_GetReels (@.LineId varchar) AS
> SELECT LINE_ID , ID
> FROM REELHIST
> WHERE (LINE_ID = @.LineId)
> GO
> If I execute that procedure from TSQL and pass the parameter value: WSE30
> (which is the criteria that I want), then no records gets return and I am
> sure they are records.
> Then if I hardcode my string in the store procedure as follow :
> ALTER PROCEDURE sp_GetReels AS
> SELECT LINE_ID , ID
> FROM REELHIST
> WHERE LINE_ID = 'WSE30'
> Then records are returns
> What I am doing wrong in my procedure to pass that input parameter ?
> Thnaks for your help
> regards
> serge

help on login issue

Hi,
I restored a db on a new server. I remeber I got a sql statement or store pr
ocedure to get all "sp_change_users_login" for the db, ie, the query result
is a list of sp_change_users_login statment for each login, so I don't need
to type for every user. But
I can't find it anymore. anyone can help? ThanksHere are some related links:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:08DCEBA2-C07B-4F12-B65A-1ACBB19CAD71@.microsoft.com...
> Hi,
> I restored a db on a new server. I remeber I got a sql statement or store
procedure to get all "sp_change_users_login" for the db, ie, the query
result is a list of sp_change_users_login statment for each login, so I
don't need to type for every user. But I can't find it anymore. anyone can
help? Thanks

help on login issue

Hi,
I restored a db on a new server. I remeber I got a sql statement or store procedure to get all "sp_change_users_login" for the db, ie, the query result is a list of sp_change_users_login statment for each login, so I don't need to type for every user. But I can't find it anymore. anyone can help? ThanksHere are some related links:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:08DCEBA2-C07B-4F12-B65A-1ACBB19CAD71@.microsoft.com...
> Hi,
> I restored a db on a new server. I remeber I got a sql statement or store
procedure to get all "sp_change_users_login" for the db, ie, the query
result is a list of sp_change_users_login statment for each login, so I
don't need to type for every user. But I can't find it anymore. anyone can
help? Thanks

help on login issue

Hi,
I restored a db on a new server. I remeber I got a sql statement or store procedure to get all "sp_change_users_login" for the db, ie, the query result is a list of sp_change_users_login statment for each login, so I don't need to type for every user. But
I can't find it anymore. anyone can help? Thanks
Here are some related links:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:08DCEBA2-C07B-4F12-B65A-1ACBB19CAD71@.microsoft.com...
> Hi,
> I restored a db on a new server. I remeber I got a sql statement or store
procedure to get all "sp_change_users_login" for the db, ie, the query
result is a list of sp_change_users_login statment for each login, so I
don't need to type for every user. But I can't find it anymore. anyone can
help? Thanks
sql

Help on elapse time to return data ??

Dear all,
I have build an ASP.net application which calls different store procedure.
When my customer request data from store procedure, I would like to display
on the page, the time it takes to return data.
How to do that ?
regards
sergeSomething like that
create proc myproc1
as
--here is your body's code
--usage
declare @.dt datetime
set @.dt=getdate()
exec myproc1
select datediff(s,@.dt,getdate())
drop proc myproc1
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:0A3D7622-0901-420D-A5F1-CCF7127BE43C@.microsoft.com...
> Dear all,
> I have build an ASP.net application which calls different store procedure.
> When my customer request data from store procedure, I would like to
> display
> on the page, the time it takes to return data.
> How to do that ?
> regards
> serge|||To add to Uri's response, you can also calculate the elapsed time in your
application. C# example:
DateTime startTime = DateTime.Now;
//execute query
TimeSpan duration = DateTime.Now.Subtract(startTime);
Response.Write(duration.ToString();
Hope this helps.
Dan Guzman
SQL Server MVP
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:0A3D7622-0901-420D-A5F1-CCF7127BE43C@.microsoft.com...
> Dear all,
> I have build an ASP.net application which calls different store procedure.
> When my customer request data from store procedure, I would like to
> display
> on the page, the time it takes to return data.
> How to do that ?
> regards
> serge|||Thnaks dan, I will do that
regards
serge
"Dan Guzman" wrote:

> To add to Uri's response, you can also calculate the elapsed time in your
> application. C# example:
> DateTime startTime = DateTime.Now;
> //execute query
> TimeSpan duration = DateTime.Now.Subtract(startTime);
> Response.Write(duration.ToString();
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:0A3D7622-0901-420D-A5F1-CCF7127BE43C@.microsoft.com...
>
>

Help on DB design

Hi,
I have a form where users can choose number of file uploads ( drop down ) and upload images. I need to store the path of images in database.I want to know what is the best way to store multiple file upload paths. As per my knowledge it possible to store paths in one field with delimiter. Is there any other good way to store the paths.

Raj

It depends,

You have 3 (or more) options,

Store as Individual Table(rows

Each file will be stored as single row with key field.

The master table can hold the key field & number of files

Store as Delimited Value

If you use SQL server 2000, you are not allowed to store more than 8000 chars, otherwise you have to change the data type to LOB(text). In SQL Server 2005 you can use the Varchar(max).

Manipulations need to be done on the UI.

Single row modification can’t be done on the UI, you have to stick with your UI.

Store as XML value

If you use SQL Server 2005, you can use new data type XML.

You can store at any number of chars

Easy to manipulate on Database itself.

Structured & Well Managed

My Rank is – 1.XML, 2.Table(rows), 3.Delimited value

|||^Thank you Manivannan.D.Sekaran . I will follow Table(rows) as i am using sql server 2000.

Help on DB Design

Hi,
I have a form where users can choose number of file uploads ( drop down ) and upload images. I need to store the path of images in database.I want to know what is the best way to store multiple file upload paths. As per my knowledge it possible to store paths in one field with delimiter. Is there any other good way to store the paths.

Raj

Quote:

Originally Posted by bootzwiz

Hi,
I have a form where users can choose number of file uploads ( drop down ) and upload images. I need to store the path of images in database.I want to know what is the best way to store multiple file upload paths. As per my knowledge it possible to store paths in one field with delimiter. Is there any other good way to store the paths.

Raj

Generate FileId in the database and GroupId and FilePath

FileId FileGroupID FilePath
--------------------------------

Like this|||

Quote:

Originally Posted by bootzwiz

Hi,
I have a form where users can choose number of file uploads ( drop down ) and upload images. I need to store the path of images in database.I want to know what is the best way to store multiple file upload paths. As per my knowledge it possible to store paths in one field with delimiter. Is there any other good way to store the paths.

Raj

i offer to have the following entity/table for your application :

1-an entity named "Session " with these fields (SessionID,SessionName,SessionDate ) with SessionID for primary key.

2-an entity named SessionFiles with these fields(SessionID,FilenameWithPath,uploadStatus) with "SessionID,FilenameWithPath" for primary key.

3-make relation for these two table with SessionID field.

4-each form shows one session in your application for uploading the related SessionFiles.

5-Enjoy it.sql

Help newbie with quering XML

Hello,
I have XML data which I receive from a vendor and store in a table
with the XML datatype in this arrangement:
<MYXML>
<ITEMS>
<ITEM name="item1" value="widget" />
<ITEM name="item2" value="dongle" />
<ITEM name="item3" value="thingy" />
</ITEMS>
</MYXML>
How do I query for item1 and return just, widget ?
TIA,
RichHi,
How about something like this
DECLARE @.x XML
SET @.x =
'<MYXML>
<ITEMS>
<ITEM name="item1" value="widget" />
<ITEM name="item2" value="dongle" />
<ITEM name="item3" value="thingy" />
</ITEMS>
</MYXML>'
SELECT @.x.value('(//ITEM[@.name="item1"]/@.value)[1]','nvarchar(MAX)')
If you want to parameterize the value for the name attribute, you can
use sql:variable() and do this
DECLARE @.x XML
SET @.x =
'<MYXML>
<ITEMS>
<ITEM name="item1" value="widget" />
<ITEM name="item2" value="dongle" />
<ITEM name="item3" value="thingy" />
</ITEMS>
</MYXML>'
DECLARE @.n nvarchar(MAX)
SET @.n = 'item1'
SELECT
@.x.value('(//ITEM[@.name=sql:variable("@.n")]/@.value)[1]','nvarchar(MAX)')
I hope this helps
Denis Ruckebusch
XML datatype test team
--
This posting is provided "AS IS" with no warranties, and confers no
rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sk8man31" <me@.aol.com> wrote in message
news:rc71825rqgo28vr6dr18beb0bn1jv148qe@.
4ax.com...
> Hello,
> I have XML data which I receive from a vendor and store in a table
> with the XML datatype in this arrangement:
> <MYXML>
> <ITEMS>
> <ITEM name="item1" value="widget" />
> <ITEM name="item2" value="dongle" />
> <ITEM name="item3" value="thingy" />
> </ITEMS>
> </MYXML>
> How do I query for item1 and return just, widget ?
> TIA,
> Rich|||On Fri, 2 Jun 2006 18:18:53 -0700, "Denis Ruckebusch [MSFT]"
<denisruc@.online.microsoft.com> wrote:

>Hi,
> How about something like this
>DECLARE @.x XML
>SET @.x =
>'<MYXML>
><ITEMS>
><ITEM name="item1" value="widget" />
><ITEM name="item2" value="dongle" />
><ITEM name="item3" value="thingy" />
></ITEMS>
></MYXML>'
>SELECT @.x.value('(//ITEM[@.name="item1"]/@.value)[1]','nvarchar(MAX)')
>
>If you want to parameterize the value for the name attribute, you can
>use sql:variable() and do this
>DECLARE @.x XML
>SET @.x =
>'<MYXML>
><ITEMS>
><ITEM name="item1" value="widget" />
><ITEM name="item2" value="dongle" />
><ITEM name="item3" value="thingy" />
></ITEMS>
></MYXML>'
>DECLARE @.n nvarchar(MAX)
>SET @.n = 'item1'
>SELECT
>@.x.value('(//ITEM[@.name=sql:variable("@.n")]/@.value)[1]','nvarchar(MAX)')
>
>I hope this helps
>Denis Ruckebusch
>XML datatype test team
Denis,
Thanks for your help, that worked great!
~Rich

help ME SQL2000

hello to all,
I have installed two serveur with sql server 2000,
one server a store launch one procedures and all it goes
two server error : Serveur MSg446, level 16 been 9 linens 1 CANNOT resolve COLLATION conflict for equal to operation.
I do not succeed to understand the type of problem you can help
thanks me
maurizioI'm unsure what the procedure is trying to do :confused:|||Originally posted by ncapi
hello to all,

I have installed two serveur with sql server 2000,
one server a store launch one procedures and all it goes
two server error : Serveur MSg446, level 16 been 9 linens 1 CANNOT resolve COLLATION conflict for equal to operation.
I do not succeed to understand the type of problem you can help
thanks me
maurizio

Istruction :
CREATE TABLE #zeta (
a VARCHAR(6) NOT NULL,
b DECIMAL(9) NULL
)

ALTER TABLE #zeta
ADD
CONSTRAINT zK_t
PRIMARY KEY (a)

SELECT RET.ID_R
FROM EL, RET, AN, #zeta
WHERE
EL.a LIKE @.P_COD_4 AND
EL.a1 = @.P_COD_1 AND
EL.a2 = @.P_COD_2 AND
EL.a3 = @.P_COD_3 AND
RET.ID_E = EL.ID_E AND
RET.COD_STATO = 2 AND
AN.ID_1 = RET.ID_1 AND
#zeta.a = EL.a
)