THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Linked servers: Can you do DDLs via openquery()?

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 *

 from openquery(SERVER02,

         '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.

 

Take II

Now we know what’s goingon, let’s try the following:

 

-- Run this on SERVER01

select *

 from openquery(SERVER02,

'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.

 

Take III

We can actually create a table via openquery() and let it persist as follows:

 

-- Run this on SERVER01

select *

 from openquery(SERVER02,

         '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.

 

Published Monday, November 15, 2010 5:23 PM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Linchi Shea said:

Hmmm... weird, I'm not sure why some of spaces seemed to have disappeared randomly between some words in this post. I wrote this in Word and did a copy/paste. I've fixed it now.

November 15, 2010 5:34 PM
 

Stephen Colbert said:

Real geeks write their posts in Vi.

November 15, 2010 5:41 PM
 

Dylan said:

@Stephen Colbert

http://xkcd.com/378/

November 16, 2010 9:02 AM
 

Stephen Colbert said:

@Dylan

awesome.

November 16, 2010 3:36 PM
 

Linchi Shea said:

Stephen;

Got bored with the Comedy Central gig??? :-) Hmmmm... what might a pompous, ultra-conservative blowhard parody in SQL Server look like, I wonder ...

November 17, 2010 2:31 PM
 

potato table said:

Linchi,

Speaking from the potato table (http://www.foxnews.com/story/0,2933,244882,00.html), I can say this:  all we are saying is give fear a chance.

Stephen

November 17, 2010 5:13 PM
 

Linchi Shea said:

Ah, speaking from the potato table, I must confess that I too are sustained on PR's protein shakes.

November 17, 2010 6:20 PM
 

W said:

Nice. This was very helpful; I was getting a little stumped on this and couldn't find anything that worked for a dynamically named target server. Thanks.

May 30, 2013 1:43 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement