Analysis Services & Default Value

Is it possible to define a default value for each a parameter also in a dataset?

I have a dataset with a parameter from the dimension Time. I would like to change the default value from the parameter. I tried to create a dataset to get the current year unique name, but if I set it in the parameter dialog it does not recognize the value.

I have a dataset in a report with the parameter Year from the dimension Time.

In the dataset I have to select a default value for the parameter. There is no other option.

In the reports parameters list I can specify a default value. This is not recognized, if it does not mach previous one.

Is it possible to set the default value of such a parameter to the current year value?

Analysis Server - Incompatible repository

Hi,
We have been using MS Analysis Server with Axapta (a mid-range ERP from MS)
for quite some time. So long we were able to create/run cubes without any
problem.
But today all of a sudden we are getting the following error message -
...................................... .........
Error
Transferring cube(s)\<CUBE NAME>\Connecting to the OLAP ServerMethod
'Connect' in COM object of class '{B492C386-0195-11D2-89BA-00C04FB9898D}'
returned error code 0x80040034 (<unknown>) which means: Incompatible
repository.
...................................... .........
The thing is nothing has changed from Axapta end. So my query is this -
Is there anything on AS that could cause such error message? Can someone
let me know please.
TIA,
Harish Mohanbabu
MBS Axapta - MVP
http://www.harishm.com/
Hello,
To isolate the issue, you may try to check if the issue occurs on different
clients or you could use MDX sample application on server to test.
If it is client side issue, please refer to the following article to
troubleshoot the issue:
288890 PRB: Incompatible Repository Error Message Occurs After Installation
of
http://support.microsoft.com/?id=288890
If the issue persists, you may try to unregister the DSO dll's in the
following order:
msmddo80.dll
msmddo.dll
msmdlock.dll
msmdnet.dll
msmdint.dll
Then register the above Dll's in the reverse order, starting with
msmdint.dll.
If we confirm it is a server side issue, it is more like a database
corruption, you may try to archive the current database, and then restore a
known good one on AS server to test.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default...national.aspx.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Analysis Server - Incompatible repository
| thread-index: AcXxCdpBYHIh/957S2CAGRK2YCeB6A==
| X-WBNR-Posting-Host: 80.245.107.27
| From: "=?Utf-8?B?SGFyaXNoIE1vaGFuYmFidQ==?=" <Axapta@.online.nospam>
| Subject: Analysis Server - Incompatible repository
| Date: Thu, 24 Nov 2005 07:15:06 -0800
| Lines: 27
| Message-ID: <668B07C4-AE5B-47F6-BD03-682AC746E133@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.datawarehouse
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.datawarehouse:21772
| X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
|
| Hi,
|
| We have been using MS Analysis Server with Axapta (a mid-range ERP from
MS)
| for quite some time. So long we were able to create/run cubes without
any
| problem.
|
| But today all of a sudden we are getting the following error message -
|
| ...................................... ........
| Error
| Transferring cube(s)\<CUBE NAME>\Connecting to the OLAP ServerMethod
| 'Connect' in COM object of class '{B492C386-0195-11D2-89BA-00C04FB9898D}'
| returned error code 0x80040034 (<unknown>) which means: Incompatible
| repository.
| ...................................... ........
|
| The thing is nothing has changed from Axapta end. So my query is this -
|
| Is there anything on AS that could cause such error message? Can someone
| let me know please.
|
| TIA,
|
| Harish Mohanbabu
| --
| MBS Axapta - MVP
| http://www.harishm.com/
|

Analysis Server - Incompatible repository

Hi,
We have been using MS Analysis Server with Axapta (a mid-range ERP from MS)
for quite some time. So long we were able to create/run cubes without any
problem.
But today all of a sudden we are getting the following error message -
.............................................
Error
Transferring cube(s)\<CUBE NAME>\Connecting to the OLAP Server Method
'Connect' in COM object of class '{B492C386-0195-11D2-89BA-00C04FB9898D
}'
returned error code 0x80040034 (<unknown> ) which means: Incompatible
repository.
.............................................
The thing is nothing has changed from Axapta end. So my query is this -
Is there anything on AS that could cause such error message? Can someone
let me know please.
TIA,
Harish Mohanbabu
--
MBS Axapta - MVP
http://www.harishm.com/Hello,
To isolate the issue, you may try to check if the issue occurs on different
clients or you could use MDX sample application on server to test.
If it is client side issue, please refer to the following article to
troubleshoot the issue:
288890 PRB: Incompatible Repository Error Message Occurs After Installation
of
http://support.microsoft.com/?id=288890
If the issue persists, you may try to unregister the DSO dll's in the
following order:
msmddo80.dll
msmddo.dll
msmdlock.dll
msmdnet.dll
msmdint.dll
Then register the above Dll's in the reverse order, starting with
msmdint.dll.
If we confirm it is a server side issue, it is more like a database
corruption, you may try to archive the current database, and then restore a
known good one on AS server to test.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Analysis Server - Incompatible repository
| thread-index: AcXxCdpBYHIh/957S2CAGRK2YCeB6A==
| X-WBNR-Posting-Host: 80.245.107.27
| From: "examnotes" <Axapta@.online.nospam>
| Subject: Analysis Server - Incompatible repository
| Date: Thu, 24 Nov 2005 07:15:06 -0800
| Lines: 27
| Message-ID: <668B07C4-AE5B-47F6-BD03-682AC746E133@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.datawarehouse
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.datawarehouse:21772
| X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
|
| Hi,
|
| We have been using MS Analysis Server with Axapta (a mid-range ERP from
MS)
| for quite some time. So long we were able to create/run cubes without
any
| problem.
|
| But today all of a sudden we are getting the following error message -
|
| .............................................
| Error
| Transferring cube(s)\<CUBE NAME>\Connecting to the OLAP Server Method
| 'Connect' in COM object of class '{B492C386-0195-11D2-89BA-00C04FB989
8D}'
| returned error code 0x80040034 (<unknown> ) which means: Incompatible
| repository.
| .............................................
|
| The thing is nothing has changed from Axapta end. So my query is this -
|
| Is there anything on AS that could cause such error message? Can someone
| let me know please.
|
| TIA,
|
| Harish Mohanbabu
| --
| MBS Axapta - MVP
| http://www.harishm.com/
|

Analysis Server

We are running Analysis Server on Windows 2000 and it's running under local system account,but most of the time we are getting the following error 'Error = -2147221455 (80040031) Error string: Unable to connect to the registry on the server ,or you are not a member of the OLAP Administrators group on this server.'This happens when we try to refresh the cube and the only thing that makes it work again is to go to the Services under the control panel and stop and restart the OLAP services.I wonder why??By refresh, do you mean re-process the cube?

amount of transactions in a period of time on sql server 2000

Is there a native tool (profile,trace,performance) feature I can use to determine the amount of transactions that occur throughout the day? Or is there a system table that keeps track of this(would be preferable .. less strain on the system)?

I assume figuring out the transaction in a certain period will enable me to calculate the busiest periods...I need to know the busiest period of the day...how do I do this without putting an additional strain on the server (can I use a different machine other than the server to save a trace) ...I need to determine strain on (processor,memory, and disk).

I also need to get a count on the largest number of users (running transactions) on the server simultaneously.

Any help/advice would be deeply appreciated

You need to run profiler for this purpose... there is no system table as such which stores all the transactions... What u can do is.. run profiler and store it as table and query the table as u want... Be sure what all are the data u need to capture... Profiler can cause performance degradation.... Capture only the required data by selecting proper column filter and event

Madhu

|||

Running PROFILER on full day is not a good thing if you still have performance problems, lately.

http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm - automating such trace.

Refer:
select count(*) as TotalConnections from master..sysprocesses
go

select substring(db_name(dbid),1,30) as DB_name ,count(*) as Connection
from master..sysprocesses
group by substring(db_name(dbid),1,30)

AMD vs Intel for SQL Servers

I know at one time AMD used to be power players for SQL Servers, but of
late, I am hearing that Intel chips are also proving to be efficient.
Can you share what you have seen in your benchmarks on some of the new
models say in the HP space using either Intel or AMD ?
ThanksOn May 3, 6:12 pm, "Hassan" <has...@.hotmail.com> wrote:
> I know at one time AMD used to be power players for SQL Servers, but of
> late, I am hearing that Intel chips are also proving to be efficient.
> Can you share what you have seen in your benchmarks on some of the new
> models say in the HP space using either Intel or AMD ?
> Thanks
We upgraded to AMD 64 bit dual opteron box with 4 gb memory and we
were completely happy with the results. That was almost 2 years ago
and back then AMD sort of swept the market with price and
performance. AMD memory access architecture for processors is far
better than performing then Intel . So I prefer AMD x64 over Intel
EMT and has been implementing most of the sql server and desktops with
AMD x64 processors|||On 04.05.2007 03:10, Bulent wrote:
> On May 3, 6:12 pm, "Hassan" <has...@.hotmail.com> wrote:
> We upgraded to AMD 64 bit dual opteron box with 4 gb memory and we
> were completely happy with the results. That was almost 2 years ago
> and back then AMD sort of swept the market with price and
> performance. AMD memory access architecture for processors is far
> better than performing then Intel . So I prefer AMD x64 over Intel
> EMT and has been implementing most of the sql server and desktops with
> AMD x64 processors
Curious: does it really make a difference? I would have guessed that
the capabilities of the IO subsystem are far more important for a DB
server than the CPU.
Kind regards
robert|||> Curious: does it really make a difference? I would have guessed that
> the capabilities of the IO subsystem are far more important for a DB
> server than the CPU.
I would not discount the importance of CPUs to that degree. It's true if
your workload is stressing something else, having more processor power
wouldn't help much. But in general, it pays to find out which processor work
s
best under what circusmstances. There are still many processing intensive
tasks. Plus, vendors are constantly trying to take advantage of the ever
increasing processor power, to trade processing for resources that may be
under stress.
Linchi
"Robert Klemme" wrote:

> On 04.05.2007 03:10, Bulent wrote:
> Curious: does it really make a difference? I would have guessed that
> the capabilities of the IO subsystem are far more important for a DB
> server than the CPU.
> Kind regards
> robert
>|||On May 4, 9:49 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> I would not discount the importance of CPUs to that degree. It's true if
> your workload is stressing something else, having more processor power
> wouldn't help much. But in general, it pays to find out which processor wo
rks
> best under what circusmstances. There are still many processing intensive
> tasks. Plus, vendors are constantly trying to take advantage of the ever
> increasing processor power, to trade processing for resources that may be
> under stress.
> Linchi
>
> "Robert Klemme" wrote:
>
>
>
>
>
> - Show quoted text -
Definitely there is the IO subsystem that's very important for
database servers. The hypertransport technology and direct memory
access that amd based systems use is also far better than Intel.
Bottom line you will get better IO performance. Before you make your
decision check out the those and compare against intel and it's Core2
Duo systems and io, memory access methods.

AMD vs Intel for SQL Servers

I know at one time AMD used to be power players for SQL Servers, but of
late, I am hearing that Intel chips are also proving to be efficient.
Can you share what you have seen in your benchmarks on some of the new
models say in the HP space using either Intel or AMD ?
Thanks
On May 3, 6:12 pm, "Hassan" <has...@.hotmail.com> wrote:
> I know at one time AMD used to be power players for SQL Servers, but of
> late, I am hearing that Intel chips are also proving to be efficient.
> Can you share what you have seen in your benchmarks on some of the new
> models say in the HP space using either Intel or AMD ?
> Thanks
We upgraded to AMD 64 bit dual opteron box with 4 gb memory and we
were completely happy with the results. That was almost 2 years ago
and back then AMD sort of swept the market with price and
performance. AMD memory access architecture for processors is far
better than performing then Intel . So I prefer AMD x64 over Intel
EMT and has been implementing most of the sql server and desktops with
AMD x64 processors
|||On 04.05.2007 03:10, Bulent wrote:
> On May 3, 6:12 pm, "Hassan" <has...@.hotmail.com> wrote:
> We upgraded to AMD 64 bit dual opteron box with 4 gb memory and we
> were completely happy with the results. That was almost 2 years ago
> and back then AMD sort of swept the market with price and
> performance. AMD memory access architecture for processors is far
> better than performing then Intel . So I prefer AMD x64 over Intel
> EMT and has been implementing most of the sql server and desktops with
> AMD x64 processors
Curious: does it really make a difference? I would have guessed that
the capabilities of the IO subsystem are far more important for a DB
server than the CPU.
Kind regards
robert
|||> Curious: does it really make a difference? I would have guessed that
> the capabilities of the IO subsystem are far more important for a DB
> server than the CPU.
I would not discount the importance of CPUs to that degree. It's true if
your workload is stressing something else, having more processor power
wouldn't help much. But in general, it pays to find out which processor works
best under what circusmstances. There are still many processing intensive
tasks. Plus, vendors are constantly trying to take advantage of the ever
increasing processor power, to trade processing for resources that may be
under stress.
Linchi
"Robert Klemme" wrote:

> On 04.05.2007 03:10, Bulent wrote:
> Curious: does it really make a difference? I would have guessed that
> the capabilities of the IO subsystem are far more important for a DB
> server than the CPU.
> Kind regards
> robert
>
|||On May 4, 9:49 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> I would not discount the importance of CPUs to that degree. It's true if
> your workload is stressing something else, having more processor power
> wouldn't help much. But in general, it pays to find out which processor works
> best under what circusmstances. There are still many processing intensive
> tasks. Plus, vendors are constantly trying to take advantage of the ever
> increasing processor power, to trade processing for resources that may be
> under stress.
> Linchi
>
> "Robert Klemme" wrote:
>
>
>
> - Show quoted text -
Definitely there is the IO subsystem that's very important for
database servers. The hypertransport technology and direct memory
access that amd based systems use is also far better than Intel.
Bottom line you will get better IO performance. Before you make your
decision check out the those and compare against intel and it's Core2
Duo systems and io, memory access methods.

AMD vs Intel for SQL Servers

I know at one time AMD used to be power players for SQL Servers, but of
late, I am hearing that Intel chips are also proving to be efficient.
Can you share what you have seen in your benchmarks on some of the new
models say in the HP space using either Intel or AMD ?
ThanksOn May 3, 6:12 pm, "Hassan" <has...@.hotmail.com> wrote:
> I know at one time AMD used to be power players for SQL Servers, but of
> late, I am hearing that Intel chips are also proving to be efficient.
> Can you share what you have seen in your benchmarks on some of the new
> models say in the HP space using either Intel or AMD ?
> Thanks
We upgraded to AMD 64 bit dual opteron box with 4 gb memory and we
were completely happy with the results. That was almost 2 years ago
and back then AMD sort of swept the market with price and
performance. AMD memory access architecture for processors is far
better than performing then Intel . So I prefer AMD x64 over Intel
EMT and has been implementing most of the sql server and desktops with
AMD x64 processors|||On 04.05.2007 03:10, Bulent wrote:
> On May 3, 6:12 pm, "Hassan" <has...@.hotmail.com> wrote:
>> I know at one time AMD used to be power players for SQL Servers, but of
>> late, I am hearing that Intel chips are also proving to be efficient.
>> Can you share what you have seen in your benchmarks on some of the new
>> models say in the HP space using either Intel or AMD ?
> We upgraded to AMD 64 bit dual opteron box with 4 gb memory and we
> were completely happy with the results. That was almost 2 years ago
> and back then AMD sort of swept the market with price and
> performance. AMD memory access architecture for processors is far
> better than performing then Intel . So I prefer AMD x64 over Intel
> EMT and has been implementing most of the sql server and desktops with
> AMD x64 processors
Curious: does it really make a difference? I would have guessed that
the capabilities of the IO subsystem are far more important for a DB
server than the CPU.
Kind regards
robert|||> Curious: does it really make a difference? I would have guessed that
> the capabilities of the IO subsystem are far more important for a DB
> server than the CPU.
I would not discount the importance of CPUs to that degree. It's true if
your workload is stressing something else, having more processor power
wouldn't help much. But in general, it pays to find out which processor works
best under what circusmstances. There are still many processing intensive
tasks. Plus, vendors are constantly trying to take advantage of the ever
increasing processor power, to trade processing for resources that may be
under stress.
Linchi
"Robert Klemme" wrote:
> On 04.05.2007 03:10, Bulent wrote:
> > On May 3, 6:12 pm, "Hassan" <has...@.hotmail.com> wrote:
> >> I know at one time AMD used to be power players for SQL Servers, but of
> >> late, I am hearing that Intel chips are also proving to be efficient.
> >>
> >> Can you share what you have seen in your benchmarks on some of the new
> >> models say in the HP space using either Intel or AMD ?
> >
> > We upgraded to AMD 64 bit dual opteron box with 4 gb memory and we
> > were completely happy with the results. That was almost 2 years ago
> > and back then AMD sort of swept the market with price and
> > performance. AMD memory access architecture for processors is far
> > better than performing then Intel . So I prefer AMD x64 over Intel
> > EMT and has been implementing most of the sql server and desktops with
> > AMD x64 processors
> Curious: does it really make a difference? I would have guessed that
> the capabilities of the IO subsystem are far more important for a DB
> server than the CPU.
> Kind regards
> robert
>|||On May 4, 9:49 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> > Curious: does it really make a difference? I would have guessed that
> > the capabilities of the IO subsystem are far more important for a DB
> > server than the CPU.
> I would not discount the importance of CPUs to that degree. It's true if
> your workload is stressing something else, having more processor power
> wouldn't help much. But in general, it pays to find out which processor works
> best under what circusmstances. There are still many processing intensive
> tasks. Plus, vendors are constantly trying to take advantage of the ever
> increasing processor power, to trade processing for resources that may be
> under stress.
> Linchi
>
> "Robert Klemme" wrote:
> > On 04.05.2007 03:10, Bulent wrote:
> > > On May 3, 6:12 pm, "Hassan" <has...@.hotmail.com> wrote:
> > >> I know at one time AMD used to be power players for SQL Servers, but of
> > >> late, I am hearing that Intel chips are also proving to be efficient.
> > >> Can you share what you have seen in your benchmarks on some of the new
> > >> models say in the HP space using either Intel or AMD ?
> > > We upgraded to AMD 64 bit dual opteron box with 4 gb memory and we
> > > were completely happy with the results. That was almost 2 years ago
> > > and back then AMD sort of swept the market with price and
> > > performance. AMD memory access architecture for processors is far
> > > better than performing then Intel . So I prefer AMD x64 over Intel
> > > EMT and has been implementing most of the sql server and desktops with
> > > AMD x64 processors
> > Curious: does it really make a difference? I would have guessed that
> > the capabilities of the IO subsystem are far more important for a DB
> > server than the CPU.
> > Kind regards
> > robert- Hide quoted text -
> - Show quoted text -
Definitely there is the IO subsystem that's very important for
database servers. The hypertransport technology and direct memory
access that amd based systems use is also far better than Intel.
Bottom line you will get better IO performance. Before you make your
decision check out the those and compare against intel and it's Core2
Duo systems and io, memory access methods.

ambiguity performance problem

The first query execution time less than 1 second
But the second query takes around one minute

SELECT ACC_KEY1,ACC_STATUS_LAST FROM PSSIG.CLNT_ACCOUNTS INNER JOIN PSSIG.CLNT_CUSTOMERS ON
PSSIG.CLNT_ACCOUNTS.CSTMR_OID = PSSIG.CLNT_CUSTOMERS.CSTMR_OID
WHERE (PSSIG.CLNT_CUSTOMERS.CSTMR_START_DT >= '1900-1-1 12:00:00') AND
(PSSIG.CLNT_CUSTOMERS.CSTMR_END_DT <= '2106-12-31 12:00:00') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 >= '0000000000000') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 <= '9999999999999') AND
(PSSIG.CLNT_ACCOUNTS.ACC_STATUS_LAST in (5,-999)) AND
ACC_KEY1 > '0' ORDER BY ACC_KEY1

SELECT ACC_KEY1,ACC_STATUS_LAST FROM PSSIG.CLNT_ACCOUNTS INNER JOIN PSSIG.CLNT_CUSTOMERS ON
PSSIG.CLNT_ACCOUNTS.CSTMR_OID = PSSIG.CLNT_CUSTOMERS.CSTMR_OID
WHERE (PSSIG.CLNT_CUSTOMERS.CSTMR_START_DT >= '1900-1-1 12:00:00') AND
(PSSIG.CLNT_CUSTOMERS.CSTMR_END_DT <= '2106-12-31 12:00:00') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 >= '0000000000000') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 <= '9999999999999') AND
(PSSIG.CLNT_ACCOUNTS.ACC_STATUS_LAST = 5 ) AND
ACC_KEY1 > '0' ORDER BY ACC_KEY1

Note 1 : value -999 deos not exist in the table field ACC_STATUS_LAST
Note 2 : value 5 exist in most of rows about ( 999999/1000000 ) from the table rows count
Note 3 : the number of rows in each table around 15000000

Hi,

didi you have a look at the execution plan ? The execution plan can be seen in QA (using SQL 2k) or Query Pane (using SQL2k5) graphically or by using the statement SET SHOWPLAN ON before isseing the statement.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Yeah, and then post it. I would love to see how an equality query is beat out by an OR query. This looks very much like it is probably an optimizer issue, unless the plan shows something obvious...|||

thank you for this notice ,

because there is a big difference between the access plan for both queries

and I don't now why this happen ,

AM PM format

How can I show time portion of a date in "hh:mm:ss" AM?PM format?
ThanksIn the format property for a textbox use the following:
hh:mm:ss tt
HTH
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"Mark Goldin" wrote:
> How can I show time portion of a date in "hh:mm:ss" AM?PM format?
> Thanks
>
>|||What Language setting have you used on the Report?
Click on the Report Property and look for Language. Set it to English
(United States)
Then click on a textbox with a time datatype in, and set T as format. Now
you have am/pm.
If you don't want to change the language of the whole report, you'll have to
use some formating function on the expression. Or change the format in your
query, to make it return the time in the correct format from your data
source.
Kaisa M. Lndahl Lervik
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:utU3$sP3GHA.3428@.TK2MSFTNGP05.phx.gbl...
> How can I show time portion of a date in "hh:mm:ss" AM?PM format?
> Thanks
>

am i doing this right?

i am trying to count the no of time a value appears in a column for a given event ID. i have written the following code:

private

void TotalChanged()
{
///<summary>
///this procedure calculates the total amount of times the event has been
///modified altogether. the number is read only and written to a text box.
///</summary>int evID = Convert.ToInt32(Request["EID"]);
myda =new SqlDataAdapter("SELECT COUNT (TotalChanged) FROM EventDateChange WHERE EventID = '" + evID + "'", mycn);
myda.Fill(ds3, "ChangedEvent");
foreach (DataRow drin ds3.Tables[0].Rows)
{
int evTotal = (int)dr["TotalChanged"];
Txt_Changes.Text = evTotal.ToString();
}
}

i know the queries ok, as i tested it in SQl Query analyser, but how do i write that value to the textbox called text-changes? everytime i run it i get the errorColumn 'TotalChanged' does not belong to table ChangedEvent.??

Anyone any ideas?? Thanks in advance!

TrySELECT COUNT (TotalChanged)AS TotalChanged FROM EventDateChange WHERE EventID = '" + evID + "'",

This is because when you use COUNT(), there is no column name. After you add AS TotalChanged, you assign the column name to the count.

|||Doh!! Thanks! Works perfectly

Alternatives to CURSORs

Hi all

I have often come across discussions on this forum saying that CURSORs are expensive in time (processing power?).

Having used CURSORs to processing a mere 2000+ record (not much at all) which took a fair while to complete, I now realize why you guys are saying CURSORs are expensive.

But is there alternatives to using CURSORs in the situation where I try to process every records returned by a particular query?

Say for example, i want to update columns that comes from different tables for every record that is returned by a SELECT JOIN query. there is no way that i can do that with a single UPDATE statement cause i can't do JOIN with UPDATE query.

All comments welcome

James :)well u can :
update a set aa= b.bb
from table_a a
inner join table_b b on a.key = b.key

since in cursors u use loops try :
loop on numeric key in table

select @.i = Min(int_key) From Tablename
while @.i <= (select @.Max(int_key) From Tablename)
begin
.
.

end|||yes you can join tables in the from clause of an update statement
[BOL] UPDATE (described)

check out example 'C' at the bottom of the help document|||Thank you guys.

Is it standard ANSI to use join in an update query? Although it would make sense that it is. I have tried it before without success for some reason. :( I will try it again.

James :)|||No, JOIN operations in an UPDATE are explicitly forbidden by the ISO, and were never addressed by ANSI. While JOIN operations in an UPDATE can be convenient, they violate most of the rules of relational algebra. Sybase and Microsoft are the only commercially successful engines I can think of that support them.

-PatP|||Really?

So in Oracle, for instance, you can't execute a statement like:

update A
set A.Column = NewValue
from A
inner join B on A.Key = B.Value

?

ANSI or not, that's pretty simple and pretty convenient too.|||Originally posted by Pat Phelan
No, JOIN operations in an UPDATE are explicitly forbidden by the ISO, and were never addressed by ANSI. While JOIN operations in an UPDATE can be convenient, they violate most of the rules of relational algebra. Sybase and Microsoft are the only commercially successful engines I can think of that support them.

-PatP

Nope...even DB2 OS/390 can do it now...it's just extremely painful...

But we did have a very good thread where we discussed how I "crossed the line" and broke the rules...

I gotta look it up...|||Here it is...

Bookmarked it...

http://www.dbforums.com/showthread.php?threadid=989508|||subqueries are useful here, as they provide for the referencing of tables.
normally in a complex update or delete i will create a query that doesnt change the data and after i recieve the correct results, i will use it as a subquery for the update\delete stmt. especially if the sarg is a dynamic value.

update t1
set c2 = x
where col3 in (select col3 from t2
where col4 = x)

however, be carefull with subqueries as they can have some definite disadvantages. specifically correlated subqueries

in addition when you join tables in an update\delete, the sql optimizer has a great deal of flexibility with the join operations where in the subquery the inner and outer queries kind of restrict the optimizers options.|||Originally posted by blindman
Really?

So in Oracle, for instance, you can't execute a statement like:

update A
set A.Column = NewValue
from A
inner join B on A.Key = B.Value

?

ANSI or not, that's pretty simple and pretty convenient too. I rarely think of Oracle, or at least I try not to.

I didn't realize that DB2 supported this form of blaspheme. I'm sure that it is great fun listening to Celko on this topic!

-PatP|||It's just so damn useful...in the (DB2) old days when it didn't, you had to either use a cursor, or genrate the satements and then execute them in a batch...|||As far back as I can remember, DB2 supported sub-queries. Sub-queries are safe to use in an UPDATE as long as they are stochastic and deterministic. I don't know when DB2 added support for JOIN operations within an UPDATE.

-PatP|||I think it was back in V5...

and whoah...

had to look that one up

http://www.hyperdictionary.com/dictionary/stochastic|||Ooops, my bad. I meant non-stochastic. Sorry.

-PatP|||Thought it was kind of like oil and water...