Asking

At Ms Access, I can export data to text or excel with only to write:
SELECT * INTO TD in 'C:' [Text;]
From TS
Now, Can SQL Server 2000 do like this? IF can, How is its syntax?Bpk,
Here is an example to work with:
EXEC master..xp_cmdshell 'osql -E -dPubs -Q"select * from
authors" -o"C:\authors.txt"'
You can also use BCP (out) and DTS.
HTH
Jerry
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:ulzUGe2xFHA.2652@.TK2MSFTNGP14.phx.gbl...
> At Ms Access, I can export data to text or excel with only to write:
> SELECT * INTO TD in 'C:' [Text;]
> From TS
> Now, Can SQL Server 2000 do like this? IF can, How is its syntax?
>|||This will work for the publisher table in the Pubs DB
exec master..xp_cmdshell 'bcp pubs.dbo.publishers out "c:\files.txt" -S
{YourServerNameHere} -T -t "," -c'
or just from the command line bcp pubs.dbo.publishers out "c:\files.txt" -S
{YourServerNameHere} -T -t "," -c
http://sqlservercode.blogspot.com/
"Bpk. Adi Wira Kusuma" wrote:

> At Ms Access, I can export data to text or excel with only to write:
> SELECT * INTO TD in 'C:' [Text;]
> From TS
> Now, Can SQL Server 2000 do like this? IF can, How is its syntax?
>
>

ask user to select excel

Dear friends, can anyone please tell me that how at the time when user executes the package i can ask a user to choose the datasource excel sheet and what are the constraints i have to take care of in the integration package desing.

thanks,

If you'd like to ask user something, then, I'm afraid, you shoud write your own client application, cause SQL Server does provide nothing to bild any interfaces.
|||

dear Sergey Gavrilenko,

is there any way that before running the integration service package i can ask the user to enter the file path of excel that he wants to use as the souce file.

if not easy way then please tell me the only possible way, even if i have to use visual studio .net to get the same.

thanks,

AS2005 Member Properties - MDX Query

Hi,

I am having a bit of a problem with retreiving member properties from our AS2005 cube. I'm using MDX query's in a VBA Excel app via ADO (not ADOMD). I've been prototyping the queries in the SQL Server Management Studio.

I currently have the following query:

WITH

MEMBER [Measures].[Portfolio] AS '[Trade].[Trade By Source System].CurrentMember.Properties("Portfolio")'

SELECT NON EMPTY {

[Measures].[Portfolio],

[Measures].[CR 01 Net Skw Adj USD],

[Measures].[JTD 01 USD]

} ON COLUMNS,

NonEmpty(Exists({[Trade].[Trade By Source System].[Trade Id].MEMBERS},[Trade].[Trade By Source System].[Source System].&[Calypso]))

ON ROWS

FROM [GCD]

WHERE ([Close Date].[Close Date].[Day].[01 Dec 2006], [Trade].[Trade By Business Unit].[Book Group].[LNCT])

This works fine, however I now want to CrossJoin the measures [CR 01 Net Skw Adj USD] and [JTD 01 USD] with a dimension called CS Tenor in order to give me a seperate value for each Tenor.

So far the only way I can do this is with the following query:

WITH

MEMBER [Measures].[Portfolio] AS '[Trade].[Trade By Source System].CurrentMember.Properties("Portfolio")'

SELECT NON EMPTY {

[Measures].[Portfolio],

[Measures].[CR 01 Net Skw Adj USD],

[Measures].[JTD 01 USD]

} * {[CSTenor].[CS Tenor].MEMBERS}

ON COLUMNS,

NonEmpty(Exists({[Trade].[Trade By Source System].[Trade Id].MEMBERS},[Trade].[Trade By Source System].[Source System].&[Calypso]))

ON ROWS

FROM [GCD]

WHERE ([Close Date].[Close Date].[Day].[01 Dec 2006], [Trade].[Trade By Business Unit].[Book Group].[LNCT])

This has the unfortunate side effect of CrossJoining the Portfolio member property with Tenor as well, resulting in 9 unwanted Portfolio columns (there are 9 members in CS Tenor). In reality the problem is more severe as I have around 10 member properties in the query, I reduced it to 1 here for simplicity.

My question is: Can I bring back member properties on the Rows axis? and a seperate question is: Can I CrossJoin some measures and not other on the columns axis, for example, could I CrossJoin [CR 01 Net Skw Adj USD] with CS Tenor, but not [JTD 01 USD] ?

Many thanks!

I don't believe you can cross join some measures and not other on the column axis but assuminng portfolio is an attribute of your Trade dimension the you can try this on your rows axis:

NonEmpty(Exists({[Trade].[Trade By Source System].[Trade Id].MEMBERS},[Trade].[Trade By Source System].[Source System].&[Calypso],strtomember("[Trade].[Portfolio].[" + [Trade].[Trade By Source System].CurrentMember.Properties("Portfolio") + "]") ))

Hope this helps.

V

|||

Actually, you should be able to do what you want on the column axis by simply constructing the full set of tuples that you want back. Here's an example from Adventure Works:

select

{ [Date].[Calendar Year].Members } on rows,

{ { [Measures].[Internet Sales Amount] } * { [Product].[Category].Members }, ( [Measures].[Internet Gross Profit], [Product].[Category].[All Products] ) } on columns

from

[Adventure Works]

In this query, I'm just pulling back calendar years on rows. On the columns, I'm pulling back the [Internet Sales Amount] measure cross joined with the product categories along with the [Internet Gross Profit] measure -- but with it coming back only with [All Products], not all the product cateogries. The only caveat is that all the tuples returned have to have the same dimensionality. Thus the need to return [Internet Gross Profit] with a member (in this case, the [All Products] member) of the same hierarchy used in the cross join that generates the first set of tuples.

In your case, you'd want something like this (not knowing exactly what your [All] member is, I just called it [All CS Tenors]):

{ { [Measures].[CR 01 Net Skw Adj USD] * [CSTenor].[CS Tenor].Members }, ( [Measures].[JTD 01 USD], [CSTenor].[CS Tenor].[All CS Tenors] ) } on columns

HTH,

Dave Fackler

|||

Hi,

Sorry for the delay in replying, I've been on vaction.

Many many thanks for this solution, it works perfectly!

Happy New Year,

Stuart

AS2005 Cube Excel 2007 Filtering Issue

When browsing our AS2005 Cube we only have one filtering option. Top 10... under the Value Filters menu item. All other filtering options are disabled. I can create a pivot table manually from data in an excel worksheet and the filtering options work fine. Which leads me to believe it is something wrong with our cube design / metadata? Any ideas for what we might need to have the filtering options enabled.Have you installed SP2 on the AS server ? Some features of Excel 2007 are only enabled against AS SP2.|||No it doesn't, I have tested against my local copy of sql server which does have sp2 and it resolved the issue. Thanks for the help.|||

Hello Mosha. It would be interesting to know about the features in Excel 2007 that are disabled before SSAS2005 SP2?

Is this something that you have mentioned in your Blog(multiselect) or something else?

Do you mean internal features like the MDX being generated from Excel2007?

Are these new features being enabled by an update on the client?

Kind Regards

Thomas Ivarsson

|||

> It would be interesting to know about the features in Excel 2007 that are disabled before SSAS2005 SP2?

I agree that it would be interesting to know. This question belongs to the Excel forum, however. Also, in couple of months when everybody has upgraded to SP2, this will become a moot point.

> Are these new features being enabled by an update on the client?

I beleive it's enough to upgrade AS server only.

|||

From what you are saying I think the new features will be in an Office 2007 SP1, not in SSAS2005 SP2, even if SSAS2005 will provide the basis for that.

Writeback in Excel 2007 will be on my whish list.

I think we have a problem of where to post Excel 2007 and ProClarity questions regarding SSAS2005.

Regards

Thomas Ivarsson

|||

For Excel 2007 questions, the following forum seems to be appropriate: http://forums.microsoft.com/technet/showforum.aspx?forumid=544&siteid=17

AS2005 and Excel

Hi all

i am trying to connect to a AS2005 cube from excel.

On my computer (Excel2003+sp1) i am able to see and broswe the cube.On the user's Pc (excel2003+sp1) I istalled the msolap9.dll but i got this error when i try to connect to the OLAPserver:

"Unable to connect to data source.Reason: Unable to locate database server"

Both me and the user are in the Cube's Role Membership.....

Any idea?

Thx in advance

Are you using this addin or not?: http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en

The message "Unable to connect to data source.Reason: Unable to locate database server".,can mean that the service is down or you can have a firewall problem.

Many causes. Can you supply more details?

Regards

Thomas Ivarsson

|||

Thank you so much Thomas, that is was i forgot to install....

thx

AS connection problems from Excel

I have problems to create connections to some of my cubes from Excel.

Configuration

Client- XP professional with Excel (2007, B2TR) and SQL 2005 SP2 TR client part's installed.

Server - Windows 2003 R2 with SQL AS 2005 SP2 TR.

Logged in as administrator with same password on client as server.

There are no rules configured for any of my databases.

When I try to create a new Analysis connection from Excel there is no cubes in the list of available cubes for some of my databases.

It works great for two om my databes but not for the others. I have compared my databases but I can't find anything that is different. And it works great to browse all my cubes from SQl Server BI Development Studio from my client.

Any idea?

Thanks,
Christer

If the cubes are deployed and processed, then you suppose to see it in the Excel 2007 beta 2. Can you be more specific in your question, I might can help as I am using the same configuration you have.

IMRAN

AS 2000, Excel 2003 and calulated measures

Hi

I have created a calculated measure - which when i view in the Analysis Manager - Browse it displays what I need.

In my Excel Pivot Table I cannot see the field when I Show Field List? is it possible to have calc members in Excel 2003?

however, my initial problem is this (and I am hoping someone can come up with a better solution):

I have a fact table that is simply number of hours worked per month and linked to dimensions such as the customer. Each Customer has a budgeted time (which is on the customer dimension) - I need to include this budget.

I have a separate cube that is the detail of the budget and I have tried creating a virtual cube - the problem with this is the budget is only visible if I use dimensions from that cube and the hours are only visible when I use dimensions from that cube - I would liek to see them together. (the dimensions in both cubes are identical)

Anyone?

Steve

Sounds like you have a bunch of private dimensions in your cubes. Its been a while since I worked on 2K, but i think global dimensions (dimensions that can be shared in virtual cubes) are called "shared dimensions". If you used the wizard to create the dimensions and cubes I think it defaults to adding dimensions as private.

|||

That may solve the virtual cube issue - thanks.

Any idea why I cannot see a calculated member in Excel while I can see it in the MMC browser?

Thanks

|||

Shared dimensions and virtual cubes work a treat - thanks.

Apparently, a calculated measure that is based on a dimension table does not appear in Excel 2003!!

Array was not long enough. Check destIndex and length, and the array's lower bounds

When I try to export a report via Excel, I get an error of :

"Array was not long enough. Check destIndex and length, and the array's lower bounds."

Is this because the cell within Excel contains too many characters? There are two detail bands which could contain several pages worth of text.

I can export fine in HTML or PDF.

Thanks!

Hello,

We fix some problems with long text values in excel renderer and a hotfix is avaialble for RS2000 SP2.

Thank you,

Nico

|||

Nicoleta,

We already installed that SP2 since we were missing the printer icon when we first tried running the reports.

Is there a way to actually see which SP is on the server?

I tried looking at the Add / Remove Programs, and didn't see anything.

Thanks!

Are there limitations to OLAP in Excel?

Hi,

Now, I use Analysis Services 2005 and Excel 2003 SP2 to deploy my OLAP model.

In Excel sheet, when creating a pivot table based on a Cube in Analysis Services, everything appears to work until I add a fourth dimension to the pivot table. Once I do that, I get a message that says "Running OLAP Query...(Press Esc to cancel)" so I must wait for a long time (about 5 minutes) to get the result. But while browsing the same data dimensions in Analysis Services Window, everything appears immediately.

Any ideas?

Best Regards!

You might qualify the question to read: "Are there limitations to using AS 2005 cubes in Excel 2003?"

Choosing client for SSAS 2005: Excel 2003 or Excel 2007

By Ramunas Balukonis

After we migrated Analysis Services from 2000 to 2005 we discovered that the same (or analogous) queries against SSAS 2005 runs even slower than AS 2000. Our users used MS Excel 2003 as front end on AS 2005. After some time I installed MS Excel 2007. Then I tried to do the same Pivot Table actions on both Excel versions (2003 and 2007): make Pivot Table from Advenure Works database placing days of [Date].[Calendar] [July 2003] and [August 2003] on rows, [Mountain-200 Silver, 38] and [Mountain-200 Silver, 42] from product [Product].[categories] on columns and [Sales Amount] as facts

Excel 2003 generates cumbersome MDX wits lots of except:

...

Excel 2007 generates elegant MDX:

...

The 1’st query from Excel 2003 runs 5 times longer!

After some time I gathered statistics from profiler and querylog. The results are as follows:

Avg MDX text length (Excel 2003): 2403
Avg MDX text length (Excel 2007): 1567
Avg query duration (Excel 2003): 507 ms
Avg query duration (Excel 2007): 96 ms

|||Thanks Ramunas!
Did you try to use the browser in Analysis Services or the Reporting Services to query your OLAP data . In my opinion, the queried time of using two ways above is better than the time of using Excel pivot table.
Could you explain me the reason why?

|||

Yeah, Excel 2003 is horrible with OLAP. It's the MDX issue, it does tons of EXCEPTS(), etc. Instead of scripting what it wants in the pivottable, it scripts what it doesn't want. Not sure how this ever got passed.

For your last reply, you will be hitting cache if you run from Excel, then run the same query somewhere else. You would have to execute the ClearCache command after every MDX statement for a true test. Or could be a network slowup if your using Excel externally (off the server) and running Management Studio on the server directly, for example.

Are there limitations to OLAP in Excel?

Hi,

Now, I use Analysis Services 2005 and Excel 2003 SP2 to deploy my OLAP model.

In Excel sheet, when creating a pivot table based on a Cube in Analysis Services, everything appears to work until I add a fourth dimension to the pivot table. Once I do that, I get a message that says "Running OLAP Query...(Press Esc to cancel)" so I must wait for a long time (about 5 minutes) to get the result. But while browsing the same data dimensions in Analysis Services Window, everything appears immediately.

Any ideas?

Best Regards!

You might qualify the question to read: "Are there limitations to using AS 2005 cubes in Excel 2003?"

Choosing client for SSAS 2005: Excel 2003 or Excel 2007

By Ramunas Balukonis

After we migrated Analysis Services from 2000 to 2005 we discovered that the same (or analogous) queries against SSAS 2005 runs even slower than AS 2000. Our users used MS Excel 2003 as front end on AS 2005. After some time I installed MS Excel 2007. Then I tried to do the same Pivot Table actions on both Excel versions (2003 and 2007): make Pivot Table from Advenure Works database placing days of [Date].[Calendar] [July 2003] and [August 2003] on rows, [Mountain-200 Silver, 38] and [Mountain-200 Silver, 42] from product [Product].[categories] on columns and [Sales Amount] as facts

Excel 2003 generates cumbersome MDX wits lots of except:

...

Excel 2007 generates elegant MDX:

...

The 1’st query from Excel 2003 runs 5 times longer!

After some time I gathered statistics from profiler and querylog. The results are as follows:

Avg MDX text length (Excel 2003): 2403
Avg MDX text length (Excel 2007): 1567
Avg query duration (Excel 2003): 507 ms
Avg query duration (Excel 2007): 96 ms

|||Thanks Ramunas!
Did you try to use the browser in Analysis Services or the Reporting Services to query your OLAP data . In my opinion, the queried time of using two ways above is better than the time of using Excel pivot table.
Could you explain me the reason why?

|||

Yeah, Excel 2003 is horrible with OLAP. It's the MDX issue, it does tons of EXCEPTS(), etc. Instead of scripting what it wants in the pivottable, it scripts what it doesn't want. Not sure how this ever got passed.

For your last reply, you will be hitting cache if you run from Excel, then run the same query somewhere else. You would have to execute the ClearCache command after every MDX statement for a true test. Or could be a network slowup if your using Excel externally (off the server) and running Management Studio on the server directly, for example.