A connection could not be established...Cannot open user default database...

Hello,
I'm running SQL7 on a Win2K server. I was in the midst of
restoring a database when I lost connectivity to the
network and the job failed.
Now if I try to view the database within Enterprise
Manager, I get the following error:
---
A connection could not be established to <servername>.
Cannot open user default database '<ID>. Using master
database instead. Please varify SQL Server is running and
check your SQL Server registration properties (by right-
clicking on the <servername> node) and try again.
---
I checked and SQL is running and the registration
properties are correct.
Any help would be appreciated.Seems like the database is "lost", as you terminated the restore, and the
logins that your EM registration login is using has that database as the
default database. I suggest you login using ISQL.EXE and change default
database using sp_defaultdb.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:0c6201c3df7b$ee9a7fe0$a101280a@.phx.gbl...
> Hello,
> I'm running SQL7 on a Win2K server. I was in the midst of
> restoring a database when I lost connectivity to the
> network and the job failed.
> Now if I try to view the database within Enterprise
> Manager, I get the following error:
> ---
> A connection could not be established to <servername>.
> Cannot open user default database '<ID>. Using master
> database instead. Please varify SQL Server is running and
> check your SQL Server registration properties (by right-
> clicking on the <servername> node) and try again.
> ---
> I checked and SQL is running and the registration
> properties are correct.
> Any help would be appreciated.
>|||Thanks Tibor - I tried that as outlined in MS KB 196076,
but I still get a the same error.(?)
>--Original Message--
>Seems like the database is "lost", as you terminated the
restore, and the
>logins that your EM registration login is using has that
database as the
>default database. I suggest you login using ISQL.EXE and
change default
>database using sp_defaultdb.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0c6201c3df7b$ee9a7fe0$a101280a@.phx.gbl...
>> Hello,
>> I'm running SQL7 on a Win2K server. I was in the midst
of
>> restoring a database when I lost connectivity to the
>> network and the job failed.
>> Now if I try to view the database within Enterprise
>> Manager, I get the following error:
>> ---
>> A connection could not be established to <servername>.
>> Cannot open user default database '<ID>. Using master
>> database instead. Please varify SQL Server is running
and
>> check your SQL Server registration properties (by right-
>> clicking on the <servername> node) and try again.
>> ---
>> I checked and SQL is running and the registration
>> properties are correct.
>> Any help would be appreciated.
>>
>
>.
>|||Tibor Karaszi wrote:
> Seems like the database is "lost", as you terminated the restore, and the
> logins that your EM registration login is using has that database as the
> default database. I suggest you login using ISQL.EXE and change default
> database using sp_defaultdb.
>
Thanks for the information. That seems to have fixed the lost db server
issue in the drop down. Still can't get the damn app server to
connect to the db server though. Will keep searching.|||> Thanks for the information. That seems to have fixed the lost db server
> issue in the drop down. Still can't get the damn app server to connect
> to the db server though. Will keep searching.
Everyone may ignore my last post.

A connection could not be established...Cannot open user default database...

Hello,
I'm running SQL7 on a Win2K server. I was in the midst of
restoring a database when I lost connectivity to the
network and the job failed.
Now if I try to view the database within Enterprise
Manager, I get the following error:
---
A connection could not be established to <servername>.
Cannot open user default database '<ID>. Using master
database instead. Please varify SQL Server is running and
check your SQL Server registration properties (by right-
clicking on the <servername> node) and try again.
---
I checked and SQL is running and the registration
properties are correct.
Any help would be appreciated.Seems like the database is "lost", as you terminated the restore, and the
logins that your EM registration login is using has that database as the
default database. I suggest you login using ISQL.EXE and change default
database using sp_defaultdb.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:0c6201c3df7b$ee9a7fe0$a101280a@.phx.gbl...

quote:

---

> Hello,
> I'm running SQL7 on a Win2K server. I was in the midst of
> restoring a database when I lost connectivity to the
> network and the job failed.
> Now if I try to view the database within Enterprise
> Manager, I get the following error:
> ---
> A connection could not be established to <servername>.
> Cannot open user default database '<ID>. Using master
> database instead. Please varify SQL Server is running and
> check your SQL Server registration properties (by right-
> clicking on the <servername> node) and try again.
> ---
> I checked and SQL is running and the registration
> properties are correct.
> Any help would be appreciated.
>

---

|||Thanks Tibor - I tried that as outlined in MS KB 196076,
but I still get a the same error.(?)

quote:

---

>--Original Message--
>Seems like the database is "lost", as you terminated the

---

restore, and the

quote:

---

>logins that your EM registration login is using has that

---

database as the

quote:

---

>default database. I suggest you login using ISQL.EXE and

---

change default

quote:

---

>database using sp_defaultdb.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?

---

oi=djq&as_ugroup=microsoft.public.sqlserver

quote:

---

>
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:0c6201c3df7b$ee9a7fe0$a101280a@.phx.gbl...
of[QUOTE]
and[QUOTE]
>
>.
>

---

A better Windows Scheduler

I am looking for a scheduler to run batch jobs and vbscripts. I like
the SQLServerAgent Job scheduler but these jobs will not be running on
a SQL server.
The features I like in SQL are: history, email notifications and
advanced scheduling logic. Also, as a DBA, I've found SQLAgent to be
trustworthy - stable and reliable (unlike Windows Scheduler - which I
find pale in comparisson).
I'm aware that I could add this logic to the batches themselves, but
for the amount of files that will be scheduled daily - that's not a
practical solution.
Anyone thoughts? Thanks.I know CA do a scheduler but i think it will be overkill for your
requirements. Think it's called Unicentre
--
HTH
Ryan Waight, MCDBA, MCSE
"Sean O'Thule" <othule@.hotmail.com> wrote in message
news:63840202.0310060541.15fcf0e1@.posting.google.com...
> I am looking for a scheduler to run batch jobs and vbscripts. I like
> the SQLServerAgent Job scheduler but these jobs will not be running on
> a SQL server.
> The features I like in SQL are: history, email notifications and
> advanced scheduling logic. Also, as a DBA, I've found SQLAgent to be
> trustworthy - stable and reliable (unlike Windows Scheduler - which I
> find pale in comparisson).
> I'm aware that I could add this logic to the batches themselves, but
> for the amount of files that will be scheduled daily - that's not a
> practical solution.
> Anyone thoughts? Thanks.|||It may not meet you needs, but have you considered using DTS packages as the
container for managing the VB scripts, etc you need to run. You can then use
SQL Agent to actually manage the DTS packages.
You could easily run jobs that are not actually on the SQL Server... Not a
true general purpose scheduler, but it's free.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Sean O'Thule" <othule@.hotmail.com> wrote in message
news:63840202.0310060541.15fcf0e1@.posting.google.com...
> I am looking for a scheduler to run batch jobs and vbscripts. I like
> the SQLServerAgent Job scheduler but these jobs will not be running on
> a SQL server.
> The features I like in SQL are: history, email notifications and
> advanced scheduling logic. Also, as a DBA, I've found SQLAgent to be
> trustworthy - stable and reliable (unlike Windows Scheduler - which I
> find pale in comparisson).
> I'm aware that I could add this logic to the batches themselves, but
> for the amount of files that will be scheduled daily - that's not a
> practical solution.
> Anyone thoughts? Thanks.|||I think you are referring to Autosys.
Linchi
>--Original Message--
>I know CA do a scheduler but i think it will be overkill
for your
>requirements. Think it's called Unicentre
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Sean O'Thule" <othule@.hotmail.com> wrote in message
>news:63840202.0310060541.15fcf0e1@.posting.google.com...
>> I am looking for a scheduler to run batch jobs and
vbscripts. I like
>> the SQLServerAgent Job scheduler but these jobs will
not be running on
>> a SQL server.
>> The features I like in SQL are: history, email
notifications and
>> advanced scheduling logic. Also, as a DBA, I've found
SQLAgent to be
>> trustworthy - stable and reliable (unlike Windows
Scheduler - which I
>> find pale in comparisson).
>> I'm aware that I could add this logic to the batches
themselves, but
>> for the amount of files that will be scheduled daily -
that's not a
>> practical solution.
>> Anyone thoughts? Thanks.
>
>.
>|||Looks expensive, I forgot to mention, I was hoping to find something
inexpensive or free.
I'm surprised MS doesn't take the componenets of the SQLAgent job
scheduler and generalize it for the OS. It's really a useful tool.
I want to avoid running everything from a SQL server. The first
server that I am looking to run this on will be an ftp server. Bits
of code will exist on that server itself (to encrypt/decrypt files).
So I prefer to run scripts locally.
Thanks for the input.

A basic question: Removing duplicate results from Max function

Hi,

Say I have a table Job with columns name, date, salary . I want to get
the name ,date and salary for the date when that person earned maximum
salary. I am using something like

SELECT X.name,X.date,X.salary
FROM job X
WHERE X.salary IN
(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);

The problem is ; if a person earns maximum salary on two dates, both of
the dates are printed. I just want to get any one of those two rows.
I tried

SELECT X.name,Min(X.date),X.salary
FROM job X
WHERE X.salary IN
(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);

but it gives error.
Can anybody please suggest a solution?

Regards,
Aamir(aamircheema@.gmail.com) writes:
> Say I have a table Job with columns name, date, salary . I want to get
> the name ,date and salary for the date when that person earned maximum
> salary. I am using something like
>
> SELECT X.name,X.date,X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>
> The problem is ; if a person earns maximum salary on two dates, both of
> the dates are printed. I just want to get any one of those two rows.
> I tried
> SELECT X.name,Min(X.date),X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
> but it gives error.

SELECT a.name, a.date, a.salary
FROM tbl a
JOIN (SELECT b.name, date = MAX(b.date)
FROM tbl b
JOIN (SELECT name, salary = MAX(salary)
FROM tbl
GROUP BY name) c ON c.name = b.name
AND c.salary = b.salary
GROUP BY a1.name) b ON a.name = b.name
AND a.date = b.date

This presumes that (name, date) is unique, and a person does not have
two salaries the same day.

The inner selects are derived tables - sort of virtual temp tables within
the query. A very powerful tool to write complex queries. A derived table
is independent of the outer query, and this why the alias b can be reused.
Note that they are not necessarily computed in whole - the optimizer often
recast computation order for a very very effceient query plan.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||aamircheema@.gmail.com wrote:
> Hi,
> Say I have a table Job with columns name, date, salary . I want to get
> the name ,date and salary for the date when that person earned maximum
> salary. I am using something like
>
> SELECT X.name,X.date,X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>
> The problem is ; if a person earns maximum salary on two dates, both of
> the dates are printed. I just want to get any one of those two rows.
> I tried
> SELECT X.name,Min(X.date),X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
> but it gives error.
> Can anybody please suggest a solution?
> Regards,
> Aamir

In SQL Server 2005:

WITH j AS
(SELECT name, date, salary,
RANK() OVER (PARTITION BY name ORDER BY salary DESC, date DESC) rnk
FROM job)
SELECT name, date, salary
FROM j
WHERE rnk = 1 ;

That assumes the combination of (name,salary,date) is unique. If it
isn't then just add other columns to the ORDER BY specification to make
a key.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||On 10 Jun 2006 07:48:38 -0700, aamircheema@.gmail.com wrote:

(snip)
>I tried
>SELECT X.name,Min(X.date),X.salary
>FROM job X
>WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>but it gives error.
>Can anybody please suggest a solution?

Hi Aamir,

Here's a third suggestion:

SELECT X.name,Min(X.date),X.salary
FROM job X
WHERE X.salary IN
(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name)
GROUP BY X.name, X.salary;

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP|||
Thanks Everybody. That helped a lot

60 days of backups

I have had a request to run backups of a database to disk, and retain the
backups until they are 60 days old. I've got the job set up, but it either
appends or overwrites the previous job. Does anyone know of a way to do
this?
TIA,
KSee
http://www.nigelrivett.net/BackupAllDatabases.html
You could use the sqlmaint exe but I think backups are too
important.
>--Original Message--
>I have had a request to run backups of a database to
disk, and retain the
>backups until they are 60 days old. I've got the job set
up, but it either
>appends or overwrites the previous job. Does anyone know
of a way to do
>this?
>TIA,
>K
>
>.
>|||Use the maintenance function in SLQ and enable the SQL server agent service
--
J
www.urbanvoyeur.com
"msnews.microsoft.com" <kb@.blah.com> wrote in message
news:uet5nU7iDHA.1696@.TK2MSFTNGP12.phx.gbl...
> I have had a request to run backups of a database to disk, and retain the
> backups until they are 60 days old. I've got the job set up, but it
either
> appends or overwrites the previous job. Does anyone know of a way to do
> this?
> TIA,
> K
>|||Thanks for the help...I setup a maintenance plan and have my fingers crossed
that this will work!
K
"UrbanVoyeur" <nospam@.nospam.com> wrote in message
news:ey9Bi4AjDHA.1676@.TK2MSFTNGP09.phx.gbl...
> Use the maintenance function in SLQ and enable the SQL server agent
service
> --
> J
> www.urbanvoyeur.com
>
> "msnews.microsoft.com" <kb@.blah.com> wrote in message
> news:uet5nU7iDHA.1696@.TK2MSFTNGP12.phx.gbl...
> > I have had a request to run backups of a database to disk, and retain
the
> > backups until they are 60 days old. I've got the job set up, but it
> either
> > appends or overwrites the previous job. Does anyone know of a way to do
> > this?
> >
> > TIA,
> > K
> >
> >
>

3rd party tools for job scheduling

I am looking for a better way to schedule jobs in SQL Server. I found
this tool called SQLschedule at www.idera.com and am using the 30 day
trial. This tool really has a cool GUI, and seems to work well. Does
anyone else have any experience with their tools?Are you aware of SQL Server's own Agent? What functionality are you looking
for that Agent doesn't provide?

--
David Portas
SQL Server MVP
--