Solved

drop database from remote link server

Posted on 2007-04-03
10
1,707 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
  • 5
  • 2
10 Comments
 
LVL 4

Accepted Solution

by:
Rick_Townsend earned 125 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
 
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now