March 29, 2012 at 1:48 pm
I created a new schema in our SQL Server 2005 database, with the intent of putting stored procedures there that are strictly for management reports. We have a generic SQL user with SELECT, UPDATE, INSERT and DELETE privileges on some tables and EXECUTE on some SP's. I added my new schema (MgmtRpt) to the generic SQL account, but dbo is still it's default schema. My coworker tried to bring up SQL Server Business Intelligence, but couldn't find the SP. My best guess is that, with SQL Server 2005, SSBI will not "see" any other schema other than dbo, even if you specify a SQL user that has privileges to that schema and can run those stored procedures.
Am I correct about this?
Kindest Regards, Rod Connect with me on LinkedIn.
March 29, 2012 at 4:37 pm
I haven't been in this position yet, but I would have to hazard a guess and say no. It wouldn't make sense to restrict reports to only dbo.
March 29, 2012 at 10:18 pm
We have multiple schemas and use ssrs to access procs in those different schemas.
If you log on as the particular user, can you see the proc in question?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2012 at 12:19 am
You can access any database schema from within SSRS.
My first thoughts for your problem:
1. Make sure that the person who develops the report in SSRS is connecting with the correct user
2. Make sure that when he calls the SP, he doesn't forget to add the schema name before ths SP name (seems obvious, but is forgotten quite a lot)
3. Review the access rights for the user on the schema, maybe there is something missing
Hope this helps you out...
March 30, 2012 at 8:40 am
SQLRNNR (3/29/2012)
We have multiple schemas and use ssrs to access procs in those different schemas.If you log on as the particular user, can you see the proc in question?
I just tested it. I can log into SSMS as the generic user I mentioned previously. It can see the stored procedures I've saved under my new MgmtRpt schema. I can run all of those SP's, as that user.
Kindest Regards, Rod Connect with me on LinkedIn.
March 30, 2012 at 9:15 am
tom.wauters (3/30/2012)
You can access any database schema from within SSRS.My first thoughts for your problem:
1. Make sure that the person who develops the report in SSRS is connecting with the correct user
2. Make sure that when he calls the SP, he doesn't forget to add the schema name before ths SP name (seems obvious, but is forgotten quite a lot)
3. Review the access rights for the user on the schema, maybe there is something missing
Hope this helps you out...
OK, I've got SQL Server 2005 BI installed on an old PC of mine. I tried it out, and found that using that I could create a report using the generic query writer. Then I did something like this:
EXEC MgmtRpt.mySP '2012-3-1', '2012-3-31'
It finished creating the report, and I did preview the report and it worked fine.
However, this is not the design experience my coworker is used to doing. She's used to selecting a stored procedure from a listbox, and have it understand that it is a stored procedure and that it takes parameters (2 datetime parameters). Anything other than that design experience is, for her, unacceptable.
Now, this is my first go at generating a quick-and-dirty report. Perhaps I could have done something different, but at this point I don't know what. When I tried bringing up the query wizard (instead of the generic query writer), it gave me a dialog box with Tables, Views, Functions and Synonyms. I couldn't find the SP under any of those 4 tabs. The only way I could, was by typing it into the generic query builder using the SQL syntax I gave above. I've noticed that when I open the report file (.rdl) in the designer, there's 3 tabs. It's opened, by default to the Preview tab. I opened the Data tab and I see that the Command type is Text. I'm assuming it is that because I'm using the EXEC command in the query. I can change the dropdown to Stored Procedure, but then I'm guessing the SQL command I gave above will no longer work, so what do I do?
Kindest Regards, Rod Connect with me on LinkedIn.
March 30, 2012 at 9:18 am
Another question, which is related to this issue, concerns the different versions of SSRS. Our production database server is a SQL Server 2005 Standard Edition. We've got, on our developer boxes, SQL Server 2008 R2 Developer Edition (at least I do). However, because I know that our SSRS reports were written some years ago using SSRS 2005 I never installed SSRS for SQL Server 2008 R2. More recently it's my understanding that there hasn't been a new version of SSRS since 2005; that is until this year with the release of SQL Server 2012. Am I correct about that, or am I mistaken?
Kindest Regards, Rod Connect with me on LinkedIn.
March 30, 2012 at 9:25 am
Rod at work (3/30/2012)
Another question, which is related to this issue, concerns the different versions of SSRS. Our production database server is a SQL Server 2005 Standard Edition. We've got, on our developer boxes, SQL Server 2008 R2 Developer Edition (at least I do). However, because I know that our SSRS reports were written some years ago using SSRS 2005 I never installed SSRS for SQL Server 2008 R2. More recently it's my understanding that there hasn't been a new version of SSRS since 2005; that is until this year with the release of SQL Server 2012. Am I correct about that, or am I mistaken?
SSRS is significantly different in 2008 than 2005. Yes SSRS has released new editions since 2005 (2008, 2008 R2 are both different than 2005).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2012 at 9:29 am
Rod at work (3/30/2012)
tom.wauters (3/30/2012)
You can access any database schema from within SSRS.My first thoughts for your problem:
1. Make sure that the person who develops the report in SSRS is connecting with the correct user
2. Make sure that when he calls the SP, he doesn't forget to add the schema name before ths SP name (seems obvious, but is forgotten quite a lot)
3. Review the access rights for the user on the schema, maybe there is something missing
Hope this helps you out...
OK, I've got SQL Server 2005 BI installed on an old PC of mine. I tried it out, and found that using that I could create a report using the generic query writer. Then I did something like this:
EXEC MgmtRpt.mySP '2012-3-1', '2012-3-31'It finished creating the report, and I did preview the report and it worked fine.
However, this is not the design experience my coworker is used to doing. She's used to selecting a stored procedure from a listbox, and have it understand that it is a stored procedure and that it takes parameters (2 datetime parameters). Anything other than that design experience is, for her, unacceptable.
...
This is behavior that I have seen occasionally. It is fixed with 2008.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2012 at 10:30 am
SQLRNNR (3/30/2012)
Rod at work (3/30/2012)
Another question, which is related to this issue, concerns the different versions of SSRS. Our production database server is a SQL Server 2005 Standard Edition. We've got, on our developer boxes, SQL Server 2008 R2 Developer Edition (at least I do). However, because I know that our SSRS reports were written some years ago using SSRS 2005 I never installed SSRS for SQL Server 2008 R2. More recently it's my understanding that there hasn't been a new version of SSRS since 2005; that is until this year with the release of SQL Server 2012. Am I correct about that, or am I mistaken?SSRS is significantly different in 2008 than 2005. Yes SSRS has released new editions since 2005 (2008, 2008 R2 are both different than 2005).
Oh really! Well, I'm wrong. Thank you for correcting me on this. But, just so that I've got it clear in my own head, if we did develop a SSRS in 2008, I would think we couldn't deploy it to our production database server, where only SSRS 2005 is running. Am I correct about that?
Kindest Regards, Rod Connect with me on LinkedIn.
March 30, 2012 at 2:17 pm
Rod at work (3/30/2012)
SQLRNNR (3/30/2012)
Rod at work (3/30/2012)
Another question, which is related to this issue, concerns the different versions of SSRS. Our production database server is a SQL Server 2005 Standard Edition. We've got, on our developer boxes, SQL Server 2008 R2 Developer Edition (at least I do). However, because I know that our SSRS reports were written some years ago using SSRS 2005 I never installed SSRS for SQL Server 2008 R2. More recently it's my understanding that there hasn't been a new version of SSRS since 2005; that is until this year with the release of SQL Server 2012. Am I correct about that, or am I mistaken?SSRS is significantly different in 2008 than 2005. Yes SSRS has released new editions since 2005 (2008, 2008 R2 are both different than 2005).
Oh really! Well, I'm wrong. Thank you for correcting me on this. But, just so that I've got it clear in my own head, if we did develop a SSRS in 2008, I would think we couldn't deploy it to our production database server, where only SSRS 2005 is running. Am I correct about that?
That is correct. If you develop in 2008, you will need to deploy to SSRS 2008.
As for the other question - I was referring to fixed in SSRS 2008.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2012 at 12:06 am
I would suggest to migrate to SQL Server 2008 R2.
First of all, your databases will be in the newer environment, which has lots of improvements compared to SQL Server 2005.
Second, and for this I rely on my colleagues here, since I couldn't test it, your problem would disappear with SSRS 2008 R2.
Is there any specific reason why your production environment is still on SQL Server 2005 ?
April 2, 2012 at 10:39 am
tom.wauters (4/2/2012)
I would suggest to migrate to SQL Server 2008 R2.First of all, your databases will be in the newer environment, which has lots of improvements compared to SQL Server 2005.
Second, and for this I rely on my colleagues here, since I couldn't test it, your problem would disappear with SSRS 2008 R2.
Is there any specific reason why your production environment is still on SQL Server 2005 ?
The reason we've not migrated is money. We're a non-profit organization. We don't have the latest and greatest, we don't go to conferences, seminars, etc. A training budget is non-existant, and pay is low enough that we cannot afford our own. Even our backup tapes are highly vulnerable, because they sit on a book shelf in the server room. I've finally won a major victory in convincing my management that we at least need a fireproof safe to put them into. This organization has been around for 16 years, and I've only now won that. Upgrade to SQL 2008 R2 or SQL 2012? HA! HA! HA! Not going to happen.
Kindest Regards, Rod Connect with me on LinkedIn.
April 2, 2012 at 10:48 am
Rod at work (4/2/2012)
tom.wauters (4/2/2012)
I would suggest to migrate to SQL Server 2008 R2.First of all, your databases will be in the newer environment, which has lots of improvements compared to SQL Server 2005.
Second, and for this I rely on my colleagues here, since I couldn't test it, your problem would disappear with SSRS 2008 R2.
Is there any specific reason why your production environment is still on SQL Server 2005 ?
The reason we've not migrated is money. We're a non-profit organization. We don't have the latest and greatest, we don't go to conferences, seminars, etc. A training budget is non-existant, and pay is low enough that we cannot afford our own. Even our backup tapes are highly vulnerable, because they sit on a book shelf in the server room. I've finally won a major victory in convincing my management that we at least need a fireproof safe to put them into. This organization has been around for 16 years, and I've only now won that. Upgrade to SQL 2008 R2 or SQL 2012? HA! HA! HA! Not going to happen.
Just curious, as I did interview with a couple of non-profits recently, does your organization pay full price for its software? The non-profits I talked to seemed to get deep discounts when purchasing software like MS SQL Server.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply