?
Solved

drop database from remote link server

Posted on 2007-04-03
10
Medium Priority
?
1,905 Views
Last Modified: 2008-06-28
EXEC sp_addlinkedserver @Server=ServerName,
                        @srvproduct='',
                        @provider='SQLNCLI',
                        @datasrc=[severname\instance]

       EXEC sp_addlinkedsrvlogin @rmtsrvname=servername, @useself=false,
                        @rmtuser='sa',
                        @rmtpassword='sa'

SELECT * FROM [servername].[databasename].[sa].table_name

error occur:
OLE DB provider "SQLNCLI" for linked server "servername" returned message "Unable to complete login process due to delay in opening server connection".
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server

also i have to drop the database from remote server
0
Comment
Question by:covisible
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
10 Comments
 
LVL 4

Accepted Solution

by:
Rick_Townsend earned 500 total points
ID: 18937675
Is the remote sql server configured to allow remote connections?  You can run the Surface Area Configuration tool (from the start menu, under SQL Server 2005) to check the setup for that server; run the Services and Connections option from the splash screen.  If remote connections are turned off, turn them on, and enable TCP/IP and/or Named Pipes.  (I like TCP/IP, but whatever suits your network.)

"What if I'm not the admin on the remote server?" - then the admin has to do this for you, or you are out of luck.  Sorry, but if you could configure these things remotely, anyone could enable remote access on your server whenever they feel like it and hack their way in.

Another good thing to check is your firewalls.  Open Windows firewall on the remote server and check if it's blocking access to SQL Server.  Go to Start Menu -> Control Panel -> Windows Firewall -> Exceptions tab.  If sqlservr.exe isn't there, click Add Program and add
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe
(That's the default path, of course.  You're installation may be different.)

I'm not sure you can drop remote databases, but you can try.  Once you've got a remote connection, I think you can run the following

DROP DATABASE [database_to_delete]

If that doesn't work, let us know, and you can try something trickier, like:

USE [servername].[databasename]
GO
DROP DATABASE [database_to_delete]
GO

I'd suggest simply prefixing the dbname in drop database with the servername, but I don't think that's valid syntax.
0
 
LVL 2

Expert Comment

by:cm0605
ID: 21615437

A simple way to do this is to have xp_cmdshell execute an OSQL query.

EXEC master.dbo.xp_cmdshell 'OSQL -E -S YourServer -Q "DROP DATABASE DataBaseName"'
0
 
LVL 4

Expert Comment

by:Rick_Townsend
ID: 21618575
He wanted to drop a remote database.  xp_cmdshell runs on the local server, and so won't have access to the database on the remote server.  He'd have to run your command on the remote server itself, either through a linked server script or by connecting directly to the remote server - in which case, why runs OSGL from the command line when you can run the DROP DATABASE command directly in T-SQL?

Please correct me if I've misinterpreted your idea.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 4

Expert Comment

by:Rick_Townsend
ID: 21618613
Basically, he was describing two problems.  His linked server commands were timing out, probably due to a firewall issue, and he wanted to know if he could then drop a remote db.  I tried to guide him into solving both problems.

Actually, looks like this question is more than a year old.  It should have been identified for clean up some time ago...
0
 
LVL 4

Expert Comment

by:Rick_Townsend
ID: 21860143
Normally I agree to split the points, but the solution proposed by cm0605 would not have worked in the situation described by the question's author.  The title of the question is specifically "drop database from REMOTE LINK server", indicating he needs to do it remotely.  Anyone searching for this as a PAQ would not be helped by cm0605's answer, as they would already need to have the linked server setup working in order to execute his suggested command.  If they have that access, then my first comment (just executing the DROP DATABASE command from T-SQL) is the correct one.

I suggest that the points not be split, that only my first comment be marked as the correct solution.  It addresses all the asker's problems, and my follow up comments indicate why cm0605's solution is incorrect.
0
 

Expert Comment

by:johnblox
ID: 22425693
So Rick gets the points eh!

Rick I'd like to point out that neither

DROP DATABASE [database_to_delete]

             OR

USE [servername].[databasename]
GO
DROP DATABASE [database_to_delete]
GO

Actually Work!!! I presume you just guessed and didn't test it as neither is valid syntax!

I'm still searching for the syntax to drop a database form a linked server, anyone?
0
 

Expert Comment

by:johnblox
ID: 22425798
OK I've worked out how to do it and the above answers are of no use at all!
0
 
LVL 4

Expert Comment

by:Rick_Townsend
ID: 22431778
Hi John.  You'll find that Drop Database is the correct syntax for SQL Server, see Books Online:  http://msdn.microsoft.com/en-us/library/ms178613.aspx
There are a number of caveats, such as the fact that the database can't be in use, you must execute the command from the same SQL Server instance, you can't execute it from a process conntected to the database being dropped, and you must have the correct permissions.  Normally I would simply recommend using SQL Server Management Studio, right-click and choose delete (or script as Drop), but as I stated in my very first comment, the issue is that the author wanted to execute them remotely (ie: from one SQL Server instance, drop a database in a different instance):

...
I'm not sure you can drop remote databases, but you can try.  Once you've got a remote connection, I think you can run the following.

DROP DATABASE [database_to_delete]

If that doesn't work, let us know, and you can try something trickier, like:

USE [servername].[databasename]
GO
DROP DATABASE [database_to_delete]
GO

I'd suggest simply prefixing the dbname in drop database with the servername, but I don't think that's valid syntax.
I was trying to explain it without going into all the details and caveats, but the author never did come back with a response.

If you got any errors dropping a database, or if you found a way you think is better, please post it so anyone else reading this thread can benefit.
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question