Home About Us SQL Interview Book Contact Us RSS
Articles
Tools
Code Snippets
Links
Tips & Tricks
FAQ
Resources
Articles
Code Snippets
Links
FAQ
Resources

Saravana Kumar
Vinod Kumar


Q: How can I drop a table on a Linked Server ?

Answer:

Assume that you have to execute the command DROP TABLE tableName on a linked server. The typical syntax that someone will try is:

DROP TABLE serverName.master.dbo.TableName

This for some reason fails ... If you were wondering why this happens then the answer is simple. The context of the user is unavailable to the linked server and hence you get this error. The only workaround possible is:

EXEC serverName.database.userContext.sp_executesql N'drop table tableName'

Essentially this would execute the  sp_executesql on the linked server and drop the table. This is a typical requirement to have and a rare one also.