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

Dear all,
I am not so familar with SQL syntax.
Is there a good link where keywords and syntax are clearly explain ?
I am running SQL server 2000 Sp4 on server 2003.
regards
sergeHi serge,
read the "BOL"
http://www.aspfaq.com/show.asp?id=2229
HTH, Jens Suessmeyer,|||Hi
Books online is a good source for a lot of information and should be the
first thing to look at. If it is not in your SQL Server program group then
you can download it from
http://www.microsoft.com/downloads/...&displaylang=en
John
"serge calderara" wrote:

> Dear all,
> I am not so familar with SQL syntax.
> Is there a good link where keywords and syntax are clearly explain ?
> I am running SQL server 2000 Sp4 on server 2003.
> regards
> serge|||If BOL is not your thing you might try this free book:
http://www.managedtime.com/freesqlbook.php3
Msql

Help on SQL syntax

Dear Anyone,
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
Annette
You can use AVG() aggregate function with GROUP BY clause, for example:
SELECT SAMPLE.CORRECTED_COL_DATE, AVG(SAMPLE.PRODUCT_GRADE)
FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS, PRODUCT
WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND
PRODUCT.NAME = SAMPLE.PRODUCT AND
PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND
PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME LIKE '%SD'))
AND
RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'
GROUP BY SAMPLE.CORRECTED_COL_DATE
ORDER BY CORRECTED_COL_DATE
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Annette Malini" <anonymous@.discussions.microsoft.com> wrote in message
news:731d01c48423$63065e20$a501280a@.phx.gbl...
> Dear Anyone,
> I am no expert with SQL server but I am trying obtain
> some data from this server using SQL syntax, I would like
> to average out the data by correction_col_date below, can
> anyone help please!
> SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
> SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
> SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
> SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
> PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
> RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
> RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
> RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
> PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
> SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
> SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
> PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
> SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
> SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
> SAMPLE.PRODUCT_GRADE AND
> PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
> PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
> RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
> ((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
> LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
> RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
> CORRECTED_COL_DATE
> Thanks,
> Annette
>

Help on SQL syntax

Dear Anyone,
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME = SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
AnnetteYou can use AVG() aggregate function with GROUP BY clause, for example:
SELECT SAMPLE.CORRECTED_COL_DATE, AVG(SAMPLE.PRODUCT_GRADE)
FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS, PRODUCT
WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND
PRODUCT.NAME = SAMPLE.PRODUCT AND
PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND
PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME LIKE '%SD'))
AND
RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'
GROUP BY SAMPLE.CORRECTED_COL_DATE
ORDER BY CORRECTED_COL_DATE
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Annette Malini" <anonymous@.discussions.microsoft.com> wrote in message
news:731d01c48423$63065e20$a501280a@.phx.gbl...
> Dear Anyone,
> I am no expert with SQL server but I am trying obtain
> some data from this server using SQL syntax, I would like
> to average out the data by correction_col_date below, can
> anyone help please!
> SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
> SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
> SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
> SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
> PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
> RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
> RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
> RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
> PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
> SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
> SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
> PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME => SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION => SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE => SAMPLE.PRODUCT_GRADE AND
> PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
> PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
> RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
> ((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
> LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
> RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
> CORRECTED_COL_DATE
> Thanks,
> Annette
>

Help on SQL syntax

Dear Anyone,
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
AnnetteYou can use AVG() aggregate function with GROUP BY clause, for example:
SELECT SAMPLE.CORRECTED_COL_DATE, AVG(SAMPLE.PRODUCT_GRADE)
FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS, PRODUCT
WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND
PRODUCT.NAME = SAMPLE.PRODUCT AND
PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND
PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME LIKE '%SD'))
AND
RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'
GROUP BY SAMPLE.CORRECTED_COL_DATE
ORDER BY CORRECTED_COL_DATE
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Annette Malini" <anonymous@.discussions.microsoft.com> wrote in message
news:731d01c48423$63065e20$a501280a@.phx.gbl...
> Dear Anyone,
> I am no expert with SQL server but I am trying obtain
> some data from this server using SQL syntax, I would like
> to average out the data by correction_col_date below, can
> anyone help please!
> SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
> SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
> SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
> SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
> PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
> RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
> RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
> RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
> PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
> SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
> SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
> PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
> SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
> SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
> SAMPLE.PRODUCT_GRADE AND
> PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
> PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
> RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
> ((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
> LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
> RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
> CORRECTED_COL_DATE
> Thanks,
> Annette
>

Help on SQL Syantax

Dear Anyone,
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
Annette
post relevent table structures, sample data and expected result set.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

help on SQL server backup!

Dear all,
I have a problem right now. We got a production server running. Now we want
to setup an another identical database at a remote office. The problem is
that replication can't be done between the two offices. To make sure the
remote office will have as much data as the production server. What shall I
do?
Help!!!!Hi
backup/restore commands. (see in BOL)
"big tony" <bigtony@.discussions.microsoft.com> wrote in message
news:C9CD538F-05AD-4CE4-8A87-45EAFEAEE024@.microsoft.com...
> Dear all,
> I have a problem right now. We got a production server running. Now we
want
> to setup an another identical database at a remote office. The problem is
> that replication can't be done between the two offices. To make sure the
> remote office will have as much data as the production server. What shall
I
> do?
> Help!!!!

help on SQL server backup!

Dear all,
I have a problem right now. We got a production server running. Now we want
to setup an another identical database at a remote office. The problem is
that replication can't be done between the two offices. To make sure the
remote office will have as much data as the production server. What shall I
do?
Help!!!!Hi
backup/restore commands. (see in BOL)
"big tony" <bigtony@.discussions.microsoft.com> wrote in message
news:C9CD538F-05AD-4CE4-8A87-45EAFEAEE024@.microsoft.com...
> Dear all,
> I have a problem right now. We got a production server running. Now we
want
> to setup an another identical database at a remote office. The problem is
> that replication can't be done between the two offices. To make sure the
> remote office will have as much data as the production server. What shall
I
> do?
> Help!!!!

help on SQL server backup!

Dear all,
I have a problem right now. We got a production server running. Now we want
to setup an another identical database at a remote office. The problem is
that replication can't be done between the two offices. To make sure the
remote office will have as much data as the production server. What shall I
do?
Help!!!!
Hi
backup/restore commands. (see in BOL)
"big tony" <bigtony@.discussions.microsoft.com> wrote in message
news:C9CD538F-05AD-4CE4-8A87-45EAFEAEE024@.microsoft.com...
> Dear all,
> I have a problem right now. We got a production server running. Now we
want
> to setup an another identical database at a remote office. The problem is
> that replication can't be done between the two offices. To make sure the
> remote office will have as much data as the production server. What shall
I
> do?
> Help!!!!
sql

Help on Security of reports acccess

Dear all,
If I need to give acces to a particular folder and report type based on
groups of user's where this can be configured ?
For instance typically a user belonging to group MAINTENANCE will be able to
use reports only from maintenance folder and groups of QUALITY reports from
quality folder.
The yshould only use the report and not have access to datasource even
seeing them
Do I use the adminstrativ part of report server or should I create
authorisation and credential rights inside the Web.Config file of my report
server '
thnaks for help
regards
sergeIf you are using Report Manager to deliver the reports the security can be
configured right from there. Browse to the Report Manager web page
(http://myserver/reports). If your account has admin privileges then you
can edit the security, found under Properties for each folder. I am using
this to deliver reports for Accounting, Warehouse, and Transportation from
the same server, and each group sees only the reports I want them to see.
They also don't have access to the data source.
Just make sure that all groups have access to the home folder of Report
Manager, otherwise they won't get too far.
-Greg

Help on scheduling a data change ??

Dear all,
I have a table with a certain amount of data. One of the data column is
named STATUS. by default all entry have a status of 5 (which means for the
client application a waiting states)
I need to schedule the change of this status from 5 to 1 automatically.
For example I would like that status value for all records with 5 is chaged
to 1 on a particular date and time.
How can I do that ?
regards
sergeHi
UPDATE Table SET status=1 WHERE status =1
AND GETDATE()>='20050101' AND GETDATE() <'20050201'
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:BDA2B6B7-B9EF-4D79-A836-AAD0EE84B783@.microsoft.com...
> Dear all,
> I have a table with a certain amount of data. One of the data column is
> named STATUS. by default all entry have a status of 5 (which means for the
> client application a waiting states)
> I need to schedule the change of this status from 5 to 1 automatically.
> For example I would like that status value for all records with 5 is
chaged
> to 1 on a particular date and time.
> How can I do that ?
> regards
> serge|||Correction
Should be
UPDATE Table SET status=5 WHERE status =1
......
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OkOhaZQjFHA.3148@.TK2MSFTNGP09.phx.gbl...
> Hi
> UPDATE Table SET status=1 WHERE status =1
> AND GETDATE()>='20050101' AND GETDATE() <'20050201'
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:BDA2B6B7-B9EF-4D79-A836-AAD0EE84B783@.microsoft.com...
the
> chaged
>|||May be you should check out "Scheduling Jobs" in BOL.
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:BDA2B6B7-B9EF-4D79-A836-AAD0EE84B783@.microsoft.com...
> Dear all,
> I have a table with a certain amount of data. One of the data column is
> named STATUS. by default all entry have a status of 5 (which means for the
> client application a waiting states)
> I need to schedule the change of this status from 5 to 1 automatically.
> For example I would like that status value for all records with 5 is
chaged
> to 1 on a particular date and time.
> How can I do that ?
> regards
> serge|||Thnaks for the tip.
Where should I store that SQL querry ?
Does it have to be in a store procedure and then run that store procedure
from a schedule task ?
"Uri Dimant" wrote:

> Correction
> Should be
> UPDATE Table SET status=5 WHERE status =1
> .......
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OkOhaZQjFHA.3148@.TK2MSFTNGP09.phx.gbl...
> the
>
>|||>Does it have to be in a store >procedure and then run that store >procedure
>from a schedule task ?
Yes.
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EE63176F-C87F-4BEA-A8FB-4D18A47C7959@.microsoft.com...
> Thnaks for the tip.
> Where should I store that SQL querry ?
> Does it have to be in a store procedure and then run that store procedure
> from a schedule task ?
>
> "Uri Dimant" wrote:
>
is
for
automatically.|||Hi,
If I use the task scheduler to run the store procedure, I do not need the
GETDATE function, the UPDATE is enough right ?
"Uri Dimant" wrote:

> Yes.
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:EE63176F-C87F-4BEA-A8FB-4D18A47C7959@.microsoft.com...
> is
> for
> automatically.
>
>|||You could just store the creation date in the table and then derive the
Status from the date when you query the data.
David Portas
SQL Server MVP
--|||That was the idea I get at first.
Something like storing the desire date/Time when the status needs to be
changed and then update teh status when the date/time field is corresponding
to current date.
But then if I do that I can use different methode to check this date.
Either I can do it in my client VB application or on the server itself.
But if I do it on the server side what shoudl I use to pol the current date
and check if it correspond to the one store in the table and then if it is
update the field.
Can we make some loop in server procedure ?
Sorry I am new n that and could not get the proper way
thnaks for your help
serge
"David Portas" wrote:

> You could just store the creation date in the table and then derive the
> Status from the date when you query the data.
> --
> David Portas
> SQL Server MVP
> --
>|||What do you mean by derive the status '
by the way I have try to run a script from a new job that I schedule at a
certain time. If I run the script from the querry analyser it works fine, bu
t
when I put it as a new job and load the script file to execute, the set the
time to schedule it, it is not updating or may be not runing at all.
Script is :
EXEC dbo.UpdateStatus
GO
and UpdateStatus is a store procedure
Any idea?
thaks
"David Portas" wrote:

> You could just store the creation date in the table and then derive the
> Status from the date when you query the data.
> --
> David Portas
> SQL Server MVP
> --
>

Help on Registering SQL Server in Enterprise Admin

Dear All
We want to monitor SQL job history for 50+ servers using id which doesn't
have admin priviliges on server and database. On database front I can
achieveit by assiging TargetServer role but I want to add all servers in
Enterprise Administartor console. What are minimum rights we require to
register server in Enterprise Admin for Windows 2000 & 2003 Servers?
Rahul> Enterprise Administartor console. What are minimum rights we require to
> register server in Enterprise Admin for Windows 2000 & 2003 Servers?
You will have EM installed on your workstation and to know a login and
password to register the SQL Server
"rahulpt" <rahulpt@.discussions.microsoft.com> wrote in message
news:36C3F6A6-BBF7-41A1-A97B-E5EC3A6AD6F9@.microsoft.com...
> Dear All
> We want to monitor SQL job history for 50+ servers using id which doesn't
> have admin priviliges on server and database. On database front I can
> achieveit by assiging TargetServer role but I want to add all servers in
> Enterprise Administartor console. What are minimum rights we require to
> register server in Enterprise Admin for Windows 2000 & 2003 Servers?
> --
> Rahul|||The minimum 'rights' to register a server are to be able to log into the ser
ver. It is not necessary to have permissions for any database.
However, in order to view the job history in EM (SQL 2000), I think that it
will be necessary to be in the sysadmin server role for all servers. (I'm a
little fuzzy on this...)
For Management Studio (SQL 2005) you must be in the SQLAgentReaderRole (or s
ysadmin) in order to view Job history of all the jobs.
If you used Query Analyzer and had some form or script, it would be necessar
y to only have SELECT permissions on the various sysjob... tables in the msd
b databases.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"rahulpt" <rahulpt@.discussions.microsoft.com> wrote in message news:36C3F6A6-BBF7-41A1-A97B-
E5EC3A6AD6F9@.microsoft.com...
> Dear All
>
> We want to monitor SQL job history for 50+ servers using id which doesn't
> have admin priviliges on server and database. On database front I can
> achieveit by assiging TargetServer role but I want to add all servers in
> Enterprise Administartor console. What are minimum rights we require to
> register server in Enterprise Admin for Windows 2000 & 2003 Servers?
>
> --
> Rahul|||Dear All
Thanks for your inputrs. But how I will give permission to domain user to
log on to SQL Server? Do you mean by adding user in SQL Login? Also when we
add user to SQL it asks for "Database" name, like master etc or user created
like test,test1 etc.
So which database we need to assign while adding user to sql login and what
will be security implications of same? I.e.if we add user with database as
master or user database test then what rights the user will have on these
databases'
Rahul
"Arnie Rowland" wrote:
[vbcol=seagreen]
> The minimum 'rights' to register a server are to be able to log into the s
erver. It is not necessary to have permissions for any database.
> However, in order to view the job history in EM (SQL 2000), I think that i
t will be necessary to be in the sysadmin server role for all servers. (I'm
a little fuzzy on this...)
> For Management Studio (SQL 2005) you must be in the SQLAgentReaderRole (or
sysadmin) in order to view Job history of all the jobs.
> If you used Query Analyzer and had some form or script, it would be necess
ary to only have SELECT permissions on the various sysjob... tables in the m
sdb databases.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "rahulpt" <rahulpt@.discussions.microsoft.com> wrote in message news:36C3F6
A6-BBF7-41A1-A97B-E5EC3A6AD6F9@.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 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 Date handling ?

Dear all,
What is the way under sql to extract the month from a date and then defined
a querry whcih fetch only data based on that particular month ?
thaks for your help
regardsSomething like this ?
declare @.dateVar datetime
select @.dateVar = '10102004'-- or whatever
select <column list>
from table1
where month(@.DateVar) = month(dateColumn)
MC
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EAF2309E-1EE2-4830-9FB7-41B3896A7257@.microsoft.com...
> Dear all,
> What is the way under sql to extract the month from a date and then
> defined
> a querry whcih fetch only data based on that particular month ?
> thaks for your help
> regards|||examnotes (sergecalderara@.discussions.microsoft.com)
writes:
> What is the way under sql to extract the month from a date and then
> defined a querry whcih fetch only data based on that particular month ?
One way is:
SELECT .. FROM tbl
WHERE datecol >= convert(char(6), @.date, 112) + '01'
AND datecol < convert(char(6), dateadd(Month, 1, @.date), 112) + '01'
There are ways to write this in shorter code, but it's important to not
put the date column into any expression, as that would preclude the
use of index on that column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
To get a particular month from the given date u can use:
SELECT MONTH(<given_date> )
FROM <TABLE>
or
SELECT DATEPART("m",<given_date> )
FROM <TABLE>
to get records based on a particular date.
SELECT *
FROM <TABLE>
WHERE DATEPART("m",<given_date> ) = <month>
Please let me know if you have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"serge calderara" wrote:

> Dear all,
> What is the way under sql to extract the month from a date and then define
d
> a querry whcih fetch only data based on that particular month ?
> thaks for your help
> regards

help on alias...

Dear friends and guru:
I am trying to do insertion with alias
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
--
SincerelyA) This is the wrong group for this request, it is the Reporting Services
group.
B) Don't use aliases, they don't work on inserts. Since Insert only inserts
into a single table, the tablename prefix is totally unneccesary.
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:C3EFCB25-67CF-4FED-BFE4-D9A7EB7989A4@.microsoft.com...
> Dear friends and guru:
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely
>

help needed with sql query. percentage of the females.

Dear All
I have following table
id|name|female
1|John|No
2|Brian|No
3|Tanya|Yes
How can I get percentage of females in this table?
Thanks a lot!
SviridSELECT Sum(CASE WHEN 'Yes' = female THEN 1e0 END) / Count(*)
FROM myTable-PatP|||This should be faster on big number of records:

select FemalesPercentage = 100.0 * sum(case when female = 'Yes' then NumberOf else 0.0 end) / sum(NumberOf)
from (select female,
NumberOf = count(1)
from SviridsTable
group by female) as x

Help me!emergency!

dear:

can anyone tell how to connect to Microsoft Mobile Server 2005 using System.Data.SqlceConnection or using System.oledbConnection connect to Microsoft Access DataBase in Pocket Pc or windows CE program?

dose there any minimum requirements i need to install?would you like to list a list for me

thanks alot.

If you are using Visual Studio 2005 that has Smart Device programming support, you are ready to develop against SQL Server Mobile 2005.

If not, then this link describes the installation for SQL Server Mobile 2005: http://msdn2.microsoft.com/en-us/library/ms171938(SQL.90).aspx

For developing SQLMobile applications using Visual Studio 2005 you can refer:

http://msdn2.microsoft.com/en-us/7bx62e55(VS.80).aspx

http://msdn2.microsoft.com/en-us/library/kckakk5w(VS.80).aspx

SQL Mobile programming references to help you out:

http://msdn2.microsoft.com/en-us/library/ms174628(SQL.90).aspx. This link walks your through writing a smart device application that creates a new SQL Mobile database.

http://msdn2.microsoft.com/en-us/library/ms174462(SQL.90).aspx: This link is the SQL Server Mobile .NET programming reference. The documentation for System.Data.SqlServerCe classes can help you program to connect and access SQL Mobile in a device application.

Please let me know if this can help you get started.

Help me please i have a problem in ALTER command

Dear all

I need to add new column in table TranPay , I use ALTER command on PDA on Button Click

Dim SqlStm = "ALTER TABLE TranPay ALTER COLUMN TranKey IDENTITY (1, 1)"

Dim Dcmd As SqlCeCommand = New SqlCeCommand

Dcmd.Connection = Conn

Dcmd.CommandText = SqlStm

Dcmd.ExecuteNonQuery()

But cannot create new Field , Help me please

Brg ,

Tingnong

Try this:

ALTER TABLE TranPay ALTER COLUMN TranKey int IDENTITY(1,1)

You must specify the column data type.|||

Try this:

Code Snippet

ALTER TABLE TranPay ADD TranKey int IDENTITY (1,1)