Data Source View and Report Model

Guys,

I created a Data Source View that uses two different Data Source connections to two different databases. I was able to drop in the tables in the data source view designer from the two databases, but when I try to create the Report Model based on that data source view, it's complaining that it can't find table in the non-primary data source from the data source view.

Here is the error I'm getting:

An error occurred while executing a command.

Message: Invalid object name 'dbo.[tablename]'

Command:

SELECT COUNT(*) FROM [dbo].[tablename] t

Why can't it find the database to connect to since the data source view is already creating using two different databases. No problem in the data source view, but when it actually becomes a model, it complains. How come it can't find one of the tables in a different database?

Please help and any help is appreciated.

Enkh.

semantic queries currently do not support multiple data sources. you can create named query that would reference table from another database using db.schema.tablename notation.

Data SOurce View and Missing Diagrams

Guys, I am a newbie working through the SSAS tutorial. I am at lesson 5 and something has gone wrong with my Data Source View. Although I have diagrams in the Diagram Source pane I can not get them to show in the actual diagram pane. That pane just shows a grey background rather than the cream canvas. New diagrams in that data source view show the same problem.

If I create a new Data Sorce View, I can create diagrams and see them.

I do not want to create a new Data Sorce View and abandon the old one, because it will break the tutorial. I do not have a record of everything that was in the old one. Neither do I know how to repoint my cube at the new Data Sorce View.

Can someone please tell me how to restore the view of the diagrams.

Thanks

Cliff

How about re-installing the tutorial files? It is hard to tell what was wrong, but you might want to send the broken solution to andrewgaATnetzeroDOTcom and i will take a look.

In order to change the data source view for the cube you can invoke View | Code menu item when the cube designer is open, locate the XML code like below and replace the ID of the DSV.

<Source dwd:design-time-name="80d8b583-e073-4437-83cc-f17c461bfa13">
<DataSourceViewID>Simple Sales</DataSourceViewID>
</Source>

Note, that you will probably have to change bindings for existing measures.sql

Data source problem. Help! - remote report server

Hi guys,

I have project A with the data source name called 'dsyellow', which is a shared data source. The reports in this project were deployed and working fine.I created a new project(project B) and used the same dataset name (i.e.dsyellow), however this data source points to a different database. Accidentaly when I deploy project B, I overwrote the data source of Project B and I am unable to run the reports in project A. I am getting the following error message. I tried to re-deploy the the first datasource by overwriting the latest data source, however, when I run the report, I am getting the same problem. Please this is urgent. Let me know if you have any idea.

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsyellow'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

From Report Manager you can create a second data source that will work for Project B. Then go to the properties page for the report and select the Data Source tab and point it to this new Data source. For the Project A report, also make sure that on the Data Source tab you are pointing to the dsYellow shared data source, then go to the properties tab for the dsYellow shared datasource and make sure that the connection string and user information are what you expect.

I hope this helps.

|||

I think what you said make sense. However, I am having one more issue that prevent me to do that. The issue is that I am not seeing the Data Source folder from the content page of the Report Manager. I think it seems that it is hidden. I hope I will be able to solve the problem if I am able to see the Data Source folder first. Do you have any idea about this?

Appreciate it.

|||

hey! I figured out how to get back the hidden data source folder. I think I can do it now.

Thanks for your help.

|||Items are only hidden in the list view, if you click on the Detail View button, all items are visible. Is this what you found?|||

Yea, that was what I found. But the issue is still there. Here is the thing,I created a new project with some reports in it. My data source is from a remote server. The report is also deployed on the same remote server. I am able to run the report from my development machine. However, the error comes after deploying it on the remote server and try to run it from the report server. I checked that my report is pointing to the right server name and database name. But no luck. Ohhhhhh it makes me crazy.

|||Can you run the report on the actual report server so you can get the full error?|||

I did. This is the error message:

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsyellow'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

|||The error is stating that you are not local so therefore it is not showing the full error stack. Can you look in the RS log files to get the actual error?|||Do you know which table shows the errors?|||

Daniel is suggesting to look in the RS log files. They are located here:

{Drive}:\Program Files\Microsoft SQL Server\MSSQL.{Instance_of_RS}\Reporting Services\LogFiles

Jarret

|||

Hey guys,

It may sounds crazy but I am having some previllage issue to access the report server machine to get the log file. Anyhow, let me put the problem this way. I have three servers, assume ServerA,ServerB, and ServerC. ServerA is where the database(datasource) is located. ServerB is used as a report server and Server C is my local machine where I design reports. So obviously I deployed the reports from my local machine(server C) to the remote report server(server B). Deployement was successful but I got the following error when I run the report from the report server. You might think that the report is not pointing to the right data source, I checked that several times and it is pointing to the right data source.

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsCallB'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors|||

Going along with Daniel's suggestion earlier, you should open up Internet Explorer from ServerB's desktop (the RS server), and go to http://localhost/ReportServer. Then navigate to your report and see if you get more information about your error.

Another thing to check... Make sure your report's data source is setup correctly by doing this:

Open http://ServerB/Reports and navigate to your report.
Click on the 'Properties' tab, then select 'Data Source' from the left side.
Double check your data source and initial catalog in your connection string.
Then, select 'Credentials stored securely in the report server' in the 'Connect using...' section.
Type in a domain account with sysadmin privileges (for this test) like DOMAIN\USERNAME, with its respective password.
Check the box for 'Use as Windows credentials when connecting to the data source'.
Click 'Apply' at the bottom, and then click the 'View' tab to run the report.

Hope this helps.

Jarret

|||I did that so many times, but no luck.|||

Hmmmm...

Open up Reporting Services Configuration Manager and connect to ServerB. Click 'Database Setup' from the left pane. Make sure that the Server Name, Database Name, Credentials Type, Account Name, and Password are all correct.

Log into ServerB directly and browse to http://localhost/Reports and paste the message you get when you try to run this report. It should give more information about your error.

Do any other reports work from ServerB? If so, can you create another report pointing to the same database as dsCallB, with "select top 1 Name from sys.objects". Deploy it and see if it returns any data.

Jarret

sql

Data source problem. Help!

Hi guys,

I have project A with the data source name called 'dsyellow', which is a shared data source. The reports in this project were deployed and working fine.I created a new project(project B) and used the same dataset name (i.e.dsyellow), however this data source points to a different database. Accidentaly when I deploy project B, I overwrote the data source of Project B and I am unable to run the reports in project A. I am getting the following error message. I tried to re-deploy the the first datasource by overwriting the latest data source, however, when I run the report, I am getting the same problem. Please this is urgent. Let me know if you have any idea.

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsyellow'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

From Report Manager you can create a second data source that will work for Project B. Then go to the properties page for the report and select the Data Source tab and point it to this new Data source. For the Project A report, also make sure that on the Data Source tab you are pointing to the dsYellow shared data source, then go to the properties tab for the dsYellow shared datasource and make sure that the connection string and user information are what you expect.

I hope this helps.

|||

I think what you said make sense. However, I am having one more issue that prevent me to do that. The issue is that I am not seeing the Data Source folder from the content page of the Report Manager. I think it seems that it is hidden. I hope I will be able to solve the problem if I am able to see the Data Source folder first. Do you have any idea about this?

Appreciate it.

|||

hey! I figured out how to get back the hidden data source folder. I think I can do it now.

Thanks for your help.

|||Items are only hidden in the list view, if you click on the Detail View button, all items are visible. Is this what you found?|||

Yea, that was what I found. But the issue is still there. Here is the thing,I created a new project with some reports in it. My data source is from a remote server. The report is also deployed on the same remote server. I am able to run the report from my development machine. However, the error comes after deploying it on the remote server and try to run it from the report server. I checked that my report is pointing to the right server name and database name. But no luck. Ohhhhhh it makes me crazy.

|||Can you run the report on the actual report server so you can get the full error?|||

I did. This is the error message:

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsyellow'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

|||The error is stating that you are not local so therefore it is not showing the full error stack. Can you look in the RS log files to get the actual error?|||Do you know which table shows the errors?|||

Daniel is suggesting to look in the RS log files. They are located here:

{Drive}:\Program Files\Microsoft SQL Server\MSSQL.{Instance_of_RS}\Reporting Services\LogFiles

Jarret

|||

Hey guys,

It may sounds crazy but I am having some previllage issue to access the report server machine to get the log file. Anyhow, let me put the problem this way. I have three servers, assume ServerA,ServerB, and ServerC. ServerA is where the database(datasource) is located. ServerB is used as a report server and Server C is my local machine where I design reports. So obviously I deployed the reports from my local machine(server C) to the remote report server(server B). Deployement was successful but I got the following error when I run the report from the report server. You might think that the report is not pointing to the right data source, I checked that several times and it is pointing to the right data source.

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsCallB'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors|||

Going along with Daniel's suggestion earlier, you should open up Internet Explorer from ServerB's desktop (the RS server), and go to http://localhost/ReportServer. Then navigate to your report and see if you get more information about your error.

Another thing to check... Make sure your report's data source is setup correctly by doing this:

Open http://ServerB/Reports and navigate to your report.
Click on the 'Properties' tab, then select 'Data Source' from the left side.
Double check your data source and initial catalog in your connection string.
Then, select 'Credentials stored securely in the report server' in the 'Connect using...' section.
Type in a domain account with sysadmin privileges (for this test) like DOMAIN\USERNAME, with its respective password.
Check the box for 'Use as Windows credentials when connecting to the data source'.
Click 'Apply' at the bottom, and then click the 'View' tab to run the report.

Hope this helps.

Jarret

|||I did that so many times, but no luck.|||

Hmmmm...

Open up Reporting Services Configuration Manager and connect to ServerB. Click 'Database Setup' from the left pane. Make sure that the Server Name, Database Name, Credentials Type, Account Name, and Password are all correct.

Log into ServerB directly and browse to http://localhost/Reports and paste the message you get when you try to run this report. It should give more information about your error.

Do any other reports work from ServerB? If so, can you create another report pointing to the same database as dsCallB, with "select top 1 Name from sys.objects". Deploy it and see if it returns any data.

Jarret

Data source problem. Help!

Hi guys,

I have project A with the data source name called 'dsyellow', which is a shared data source. The reports in this project were deployed and working fine.I created a new project(project B) and used the same dataset name (i.e.dsyellow), however this data source points to a different database. Accidentaly when I deploy project B, I overwrote the data source of Project B and I am unable to run the reports in project A. I am getting the following error message. I tried to re-deploy the the first datasource by overwriting the latest data source, however, when I run the report, I am getting the same problem. Please this is urgent. Let me know if you have any idea.

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsyellow'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

From Report Manager you can create a second data source that will work for Project B. Then go to the properties page for the report and select the Data Source tab and point it to this new Data source. For the Project A report, also make sure that on the Data Source tab you are pointing to the dsYellow shared data source, then go to the properties tab for the dsYellow shared datasource and make sure that the connection string and user information are what you expect.

I hope this helps.

|||

I think what you said make sense. However, I am having one more issue that prevent me to do that. The issue is that I am not seeing the Data Source folder from the content page of the Report Manager. I think it seems that it is hidden. I hope I will be able to solve the problem if I am able to see the Data Source folder first. Do you have any idea about this?

Appreciate it.

|||

hey! I figured out how to get back the hidden data source folder. I think I can do it now.

Thanks for your help.

|||Items are only hidden in the list view, if you click on the Detail View button, all items are visible. Is this what you found?|||

Yea, that was what I found. But the issue is still there. Here is the thing,I created a new project with some reports in it. My data source is from a remote server. The report is also deployed on the same remote server. I am able to run the report from my development machine. However, the error comes after deploying it on the remote server and try to run it from the report server. I checked that my report is pointing to the right server name and database name. But no luck. Ohhhhhh it makes me crazy.

|||Can you run the report on the actual report server so you can get the full error?|||

I did. This is the error message:

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsyellow'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

|||The error is stating that you are not local so therefore it is not showing the full error stack. Can you look in the RS log files to get the actual error?|||Do you know which table shows the errors?|||

Daniel is suggesting to look in the RS log files. They are located here:

{Drive}:\Program Files\Microsoft SQL Server\MSSQL.{Instance_of_RS}\Reporting Services\LogFiles

Jarret

|||

Hey guys,

It may sounds crazy but I am having some previllage issue to access the report server machine to get the log file. Anyhow, let me put the problem this way. I have three servers, assume ServerA,ServerB, and ServerC. ServerA is where the database(datasource) is located. ServerB is used as a report server and Server C is my local machine where I design reports. So obviously I deployed the reports from my local machine(server C) to the remote report server(server B). Deployement was successful but I got the following error when I run the report from the report server. You might think that the report is not pointing to the right data source, I checked that several times and it is pointing to the right data source.

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsCallB'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors|||

Going along with Daniel's suggestion earlier, you should open up Internet Explorer from ServerB's desktop (the RS server), and go to http://localhost/ReportServer. Then navigate to your report and see if you get more information about your error.

Another thing to check... Make sure your report's data source is setup correctly by doing this:

Open http://ServerB/Reports and navigate to your report.
Click on the 'Properties' tab, then select 'Data Source' from the left side.
Double check your data source and initial catalog in your connection string.
Then, select 'Credentials stored securely in the report server' in the 'Connect using...' section.
Type in a domain account with sysadmin privileges (for this test) like DOMAIN\USERNAME, with its respective password.
Check the box for 'Use as Windows credentials when connecting to the data source'.
Click 'Apply' at the bottom, and then click the 'View' tab to run the report.

Hope this helps.

Jarret

|||I did that so many times, but no luck.|||

Hmmmm...

Open up Reporting Services Configuration Manager and connect to ServerB. Click 'Database Setup' from the left pane. Make sure that the Server Name, Database Name, Credentials Type, Account Name, and Password are all correct.

Log into ServerB directly and browse to http://localhost/Reports and paste the message you get when you try to run this report. It should give more information about your error.

Do any other reports work from ServerB? If so, can you create another report pointing to the same database as dsCallB, with "select top 1 Name from sys.objects". Deploy it and see if it returns any data.

Jarret

Data Source Credentials problem

Hi guys.

I've got an Xml DataSource in my report server, used to talk to a web service to retrieve it's data. Now, since this is an Xml DataSource that talks to a Web service, i shouldn't have to give it credentials to run with, or - since the web service runs on the machine right next to my report server - I could use Windows Integrated Auth.

However ... when I try to use Windows Integrated auth. I get back the error:

"This

data source is configured to use Windows integrated security. Windows integrated

security is either disabled for this report server or your report server is

using Trusted Account mode. "


And when I tried to use no credentials, before I can even see the report, I get the error:

"the user data source credentials that are required to execute this report are

not stored in the report server database." - where I just told it that it doesn't need credentials to run this datasource !!!

For the first error you got, there are two possible causes:

1. If you are running RS in sharepoint integrate mode, make sure the authentication mode you set in sharepoint central admin is Windows Integrated, not Trusted Account mode.

2. If you are running RS in non-sharepoint integrate mode, make sure this system properties is set to be true: "EnableIntegratedSecurity".

For the second error, if you set to be none, RS uses a fixed credential configured in Report Server config file to connect to your data source, you have to add it.

|||For the second issue make sure that unattended execution account is initialized. Open Reporting Services Config Manager and goto Execution Account tab|||No, I'm not running in Sharepoint Integrated mode, but I'll try the second resolution and let you guys know what happened, thanx :)|||

Well, here goes the whole story ...

First of all, we're using the Forms Authentication sample in our installation. Thought I should mention that in view of the error I'm about to write ...

Well, I've used the R. Services Configuration tool to set the execution account to a user in my domain. The config tool accepted it fine, only when I tried to run the report I got back:

|||

Finally, i got it to work !!! I created a new user account fot the execution account, one that had no xml special characters in it's uname/pwd and it worked fine.

Now, you guys should forward this to the implementing team because it's just rediculous ...

If the username / password of the Execution Account has any sort of "XML special characters" in it, the WMIProvider will fail to set the exec. account details. Although those credentials actually get written encrypted in the xml config file, so that doesn't seem really necessary.

Anyway, that WOULD be ok, u n l e s s ... if you try to type in the credentials, replacing those special characters with their &xxx; equivalents, the tool will succeed, and write out those details encrypted in the xml file. BUT !!! It won't replace the &amp; to an & in the encrypted string it writes out, so when you try to run reports, login will fail, and you're left there wondering what went wrong.

Data quality tools

Guys,

I'm trying to implement some of the data quality techniques. I use staging tables in my ETL process.

What I'd like to do is:

- check for required fields - make sure all required fields have values in all records

- check for duplicates

- check for data formats (line phone#, zip code etc)

Obviously all of this can be in SQL.

My question is what would be the best approach in SSIS to do it?

Dima.

Dima S wrote:

- check for required fields - make sure all required fields have values in all records

The Derived Column component has an ISNULL() function.

Dima S wrote:

- check for duplicates

Aggregate transform will give you all records that contains duplicates. COUNT over all the columns in which you want to check for duplicates.

Dima S wrote:

- check for data formats (line phone#, zip code etc)

Regular expressions would be a good fit here: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1754.aspx

-Jamie

|||

Thanks!

data from mdf to excel

Hello guys,
I have an mdf file and I need to get the data
from there to excel (not vice versa). Can I do that
without installing ms sql server. I have heard that
odbc sql drivers help but I don't know how.
I'm a beginner in this area. Please help me.

Best regards,
JessicaI don't believe you can do this. If you attach the mdf file to a SQL Server you can easily do this with DTS.|||If the MDF file is readable in another database tool you can export it to CSV then import it into excel... just one option|||U must install SQL Server,and attach the database.Then using sql server's data import & export tools to do it.|||Thank you guys, it seems that I still have
to install ms sql to do this.

Jessica