Hi all,

I'm trying to retrieve the current date in my MDX queries using VBA!Now(),
like everybody else does in tons of other posts. Unfortunately, as I found
out, there are some VBA functions (like VBA!Right, VBA!Year or VBA!Month)
that do work without Excel installed on the server, but VBA!Now and
VBA!Today don't exist, when you don't have Excel. Do I have to write my
first Analysis Services Stored Procedure to solve this simple problem?
By the way, I am using Analysis Services 2005 without any service packs.

Thanks everybody,

Markus.

VBA (including the Now function) is available without the need for Excel.

Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Markus Raatz" <mraatz_doesnt_want_spam (AT) ixto (DOT) de> wrote

OK, so maybe Excel is not the problem, but when I execute this on an AS 2000
server with Excel installed, on good old Foodmart:

With Member Measures.MyYear AS 'VBA!TRIM(VBA!STR(VBA!YEAR(VBA!NOW())))'
select
{[Measures].[MyYear]} on columns
from Sales

....it returned the current year. On AS 2005 9.00.1399.06, no Excel, and on
AdventureWorks, when I execute

WITH MEMBER Measures.VBAYear AS VBA!TRIM(VBA!STR(VBA!YEAR(VBA!NOW())))

SELECT Measures.VBAYear ON COLUMNS

FROM [Adventure Works]

....it just complains that the NOW-function doesn't exist, excuse my german:
Query (1, 59) Die [VBA].[NOW]-Funktion ist nicht vorhanden.

I've tested that on another 9.00.1399.06 with Excel installed: works just
fine. Do I have to re-register some VBA dll that got lost on the way?

Any hints about this behavior are greatly appreciated!!

Markus.

"Akshai Mirchandani [MS]" <akshaim (AT) online (DOT) microsoft.com> schrieb im
Newsbeitrag news:ulzvDWSWGHA.1900 (AT) TK2MSFTNGP04 (DOT) phx.gbl...

Did you do something special on this machine? For example, move the data
folder or something like that?

That could have messed up the VBA system assembly, but I can't think of too
many other possibilities.

Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Markus Raatz" <mraatz_doesnt_want_spam (AT) ixto (DOT) de> wrote

I didn't do anything (that's what they all say), but actually it is a pretty
simple installation, it used to be a CTP verson of SQL 2005 but was removed
from the computer and reinstalled with the final product, all databases were
reattached and the cubes re-deployed from their original solutions.

Actually, I managed to find another machine with 9.00.2029.00 without Excel
installed on the server, where the statement did work, but got feedback from
yet another machine with the final version, no service packs, no excel,
where it also didn't work.

But let's be positive: Does anybody have an idea how to repair the VBA
system assembly?

Thank you very much so far,


Markus.


"Akshai Mirchandani [MS]" <akshaim (AT) online (DOT) microsoft.com> schrieb im
Newsbeitrag news:uRYT69aWGHA.5096 (AT) TK2MSFTNGP03 (DOT) phx.gbl...

Now I feel extremely foolish -- I just remembered and verified that there
was a bug in RTM with the Now() function. What is worse is that I filed this
bug

SP1 fixes this problem (which is why your machine with 2029 works)....
Unfortunately the only workaround in RTM would be to create your own
assembly.

Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Markus Raatz" <mraatz_doesnt_want_spam (AT) ixto (DOT) de> wrote

*ROFL*

Nice solution to a strange-looking problem, indeed!

OK, so I'm going to give it a try with the SP1 CTP!

Thank you very much for your help, Akshai!

Markus.

"Akshai Mirchandani [MS]" <akshaim (AT) online (DOT) microsoft.com> schrieb im
Newsbeitrag news:eaMVBmnWGHA.3440 (AT) TK2MSFTNGP02 (DOT) phx.gbl...

Hi Akshai, hi all,

I hope someone is still reading this, because now it gets really weird...

I upgraded this server to 9.00.2040, still looking for VBA!Now(). And I got
it! But: now VBA!Year() is gone (used to work before the upgrade), and also
VBA!Str(). Because there's also no VBA!Datepart, there is no reliable way to
extract the year from VBA!Now(). I can use VBA!Mid(VBA!cStr(VBA!Now()),7,4),
but that only works with a german date format. Could you check if this is
expected behaviour? Is there a list in BOL (I'm currently downloading BOL
March 2006 CTP) that shows all the VBA functions that should work?

Really confused by now:

Markus.

"Markus Raatz" <mraatz_doesnt_want_spam (AT) ixto (DOT) de> schrieb im Newsbeitrag
news:%23ncVnJIXGHA.3684 (AT) TK2MSFTNGP05 (DOT) phx.gbl...

Now it sounds like this issue:

Some of the VBA functions (including the ones that work for you below) are
implemented natively by the server. Others are implemented using the VBA.NET
assembly -- if something went wrong with this assembly then the native VBA
functions would work, but the others would not.

A couple of possibilities:
1. Open this file "<datafolder>\VBAMDX.0.asm.xml" and check that the file
references the correct path to the msmdvbanet.dll.
2. Perhaps that assembly is not signed for you for some reason (I don't know
where you got the build from). In that case, .NET will not load the assembly
without you ignoring the strong name requirement. You may find a log entry
somewhere that indicates this is the cause for the failure.

I would suggest trying the final SP1 build...

Thanks,
Akshai
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/Sho...ID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Markus Raatz" <mraatz_doesnt_want_spam (AT) ixto (DOT) de> wrote

Hi Akshai, Hi everybody,

I'm always amazed by the wealth of information that I see in these
newsgroups! Akshai: that was it, solved my problem 100%. My AS-Server has a
"split installation": program folder on C:\, data folder on D:\. and looking
up in <datafolder>\VBAMDX.0.asm.xml I found out that in fact it pointed to
D:\Program Files\Microsoft SQL Server\MSSQL.2\bin\msmdvbanet.dll, while the
..dll was in C:\Program Files\Microsoft SQL
Server\MSSQL.2\bin\msmdvbanet.dll. I edited the .xml-file, restarted the
AS-Service and all VBA functions were back! The version of the .dll was
9.0.2040.0, so it was obviously replaced by SP 1 CTP installation, but the
reference to it in VBAMDX.0.asm.xml was not modified.
I hope this doesn't happen to many more people out there, but if it does,
I'm sure they'll be glad when they read this thread.

Thank you very much again for your support, Akshai,

Markus.

"Akshai Mirchandani [MS]" <akshaim (AT) online (DOT) microsoft.com> schrieb im
Newsbeitrag news:ulPCIIzXGHA.3840 (AT) TK2MSFTNGP02 (DOT) phx.gbl...