Credentials in Data Sources are getting locked out of SQL

Weird one this....

Created a report. Set up a new datasource. Using credentials securely stored in server. Set up a local user on the SQL Server specifically to reference through this datasource.

Go to reportserver, run the report, and it runs fine. Run it a couple more times, runs fine. Then, all of a sudden, it fails, with the error message

The referenced account is currently locked out and may not be logged on to. (Exception from HRESULT: 0x80070775)

This is very strange. I check the account on SQL, and it's still enabled. As it's not a domain account, it hasn't been disabled on the domain. I'm not even familiar with the phrase 'locked out'. After a bit of checking I find out about maxInvalidPasswordAttempts, whereby ASP 'locks out' accounts after 'n' wrong password attempts.

Firstly, I'm not entering the password wrong. It's correctly entered, and stored in the report server.

Secondly, where the heck does RS get off locking out accounts without my permission?

I have no idea how to unlock these accounts.

If I go ahead and create another account on the SQL, exactly the same thing happens. I can reproduce this at will. It works the first couple of times, then bang, 'locked out' again.

Anyone got any ideas on how to modify this behavoir?I am getting the same error, all of a sudden, does anyone have a clue how to fix this?|||I am having the same problem. Does anyone know if this is a SP2 problem? Please help!

|||Hi
How did you create a user to access the database and this user is not a domain user? If this user is not a sql login user then the OS will lock ou the user and thus the dB lockout. If the user is a non domain user then the OS is trying netuse the account and this will definately lock it out.

gatharia|||

|||

I've solved part of my troubles. I had set up a shared schedule and accidentally was using the default recurrance pattern to start every 10 minutes. This can happen very easily, as when you go to test something you might want to run it once. So... you create a new shared schedule (so you don't end up running everything on your production shared schedule). I don't know why the default is set to this recurrance pattern instead of once. Anyway... I was having the same problem as Sam Loud, but it was not caused by what I was currently doing. It was caused by this recurring "thing" that was running with the wrong password.

Another "Thing" that caused this problem is when I tried to use shared data sources. As far as I can tell, you cannot use shared data sources when trying to use subscriptions to fileshare. I think this is a bug with reportserver. The first time it runs, it will run successfully. The second time it runs, you will get the RSLogonFailed. I don't know any way around this problem other than to just not use shared data sources.

The other thing that has caused me problems is that when I modify a report and deploy it, make sure not to overwrite the datasource (note: this is the default setting in IIS). If you overwrite the datasource, then the userid and password that you set up on the datasource on the server gets wiped out and everything bombs out on logon and again you have a lockout situation.

I am still having some lockouts and still working on this problem. If anyone else can let me know what their experiences are that would be very helpful. I am using subscriptions to fileshare with about 200 reports running on various schedules. Thanks!

Credentials in Data Sources are getting locked out of SQL

Weird one this....

Created a report. Set up a new datasource. Using credentials securely stored in server. Set up a local user on the SQL Server specifically to reference through this datasource.

Go to reportserver, run the report, and it runs fine. Run it a couple more times, runs fine. Then, all of a sudden, it fails, with the error message

The referenced account is currently locked out and may not be logged on to. (Exception from HRESULT: 0x80070775) This is very strange. I check the account on SQL, and it's still enabled. As it's not a domain account, it hasn't been disabled on the domain. I'm not even familiar with the phrase 'locked out'. After a bit of checking I find out about maxInvalidPasswordAttempts, whereby ASP 'locks out' accounts after 'n' wrong password attempts.

Firstly, I'm not entering the password wrong. It's correctly entered, and stored in the report server.

Secondly, where the heck does RS get off locking out accounts without my permission?

I have no idea how to unlock these accounts.

If I go ahead and create another account on the SQL, exactly the same thing happens. I can reproduce this at will. It works the first couple of times, then bang, 'locked out' again.

Anyone got any ideas on how to modify this behavoir?
I am getting the same error, all of a sudden, does anyone have a clue how to fix this?|||I am having the same problem. Does anyone know if this is a SP2 problem? Please help!|||Hi
How did you create a user to access the database and this user is not a domain user? If this user is not a sql login user then the OS will lock ou the user and thus the dB lockout. If the user is a non domain user then the OS is trying netuse the account and this will definately lock it out.

gatharia
|||

I've solved part of my troubles. I had set up a shared schedule and accidentally was using the default recurrance pattern to start every 10 minutes. This can happen very easily, as when you go to test something you might want to run it once. So... you create a new shared schedule (so you don't end up running everything on your production shared schedule). I don't know why the default is set to this recurrance pattern instead of once. Anyway... I was having the same problem as Sam Loud, but it was not caused by what I was currently doing. It was caused by this recurring "thing" that was running with the wrong password.

Another "Thing" that caused this problem is when I tried to use shared data sources. As far as I can tell, you cannot use shared data sources when trying to use subscriptions to fileshare. I think this is a bug with reportserver. The first time it runs, it will run successfully. The second time it runs, you will get the RSLogonFailed. I don't know any way around this problem other than to just not use shared data sources.

The other thing that has caused me problems is that when I modify a report and deploy it, make sure not to overwrite the datasource (note: this is the default setting in IIS). If you overwrite the datasource, then the userid and password that you set up on the datasource on the server gets wiped out and everything bombs out on logon and again you have a lockout situation.

I am still having some lockouts and still working on this problem. If anyone else can let me know what their experiences are that would be very helpful. I am using subscriptions to fileshare with about 200 reports running on various schedules. Thanks!

|||

The final (and most difficult to resolve) problem that was causing the "logon Failed" and then account lockout problem(s) was related to the windows account that expired (it was set up to expire in 90 days). Since I used this userid when setting up my report subscriptions, all of my subscriptions failed with invalid login and then the account was locked out. When the account userid was reactivated in WINDOWS with the same password, still all of the subscriptions failed on login. This was very frustrating because the main report services account was getting locked every day and nobody could get to the report server and all the report jobs were failing. I tried going into each report subscription and retyping the userid and password (which were valid), but I still got the invalid login. After 3 weeks of frustration and trying different things, I have discovered that what you have to do is 1) go in to the subscription general screen and change the userid and password to something invalid and totally different 2) go out of the subscription general screen (to save your changes) and 3) go back into the subscription general screen and re-type the correct userid and password. This resolved all of my login and lockout problems with the subscriptions and writing to datashare. I hope that this helps anyone else out there who might be struggling with this same issue/problem with reportserver and subscriptions. It is obviously a bug.

Credentials in Data Sources are getting locked out of SQL

Weird one this....

Created a report. Set up a new datasource. Using credentials securely stored in server. Set up a local user on the SQL Server specifically to reference through this datasource.

Go to reportserver, run the report, and it runs fine. Run it a couple more times, runs fine. Then, all of a sudden, it fails, with the error message

The referenced account is currently locked out and may not be logged on to. (Exception from HRESULT: 0x80070775) This is very strange. I check the account on SQL, and it's still enabled. As it's not a domain account, it hasn't been disabled on the domain. I'm not even familiar with the phrase 'locked out'. After a bit of checking I find out about maxInvalidPasswordAttempts, whereby ASP 'locks out' accounts after 'n' wrong password attempts.

Firstly, I'm not entering the password wrong. It's correctly entered, and stored in the report server.

Secondly, where the heck does RS get off locking out accounts without my permission?

I have no idea how to unlock these accounts.

If I go ahead and create another account on the SQL, exactly the same thing happens. I can reproduce this at will. It works the first couple of times, then bang, 'locked out' again.

Anyone got any ideas on how to modify this behavoir?
I am getting the same error, all of a sudden, does anyone have a clue how to fix this?|||I am having the same problem. Does anyone know if this is a SP2 problem? Please help!|||Hi
How did you create a user to access the database and this user is not a domain user? If this user is not a sql login user then the OS will lock ou the user and thus the dB lockout. If the user is a non domain user then the OS is trying netuse the account and this will definately lock it out.

gatharia
|||

I've solved part of my troubles. I had set up a shared schedule and accidentally was using the default recurrance pattern to start every 10 minutes. This can happen very easily, as when you go to test something you might want to run it once. So... you create a new shared schedule (so you don't end up running everything on your production shared schedule). I don't know why the default is set to this recurrance pattern instead of once. Anyway... I was having the same problem as Sam Loud, but it was not caused by what I was currently doing. It was caused by this recurring "thing" that was running with the wrong password.

Another "Thing" that caused this problem is when I tried to use shared data sources. As far as I can tell, you cannot use shared data sources when trying to use subscriptions to fileshare. I think this is a bug with reportserver. The first time it runs, it will run successfully. The second time it runs, you will get the RSLogonFailed. I don't know any way around this problem other than to just not use shared data sources.

The other thing that has caused me problems is that when I modify a report and deploy it, make sure not to overwrite the datasource (note: this is the default setting in IIS). If you overwrite the datasource, then the userid and password that you set up on the datasource on the server gets wiped out and everything bombs out on logon and again you have a lockout situation.

I am still having some lockouts and still working on this problem. If anyone else can let me know what their experiences are that would be very helpful. I am using subscriptions to fileshare with about 200 reports running on various schedules. Thanks!

|||

The final (and most difficult to resolve) problem that was causing the "logon Failed" and then account lockout problem(s) was related to the windows account that expired (it was set up to expire in 90 days). Since I used this userid when setting up my report subscriptions, all of my subscriptions failed with invalid login and then the account was locked out. When the account userid was reactivated in WINDOWS with the same password, still all of the subscriptions failed on login. This was very frustrating because the main report services account was getting locked every day and nobody could get to the report server and all the report jobs were failing. I tried going into each report subscription and retyping the userid and password (which were valid), but I still got the invalid login. After 3 weeks of frustration and trying different things, I have discovered that what you have to do is 1) go in to the subscription general screen and change the userid and password to something invalid and totally different 2) go out of the subscription general screen (to save your changes) and 3) go back into the subscription general screen and re-type the correct userid and password. This resolved all of my login and lockout problems with the subscriptions and writing to datashare. I hope that this helps anyone else out there who might be struggling with this same issue/problem with reportserver and subscriptions. It is obviously a bug.

Credentials in Data Sources are getting locked out of SQL

Weird one this....

Created a report. Set up a new datasource. Using credentials securely stored in server. Set up a local user on the SQL Server specifically to reference through this datasource.

Go to reportserver, run the report, and it runs fine. Run it a couple more times, runs fine. Then, all of a sudden, it fails, with the error message

The referenced account is currently locked out and may not be logged on to. (Exception from HRESULT: 0x80070775) This is very strange. I check the account on SQL, and it's still enabled. As it's not a domain account, it hasn't been disabled on the domain. I'm not even familiar with the phrase 'locked out'. After a bit of checking I find out about maxInvalidPasswordAttempts, whereby ASP 'locks out' accounts after 'n' wrong password attempts.

Firstly, I'm not entering the password wrong. It's correctly entered, and stored in the report server.

Secondly, where the heck does RS get off locking out accounts without my permission?

I have no idea how to unlock these accounts.

If I go ahead and create another account on the SQL, exactly the same thing happens. I can reproduce this at will. It works the first couple of times, then bang, 'locked out' again.

Anyone got any ideas on how to modify this behavoir?
I am getting the same error, all of a sudden, does anyone have a clue how to fix this?|||I am having the same problem. Does anyone know if this is a SP2 problem? Please help!|||Hi
How did you create a user to access the database and this user is not a domain user? If this user is not a sql login user then the OS will lock ou the user and thus the dB lockout. If the user is a non domain user then the OS is trying netuse the account and this will definately lock it out.

gatharia
|||

I've solved part of my troubles. I had set up a shared schedule and accidentally was using the default recurrance pattern to start every 10 minutes. This can happen very easily, as when you go to test something you might want to run it once. So... you create a new shared schedule (so you don't end up running everything on your production shared schedule). I don't know why the default is set to this recurrance pattern instead of once. Anyway... I was having the same problem as Sam Loud, but it was not caused by what I was currently doing. It was caused by this recurring "thing" that was running with the wrong password.

Another "Thing" that caused this problem is when I tried to use shared data sources. As far as I can tell, you cannot use shared data sources when trying to use subscriptions to fileshare. I think this is a bug with reportserver. The first time it runs, it will run successfully. The second time it runs, you will get the RSLogonFailed. I don't know any way around this problem other than to just not use shared data sources.

The other thing that has caused me problems is that when I modify a report and deploy it, make sure not to overwrite the datasource (note: this is the default setting in IIS). If you overwrite the datasource, then the userid and password that you set up on the datasource on the server gets wiped out and everything bombs out on logon and again you have a lockout situation.

I am still having some lockouts and still working on this problem. If anyone else can let me know what their experiences are that would be very helpful. I am using subscriptions to fileshare with about 200 reports running on various schedules. Thanks!

|||

The final (and most difficult to resolve) problem that was causing the "logon Failed" and then account lockout problem(s) was related to the windows account that expired (it was set up to expire in 90 days). Since I used this userid when setting up my report subscriptions, all of my subscriptions failed with invalid login and then the account was locked out. When the account userid was reactivated in WINDOWS with the same password, still all of the subscriptions failed on login. This was very frustrating because the main report services account was getting locked every day and nobody could get to the report server and all the report jobs were failing. I tried going into each report subscription and retyping the userid and password (which were valid), but I still got the invalid login. After 3 weeks of frustration and trying different things, I have discovered that what you have to do is 1) go in to the subscription general screen and change the userid and password to something invalid and totally different 2) go out of the subscription general screen (to save your changes) and 3) go back into the subscription general screen and re-type the correct userid and password. This resolved all of my login and lockout problems with the subscriptions and writing to datashare. I hope that this helps anyone else out there who might be struggling with this same issue/problem with reportserver and subscriptions. It is obviously a bug.

Credentials in Data Sources are getting locked out of SQL

Weird one this....

Created a report. Set up a new datasource. Using credentials securely stored in server. Set up a local user on the SQL Server specifically to reference through this datasource.

Go to reportserver, run the report, and it runs fine. Run it a couple more times, runs fine. Then, all of a sudden, it fails, with the error message

The referenced account is currently locked out and may not be logged on to. (Exception from HRESULT: 0x80070775)

This is very strange. I check the account on SQL, and it's still enabled. As it's not a domain account, it hasn't been disabled on the domain. I'm not even familiar with the phrase 'locked out'. After a bit of checking I find out about maxInvalidPasswordAttempts, whereby ASP 'locks out' accounts after 'n' wrong password attempts.

Firstly, I'm not entering the password wrong. It's correctly entered, and stored in the report server.

Secondly, where the heck does RS get off locking out accounts without my permission?

I have no idea how to unlock these accounts.

If I go ahead and create another account on the SQL, exactly the same thing happens. I can reproduce this at will. It works the first couple of times, then bang, 'locked out' again.

Anyone got any ideas on how to modify this behavoir?
I am getting the same error, all of a sudden, does anyone have a clue how to fix this?|||I am having the same problem. Does anyone know if this is a SP2 problem? Please help!

|||Hi
How did you create a user to access the database and this user is not a domain user? If this user is not a sql login user then the OS will lock ou the user and thus the dB lockout. If the user is a non domain user then the OS is trying netuse the account and this will definately lock it out.

gatharia
|||

|||

I've solved part of my troubles. I had set up a shared schedule and accidentally was using the default recurrance pattern to start every 10 minutes. This can happen very easily, as when you go to test something you might want to run it once. So... you create a new shared schedule (so you don't end up running everything on your production shared schedule). I don't know why the default is set to this recurrance pattern instead of once. Anyway... I was having the same problem as Sam Loud, but it was not caused by what I was currently doing. It was caused by this recurring "thing" that was running with the wrong password.

Another "Thing" that caused this problem is when I tried to use shared data sources. As far as I can tell, you cannot use shared data sources when trying to use subscriptions to fileshare. I think this is a bug with reportserver. The first time it runs, it will run successfully. The second time it runs, you will get the RSLogonFailed. I don't know any way around this problem other than to just not use shared data sources.

The other thing that has caused me problems is that when I modify a report and deploy it, make sure not to overwrite the datasource (note: this is the default setting in IIS). If you overwrite the datasource, then the userid and password that you set up on the datasource on the server gets wiped out and everything bombs out on logon and again you have a lockout situation.

I am still having some lockouts and still working on this problem. If anyone else can let me know what their experiences are that would be very helpful. I am using subscriptions to fileshare with about 200 reports running on various schedules. Thanks!

|||

The final (and most difficult to resolve) problem that was causing the "logon Failed" and then account lockout problem(s) was related to the windows account that expired (it was set up to expire in 90 days). Since I used this userid when setting up my report subscriptions, all of my subscriptions failed with invalid login and then the account was locked out. When the account userid was reactivated in WINDOWS with the same password, still all of the subscriptions failed on login. This was very frustrating because the main report services account was getting locked every day and nobody could get to the report server and all the report jobs were failing. I tried going into each report subscription and retyping the userid and password (which were valid), but I still got the invalid login. After 3 weeks of frustration and trying different things, I have discovered that what you have to do is 1) go in to the subscription general screen and change the userid and password to something invalid and totally different 2) go out of the subscription general screen (to save your changes) and 3) go back into the subscription general screen and re-type the correct userid and password. This resolved all of my login and lockout problems with the subscriptions and writing to datashare. I hope that this helps anyone else out there who might be struggling with this same issue/problem with reportserver and subscriptions. It is obviously a bug.

Credentials for Data Sources - Changing Authentication

I have Visual Studio installed on my local laptop, as well as on the report server. The only place reporting services is installed is on the report server.

I have a solution called “Performance Monitoring”, and within that solution is a report of the same name. That report uses 2 data sets, “DSOne” and “DSTwo” and these data sets are pulled from 2 shared data sources. I can edit the report locally and preview it just fine and the results are what we’re looking for.

Right now, the data sources are all set to Windows authentication. We would like to tie the data sources to a specific user ID and password, so that I or anybody else is able to view the report without having to have a separate login for all those people. I have tried changing the data sources' properties in Visual Studio to reflect using a specific login/pass (my own). I have tried this in Report Manager too – editing the data sources there to use my login and pass. Both of these methods have not worked.

In Visual Studio, in the Solution Explorer, I double click on one of the data sources and go to the credentials tab. “Use Windows Authentication” is checked, so I check “use a specific user name and password” instead and then enter my login and password. When I try to preview the report, it gives me the following error and I am stumped as to why this is:

“An error has occurred during report processing. Cannot create a connection to data source “DSTwo”. Login failed for user “xxx”.”

I have also tried editing the login and password through the “edit” button on the “general” tab of whatever data source I’m working with. The “Use Windows NT Integrated Security” box is checked, so I check the “Use a Specific Name and Password” box and again, type in my login info. When I hit “Test Connection”, I get the following:

“Test connection failed because of an error in initializing provider. Login failed for user ‘xxx.”

Hopefully this provided enough detail as to what the heck is going on here. I’ve been playing with these processes for a few days and for whatever reason, it is not working. I’m hoping somebody may have some insight as to why this is.

Thanks,
Matt

If you're using stored windows credentials, you need to check the "use as windows credentials" checkbox on the data source properties page.

-Lukasz

Credentials for Data Sources - Changing Authentication

I have Visual Studio installed on my local laptop, as well as on the report server. The only place reporting services is installed is on the report server.

I have a solution called “Performance Monitoring”, and within that solution is a report of the same name. That report uses 2 data sets, “DSOne” and “DSTwo” and these data sets are pulled from 2 shared data sources. I can edit the report locally and preview it just fine and the results are what we’re looking for.

Right now, the data sources are all set to Windows authentication. We would like to tie the data sources to a specific user ID and password, so that I or anybody else is able to view the report without having to have a separate login for all those people. I have tried changing the data sources' properties in Visual Studio to reflect using a specific login/pass (my own). I have tried this in Report Manager too – editing the data sources there to use my login and pass. Both of these methods have not worked.

In Visual Studio, in the Solution Explorer, I double click on one of the data sources and go to the credentials tab. “Use Windows Authentication” is checked, so I check “use a specific user name and password” instead and then enter my login and password. When I try to preview the report, it gives me the following error and I am stumped as to why this is:

“An error has occurred during report processing. Cannot create a connection to data source “DSTwo”. Login failed for user “xxx”.”

I have also tried editing the login and password through the “edit” button on the “general” tab of whatever data source I’m working with. The “Use Windows NT Integrated Security” box is checked, so I check the “Use a Specific Name and Password” box and again, type in my login info. When I hit “Test Connection”, I get the following:

“Test connection failed because of an error in initializing provider. Login failed for user ‘xxx.”

Hopefully this provided enough detail as to what the heck is going on here. I’ve been playing with these processes for a few days and for whatever reason, it is not working. I’m hoping somebody may have some insight as to why this is.

Thanks,
Matt

If you're using stored windows credentials, you need to check the "use as windows credentials" checkbox on the data source properties page.

-Lukasz

Creating VirtualDeviceSet for Remote SQL Server 2005 failed.

Hi, all!

I am writing the VDI application to allow backup/restore MS SQL Server 2005. I would want to backup/restore remote servers as well as local. I have local instance of MS SQL 2005 and remote. Local instance has MSSQL2005_ZORG instance name, remote uses default (so it supposed to be MSSQLSERVER).

While connection to those server via SQL Server Management studio i see local server as "ZORG\MSSQL2005_ZORG" and remote as "VM2000SRVZ2".

When i try to create VIrtualDeviceSet via CreateEx i pass "MSSQL2005_ZORG" as lpInstanceName parameter and all works fine. But I could't create same device set for remote 'MSSQLSERVER' instance. I passed any combination for that, such as "VM2000SRVZ2\MSSQLSERVER", "MSSQLSERVER", "VM2000SRVZ2", "\\VM2000SRVZ2\MSSQLSERVER" and so on. No luck always get VD_E_INSTANCE_NAME (0x80770007).

Any idea?

--Thanks

Hi,
As per the Microsoft Virtual Backup specifications, VDI can be used only on local machines.You can download the specifications from

http://www.microsoft.com/downloads/details.aspx?familyid=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&displaylang=en

If u have used VDI for some time, i need ur help.Relpy if you can

Creating VirtualDeviceSet for Remote SQL Server 2005 failed.

Hi, all!

I am writing the VDI application to allow backup/restore MS SQL Server 2005. I would want to backup/restore remote servers as well as local. I have local instance of MS SQL 2005 and remote. Local instance has MSSQL2005_ZORG instance name, remote uses default (so it supposed to be MSSQLSERVER).

While connection to those server via SQL Server Management studio i see local server as "ZORG\MSSQL2005_ZORG" and remote as "VM2000SRVZ2".

When i try to create VIrtualDeviceSet via CreateEx i pass "MSSQL2005_ZORG" as lpInstanceName parameter and all works fine. But I could't create same device set for remote 'MSSQLSERVER' instance. I passed any combination for that, such as "VM2000SRVZ2\MSSQLSERVER", "MSSQLSERVER", "VM2000SRVZ2", "\\VM2000SRVZ2\MSSQLSERVER" and so on. No luck always get VD_E_INSTANCE_NAME (0x80770007).

Any idea?

--Thanks

Hi,
As per the Microsoft Virtual Backup specifications, VDI can be used only on local machines.You can download the specifications from

http://www.microsoft.com/downloads/details.aspx?familyid=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&displaylang=en

If u have used VDI for some time, i need ur help.Relpy if you can

Creating Reports

In Microsoft access , Im using local tables or report
tables to create a repor that I manipulate from the Main
database which is a Access database also, how I can make
a report for SQL Server database
What about creating linked tables in Access, did you try this alreads ?
One Possibility is : If you are using a DSN u can link these objects in
Access via a ODBC Datasource
HTH, Jens Smeyer.
http://www.sqlserver2005.de
"Arnel Lizardo" <arnellizardo@.yahoo.com> schrieb im Newsbeitrag
news:047401c54007$7be52250$a401280a@.phx.gbl...
> In Microsoft access , Im using local tables or report
> tables to create a repor that I manipulate from the Main
> database which is a Access database also, how I can make
> a report for SQL Server database

Creating Reports

In Microsoft access , Im using local tables or report
tables to create a repor that I manipulate from the Main
database which is a Access database also, how I can make
a report for SQL Server databaseWhat about creating linked tables in Access, did you try this alreads ?
One Possibility is : If you are using a DSN u can link these objects in
Access via a ODBC Datasource
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Arnel Lizardo" <arnellizardo@.yahoo.com> schrieb im Newsbeitrag
news:047401c54007$7be52250$a401280a@.phx.gbl...
> In Microsoft access , Im using local tables or report
> tables to create a repor that I manipulate from the Main
> database which is a Access database also, how I can make
> a report for SQL Server database

Creating Report on Local XML File

Hey All,

I am attempting to create a report on SQL Reporting Services that uses a local XML file as its datasource. Although i did not find a way to directly do this, I found an MSDN article that walks a person through on how to use a report parameter to paste in your XML data from the page. When I set up the report in this way, I get an "Invalid URI" error when I go to the preview tab.

http://msdn2.microsoft.com/en-us/library/aa964129.aspx

What would be the best way for me to use an XML file as a datasource using reporting services? Is there a way that I can use a local file and use a parameter to point at that file?

Any info, links, tuts, etc. would help. Thanks!

Nathan

The XML Provider you are testing requires an url-addresable file, e.g. putting the file in wwwroot and using http://filename as URL.

Alternatively, you could write a custom data extension, as explained in this article. Another article to check is this.

Creating offline (local) cubes using XMLA, or any other way..

Hi,

I realise this subject has been discussed a lot but there seems to be no definitive answer.

The company I work for creates many cubes (currently using many SQL statements) for the sales regions at the end of each month and we need to move to a better platform, hence analysis services projects.

Is there any "easy" way to take a cube created by an analysis services project and save it to an offline (local) cube?

Currently I am trying to use VB.NET 2005 to process the XMLA definition from AS2005, which is easy to get at.

The VB code is:

Dim myClient As New Microsoft.AnalysisServices.Xmla.XmlaClient

Dim l_result, l_XmlaCommand As String

l_result = ""

Try

REMDim l_connect As String = "Provider=MSOLAP.3;Data Source=L00086SINEY;Initial Catalog=Beta1;Integrated Security=SSPI "

Dim l_connect As String = "Provider=MSOLAP.3;Data Source=c:\inetpub\wwwroot\xmla\BetaTest.cub;Integrated Security=SSPI;UseExistingFile=False"

myClient.Connect(l_connect)

l_XmlaCommand = "<Statement>begin transaction</Statement>"

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

Dim FS As New IO.StreamReader("C:\XMLA_Command.txt")

l_XmlaCommand = FS.ReadToEnd

FS.Close()

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

l_XmlaCommand = "<Statement>commit transaction</Statement>"

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

Catch ex As Exception

MsgBox("Error in Client connection")

Finally

myClient.Disconnect()

End Try

The XMLA_Command.txt (first few lines) is:

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

<ParentObject>

<DatabaseID>http://localhost/xmla/testcube.cub</DatabaseID>

</ParentObject>

<ObjectDefinition>

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

<ID>Beta1</ID>

<Name>Beta1</Name>

<Annotations>

<Annotation>

and finally the error I get is:

Errors in the metadata manager. Either the database with the ID of 'http://localhost/xmla/testcube.cub' does not exist in the server with the ID of 'L00086SINEY\LocalCube', or the user does not have permissions to access the object.

The cube is created fine when pointed at the Analysis Server but not when a local target is used.

Apologies for the length of post but it explains where I am up to.

Obviously if there is a "create local cube" wizard that I'm missing in AS2005 then I'll be more than happy to abandon the project!

Any help, even if it's book recommendations, would be greatly appreciated.

Thanks,

John.

Follow up:

Sorted the answer myself with a little (OK lot!) of help from a book by Teo Lachev, Applied Microsoft Analysis Services 2005. it uses code so it won't be ideal for everyone but it works and is a lot simpler than trying to work out how XMLA works in AS2005 with local cubes, especially as even xmla.org now seems largely forgotten.

By using the code from the above book you can point the created program to an existing cube created using AS2005 within the BIDS package and output it to a local cube file, very simple.

A big thank you goes out to Teo as days of searching have now finished.

|||John

I'm deeply interested in any solution for creating offline cubes programmatically.

How did you solve your problem?

I managed to create them in general using "CREATE GLOBAL CUBE"-syntax but
I can not ignore processing errors like I can do when using the management console.

Any answer is appreciated

Regards
Klaus Wiesel|||The sample in Teo's book is an application that will build the "CREATE GLOBAL CUBE" command, so it probably will not help you much, there were no options for setting the error configuration. Teo maintains a website at www.prologika.com|||I have put the MDX statment "Create Cube ... " in SQL Agent , and it gives me errors, i want to schedule the creation of the cube , not do it maunally. any help?

Creating offline (local) cubes using XMLA, or any other way..

Hi,

I realise this subject has been discussed a lot but there seems to be no definitive answer.

The company I work for creates many cubes (currently using many SQL statements) for the sales regions at the end of each month and we need to move to a better platform, hence analysis services projects.

Is there any "easy" way to take a cube created by an analysis services project and save it to an offline (local) cube?

Currently I am trying to use VB.NET 2005 to process the XMLA definition from AS2005, which is easy to get at.

The VB code is:

Dim myClient As New Microsoft.AnalysisServices.Xmla.XmlaClient

Dim l_result, l_XmlaCommand As String

l_result = ""

Try

REMDim l_connect As String = "Provider=MSOLAP.3;Data Source=L00086SINEY;Initial Catalog=Beta1;Integrated Security=SSPI "

Dim l_connect As String = "Provider=MSOLAP.3;Data Source=c:\inetpub\wwwroot\xmla\BetaTest.cub;Integrated Security=SSPI;UseExistingFile=False"

myClient.Connect(l_connect)

l_XmlaCommand = "<Statement>begin transaction</Statement>"

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

Dim FS As New IO.StreamReader("C:\XMLA_Command.txt")

l_XmlaCommand = FS.ReadToEnd

FS.Close()

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

l_XmlaCommand = "<Statement>commit transaction</Statement>"

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

Catch ex As Exception

MsgBox("Error in Client connection")

Finally

myClient.Disconnect()

End Try

The XMLA_Command.txt (first few lines) is:

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

<ParentObject>

<DatabaseID>http://localhost/xmla/testcube.cub</DatabaseID>

</ParentObject>

<ObjectDefinition>

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

<ID>Beta1</ID>

<Name>Beta1</Name>

<Annotations>

<Annotation>

and finally the error I get is:

Errors in the metadata manager. Either the database with the ID of 'http://localhost/xmla/testcube.cub' does not exist in the server with the ID of 'L00086SINEY\LocalCube', or the user does not have permissions to access the object.

The cube is created fine when pointed at the Analysis Server but not when a local target is used.

Apologies for the length of post but it explains where I am up to.

Obviously if there is a "create local cube" wizard that I'm missing in AS2005 then I'll be more than happy to abandon the project!

Any help, even if it's book recommendations, would be greatly appreciated.

Thanks,

John.

Follow up:

Sorted the answer myself with a little (OK lot!) of help from a book by Teo Lachev, Applied Microsoft Analysis Services 2005. it uses code so it won't be ideal for everyone but it works and is a lot simpler than trying to work out how XMLA works in AS2005 with local cubes, especially as even xmla.org now seems largely forgotten.

By using the code from the above book you can point the created program to an existing cube created using AS2005 within the BIDS package and output it to a local cube file, very simple.

A big thank you goes out to Teo as days of searching have now finished.

|||John

I'm deeply interested in any solution for creating offline cubes programmatically.

How did you solve your problem?

I managed to create them in general using "CREATE GLOBAL CUBE"-syntax but
I can not ignore processing errors like I can do when using the management console.

Any answer is appreciated

Regards
Klaus Wiesel|||The sample in Teo's book is an application that will build the "CREATE GLOBAL CUBE" command, so it probably will not help you much, there were no options for setting the error configuration. Teo maintains a website at www.prologika.com|||I have put the MDX statment "Create Cube ... " in SQL Agent , and it gives me errors, i want to schedule the creation of the cube , not do it maunally. any help?

Creating offline (local) cubes using XMLA, or any other way..

Hi,

I realise this subject has been discussed a lot but there seems to be no definitive answer.

The company I work for creates many cubes (currently using many SQL statements) for the sales regions at the end of each month and we need to move to a better platform, hence analysis services projects.

Is there any "easy" way to take a cube created by an analysis services project and save it to an offline (local) cube?

Currently I am trying to use VB.NET 2005 to process the XMLA definition from AS2005, which is easy to get at.

The VB code is:

Dim myClientAsNew Microsoft.AnalysisServices.Xmla.XmlaClient

Dim l_result, l_XmlaCommandAsString

l_result =""

Try

REMDim l_connect As String = "Provider=MSOLAP.3;Data Source=L00086SINEY;Initial Catalog=Beta1;Integrated Security=SSPI "

Dim l_connectAsString ="Provider=MSOLAP.3;Data Source=c:\inetpub\wwwroot\xmla\BetaTest.cub;Integrated Security=SSPI;UseExistingFile=False"

myClient.Connect(l_connect)

l_XmlaCommand ="<Statement>begin transaction</Statement>"

myClient.Execute(l_XmlaCommand,"", l_result,False,True)

Dim FSAsNew IO.StreamReader("C:\XMLA_Command.txt")

l_XmlaCommand = FS.ReadToEnd

FS.Close()

myClient.Execute(l_XmlaCommand,"", l_result,False,True)

l_XmlaCommand ="<Statement>commit transaction</Statement>"

myClient.Execute(l_XmlaCommand,"", l_result,False,True)

Catch exAs Exception

MsgBox("Error in Client connection")

Finally

myClient.Disconnect()

EndTry

The XMLA_Command.txt (first few lines) is:

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

<ParentObject>

<DatabaseID>http://localhost/xmla/testcube.cub</DatabaseID>

</ParentObject>

<ObjectDefinition>

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

<ID>Beta1</ID>

<Name>Beta1</Name>

<Annotations>

<Annotation>

and finally the error I get is:

Errors in the metadata manager. Either the database with the ID of 'http://localhost/xmla/testcube.cub' does not exist in the server with the ID of 'L00086SINEY\LocalCube', or the user does not have permissions to access the object.

The cube is created fine when pointed at the Analysis Server but not when a local target is used.

Apologies for the length of post but it explains where I am up to.

Obviously if there is a "create local cube" wizard that I'm missing in AS2005 then I'll be more than happy to abandon the project!

Any help, even if it's book recommendations, would be greatly appreciated.

Thanks,

John.

Follow up:

Sorted the answer myself with a little (OK lot!) of help from a book by Teo Lachev, Applied Microsoft Analysis Services 2005. it uses code so it won't be ideal for everyone but it works and is a lot simpler than trying to work out how XMLA works in AS2005 with local cubes, especially as even xmla.org now seems largely forgotten.

By using the code from the above book you can point the created program to an existing cube created using AS2005 within the BIDS package and output it to a local cube file, very simple.

A big thank you goes out to Teo as days of searching have now finished.

|||John

I'm deeply interested in any solution for creating offline cubes programmatically.

How did you solve your problem?
I managed to create them in general using "CREATE GLOBAL CUBE"-syntax but
I can not ignore processing errors like I can do when using the management console.

Any answer is appreciated

Regards
Klaus Wiesel

|||The sample in Teo's book is an application that will build the "CREATE GLOBAL CUBE" command, so it probably will not help you much, there were no options for setting the error configuration. Teo maintains a website at www.prologika.com|||I have put the MDX statment "Create Cube ... " in SQL Agent , and it gives me errors, i want to schedule the creation of the cube , not do it maunally. any help?

creating local table from SP

Hi ,
i am using Ms Access to connect to SQL SERVER, i am running some SP that
create the local tables in SQL Server , but is there anyway for me to change
the SP such that the "local tables" will be created in MS Access instead ?
tks & rdgs
Hi
The SP runs inside SQL Server, and it does not know, or care about the
Access Front-End. A SP can not modify a local Access DB, unless SQL Server is
linked to it (no Access linked to SQL Server)
Regards
Mike
"maxzsim" wrote:

> Hi ,
> i am using Ms Access to connect to SQL SERVER, i am running some SP that
> create the local tables in SQL Server , but is there anyway for me to change
> the SP such that the "local tables" will be created in MS Access instead ?
> tks & rdgs

creating local table from SP

Hi ,
i am using Ms Access to connect to SQL SERVER, i am running some SP that
create the local tables in SQL Server , but is there anyway for me to change
the SP such that the "local tables" will be created in MS Access instead ?
tks & rdgsHi
The SP runs inside SQL Server, and it does not know, or care about the
Access Front-End. A SP can not modify a local Access DB, unless SQL Server is
linked to it (no Access linked to SQL Server)
Regards
Mike
"maxzsim" wrote:
> Hi ,
> i am using Ms Access to connect to SQL SERVER, i am running some SP that
> create the local tables in SQL Server , but is there anyway for me to change
> the SP such that the "local tables" will be created in MS Access instead ?
> tks & rdgs

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.