drop database from remote link server

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
covisibleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rick_TownsendCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cm0605Commented:

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
Rick_TownsendCommented:
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Rick_TownsendCommented:
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
Rick_TownsendCommented:
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
johnbloxCommented:
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
johnbloxCommented:
OK I've worked out how to do it and the above answers are of no use at all!
0
Rick_TownsendCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.