Post reply

Data import from MS Access/SQL Server DB

  • April 25, 2008 at 5:06 am

    #186964

    I am creating reports using SSRS 2005 from my DB say DB_A. I require data from another SQL 2000 DB, DB_B as well as MS Access on a regular basis but do not have permission to link the servers neither to execute DTS packages (yer talk abt bureaucracy ....)

    I can run the import/export wizard given that I need to do it for at least 10 tables but that takes too much time and I was looking for a way to write a SP that i can execute from my DB.

    Any ideas?

    Thanks

  • April 25, 2008 at 9:41 am

    #807262

    Are you allowed to create SSIS packages using BIDS?

  • April 25, 2008 at 10:40 am

    #807311

    Take a look at OpenRowset in Books Online. It can connect to non-linked servers. Will that do what you need?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • May 8, 2008 at 5:47 am

    #812351

    Thanks for your responses... been on a break.

    Just tried the OPENROWSET option but that will not work as again security options ... and I am not being allowed access to.

    As for writing DTS packages from BIDS, how do I go about doing that.

    Just to clarify, I have access to the other server that I need hence can do the imports but other than that I am heavily restricted in what I can cannot do with it.

    Thanks

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

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