Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1552
  • Last Modified:

SQL SERVER 2005: Unable to complete login process due to delay in opening server connection??

Hello,

I've just completed a database migration between servers and now I can't run basic commands on the sql server. The migration is for our AV database and was carried out using a simple backup from one server, then copy to the other server and restore the database there. Both are running SQL Server 2005 and neither are newly installed servers. The AV management service on the AV server is not restarting correctly so following their advice I'm trying some sql commands to troubleshoot.

 I haven't personally ran the below commands myself before this migration so I'm not sure if the migration has had an adverse affect or if the command would always have returned the same errors.

Upon running the following from the command line(where mssqlserver is the instance name) I get the below error

OSQL -E -S .\\mssqlserver -Q "SELECT NAME FROM SYSDATABASES"

[SQL Native Client]Unable to complete login process due to delay in opening
server connection


I've been researching various remedies and I've tried all the below but I'm still getting the same error:

Checked TCP/IP is enabled
Started the Server Browser Service
Ensured Windows Firewall is disabled.
Ensured I have full permissions on the DB
Set the DatabaseConnection MS for the particular database to Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=DBname;Data Source=SQLServer\instancename;

and also below without the servername so as to try both

Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=DBname;Data Source=instancename;

Any helpful ideas would be greatly appreciated.

Thanks in advance,

Brian
 
0
Greythorn
Asked:
Greythorn
  • 3
  • 3
1 Solution
 
BrandonGalderisiCommented:
mssqlserver is usally what is referred to as the "Default" instance.  Are you sure that your server is running in an instance.  Typically you would see (INSTANCE NAME) in the services list in the control panel.

You most likely are running the default instance which doesn't have a name.
0
 
GreythornAuthor Commented:
I've checked the services and MSSQLSERVER is listed on 4 of the SQL services there. Its also listed under the SQL Server Configuration Manager under Network Configuration so I'm assuming it is running that instance.

thanks
0
 
BrandonGalderisiCommented:
So those are the default instances most likely.

try this from the local server:


OSQL -E -Q "SELECT NAME FROM SYSDATABASES"

Without -S i will attempt to attach to the default instance on the local server.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
GreythornAuthor Commented:
The initial and follow up responses were very quick and easy to follow.
0
 
GreythornAuthor Commented:
That worked straight away and listed the required database. I guess it just couldn't connect to the default database with the earlier command.

Thanks a lot for you help Brandon.
0
 
BrandonGalderisiCommented:
It's not the default database, it's the default, non-named instance.

I'd recommend reading over this article I wrote.  Hopefully it will clear things up.

http://www.sqlservernation.com/home/what-are-instances.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now