• dwain.c (11/15/2012)


    opc.three (11/15/2012)


    Linked Servers are useful for server-to-server communication but if you're just needing to communicate from one database to another on the same instance then Linked Servers would not be a great option.

    With the proper permissions any SQL statement executed within a database can use 3-part naming to access data in any other database on the instance, e.g. This

    SELECT column_a

    FROM dbA.dbo.table_a;

    can be run in dbB to retrieve data.

    SQL statements that use 3-part naming can belong to views, triggers, stored procedures, user-defined functions...pretty much any SQL object can contain a query with a 3-part name.

    dbA can be a DB2 database? I didn't know that.

    Not straightaway but we could always define a SYNONYM named dbA.dbo.table that resolved to a remote table referenced by its 4-part name, however that's a different bunny trail.

    There is an ambiguity in the original post, and I read "db2" as the name of a SQL Server database on the same instance as dbA. If "db2" actually means an instance of an IBM DB2 database to the original poster then Linked Servers are pretty much the only option if the requirement was to do everything in T-SQL. if that is the case then I would edit to replace "3-part naming" in my initial post with "4-part naming."

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato