Creating stored procs with MSforeachdb

I'm trying to create stored procedures in about 85 databases. I keep
running into the problem 'Incorrect syntax near the keyword
'procedure':
declare @.cmd1 varchar(500)
set @.cmd1 =
'if ''?'' like ''%_06'' CREATE procedure newproc AS select * from
item'
exec sp_MSforeachdb @.command1=@.cmd1
Thanks.
Hello,
You can use something like this:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
CREATE procedure newproc AS select * from item
'')'
However, you should consider if it's better to keep so many different
databases (with the same structure) or if you should consolidate all
the data in a single database. If you are using different databases for
each company it may be OK, but if you are using different databases for
each year, you should reconsider.
Razvan
|||Why don=B4t you do something like this:
SELECT 'USE ' + NAME + CHAR(13) + 'CREATE procedure newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
Then use the output for the execution.
HTH, Jens Suessmeyer.
|||Razvan,
Thank you for your reply. However, this give the result "There is
already an object named 'setnewrate_06_Feb' in the database." once for
each of the databases. It creates the proc in master, never sets the
context to the other dbs, and tries to recreate the proc in master
repeatedly. Here's the actual statement:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
create procedure setnewrate_06_Feb AS
UPDATE setRate SET TPAFee = 0.23 WHERE [Remit Date] >=
''''1/1/2005'''' OR resolved = 0
UPDATE setRate SET TPAFee = 0.18 WHERE [Remit Date] >=
''''1/1/2006''''
UPDATE setRate SET FFPRate = 0.6745 WHERE [Submit Date] <
''''10/1/2005'''' OR resolved = 0
UPDATE setRate SET FFPRate = 0.6698 WHERE [Submit Date] >=
''''10/1/2005''''
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 0
UPDATE setRate SET AdminRate = 0 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 1
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] >
''''11/30/04''''
UPDATE setRate SET expcd = ([amount]*[FFPRate]*(1-AdminRate)) - ([Bill
Log Units]*[TPAFee])
'')'
I also appreciate your comment on consolidating databases. We are
actively considering that.
Thanks again.
|||Jens,
This unfortunately does not work because "create procedure" must be the
first statement in a batch.
Thanks for the response.
Steve
|||This is working, because "GO" is the batch separator in this case. So
th CREATE Proc is the first statement in the batch...
HTH, Jens Suessmeyer.
|||Hi Jens,
I really wish it did work. Here's a part of the output that I used to
execute:
USE Glendale_06 CREATE procedure newproc AS select * from item GO
USE Ash_Fork_06 CREATE procedure newproc AS select * from item GO
USE Grand_Canyon_06 CREATE procedure newproc AS select * from item GO
Here's the output from this:
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.
Thank you,
Steve
|||Sorry, you are definitely 100% correct.
This should fix it.
SELECT 'USE ' + NAME + CHAR(13) + 'GO' + CHAR(13) + 'CREATE procedure
newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
HTH, Jens Suessmeyer.
|||Hello,
The following should work:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' use ? exec(''
CREATE procedure newproc AS select * from item
'')'
Razvan
|||Thanks VERY MUCh to you both, I will give these both a try.
Cheers,
Steve

Creating stored procs with MSforeachdb

I'm trying to create stored procedures in about 85 databases. I keep
running into the problem 'Incorrect syntax near the keyword
'procedure':
declare @.cmd1 varchar(500)
set @.cmd1 =
'if ''?'' like ''%_06'' CREATE procedure newproc AS select * from
item'
exec sp_MSforeachdb @.command1=@.cmd1
Thanks.Hello,
You can use something like this:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
CREATE procedure newproc AS select * from item
'')'
However, you should consider if it's better to keep so many different
databases (with the same structure) or if you should consolidate all
the data in a single database. If you are using different databases for
each company it may be OK, but if you are using different databases for
each year, you should reconsider.
Razvan|||Why don=B4t you do something like this:
SELECT 'USE ' + NAME + CHAR(13) + 'CREATE procedure newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
Then use the output for the execution.
HTH, Jens Suessmeyer.|||Razvan,
Thank you for your reply. However, this give the result "There is
already an object named 'setnewrate_06_Feb' in the database." once for
each of the databases. It creates the proc in master, never sets the
context to the other dbs, and tries to recreate the proc in master
repeatedly. Here's the actual statement:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
create procedure setnewrate_06_Feb AS
UPDATE setRate SET TPAFee = 0.23 WHERE [Remit Date] >=
''''1/1/2005'''' OR resolved = 0
UPDATE setRate SET TPAFee = 0.18 WHERE [Remit Date] >=
''''1/1/2006''''
UPDATE setRate SET FFPRate = 0.6745 WHERE [Submit Date] <
''''10/1/2005'''' OR resolved = 0
UPDATE setRate SET FFPRate = 0.6698 WHERE [Submit Date] >=
''''10/1/2005''''
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 0
UPDATE setRate SET AdminRate = 0 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 1
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] >
''''11/30/04''''
UPDATE setRate SET expcd = ([amount]*[FFPRate]*(1-AdminRate)) - (
1;Bill
Log Units]*[TPAFee])
'')'
I also appreciate your comment on consolidating databases. We are
actively considering that.
Thanks again.|||Jens,
This unfortunately does not work because "create procedure" must be the
first statement in a batch.
Thanks for the response.
Steve|||This is working, because "GO" is the batch separator in this case. So
th CREATE Proc is the first statement in the batch...
HTH, Jens Suessmeyer.|||Hi Jens,
I really wish it did work. Here's a part of the output that I used to
execute:
USE Glendale_06 CREATE procedure newproc AS select * from item GO
USE Ash_Fork_06 CREATE procedure newproc AS select * from item GO
USE Grand_Canyon_06 CREATE procedure newproc AS select * from item GO
Here's the output from this:
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.
Thank you,
Steve|||Sorry, you are definitely 100% correct.
This should fix it.
SELECT 'USE ' + NAME + CHAR(13) + 'GO' + CHAR(13) + 'CREATE procedure
newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
HTH, Jens Suessmeyer.|||Hello,
The following should work:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' use ? exec(''
CREATE procedure newproc AS select * from item
'')'
Razvan|||Thanks VERY MUCh to you both, I will give these both a try.
Cheers,
Steve

Creating stored procs with MSforeachdb

I'm trying to create stored procedures in about 85 databases. I keep
running into the problem 'Incorrect syntax near the keyword
'procedure':
declare @.cmd1 varchar(500)
set @.cmd1 = 'if ''?'' like ''%_06'' CREATE procedure newproc AS select * from
item'
exec sp_MSforeachdb @.command1=@.cmd1
Thanks.Hello,
You can use something like this:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
CREATE procedure newproc AS select * from item
'')'
However, you should consider if it's better to keep so many different
databases (with the same structure) or if you should consolidate all
the data in a single database. If you are using different databases for
each company it may be OK, but if you are using different databases for
each year, you should reconsider.
Razvan|||Why don=B4t you do something like this:
SELECT 'USE ' + NAME + CHAR(13) + 'CREATE procedure newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
Then use the output for the execution.
HTH, Jens Suessmeyer.|||Razvan,
Thank you for your reply. However, this give the result "There is
already an object named 'setnewrate_06_Feb' in the database." once for
each of the databases. It creates the proc in master, never sets the
context to the other dbs, and tries to recreate the proc in master
repeatedly. Here's the actual statement:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
create procedure setnewrate_06_Feb AS
UPDATE setRate SET TPAFee = 0.23 WHERE [Remit Date] >=''''1/1/2005'''' OR resolved = 0
UPDATE setRate SET TPAFee = 0.18 WHERE [Remit Date] >=''''1/1/2006''''
UPDATE setRate SET FFPRate = 0.6745 WHERE [Submit Date] <
''''10/1/2005'''' OR resolved = 0
UPDATE setRate SET FFPRate = 0.6698 WHERE [Submit Date] >=''''10/1/2005''''
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] <=''''11/30/04'''' AND resolved = 0
UPDATE setRate SET AdminRate = 0 WHERE [Submit Date] <=''''11/30/04'''' AND resolved = 1
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] >
''''11/30/04''''
UPDATE setRate SET expcd = ([amount]*[FFPRate]*(1-AdminRate)) - ([Bill
Log Units]*[TPAFee])
'')'
I also appreciate your comment on consolidating databases. We are
actively considering that.
Thanks again.|||Jens,
This unfortunately does not work because "create procedure" must be the
first statement in a batch.
Thanks for the response.
Steve|||This is working, because "GO" is the batch separator in this case. So
th CREATE Proc is the first statement in the batch...
HTH, Jens Suessmeyer.|||Hi Jens,
I really wish it did work. Here's a part of the output that I used to
execute:
USE Glendale_06 CREATE procedure newproc AS select * from item GO
USE Ash_Fork_06 CREATE procedure newproc AS select * from item GO
USE Grand_Canyon_06 CREATE procedure newproc AS select * from item GO
Here's the output from this:
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.
Thank you,
Steve|||Sorry, you are definitely 100% correct.
This should fix it.
SELECT 'USE ' + NAME + CHAR(13) + 'GO' + CHAR(13) + 'CREATE procedure
newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
HTH, Jens Suessmeyer.|||Hello,
The following should work:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' use ? exec(''
CREATE procedure newproc AS select * from item
'')'
Razvan|||Thanks VERY MUCh to you both, I will give these both a try.
Cheers,
Steve

Creating SQL View - syntax is wrong

We are creating a view in SQL to format data to DTS to another database. Here is the section of the view which does not work -- eliminating this section from the query allows the view to be created:

CASE APTran.LineNbr
WHEN < '0' THEN 'O'
--Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
WHEN > '0' THEN 'D'
--Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
ELSE 'E'
END AS JED_DIST_OR_OFF,

I am attaching the whole query stmt here.

Thanks very much for all assistance.Is LineNbr numeric?

--EDIT:

Can you tell us the error?

Might help|||The error received in Query analyzer is:
Server: Msg 170, Level 15, State 1, Procedure xvr_03901EXPORTER, Line 107
Line 107: Incorrect syntax near '<'.
Server: Msg 170, Level 15, State 1, Procedure xvr_03901EXPORTER, Line 133
Line 133: Incorrect syntax near 'APTran'.
Server: Msg 170, Level 15, State 1, Procedure xvr_03901EXPORTER, Line 186
Line 186: Incorrect syntax near 'APDoc'.

The field aptran.linenbr is datatype smallint.

Thanks, Dave Spangler|||CASE
WHEN cast(APTran.LineNbr as int) < 0 THEN 'O'
--Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
WHEN cast(APTran.LineNbr as int) > 0 THEN 'D'
--Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
ELSE 'E'
END AS JED_DIST_OR_OFF,|||Hey, based on the code you submitted before I clicked on Post, I thought (here's that magic word again ;)) that it was a varchar field!

CASE
WHEN APTran.LineNbr < 0 THEN 'O'
--Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
WHEN APTran.LineNbr > 0 THEN 'D'
--Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
ELSE 'E'
END AS JED_DIST_OR_OFF,

And you probably need to test for NULL unless this is taken care of by the 'IsNullable' property of the field.|||Originally posted by rdjabarov
Hey, based on the code you submitted before I clicked on Post, I thought (here's that magic word again ;)) that it was a varchar field!

CASE
WHEN APTran.LineNbr < 0 THEN 'O'
--Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
WHEN APTran.LineNbr > 0 THEN 'D'
--Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
ELSE 'E'
END AS JED_DIST_OR_OFF,

And you probably need to test for NULL unless this is taken care of by the 'IsNullable' property of the field.

Hey, get your own ideas...:D

And why would they have to check for Nulls Null would give them an "E"|||Yup, forgot about ELSE

Creating local cubes from relational sources using XMLA

Hi,

I have looked at Chris Webb′s example in creating local cubes. I′m trying to follow his instructions but I think that the syntax or the code is incorrect somehow.

Code Snippet

CREATE GLOBAL CUBE statement. FILENAME|C:\myCube.cub|DDL|

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
</Parallel>

Hello Inmon,

pleae visit the blog entry again and read again:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!883.entry

It is not possible to create a local cube from relational data with CREATE GLOBAL CUBE Syntax.

I got the sample running.

HTH

J?rg

|||

Hi J?rg,

Then I would be happy if you told me how you solved it.

This is the example I followed by Chris:

· Open MDX Sample app - yes, this is another one of those areas where SQLMS won't do the job. Connect to the Adventure Works database

· Paste a CREATE GLOBAL CUBE statement into a query pane. Don't run it yet though...

· Start a new Profiler trace on your target database

· Run the CREATE GLOBAL CUBE statement, then stop the trace

· Inside your trace, you'll see the CREATE GLOBAL CUBE statement appear twice followed by a statement which begins with something like this: "CREATE GLOBAL CUBE statement. FILENAME|c:\MyfileName.cub|DDL|" and is followed by an XMLA batch statement

· Copy the XMLA batch statement out to MDX Sample App (if you find you can't do this, you might need to save the trace as an XML file and open it in Notepad or something) and

· Close the connection to your server cube in MDX Sample app, then reconnect to the path you want to create your new cub file at, eg "c:\cubtest.cub" (instead of supplying the name of your server in the connection dialog box).

· Run the Batch statement, and your local cube is created!

|||

Hi Inmon,

this is the sample for relational datasources

In SSMS script the database you've just created to a new query editor window. Still in SSMS, right-click on the database again and select Process, select 'Process Full' then instead of clicking OK click the Script button and choose to script to a new query editor window. You now have the two XMLA commands you need to create and process your local cube, you just need to combine them into one. Select all of the Create statement generated when you scripted the database and paste it into the Batch statement generated from the Process dialog, immediately after the first line (containing the 'Batch' command) and before the XMLA that actually does the processing. Copy the new statement, open up MDX Sample app, connect to the path of the new local cube file, paste the statement into MDX Sample app and then run it. And voila, your local cube will be built direct from the relational data source.|||

Hi Joschko,

I have read that example too. Combine the two scripts into one is no problem but I can′t connect to the path of my local cube. When I connect to my local cube I connect to Analysis Services and writing c:\myCube.cub in the server name field. The .cub file creates but following message appear:

Code Snippet

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.Xmla)

The 'c:\myCube.cub' local cube file cannot be opened. (Microsoft.AnalysisServices.Xmla)

The operation completed successfully (Microsoft.AnalysisServices.Xmla)

When I then run the script I recieve the following message:

Code Snippet

TITLE: Microsoft SQL Server Management Studio

Either the user, DOMAIN\userid, does not have access to the c:\myCube.cub database, or the database does not exist. (Microsoft SQL Server 2005 Analysis Services)

|||

Hi,

I have now succeeded with connect the script into one. Wpuld you like to confirm this?

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
</Parallel>
</Batch>

When I connect to my local cube through new query and select AS XMLA query and the run it I receive the following message:

Code Snippet

Executing the query ...

A connection cannot be made. Ensure that the server is running.

Execution complete

Regards,

Inmon

|||

Inmon,

I've just got the message you sent me via my blog, but I can't reply because you're communication settings won't allow me. Can you contact me directly please? You can find my contact details at www.crossjoin.co.uk

Chris

|||

I Chris,

You ought to have the email right now. But you can write down the detail instructions about local cubes here at msdn if you want to. Thanks again for you help.

Creating local cubes from relational sources using XMLA

Hi,

I have looked at Chris Webb′s example in creating local cubes. I′m trying to follow his instructions but I think that the syntax or the code is incorrect somehow.

Code Snippet

CREATE GLOBAL CUBE statement. FILENAME|C:\myCube.cub|DDL|

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
</Parallel>

Hello Inmon,

pleae visit the blog entry again and read again:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!883.entry

It is not possible to create a local cube from relational data with CREATE GLOBAL CUBE Syntax.

I got the sample running.

HTH

J?rg

|||

Hi J?rg,

Then I would be happy if you told me how you solved it.

This is the example I followed by Chris:

· Open MDX Sample app - yes, this is another one of those areas where SQLMS won't do the job. Connect to the Adventure Works database

· Paste a CREATE GLOBAL CUBE statement into a query pane. Don't run it yet though...

· Start a new Profiler trace on your target database

· Run the CREATE GLOBAL CUBE statement, then stop the trace

· Inside your trace, you'll see the CREATE GLOBAL CUBE statement appear twice followed by a statement which begins with something like this: "CREATE GLOBAL CUBE statement. FILENAME|c:\MyfileName.cub|DDL|" and is followed by an XMLA batch statement

· Copy the XMLA batch statement out to MDX Sample App (if you find you can't do this, you might need to save the trace as an XML file and open it in Notepad or something) and

· Close the connection to your server cube in MDX Sample app, then reconnect to the path you want to create your new cub file at, eg "c:\cubtest.cub" (instead of supplying the name of your server in the connection dialog box).

· Run the Batch statement, and your local cube is created!

|||

Hi Inmon,

this is the sample for relational datasources

In SSMS script the database you've just created to a new query editor window. Still in SSMS, right-click on the database again and select Process, select 'Process Full' then instead of clicking OK click the Script button and choose to script to a new query editor window. You now have the two XMLA commands you need to create and process your local cube, you just need to combine them into one. Select all of the Create statement generated when you scripted the database and paste it into the Batch statement generated from the Process dialog, immediately after the first line (containing the 'Batch' command) and before the XMLA that actually does the processing. Copy the new statement, open up MDX Sample app, connect to the path of the new local cube file, paste the statement into MDX Sample app and then run it. And voila, your local cube will be built direct from the relational data source.|||

Hi Joschko,

I have read that example too. Combine the two scripts into one is no problem but I can′t connect to the path of my local cube. When I connect to my local cube I connect to Analysis Services and writing c:\myCube.cub in the server name field. The .cub file creates but following message appear:

Code Snippet

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.Xmla)

The 'c:\myCube.cub' local cube file cannot be opened. (Microsoft.AnalysisServices.Xmla)

The operation completed successfully (Microsoft.AnalysisServices.Xmla)

When I then run the script I recieve the following message:

Code Snippet

TITLE: Microsoft SQL Server Management Studio

Either the user, DOMAIN\userid, does not have access to the c:\myCube.cub database, or the database does not exist. (Microsoft SQL Server 2005 Analysis Services)

|||

Hi,

I have now succeeded with connect the script into one. Wpuld you like to confirm this?

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
</Parallel>
</Batch>

When I connect to my local cube through new query and select AS XMLA query and the run it I receive the following message:

Code Snippet

Executing the query ...

A connection cannot be made. Ensure that the server is running.

Execution complete

Regards,

Inmon

|||

Inmon,

I've just got the message you sent me via my blog, but I can't reply because you're communication settings won't allow me. Can you contact me directly please? You can find my contact details at www.crossjoin.co.uk

Chris

|||

I Chris,

You ought to have the email right now. But you can write down the detail instructions about local cubes here at msdn if you want to. Thanks again for you help.

Creating local cubes from relational sources using XMLA

Hi,

I have looked at Chris Webb′s example in creating local cubes. I′m trying to follow his instructions but I think that the syntax or the code is incorrect somehow.

Code Snippet

CREATE GLOBAL CUBE statement. FILENAME|C:\myCube.cub|DDL|

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
</Parallel>

Hello Inmon,

pleae visit the blog entry again and read again:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!883.entry

It is not possible to create a local cube from relational data with CREATE GLOBAL CUBE Syntax.

I got the sample running.

HTH

J?rg

|||

Hi J?rg,

Then I would be happy if you told me how you solved it.

This is the example I followed by Chris:

· Open MDX Sample app - yes, this is another one of those areas where SQLMS won't do the job. Connect to the Adventure Works database

· Paste a CREATE GLOBAL CUBE statement into a query pane. Don't run it yet though...

· Start a new Profiler trace on your target database

· Run the CREATE GLOBAL CUBE statement, then stop the trace

· Inside your trace, you'll see the CREATE GLOBAL CUBE statement appear twice followed by a statement which begins with something like this: "CREATE GLOBAL CUBE statement. FILENAME|c:\MyfileName.cub|DDL|" and is followed by an XMLA batch statement

· Copy the XMLA batch statement out to MDX Sample App (if you find you can't do this, you might need to save the trace as an XML file and open it in Notepad or something) and

· Close the connection to your server cube in MDX Sample app, then reconnect to the path you want to create your new cub file at, eg "c:\cubtest.cub" (instead of supplying the name of your server in the connection dialog box).

· Run the Batch statement, and your local cube is created!

|||

Hi Inmon,

this is the sample for relational datasources

In SSMS script the database you've just created to a new query editor window. Still in SSMS, right-click on the database again and select Process, select 'Process Full' then instead of clicking OK click the Script button and choose to script to a new query editor window. You now have the two XMLA commands you need to create and process your local cube, you just need to combine them into one. Select all of the Create statement generated when you scripted the database and paste it into the Batch statement generated from the Process dialog, immediately after the first line (containing the 'Batch' command) and before the XMLA that actually does the processing. Copy the new statement, open up MDX Sample app, connect to the path of the new local cube file, paste the statement into MDX Sample app and then run it. And voila, your local cube will be built direct from the relational data source.|||

Hi Joschko,

I have read that example too. Combine the two scripts into one is no problem but I can′t connect to the path of my local cube. When I connect to my local cube I connect to Analysis Services and writing c:\myCube.cub in the server name field. The .cub file creates but following message appear:

Code Snippet

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.Xmla)

The 'c:\myCube.cub' local cube file cannot be opened. (Microsoft.AnalysisServices.Xmla)

The operation completed successfully (Microsoft.AnalysisServices.Xmla)

When I then run the script I recieve the following message:

Code Snippet

TITLE: Microsoft SQL Server Management Studio

Either the user, DOMAIN\userid, does not have access to the c:\myCube.cub database, or the database does not exist. (Microsoft SQL Server 2005 Analysis Services)

|||

Hi,

I have now succeeded with connect the script into one. Wpuld you like to confirm this?

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
</Parallel>
</Batch>

When I connect to my local cube through new query and select AS XMLA query and the run it I receive the following message:

Code Snippet

Executing the query ...

A connection cannot be made. Ensure that the server is running.

Execution complete

Regards,

Inmon

|||

Inmon,

I've just got the message you sent me via my blog, but I can't reply because you're communication settings won't allow me. Can you contact me directly please? You can find my contact details at www.crossjoin.co.uk

Chris

|||

I Chris,

You ought to have the email right now. But you can write down the detail instructions about local cubes here at msdn if you want to. Thanks again for you help.