Is there any possibility to two different ddbs to see each other?
-
Hi!
I'm working on a db application. In MS Sql server, I have two different db's from where I want to get certain knowledge. Is there any possible answer to how I can make a stored procedure to other db, from which I can first fetch data to a cursor from other db on the server and then based on that data, i could make a query in the stored proc.?
-
Retrieving data from another database on the same server is very straightforward. E.g. from the master database you can issue the command SELECT * FROM pubs..sales (no need to specify the database owner here).
If the DB is on another SQL server, the best method is to create a linked server (from the Security tab of enterprise manager) to the remote server, map the login of the local server to a login on the remote server that has the required DB access. You can then issue the four part source query to read data from the remote database.
For example, Add a linked server called REMOTE_SERVER and issue a query like
SELECT * FROM REMOTE_SERVER.pubs.DBO.sales
The database owner is not optional in this query.
-
Furthermore, you can use the results from your cursor to build up a SQL string and then execute the string dynamically. This is highly flexible but has the downside that the string being executed is always compiled on demand - every time it is run.
E.g.
DECLARE @sDB VARCHAR(50)
DECLARE @sQuery VARCHAR(4000)
SET @sDB = 'pubs'
SELECT @sQuery = 'SELECT * FROM ' + @sDB + '..Sales'
EXECUTE ( @sQuery )
An alternative would be to create a stored procedure that takes parameters from your cursor. This would be compiled. The choice is yours.
-
if you are using sp3 and hopefully you are then you will have to consider the effects of cross database ownership chaining
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply