Is it possible to create a stored procedure that process data from different DBs (1 Viewer)

[accesser2003]

I would like to write a stored procedure that insert data from table [SourceEvents] located at a DB linked to DSN called CARDAX into another table [AllAttendanceEvents] located at the same DB of the stored procedure, which this DB is linked to a DSN called BAHRAIN.

If both tables are located at the same databases, the stored procedure will be as follow

Create Stored Prcoedure SP1 AS

INSERT INTO dbo.AllAttendanceEvents.*
SELECT dbo.SourceEvents.*
FROM dbo.SourceEvents


But how it will be if the case as explained above??

Thanks,

 

[Banana]

If you google for 'SQL Clause IN' you may get an idea...

 

[accesser2003]

Banana you always dont got what I am looking for.

However, thanks.

 

[georgedwilkinson]

A natural misunderstanding since this is in the general forum.

You can reference a database in the same server by pre-pending the database name to the table name. Here is a highly modified sample (that works) from my database:

USE [ProductionDB];
GO
 
select * from x where x_ClientID not in 
(select y_ClientID from [SandboxDB].dbo.y)
GO

You'll need to make sure you have the right references on the server.

 

[accesser2003]

Thanks Mr. georgedwilkinson.

However, Unfortunatelly the another database is located in different server. If I only put the database name, it will not find it as it thinks its in the same database. Correct? For this reason I had clarify my question by mentioning the DSN Name which refers to different server database.


Please help

 

[Banana]

Now I know why I remembered it as "IN" clause.... This is the syntax you want.

SELECT * FROM
[ODBC;DRIVER=SQL Server;
SERVER=T02ADN01;
UID=xxxx;
PWD=xxxx;
DATABASE=xxxx;
Network=DBMSSOCN].tblAreas

{Which is about the same as an IN clause, accept you have to ability to JOIN tables from completely different sources ... which I think is really cool to do when trying to sync databases.}

That was from Brent in another thread, which was what I was thinking of when I told you to look up the IN clause.

 

[georgedwilkinson]

Did we get this solved yet?