Recovery of the SQL Server 2000 Database.

Situation:
SQL S2000 DB with some 6 mil. records, 100 fields for each record - was indexed at first on just few fields, then some more and when everything seemed to be OK and we had partially indexed good DB we stetted up indexing (single for each field) for the remaining 60 or so fields and left for the weekend.

Upon returning 2 days later we found SQL Server unusually busy and non-responding to Enterprise Manager at all.

Finally we have stopped SQL Server, with proper and uneventful shutdown - restarted the machine and at the moment all that is happening after lots of the activity at first (from current activity log) is that: "Recovery of database 'OurDB' (10) is 64% complete (approximately 240 more seconds) (Phase 2 of 3)."
No clue in previous messages in the current logs, no clue on previous logs.
All databases are inaccessible from Enterprise Manager.

What could be happening and now what?
:confused: :confused:When you say, "proper and uneventful shutdown " do you mean, sp_who showed that nothing was running? Could it be that indexes were still being build? And are you really creating (100) indexes for each field in every table?!?

-jfp|||jfp,
after some additional investigation I have discovered, that indeed, SQL Server was shut down with Windows "End Task" which unfortunately means, that - yes, it could very well still have been in the middle of indexing - and therefore ultimate price was paid for not having DB backed up BEFORE indexing.

BTW indexing of this sort on DB on just dual Xeon 500 box with 1 GB of Ram could go on for days.

Yes, we are indexing all the fields in order to query them and get the idea what the heck we need to do with all this wonderful data.
This is a boring trial run only to help prepare criteria for the real app with less junk and therefore less indexed junk.

Recordset to Array

Using Visual Basic can you assign a resultant recordset to an array
without looping through the number or returned records and fields.
Dim strArray() as String
Dim rs As ADODB.Recordset
rs.Open SQLStatement, Connection
strArray = rs
Many thanks for any assistance.
See if this helps you
http://www.sommarskog.se/arrays-in-sql.html
Madhivanan
|||There are no arrays in SQL Server and nor are they needed. What is your
question?
David Portas
SQL Server MVP
|||String and array are distinct data types in VB. One workaround is to use a
variant array and use GetRows method. See your VB/ADO manual for details and
examples on how to use this method.
Anith

Recordset to Array

Using Visual Basic can you assign a resultant recordset to an array
without looping through the number or returned records and fields.
Dim strArray() as String
Dim rs As ADODB.Recordset
rs.Open SQLStatement, Connection
strArray = rs
Many thanks for any assistance.See if this helps you
http://www.sommarskog.se/arrays-in-sql.html
Madhivanan|||There are no arrays in SQL Server and nor are they needed. What is your
question?
David Portas
SQL Server MVP
--|||String and array are distinct data types in VB. One workaround is to use a
variant array and use GetRows method. See your VB/ADO manual for details and
examples on how to use this method.
Anith

Recordset to Array

Using Visual Basic can you assign a resultant recordset to an array
without looping through the number or returned records and fields.
Dim strArray() as String
Dim rs As ADODB.Recordset
rs.Open SQLStatement, Connection
strArray = rs
Many thanks for any assistance.See if this helps you
http://www.sommarskog.se/arrays-in-sql.html
Madhivanan|||There are no arrays in SQL Server and nor are they needed. What is your
question?
David Portas
SQL Server MVP
--|||String and array are distinct data types in VB. One workaround is to use a
variant array and use GetRows method. See your VB/ADO manual for details and
examples on how to use this method.
Anith

recordset

hi
I have 3 tables:Article,Source and File.
Each article can have multiple filenames.
The fields of table Article are:ArticleID,SourceID,ArticleDate,ArticleCategory
The fields of table Source are:SourceID,SourceName
the fields of Table File:ID,ArticleID,Filename
Select*from Article inner join Source on Article.SourceID=source.SourceID order by ArticleDate
I obtain a number of recordsets from the above query.
Then for each recordset(Rs1),Let's say for the first recordset
Rs1.MoveFirst
I want to apply this query:
Rs2.Open"Select SourceName,ArticleDate,File.Filename from [RS1] inner Join File on Article.ArticleID=File.ArticleID
I want from the above query to have the Filenames corresponding to each Article because in my VB
form I have 2 command buttons:one gives me the article's definition(Date,Source)
and the other gives me the Filename of the current recordset(Article)
The above SQL syntax is it correct?why not just join all 3 tables in your 1st query
select a.ArticleID, a.SourceID, s.SourceName, a.ArticleDate, a.ArticleCategory, f.ID, f.Filename
from Article a inner join Source s
on a.SourceID = s.SourceID
inner join File f
on a.ArticleID = f.ArticleID
order by a.ArticleDate|||KH is correct with the best solution.

Just as an FYI - you cannot use a recordset in a SQL statement. There are, I believe, 3rd party utilities that allow you to join recordsets and query them a little like you are trying to do but I don't know how well these perform (quite badly I would imagine) and I have never come accross a situation where they are necessary.

HTH

Records drilled through multiple times due to Partitions

Hi,

I have created a cube for a telephony database. In this database there are two fields that point to the same table named tblPhonebook. These fields are FromPhonebookID and ToPhonebookID, which respectively contain the Person in the phonebook who placed the call and the Person in the phonebook who received the call. (the latter could both be an internal person and an external person).

I created one dimension. The Phonebook dimension which points to the table tblPhonebook.

When I select a person in this dimension I want to see all the calls this person placed and also all the calls this person received. So I created two "views" in SQL Server.

view1: SELECT ToID as UseID, * FROM tblData

view2: SELECT FromID as UseID, * FROM tblData

With the Cube I created two partitions. The first partition takes view1 as data table, the second partition takes view2 as data table. My Phonebook dimension is related to the UseID in these views.

So far so good. When I select a person in the Phonebook dimension, I get a neat result of his ingoing and outgoing phonecalls. However, when I select "All Phonebook", so not a specific user in the phonebook list, and instead select a date for instance, there occurs a problem. Because no Phonebook entry is specified there are always two entries per phonecall. One from view1 and one from view2. So my calls are counted double.

For counting the calls this is also not a problem, because I can perform a Distinct Count on the tblData.ID. I also measure the Average of the Conversation Time. This is also not a problem, because every value is counted double, not one excluded, so the average stays the same (isn't it? conversations lasting 1, 8 and 12 seconds average on 7 seconds. If those are counted twice then it would be 1,1,8,8,12,12 which also average on 7).

But when I select a drillthrough (with "All Phonebook" selected) the problem arrises. All entries are showed double in my drillthrough. First all the From Data ordered by date, then all the To Data ordered by date.

Ofcourse I don't want that. I want them only to be listed once. How do I filter this drillthrough that when no phonebook entry is selected, they still only appear once in my list?

(Sorry for the long introduction to a one line question ;))

Greets,

Edward

For one, Analysis Server in this case delivers exactly to the promise : in the result of the drillrhough it returns you all the data you selected to perform the drillthrough into.

Now about your situation. To solve your problem, you might decide to model your cube a bit different. You can create another dimension with only a single non-aggregatable attribute and 2 values "From" and "To". Then your user will choose to drill through into which part of the data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hmmm... so no filter possible? that was what I was afraid for ;)

Thanks for looking into my problem....

Greets,

Edward

Records dont get deleted from the database!

I am using this code to delete records from a table in the database that has two fields (both are primary keys) I am not getting any error, but nothing gets deleted. Thanks for the help.

Code:

protectedvoid btnRemove_Click(object sender,EventArgs e)

{

Remove();

Response.Redirect("RegisteredCompanies.aspx");

}

protectedvoid Remove()

{

//update database with filename

//Save Changes

connection.Open();

using (SqlTransaction trans = connection.BeginTransaction())

{

try

{

SqlHelper.ExecuteNonQuery(trans,CommandType.StoredProcedure,"spRemoveCompany",newSqlParameter("@.Userid",getGdUserID(Profile.UserName)),newSqlParameter("@.BusinessID",getBusinessID());

}

catch (Exception err)

{

// throw exception

trans.Rollback();

lblStatus.Text ="An error has occurred in the delete process. Please try again.";

}

}

Here is the stored procedure:

ALTERPROCEDURE[dbo].[spRemoveCompany]

@.Useriduniqueidentifier OUTPUT,

@.BusinessID uniqueidentifier OUTPUT

AS

DELETEFROMAssociations2WHEREUserid = @.Userid AND BusinessID = @.BusinessID

RETURN

You must commit the transaction in your code.

|||

Also, why are you using OUTPUT on your parameters?

|||

yes, I used output in the stored procedure. How do i commit the transaction? Thanks!

|||

Got it working now!! Thanks a lot!!

Recording Gaps

hi,

I have a view that contains details of when people are allocated to certain task, relevant fields as follows:

RES_ID int(4) (The Id no for that member of staff)
PK TASK_ID int(4) (the code for the task)
PK START_DATE datetime(8) (The date and time that they started on the task
END_DATE datetime(8) (when the task finishes)

I have been asked to create a report that shows when people are NOT working on a particular date (so they can reassign people if a someone calls in sick). Sounds easy until you think about it.

e.g. say Brian works the following hours on 02.01.2007
3.00am - 4.00am
4.30am - 6.00am
7.00am - 11.00am

I need to show the following in report:
Brian free 12.00am - 2.59am
Brian free 4.01am - 4.29am
Brian free 6.01am - 6.59am
Brian free 11.01am - 11.59pm

(I need this info for each member of staff)

I cannot work out how to do this. I am relatively new to SQL server but have a good understanding of relational databases.

Any help would be much appreciated as I am totally stuck.

Thanks,
Chris

The best way to accomplish this type of goal is to have a 'Calendar' table. Then you simply JOIN against the Calendar table to interact with your data in a temporal fashion.

Read about using a Calendar table here:

Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519

|||

Try:

Code Snippet

createtable #t (

RES_ID int,

TASK_ID int,

[START_DATE] datetime,

END_DATE datetime

)

go

insertinto #t values(1, 1,'2007-06-18T03:00:00','2007-06-18T04:00:00')

insertinto #t values(1, 1,'2007-06-18T04:30:00','2007-06-18T06:00:00')

insertinto #t values(1, 1,'2007-06-18T07:00:00','2007-06-18T11:00:00')

insertinto #t values(2, 1,'2007-06-18T03:20:00','2007-06-18T04:30:00')

insertinto #t values(2, 1,'2007-06-18T04:31:00','2007-06-18T06:15:00')

insertinto #t values(2, 1,'2007-06-18T07:00:00','2007-06-18T12:00:00')

go

declare @.d datetime

set @.d ='20070618'

;with cte

as

(

select

a.RES_ID,

dateadd(minute, 1, a.END_DATE)as [START_DATE_1],

dateadd(minute,-1,

(

selectmin(b.[START_DATE])

from #t as b

where b.RES_ID = a.RES_ID and b.[START_DATE] > a.END_DATE

)

)as [END_DATE_1]

from

#t as a

where

a.[START_DATE] >= @.d and a.[END_DATE] <dateadd(day, 1, @.d)

andnotexists(

select*

from #t as b

where

b.RES_ID = a.RES_ID

and b.[START_DATE] >= @.d and b.[END_DATE] <dateadd(day, 1, @.d)

and b.[START_DATE] =dateadd(minute, 1, a.END_DATE)

)

and a.END_DATE <(

selectmax(c.END_DATE)

from #t as c

where

c.RES_ID = a.RES_ID

and c.[START_DATE] >= @.d and c.[END_DATE] < dateadd(day, 1, @.d)

)

)

select

*

from

cte

union

select

RES_ID,

@.d,

dateadd(minute, -1, min([START_DATE]))

from

#t

where

[START_DATE] >= @.d and [END_DATE] < dateadd(day, 1, @.d)

group by

RES_ID

having

dateadd(minute, -1, min([START_DATE])) > @.d

union

select

RES_ID,

dateadd(minute, 1, max([START_DATE])),

dateadd(minute, 1439, @.d)

from

#t

where

[START_DATE] >= @.d and [END_DATE] < dateadd(day, 1, @.d)

group by

RES_ID

having

dateadd(minute, 1, max([START_DATE])) < dateadd(minute, 1439, @.d)

order by

RES_ID,

[START_DATE_1]

go

drop table #t

go

The idea behind this possible solution, is to find the gaps for each RES_ID for a specific date.

AMB

|||

Hi,

thanks for the responses. Hunchback, how would I turn this into a stored procedure with the date as a parameter? I have tried but get lots of syntax errors. I'm sorry but I have only created basic SPs in the past.

Thanks,

Chris

|||

Code Snippet

create procedure dbo.usp_p1

@.d datetime

as

set nocount on

;with cte

as

(

select

a.RES_ID,

dateadd(minute, 1, a.END_DATE)as [START_DATE_1],

dateadd(minute,-1,

(

selectmin(b.[START_DATE])

from #t as b

where b.RES_ID = a.RES_ID and b.[START_DATE] > a.END_DATE

)

)as [END_DATE_1]

from

#t as a

where

a.[START_DATE] >= @.d and a.[END_DATE] <dateadd(day, 1, @.d)

andnotexists(

select*

from #t as b

where

b.RES_ID = a.RES_ID

and b.[START_DATE] >= @.d and b.[END_DATE] <dateadd(day, 1, @.d)

and b.[START_DATE] =dateadd(minute, 1, a.END_DATE)

)

and a.END_DATE <(

selectmax(c.END_DATE)

from #t as c

where

c.RES_ID = a.RES_ID

and c.[START_DATE] >= @.d and c.[END_DATE] < dateadd(day, 1, @.d)

)

)

select

*

from

cte

union

select

RES_ID,

@.d,

dateadd(minute, -1, min([START_DATE]))

from

#t

where

[START_DATE] >= @.d and [END_DATE] < dateadd(day, 1, @.d)

group by

RES_ID

having

dateadd(minute, -1, min([START_DATE])) > @.d

union

select

RES_ID,

dateadd(minute, 1, max([START_DATE])),

dateadd(minute, 1439, @.d)

from

#t

where

[START_DATE] >= @.d and [END_DATE] < dateadd(day, 1, @.d)

group by

RES_ID

having

dateadd(minute, 1, max([START_DATE])) < dateadd(minute, 1439, @.d)

order by

RES_ID,

[START_DATE_1]

go

exec dbo.usp_p1 '19960704'

go

AMB

|||

hi,

I keep getting the following message when I try to save the SP

ADO error:Incorrect syntax near the word 'with'

the server is SQL SERVER 2000 and I am trying to save the SP in an Access .adp file. Do I need a slightly different syntax?

Thanks,

Chris

|||

Commom table expressions were introduced with SS 2005, so we have to change the statement.

create procedure dbo.usp_p1

@.d datetime

as

set nocount on

select

a.RES_ID,

dateadd(minute, 1, a.END_DATE)as [START_DATE_1],

dateadd(minute,-1,

(

selectmin(b.[START_DATE])

from #t as b

where b.RES_ID = a.RES_ID and b.[START_DATE] > a.END_DATE

)

)as [END_DATE_1]

from

#t as a

where

a.[START_DATE] >= @.d and a.[END_DATE] <dateadd(day, 1, @.d)

andnotexists(

select*

from #t as b

where

b.RES_ID = a.RES_ID

and b.[START_DATE] >= @.d and b.[END_DATE] <dateadd(day, 1, @.d)

and b.[START_DATE] =dateadd(minute, 1, a.END_DATE)

)

and a.END_DATE <(

selectmax(c.END_DATE)

from #t as c

where

c.RES_ID = a.RES_ID

and c.[START_DATE] >= @.d and c.[END_DATE] < dateadd(day, 1, @.d)

)

union

select

RES_ID,

@.d,

dateadd(minute, -1, min([START_DATE]))

from

#t

where

[START_DATE] >= @.d and [END_DATE] < dateadd(day, 1, @.d)

group by

RES_ID

having

dateadd(minute, -1, min([START_DATE])) > @.d

union

select

RES_ID,

dateadd(minute, 1, max([START_DATE])),

dateadd(minute, 1439, @.d)

from

#t

where

[START_DATE] >= @.d and [END_DATE] < dateadd(day, 1, @.d)

group by

RES_ID

having

dateadd(minute, 1, max([START_DATE])) < dateadd(minute, 1439, @.d)

order by

RES_ID,

[START_DATE_1]

go

exec dbo.usp_p1 '19960704'

go

|||here's, another approach, this may or may not perform better than hunchbacks post, although they both have the same logic, btw, thanks hunchback for the test data that i piggy back with

[edited]
create table #t (
RES_ID int,
TASK_ID int,
[START_DATE] datetime,
END_DATE datetime
)

insert into #t values(1, 1, '2007-06-18T03:00:00', '2007-06-18T04:00:00')
insert into #t values(1, 1, '2007-06-18T04:30:00', '2007-06-18T06:00:00')
insert into #t values(1, 1, '2007-06-18T07:00:00', '2007-06-18T11:00:00')
insert into #t values(2, 1, '2007-06-18T03:20:00', '2007-06-18T04:30:00')
insert into #t values(2, 1, '2007-06-18T04:31:00', '2007-06-18T06:15:00')
insert into #t values(2, 1, '2007-06-18T07:00:00', '2007-06-18T12:00:00')

declare @.d datetime

set @.d = '20070618'

select *
from #t

select identity(int,1,1) as row
, *
, dateadd(mi,-1,start_date) as free_end
, dateadd(mi,1,end_date) as free_start
into #r
from #t
order by
res_id
, start_date

select res_id
, task_id
, min(dateadd(mi,-1,start_date)) as min_start
, max(dateadd(mi,1,end_date)) as max_end
into #minmax
from #t
group by
res_id
, task_id

select distinct
t.res_id
, t.task_id
, dateadd(mi,1,dateadd(mi,-1,@.d)) as free_start
, r.min_start as free_end
from #t t inner join
#minmax r on t.res_id = r.res_id
and t.task_id = r.task_id
union
select r1.res_id
, r1.task_id
, r1.free_start as free_start
, r2.free_end as free_end
from #r r1 inner join
#r r2 on r1.row = r2.row - 1
and r1.res_id = r2.res_id
and r1.task_id = r2.task_id
union
select distinct
t.res_id
, t.task_id
, r.max_end as free_start
, dateadd(mi,-1,dateadd(day,1,@.d)) as free_end
from #t t inner join
#minmax r on t.res_id = r.res_id
and t.task_id = r.task_id

drop table #t
drop table #r
drop table #minmax|||

hi Hunchback,

thanks very much this is great, but having had a close look at the data that the report has to deal with, it is not quite there - not your fault, mine for not being specific enough.

These are the extra conditions it has to deal with:

a task can start on the day before but end on the day of the report,e.g.

Chris task1 01/01/2007 23:50 - 02/01/2007 01.00

for the above, the report would have to say the freetime started at 01.01am.

Also, tasks can overlap ,e.g.

Chris task1 02/01/2007 06.50 - 02/01/2007 07.30

Chris task2 02/01/2007 07.15 - 02/01/2007 07.40

Chris task4 02/01/2007 07.20 - 02/01/2007 07.35

Chris task3 02/01/2007 07.50 - 02/01/2007 23.59

in the above the freetime is

00.00 - 06.49,

07.41 - 07.49

And finally,

if someone does not have any tasks that day then their freetime should show 00.00 - 23.59.

I realise that these extra conditions make it that much harder, but they were the reason that I originally thought I had no chance of working it out. It is my fault that I overlooked mentioning them.

Thanks,

Chris

|||You will probably find that cursors run much quicker here when you have a realistic amount of data.

How does this code grab you? I'd use a proper resources table in the first statement though. This should handle overlaps and people who are missing.

--First populate everyone
select
r.res_id, @.thedate as start_date, dateadd(day,1,dateadd(minute,-1,@.thedate)) as end_date
into #tmp
from (select distinct res_id from #t) r

--Now run through the time worked
declare csrCsr cursor for
select res_id, start_date, end_date
from #t
where start_date >= @.thedate
and start_date < dateadd(day,1,@.thedate)
order by res_id, start_date
for read only

declare @.res int, @.start datetime, @.end datetime

open csrCsr
fetch csrCsr into @.res, @.start, @.end
while (@.@.fetch_status = 0)
begin
-- For each overlap, put records in either side, and then get rid of the original
insert into #tmp
select @.res, start_date, dateadd(minute,-1,@.start)
from #tmp
where res_id = @.res
and start_date < @.end
and @.start < end_date
and start_date < @.start

insert into #tmp
select @.res, dateadd(minute,1,@.end), end_date
from #tmp
where res_id = @.res
and start_date < @.end
and @.start < end_date
and @.end < end_date

delete
from #tmp
where res_id = @.res
and start_date < @.end
and @.start < end_date

fetch csrCsr into @.res, @.start, @.end
end
close csrCsr
deallocate csrCsr

--Now query the table, and drop the temporary resource
select * from #tmp
drop table #tmp

--Rob|||Oh, throw that into a stored procedure, like this:

create procedure dbo.fetch_times (@.thedate datetime)
as
...

--and of course, use the proper table name instead of #t.|||

hi,

I'd like to thank everyone for their help. After a day or too of navel-gazing, I admitted to myself that my SQL is not that great and resorted to.......CURSORS!

Sorry, but once I did that, it was pretty easy (see below). It is all well and good saying everything should be done with set-base solutions, but I don't have the time or skill for the more extreme examples of these.

anyway, here's my solution:

ALTER PROCEDURE GAP_Report
(
@.d DATETIME
)

AS
SET NOCOUNT ON

DECLARE @.resname VARCHAR(50), @.startdate DATETIME, @.enddate DATETIME, @.rname VARCHAR(50), @.edate DATETIME

SET @.rname = ''

DECLARE @.FreeTime TABLE
(
Res_name VARCHAR(50),
free_start DATETIME,
free_end DATETIME
)

DECLARE cCursor CURSOR FAST_FORWARD
FOR

SELECT
RES_NAME,TASK_START_DATE, TASK_FINISH_DATE
FROM
view_TasksPerUser
WHERE
((DATEDIFF(d,TASK_START_DATE,@.d) = 0) OR (DATEDIFF(d,TASK_FINISH_DATE,@.d) = 0))
ORDER BY
RES_NAME,TASK_START_DATE

OPEN cCursor
FETCH cCursor INTO @.resname, @.startdate, @.enddate

WHILE @.@.Fetch_Status = 0
BEGIN
IF @.rname <> @.resname
BEGIN
IF DATEDIFF(d,@.d,@.startdate) = 0
BEGIN
INSERT INTO @.FreeTime VALUES (@.resname,@.d,DATEADD(s,-1,@.startdate))
END

IF @.rname <> ''
BEGIN
IF DATEDIFF(d,@.d,@.enddate) = 0
BEGIN
INSERT INTO @.FreeTime VALUES (@.rname,DATEADD(s,1,@.edate),DATEADD(s,-1,DATEADD(d,1,@.d)))
END
END
SET @.edate = @.enddate
END
ELSE
BEGIN
IF DATEDIFF(s,@.edate,@.Startdate) > 0
BEGIN

INSERT INTO @.FreeTime VALUES (@.resname,DATEADD(s,1,@.edate), DATEADD(s,-1,@.startdate))
END
END

SET @.rname = @.resname

IF @.enddate > @.edate

SET @.edate = @.enddate

FETCH cCursor INTO @.resname, @.startdate, @.enddate

END

CLOSE cCursor
DEALLOCATE cCURSOR

INSERT INTO
@.FreeTime
SELECT
RES_NAME as res_name,
@.d as Free_start,
DATEADD(s,-1,DATEADD(d,1,@.d)) as Free_end
FROM
(SELECT
DISTINCT RES_NAME
FROM
MSP_RESOURCES
WHERE
RES_NAME <> ''
UNION ALL
SELECT
DISTINCT res_name
FROM
@.Freetime) U
GROUP BY res_name
HAVING COUNT(*) = 1

SELECT
*
FROM
@.Freetime
ORDER BY res_name, free_start, free_end

RETURN 0

thanks again,

Chris

Record Synchronization

I wanna syncronize data from two sources. (say fields like First Name, Last Name, Email, TimeStamp)

I wanna syncronize their changes during last 24 hours. I check the TimeStamp for the whole record. i dont care about fields.

Whichever is the latest Updates/Creates it on other side depending upon if it exists there. The side having greater TimeStamp rules and the record is overwritten on the loosing side. Great..

The catch is if the record does not exist at other side... i wanna create a new record.

I just wanna read the records that have changed during last 24 hours at either side in two separate DataSets. I will batch update them Later. since a record was not modified/ or it did not exist it was not selected...

How im gonna know (if i read only the records that have changed during last 24 hours)

if i need to (add/ or modify) it to other side?

Rephrase: if it already exists or not on other side?

You could try delete old/insert new instead of update. If you do not have the record on the other side you will simply ignore the result of delete.|||

If the two data sources are connectable and reachable to each other you could use a linked server / cross database query to get the diff results. If not I would consider using a query to extract all the data from one source and check this one-by-one with on the second data source.it depends on the amount of data if you do the direct comparison on the server or on the client.

Jens K. Suessmeyer.

http://www.sqlserver2008.de

Record size too big for table

Getting the subject error after creating fields in a SQL table. Total
fields is 810. I was told the total character count for all fields combined
in a SQL table couldn't exceed about 8000, but I cannot find any reference
to this.
See "Maximum Capacity Specifications" in BOL (Bytes per row).
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
AMB
"Absolutely" wrote:

> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combined
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>
|||8060 bytes is the total size of a row in a sql server 2000 table
http://sqlservercode.blogspot.com/
"Absolutely" wrote:

> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combined
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>

Record size too big for table

Getting the subject error after creating fields in a SQL table. Total
fields is 810. I was told the total character count for all fields combined
in a SQL table couldn't exceed about 8000, but I cannot find any reference
to this.See "Maximum Capacity Specifications" in BOL (Bytes per row).
http://msdn.microsoft.com/library/d...br />
8dbn.asp
AMB
"Absolutely" wrote:

> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combin
ed
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>|||8060 bytes is the total size of a row in a sql server 2000 table
http://sqlservercode.blogspot.com/
"Absolutely" wrote:

> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combin
ed
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>

Record size too big for table

Getting the subject error after creating fields in a SQL table. Total
fields is 810. I was told the total character count for all fields combined
in a SQL table couldn't exceed about 8000, but I cannot find any reference
to this.See "Maximum Capacity Specifications" in BOL (Bytes per row).
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
AMB
"Absolutely" wrote:
> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combined
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>|||8060 bytes is the total size of a row in a sql server 2000 table
http://sqlservercode.blogspot.com/
"Absolutely" wrote:
> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combined
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>

Record selection for latest dates (two fields)

Hi there.

I was wondering if anyone could help me here.

I need to select record details for the latest dates from two fields. The fields are:

Date1; and
Date2.

I tried to use the Maximum formula, but can not get it to work (i.e it wont select the latest date from each of the fields, and supress all other records):

{RCF03FormalAndUpcomingFormalReviews_RCF03.SttDat}= Maximum ({RCF03FormalAndUpcomingFormalReviews_RCF03.SttDat}) and
{RCF03FormalAndUpcomingFormalReviews_RCF03.QCF0612} = Maximum({RCF03FormalAndUpcomingFormalReviews_RCF03.QCF0612})

Any help would be great.

Cheers.

Mat.Not quite sure what you are asking, but if you want to select records for only the two most recent dates in the database, use a subroutine to select those dates, then "share" them with the main report.|||Try creating two formulas
@.maxrcf03 would be

Maximum ({RCF03FormalAndUpcomingFormalReviews_RCF03.SttDat})

@.MaxQCF would be

Maximum({RCF03FormalAndUpcomingFormalReviews_RCF03.QCF0612})

then use the formulas to return your maximum date for each of these fields.|||I tried that, but could not get it working.

Here is a sample of my dataset, where I need to return only information in row 1 (record 1):

Row 1. SPRING Cecil fred 31/05/2007 6/06/2007
Row 2. SPRING Cecil fred 1/06/2007 6/06/2007
Row 3. SPRING Cecil fred 18/06/2007 6/06/2007
Row 4. SPRING Cecil fred 15/06/2007 6/06/2007
Row 5. SPRING Cecil fred 19/06/2007 6/06/2007
Row 6. SPRING Cecil fred 25/06/2007 6/06/2007

The formulae overwrites the whole data range for all clients eg:

SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 1/06/2007
WINTER Wayne 31/05/2007 4/06/2007
WINTER Wayne 31/05/2007 4/06/2007
WINTER Wayne 31/05/2007 4/06/2007

I'm not sure how to make the formulae record-specific i.e. so it only applies to Mr Spring, anmd not to Mr Winter.

Cheers and thanks for your help.

Mat.|||Group on the field that is SPRING, WINTER etc. I'll call it {table.id}

Add a GROUP selection formula of
{table.date1} = Maximum({table.date1}, {table.id})
and {table.date2} = Maximum({table.date2}, {table.id})

Note that this means that if a particular group does not have a record where *both* of the dates are the latest respective dates within the group then it will not show at all.

An alternative would be to group by {table.id}, sort descending on both date1 and date2 and then print in the group header (or sort ascending and print in the group footer).|||Thanks Jagan.

I'll give that a go tomorrow at work.

cheers.

Mat.

Record Selection - CR9

Hi

I want my report to output data if any one of three date fields is greater or equal to today.

In my record selection i entered "datefield1" >= currentdate OR "datefield2" >= currentdate OR "datefield3" >= currentdate. However, report itself seems to only act after the first datefield is true and does not output data if the others are true. Crystal seems to ignore the subsequent record selections...

Where am I going wrong? Thanks...this is the code that is not working fully.. I have also tried xor

{tblKeyDates.datPODDue}>= currentdate or
{tblKeyDates.datHearing}>= currentdate or
{tblKeyDates.datPt8Iss}>= currentdate|||Is what you've shown the entire record selection formula?