Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

drop database from remote link server

Posted on 2007-04-03
10
Medium Priority
?
1,958 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 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

971 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