Creating an MS SQL data table programatically

I use Visual Studio and VB.
If I want to create an SQL data table on the server, I go to ServerExplorer and make a small table with the corerct columns. Then I addrows programatically as needed from the Application I'm working with.
It would suit me, for a current job, to be able to create the table itself programatically.
I don't know how to do this. Can it be done ? If so, could someone giveme a starter, please ? Four cols with a key in the first.
David Morley
SQL understands both Data Manipulation Language (DML) and Data Definition Language (DDL). The DML is the stuff you use to change the values in the table whereas the DDL allows you to change the structure of your database. Look up 'CREATE TABLE' and start there. PS, I'd avoid ADOX and the like unless you're making a cross database product...even then I'd probably stay clear.

creating an INSTANCE from an existing INSTANCE

Is it possible , without using the installation CD , to create a new
INSTANCE of SQL Server 2005 from an existing INSTANCE on the same server?
--
___________________________________
Need an IT job? http://www.ITjobfeed.comNo. (Sorry.)
RLF
"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:7d-dnT4Z_5Cy-0DbnZ2dnUVZ8v6dnZ2d@.bt.com...
> Is it possible , without using the installation CD , to create a new
> INSTANCE of SQL Server 2005 from an existing INSTANCE on the same server?
>
> --
>
> ___________________________________
> Need an IT job? http://www.ITjobfeed.com
>
>
>|||No Jack. You must start setup from CD\DVD again and choose another name for
your new instance. It's totally a new installation except for some common
services (SSIS etc.)
--
Ekrem Önsoy
"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:7d-dnT4Z_5Cy-0DbnZ2dnUVZ8v6dnZ2d@.bt.com...
> Is it possible , without using the installation CD , to create a new
> INSTANCE of SQL Server 2005 from an existing INSTANCE on the same server?
>
> --
>
> ___________________________________
> Need an IT job? http://www.ITjobfeed.com
>
>
>

Creating an installer for SQL-side components of an app

Hello.
I'm researching about the ways I can create an installer of the SQL (2000)
objects for an application.
At the present time we're using a set of calls to osql utility from a VB
application. But the maintenance of the scripts is becoming cumbersome.
Does MS provides utilities for this? Where can I look up for further info?
Thanks.-
| Thread-Topic: Creating an installer for SQL-side components of an app
| thread-index: AcThIuI4MycuAx2CSYGbOG6c0kGPaQ==
| X-WBNR-Posting-Host: 200.44.173.82
| From: =?Utf-8?B?cnBhbGxhcmVz?= <rpallares@.discussions.microsoft.com>
| Subject: Creating an installer for SQL-side components of an app
| Date: Mon, 13 Dec 2004 06:49:01 -0800
| Lines: 12
| Message-ID: <CF1D14A9-81F6-4891-BCCD-4D8299366B7D@.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.clients
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.clients:29256
| X-Tomcat-NG: microsoft.public.sqlserver.clients
|
| Hello.
|
| I'm researching about the ways I can create an installer of the SQL
(2000)
| objects for an application.
|
| At the present time we're using a set of calls to osql utility from a VB
| application. But the maintenance of the scripts is becoming cumbersome.
|
| Does MS provides utilities for this? Where can I look up for further info?
|
| Thanks.-
|
|
<><><><><><><><><><><><><><><><><><><><><><><><><> <><><>
Hi,
If you still need assistance and you're using MSDE then I believe you will
find this link useful:
http://msdn.microsoft.com/library/de...us/dnmsde/html
/msdedepl.asp
Regards,
Yasemin Gunduz
Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
|||What u mean with the SQL server Objects ?
"rpallares" <rpallares@.discussions.microsoft.com> wrote in message
news:CF1D14A9-81F6-4891-BCCD-4D8299366B7D@.microsoft.com...
> Hello.
> I'm researching about the ways I can create an installer of the SQL (2000)
> objects for an application.
> At the present time we're using a set of calls to osql utility from a VB
> application. But the maintenance of the scripts is becoming cumbersome.
> Does MS provides utilities for this? Where can I look up for further info?
> Thanks.-
>

Creating an INSERT UPDATE Trigger that works on certain account

Hi folks,
I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
to another proprietary database server. The other database server will
occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated by
the proprietary database account. How do I capture the user or group name in
MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
What is the equivalent in MS SQL? Thanks in advance!!!just use user_name()
--
Venkat
sql server admirer
"Jaime Rios" wrote:
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated by
> the proprietary database account. How do I capture the user or group name in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
> What is the equivalent in MS SQL? Thanks in advance!!!|||This is a multi-part message in MIME format.
--=_NextPart_000_0EFB_01C6952D.3CD86780
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Try the sytem function: system_user.
For example, SELECT system_user will provide the Domain\Loginname of the =currently logged in user account.
Look in Books-on-Line for more information on system_user.
-- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message =news:EB859EAF-3AC8-4CDC-8ECF-89A3038CC99A@.microsoft.com...
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL =2000 > to another proprietary database server. The other database server will =
> occassionally push data back into MS SQL via JDBC. I don't want the MS =SQL > trigger to execute its SQL code if the INSERT UPDATE Trigger was =initiated by > the proprietary database account. How do I capture the user or group =name in > MS SQL? I know in PostgreSQL, you can use the "user" variable in a =trigger. > What is the equivalent in MS SQL? Thanks in advance!!!
--=_NextPart_000_0EFB_01C6952D.3CD86780
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try the sytem function: system_user. =
For example, SELECT system_user will provide the =Domain\Loginname of the currently logged in user account.
Look in Books-on-Line for more =information on system_user.
-- Arnie Rowland, YACE* "To be =successful, your heart must accompany your knowledge."
*Yet Another Certification =Exam
"Jaime Rios" wrote in message news:EB859EAF-3AC8-4CDC-8ECF-89A3038CC99A@.microsoft.com...> =Hi folks,> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL =2000 > to another proprietary database server. The other database =server will > occassionally push data back into MS SQL via JDBC. I don't want =the MS SQL > trigger to execute its SQL code if the INSERT UPDATE =Trigger was initiated by > the proprietary database account. How do I capture =the user or group name in > MS SQL? I know in PostgreSQL, you can use =the "user" variable in a trigger. > What is the equivalent in MS SQL? =Thanks in advance!!!

---

--=_NextPart_000_0EFB_01C6952D.3CD86780--|||A problem with using user_name() is that it will return 'dbo' for any user
that is in the dbOwner or SA roles. That may not be as helpful as
system_user. (no parens after system_user).
System_User provides the complete loggin DOMAIN\username. It is the best
option for any form of auditing and perhaps for the purpose you have in
mind.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:3F000F3F-7605-4D1E-BCD1-B9452F532943@.microsoft.com...
> just use user_name()
> --
> Venkat
> sql server admirer
>
> "Jaime Rios" wrote:
>> Hi folks,
>> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL
>> 2000
>> to another proprietary database server. The other database server will
>> occassionally push data back into MS SQL via JDBC. I don't want the MS
>> SQL
>> trigger to execute its SQL code if the INSERT UPDATE Trigger was
>> initiated by
>> the proprietary database account. How do I capture the user or group name
>> in
>> MS SQL? I know in PostgreSQL, you can use the "user" variable in a
>> trigger.
>> What is the equivalent in MS SQL? Thanks in advance!!!|||I concur with Arnie.
--
Venkat
sql server admirer
"Arnie Rowland" wrote:
> Try the sytem function: system_user.
> For example, SELECT system_user will provide the Domain\Loginname of the currently logged in user account.
> Look in Books-on-Line for more information on system_user.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message news:EB859EAF-3AC8-4CDC-8ECF-89A3038CC99A@.microsoft.com...
> > Hi folks,
> > I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
> > to another proprietary database server. The other database server will
> > occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> > trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated by
> > the proprietary database account. How do I capture the user or group name in
> > MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
> > What is the equivalent in MS SQL? Thanks in advance!!!

Creating an INSERT UPDATE Trigger that works on certain account

just use user_name()
--
Venkat
sql server admirer
"Jaime Rios" wrote:

> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!Try the sytem function: system_user.
For example, SELECT system_user will provide the Domain\Loginname of the cur
rently logged in user account.
Look in Books-on-Line for more information on system_user.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message news:EB859EAF-3AC8-4CDC-
8ECF-89A3038CC99A@.microsoft.com...
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||A problem with using user_name() is that it will return 'dbo' for any user
that is in the dbOwner or SA roles. That may not be as helpful as
system_user. (no parens after system_user).
System_User provides the complete loggin DOMAIN\username. It is the best
option for any form of auditing and perhaps for the purpose you have in
mind.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:3F000F3F-7605-4D1E-BCD1-B9452F532943@.microsoft.com...[vbcol=seagreen]
> just use user_name()
> --
> Venkat
> sql server admirer
>
> "Jaime Rios" wrote:
>|||Hi folks,
I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
to another proprietary database server. The other database server will
occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated b
y
the proprietary database account. How do I capture the user or group name in
MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
What is the equivalent in MS SQL? Thanks in advance!!!|||just use user_name()
--
Venkat
sql server admirer
"Jaime Rios" wrote:

> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||Try the sytem function: system_user.
For example, SELECT system_user will provide the Domain\Loginname of the cur
rently logged in user account.
Look in Books-on-Line for more information on system_user.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message news:EB859EAF-3AC8-4CDC-
8ECF-89A3038CC99A@.microsoft.com...
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||A problem with using user_name() is that it will return 'dbo' for any user
that is in the dbOwner or SA roles. That may not be as helpful as
system_user. (no parens after system_user).
System_User provides the complete loggin DOMAIN\username. It is the best
option for any form of auditing and perhaps for the purpose you have in
mind.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:3F000F3F-7605-4D1E-BCD1-B9452F532943@.microsoft.com...[vbcol=seagreen]
> just use user_name()
> --
> Venkat
> sql server admirer
>
> "Jaime Rios" wrote:
>sql

Creating an Indexed View

I am trying to create an indexed view, on a date from a date dimension table...I am new to SQL, and I am at a loss of ideas on this one. Any help would be greatly appreciated!

Here is the Error I am given

"Msg 4513, Level 16, State 2, Procedure VEW_F_MZT_ORDER_HEADER_DAY, Line 3

Cannot schema bind view 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'. 'JJWHSE.VEW_F_INVC_SHIP_TO' is not schema bound.

Msg 1939, Level 16, State 1, Line 1

Cannot create index on view 'VEW_F_MZT_ORDER_HEADER' because the view is not schema bound."

Here is my code..

CREATE VIEW [JJWHSE].[VEW_F_MZT_ORDER_HEADER_DAY] WITH SCHEMABINDING

AS

SELECT TEW_D_DT.DT_KEY AS DATE_KEY,

VEW_F_MZT_ORDER_HEADER.LOCATION_KEY AS LOC_KEY,

TEW_D_LOC.LOC_DESC AS LOC_DESC ,

TEW_D_LOC.RGN_DESC AS REGION_DESC,

TEW_D_LOC.DISTRICT_DESC AS DISTRICT_DESC,

ISNULL(SUM(VEW_F_INVC_PAY_EXT.PRORATED_NET_PRICE),0) AS CONCIERGE_FLASH,

COUNT_BIG(*) AS COUNT

FROM

JJWHSE.VEW_F_INVC_SHIP_TO VEW_F_INVC_SHIP_TO

INNER JOIN

JJWHSE.VEW_F_INVC_PAY_EXT VEW_F_INVC_PAY_EXT

ON

VEW_F_INVC_SHIP_TO.DATE_KEY = VEW_F_INVC_PAY_EXT.DATE_KEY

AND VEW_F_INVC_SHIP_TO.ORDER_NUMBER = VEW_F_INVC_PAY_EXT.ORDER_NUMBER

AND VEW_F_INVC_SHIP_TO.INVOICE_NUMBER = VEW_F_INVC_PAY_EXT.INVOICE_NUMBER

AND VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER = VEW_F_INVC_PAY_EXT.SHIP_TO_NUMBER

INNER JOIN

JJWHSE.VEW_F_INVC_DTL VEW_F_INVC_DTL

ON

VEW_F_INVC_DTL.DATE_KEY = VEW_F_INVC_SHIP_TO.DATE_KEY

AND VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_INVC_SHIP_TO.ORDER_NUMBER

AND VEW_F_INVC_DTL.INVOICE_NUMBER = VEW_F_INVC_SHIP_TO.INVOICE_NUMBER

AND VEW_F_INVC_DTL.SHIP_TO_NUMBER = VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER

AND VEW_F_INVC_DTL.LINE_NUMBER = VEW_F_INVC_PAY_EXT.LINE_NUMBER

AND VEW_F_INVC_DTL.SEQUENCE_NUMBER = VEW_F_INVC_PAY_EXT.SEQUENCE_NUMBER

AND VEW_F_INVC_DTL.NON_INVENTORY = 'N'

AND VEW_F_INVC_DTL.GIFT_CARD = 'N'

INNER JOIN

JJWHSE.VEW_F_MZT_ORDER_HEADER VEW_F_MZT_ORDER_HEADER

ON

VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_MZT_ORDER_HEADER.ORDER_NUMBER

AND VEW_F_MZT_ORDER_HEADER.ACTIVE_FLAG = 1

INNER JOIN

JJWHSE.TEW_D_DT TEW_D_DT

ON

VEW_F_INVC_DTL.DATE_KEY = TEW_D_DT.DT_KEY

INNER JOIN

JJWHSE.TEW_D_LOC TEW_D_LOC

ON

VEW_F_MZT_ORDER_HEADER.LOCATION_KEY = TEW_D_LOC.LOC_KEY

WHERE VEW_F_INVC_SHIP_TO.CHANNEL = 'I'

GROUP BY TEW_D_DT.DT_KEY , VEW_F_MZT_ORDER_HEADER.LOCATION_KEY , TEW_D_LOC.LOC_DESC ,

TEW_D_LOC.RGN_DESC , TEW_D_LOC.DISTRICT_DESC

GO

CREATE UNIQUE CLUSTERED INDEX IX_VEW_F_MZT_ORDER_HEADE_DAY ON JJWHSE.VEW_F_MZT_ORDER_HEADER ( DATE_KEY )

The first error message is as direct as it can be. You cannot

create a view WITH SCHEMABINDING when its definition mentions

another view that was not itself created WITH SCHEMABINDING.

In your case, you can't create 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'

with schemabinding because it refers to another view,

'JJWHSE.VEW_F_INVC_SHIP_TO', which was not created with

schemabinding.

There does seem to be a bit of confusion. The code you pasted

here tries to do two things:

1. Create a view named 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'

2. Create an index on 'VEW_F_MZT_ORDER_HEADER',

which is a *different* view.

The second error you got (Cannot create index...)

has nothing at all to do with the first error or with

the CREATE VIEW code that generates the first error. It's

also as clear as it can be. The 'VEW_F_MZT_ORDER_HEADER'

exists, but it was not created with schemabinding, which

is a requirement for creating an index on a view.

If you want to index a view, that view and all the views

(and functions) on which it depends must be created with

schemabinding and meet the requirements for that option.

Steve Kass

Drew University

http://www.stevekass.com
topcoder_cc@.discussions.microsoft.com wrote:

> I am trying to create an indexed view, on a date from a date dimension

> table...I am new to SQL, and I am at a loss of ideas on this one. Any

> help would be greatly appreciated!

>

> Here is the Error I am given

>

> "Msg 4513, Level 16, State 2, Procedure VEW_F_MZT_ORDER_HEADER_DAY, Line

> 3

>

> Cannot schema bind view 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'.

> 'JJWHSE.VEW_F_INVC_SHIP_TO' is not schema bound.

>

> Msg 1939, Level 16, State 1, Line 1

>

> Cannot create index on view 'VEW_F_MZT_ORDER_HEADER' because the view is

> not schema bound."

>

> Here is my code..

>

> CREATE VIEW [JJWHSE].[VEW_F_MZT_ORDER_HEADER_DAY] WITH SCHEMABINDING

>

> AS

>

> SELECT TEW_D_DT.DT_KEY AS DATE_KEY,

>

> VEW_F_MZT_ORDER_HEADER.LOCATION_KEY AS LOC_KEY,

>

> TEW_D_LOC.LOC_DESC AS LOC_DESC ,

>

> TEW_D_LOC.RGN_DESC AS REGION_DESC,

>

> TEW_D_LOC.DISTRICT_DESC AS DISTRICT_DESC,

>

> ISNULL(SUM(VEW_F_INVC_PAY_EXT.PRORATED_NET_PRICE),0) AS CONCIERGE_FLASH,

>

> COUNT_BIG(*) AS COUNT

>

> FROM

>

> JJWHSE.VEW_F_INVC_SHIP_TO VEW_F_INVC_SHIP_TO

>

> INNER JOIN

>

> JJWHSE.VEW_F_INVC_PAY_EXT VEW_F_INVC_PAY_EXT

>

> ON

>

> VEW_F_INVC_SHIP_TO.DATE_KEY = VEW_F_INVC_PAY_EXT.DATE_KEY

>

> AND VEW_F_INVC_SHIP_TO.ORDER_NUMBER = VEW_F_INVC_PAY_EXT.ORDER_NUMBER

>

> AND VEW_F_INVC_SHIP_TO.INVOICE_NUMBER =

> VEW_F_INVC_PAY_EXT.INVOICE_NUMBER

>

> AND VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER =

> VEW_F_INVC_PAY_EXT.SHIP_TO_NUMBER

>

> INNER JOIN

>

> JJWHSE.VEW_F_INVC_DTL VEW_F_INVC_DTL

>

> ON

>

> VEW_F_INVC_DTL.DATE_KEY = VEW_F_INVC_SHIP_TO.DATE_KEY

>

> AND VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_INVC_SHIP_TO.ORDER_NUMBER

>

> AND VEW_F_INVC_DTL.INVOICE_NUMBER = VEW_F_INVC_SHIP_TO.INVOICE_NUMBER

>

> AND VEW_F_INVC_DTL.SHIP_TO_NUMBER = VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER

>

> AND VEW_F_INVC_DTL.LINE_NUMBER = VEW_F_INVC_PAY_EXT.LINE_NUMBER

>

> AND VEW_F_INVC_DTL.SEQUENCE_NUMBER = VEW_F_INVC_PAY_EXT.SEQUENCE_NUMBER

>

> AND VEW_F_INVC_DTL.NON_INVENTORY = 'N'

>

> AND VEW_F_INVC_DTL.GIFT_CARD = 'N'

>

> INNER JOIN

>

> JJWHSE.VEW_F_MZT_ORDER_HEADER VEW_F_MZT_ORDER_HEADER

>

> ON

>

> VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_MZT_ORDER_HEADER.ORDER_NUMBER

>

> AND VEW_F_MZT_ORDER_HEADER.ACTIVE_FLAG = 1

>

> INNER JOIN

>

> JJWHSE.TEW_D_DT TEW_D_DT

>

> ON

>

> VEW_F_INVC_DTL.DATE_KEY = TEW_D_DT.DT_KEY

>

> INNER JOIN

>

> JJWHSE.TEW_D_LOC TEW_D_LOC

>

> ON

>

> VEW_F_MZT_ORDER_HEADER.LOCATION_KEY = TEW_D_LOC.LOC_KEY

>

> WHERE VEW_F_INVC_SHIP_TO.CHANNEL = 'I'

>

> GROUP BY TEW_D_DT.DT_KEY , VEW_F_MZT_ORDER_HEADER.LOCATION_KEY ,

> TEW_D_LOC.LOC_DESC ,

>

> TEW_D_LOC.RGN_DESC , TEW_D_LOC.DISTRICT_DESC

>

> GO

>

> CREATE UNIQUE CLUSTERED INDEX IX_VEW_F_MZT_ORDER_HEADE_DAY ON

> JJWHSE.VEW_F_MZT_ORDER_HEADER ( DATE_KEY )

>

>

Creating an Index with a calulated member in MDX ?

Anyone got a clue on how to create a meassure that shows the Actual meassure as an Index, where current month is index 100 ?

Ex:

Jan07: Actual = 32000 -> index = 114

Feb07: Actual = 34000 -> index = 121

Mar07: Actual = 28000 -> index = 100

Apr07: Actual = 20000 -> index = 71

It must be something with creating af defaultmember in the timedimension, that is dynamic and points to getdate(). Then make a calculation that uses defaultmembers value to calculate the indexnumber|||

If you wanted to use the current system date on the server as your definition of the "current date" then you could do something roughly like the following:

Code Snippet

(

([Date].[Month].CurrentMember, [Measures].[Actual])

/ (StrToMember("[Date].[Month].[" + FORMAT(NOW(),"MMMyy") + "]"),[Measures].[Actual])

) * 100

This code is pretty rough, there is no logic in there for handling the All member and it would only work at month granularity, but hopefully it is enough to get you started. You could also set the "current date" as the default member, but it is not strictly necessary to do the calculation.