| Author |
Topic |
|
cparker
Starting Member
16 Posts |
 Posted - 2008-10-10 : 10:40:42
|
| An error has occurred during report processing.Query execution failed for data set 'DataSet1'.Could not find stored procedure 'SP_SMTR_SAB_AvailUsedRoll30Days'. Does this simply mean that he is referencing a stored procedure in the rdl that does not exist on the server?This stored procedure does exist on the SQL 2005 DB server 3 under SMTR_SAB_Prod>programmability>Stored Procedures>dbo.SP_SMTR_SAB_AvailUsedRoll30DaysThe Shared Data source is SAM_DXT_TROPMI on DB server 5 this is the only selectable shared data source. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-10 : 11:42:18
|
| Make sure datasource is using the same credentials to connect whose default schema the procedure exists. |
 |
|
|
cparker
Starting Member
16 Posts |
Posted - 2008-10-10 : 13:34:34
|
| Well, it works for other .rdl files that are loaded to the SQL server. |
|
|
|
harlingtonthewizard
Constraint Violating Yak Guru
352 Posts |
Posted - 2008-10-10 : 20:22:36
|
| Start SQL Managemment Studio, Connect to Server, expand databases, <your DB name>, programability, stored procedures and see if the procedure exists. If not then it is most likely the procedure did not exist or was deleted and is referenced from BI as a dataset. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-11 : 01:21:02
|
quote:
Originally posted by harlingtonthewizard
Start SQL Managemment Studio, Connect to Server, expand databases, <your DB name>, programability, stored procedures and see if the procedure exists. If not then it is most likely the procedure did not exist or was deleted and is referenced from BI as a dataset.
not only that it exists but also that its existing in schema which the credentials used to connect through datasource has access to. |
|
|
|
cparker
Starting Member
16 Posts |
Posted - 2008-10-13 : 10:13:01
|
| I checked the properties, under permissions it is blank. Under Schema permissions the acme\sql-smtr_sab_prod-read has execute and view definition. The users that run that report are part of this group. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-13 : 10:18:10
|
quote:
Originally posted by cparker
I checked the properties, under permissions it is blank. Under Schema permissions the acme\sql-smtr_sab_prod-read has execute and view definition. The users that run that report are part of this group.
will the reports be running in same security context as users or will they be using some kind application login to connect to server? |
|
|
|
cparker
Starting Member
16 Posts |
Posted - 2008-10-13 : 12:45:30
|
| They are connecting via the SQL reporting services website. It is internal to the domain. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-13 : 13:02:25
|
quote:
Originally posted by cparker
They are connecting via the SQL reporting services website. It is internal to the domain.
does that mean they use integrated windows authentication? |
|
|
|
cparker
Starting Member
16 Posts |
Posted - 2008-10-13 : 14:41:07
|
| The schema permissions show an AD group, the database permissions show a mix of AD and SQL. The thing is the other stored procedures are setup similiarly and the data source on other .rdl files are set similiarly, I can't figure out why this is failing. |
|
|
|
cparker
Starting Member
16 Posts |
Posted - 2008-10-13 : 14:54:13
|
| I just looked at the way the developer is running it. He is running it in Microsoft Visual Studio. The dataset is set to:Name:dataset1Data Source:datasourceSMTRCommand type:TextQuery String:SP_SMTR_SABAvailUseRoll30DaysDatasourceName:datasourceSMTRUse Shared data source reference is checked for "datasourceSMTR"Shared Data SourceName:datasourceSMTRType:Microsoft SQL ServerConnection String:Datasource=SPCHOUDB13;Initial Catalog=SMTR_SAB_Prod.The only shared datasource I can select on the SQL 2005 reporting services webpage is SAM_DXT_IMPORT. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-14 : 13:35:20
|
| try deploying the dataset developer uses to server and map the datasource property of your report to it from server. then try accessing |
|
|
|
cparker
Starting Member
16 Posts |
Posted - 2008-10-24 : 12:07:42
|
| OK, I don't know if this makes a differences but the report writer says that he needs to reference two different data sources. Is this possible and if so, how do I configure it or does he need to do that in the report? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-24 : 12:54:58
|
quote:
Originally posted by cparker
OK, I don't know if this makes a differences but the report writer says that he needs to reference two different data sources. Is this possible and if so, how do I configure it or does he need to do that in the report?
its possible. If he's using two datasources. you must have access to both datasources to use report yourselves. each datasource will be connecting to different server using different credentials. You or running account should have a valid login existing on both servers. |
|
|
|
cparker
Starting Member
16 Posts |
Posted - 2008-10-24 : 14:44:27
|
| So, does that mean that his report needs to correctly reference these two datasources or is it something I should configure within SQL 2005 reporting services website and if so, how? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-25 : 02:29:55
|
quote:
Originally posted by cparker
So, does that mean that his report needs to correctly reference these two datasources or is it something I should configure within SQL 2005 reporting services website and if so, how?
first of all you need to analyse using what account you will be running the report. your own domain account or using another account which has been defined for application role in server. DEpending on that you need to see if the used account has access to both the databases which two datasources are trying to connect (valid login exists in both for whatever account you're using to run report). |
|
|
|
keithfx
Starting Member
1 Post |
Posted - 2009-09-02 : 11:04:01
|
| Try to prepend the database owner,e.g.exec accounting.SprocNameCheers!--Keith Fullerkeithfx2hotmail.com |
|
|
|
|
Query execution failed for data set 'DataSet1'