More specifically, can you create a table in openquery() over a linked server on another SQL Server instance using the SQL Server Native Client OLE DB provider? I don’t know what the official story is on this because I can’t seem to find any explicit documentation in SQL Server Books Online or elsewhere. If you know whether that is documented, let me know.
Now, I’m not talking about whether you should or should not create any table via openquery(). All the experts I talked with seem to agree that you probably should not. I’m simply interested in whether you can do it or not. If you execute the following statement,you’ll find that table FOO is not createdon SERVER02 even though you are not getting any error message:
-- Run this on SERVER01 withSERVER02 defined as a linked server
-- using SQL Server native client OLE DB provider
'select @@servername;create table FOO(iint)')
One possibility is that the CREATE TABLE statement is not executed at all on SERVER02. To verify, you can start a SQL Profiler trace on SERVER02 before you run the above statement, and you’ll see that both statement select @@servername, and statement create table FOO(i int) in fact run to completion on SERVER02. But when you try to find the table, it’s not there.
To see why the table does not exist, you need to include event SQLTransaction in your profiler trace. Re-run the above statement, and then you’ll see that the CREATE TABLE statement is actually sandwitched between a BEGIN TRAN and a ROLLBACK TRAN. In other words, the table is indeed created on SERVER02, but the change is then rolledback, thus you don’t see such a table on SERVER02. Since no user defined transaction is started explicitly, the transaction is the work of the OLE DB provider.
Now we know what’s goingon, let’s try the following:
-- Run this on SERVER01
'select @@servername;create tableFOO(i int); waitfor delay ''00:01:00''')
On SERVER02, run the following (by default all this should take place in the master database):
select * from FOO;
While the openquery() statement is running on SERVER01, the above SELECT statement on SERVER02 will be blocked. This is a good sign that table FOO likely exists or being created.
We can actually create a table via openquery() and let it persist as follows:
-- Run this on SERVER01
'select @@servername;create table FOO(iint); commit tran')
In other words, we’ll force the transaction initiated by the OLE DB provider to commit so that by the time it issues a ROLLBACK, there is nothing to rollback. Now, if you check on SERVER02, you’ll table FOO just like anyother table.
Just from experimenting and not based on any inside knowledge, it appears that whenever the OLE DB provider sees any DDL statement, it’ll just rollback the transaction.
Again, should you execute DDL statements via openquery()? The answer is probably not, especially when you can easily run any DDL across servers with either EXECUTE() AT or sp_executesql. But it’s always nice to find out a bit more about why.