[URGENT] Problem to Restore Database from backup

Mrs,

I'm having a serious problem in SQL 2000 SP3 Database which is used Business Applications.
Today, I tryied to take backup in service time by the Enterprize Manager, the following
error occured and could not take backup.
--
Error 926 : Database '[database name]' cannot opened. It has been marked SUSPECT by recovery.
See the SQL Server errorlog for more information.
--
After that, we tried same operation 3 times. But result is same.

After sometime, end-user report us that they can not log-in to database.

So we tried to dettach this database and attached it.
We success to dettach, but we can not attached it.

Also we tried to restore this database from Yesterday's backup file by using Enterprize Manager,
but the following error occured, and could not complete.
This backup file was generated daily backup task in Maintenance Plan in Enterprize Manager.
--
Microsoft SQl-DMO(ODBC SQLState:42000)
The media family on device "E:\ccd\......xxx.bak" is incorrectly formed.
SQL Server cannot process this media family.
RESTORE DATABASE is terminating abnormally.

--

We need to recover the database immediately not to stop the end-user business process.
Please help us.

Yamagury.

refer , http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1977204&SiteID=1 try as suggested and get back.... also you need to apply SP4 or go to sql 2005 as sql 2000 sp3 has ended its life cycle in july 10th 2007 itself....

refer

http://support.microsoft.com/gp/lifesupsps

|||Thank you for your support.

Yes, We already tried to apply SP4.
But this problem was not solved.

Now I'm trying to do as your instruction.|||(you had to back up the transaction log before you perform any restore operations and hadn't detach it before.

You had to verify your backup with RESTORE VERIFYONLY statement .)

use RESTORE HEADERONLY and see if you have multiple backup files in the the same backup file. If yes then
try using FILE= option while restoring.

File is from :

RESTORE DATABASE { database_name | @.database_name_var }
< file_or_filegroup > [ ,...n ]
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @.file_number } ] .....

see Books Online

(Why do you have not SP4?

Read this.)

|||
Dear Deepak

I tried to create database and swap mdf/ldf file.
and go into single user mode, and then run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS,

but, DBCC command return error. Database could not be repaired.

How can I do after this?|||

Ok you can try as follows,

Alter database DBname set emergency

this will bring your db into emergency mode so that you can access your db and then subsequently export the datas alone from this db to another db so that you can minimize the dataloss.....if you have a full backup then drop this suspect database and restore it from full backup but there will be dataloss to minimize it set the db as emergency mode and export the tables,sps etc which you feel is required to another db in UAT server and script those and rerun the script in the restored db

[TCP/IP sockets]sql server does not exist or access denied

I reinstalled sql server 2000 on Windoes server 2003 three times due to diff
errors - 1st time replication recovery did not work; 2nd time typo in the
file name ; 3rd time typo in the server name. Then when I installed it the
4th time , the installation failed with system error [TCP/IP sockets] sq
l
server does not exist or access denied. Is this possible that the problem
is related to the port conflict in the machine?
Please advise !!!
Thanks.
Wen ChangRestart ur server delete all the installation files.
HTH
from
Doller
Wen Chang wrote:
> I reinstalled sql server 2000 on Windoes server 2003 three times due to di
ff
> errors - 1st time replication recovery did not work; 2nd time typo in the
> file name ; 3rd time typo in the server name. Then when I installed it th
e
> 4th time , the installation failed with system error [TCP/IP sockets]
sql
> server does not exist or access denied. Is this possible that the proble
m
> is related to the port conflict in the machine?
> Please advise !!!
> Thanks.
> Wen Chang

[Performance Discussion] To schedule a time for mssql command, which way would be faster a

1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection

above, which way would be faster and get a better performance?I don't think you'll see any performance difference.

but go with agent.|||ok, but will have a difference when busy

[OTP] Multi User Application Database Transaction Stability

I am currently developing a multi user VB.NET 2005 application and this is my first time to involve in multi user application. Anyway, i know that multi-user application is not as easy as standalone application. I need to take care of the database stability for each and every user. My concern is may i know how to build up this stability for each and every transaction in terms of Data insert and update? This database resides on server and the user is able to access the data through the client application.

INSERT

The primary key is generated by application when the user enters into the data insert screen and it is done by counting the total amount of records in database. If two users perform insert at the same time, may i know how to deal with these primary keys? I am sure my application must be able to generate two different primary keys for these two users based on the time gap e.g. 0.001 milli second for the first user who enters into the screen and occupy the first primary key than the second user. But i have no idea on how to translate this to code.

UPDATE

If a particular record is viewed by a user and that user peforms an updates on the record while another user updates it at the same time, i may need to figure out a solution for this else the database will really be crashed. Is there any suggestion on it? May i know how to implement lock and unlock the record? Thank you.

For the INSERT part, you can't really generate keys like that from the client side without locking the entire table on the server. You can look at use of IDENTITY column for example which allows you to generate sequential numbers on the server. The server handles the concurrency part. There are other ways to generate keys on the client like using GUID for example. But all of these depends on the type of application you are writing and your data model.

For the UPDATE part, when one connection is updating a particular row or rows then any other connection that tries to update the same row(s) will be blocked. So the concurrent updates will happen in serial fashion depending on which ran first.

My suggestion would be to look at some of the topics on concurrency, locking, transactions in Books Online to get an idea of what the database engine can do. You should also get a book on SQL Server that describes some of these concepts so you don't try to invent or reinvent elaborate techniques on the client which might be suspect at best.

[Microsoft][ODBC SQL Server Driver]Timeout expired

Hi,
[Microsoft][ODBC SQL Server Driver]Timeout expired
I am getting the above error all the time:
In are old verison of the App it is not working.
The query is updating are table that has varchar(8000) columns and text(16)
columns.
Is there anything I can do to make this run faster. If you run the whole
query in SQL Analyzer it runs in milliseconds, but through the ASP pages that
are updating a record it Timeouts. And it is only happening to this one
table, we have other similar tables that have text and large varchars, that
run in are ASP pages just fine.
Thanks,
MattHi
Whist it is running, run sp_who2
Check for blocked processes
Looks like you have processes blocking each other so eventually the query
times out.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jaffa" <Jaffa@.discussions.microsoft.com> wrote in message
news:EE8F55E6-C237-4262-920D-B0E889DE29CD@.microsoft.com...
> Hi,
> [Microsoft][ODBC SQL Server Driver]Timeout expired
>
> I am getting the above error all the time:
> In are old verison of the App it is not working.
> The query is updating are table that has varchar(8000) columns and
text(16)
> columns.
> Is there anything I can do to make this run faster. If you run the whole
> query in SQL Analyzer it runs in milliseconds, but through the ASP pages
that
> are updating a record it Timeouts. And it is only happening to this one
> table, we have other similar tables that have text and large varchars,
that
> run in are ASP pages just fine.
> Thanks,
> Matt
>|||Can you post the DDL for the table and exactly how you are updating the row?
--
Andrew J. Kelly SQL MVP
"Jaffa" <Jaffa@.discussions.microsoft.com> wrote in message
news:EE8F55E6-C237-4262-920D-B0E889DE29CD@.microsoft.com...
> Hi,
> [Microsoft][ODBC SQL Server Driver]Timeout expired
>
> I am getting the above error all the time:
> In are old verison of the App it is not working.
> The query is updating are table that has varchar(8000) columns and
> text(16)
> columns.
> Is there anything I can do to make this run faster. If you run the whole
> query in SQL Analyzer it runs in milliseconds, but through the ASP pages
> that
> are updating a record it Timeouts. And it is only happening to this one
> table, we have other similar tables that have text and large varchars,
> that
> run in are ASP pages just fine.
> Thanks,
> Matt
>

[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error

We have built an application (VB 6 with a SQL 2000 backend database)
in-house and from time to time, we get the dbnetlib error message. This erro
r
message seems to happen when the machine is just idle and is driving me nuts
.
The error message doesnt seem to have the issue while the user is actively
using the application. Could this problem have something to do with
connection pooling? If anyone has any ideas, it would be much appreciated.
Thanks in advance!
Mike HartmanWe have seen this from time-to-time. It seems that the connection "ages out"
after a long period of dormancy. The solution I use is to occasionally poll
the server to make sure it knows that the application is still alive. I
don't think it's a connection pooling issue. It looks like the transport
layer is being closed at the server end (in my case).
USE <initial catalog>
is enough to do the job.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:772A11BE-EF7D-4088-8654-9C29D15F1FCC@.microsoft.com...
> We have built an application (VB 6 with a SQL 2000 backend database)
> in-house and from time to time, we get the dbnetlib error message. This
> error
> message seems to happen when the machine is just idle and is driving me
> nuts.
> The error message doesnt seem to have the issue while the user is actively
> using the application. Could this problem have something to do with
> connection pooling? If anyone has any ideas, it would be much appreciated.
> Thanks in advance!
> Mike Hartman

[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error

We have built an application (VB 6 with a SQL 2000 backend database)
in-house and from time to time, we get the dbnetlib error message. This error
message seems to happen when the machine is just idle and is driving me nuts.
The error message doesnt seem to have the issue while the user is actively
using the application. Could this problem have something to do with
connection pooling? If anyone has any ideas, it would be much appreciated.
Thanks in advance!
Mike Hartman
We have seen this from time-to-time. It seems that the connection "ages out"
after a long period of dormancy. The solution I use is to occasionally poll
the server to make sure it knows that the application is still alive. I
don't think it's a connection pooling issue. It looks like the transport
layer is being closed at the server end (in my case).
USE <initial catalog>
is enough to do the job.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:772A11BE-EF7D-4088-8654-9C29D15F1FCC@.microsoft.com...
> We have built an application (VB 6 with a SQL 2000 backend database)
> in-house and from time to time, we get the dbnetlib error message. This
> error
> message seems to happen when the machine is just idle and is driving me
> nuts.
> The error message doesnt seem to have the issue while the user is actively
> using the application. Could this problem have something to do with
> connection pooling? If anyone has any ideas, it would be much appreciated.
> Thanks in advance!
> Mike Hartman

[Microsoft][ODBC SQL Server Driver] Invalid cursor stateevery time I save in enterprise ma

Hi,
every time I save after a change in any table through enterprise nmanager
I receive this error:
[Microsoft][ODBC SQL Server Driver] Invalid cursor state
If I generate a script instead-of saving directly
and if I execute this script through the query analyzer all works fine!
My windows 2003 is english
my SQL Server is french
my default language is french
I've found the same error on another server which is in the same config.
any idea?
thanks.
Jerome.
> every time I save after a change in any table through enterprise nmanager
> I receive this error:
> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
So why not use scripts in Query Analyzer? IMHO, there are only a few cases
where Enterprise Manager should be used...
http://www.aspfaq.com/2515
http://www.aspfaq.com/2455
http://www.aspfaq.com/
(Reverse address to reply.)
|||enterprise manager is easy to use.
When I update or add a column, I can do this very quickly in enterprise
manager
using query analyzer is an overhead for me :-)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
> So why not use scripts in Query Analyzer? IMHO, there are only a few
> cases
> where Enterprise Manager should be used...
> http://www.aspfaq.com/2515
> http://www.aspfaq.com/2455
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
|||This is over your head?
ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
http://www.aspfaq.com/
(Reverse address to reply.)
"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> enterprise manager is easy to use.
> When I update or add a column, I can do this very quickly in enterprise
> manager
> using query analyzer is an overhead for me :-)
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
nmanager
>
|||yep
specially when you have a lot of columns to change/add/remove :-)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
> This is over your head?
> ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
> nmanager
>
|||> yep
> specially when you have a lot of columns to change/add/remove :-)
Okay, so is there something especially painful about learning them? It
certainly can't HURT you to have this knowledge; surely someday you will
have to connect to SQL Server without using a GUI as a crutch...
|||There are different combinations of service packs that lend
themselves to having this error more often - e.g between
8.00.0859 and 8.00.0875 you can get the error more often,
version 8.00.0927 or no SP3 version 8.00.0194 you don't,
etc.
The bottom line is what Aaron keeps telling you - you should
be using Query Analyzer for this type of thing. It was
designed more for this type of activity.
-Sue
On Mon, 15 Nov 2004 11:17:34 -0500, "Jj"
<willgart_A_@.hotmail_A_.com> wrote:

>yep
>specially when you have a lot of columns to change/add/remove :-)
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
>
|||I've the version 859
what is the 927 version? SP4? intermediate release?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:isshp0916fff78e8q6i0rn7n1lh5a0meen@.4ax.com...
> There are different combinations of service packs that lend
> themselves to having this error more often - e.g between
> 8.00.0859 and 8.00.0875 you can get the error more often,
> version 8.00.0927 or no SP3 version 8.00.0194 you don't,
> etc.
> The bottom line is what Aaron keeps telling you - you should
> be using Query Analyzer for this type of thing. It was
> designed more for this type of activity.
> -Sue
> On Mon, 15 Nov 2004 11:17:34 -0500, "Jj"
> <willgart_A_@.hotmail_A_.com> wrote:
>
|||> what is the 927 version? SP4? intermediate release?
Hotfix. Only certain ones are available without contacting PSS directly.
http://www.aspfaq.com/2160
http://www.aspfaq.com/
(Reverse address to reply.)
|||Hot fix for the following:
http://support.microsoft.com/?kbid=839688
But then you will be managing hot fixes based upon not
wanting to use Query Analyzer for writing T-SQL to maintain
your databases. Doesn't seem like a good path to go down.
So if another slammer comes out, would your company want you
to patch it and use T-SQL for table changes or would they
want you to leave the box vulnerable so you could use
Enterprise Manager to make all your table changes?
-Sue
On Mon, 15 Nov 2004 13:40:43 -0500, "Jj"
<willgart_A_@.hotmail_A_.com> wrote:

>I've the version 859
>what is the 927 version? SP4? intermediate release?
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:isshp0916fff78e8q6i0rn7n1lh5a0meen@.4ax.com.. .
>

[Microsoft][ODBC SQL Server Driver] Invalid cursor stateevery time I save in enterprise ma

Hi,
every time I save after a change in any table through enterprise nmanager
I receive this error:
[Microsoft][ODBC SQL Server Driver] Invalid cursor state
If I generate a script instead-of saving directly
and if I execute this script through the query analyzer all works fine!
My windows 2003 is english
my SQL Server is french
my default language is french
I've found the same error on another server which is in the same config.
any idea?
thanks.
Jerome.> every time I save after a change in any table through enterprise nmanager
> I receive this error:
> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
So why not use scripts in Query Analyzer? IMHO, there are only a few cases
where Enterprise Manager should be used...
http://www.aspfaq.com/2515
http://www.aspfaq.com/2455
--
http://www.aspfaq.com/
(Reverse address to reply.)|||enterprise manager is easy to use.
When I update or add a column, I can do this very quickly in enterprise
manager
using query analyzer is an overhead for me :-)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
>> every time I save after a change in any table through enterprise nmanager
>> I receive this error:
>> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
> So why not use scripts in Query Analyzer? IMHO, there are only a few
> cases
> where Enterprise Manager should be used...
> http://www.aspfaq.com/2515
> http://www.aspfaq.com/2455
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||This is over your head?
ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
> enterprise manager is easy to use.
> When I update or add a column, I can do this very quickly in enterprise
> manager
> using query analyzer is an overhead for me :-)
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
> >> every time I save after a change in any table through enterprise
nmanager
> >> I receive this error:
> >> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
> >
> > So why not use scripts in Query Analyzer? IMHO, there are only a few
> > cases
> > where Enterprise Manager should be used...
> >
> > http://www.aspfaq.com/2515
> > http://www.aspfaq.com/2455
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
>|||yep
specially when you have a lot of columns to change/add/remove :-)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
> This is over your head?
> ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
>> enterprise manager is easy to use.
>> When I update or add a column, I can do this very quickly in enterprise
>> manager
>> using query analyzer is an overhead for me :-)
>>
>> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
>> >> every time I save after a change in any table through enterprise
> nmanager
>> >> I receive this error:
>> >> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
>> >
>> > So why not use scripts in Query Analyzer? IMHO, there are only a few
>> > cases
>> > where Enterprise Manager should be used...
>> >
>> > http://www.aspfaq.com/2515
>> > http://www.aspfaq.com/2455
>> >
>> > --
>> > http://www.aspfaq.com/
>> > (Reverse address to reply.)
>> >
>> >
>>
>|||> yep
> specially when you have a lot of columns to change/add/remove :-)
Okay, so is there something especially painful about learning them? It
certainly can't HURT you to have this knowledge; surely someday you will
have to connect to SQL Server without using a GUI as a crutch...|||There are different combinations of service packs that lend
themselves to having this error more often - e.g between
8.00.0859 and 8.00.0875 you can get the error more often,
version 8.00.0927 or no SP3 version 8.00.0194 you don't,
etc.
The bottom line is what Aaron keeps telling you - you should
be using Query Analyzer for this type of thing. It was
designed more for this type of activity.
-Sue
On Mon, 15 Nov 2004 11:17:34 -0500, "Jéjé"
<willgart_A_@.hotmail_A_.com> wrote:
>yep
>specially when you have a lot of columns to change/add/remove :-)
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
>> This is over your head?
>> ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
>> enterprise manager is easy to use.
>> When I update or add a column, I can do this very quickly in enterprise
>> manager
>> using query analyzer is an overhead for me :-)
>>
>> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
>> >> every time I save after a change in any table through enterprise
>> nmanager
>> >> I receive this error:
>> >> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
>> >
>> > So why not use scripts in Query Analyzer? IMHO, there are only a few
>> > cases
>> > where Enterprise Manager should be used...
>> >
>> > http://www.aspfaq.com/2515
>> > http://www.aspfaq.com/2455
>> >
>> > --
>> > http://www.aspfaq.com/
>> > (Reverse address to reply.)
>> >
>> >
>>
>>
>|||I've the version 859
what is the 927 version? SP4? intermediate release?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:isshp0916fff78e8q6i0rn7n1lh5a0meen@.4ax.com...
> There are different combinations of service packs that lend
> themselves to having this error more often - e.g between
> 8.00.0859 and 8.00.0875 you can get the error more often,
> version 8.00.0927 or no SP3 version 8.00.0194 you don't,
> etc.
> The bottom line is what Aaron keeps telling you - you should
> be using Query Analyzer for this type of thing. It was
> designed more for this type of activity.
> -Sue
> On Mon, 15 Nov 2004 11:17:34 -0500, "Jéjé"
> <willgart_A_@.hotmail_A_.com> wrote:
>>yep
>>specially when you have a lot of columns to change/add/remove :-)
>>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>>news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
>> This is over your head?
>> ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
>> enterprise manager is easy to use.
>> When I update or add a column, I can do this very quickly in enterprise
>> manager
>> using query analyzer is an overhead for me :-)
>>
>> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
>> >> every time I save after a change in any table through enterprise
>> nmanager
>> >> I receive this error:
>> >> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
>> >
>> > So why not use scripts in Query Analyzer? IMHO, there are only a few
>> > cases
>> > where Enterprise Manager should be used...
>> >
>> > http://www.aspfaq.com/2515
>> > http://www.aspfaq.com/2455
>> >
>> > --
>> > http://www.aspfaq.com/
>> > (Reverse address to reply.)
>> >
>> >
>>
>>
>|||> what is the 927 version? SP4? intermediate release?
Hotfix. Only certain ones are available without contacting PSS directly.
http://www.aspfaq.com/2160
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Hot fix for the following:
http://support.microsoft.com/?kbid=839688
But then you will be managing hot fixes based upon not
wanting to use Query Analyzer for writing T-SQL to maintain
your databases. Doesn't seem like a good path to go down.
So if another slammer comes out, would your company want you
to patch it and use T-SQL for table changes or would they
want you to leave the box vulnerable so you could use
Enterprise Manager to make all your table changes?
-Sue
On Mon, 15 Nov 2004 13:40:43 -0500, "Jéjé"
<willgart_A_@.hotmail_A_.com> wrote:
>I've the version 859
>what is the 927 version? SP4? intermediate release?
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:isshp0916fff78e8q6i0rn7n1lh5a0meen@.4ax.com...
>> There are different combinations of service packs that lend
>> themselves to having this error more often - e.g between
>> 8.00.0859 and 8.00.0875 you can get the error more often,
>> version 8.00.0927 or no SP3 version 8.00.0194 you don't,
>> etc.
>> The bottom line is what Aaron keeps telling you - you should
>> be using Query Analyzer for this type of thing. It was
>> designed more for this type of activity.
>> -Sue
>> On Mon, 15 Nov 2004 11:17:34 -0500, "Jéjé"
>> <willgart_A_@.hotmail_A_.com> wrote:
>>yep
>>specially when you have a lot of columns to change/add/remove :-)
>>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>>news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
>> This is over your head?
>> ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
>> enterprise manager is easy to use.
>> When I update or add a column, I can do this very quickly in enterprise
>> manager
>> using query analyzer is an overhead for me :-)
>>
>> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
>> >> every time I save after a change in any table through enterprise
>> nmanager
>> >> I receive this error:
>> >> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
>> >
>> > So why not use scripts in Query Analyzer? IMHO, there are only a few
>> > cases
>> > where Enterprise Manager should be used...
>> >
>> > http://www.aspfaq.com/2515
>> > http://www.aspfaq.com/2455
>> >
>> > --
>> > http://www.aspfaq.com/
>> > (Reverse address to reply.)
>> >
>> >
>>
>>
>>
>|||ok ok...
I'll try to change my working method ;-)
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:9tuhp0p5ab2leg6t7527kfe6r68n014ia8@.4ax.com...
> Hot fix for the following:
> http://support.microsoft.com/?kbid=839688
> But then you will be managing hot fixes based upon not
> wanting to use Query Analyzer for writing T-SQL to maintain
> your databases. Doesn't seem like a good path to go down.
> So if another slammer comes out, would your company want you
> to patch it and use T-SQL for table changes or would they
> want you to leave the box vulnerable so you could use
> Enterprise Manager to make all your table changes?
> -Sue
> On Mon, 15 Nov 2004 13:40:43 -0500, "Jéjé"
> <willgart_A_@.hotmail_A_.com> wrote:
>>I've the version 859
>>what is the 927 version? SP4? intermediate release?
>>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>>news:isshp0916fff78e8q6i0rn7n1lh5a0meen@.4ax.com...
>> There are different combinations of service packs that lend
>> themselves to having this error more often - e.g between
>> 8.00.0859 and 8.00.0875 you can get the error more often,
>> version 8.00.0927 or no SP3 version 8.00.0194 you don't,
>> etc.
>> The bottom line is what Aaron keeps telling you - you should
>> be using Query Analyzer for this type of thing. It was
>> designed more for this type of activity.
>> -Sue
>> On Mon, 15 Nov 2004 11:17:34 -0500, "Jéjé"
>> <willgart_A_@.hotmail_A_.com> wrote:
>>yep
>>specially when you have a lot of columns to change/add/remove :-)
>>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>>news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
>> This is over your head?
>> ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
>> enterprise manager is easy to use.
>> When I update or add a column, I can do this very quickly in
>> enterprise
>> manager
>> using query analyzer is an overhead for me :-)
>>
>> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
>> >> every time I save after a change in any table through enterprise
>> nmanager
>> >> I receive this error:
>> >> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
>> >
>> > So why not use scripts in Query Analyzer? IMHO, there are only a
>> > few
>> > cases
>> > where Enterprise Manager should be used...
>> >
>> > http://www.aspfaq.com/2515
>> > http://www.aspfaq.com/2455
>> >
>> > --
>> > http://www.aspfaq.com/
>> > (Reverse address to reply.)
>> >
>> >
>>
>>
>>
>|||Related question:
Because of this same problem I have started using the query analyzer for my
query changes. I cannot find a reference for how to use ALTER to
rearrange(MOVE) columns within a table. Can someone show me an example
syntax of how to do this in TSQL?
"Jéjé" wrote:
> ok ok...
> I'll try to change my working method ;-)
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:9tuhp0p5ab2leg6t7527kfe6r68n014ia8@.4ax.com...
> > Hot fix for the following:
> > http://support.microsoft.com/?kbid=839688
> >
> > But then you will be managing hot fixes based upon not
> > wanting to use Query Analyzer for writing T-SQL to maintain
> > your databases. Doesn't seem like a good path to go down.
> > So if another slammer comes out, would your company want you
> > to patch it and use T-SQL for table changes or would they
> > want you to leave the box vulnerable so you could use
> > Enterprise Manager to make all your table changes?
> >
> > -Sue
> >
> > On Mon, 15 Nov 2004 13:40:43 -0500, "Jéjé"
> > <willgart_A_@.hotmail_A_.com> wrote:
> >
> >>I've the version 859
> >>what is the 927 version? SP4? intermediate release?
> >>
> >>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> >>news:isshp0916fff78e8q6i0rn7n1lh5a0meen@.4ax.com...
> >> There are different combinations of service packs that lend
> >> themselves to having this error more often - e.g between
> >> 8.00.0859 and 8.00.0875 you can get the error more often,
> >> version 8.00.0927 or no SP3 version 8.00.0194 you don't,
> >> etc.
> >> The bottom line is what Aaron keeps telling you - you should
> >> be using Query Analyzer for this type of thing. It was
> >> designed more for this type of activity.
> >>
> >> -Sue
> >>
> >> On Mon, 15 Nov 2004 11:17:34 -0500, "Jéjé"
> >> <willgart_A_@.hotmail_A_.com> wrote:
> >>
> >>yep
> >>specially when you have a lot of columns to change/add/remove :-)
> >>
> >>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> >>news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
> >> This is over your head?
> >>
> >> ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
> >>
> >> --
> >> http://www.aspfaq.com/
> >> (Reverse address to reply.)
> >>
> >>
> >>
> >>
> >> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> >> news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
> >> enterprise manager is easy to use.
> >> When I update or add a column, I can do this very quickly in
> >> enterprise
> >> manager
> >>
> >> using query analyzer is an overhead for me :-)
> >>
> >>
> >> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> >> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
> >> >> every time I save after a change in any table through enterprise
> >> nmanager
> >> >> I receive this error:
> >> >> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
> >> >
> >> > So why not use scripts in Query Analyzer? IMHO, there are only a
> >> > few
> >> > cases
> >> > where Enterprise Manager should be used...
> >> >
> >> > http://www.aspfaq.com/2515
> >> > http://www.aspfaq.com/2455
> >> >
> >> > --
> >> > http://www.aspfaq.com/
> >> > (Reverse address to reply.)
> >> >
> >> >
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
>
>|||you have to copy your table in a new one which has the right order
then delete the first table and rename the temporary table to the right name
(all in a transaction)
or you can use enterprise manager to change the order and ask to generate
the SQL statement instead-of saving the change. then execute this syntax.
"Don" <Don@.discussions.microsoft.com> wrote in message
news:140DD312-365C-45C3-9280-8082FAE8F62C@.microsoft.com...
> Related question:
> Because of this same problem I have started using the query analyzer for
> my
> query changes. I cannot find a reference for how to use ALTER to
> rearrange(MOVE) columns within a table. Can someone show me an example
> syntax of how to do this in TSQL?
> "Jéjé" wrote:
>> ok ok...
>> I'll try to change my working method ;-)
>>
>> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>> news:9tuhp0p5ab2leg6t7527kfe6r68n014ia8@.4ax.com...
>> > Hot fix for the following:
>> > http://support.microsoft.com/?kbid=839688
>> >
>> > But then you will be managing hot fixes based upon not
>> > wanting to use Query Analyzer for writing T-SQL to maintain
>> > your databases. Doesn't seem like a good path to go down.
>> > So if another slammer comes out, would your company want you
>> > to patch it and use T-SQL for table changes or would they
>> > want you to leave the box vulnerable so you could use
>> > Enterprise Manager to make all your table changes?
>> >
>> > -Sue
>> >
>> > On Mon, 15 Nov 2004 13:40:43 -0500, "Jéjé"
>> > <willgart_A_@.hotmail_A_.com> wrote:
>> >
>> >>I've the version 859
>> >>what is the 927 version? SP4? intermediate release?
>> >>
>> >>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>> >>news:isshp0916fff78e8q6i0rn7n1lh5a0meen@.4ax.com...
>> >> There are different combinations of service packs that lend
>> >> themselves to having this error more often - e.g between
>> >> 8.00.0859 and 8.00.0875 you can get the error more often,
>> >> version 8.00.0927 or no SP3 version 8.00.0194 you don't,
>> >> etc.
>> >> The bottom line is what Aaron keeps telling you - you should
>> >> be using Query Analyzer for this type of thing. It was
>> >> designed more for this type of activity.
>> >>
>> >> -Sue
>> >>
>> >> On Mon, 15 Nov 2004 11:17:34 -0500, "Jéjé"
>> >> <willgart_A_@.hotmail_A_.com> wrote:
>> >>
>> >>yep
>> >>specially when you have a lot of columns to change/add/remove :-)
>> >>
>> >>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> >>news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
>> >> This is over your head?
>> >>
>> >> ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
>> >>
>> >> --
>> >> http://www.aspfaq.com/
>> >> (Reverse address to reply.)
>> >>
>> >>
>> >>
>> >>
>> >> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> >> news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
>> >> enterprise manager is easy to use.
>> >> When I update or add a column, I can do this very quickly in
>> >> enterprise
>> >> manager
>> >>
>> >> using query analyzer is an overhead for me :-)
>> >>
>> >>
>> >> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> >> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
>> >> >> every time I save after a change in any table through
>> >> >> enterprise
>> >> nmanager
>> >> >> I receive this error:
>> >> >> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
>> >> >
>> >> > So why not use scripts in Query Analyzer? IMHO, there are only
>> >> > a
>> >> > few
>> >> > cases
>> >> > where Enterprise Manager should be used...
>> >> >
>> >> > http://www.aspfaq.com/2515
>> >> > http://www.aspfaq.com/2455
>> >> >
>> >> > --
>> >> > http://www.aspfaq.com/
>> >> > (Reverse address to reply.)
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>>

[Microsoft][ODBC SQL Server Driver] Invalid cursor stateevery time I save in enterpris

Hi,
every time I save after a change in any table through enterprise nmanager
I receive this error:
[Microsoft][ODBC SQL Server Driver] Invalid cursor state
If I generate a script instead-of saving directly
and if I execute this script through the query analyzer all works fine!
My windows 2003 is english
my SQL Server is french
my default language is french
I've found the same error on another server which is in the same config.
any idea?
thanks.
Jerome.> every time I save after a change in any table through enterprise nmanager
> I receive this error:
> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
So why not use scripts in Query Analyzer? IMHO, there are only a few cases
where Enterprise Manager should be used...
http://www.aspfaq.com/2515
http://www.aspfaq.com/2455
http://www.aspfaq.com/
(Reverse address to reply.)|||enterprise manager is easy to use.
When I update or add a column, I can do this very quickly in enterprise
manager
using query analyzer is an overhead for me :-)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
> So why not use scripts in Query Analyzer? IMHO, there are only a few
> cases
> where Enterprise Manager should be used...
> http://www.aspfaq.com/2515
> http://www.aspfaq.com/2455
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||This is over your head?
ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
http://www.aspfaq.com/
(Reverse address to reply.)
"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
> enterprise manager is easy to use.
> When I update or add a column, I can do this very quickly in enterprise
> manager
> using query analyzer is an overhead for me :-)
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
nmanager[vbcol=seagreen]
>|||yep
specially when you have a lot of columns to change/add/remove :-)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
> This is over your head?
> ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
> nmanager
>|||> yep
> specially when you have a lot of columns to change/add/remove :-)
Okay, so is there something especially painful about learning them? It
certainly can't HURT you to have this knowledge; surely someday you will
have to connect to SQL Server without using a GUI as a crutch...|||There are different combinations of service packs that lend
themselves to having this error more often - e.g between
8.00.0859 and 8.00.0875 you can get the error more often,
version 8.00.0927 or no SP3 version 8.00.0194 you don't,
etc.
The bottom line is what Aaron keeps telling you - you should
be using Query Analyzer for this type of thing. It was
designed more for this type of activity.
-Sue
On Mon, 15 Nov 2004 11:17:34 -0500, "Jj"
<willgart_A_@.hotmail_A_.com> wrote:

>yep
>specially when you have a lot of columns to change/add/remove :-)
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
>|||I've the version 859
what is the 927 version? SP4? intermediate release?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:isshp0916fff78e8q6i0rn7n1lh5a0meen@.
4ax.com...
> There are different combinations of service packs that lend
> themselves to having this error more often - e.g between
> 8.00.0859 and 8.00.0875 you can get the error more often,
> version 8.00.0927 or no SP3 version 8.00.0194 you don't,
> etc.
> The bottom line is what Aaron keeps telling you - you should
> be using Query Analyzer for this type of thing. It was
> designed more for this type of activity.
> -Sue
> On Mon, 15 Nov 2004 11:17:34 -0500, "Jj"
> <willgart_A_@.hotmail_A_.com> wrote:
>
>|||> what is the 927 version? SP4? intermediate release?
Hotfix. Only certain ones are available without contacting PSS directly.
http://www.aspfaq.com/2160
http://www.aspfaq.com/
(Reverse address to reply.)|||Hot fix for the following:
http://support.microsoft.com/?kbid=839688
But then you will be managing hot fixes based upon not
wanting to use Query Analyzer for writing T-SQL to maintain
your databases. Doesn't seem like a good path to go down.
So if another slammer comes out, would your company want you
to patch it and use T-SQL for table changes or would they
want you to leave the box vulnerable so you could use
Enterprise Manager to make all your table changes?
-Sue
On Mon, 15 Nov 2004 13:40:43 -0500, "Jj"
<willgart_A_@.hotmail_A_.com> wrote:

>I've the version 859
>what is the 927 version? SP4? intermediate release?
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:isshp0916fff78e8q6i0rn7n1lh5a0meen@.
4ax.com...
>

[FR/EN] How to make several hundreds of recording at the same time?

FR
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-= *-
bonjour,
J'effectue plusieurs enregistrements provenant de plusieurs serveurs
dans une base SQL.
Mon probl=E8me est que l'enregistrement =E9crit des donn=E9es dans
plusieurs tables et donc quand il y a plusieurs centaines
d'enregistrements =E0 la m=EAme heure les donn=E9es sont m=E9lang=E9es dans
les tables.
J'utilise donc un BEGIN..TRAN COMMIT..TRAN pour faire une transaction
compl=E8te sans erreur, mais maintenant j'ai des probl=E8mes de
'resources lock'.
D'avance merci
christophe
EN
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-= *-
hello,
I carry out several recordings coming from several waiters in a base
SQL.
My problem is that the written recording of the data in several tables
and thus when there are several hundreds of recordings per same hour
the data are mixed in the tables.
I thus use a BEGIN..TRAN COMMIT..TRAN to make a complete transaction
without error, but now I have problems of ' resources lock'. - how to
make several hundreds of recording per same hour without mixing the
data?
In advance thank you for your assistance
christopheAlextophi a écrit :
> FR
> -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
> bonjour,
> J'effectue plusieurs enregistrements provenant de plusieurs serveurs
> dans une base SQL.
> Mon problème est que l'enregistrement écrit des données dans
> plusieurs tables et donc quand il y a plusieurs centaines
> d'enregistrements à la même heure les données sont mélangées dans
> les tables.
> J'utilise donc un BEGIN..TRAN COMMIT..TRAN pour faire une transaction
> complète sans erreur, mais maintenant j'ai des problèmes de
> 'resources lock'.
> D'avance merci
> christophe
> EN
> -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
> hello,
> I carry out several recordings coming from several waiters in a base
> SQL.
> My problem is that the written recording of the data in several tables
> and thus when there are several hundreds of recordings per same hour
> the data are mixed in the tables.
> I thus use a BEGIN..TRAN COMMIT..TRAN to make a complete transaction
> without error, but now I have problems of ' resources lock'. - how to
> make several hundreds of recording per same hour without mixing the
> data?
> In advance thank you for your assistance
> christophe
>
Si tu décrivait un peu plus ton problème avec le code et les DDL des
tables en jeu ce serait mieux.
Accessoirement poste dans le forum FR :
news:microsoft.public.fr.sqlserver
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

[Cummulative] does not work

-- Take all year but current year

Sum

(

PeriodsToDate

(

[Time].[FiscalYear].[Year],

Ancestor([Time].[FiscalYear].CurrentMember, [Time].[FiscalYear].Year).PrevMember

)

, [Measures].[PTD Actual])

+

-- Take current YTD

SUM(Ytd([Time].[FiscalYear].CurrentMember), [Measures].[PTD Actual])

-

Above MDX is trying get a Cummulative number up to current time period. The time.FiscalYear was defined with Y, Q and M levels. I tried with an (All) level and w/o (All) level. But PeriodsToDate just does not go beyond pervious year to get all the Periods (here all the periods are all the years up to last year). So when I look at Y2007 cummulative number, I only see it adds up all the number in Y2006 and whatever periods up current in Y2007. Anything before 2006 was not included.

Is there a correct way to do cummulative actual (add up all the previous years plus all the period up to CurrentMember in current year)?

Try this:

SUM({NULL:[Time].[FiscalYear].CurrentMember}, [Measures].[PTD Actual])

|||

Hi, Deepak:

Could you explain a bit what the purpose of NULL in the function?

Thanks!

Julius

|||

Sorry, Deepak.

I am on AS 2000. It gives me syntax error on NULL.

Could you help?

Thanks again!

Julius

|||

The "NULL:" won't work in AS 2000, so (assuming that there is an "All" level) you could try:

Sum

(

PeriodsToDate

(

[Time].[FiscalYear].[All]

)

, [Measures].[PTD Actual])

|||

Deepak:

Interesting that AS2000 took this MDX.

But it seems the set is not the calc member is meant to be. By using [All] w/o defining a CurrentMember, it seems return the ENTIRE time dimension which includes the future period.

If we have 2003, 2004, 2005, 2006, 2007, 2008 as Yearl level members, how do I ask MDX to give the "Cumulative up to date" figure up to 2006 (including 2003, 2004, 2005, 2006) w/o anything from 2007 and 2008? I think (All) will just return entire dimension member set, right? Or in other words, how do I tell MDX that I only want to PeriodsToDate up to my current Year level member (or Previous Year level member, so I can add YTD accurately for current year) when using (All)?

I am confused.

Thanks much!

Julius

|||Depends on how you define your current date - if it's based on the latest date with data in the cube, then previous year (2006) could be computed like Tail(NonEmptyCrossJoin([Time].[Year].Members)).Item(0).PrevMember. Without explicitly selecting a CurrentMember, the DefaultMember will be used instead. So you could set the Time dimension's DefaultMember to the above expression, so that the year 2006 is selected by default.