newbie question on select

Hi All,
I need to get records vayring from 1 to 100, What is the best way without g
etting one record at a time.
Is the only other way by generating a select command with In Statment each t
ime,
But this will make the string very long.
Any help will appreicated
Thank You.Sound like you need a server side cursor / paging solution for your
problem:
http://www.google.com/search?hl=de&...ql+server&meta=
There are tons of hits on the internet, perhaps you take a deeper look
in the examples to decide for one.
HTH, jens Suessmeyer.|||Please post your DDL plus sample data and expected results. Do you want the
rows where a particular column is in the range 1 - 100? If so, try:
select
*
from
MyTable
where
MyCol between 1 and 100
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<CobraStrikes@.al.com> wrote in message
news:1140960857.29276.0@.ersa.uk.clara.net...
Hi All,
I need to get records vayring from 1 to 100, What is the best way
without getting one record at a time.
Is the only other way by generating a select command with In Statment each
time,
But this will make the string very long.
Any help will appreicated
Thank You.|||What version are you using ?
SQL Server 2005
CREATE TABLE SpeakerStats
(
speaker VARCHAR(10) NOT NULL PRIMARY KEY,
score INT NOT NULL,
)
SET NOCOUNT ON
INSERT INTO SpeakerStats VALUES('Dan', 1)
INSERT INTO SpeakerStats VALUES('Ron', 2)
INSERT INTO SpeakerStats VALUES('Kathy', 3)
INSERT INTO SpeakerStats VALUES('Suzanne', 4)
INSERT INTO SpeakerStats VALUES('Joe', 5)
INSERT INTO SpeakerStats VALUES('Robert', 6)
INSERT INTO SpeakerStats VALUES('Mike', 7)
WITH myCTE (rownum,speaker,score)
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
speaker, score
FROM SpeakerStats
)
SELECT * FROM myCTE WHERE rownum BETWEEN 5 AND 7
ORDER BY rownum DESC
SQL Server 2000
SELECT * FROM
(
SELECT * ,(SELECT COUNT(*) FROM SpeakerStats S
WHERE S.speaker<=SpeakerStats.speaker)rownum
FROM SpeakerStats
) AS Der WHERE rownum >=5 AND rownum <8
ORDER BY rownum
<CobraStrikes@.al.com> wrote in message
news:1140960857.29276.0@.ersa.uk.clara.net...
> Hi All,
> I need to get records vayring from 1 to 100, What is the best way
> without getting one record at a time.
> Is the only other way by generating a select command with In Statment each
> time,
> But this will make the string very long.
> Any help will appreicated
> Thank You.
>
>|||We need more information on what you're trying to do. An example that we
could work with would be more helpful.
Regards
Colin Dawson
www.cjdawson.com
<CobraStrikes@.al.com> wrote in message
news:1140960857.29276.0@.ersa.uk.clara.net...
> Hi All,
> I need to get records vayring from 1 to 100, What is the best way
> without getting one record at a time.
> Is the only other way by generating a select command with In Statment each
> time,
> But this will make the string very long.
> Any help will appreicated
> Thank You.
>
>|||Sorry, I have posted this in the wrong group, it should have posted it to th
e Access group.
I have table with 500 employee details depending on the user selection it c
an be between
1 and 100 emp records of the 500 records not necessarily consecutive record
s.
I will google with link provided.
Thank you all for the quick replies.

Newbie question here - stored procedures

Hi I just installed sql server 2005 for the first time today. Anyways I have a bunch of stored procs from an application i developed on the SQL 2000 server. Anyways I am having a problem with one of my stored procedures.

CREATE PROCEDURE ProvinceRetrieveAdHoc

(@.whereClause VARCHAR(256))

AS

EXEC("SELECT * FROM tblProvince WHERE " + @.whereClause)

RETURN

GO

However this isn't working, I am getting this error:

Incorrect syntax near 'SELECT * FROM tblProvince WHERE '.

Now I am guessing the EXEC statement must have changed, as every stored proc that uses the exec command errors out with this same error. All my other procedures run just fine. Can anyone please tell me how to fix it?

THanks

oh wow nm, i figured it out.. just replaced double quotes with single.sql

NEWBIE question for ya!

I ve been ask to diplay some infomation based a certain date and time.
I'll be using asp and mssql 2k.
Like friday between 8:00pm to 10pm to display
then on Saturday between 2:00pm and 4:00pm to display and for the rest of
the time not display. any suggetions.
I've been looking at getday()
any suggetions?
Thank you for your time in advanced.
Message posted via http://www.sqlmonster.com
Sven,
Check this article:
http://www.karaszi.com/sqlserver/inf...asp#Searching.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Sven Garber via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:a2eae88fcfcc4a3494bcb278914a66d9@.SQLMonster.c om...
> I ve been ask to diplay some infomation based a certain date and time.
> I'll be using asp and mssql 2k.
> Like friday between 8:00pm to 10pm to display
> then on Saturday between 2:00pm and 4:00pm to display and for the rest of
> the time not display. any suggetions.
> I've been looking at getday()
> any suggetions?
> Thank you for your time in advanced.
> --
> Message posted via http://www.sqlmonster.com
|||Dejan,
Thank you for the link referal...
Sharing is caring...
Thank you
Message posted via http://www.sqlmonster.com

NEWBIE question for ya!

I ve been ask to diplay some infomation based a certain date and time.
I'll be using asp and mssql 2k.
Like friday between 8:00pm to 10pm to display
then on Saturday between 2:00pm and 4:00pm to display and for the rest of
the time not display. any suggetions.
I've been looking at getday()
any suggetions?
Thank you for your time in advanced.
Message posted via http://www.droptable.comSven,
Check this article:
http://www.karaszi.com/sqlserver/in...asp#Searching.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Sven Garber via droptable.com" <forum@.droptable.com> wrote in message
news:a2eae88fcfcc4a3494bcb278914a66d9@.SQ
droptable.com...
> I ve been ask to diplay some infomation based a certain date and time.
> I'll be using asp and mssql 2k.
> Like friday between 8:00pm to 10pm to display
> then on Saturday between 2:00pm and 4:00pm to display and for the rest of
> the time not display. any suggetions.
> I've been looking at getday()
> any suggetions?
> Thank you for your time in advanced.
> --
> Message posted via http://www.droptable.com|||Dejan,
Thank you for the link referal...
Sharing is caring...
Thank you
Message posted via http://www.droptable.com

NEWBIE question for ya!

I ve been ask to diplay some infomation based a certain date and time.
I'll be using asp and mssql 2k.
Like friday between 8:00pm to 10pm to display
then on Saturday between 2:00pm and 4:00pm to display and for the rest of
the time not display. any suggetions.
I've been looking at getday()
any suggetions?
Thank you for your time in advanced.
--
Message posted via http://www.sqlmonster.comSven,
Check this article:
http://www.karaszi.com/sqlserver/info_datetime.asp#Searching.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Sven Garber via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:a2eae88fcfcc4a3494bcb278914a66d9@.SQLMonster.com...
> I ve been ask to diplay some infomation based a certain date and time.
> I'll be using asp and mssql 2k.
> Like friday between 8:00pm to 10pm to display
> then on Saturday between 2:00pm and 4:00pm to display and for the rest of
> the time not display. any suggetions.
> I've been looking at getday()
> any suggetions?
> Thank you for your time in advanced.
> --
> Message posted via http://www.sqlmonster.com|||Dejan,
Thank you for the link referal...
Sharing is caring...
Thank you
--
Message posted via http://www.sqlmonster.com

Newbie question about initial table size

Hi all,

I've worked with informix for a very long time and this is my first aproach to sql server. I have an extremely simple design for a "small" database and at this moment I'm creating the tables, in informix I can assign a first extent and next extent size to the creation of the table so if your volume and growth analisys is good you can basically be sure that you will allways have contigous space on disk for your table. I'm readin BOL to see if I have that feature here but can't seem to find anything similar. Does that mean that my table data will be "fragmented" all over the primary and secondary files every time I load into them? Would it be a good practice to simulate the extents by creating a secondary file for each table with the size I require?

Any coments will be greatly appreciated :)

Luis TorresSpecifying that the db grow in reletively large chunks (so that the file doesn't grow very often) can reduce it's fragmentation. A seperate file for very large tables or for tables that get updated a lot is a good idea. Also you can set up amaintenance plan to run (daily, weekly, etc..) that can reorganize (optimize) the data & indexes.|||The size allocation for tables is done on the extent level. It means that if the last page of the initial extent is filled, a logically contiguous set of 8x8K pages is allocated for the new data. There may be fragmentation between the extents, and depending on your RAID level and array architecture the physical continuity of pages, but "logically" each extent is comprised of 8 continuous pages sitting in a row ;)|||Thanks pshisbey and rdjabarov for your comments, they are greatly appreciated :)

Luis Torres

Newbie question - Getting the headcount at the beginning of the year using OpeningPeriod

Hello,

Here's a newbie question that I am having a heck of a time figuring out...

I want to return only the balance at the begining of the year. My time dimension has a hierarchy as follows:
-Fiscal Year
--Month
Period

I then created (from the template) a Calculated Member that does return the number. However, as you can see, it is only from Fiscal Year 2006, the first period (Period Number = 1)

Aggregate
(
OPENINGPERIOD([Dim Row Date Vw].[FiscalYear - EnglishMonth].[Period Number],
[Dim Row Date Vw].[FiscalYear - EnglishMonth].[FiscalYear].&[2006].&[1]),
[Measures].[ACTIVE]
)

How do I change this so it pulls from the first period for each year, at the beginning of that year?

Thank you for the help.

If you are using AS2005 EE, you may consider using semiadditive measure aggregation function FirstChild which should do exactly what you need.|||

Mosha,

Thank you for the information. I do have AS2005 EE, but how exactly do I use a semiaditive measure to get the count of just Period Number 1 from each year?

I thought that the semiadditive would still add up each Period, so I don't know exactly how to refer to just the Period Number 1.

Thank you for the help.

|||Just change the AggregationFunction of the measure from Sum to FirstChild - and this should be enough. It won't add up along Periods, but will return the first day for the current period.|||

Something so simple, of course I didn't see it.

Thank you so much for the help.

Newbie Question

Hello,
A very simple question:
What happens when two people work at the same time in the same record?
The Last win or the first?It depends on the configuration of your database, what your
application/component is doing in background, what you have
done before starting with the update ...
Normaly you use "optimistic locking", which means you do not
lock the record when you read it or when you start to change it.
The record will only be locked for the short time of writing the
new data in the database.
Now when somebody has changed the same record there could
be two options:
a) your database/component checks that the record was changed
in the meantime and gives an error
b) your database/component only checks if one of the fields you
have changed where changed by another user.
For example: your record holds person number and name
you read the record and change the number, in the meantime
somebody has changed the name. When you write your change
into the database so it only checks if number is like before. This is
because the other user only changed the name. Your update now
only changes the number and when you read the record again you
will see your new number and the new name.
But there are a lot more possibilities, you should read a good book
about transactions and locking mechanism and do tests, test, tests, ...
Another way would be "pessimistic locking" where you read a record
again with locking if the state goes to editing or to lock when you are
reading the record and if locking is okay you can be sure that your
changes can be written and nobody else can change something in the
meantime. But the bad thing is that the record stays still locked if
somebody started editing and then goes to luch before saving the
changes.

bye,
Helmut|||Thanks Helmut
for this full Description.
I will test some scenarios and think I take the last option with the locked records.
Bye Martin

Newbie needs to be told where to go! (Cross-Post)

Greetings:
I've been developing web applications with asp/sql and access for some
time now on existing servers. I am now in a situation that requires me to
install SQL Server (SBS 2K sp3) as the backend for an intranet served from
IIS. I've installed the SQL services and have created the database and I
have some of the web based application written, no problem, but I've yet to
be able to access the database that I created on the newly installed SQL
server from the web based applications. Even after setting up the virtual
directory with the IIS SQL snap-in, I only get a DNS Server not found error
when I try to browse the site. If I remove the IIS/SQL virtual directory, I
can browse pages that don't error out due to data references.
Where can I find a walk through for setting up SQL Server and IIS to
function together in this very basic manner?
Thanks in advance.
--
Jerry M. Gartner
www.gartnerwebdev.comHi,
Have a look at these...
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;176378
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;176380
http://support.microsoft.com/default.aspx?scid=kb;zh-
cn;chs264921
http://support.microsoft.com/default.aspx?scid=kb;PL;264921
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;245243
>--Original Message--
>Greetings:
> I've been developing web applications with asp/sql
and access for some
>time now on existing servers. I am now in a situation
that requires me to
>install SQL Server (SBS 2K sp3) as the backend for an
intranet served from
>IIS. I've installed the SQL services and have created
the database and I
>have some of the web based application written, no
problem, but I've yet to
>be able to access the database that I created on the
newly installed SQL
>server from the web based applications. Even after
setting up the virtual
>directory with the IIS SQL snap-in, I only get a DNS
Server not found error
>when I try to browse the site. If I remove the IIS/SQL
virtual directory, I
>can browse pages that don't error out due to data
references.
> Where can I find a walk through for setting up SQL
Server and IIS to
>function together in this very basic manner?
>Thanks in advance.
>
>--
>Jerry M. Gartner
>www.gartnerwebdev.com
>
>.
>|||Peter:
Preliminarily, this appears to be what I'm looking for. I didn't think
to use ODBC and a DSN. What are the major differences between ODBC and
"direct" connection? There will be under 30 clients using the DB - this is
an on demand intranet application and will be hit lightly and sporadically
throughout the day. The SQL server is also the PDC and fileserver/DFS root
and gateway. (2K SP4)
--
Jerry M. Gartner
www.gartnerwebdev.com
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:03de01c3a83a$14ef9140$a001280a@.phx.gbl...
> Hi,
> Have a look at these...
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;176378
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;176380
> http://support.microsoft.com/default.aspx?scid=kb;zh-
> cn;chs264921
> http://support.microsoft.com/default.aspx?scid=kb;PL;264921
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;245243

Newbie needs help with SQL statement

Hello,

I'm having a difficult time finding the right sql syntax to perform an
update. Here is the situation:

I have two tables, each with an orderid field and a removal_date field.
There is a one-to-many relationship between table A and table B, with B
having multiple records to each one in table A, related by the orderid
field. Table A's primary key is the orderid field, and is the only table
that has data in the removal_date field. I would like to update the
removal_date field in table B with the values of the removal_date field in
table A.

Can this be done with a single sql statement? Right now I'm using a VB
program to build the update sql, but this is cumbersome. Any help would be
appreciated.

Thanks.You would need to check this, but I think it would work.

update TableB
set TableB.removal_date = TableA.removal_date
from Tableb join TableA on TableB.orderid = TableA.orderid

"George J" <gjewell@.houston.rr.com> wrote in message
news:Sxn6d.31407$W21.29433@.fe2.texas.rr.com...
> Hello,
> I'm having a difficult time finding the right sql syntax to perform an
> update. Here is the situation:
> I have two tables, each with an orderid field and a removal_date field.
> There is a one-to-many relationship between table A and table B, with B
> having multiple records to each one in table A, related by the orderid
> field. Table A's primary key is the orderid field, and is the only table
> that has data in the removal_date field. I would like to update the
> removal_date field in table B with the values of the removal_date field in
> table A.
> Can this be done with a single sql statement? Right now I'm using a VB
> program to build the update sql, but this is cumbersome. Any help would be
> appreciated.
> Thanks.|||On Wed, 29 Sep 2004 00:42:26 GMT, George J wrote:

>Hello,
>I'm having a difficult time finding the right sql syntax to perform an
>update. Here is the situation:
>I have two tables, each with an orderid field and a removal_date field.
>There is a one-to-many relationship between table A and table B, with B
>having multiple records to each one in table A, related by the orderid
>field. Table A's primary key is the orderid field, and is the only table
>that has data in the removal_date field. I would like to update the
>removal_date field in table B with the values of the removal_date field in
>table A.
>Can this be done with a single sql statement? Right now I'm using a VB
>program to build the update sql, but this is cumbersome. Any help would be
>appreciated.
>Thanks.

Hi George,

As an alternative to Oscar's suggestion:

UPDATE tableB
SET removal_date = (SELECT removal_date
FROM tableA
WHERE tableA.orderid = tableB.orderid)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Newbie Needs Help Getting Date

Hello,
I have a field in a table that automatically saves the Date and Time.
My field looks like this: 10/10/2003 2:24:40 PM
I need to retrieve ONLY the date portion of this field. Is there a simple way to do this? Any help is greatly appreciated!
Thanks!SELECT CONVERT(varchar(26),GetDate(),1)

Look up CONVERT in BOL for more options...|||...but you'll need to cast is as a date value again if you want to use it like a date:

SELECT CAST(CONVERT(varchar(26),GetDate(),1) as DateTime)

...or my preference because it sorts correctly as varchar...
SELECT CAST(CONVERT(varchar(10),GetDate(),120) as DateTime)

Everybody has their own preference for this, but the general method of casting as a string and then back to datetime (if necessary) is standard.

blindman

P.S.: If you still need help getting a date, check out this link:
http://personals.yahoo.com/|||heh, nice P.S. -- this forum needs the occasional chuckle

by the way, style 120, which blindman mentioned, is the ISO standard format yyyy-mm-dd

is sorts nicely as a varchar because it goes from highest to lowest (well, i didn't say that right, but i hope you know what it means)

in addition, ISO format is always correctly interpreted by all databases when inserting dates

i cannot count how many times i've seen posts on various forums from people who have run into trouble trying to insert values like '04/03/2003' (march 4th or april 3rd?)|||Originally posted by r937
i cannot count how many times i've seen posts on various forums from people who have run into trouble trying to insert values like '04/03/2003' (march 4th or april 3rd?)

Funny, I'm trying to explain that to my fellow programmers right now ... They've got the most intricate functions to fix those problems and they all screw up when someone changes the regional settings on their servers :)|||Nice PS: However, my wife MAY be mad at me for looking at personals, or maybe she is tired of me and would like that!!! LOL

I may not have described my problem well enough or probably I am not understanding since I am new to all this. This is more of what I need...

I have these dates and times: Field Name is GetDate

10/10/2003 2:24:40 PM
10/10/2003 3:34:41 PM
10/20/2003 2:24:40 PM
10/20/2003 6:54:20 PM
10/21/2003 2:24:40 PM
10/21/2003 8:34:43 PM

I need to pull out the different dates and display only one date:

10/10/2003
10/20/2003
10/21/2003

Does that make sense??
Thanks again!!!|||Please tell me you didn't name your field "GetDate". :confused:

You actually are performing two operations, the first is to truncate datetime values (which we've shown you how to do) and the second is reducing the result set to a single row for each date. You can do this using either the SELECT DISTINCT syntax, or if you need to perform aggregate functions on other columns (sum, count, avg etc...) you can use the GROUP BY syntax.

Read up on the SELECT statement in Books Online. It is very powerful, has many options, and is 90%+ of the statements DBA's write.

blindman|||Thanks for the assistance...

I didn't name the field getDate: I misstyped... it is DateSubmitted...

Thinking dateSubmitted and getDate came out of fingers... hehehe

Thanks so much for everything!!!!

newbie help--restore to point in time

I have a customer who's cart has been deleting orders incorrectly. I
backed up and restored the database from the remote server to my local.
Would like to restore to point in time before the delete operation.
>From various sources this is the script I'm using:
BACKUP LOG plebesummercom
TO disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
WITH NORECOVERY, No_truncate
Go
RESTORE DATABASE plebesummercom
FROM disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\dbbu080106\8_1_2006_5_30_
42_PM.bak'
WITH NORECOVERY;
GO
RESTORE LOG [plebesummercom]
FROM DISK = N'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
11:13:04'
GO
Ok, no errrors are thrown, but when I select from a table, I'm still
finding orders with dates past July 16. Am I trying to do something not
allowed? Both the database bu and the log bu contain transactions and
data through August 2nd. Can someone point me in the right direction?Why are you using the NOUNLOAD option when recovering from a file?
Also,
What is the "N" right before the date/time in your STOPAT ?
Your first statement here says to backup log? I think you would want:
RESTORE DATABASE
(FROM ...)
WITH NORECOVERY
--this one for every full log you want to roll in:
RESTORE LOG
(FROM ...)
WITH NORECOVERY
RESTORE LOG
(FROM ...)
WITH RECOVERY, STOPAT = 'Date/time'
Also,
check out the RESTORE topic in Books Online.
I hope this is helpful.
medusa wrote:
> I have a customer who's cart has been deleting orders incorrectly. I
> backed up and restored the database from the remote server to my local.
> Would like to restore to point in time before the delete operation.
> BACKUP LOG plebesummercom
> TO disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
> WITH NORECOVERY, No_truncate
> Go
> RESTORE DATABASE plebesummercom
> FROM disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\dbbu080106\8_1_2006_5_3
0_42_PM.bak'
> WITH NORECOVERY;
> GO
> RESTORE LOG [plebesummercom]
> FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> 11:13:04'
> GO
> Ok, no errrors are thrown, but when I select from a table, I'm still
> finding orders with dates past July 16. Am I trying to do something not
> allowed? Both the database bu and the log bu contain transactions and
> data through August 2nd. Can someone point me in the right direction?|||Well, that was the latest round of code. The n is put in there if you
tell management studio to script the action. The backup log is in there
because if it's not an error is thrown about not backing up the log
tail. Here, this doesn't work either--contains transactions through Aug
2:
BACKUP LOG plebesummercom
TO disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
WITH NORECOVERY, No_truncate
Go
RESTORE DATABASE plebesummercom
FROM disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\dbbu080106\8_1_2006_5_30_
42_PM.bak'
WITH NORECOVERY;
GO
RESTORE LOG [plebesummercom]
FROM DISK ='C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
GO
SGCSNA wrote:[vbcol=seagreen]
> Why are you using the NOUNLOAD option when recovering from a file?
> Also,
> What is the "N" right before the date/time in your STOPAT ?
> Your first statement here says to backup log? I think you would want:
> RESTORE DATABASE
> (FROM ...)
> WITH NORECOVERY
> --this one for every full log you want to roll in:
> RESTORE LOG
> (FROM ...)
> WITH NORECOVERY
> RESTORE LOG
> (FROM ...)
> WITH RECOVERY, STOPAT = 'Date/time'
> Also,
> check out the RESTORE topic in Books Online.
> I hope this is helpful.
>
> medusa wrote:|||Are you restoring from a full backup that falls *before* your STOPAT
date and all of the subsequent logs, up to the one that contains the
STOPAT date?
medusa wrote:[vbcol=seagreen]
> Well, that was the latest round of code. The n is put in there if you
> tell management studio to script the action. The backup log is in there
> because if it's not an error is thrown about not backing up the log
> tail. Here, this doesn't work either--contains transactions through Aug
> 2:
> BACKUP LOG plebesummercom
> TO disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
> WITH NORECOVERY, No_truncate
> Go
> RESTORE DATABASE plebesummercom
> FROM disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\dbbu080106\8_1_2006_5_3
0_42_PM.bak'
> WITH NORECOVERY;
> GO
> RESTORE LOG [plebesummercom]
> FROM DISK ='C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
> WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> GO
>
> SGCSNA wrote:|||The full backup goes through Aug 2. I want to stop at July 16.
SGCSNA wrote:[vbcol=seagreen]
> Are you restoring from a full backup that falls *before* your STOPAT
> date and all of the subsequent logs, up to the one that contains the
> STOPAT date?
> medusa wrote:|||STOPAT only works with Transaction Log Backups, so you would need a
full backup from sometime before the time you want on July 16th and the
subsequent log backups through the time you want on 7/16.
medusa wrote:[vbcol=seagreen]
> The full backup goes through Aug 2. I want to stop at July 16.
> SGCSNA wrote:|||Thanks a lot for you help.
SGCSNA wrote:[vbcol=seagreen]
> STOPAT only works with Transaction Log Backups, so you would need a
> full backup from sometime before the time you want on July 16th and the
> subsequent log backups through the time you want on 7/16.
> medusa wrote:

newbie help--restore to point in time

I have a customer who's cart has been deleting orders incorrectly. I
backed up and restored the database from the remote server to my local.
Would like to restore to point in time before the delete operation.
>From various sources this is the script I'm using:
BACKUP LOG plebesummercom
TO disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
WITH NORECOVERY, No_truncate
Go
RESTORE DATABASE plebesummercom
FROM disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
WITH NORECOVERY;
GO
RESTORE LOG [plebesummercom]
FROM DISK = N'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
11:13:04'
GO
Ok, no errrors are thrown, but when I select from a table, I'm still
finding orders with dates past July 16. Am I trying to do something not
allowed? Both the database bu and the log bu contain transactions and
data through August 2nd. Can someone point me in the right direction?Why are you using the NOUNLOAD option when recovering from a file?
Also,
What is the "N" right before the date/time in your STOPAT ?
Your first statement here says to backup log? I think you would want:
RESTORE DATABASE
(FROM ...)
WITH NORECOVERY
--this one for every full log you want to roll in:
RESTORE LOG
(FROM ...)
WITH NORECOVERY
RESTORE LOG
(FROM ...)
WITH RECOVERY, STOPAT = 'Date/time'
Also,
check out the RESTORE topic in Books Online.
I hope this is helpful.
medusa wrote:
> I have a customer who's cart has been deleting orders incorrectly. I
> backed up and restored the database from the remote server to my local.
> Would like to restore to point in time before the delete operation.
> >From various sources this is the script I'm using:
> BACKUP LOG plebesummercom
> TO disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> WITH NORECOVERY, No_truncate
> Go
> RESTORE DATABASE plebesummercom
> FROM disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> WITH NORECOVERY;
> GO
> RESTORE LOG [plebesummercom]
> FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> 11:13:04'
> GO
> Ok, no errrors are thrown, but when I select from a table, I'm still
> finding orders with dates past July 16. Am I trying to do something not
> allowed? Both the database bu and the log bu contain transactions and
> data through August 2nd. Can someone point me in the right direction?|||Well, that was the latest round of code. The n is put in there if you
tell management studio to script the action. The backup log is in there
because if it's not an error is thrown about not backing up the log
tail. Here, this doesn't work either--contains transactions through Aug
2:
BACKUP LOG plebesummercom
TO disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
WITH NORECOVERY, No_truncate
Go
RESTORE DATABASE plebesummercom
FROM disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
WITH NORECOVERY;
GO
RESTORE LOG [plebesummercom]
FROM DISK ='C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
GO
SGCSNA wrote:
> Why are you using the NOUNLOAD option when recovering from a file?
> Also,
> What is the "N" right before the date/time in your STOPAT ?
> Your first statement here says to backup log? I think you would want:
> RESTORE DATABASE
> (FROM ...)
> WITH NORECOVERY
> --this one for every full log you want to roll in:
> RESTORE LOG
> (FROM ...)
> WITH NORECOVERY
> RESTORE LOG
> (FROM ...)
> WITH RECOVERY, STOPAT = 'Date/time'
> Also,
> check out the RESTORE topic in Books Online.
> I hope this is helpful.
>
> medusa wrote:
> > I have a customer who's cart has been deleting orders incorrectly. I
> > backed up and restored the database from the remote server to my local.
> > Would like to restore to point in time before the delete operation.
> > >From various sources this is the script I'm using:
> >
> > BACKUP LOG plebesummercom
> > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > WITH NORECOVERY, No_truncate
> > Go
> > RESTORE DATABASE plebesummercom
> > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > WITH NORECOVERY;
> > GO
> >
> > RESTORE LOG [plebesummercom]
> > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> >
> > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > 11:13:04'
> > GO
> >
> > Ok, no errrors are thrown, but when I select from a table, I'm still
> > finding orders with dates past July 16. Am I trying to do something not
> > allowed? Both the database bu and the log bu contain transactions and
> > data through August 2nd. Can someone point me in the right direction?|||Are you restoring from a full backup that falls *before* your STOPAT
date and all of the subsequent logs, up to the one that contains the
STOPAT date?
medusa wrote:
> Well, that was the latest round of code. The n is put in there if you
> tell management studio to script the action. The backup log is in there
> because if it's not an error is thrown about not backing up the log
> tail. Here, this doesn't work either--contains transactions through Aug
> 2:
> BACKUP LOG plebesummercom
> TO disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> WITH NORECOVERY, No_truncate
> Go
> RESTORE DATABASE plebesummercom
> FROM disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> WITH NORECOVERY;
> GO
> RESTORE LOG [plebesummercom]
> FROM DISK ='C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> GO
>
> SGCSNA wrote:
> > Why are you using the NOUNLOAD option when recovering from a file?
> >
> > Also,
> > What is the "N" right before the date/time in your STOPAT ?
> >
> > Your first statement here says to backup log? I think you would want:
> >
> > RESTORE DATABASE
> > (FROM ...)
> > WITH NORECOVERY
> >
> > --this one for every full log you want to roll in:
> > RESTORE LOG
> > (FROM ...)
> > WITH NORECOVERY
> >
> > RESTORE LOG
> > (FROM ...)
> > WITH RECOVERY, STOPAT = 'Date/time'
> >
> > Also,
> >
> > check out the RESTORE topic in Books Online.
> >
> > I hope this is helpful.
> >
> >
> > medusa wrote:
> > > I have a customer who's cart has been deleting orders incorrectly. I
> > > backed up and restored the database from the remote server to my local.
> > > Would like to restore to point in time before the delete operation.
> > > >From various sources this is the script I'm using:
> > >
> > > BACKUP LOG plebesummercom
> > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > WITH NORECOVERY, No_truncate
> > > Go
> > > RESTORE DATABASE plebesummercom
> > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > WITH NORECOVERY;
> > > GO
> > >
> > > RESTORE LOG [plebesummercom]
> > > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > >
> > > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > > 11:13:04'
> > > GO
> > >
> > > Ok, no errrors are thrown, but when I select from a table, I'm still
> > > finding orders with dates past July 16. Am I trying to do something not
> > > allowed? Both the database bu and the log bu contain transactions and
> > > data through August 2nd. Can someone point me in the right direction?|||The full backup goes through Aug 2. I want to stop at July 16.
SGCSNA wrote:
> Are you restoring from a full backup that falls *before* your STOPAT
> date and all of the subsequent logs, up to the one that contains the
> STOPAT date?
> medusa wrote:
> > Well, that was the latest round of code. The n is put in there if you
> > tell management studio to script the action. The backup log is in there
> > because if it's not an error is thrown about not backing up the log
> > tail. Here, this doesn't work either--contains transactions through Aug
> > 2:
> >
> > BACKUP LOG plebesummercom
> > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > WITH NORECOVERY, No_truncate
> > Go
> > RESTORE DATABASE plebesummercom
> > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > WITH NORECOVERY;
> > GO
> >
> > RESTORE LOG [plebesummercom]
> > FROM DISK ='C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> >
> > WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> > GO
> >
> >
> > SGCSNA wrote:
> > > Why are you using the NOUNLOAD option when recovering from a file?
> > >
> > > Also,
> > > What is the "N" right before the date/time in your STOPAT ?
> > >
> > > Your first statement here says to backup log? I think you would want:
> > >
> > > RESTORE DATABASE
> > > (FROM ...)
> > > WITH NORECOVERY
> > >
> > > --this one for every full log you want to roll in:
> > > RESTORE LOG
> > > (FROM ...)
> > > WITH NORECOVERY
> > >
> > > RESTORE LOG
> > > (FROM ...)
> > > WITH RECOVERY, STOPAT = 'Date/time'
> > >
> > > Also,
> > >
> > > check out the RESTORE topic in Books Online.
> > >
> > > I hope this is helpful.
> > >
> > >
> > > medusa wrote:
> > > > I have a customer who's cart has been deleting orders incorrectly. I
> > > > backed up and restored the database from the remote server to my local.
> > > > Would like to restore to point in time before the delete operation.
> > > > >From various sources this is the script I'm using:
> > > >
> > > > BACKUP LOG plebesummercom
> > > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > WITH NORECOVERY, No_truncate
> > > > Go
> > > > RESTORE DATABASE plebesummercom
> > > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > > WITH NORECOVERY;
> > > > GO
> > > >
> > > > RESTORE LOG [plebesummercom]
> > > > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > >
> > > > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > > > 11:13:04'
> > > > GO
> > > >
> > > > Ok, no errrors are thrown, but when I select from a table, I'm still
> > > > finding orders with dates past July 16. Am I trying to do something not
> > > > allowed? Both the database bu and the log bu contain transactions and
> > > > data through August 2nd. Can someone point me in the right direction?|||STOPAT only works with Transaction Log Backups, so you would need a
full backup from sometime before the time you want on July 16th and the
subsequent log backups through the time you want on 7/16.
medusa wrote:
> The full backup goes through Aug 2. I want to stop at July 16.
> SGCSNA wrote:
> > Are you restoring from a full backup that falls *before* your STOPAT
> > date and all of the subsequent logs, up to the one that contains the
> > STOPAT date?
> > medusa wrote:
> > > Well, that was the latest round of code. The n is put in there if you
> > > tell management studio to script the action. The backup log is in there
> > > because if it's not an error is thrown about not backing up the log
> > > tail. Here, this doesn't work either--contains transactions through Aug
> > > 2:
> > >
> > > BACKUP LOG plebesummercom
> > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > WITH NORECOVERY, No_truncate
> > > Go
> > > RESTORE DATABASE plebesummercom
> > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > WITH NORECOVERY;
> > > GO
> > >
> > > RESTORE LOG [plebesummercom]
> > > FROM DISK ='C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > >
> > > WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> > > GO
> > >
> > >
> > > SGCSNA wrote:
> > > > Why are you using the NOUNLOAD option when recovering from a file?
> > > >
> > > > Also,
> > > > What is the "N" right before the date/time in your STOPAT ?
> > > >
> > > > Your first statement here says to backup log? I think you would want:
> > > >
> > > > RESTORE DATABASE
> > > > (FROM ...)
> > > > WITH NORECOVERY
> > > >
> > > > --this one for every full log you want to roll in:
> > > > RESTORE LOG
> > > > (FROM ...)
> > > > WITH NORECOVERY
> > > >
> > > > RESTORE LOG
> > > > (FROM ...)
> > > > WITH RECOVERY, STOPAT = 'Date/time'
> > > >
> > > > Also,
> > > >
> > > > check out the RESTORE topic in Books Online.
> > > >
> > > > I hope this is helpful.
> > > >
> > > >
> > > > medusa wrote:
> > > > > I have a customer who's cart has been deleting orders incorrectly. I
> > > > > backed up and restored the database from the remote server to my local.
> > > > > Would like to restore to point in time before the delete operation.
> > > > > >From various sources this is the script I'm using:
> > > > >
> > > > > BACKUP LOG plebesummercom
> > > > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > > WITH NORECOVERY, No_truncate
> > > > > Go
> > > > > RESTORE DATABASE plebesummercom
> > > > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > > > WITH NORECOVERY;
> > > > > GO
> > > > >
> > > > > RESTORE LOG [plebesummercom]
> > > > > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > >
> > > > > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > > > > 11:13:04'
> > > > > GO
> > > > >
> > > > > Ok, no errrors are thrown, but when I select from a table, I'm still
> > > > > finding orders with dates past July 16. Am I trying to do something not
> > > > > allowed? Both the database bu and the log bu contain transactions and
> > > > > data through August 2nd. Can someone point me in the right direction?|||Thanks a lot for you help.
SGCSNA wrote:
> STOPAT only works with Transaction Log Backups, so you would need a
> full backup from sometime before the time you want on July 16th and the
> subsequent log backups through the time you want on 7/16.
> medusa wrote:
> > The full backup goes through Aug 2. I want to stop at July 16.
> >
> > SGCSNA wrote:
> > > Are you restoring from a full backup that falls *before* your STOPAT
> > > date and all of the subsequent logs, up to the one that contains the
> > > STOPAT date?
> > > medusa wrote:
> > > > Well, that was the latest round of code. The n is put in there if you
> > > > tell management studio to script the action. The backup log is in there
> > > > because if it's not an error is thrown about not backing up the log
> > > > tail. Here, this doesn't work either--contains transactions through Aug
> > > > 2:
> > > >
> > > > BACKUP LOG plebesummercom
> > > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > WITH NORECOVERY, No_truncate
> > > > Go
> > > > RESTORE DATABASE plebesummercom
> > > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > > WITH NORECOVERY;
> > > > GO
> > > >
> > > > RESTORE LOG [plebesummercom]
> > > > FROM DISK ='C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > >
> > > > WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> > > > GO
> > > >
> > > >
> > > > SGCSNA wrote:
> > > > > Why are you using the NOUNLOAD option when recovering from a file?
> > > > >
> > > > > Also,
> > > > > What is the "N" right before the date/time in your STOPAT ?
> > > > >
> > > > > Your first statement here says to backup log? I think you would want:
> > > > >
> > > > > RESTORE DATABASE
> > > > > (FROM ...)
> > > > > WITH NORECOVERY
> > > > >
> > > > > --this one for every full log you want to roll in:
> > > > > RESTORE LOG
> > > > > (FROM ...)
> > > > > WITH NORECOVERY
> > > > >
> > > > > RESTORE LOG
> > > > > (FROM ...)
> > > > > WITH RECOVERY, STOPAT = 'Date/time'
> > > > >
> > > > > Also,
> > > > >
> > > > > check out the RESTORE topic in Books Online.
> > > > >
> > > > > I hope this is helpful.
> > > > >
> > > > >
> > > > > medusa wrote:
> > > > > > I have a customer who's cart has been deleting orders incorrectly. I
> > > > > > backed up and restored the database from the remote server to my local.
> > > > > > Would like to restore to point in time before the delete operation.
> > > > > > >From various sources this is the script I'm using:
> > > > > >
> > > > > > BACKUP LOG plebesummercom
> > > > > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > > > WITH NORECOVERY, No_truncate
> > > > > > Go
> > > > > > RESTORE DATABASE plebesummercom
> > > > > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > > > > WITH NORECOVERY;
> > > > > > GO
> > > > > >
> > > > > > RESTORE LOG [plebesummercom]
> > > > > > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > > >
> > > > > > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > > > > > 11:13:04'
> > > > > > GO
> > > > > >
> > > > > > Ok, no errrors are thrown, but when I select from a table, I'm still
> > > > > > finding orders with dates past July 16. Am I trying to do something not
> > > > > > allowed? Both the database bu and the log bu contain transactions and
> > > > > > data through August 2nd. Can someone point me in the right direction?

Newbie Entity Relationship Advice

Dear All,
Firstly since the first time posting on this group, I apologise if this is
posted on the wrong newsgroup but could someone please help me with the
question below.
I have received a database designed by a colleague who has since left the
organisation and I was wondering if there were any tools or techniques that
could help create an entity relationship diagram of the database. With Acces
s
you had a utility called database documentor, which could help me with my
task. If this is not possible I would like to get a list of all tables and
Primary/Foreign key constraints.
There are over 100 tables and it would take a long time to go through each
one looking at primary keys and foreign keys. Is there anyone out there that
can offer this SQL Server 2000 newbie some helping advice?
p.s. What newsgroup should I have posted a general question on?
Thanks.
Alastair MacFarlaneYes. You can do this in SQL Server 2000. Look in the Database and you will
see a "Diagrams" icon. Use that to create the entity relationship.
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:477DE3D1-0457-4AF3-B067-BA5A880364F0@.microsoft.com...
> Dear All,
> Firstly since the first time posting on this group, I apologise if this is
> posted on the wrong newsgroup but could someone please help me with the
> question below.
> I have received a database designed by a colleague who has since left the
> organisation and I was wondering if there were any tools or techniques
> that
> could help create an entity relationship diagram of the database. With
> Access
> you had a utility called database documentor, which could help me with my
> task. If this is not possible I would like to get a list of all tables
> and
> Primary/Foreign key constraints.
> There are over 100 tables and it would take a long time to go through each
> one looking at primary keys and foreign keys. Is there anyone out there
> that
> can offer this SQL Server 2000 newbie some helping advice?
> p.s. What newsgroup should I have posted a general question on?
> Thanks.
> Alastair MacFarlane|||The Database Diagram tool within Enterprise Manager allows you to create a
visual diagram of the database that can also be used to create, edit or
delete tables. You can launch the wizard by right-cling on the Diagrams
node under the desired database and selecting 'new database diagram'. See
the Books Online for details on usage.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:477DE3D1-0457-4AF3-B067-BA5A880364F0@.microsoft.com...
> Dear All,
> Firstly since the first time posting on this group, I apologise if this is
> posted on the wrong newsgroup but could someone please help me with the
> question below.
> I have received a database designed by a colleague who has since left the
> organisation and I was wondering if there were any tools or techniques
> that
> could help create an entity relationship diagram of the database. With
> Access
> you had a utility called database documentor, which could help me with my
> task. If this is not possible I would like to get a list of all tables
> and
> Primary/Foreign key constraints.
> There are over 100 tables and it would take a long time to go through each
> one looking at primary keys and foreign keys. Is there anyone out there
> that
> can offer this SQL Server 2000 newbie some helping advice?
> p.s. What newsgroup should I have posted a general question on?
> Thanks.
> Alastair MacFarlane|||With SQL Server, create a DB diagram via Enterprise manager by selecting all
the tables. This will get u the ERD along with all existing relationships...
R
"Alastair MacFarlane" wrote:

> Dear All,
> Firstly since the first time posting on this group, I apologise if this is
> posted on the wrong newsgroup but could someone please help me with the
> question below.
> I have received a database designed by a colleague who has since left the
> organisation and I was wondering if there were any tools or techniques tha
t
> could help create an entity relationship diagram of the database. With Acc
ess
> you had a utility called database documentor, which could help me with my
> task. If this is not possible I would like to get a list of all tables an
d
> Primary/Foreign key constraints.
> There are over 100 tables and it would take a long time to go through each
> one looking at primary keys and foreign keys. Is there anyone out there th
at
> can offer this SQL Server 2000 newbie some helping advice?
> p.s. What newsgroup should I have posted a general question on?
> Thanks.
> Alastair MacFarlane|||Thanks Yosh, Dan and Rakesh. I didn't realise that the wizard would create
the entity diagram for you. This is better that Access!
Alastair
"Yosh" wrote:

> Yes. You can do this in SQL Server 2000. Look in the Database and you will
> see a "Diagrams" icon. Use that to create the entity relationship.
>
> "Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
> in message news:477DE3D1-0457-4AF3-B067-BA5A880364F0@.microsoft.com...
>
>|||The Diagram will automatically draw connecting lines only if referential
integrity constraints were implemented on the tables.
"Yosh" <Yosh@.nospam.com> wrote in message
news:u3iMO5irFHA.3788@.TK2MSFTNGP12.phx.gbl...
> Yes. You can do this in SQL Server 2000. Look in the Database and you will
> see a "Diagrams" icon. Use that to create the entity relationship.
>
> "Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
> in message news:477DE3D1-0457-4AF3-B067-BA5A880364F0@.microsoft.com...
>

Newbie Alert !

Dear Folks,

First time here.

Have just installed a 120-day evaluation copy of MSSQL 2000.

In the 'blur' and confusion of installing this on my WinXP Laptop, I
forgot to record the password.

Finally, I got into the 'logins' tab on the Enterprise Manager, and
modified the 'sa' password to 'ivlem1', and I think it was recorded
properly.

I tried to test my installation with the following at the Command
Prompt:

osql /Usa /P ivlem1

It failed saying I wasn't 'Trusted'

I would appreciate any help offered !

TIA,

-Mel SmithDid you select "mixed" mode for authentication. I would test this
differently...fire up QA and use your SA account/passwd to account.
There are too many things that could go wrong with establishing a
connection using osql.

HTH

MJKulangara
http://sqladventures.blogspot.com|||Mel Smith (medsyntel@.aol.com) writes:
> Have just installed a 120-day evaluation copy of MSSQL 2000.
> In the 'blur' and confusion of installing this on my WinXP Laptop, I
> forgot to record the password.
> Finally, I got into the 'logins' tab on the Enterprise Manager, and
> modified the 'sa' password to 'ivlem1', and I think it was recorded
> properly.
> I tried to test my installation with the following at the Command
> Prompt:
> osql /Usa /P ivlem1
> It failed saying I wasn't 'Trusted'

SQL Server have to means of authentication: Windows authentication (a.k.a
Integrated Security or Trusted Connection) and SQL Server authentication.
Windows authentication is always there, SQL authentication is optional,
and usually referred to as "mixed mode".

Windows authentcation means that you log into SQL Server with your
Windows credentials. This is very simple, you just say "osql /E" and
you are on the air. No need for password, as you are already logged
into Windows.

By default, only Windows authentication is enabled. This is because
SQL Server authentication is less secure. However, many enable
SQL authentiation for various reasons. For instance, if you are a
developer you may work with Windows authentication as long as you
develop and have sysadmin rights. But when you need to test, you
want a low-priv user, and testing different users is much easier
with SQL authentication.

You can change authentication mode from Enterprise Manager. Right-
click the server, select Properties, and then use the Security tab.

--
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|||Erland and MJK,

Thank you both for your advice !

Erland:

I tried "osql /E" and it worked ! (i.e., I got correct correct results
for the two queries: select @.@.servername and select @.@.version)

(now I'm going to go blind reading all there is on Books Online to get
myself up to speed )

btw, I'm fairly expert in xBase databases (my job is tech support for a
structural steel company that uses my software, but is now moving to MSSQL)
and I have to show that I'm up to the task to help them make that move. In
fact, they have purchased AccPac for Windows, and have installed MSSQL 2000.
But I'm at a remote location from the headquarters of this company and must
get used to MSSQL all by myself in the 'sunny south'.

Thanks again.

-Mel Smith
Mesa, Arizona, USA

Newbie : Need help in Sorting

I have written a query to find out the total worked hours & total dollars
for the employees who have worked in a particular time period .I need to sort them such that the employees with total amount =0 ( for the given period) come first followed by the employees who are to be paid .
At the end , I want to count the number of employees with total Amt = 0
& then the count of no. of employees who are to be paid . Here is the query

SELECT TAB1.EMP_ID ,
TAB1.EMP_NAME ,
TO_CHAR(ROUND((SUM(TAB1.WRKD_MINUTES))/60,2))
WRKD_HOURS,
TO_CHAR(ROUND(SUM(TAB1.WRKD_MINUTES *
TAB1.WRKD_RATE)/60)) TOTAL_AMT
FROM
( SELECT
EMPLOYEE.EMP_ID,
EMPLOYEE.EMP_NAME,
(EMPLOYEE.EMP_FIRSTNAME||' '||EMPLOYEE.EMP_LASTNAME) FULLNAME,
HOUR_TYPE.HTYPE_NAME,
TIME_CODE.TCODE_NAME,
WORK_DETAIL.WRKD_WORK_DATE,
WORK_DETAIL.WRKD_MINUTES,
WORK_DETAIL.WRKD_RATE,
TO_CHAR(ROUND((WORK_DETAIL.WRKD_MINUTES)/60,2),'9999.00') WRKD_HOURS,
TO_CHAR(ROUND((WORK_DETAIL.WRKD_MINUTES * WORK_DETAIL.WRKD_RATE)/60)) TOTAL_AMT,
CALC_GROUP.CALCGRP_NAME,
PAY_GROUP.PAYGRP_NAME
FROM
EMPLOYEE, HOUR_TYPE,TIME_CODE, WORK_DETAIL,
WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP
-- WORKBRAIN_TEAM,EMPLOYEE_TEAM
WHERE
CALC_GROUP.CALCGRP_ID = EMPLOYEE.CALCGRP_ID
AND HOUR_TYPE.HTYPE_ID = WORK_DETAIL.HTYPE_ID
AND HOUR_TYPE.HTYPE_ID = TIME_CODE.HTYPE_ID
-- AND hour_type.htype_name = 'UNPAID'
-- AND time_code.tcode_name = 'UAT'
AND WORK_DETAIL.WRKS_ID = WORK_SUMMARY.WRKS_ID
AND WORK_SUMMARY.EMP_ID = EMPLOYEE.EMP_ID
-- AND WORK_DETAIL.WRKD_WORK_DATE < SYSDATE
-- AND WORKBRAIN_TEAM.WBT_ID = EMPLOYEE_TEAM.WBT_ID
-- AND EMPLOYEE_TEAM.EMP_ID = EMPLOYEE.EMP_ID
AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
AND EMPLOYEE.PAYGRP_ID = PAY_GROUP.PAYGRP_ID ) TAB1
GROUP BY EMP_ID,EMP_NAME

