asked
No not linked server, what are the options?
Versions: SQL 2005 - SQL 2012 I have conversations with developers I often tell them to use SSIS instead of linked server to do ETL. But what is the best solution for them (other than using linked server) if they build triggers that access's data from other MSSQL servers? (trigger executes sp that gets data via linked server). I want to eliminate all linked servers since they are the source of evil. What are the options?
Generally speaking, triggers should be very light so they don't slow down DML operations on the tables, but if you really need a trigger to access data on the different server then you can use OPENROWSET. Here are the examples of connections, one using SSPI (no need to hardcode the password but the account under which the SQL Server service is running or the user which connected with SSPI must have access to the other servers), and another one with user and password hardcoded: select t.* from openrowset ( 'SQLNCLI', 'server=SOME_INSTANCE_NAME;Trusted_Connection=yes', 'select * from DB_NAME.SCHEMA_NAME.TABLE_NAME where some_condition_is_met' ) t; go select t.* from openrowset ( 'SQLNCLI', 'SOME_INSTANCE_NAME'; 'user_name'; 'her_password', 'select * from DB_NAME.SCHEMA_NAME.TABLE_NAME where some_condition_is_met' ) t; go Just make sure that the 3-part naming is used because otherwise the openrowset will try to query whatever is default database of that user (usually master). Hope this helps, Oleg
It depends on what kind of queries are executed. Generally speaking, if you have something like update from openrowset which is fed the data to be updated/inserted/deleted on the other server and also you have some updates/inserts/deletes on the local server all wrapped into a single transaction then this will work only if the DTC (Distributed Transaction Coordinator) is enabled and configured. However, if you can afford to update data on both servers without wrapping it into a single transaction then nothing extra is required. Same applies (nothing fancy is required) if the data is only updated on one of the servers but not both. Here is the sample updating a column in the table on the remote server based on the data in the local table:
update t
set
ProcessedDate = getdate()
from openrowset
(
'SQLNCLI', 'SOME_INSTANCE_NAME'; 'user_name'; 'her_password',
'select *
from DB_NAME.SCHEMA_NAME.TABLE_NAME
where some_condition_is_met'
) t inner join some_local_table loc
on t.SomeColumn = loc.SomeColumn;
go The above updates the remote table from its join with the local table.
10 People are following this question.
