Dropping database on another SQL Server instance

• navtec

  Hall of Fame

  Points: 3206

  More actions

  October 2, 2015 at 5:36 am

  #312053

  Hi,

  I have managed to successfully connect to another SQL Server instance using a linked server.

  I can then do my select statement like this:

  select * from [servername\instancename].[databasename].[databaseowner].[tablename]

  I just wanted to know how I can drop the database on the other instance?

  I have tried doing: DROP DATABASE [servername\instancename].[databasename]

  but it doesn't work and expects a database name.

• Ant-Green

  SSC Guru

  Points: 113536

  More actions

  October 2, 2015 at 5:45 am

  #1831007

  Try this

  EXEC [yourlinkedserver].tempdb.dbo.sp_executesql N'DROP DATABASE Test;';

  Need to ensure RPC is on in the linked server settings

• Lowell

  SSC Guru

  Points: 323522

  More actions

  October 2, 2015 at 6:06 am

  #1831012

  i believe the command syntax like this will work as well: i use it for creating tables remotely:

  EXEC ('DROP DATABASE [SandBox]') AT MyLinkedServer

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• navtec

  Hall of Fame

  Points: 3206

  More actions

  October 2, 2015 at 7:19 am

  #1831031

  They work thanks.