Can a stored procedure access another database?

Hi all,
I have an urgent problem. Can a stored procedure create a connection or access data from another database?
Thanks in advace. :)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|||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.|||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|||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?|||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|||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.|||I guess by saying "...in Enterprise Manager..." she meant to set up a linked server there. In short, - yup, you got it ;)|||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!!! :)|||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.|||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!!! :)|||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/9i/HSGenericConnectivity9i.php