Post reply

Running reports on a linked server

  • May 30, 2006 at 2:29 pm

    #169080

    Is there a way to run SQL Server 2005 and link to a SQL Server 2000 box and run reports against tables there using the SQL Server 2005 Reporting services?

    We are going to be moving to 2005 once our third party vendor moves their application to 2005. we do a lot of reporting on the data using Crystal reports.  Of the demos I've seen, the reporting services will do everytihng we need and we could get rid of Crystal. 

    Thanks

    Thanks

  • May 30, 2006 at 5:27 pm

    #640811

    You should be able to link to the 2000 server with SQL 2005 as a linked server. Then create a stored procedure that queries data from the linked tables and outputs them for you. reporting Services will then connect to the SQL 2005 and run that stored procedure as a 2005 query.

  • May 31, 2006 at 7:43 am

    #640921

    Thanks Jon.

    Would it also be possible, to create a distributed query on to the linked server and use that in a view on SQL 2005.  And then in turn use that view in the Reporting Services?

    Thanks

     

    Thanks

  • May 31, 2006 at 3:37 pm

    #641056

    I don't think you need to setup a linked server to do this. Why not just setup an RS datasource on your SS2005 box that points to your SS2000 box? You won't need the views either in that case... Not always a good idea to give reports direct access to the table though as the report will break if there's a table change, so putting a view/Sproc over the table may be a good idea.

    I've tried this on our SS2005 test box and it worked no probs for me . You should be able to execute Stored Procs on the SS200 server too.

    Kindest Regards,

    Martin

  • June 1, 2006 at 6:13 am

    #641147

    Thanks Martin.  That sounds good.

    Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply