June 24, 2015 at 12:03 pm
I created a stored procedure in my ERP's db to report on the database's security authorizations. The procedure (runs from Crystal Reports) works fine and displays the report correctly.
I recently needed to run the same report against a different db, so utilizing the code developed earlier, I rewrote the report in SSRS so I could use it against all my various databases. This report works fine and displays the report correctly; until I run it against my ERP database. Then I get a message stating it is unable to display the report due to the database being at compatibility level 80.
Since this is our ERP system, I can't change the compatibility level to 90. I can still run my original report, but just want to know why the database has to be at level 90. I made no functional differences to the code, the SSRS report runs fine against other databases. This seems to be an unnecessary requirement.
What does SSRS do behind the scenes that requires the database to be running at compatibility level 90?
Anybody got a clue about this?
June 25, 2015 at 9:33 pm
Are you using T-SQL commands that were new in SQL 2005, such as PIVOT, CTEs, ROW_NUMBER(), RANK_(), TILE()?
Later versions of SQL Server allow you to use new T-SQL even though the compatibility level is set to a version that otherwise wouldn't support it. SQL 2005 did not; you code is restricted to the compatibility level of the database context in which it executes.
One way around that without changing the target database is to have an additional empty database in the instance at compat level 90, and execute code from there using 3-part names. I've also switch context into the Model database just to execute a level-90 query like a recursive CTE against a level-80 database:
USE model;
GO
WITH cte AS
( SELECT [...]
FROM OtherDB.dbo.SomeTable
[...]
)
SELECT * FROM cte;
Eddie Wuerch
MCM: SQL
June 26, 2015 at 7:55 am
Eddie
Thanks for the reply. The code I used in the SSRS report came from a stored procedure that I can run from my level 80 database. I copied the procedure code directly into the report's data source code (minus the create procedure bits). There are no PIVOTs, CTEs or anything like that. Just a simple SELECT against sys.database permissions, principals and other system tables to document authorized users. I don't think any of the tables are new in SQL 2005.
When designing the report in BIDS, I could successfully preview the report data from the level 80 database. This is why I'm so confused about the level 90 error message I receive.
RMc
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply