SSRS promt in Oracle and Sql server

Question

hi

      i create a report in SSRS. so now my reports are running in sql serer Environment..now i want to change these reports to oracle server..some reports i dont have promt that reports are run for both the envirnment but when run my promt reports its not running why means i used in query like where date_dim.date=@date. but in oracle we cant give @ we want  give :date..but i create more than 50 reports..so how to write query to suitable for both the environment

Answer 1

so how to write query to suitable for both the environment

Hello,

I guess that's not possible, because Oracle and MS SQL Server do have different SQL syntax. Both have implemented a subset of ANSI SQL, but in details they differ. Starting with different parameter syntax, they have different function names (e.g. to_date = Oracle, convert(datetime = MS SQL) and so on.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

ya ok i create a table called provider_name in that table i stored that environment name like Oracle or sql server now i want to write case statement to get that data

so how to wite case statement for that

ex:

select case when provider_name='Oracle'

then

select * from student where class_no =@class_no

else

select * from student where class_no =@class_no

end from Provider_table

here i write simply example if i used like this means my SSRS reports will run???

Answer 3

Hi SQL.anandan,

It seems that you report's datasource's provider is selected at the beginning, you cannot select its provider at report's run time. So just like Olaf mentioned, there is no build-in feature to support this requirement.

---

Challen Fu

TechNet Community Support

Answer 4

Hi,

Try using Stored Procedures both in SQL and Oracle and refer the SP in Report.

Hope this helps.

---

Regards, Senthil

Answer 5

      Ya thanks for ur reply but for each report if i create a Procedure i want to create more than 100 Procedure..and also how to create a sp in oracle to fetch more than 45 columns data For Generating Reports