0

I am trying to update tables in a production environment from a staging environment. I wrote an update statement (simplified version below) that will use data from another database and I'm getting a "Cross Database Access not supported for this type of command" error. I can successfully insert data from another database, but not update. Maybe this is not allowed? In the below examples, I am logged into DB1.

First attempt:

UPDATE DB1..EMPLOYEE e1
SET e1.name = e2.name,
    e1.phone = e2.phone
FROM DB2..EMPLOYEE e2
WHERE e1.id = e2.id;

2nd attempt:

UPDATE DB1..EMPLOYEE e1
SET e1.name = e2.name,
    e1.phone = e2.phone
FROM (select name, phone, id from DB2..EMPLOYEE) e2
WHERE e1.id = e2.id;
Kelly
  • 945
  • 2
  • 18
  • 31

1 Answers1

0

You are correct, you can only change stuff in the database you're currently connected to.

Cross database statements reading from a different database in order to change data in the current database are supported though, so those statements should work while you're connected to DB1.

edit: the update statements above update a table in DB1, so you need to be connected to DB1.

r.m
  • 374
  • 1
  • 5
  • You mean the statements should work while connected to DB1? Everything else you said appears correct and I actually did get it work after I changed one of the databases, so there must be some sort of permissions or setup error when one of the databases was created. – Kelly Apr 12 '14 at 04:26
  • sorry, I'll correct my answer. Yes, while you're connected to DB1. Are you sure you were connected to DB1 when you received the error ? I've only seen that "cross database statement not supported ..." error occur when you're not connected to the right db. Other authorization issues typically return different error messages – r.m Apr 12 '14 at 14:23