Field accumulation

Hello,

I am building a crystal report for a timeclock program.

Currently I have the report displaying all employee punches and accumlating each punch at the end of the row as follows:

Time In Time Out Status Total
9/15/05 8:00 am 9/15/05 12:00 pm Working 4.00
9/15/05 12:00 am 9/15/05 1:00 pm Lunch 1.00
9/15/05 1:00 am 9/15/05 5:00 pm Working 4.00

At the bottom of the report I want to display the total hours for each status type for the pay period.

Lunch Total Break Total Reg Hours ect...

How can I accumlate the total for these fields? Sorry if this is an amature question, been racking my brain and searching diligently without any useful results.

Any help is greatly appreciated

Thanks,

LarryDo you want to show the total of times?

Fatal Exception in SQL Server

Hello,
We got a SQL Server (no SP) running on Win 2k SP4. Recently been getting
this error message in event viewer and at the same time the program that
access SQL Server crashes.
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 64 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Does anyone have any idea what it is please. Thanks
Andrew
> We got a SQL Server (no SP) running on Win 2k SP4.
If this is SQL2000, apply SQL2000 sevice pack 4 + AWE memory hotfix first.
If this SQL2005, open a support case with Microsoft. But I'd strongly
suggest that you run SQL2005 on Windows 2003 SP1.
Linchi
"Huy (Andrew) V Nguyen" <andrew.nv@.gmail.com> wrote in message
news:%234ZSg%23cLGHA.3756@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We got a SQL Server (no SP) running on Win 2k SP4. Recently been getting
> this error message in event viewer and at the same time the program that
> access SQL Server crashes.
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 64 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Does anyone have any idea what it is please. Thanks
> Andrew
|||Thank you for your reply. I already applied SP4 still the same message.
Where can I obtain AWE hotfix please.
Andrew
Linchi Shea wrote:
> If this is SQL2000, apply SQL2000 sevice pack 4 + AWE memory hotfix first.
> If this SQL2005, open a support case with Microsoft. But I'd strongly
> suggest that you run SQL2005 on Windows 2003 SP1.
> Linchi
> "Huy (Andrew) V Nguyen" <andrew.nv@.gmail.com> wrote in message
> news:%234ZSg%23cLGHA.3756@.TK2MSFTNGP10.phx.gbl...
>
|||You can get the AWE fix here :
http://www.microsoft.com/downloads/d...displaylang=en
But you will need to log a case with Microsoft CSS , because the error you
posted is not sufficient enough , we need at least the stack output from the
AV , and if there was any mini dump created in the log folder during this
time...
So as linchi said please log a case with Microsoft CSS for speedy resolution
HTH
"Huy (Andrew) V Nguyen" <andrew.nv@.gmail.com> wrote in message
news:uVHDk9cMGHA.3264@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Thank you for your reply. I already applied SP4 still the same message.
> Where can I obtain AWE hotfix please.
> Andrew
> Linchi Shea wrote:

Fatal Exception in SQL Server

Hello,
We got a SQL Server (no SP) running on Win 2k SP4. Recently been getting
this error message in event viewer and at the same time the program that
access SQL Server crashes.
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 64 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Does anyone have any idea what it is please. Thanks
Andrew> We got a SQL Server (no SP) running on Win 2k SP4.
If this is SQL2000, apply SQL2000 sevice pack 4 + AWE memory hotfix first.
If this SQL2005, open a support case with Microsoft. But I'd strongly
suggest that you run SQL2005 on Windows 2003 SP1.
Linchi
"Huy (Andrew) V Nguyen" <andrew.nv@.gmail.com> wrote in message
news:%234ZSg%23cLGHA.3756@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We got a SQL Server (no SP) running on Win 2k SP4. Recently been getting
> this error message in event viewer and at the same time the program that
> access SQL Server crashes.
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 64 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Does anyone have any idea what it is please. Thanks
> Andrew|||Thank you for your reply. I already applied SP4 still the same message.
Where can I obtain AWE hotfix please.
Andrew
Linchi Shea wrote:
> If this is SQL2000, apply SQL2000 sevice pack 4 + AWE memory hotfix first.
> If this SQL2005, open a support case with Microsoft. But I'd strongly
> suggest that you run SQL2005 on Windows 2003 SP1.
> Linchi
> "Huy (Andrew) V Nguyen" <andrew.nv@.gmail.com> wrote in message
> news:%234ZSg%23cLGHA.3756@.TK2MSFTNGP10.phx.gbl...
>|||You can get the AWE fix here :
http://www.microsoft.com/downloads/...&displaylang=en
But you will need to log a case with Microsoft CSS , because the error you
posted is not sufficient enough , we need at least the stack output from the
AV , and if there was any mini dump created in the log folder during this
time...
So as linchi said please log a case with Microsoft CSS for speedy resolution
HTH
"Huy (Andrew) V Nguyen" <andrew.nv@.gmail.com> wrote in message
news:uVHDk9cMGHA.3264@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Thank you for your reply. I already applied SP4 still the same message.
> Where can I obtain AWE hotfix please.
> Andrew
> Linchi Shea wrote:

Fatal Exception in SQL Server

Hello,
We got a SQL Server (no SP) running on Win 2k SP4. Recently been getting
this error message in event viewer and at the same time the program that
access SQL Server crashes.
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 64 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Does anyone have any idea what it is please. Thanks
Andrew> We got a SQL Server (no SP) running on Win 2k SP4.
If this is SQL2000, apply SQL2000 sevice pack 4 + AWE memory hotfix first.
If this SQL2005, open a support case with Microsoft. But I'd strongly
suggest that you run SQL2005 on Windows 2003 SP1.
Linchi
"Huy (Andrew) V Nguyen" <andrew.nv@.gmail.com> wrote in message
news:%234ZSg%23cLGHA.3756@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We got a SQL Server (no SP) running on Win 2k SP4. Recently been getting
> this error message in event viewer and at the same time the program that
> access SQL Server crashes.
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 64 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Does anyone have any idea what it is please. Thanks
> Andrew|||Thank you for your reply. I already applied SP4 still the same message.
Where can I obtain AWE hotfix please.
Andrew
Linchi Shea wrote:
>> We got a SQL Server (no SP) running on Win 2k SP4.
> If this is SQL2000, apply SQL2000 sevice pack 4 + AWE memory hotfix first.
> If this SQL2005, open a support case with Microsoft. But I'd strongly
> suggest that you run SQL2005 on Windows 2003 SP1.
> Linchi
> "Huy (Andrew) V Nguyen" <andrew.nv@.gmail.com> wrote in message
> news:%234ZSg%23cLGHA.3756@.TK2MSFTNGP10.phx.gbl...
>> Hello,
>> We got a SQL Server (no SP) running on Win 2k SP4. Recently been getting
>> this error message in event viewer and at the same time the program that
>> access SQL Server crashes.
>> Error: 0, Severity: 19, State: 0
>> SqlDumpExceptionHandler: Process 64 generated fatal exception c0000005
>> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
>> Does anyone have any idea what it is please. Thanks
>> Andrew
>|||You can get the AWE fix here :
http://www.microsoft.com/downloads/details.aspx?FamilyId=7C407047-3F1F-48B8-9E4C-DC32875E1961&displaylang=en
But you will need to log a case with Microsoft CSS , because the error you
posted is not sufficient enough , we need at least the stack output from the
AV , and if there was any mini dump created in the log folder during this
time...
So as linchi said please log a case with Microsoft CSS for speedy resolution
HTH
"Huy (Andrew) V Nguyen" <andrew.nv@.gmail.com> wrote in message
news:uVHDk9cMGHA.3264@.TK2MSFTNGP11.phx.gbl...
> Thank you for your reply. I already applied SP4 still the same message.
> Where can I obtain AWE hotfix please.
> Andrew
> Linchi Shea wrote:
>> We got a SQL Server (no SP) running on Win 2k SP4.
>> If this is SQL2000, apply SQL2000 sevice pack 4 + AWE memory hotfix
>> first. If this SQL2005, open a support case with Microsoft. But I'd
>> strongly suggest that you run SQL2005 on Windows 2003 SP1.
>> Linchi
>> "Huy (Andrew) V Nguyen" <andrew.nv@.gmail.com> wrote in message
>> news:%234ZSg%23cLGHA.3756@.TK2MSFTNGP10.phx.gbl...
>> Hello,
>> We got a SQL Server (no SP) running on Win 2k SP4. Recently been getting
>> this error message in event viewer and at the same time the program that
>> access SQL Server crashes.
>> Error: 0, Severity: 19, State: 0
>> SqlDumpExceptionHandler: Process 64 generated fatal exception c0000005
>> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
>> Does anyone have any idea what it is please. Thanks
>> Andrew

fastest way to open a query?

Hi.I am writing a program in c++ with ado which has to write 2-3 times
per second in a same field.That`s because i need speed.i have to use
sql unfortunately because our webmaster will take data from here.if i
had chance to choose i would choose berkeley db.Anyway here is a piece
of my code.how should i change the open parameters or should i try
something else?
char query[100];
sprintf(query, "SELECT * FROM MarketData WHERE EXCHANGE_ID = '%s'",
keyValue); //example keyValue = USDGBP
bstr_t bstrQuery(query);
try {
hr = m_pRecSet->Open(_variant_t(bstrQuery),
vNull,
adOpenForwardOnly,
adLockOptimistic,
adCmdText);
if (!m_pRecSet->GetadoEOF()) {
m_pRecSet->PutCollect("MARKET_DATA_BID", bidValue);
m_pRecSet->PutCollect("MARKET_DATA_ASK", askValue);
m_pRecSet->Update(vNull, vNull);
m_pRecSet->Close();
}
}
catch( _com_error &e ) {
TRACE( "Error:%08lx.\n", e.Error());
TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
}Try Select EXCHANGE_ID, MARKET_DATA_BID, MARKET_DATA_ASK FROM ...
That will reduce the amount of data being prepared and should speed it up
marginally.
<ozgecolak@.gmail.com> wrote in message
news:1129299784.233691.240390@.f14g2000cwb.googlegroups.com...
> Hi.I am writing a program in c++ with ado which has to write 2-3 times
> per second in a same field.That`s because i need speed.i have to use
> sql unfortunately because our webmaster will take data from here.if i
> had chance to choose i would choose berkeley db.Anyway here is a piece
> of my code.how should i change the open parameters or should i try
> something else?
> char query[100];
> sprintf(query, "SELECT * FROM MarketData WHERE EXCHANGE_ID = '%s'",
> keyValue); //example keyValue = USDGBP
> bstr_t bstrQuery(query);
> try {
> hr = m_pRecSet->Open(_variant_t(bstrQuery),
> vNull,
> adOpenForwardOnly,
> adLockOptimistic,
> adCmdText);
> if (!m_pRecSet->GetadoEOF()) {
> m_pRecSet->PutCollect("MARKET_DATA_BID", bidValue);
> m_pRecSet->PutCollect("MARKET_DATA_ASK", askValue);
> m_pRecSet->Update(vNull, vNull);
> m_pRecSet->Close();
> }
> }
> catch( _com_error &e ) {
> TRACE( "Error:%08lx.\n", e.Error());
> TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
> TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
> TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
> }
>|||Why bring a recordset down to the client at all? You are not reading the
values. You are making a minuimum of one trip to the server (to get a
recordset) and a maximum of two trips (to update the value if it exists).
Get rid of the recordset and limit this to a maximum of one trip to the
server. The two inefficiencies I see here are using inline sql and the
biggie of using a recordset to perform an update.
I would recommend:
1) Use a stored proc. Let SQl Server compile the execution plan saving it
from having to do it on the fly.
CREATE PROC sp_UpdateTicker @.Bid smallmoney, @.ask smallmoney, @.ID char(10)
AS
SET NOCOUNT ON
UPDATE MarketDate
SET MARKET_DATA_BID = @.Bid, MARKET_DATA_ASK = @.Ask
WHERE EXCHANGE_ID = @.ID
GO
2) Just call the ->Execute method on a connection or command object to run
the proc. Pass along the adExecuteNoRecords enum value to make sure that it
is as efficient as possible.
// Create and Configure the Command Object
pCom.CreateInstance(__uuidof(Command));
pCom->ActiveConnection = pConn;
pCom->CommandType = adCmdStoredProc ;
pCom->CommandText = _bstr_t("dbo.sp_UpdateTicker ");
// Append Parameters
pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.Bid"), adCurrency,
adParamInput, 8, _variant_t(bidValue)));
pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.Ask"), adCurrency,
adParamInput, 8, _variant_t(askValue)));
pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.ID"), adChar,
adParamInput, 10, s));
// Execute the Command
pCom->Execute(NULL, NULL, adCmdStoredProc, adExecuteNoRecords);
Even if you do not have permission on the database to create a proc, at
least get away from the recordset and pass your inline sql in the
pCom->Execute(NULL, NULL, adCmdText, adExecuteNoRecords);
HTH,
John Scragg
"ozgecolak@.gmail.com" wrote:

> Hi.I am writing a program in c++ with ado which has to write 2-3 times
> per second in a same field.That`s because i need speed.i have to use
> sql unfortunately because our webmaster will take data from here.if i
> had chance to choose i would choose berkeley db.Anyway here is a piece
> of my code.how should i change the open parameters or should i try
> something else?
> char query[100];
> sprintf(query, "SELECT * FROM MarketData WHERE EXCHANGE_ID = '%s'",
> keyValue); //example keyValue = USDGBP
> bstr_t bstrQuery(query);
> try {
> hr = m_pRecSet->Open(_variant_t(bstrQuery),
> vNull,
> adOpenForwardOnly,
> adLockOptimistic,
> adCmdText);
> if (!m_pRecSet->GetadoEOF()) {
> m_pRecSet->PutCollect("MARKET_DATA_BID", bidValue);
> m_pRecSet->PutCollect("MARKET_DATA_ASK", askValue);
> m_pRecSet->Update(vNull, vNull);
> m_pRecSet->Close();
> }
> }
> catch( _com_error &e ) {
> TRACE( "Error:%08lx.\n", e.Error());
> TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
> TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
> TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
> }
>|||thanks.now it`s faster but not as fast as i need.
John Scragg yazdi:
> Why bring a recordset down to the client at all? You are not reading the
> values. You are making a minuimum of one trip to the server (to get a
> recordset) and a maximum of two trips (to update the value if it exists).
> Get rid of the recordset and limit this to a maximum of one trip to the
> server. The two inefficiencies I see here are using inline sql and the
> biggie of using a recordset to perform an update.
> I would recommend:
> 1) Use a stored proc. Let SQl Server compile the execution plan saving it
> from having to do it on the fly.
> CREATE PROC sp_UpdateTicker @.Bid smallmoney, @.ask smallmoney, @.ID char(10)
> AS
> SET NOCOUNT ON
> UPDATE MarketDate
> SET MARKET_DATA_BID = @.Bid, MARKET_DATA_ASK = @.Ask
> WHERE EXCHANGE_ID = @.ID
> GO
> 2) Just call the ->Execute method on a connection or command object to run
> the proc. Pass along the adExecuteNoRecords enum value to make sure that
it
> is as efficient as possible.
> // Create and Configure the Command Object
> pCom.CreateInstance(__uuidof(Command));
> pCom->ActiveConnection = pConn;
> pCom->CommandType = adCmdStoredProc ;
> pCom->CommandText = _bstr_t("dbo.sp_UpdateTicker ");
> // Append Parameters
> pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.Bid"), adCurrency
,
> adParamInput, 8, _variant_t(bidValue)));
> pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.Ask"), adCurrency
,
> adParamInput, 8, _variant_t(askValue)));
> pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.ID"), adChar,
> adParamInput, 10, s));
> // Execute the Command
> pCom->Execute(NULL, NULL, adCmdStoredProc, adExecuteNoRecords);
> Even if you do not have permission on the database to create a proc, at
> least get away from the recordset and pass your inline sql in the
> pCom->Execute(NULL, NULL, adCmdText, adExecuteNoRecords);
> HTH,
> John Scragg
>
> "ozgecolak@.gmail.com" wrote:
>|||I think you need to test the individual parts. This code takes only
miliseconds to run for me.
Here is a tip. If you do this inside a loop. Declare the command, set its
properties and create all parameters OUTSIDE of the loop. Then just
repeatedly assign the bid, ask and ID values to the parameters inside the
loop and call the execute inside the loop. This will save you any repeated
object creation overhead.
HTH
John
"ozgecolak@.gmail.com" wrote:

> thanks.now it`s faster but not as fast as i need.
> John Scragg yazdi:
>

Fastest way

I've got a complex view (lots of inner and outer joins from several tables) in my SQL Server 2000 Database.

In my VB program, I've connected a grid to this view via an Ado Control
but the performances are not so great (even bad).

How can I boost the execution of this view ?

ThxI would look at those joins and see if there is any non-indexed scan first. You can also run the wizard to see if there is any room for improvement. Good luck!|||Hi again Joe from Texas (is it hot there at this moment ?)

I've put indexes on all the needed columns
but I've seen somewhere that we can create indexes on views

would that help ?|||If you look up "Creating an Indexed View" in Books Online, you'll see that there are a lot of restrictions on what types of views can be indexed. In particular, you can't index your view if it contains any of these elements:
Subqueries
Outer joins
Self joins
DISTINCT keyword
SUM functions that reference nullable expressions
.
.
. etc...

If your view is as complex as you say it is, there is a good chance that it cannot be indexed. Also, when you index a view SQL Server creates a permanent virtual table with the results of that view, and which is then updated any time any of the values in the underlying tables are updates. This can slow down other processing.

I think you would be better off rewriting your statement as a stored procedure rather than as a view. Stored procedures are more efficient than views.

Also, post your code and we may be able to find ways to make it more efficient.

blindman