CREATE TABLE in wrong database (Master)

I am using ASP.NET 1.1 and MS SQL 2005

the folowing ODBC stringconnection
Driver={SQL Server};Server=(local);MyBase;Uid=;Pwd=;Trusted_Co nnection=;

when trying to CREATE a TABLE (with vb.net code) I get an error because the TABLE are written inMaster !! and not inMyBase

I am using windows Authentication

what can be wrong ?

thank youWhat is your reason for creating table in VB.NET instead of managment studio with SQL or the GUI and why are you using obsolete ODBC instead of ADO.NET? When you get the answer to that you will know why you are creating the table in the Master database. Hope this helps.|||Caddre if you donyt know the answer to that problem please give up !
i must do it in that way because the database allready exists in many intranets on > 10 000 PC|||the problem is why is it creating tables in Master when the connection string =
Driver={SQL Server};Server=(local);MyBase;Uid=;Pwd=;Trusted_Connection=;

?

|||But you still don't do it that way, you have two options get the DBA to create the table for you or you should just right click and register the SQL Server with the database at the top of management studio. The number of users on the network is not relevant the only requirement is on the same network and in an intranet you are. And you don't need new SQL Server with your SQL Server running your DBA can give you the personal SQL Server free it comes with the license. Here I have read only access to Oracle if I need to create something the DBA will create it. And yes I also use SQL Server I can help you with most problems. Post again if you still need more help. Hope this helps.|||thanks a lot Caddre but it works fine now

just forgottent database= in the connectionstring when i have copy-pasted the string

and that way of course works perfectly|||I am glad you got it resolved and I am sorry about my first post.

CREATE TABLE in wrong database (Master)

I am using ASP.NET and a normal ODBC stringconnection

Driver={SQL Server};Server=(local);MyBase;Uid=;Pwd=;Trusted_Co nnection=;

when trying to CREATE a TABLE (with vb.net code) I get an error because the TABLE are written in Master !! and not in MyBase

i am using windows authentication

what can be wrong ?

thank youHi

Your default db will be master. You need to name your params in the string. How about:

Driver={SQL Server};Server=(local);Database=MyBase;Uid=;Pwd=;T rusted_Connection=;
?

EDIT - BTW - do you not need to put True after trusted connection or does that work?|||And...

never need to ask a connection string question of anyone again:
http://www.connectionstrings.com/
http://www.carlprothman.net/Default.aspx?tabid=81

HTH|||my connection string is perfect .. it is not a connectionstring problem|||No it's not. Have another look. MyBase is just floating there. And Trusted Connection, Uid and Pwd are parameters without values.

Check the links.|||my connection string is perfect .. it is not a connectionstring problem

So....

Why are you asking for help then?|||So....

Why are you asking for help then?It's perfect... That doesn't mean that it is working.

-PatP|||You could also set the default for the userid to the database you want the table(s) written to. Then the default would be MyBase instead of Master.

Lookup sp_defaultdb in BOL.|||this connecting string is working fine since 6 months, i have installed MS SQL 2005 on MS SQL 2000 and it doesnt work any more, it is not a connectionString problem
it is a database rights problem, and a microsoft problem too|||You could also set the default for the userid to the database you want the table(s) written to. Then the default would be MyBase instead of Master.

Lookup sp_defaultdb in BOL.

there is no UserId no password I am using windows authentication

what do you mean by Then the default would be MyBase instead of Master.

do you mean the user account ? I am using only one user account for all my databases

thank you|||Hi

Whatever account you use (SQL or NT) to connect to SQL Server will have a default database assigned to it. If you don't specify a database in your connection string you will connect to the default database. You have not specified a database in your connection string (however perfect it may be) so you are connecting to your default database, typically master (master is the default default database :) ). So - either specify a database in your connection string or change your default database in SQL Server.|||it works now !!

thank you|||this connecting string is working fine since 6 months, i have installed MS SQL 2005 on MS SQL 2000 and it doesnt work any more, it is not a connectionString problem
it is a database rights problem, and a microsoft problem tooAh - just realised your problem - I bet in 2000 the default db was set up but not in 2005 eh? Seriously - the MyBase bit in your connection string is doing nothing - I'm surprised it didn't throw an exception to be honest.

Glad to have helped :D|||it works now !!

thank you

Must be the miracle connection string...

And I'm sure you didn't change a thing...

create relationship with tables in a linked server

I need to create a relationship between a local table and tables on a
linked server. I used the design table wizard and selected the
relationship property wizard. In the reslationship property wizard,
the tables that I need to get the keys from in the linked server do not
show up. Is there a way to do this, or I simply don't have enough
permission to tables in the linked server. On the local server, the
Security tab of linked server property has Local Loging "sa", Remote
User "sa" and Remote Password "****". Thanks for your help.Hi

You can not create a relationship in this way, you are restricted to
definining FKs to tables in the same database. If you wish to force a
constraint between the local table and the remote one, you can write a
function that can be used in a check constraint or enforce it through
writing a trigger (which may be the faster and easier solution to
implement).

John

"js" <androidsun@.yahoo.com> wrote in message
news:1102725393.883680.211450@.z14g2000cwz.googlegr oups.com...
>I need to create a relationship between a local table and tables on a
> linked server. I used the design table wizard and selected the
> relationship property wizard. In the reslationship property wizard,
> the tables that I need to get the keys from in the linked server do not
> show up. Is there a way to do this, or I simply don't have enough
> permission to tables in the linked server. On the local server, the
> Security tab of linked server property has Local Loging "sa", Remote
> User "sa" and Remote Password "****". Thanks for your help.

Create Procedure #Test in another SP?

Hi,

I was wondering if it's possible to create a local stored procedure within another stored procedure? Something like the following:

CREATE PROCEDURE [sti_edocsecure].[spPagingDynamicWrapper]

@.Page int,
@.Size int,
@.ItemStatus int,
@.UserId int,
@.TableName varchar(255),
@.Select varchar(8000),
@.Criteria varchar(8000)

AS

BEGIN TRANSACTION

SET NOCOUNT ON

CREATE PROCEDURE #Test
@.Page int,
@.Size int,
@.ItemStatus int,
@.UserId int,
@.TableName varchar(255),
@.Select varchar(8000),
@.Criteria varchar(8000)

AS

BEGIN TRANSACTION

SET NOCOUNT ON

RETURN 0

COMMIT TRANSACTION
Return 0

COMMIT TRANSACTION
GO

According to MSDN you can do this but I can't find any samples. The above SP gives me an error saying:

Error 156: Incorrect syntax near the keyword 'PROCEDURE'.

So, I don't know if I'm doing something wrong or if this is even possible.

Thanks for any insight anyone can provide,

CraigNot quite sure why U'd want 2 but

declare @.sql nvarchar(500)
select @.sql = 'Create Procedure AAA As SELECT 1 as one'

exec sp_executesql @.sql

Handy little sp this one [sp_executesql]

GW

Create local snapshot replication for sql 2005 failed

I tried to create a local snapshot replication from data A to database
B for sql 2005. I followed the wizard and it was created successfully.
But,nothing written to the replication folder and the job failed.
I manually executed the sqls and it always failed on
sp_addpublication_snapshot and the error was:
'DB4\Administrator' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy.
I log in to windows 2003 as administrator and the replication account
id dbsnap. What I have to do to avoid the error?
Is there a detailed step-by=step guide to create a snapshot
replication?
Can someone provide a set of sqls that I can just use to create a local
(or remote) snapshot?
Thanks,
Andy
The scripts are:
use [T2]
exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
@.value = N'true'
GO
-- Adding the snapshot publication
use [T2]
exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
publication of
database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
@.retention = 0,
@.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous =
N'true',
@.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot =
N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
= N'active',
@.independent_agent = N'true', @.immediate_sync = N'true',
@.allow_sync_tran = N'false',
@.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
@.allow_dts = N'false',
@.replicate_ddl = 1
GO
exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
1,
@.frequency_interval = 0, @.frequency_relative_interval = 0,
@.frequency_recurrence_factor = 0,
@.frequency_subday = 0, @.frequency_subday_interval = 0,
@.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.active_start_date = 0,
@.active_end_date = 0, @.job_login =
N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
@.publisher_login =
N'sa', @.publisher_password = N'chang5911'
use [T2]
exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
@.source_owner =
N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
@.description = null,
@.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x000000000803509D,
@.identityrangemanagementoption = N'manual', @.destination_table =
N'RETURN_REASON',
@.destination_owner = N'dbo', @.vertical_partition = N'false'
GO
Can you make the admin account( 'DB4\Administrator' ) part of the sysadmin
role on the publisher?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AH" <hhhsu7a@.yahoo.com> wrote in message
news:1168789205.120731.163890@.51g2000cwl.googlegro ups.com...
>I tried to create a local snapshot replication from data A to database
> B for sql 2005. I followed the wizard and it was created successfully.
> But,nothing written to the replication folder and the job failed.
> I manually executed the sqls and it always failed on
> sp_addpublication_snapshot and the error was:
> 'DB4\Administrator' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy.
> I log in to windows 2003 as administrator and the replication account
> id dbsnap. What I have to do to avoid the error?
> Is there a detailed step-by=step guide to create a snapshot
> replication?
> Can someone provide a set of sqls that I can just use to create a local
> (or remote) snapshot?
> Thanks,
> Andy
>
> The scripts are:
> use [T2]
> exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
> @.value = N'true'
> GO
> -- Adding the snapshot publication
> use [T2]
> exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
> publication of
> database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
> @.retention = 0,
> @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous =
> N'true',
> @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
> @.compress_snapshot =
> N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
> @.allow_subscription_copy = N'false',
> @.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
> = N'active',
> @.independent_agent = N'true', @.immediate_sync = N'true',
> @.allow_sync_tran = N'false',
> @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
> @.allow_dts = N'false',
> @.replicate_ddl = 1
> GO
>
> exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
> 1,
> @.frequency_interval = 0, @.frequency_relative_interval = 0,
> @.frequency_recurrence_factor = 0,
> @.frequency_subday = 0, @.frequency_subday_interval = 0,
> @.active_start_time_of_day = 0,
> @.active_end_time_of_day = 235959, @.active_start_date = 0,
> @.active_end_date = 0, @.job_login =
> N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
> @.publisher_login =
> N'sa', @.publisher_password = N'chang5911'
>
> use [T2]
> exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
> @.source_owner =
> N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
> @.description = null,
> @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option =
> 0x000000000803509D,
> @.identityrangemanagementoption = N'manual', @.destination_table =
> N'RETURN_REASON',
> @.destination_owner = N'dbo', @.vertical_partition = N'false'
> GO
>

create local cube issue

Hello

everyone,

I have a

problem in creating the local cube in MSAS2005. This is what I have done please

guide me to solve the issue.

Steps:

1. We have installed MSAS2005

   with SP1 on Dev box

2. I created cubes on BI Studio

   (linked to dev box from my local pc)

3. I can see the cubes from MSMS

   (linked to dev box from my local pc)

4. Then I tried to CREATE GLOBAL CUBE myCube

Storage

‘c:mylocalcube.cub’

From myCube

(MEASURE [myCube].[ORDERS],

DIMENSION

[myCube].[Time].[Year -Quarter -

Month],

DIMENSION [myCube].[All Invoices].[Inv Number])

5. I got error message ’

   A connection cannot be made. Ensure that the server is

   running.’

6. MSAS2005 server is running. I

   can view the cubes from MSMS

Please

tell me if I am missing something and also help me to resolve the issue.

Thanks

You are missing a backslash:

c:mylocalcube.cub

should be

c:\mylocalcube.cub

|||Thanks. I forgot type in.

I tried that directly run this MDX query on the server, it works. Somehow can not run cross network, but I can run select MDX query in my box.

Someone have any idea?

Thanks.|||

Sorry, I don't understand what exactly doesn't work. Let's clarify:

1. Create Global Cube from your machine to the server on your machine : Works

2. Create Global Cube from you machine to the server on another machine: Doesn't

|||Sorry, I didn't say clear.
1. Create Global Cube from the server on server machine: Works
2. Create Global Cube from my machine to the server on my machine: Doesn't
3. I tried : 'SELECT
{[Measures].[Orders]} ON COLUMNS,
[Time].[Year].Members ON ROWS
FROM myCube'
from my machine to the server on my machine: Works.
means Analysis Service is running and connecting, but just not work for 'Create Global Cube'. I really do not understand why.

Any suggestions?

Thanks.

|||Could you make sure that cube file is not opened from any other application and that it's not write protected or something like this? May be just delete this file and try to create it again. The other thing that comes to mind are permissions for directory, but you are obviosly an administrator on your machine and should have permissions to your own c:\ drive. Is it Vista by any chance?|||This file not exists on the server, I want to create it, but no succeed.
I will double check permissions for the directory when server person come.

Thanks a lot Irina. I will post the result later.

By the way, Is there a way can schedule MDX query as a job?

Thanks.|||I checked permissions for the directory, everyone has read and write permissions at this moment, but I still got same error 'A connection cannot be made. Ensure that the server is running.'

What else I should check? any suggestions?

Thanks.

create local cube issue

Hello everyone,

I have a problem in creating the local cube in MSAS2005. This is what I have done please guide me to solve the issue.

Steps:

1. We have installed MSAS2005 with SP1 on Dev box
2. I created cubes on BI Studio (linked to dev box from my local pc)
3. I can see the cubes from MSMS (linked to dev box from my local pc)
4. Then I tried to CREATEGLOBALCUBE myCube

Storage ‘c:mylocalcube.cub’

From myCube

(MEASURE [myCube].[ORDERS],

DIMENSION [myCube].[Time].[Year -Quarter - Month],

DIMENSION [myCube].[All Invoices].[Inv Number])

5. I got error message ’A connection cannot be made. Ensure that the server is running.’
6. MSAS2005 server is running. I can view the cubes from MSMS

Please tell me if I am missing something and also help me to resolve the issue.

Thanks

You are missing a backslash:

c:mylocalcube.cub

should be

c:\mylocalcube.cub

|||Thanks. I forgot type in.

I tried that directly run this MDX query on the server, it works. Somehow can not run cross network, but I can run select MDX query in my box.

Someone have any idea?

Thanks.
|||

Sorry, I don't understand what exactly doesn't work. Let's clarify:

1. Create Global Cube from your machine to the server on your machine : Works

2. Create Global Cube from you machine to the server on another machine: Doesn't

|||Sorry, I didn't say clear.
1.Create Global Cube from the server on server machine: Works
2. Create Global Cube from my machine to the server on my machine: Doesn't
3. I tried : 'SELECT
{[Measures].[Orders]} ON COLUMNS,
[Time].[Year].Members ON ROWS
FROM myCube'
from my machine to the server on my machine: Works.
means Analysis Service is running and connecting, but just not work for 'Create Global Cube'. I really do not understand why.

Any suggestions?

Thanks.

|||Could you make sure that cube file is not opened from any other application and that it's not write protected or something like this? May be just delete this file and try to create it again. The other thing that comes to mind are permissions for directory, but you are obviosly an administrator on your machine and should have permissions to your own c:\ drive. Is it Vista by any chance?|||This file not exists on the server, I want to create it, but no succeed.
I will double check permissions for the directory when server person come.

Thanks a lot Irina. I will post the result later.

By the way, Is there a way can schedule MDX query as a job?

Thanks.
|||I checked permissions for the directory, everyone has read and write permissions at this moment, but I still got same error 'A connection cannot be made. Ensure that the server is running.'

What else I should check? any suggestions?

Thanks.

Create local cube from remote AS server

Hello

I try the following:

I
1) connect to a remote server and browse its AS databases and cubes
2) display one cube in a OWC 10/11 pivottable (by setting .connectionstring and .datamember
3) try to create a local cube from this server database/cube using "CREATE GLOBAL CUBE...") giving a unc path as target

Steps 1 and 2 are successful (I use a named user in the format domain\username).

Step 3 gives an error: either a connection cannot be made to server ... or AS is not running on this computer

When I try this with my local AS, 1 to 3 works fine (assumed I use localhost and integrated security)

Questions:

1) Is there any rule for user accounts to be able to create local cubes?

2) I get an error (no. -1056899072) in the sql profiler when I try step 3, but do not know how to go any further. Is there any description for this error?

Regards
Klaus Wiesel

Try to make sure the path you specify in your command is valid path for remote Analysis Server.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

create local .CUB File in AS2005

Hi,

we have a problem with the "CREATE GLOBAL CUBE" function in AS2005:

While accessing the local .CUB file (created with "CREATE GLOBAL CUBE"), our application only shows key-values for some dimensions instead of their names.
If we use the same statement in AS2000 to create the .CUB, everything is displayed correctly;
as well if the application directly connects to the DB (AS2000 & AS2005).

We used the Profiler to take a look at the traces and found something in the SELECT that is used to fill the new cube after creation:
every dimension is bracketed with "KEY()"
We tested the statement and replaced the "KEY()" with "NAME()" and it looks like this would bring the correct data.

But how do we tell the AS2005 to use NAME() instead of KEY() while compiling the "CREATE GLOBAL CUBE" statement?

Or is there annother way to create the local .CUB file?
We are able to create a cube via xmla ... but how do we write it to a local file?

Thanks for your answers and for tolerating my bad english
best regards,
Sven

I don't know what you mean by the statement "Our application only shows key-values". If you mean your application only displays dimension member keys instead of dimension member names, then, this is a bug that is being fixed in the SP2 release.

You can capture the local cube statement, modify it suitably and send it via MDX Client.

create local .CUB File in AS2005

Hi,

we have a problem with the "CREATE GLOBAL CUBE" function in AS2005:

While accessing the local .CUB file (created with "CREATE GLOBAL CUBE"), our application only shows key-values for some dimensions instead of their names.
If we use the same statement in AS2000 to create the .CUB, everything is displayed correctly;
as well if the application directly connects to the DB (AS2000 & AS2005).

We used the Profiler to take a look at the traces and found something in the SELECT that is used to fill the new cube after creation:
every dimension is bracketed with "KEY()"
We tested the statement and replaced the "KEY()" with "NAME()" and it looks like this would bring the correct data.

But how do we tell the AS2005 to use NAME() instead of KEY() while compiling the "CREATE GLOBAL CUBE" statement?

Or is there annother way to create the local .CUB file?
We are able to create a cube via xmla ... but how do we write it to a local file?

Thanks for your answers and for tolerating my bad english
best regards,
Sven

I don't know what you mean by the statement "Our application only shows key-values". If you mean your application only displays dimension member keys instead of dimension member names, then, this is a bug that is being fixed in the SP2 release.

You can capture the local cube statement, modify it suitably and send it via MDX Client.

'CREATE GLOBAL CUBE' issue

Hi,

I have cube with 10 measures and 20 dimensions. When I do 'CREATE GLOBAL CUBE',
using 10 measures with any 4 dimensions work fine, can create local cube, but could not success when I include 10 measures with any 6 or more dimensions.
The error msg: 'Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
Errors in the OLAP storage engine: An error occurred while processing the 'myCube' partition of the 'Fact myCube' measure group for the 'myCube' cube from the myCube database.
XML for Analysis parser: The XML for Analysis request timed out before it was completed.

Is it any limitations for 'CREATE GLOBAL CUBE'?

What am I missing?

Any suggestions or input will be appreciated.

Thanks.
We have identified many situations where local cube creation fails with this error. We have not found a way to work around these errors using CREATE GLOBAL CUBE. We have found ways to deal with these errors by using an ASSL Create Cube statement to create local cubes. Our product, CubeSlice, generates the required ASSL to create the local cube. We offer a free 30-day demo, so you can try it at no cost and see if your local cubes can be created with ASSL using all your dimensions. I'd encourage you to try the local cube creation with both a relational data source and an Analysis Server cube source. We give this option on the first tab of our Local Cube Options dialog. If you still get the error you describe when using CubeSlice, let me know, and we'll try to get it fixed.

Tim Peterson
www.cubeslice.com

create database at shared hoster site

I have an sql script that I ran on my local system using osql. It
created all the tables, views, and even inserted sample data into the
tables.

Now I need to create that same database on a shared hoster site. Only
the hoster does not allow the running of osql.

How can I create my sql server database on my shared hoster site?

Can I save and restore my database somehow?

Is there a stored procedure I can run which will run the .sql script
file?

thanks,

-SteveThat depends on how your provider expects you to execute SQL code - if
they don't allow osql.exe, then what do they allow? And how do you
connect to the hosted database - do you use Enterprise Manager? Since
osql.exe is just a client application, if you can connect to the SQL
server then you should be able to use it.

Simon|||Simon Hayes wrote:
> That depends on how your provider expects you to execute SQL code -
if
> they don't allow osql.exe, then what do they allow?

the hoster is godaddy.com You do get a panel where you can manually
config the database. There is something called "Query Analyser" which
provides a window to run sql statements. I dont think this is the same
as the Microsoft Query Analyzer that I read about yesterday. In this
godaddy qa window I cannot "run sqlscript.sql" like I think you can do
in Microsoft Query Analyzer.

What I was able to do was cut and paste large sections of my script.sql
from my PC editor into the GoDaddy "Query Analyser" window. Then I
clicked the "submit" button and it basically worked.

> And how do you
> connect to the hosted database - do you use Enterprise Manager?

> Since
> osql.exe is just a client application, if you can connect to the SQL
> server then you should be able to use it.

well my asp.net code can use the SqlConnection class to connect to the
database, then use SqlCommand to run SQL commands. Can osql be run in
that context?

I am curious to know if there is a stored procedure I can use which
will run an .sql script.

thanks,

-Steve|||You might be able to re-use this code in ASP:

http://groups-beta.google.com/group...e72b5441b?hl=en

A web interface which doesn't allow uploading scripts is quite limited
- I would definitely ask the provider if they can enhance it.

There's no stored proc to run a file, probably because the server would
need to have access to the script file somehow, which is tricky if it's
on your workstation. It's common to use xp_cmdshell to call osql.exe as
a way of executing scripts, but that's only really useful for syadmins,
because of the security implications.

Simon|||Simon Hayes wrote:
> You might be able to re-use this code in ASP:
>
http://groups-beta.google.com/group...e72b5441b?hl=en

that looks to be exactly what I am looking for.

thanks very much,

-Steve

> A web interface which doesn't allow uploading scripts is quite
limited
> - I would definitely ask the provider if they can enhance it.
> There's no stored proc to run a file, probably because the server
would
> need to have access to the script file somehow, which is tricky if
it's
> on your workstation. It's common to use xp_cmdshell to call osql.exe
as
> a way of executing scripts, but that's only really useful for
syadmins,
> because of the security implications.
> Simon

Create cube file with Analysis Service Command?

Hi all experts,

Is it possible to create local cube file in SQL job agent with Analysis Service Command?

And how?

Thanks in advance.

Hi,

There is a similar thread about creating local cube files here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=380407&SiteID=1

|||

In an earlier post I mentioned you can use the ASCMD utility to submit MDX statements to an OLAP cube via the command line. You can use that for what follows.

To get the MDX statement script, I opened Profiler on my SSAS instance and then started up Excel 2007. In Excel, I connected to the OLAP cube and then in the OLAP Tools portion of the Data ribbon I selected to create a local cube. I used the wizard to define the cube and submitted the request. Profiler caught the submitted statement. Here it is (with some data removed to keep the size of this post down:

Code Snippet

CREATE GLOBAL CUBE [Adventure Works] STORAGE 'C:\Documents and Settings\bcsmith\My Documents\Adventure Works.cub' FROM [Adventure Works]
(
MEASURE [Adventure Works].[Internet Sales Amount],
MEASURE [Adventure Works].[Internet Order Quantity],
MEASURE [Adventure Works].[Internet Extended Amount],
MEASURE [Adventure Works].[Internet Tax Amount],
DIMENSION [Adventure Works].[Account].[Accounts]
,
DIMENSION [Adventure Works].[Department].[Departments]
,
DIMENSION [Adventure Works].[Destination Currency].[Destination Currency]
(
LEVEL [Destination Currency],
MEMBER [Destination Currency].[Destination Currency].&[US Dollar]
),
DIMENSION [Adventure Works].[Employee].[Employees]
,
DIMENSION [Adventure Works].[Organization].[Organizations]
,
DIMENSION [Adventure Works].[Scenario].[Scenario]
(
LEVEL [Scenario],
MEMBER [Scenario].[Scenario].&[1]
)

)

Hope that helps you get started.

Bryan

|||Thanks Adrian and Bryan for the kindly advice. I am going to try it.

Thanks again.