I have tried to group by Total Amount but it does not give the necessary order . Also , the database I am using is Oracle 8I enterprise version 8.1.7.7.0 .When I try to avoid TO_CHAR & just use ROUND , My query goes in a loop .You cannot group by the Total Amount, but you can order by it. Add this at the end:

ORDER BY total_amt

I don't understand your other problem with the ROUND.|||I had earlier tried ORDER BY .But it gives an error message ,
ERROR-937 at Line 1 Column 1 Message :ORA-00937:not a single-group group function.

I was trying to avoid TO_CHAR so that I can test the query by giving conditions such as TOTAL_AMT > 45 etc .When I use TO_CHAR , I cannot use this comparisons .|||If I remove TO_CHAR , My query does not fetch any result & when I try to refresh , it says "Data is being retrieved " .But I know , that the query would not return any results & I have to abort it .|||Originally posted by ritz1975
If I remove TO_CHAR , My query does not fetch any result & when I try to refresh , it says "Data is being retrieved " .But I know , that the query would not return any results & I have to abort it .
Post your query - without the TO_CHARs and with the ORDER BY that you tried. This isn't making sense!|||I again gives the same oracle error msg which i posted .|||Originally posted by andrewst
Post your query - without the TO_CHARs and with the ORDER BY that you tried. This isn't making sense!
I also don't understand why you are doing a select from a select - why not just this:

SELECT
EMPLOYEE.EMP_ID,
EMPLOYEE.EMP_NAME,
ROUND((SUM(WORK_DETAIL.WRKD_MINUTES))/60,2) WRKD_HOURS,
ROUND(SUM(WORK_DETAIL.WRKD_MINUTES * WORK_DETAIL.WRKD_RATE)/60) TOTAL_AMT
FROM
EMPLOYEE, HOUR_TYPE,TIME_CODE, WORK_DETAIL,
WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP
-- WORKBRAIN_TEAM,EMPLOYEE_TEAM
WHERE
CALC_GROUP.CALCGRP_ID = EMPLOYEE.CALCGRP_ID
AND HOUR_TYPE.HTYPE_ID = WORK_DETAIL.HTYPE_ID
AND HOUR_TYPE.HTYPE_ID = TIME_CODE.HTYPE_ID
-- AND hour_type.htype_name = 'UNPAID'
-- AND time_code.tcode_name = 'UAT'
AND WORK_DETAIL.WRKS_ID = WORK_SUMMARY.WRKS_ID
AND WORK_SUMMARY.EMP_ID = EMPLOYEE.EMP_ID
-- AND WORK_DETAIL.WRKD_WORK_DATE < SYSDATE
-- AND WORKBRAIN_TEAM.WBT_ID = EMPLOYEE_TEAM.WBT_ID
-- AND EMPLOYEE_TEAM.EMP_ID = EMPLOYEE.EMP_ID
AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
AND EMPLOYEE.PAYGRP_ID = PAY_GROUP.PAYGRP_ID
GROUP BY EMP_ID,EMP_NAME
ORDER BY TOTAL_AMT
?|||Originally posted by ritz1975
I again gives the same oracle error msg which i posted .
Try the version I just posted - or show me the code that you are actually running that gives the error message.|||That's because I have to use the other variables like Hourtpe ,Calcgroup,Paygroup etc . in the report view .I am working on the PL/SQL inside a product called WORKBRAIN ERM (Employee Relationship Management) .

Unless I use these variables in the SELECT ,I cannot define report criteria while designing the report .

I am trying your query now .|||I know nothing about WORKBRAIN ERM, but maybe it is part of the problem. I suggest you try running your query in SQL Plus first. Once it works there, THEN cut and paste it into WORKBRAIN ERM. If it then doesn't work, you know where the problem lies...

I understand that you need to join to various tables to restrict your query, but I still don't see why that should mean you need to use an inline view (SELECT FROM (SELECT FROM))|||I tried to run your code .But it now gives an error message ,
ERROR-918 at Line 1 Column 1 Message :ORA-00918:column ambiguously defined .

I changed the table order in the FROM statement to
EMPLOYEE,WORK_DETAIL,HOUR_TYPE,TIME_CODE,
WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP

instead of EMPLOYEE,HOUR_TYPE,TIME_CODE,WORK_DETAIL,
WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP

but the result is the same .|||See here is the sample code of the view

CREATE OR REPLACE VIEW REPT_VIEW_IP_S_PAID_UNPAID
(
EMP_ID,
EMP_NAME,
FULLNAME,
HYPE_NAME,
TCODE_NAME,
WRKD_WORK_DATE,
WRKD_MINUTES,
WRKD_HOURS,
TOTAL_AMT,
CALCGRP_NAME,
PAYGRP_NAME
)
AS
SELECT
EMPLOYEE.EMP_ID,
EMPLOYEE.EMP_NAME,
(EMPLOYEE.EMP_FIRSTNAME||' '||EMPLOYEE.EMP_LASTNAME) FULLNAME,
HOUR_TYPE.HTYPE_NAME,
TIME_CODE.TCODE_NAME,
WORK_DETAIL.WRKD_WORK_DATE,
WORK_DETAIL.WRKD_MINUTES,
TO_CHAR(ROUND(SUM(WORK_DETAIL.WRKD_MINUTES)/60,2),'9999.00') WRKD_HOURS,
TO_CHAR(ROUND(SUM(WORK_DETAIL.WRKD_MINUTES * WORK_DETAIL.WRKD_RATE)/60)) TOTAL_AMT,
CALC_GROUP.CALCGRP_NAME,
PAY_GROUP.PAYGRP_NAME
FROM
EMPLOYEE, HOUR_TYPE,TIME_CODE, WORK_DETAIL,
WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP
-- WORKBRAIN_TEAM,EMPLOYEE_TEAM
WHERE
CALC_GROUP.CALCGRP_ID = EMPLOYEE.CALCGRP_ID
AND HOUR_TYPE.HTYPE_ID = WORK_DETAIL.HTYPE_ID
AND HOUR_TYPE.HTYPE_ID = TIME_CODE.HTYPE_ID
-- AND hour_type.htype_name <> 'UNPAID'
-- AND time_code.tcode_name <> 'UAT'
AND WORK_DETAIL.WRKS_ID = WORK_SUMMARY.WRKS_ID
AND WORK_SUMMARY.EMP_ID = EMPLOYEE.EMP_ID
-- AND WORK_DETAIL.WRKD_WORK_DATE < SYSDATE
-- AND WORKBRAIN_TEAM.WBT_ID = EMPLOYEE_TEAM.WBT_ID
-- AND EMPLOYEE_TEAM.EMP_ID = EMPLOYEE.EMP_ID
AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
AND EMPLOYEE.PAYGRP_ID = PAY_GROUP.PAYGRP_ID

I am using all those SELECT fields 'coz If I do not select all of them in the same order , I cannot define them under Create view statement|||I had forgotten to put the table aliases on the GROUP BY columns. Try this now:

SELECT
EMPLOYEE.EMP_ID,
EMPLOYEE.EMP_NAME,
ROUND((SUM(WORK_DETAIL.WRKD_MINUTES))/60,2) WRKD_HOURS,
ROUND(SUM(WORK_DETAIL.WRKD_MINUTES * WORK_DETAIL.WRKD_RATE)/60) TOTAL_AMT
FROM
EMPLOYEE, HOUR_TYPE,TIME_CODE, WORK_DETAIL,
WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP
-- WORKBRAIN_TEAM,EMPLOYEE_TEAM
WHERE
CALC_GROUP.CALCGRP_ID = EMPLOYEE.CALCGRP_ID
AND HOUR_TYPE.HTYPE_ID = WORK_DETAIL.HTYPE_ID
AND HOUR_TYPE.HTYPE_ID = TIME_CODE.HTYPE_ID
-- AND hour_type.htype_name = 'UNPAID'
-- AND time_code.tcode_name = 'UAT'
AND WORK_DETAIL.WRKS_ID = WORK_SUMMARY.WRKS_ID
AND WORK_SUMMARY.EMP_ID = EMPLOYEE.EMP_ID
-- AND WORK_DETAIL.WRKD_WORK_DATE < SYSDATE
-- AND WORKBRAIN_TEAM.WBT_ID = EMPLOYEE_TEAM.WBT_ID
-- AND EMPLOYEE_TEAM.EMP_ID = EMPLOYEE.EMP_ID
AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
AND EMPLOYEE.PAYGRP_ID = PAY_GROUP.PAYGRP_ID
GROUP BY EMPLOYEE.EMP_ID,EMPLOYEE.EMP_NAME
ORDER BY TOTAL_AMT|||That Works .Some how it still goes into a loop when I am using ROUND .I used TO_CHAR in fornt of both these variables & it then works .

Don't know why it does not work with a simple ROUND .I think I will need to check with the vendor .

Thanks a lot ,Andrewst ...looks like this should complete my query|||Also , how do I get the counts of employees with Total amount = 0 & the rest separately ?|||Originally posted by ritz1975
Also , how do I get the counts of employees with Total amount = 0 & the rest separately ?
Now that DOES need an inline view:

SELECT COUNT(*) FROM
( SELECT emp_id, SUM(...)
FROM ...
GROUP BY emp_id
HAVING SUM(...) = 0
)

newbee question on function

Hi,
I created some functions in SQL server 2000 server. Every time I use the
functions I created I have to prefix them with dbo., say dbo.myFunction. Is
there a way to get around it?
TIANope. Qualifying scalar UFD with owner is mandatory. In fact, owner qualifyi
ng in general is a very
good thing to do, so you should get into the habit of always doing it...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Danny Ni" <dnd@.yahoo.com> wrote in message news:%23KV4rzSaFHA.2128@.TK2MSFTNGP14.phx.gbl...

> Hi,
> I created some functions in SQL server 2000 server. Every time I use the
> functions I created I have to prefix them with dbo., say dbo.myFunction. I
s
> there a way to get around it?
> TIA
>

newbe ? re:date format

Hi, I'm developing a page in asp.net using a sqldb and one of the values I'm sending to the database is a date/time value. I only want the table to show the date in short format, no time. Is there a way to restrict or convert the data in the table design mode?
Thanks
RaifSQL Server dates are stored as numeric values that interpreted as dates with time of day. Formatting of output should be handled by your user interface, not the database server.|||Thanks I sort suspected that as I started writing the post.
Thanks again|||Go with a smalldatetime in the sql table so that you are only using a (4). The go with a .ToString() and something like this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimeclasstostringtopic4.asp
Hope it helps.|||If you're doing straight SELECT from your ASP, you can use CONVERT(char(10), <your_date_field>, 101) on your date/time field.