Cross-server transaction with msdtc, switching context of DML without dynamic SQL

I have a design in which I have two SQL Server databases. I have a single transaction containing DML statements to both databases.

The problem is the second database name is not predefined. It can be a parameter to the stored procedure. I want to specify the database name and server name in each DML statement.

Without using dynamic SQL, is there a way to switch the context of the statement.

Or simply: how di I prefix the server name a开发者_高级运维nd database name in DML statement where the server name and database name are dynamic arguments?

Is there a way to accomplish this without using dynamic SQL? Or Is there another way to handle this issue?

Is it not a better practice to handle DML in a seperate SP or process, rather than joining them in the existing process. It is not possible to use variables in four-part names: Unless specified otherwise, all Transact-SQL references to the name of a database object can be a four-part name in the following form:

server_name .[database_name].[schema_name].object_name

| database_name.[schema_name].object_name

| schema_name.object_name

| object_name

server_name Specifies a linked server name or remote server name.

database_name Specifies the name of a SQL Server database when the object resides in a local instance of SQL Server. When the object is in a linked server, database_name specifies an OLE DB catalog.

schema_name Specifies the name of the schema that contains the object if the object is in a SQL Server database. When the object is in a linked server, schema_name specifies an OLE DB schema name. For more information about schemas, see User-Schema Separation.

object_name Refers to the name of the object.