Solved

drop database from remote link server

Posted on 2007-04-03
10
1,770 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

773 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