Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2004
    Posts
    10

    Exclamation Unanswered: Can a stored procedure access another database? (Problem Sloved)

    Hi all,

    I have an urgent problem. Can a stored procedure create a connection or access data from another database?

    Thanks in advace.
    Last edited by starfantasy84; 07-05-04 at 22:22. Reason: Problem is sloved. The thread can be closed

  2. #2
    Join Date
    Jun 2004
    Posts
    57
    Quote Originally Posted by starfantasy84
    Hi all,

    I have an urgent problem. Can a stored procedure create a connection or access data from another database?

    Thanks in advace.
    you know the db name? try USE db name

  3. #3
    Join Date
    Apr 2004
    Posts
    64
    Refer to the table within your SP as databasename.owner.tablename, and use it as normal. If the table sits in a different server, create a linked server and use servername.databasename.owner.table.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by starfantasy84
    I have an urgent problem. Can a stored procedure create a connection or access data from another database?
    Do you mean another SQL Server database, or a different database engine (like DB2 or Oracle)? Can the SQL Server "see" the other database?

    -PatP

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by starfantasy84
    Hi all,

    I have an urgent problem. Can a stored procedure create a connection or access data from another database?

    Thanks in advace.
    What is the problem?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jun 2004
    Posts
    10
    Ermz..

    The thing I want to do is this, I have a local database and I need to write a local stored procedure that is able to retrieve data from the local tables AND data from tables that are in another remote SQL server.

    So from the threads I read, I just connect the remote server with my local one in the enterprise manager and use remoteserver.database.owner.table to get the data right?

    Thanks a lot!!!

    Victoria

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    errrr, not exactly. You need to setup a linked server first or use OPENROWSET. Search the forum for linked server and you'll get plenty of examples. If you setup the linked server, you then use your syntax. If not, you will need to use OPENROWSET.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I guess by saying "...in Enterprise Manager..." she meant to set up a linked server there. In short, - yup, you got it
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jun 2004
    Posts
    10
    Hi,

    think I'll be using the linked server then cos my query to another database will go on every few mins. So for that's should be it until i really test it out. If my local database server is oracle 9i then is the concept somehow simliar, meaning that i have to be connected to the remote sql server right? If so, how do I do it? Cos there's no linked server for me to create in the oracle enterprise manager.

    Thanks a lot for all of your help!!!

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    From Horacle to SQL you need a DBLink, - very similar concept, but ironically is even easier to set up. Use the Horacle's EM, it's straight forward.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Jun 2004
    Posts
    10
    Hi,

    I'll be looking into the DBLink for oracle a while later. Thanks for the advice.

    For MS Sql server,
    I added the remote server to my local server as a linked server successful. I also set the ANSI_NULLS using the sp_configure procedure. But when i write codes to access the remote server in a stored procedure, the EM keep telling me that i have to set the ANSI_NULLS and ANSI_WARNINGS. I tried the same codes in the query analyser and it has no problem.

    Can someone help? Thanks!!!

  12. #12
    Join Date
    Jun 2004
    Posts
    10
    Hi, i have sloved the problem that is to create the stored procedure using query analyser. Somehow the enterprise manager is a bit "something wrong". =)

    For the people who want to know how to create a DBLink in oracle, here is a very good tutorial.

    http://www.oracle-base.com/articles/...ectivity9i.php

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •