Can't create Primary Key??

Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREAT
E UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been
terminated.
Rob
IT guy!Assuming your PK is compreised of columns Col1 and Col2, run the following:
select
Col1
, Col2
, count (*)
from
MyTable
group by
Col1
, Col2
having
count (*) > 1
This will reveal the duplicates.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREAT
E UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been
terminated.
Rob
IT guy!|||Assuming your PK is comprised of columns Col1 and Col2, run the following:
select
Col1
, Col2
, count (*)
from
MyTable
group by
Col1
, Col2
having
count (*) > 1
This will reveal the duplicates.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREAT
E UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been
terminated.
Rob
IT guy!|||Tom,
I try running the query but it times out. The table is quite large.
"Tom Moreau" wrote:

> Assuming your PK is comprised of columns Col1 and Col2, run the following:
> select
> Col1
> , Col2
> , count (*)
> from
> MyTable
> group by
> Col1
> , Col2
> having
> count (*) > 1
> This will reveal the duplicates.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
> Hi Guy's
> I've migrated an access DB of ours over to SQL 2000 and when I try to set
> the primary key I get the error below. I've checked for and deleted all of
> the other indexes so i'm not sure why it says a duplicate was found. I hav
e
> some other tables in the Database which I was able to set a primary key on
> but this table chokes each time. Help I'm stuck!
> 'Master' table
> - Unable to create index 'PK_Master'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CRE
ATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is 'type 6c, len 9'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not creat
e constraint.
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> --
> Rob
> IT guy!
>|||> I try running the query but it times out.
Where did you try running it? I suggest usng Query Analyzer, not Enterprise
Manager. You might also try
...
FROM MyTable WITH (NOLOCK)
...
http://www.aspfaq.com/
(Reverse address to reply.)|||Rob,
Try
select
Col1
, Col2
, count (*)
from
MyTable
where Col1 = 'type 6c, len 9'
group by
Col1
, Col2
having
count (*) > 1
This assumes that Col1 is the initial column of PK_master, the primary
key you are trying to set, since this value was provided in the error
message.
Steve Kass
Drew University
Rob wrote:
[vbcol=seagreen]
>Tom,
>I try running the query but it times out. The table is quite large.
>"Tom Moreau" wrote:
>
>|||You probably have no indexes on the table yet. Run it through QA and let it
finish.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:1960D77C-C7C8-40C7-B927-6FC1360C26DC@.microsoft.com...
Tom,
I try running the query but it times out. The table is quite large.
"Tom Moreau" wrote:

> Assuming your PK is comprised of columns Col1 and Col2, run the following:
> select
> Col1
> , Col2
> , count (*)
> from
> MyTable
> group by
> Col1
> , Col2
> having
> count (*) > 1
> This will reveal the duplicates.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
> Hi Guy's
> I've migrated an access DB of ours over to SQL 2000 and when I try to set
> the primary key I get the error below. I've checked for and deleted all of
> the other indexes so i'm not sure why it says a duplicate was found. I
have
> some other tables in the Database which I was able to set a primary key on
> but this table chokes each time. Help I'm stuck!
> 'Master' table
> - Unable to create index 'PK_Master'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CRE
ATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is 'type 6c, len 9'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create[/vbcol
]
constraint.[vbcol=seagreen]
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> --
> Rob
> IT guy!
>

Can't create Primary Key??

Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
--
Rob
IT guy!Assuming your PK is compreised of columns Col1 and Col2, run the following:
select
Col1
, Col2
, count (*)
from
MyTable
group by
Col1
, Col2
having
count (*) > 1
This will reveal the duplicates.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
--
Rob
IT guy!|||Assuming your PK is comprised of columns Col1 and Col2, run the following:
select
Col1
, Col2
, count (*)
from
MyTable
group by
Col1
, Col2
having
count (*) > 1
This will reveal the duplicates.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
--
Rob
IT guy!|||Tom,
I try running the query but it times out. The table is quite large.
"Tom Moreau" wrote:
> Assuming your PK is comprised of columns Col1 and Col2, run the following:
> select
> Col1
> , Col2
> , count (*)
> from
> MyTable
> group by
> Col1
> , Col2
> having
> count (*) > 1
> This will reveal the duplicates.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
> Hi Guy's
> I've migrated an access DB of ours over to SQL 2000 and when I try to set
> the primary key I get the error below. I've checked for and deleted all of
> the other indexes so i'm not sure why it says a duplicate was found. I have
> some other tables in the Database which I was able to set a primary key on
> but this table chokes each time. Help I'm stuck!
> 'Master' table
> - Unable to create index 'PK_Master'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is 'type 6c, len 9'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> --
> Rob
> IT guy!
>|||> I try running the query but it times out.
Where did you try running it? I suggest usng Query Analyzer, not Enterprise
Manager. You might also try
...
FROM MyTable WITH (NOLOCK)
...
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Rob,
Try
select
Col1
, Col2
, count (*)
from
MyTable
where Col1 = 'type 6c, len 9'
group by
Col1
, Col2
having
count (*) > 1
This assumes that Col1 is the initial column of PK_master, the primary
key you are trying to set, since this value was provided in the error
message.
Steve Kass
Drew University
Rob wrote:
>Tom,
>I try running the query but it times out. The table is quite large.
>"Tom Moreau" wrote:
>
>>Assuming your PK is comprised of columns Col1 and Col2, run the following:
>>select
>> Col1
>>, Col2
>>, count (*)
>>from
>> MyTable
>>group by
>> Col1
>>, Col2
>>having
>> count (*) > 1
>>This will reveal the duplicates.
>>--
>>Tom
>>---
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com
>>
>>"Rob" <Rob@.discussions.microsoft.com> wrote in message
>>news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
>>Hi Guy's
>>I've migrated an access DB of ours over to SQL 2000 and when I try to set
>>the primary key I get the error below. I've checked for and deleted all of
>>the other indexes so i'm not sure why it says a duplicate was found. I have
>>some other tables in the Database which I was able to set a primary key on
>>but this table chokes each time. Help I'm stuck!
>>'Master' table
>>- Unable to create index 'PK_Master'.
>>ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
>>INDEX terminated because a duplicate key was found for index ID 1. Most
>>significant primary key is 'type 6c, len 9'.
>>[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
>>See previous errors.
>>[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>>terminated.
>>--
>>Rob
>>IT guy!
>>
>>|||You probably have no indexes on the table yet. Run it through QA and let it
finish.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:1960D77C-C7C8-40C7-B927-6FC1360C26DC@.microsoft.com...
Tom,
I try running the query but it times out. The table is quite large.
"Tom Moreau" wrote:
> Assuming your PK is comprised of columns Col1 and Col2, run the following:
> select
> Col1
> , Col2
> , count (*)
> from
> MyTable
> group by
> Col1
> , Col2
> having
> count (*) > 1
> This will reveal the duplicates.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
> Hi Guy's
> I've migrated an access DB of ours over to SQL 2000 and when I try to set
> the primary key I get the error below. I've checked for and deleted all of
> the other indexes so i'm not sure why it says a duplicate was found. I
have
> some other tables in the Database which I was able to set a primary key on
> but this table chokes each time. Help I'm stuck!
> 'Master' table
> - Unable to create index 'PK_Master'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is 'type 6c, len 9'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint.
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> --
> Rob
> IT guy!
>

Cant connect to Reporting Services

our IT guy installed Reporting Services on our test server, but the problem is that I cannot not connect to it. It says the the report services instance coundt not be found. Am I correct in saying that report services needs its own instance?? Cause when I put it on my local computer I gave it its own instance and theres no problems thereWithout knowing the exact error, it's going to be difficult for anyone to help.|||Cannot connect to GCSQLTESTTITLE: Connect to Server
----------

Cannot connect to GCSQLTEST.

----------
ADDITIONAL INFORMATION:

The Report Services Instance could not be found (Microsoft.SqlServer.Management.UI.RSClient)

----------

Can't connect to databases through Enterprise Manager

Understand I am not a DBA. I am just the guy who has to
work with this stuff.
We have client software that uses SQL 7 as a database.
Winpak is a door access software made by Northern. It
stores proxy card info, access levels, historys, etc. in
a SQL 7 database. I know the databases are still there
because Winpak connects and I can pull up info.
This started because I noticed whoever set up the primary
and archive databases pointed them both to the same file
name. I backed up the primary and deleted it. Restored
it with the full backup and made sure it was pointed to a
different file name. Everything worked fine and still
showed up in Enterprise Manager. I restarted the computer
later and tried to go back into Enterprise Manager. Tried
to expand 'SQL Server Group' to list all the databases and
I got an error saying, "A connection could not be
established to COMPUTER - Cannot open user default
database '<ID>'. Using master database instead. Please
verify SQL Server is running and check your SQL Server
registration properties (by right-clicking on the COMPUTER
node) and try again."
My services were running just fine and when I checked my
registration properties everything looked fine. Redid the
registration properties with no luck. Using NT
authentication and even making a new one. The other
problem is that somebody set this thing up 3 years ago and
I have no idea what the 'sa' password is and I have tried
every simple password I can think of. I am checked out
the knowledge database articles matching my error message
and they didn't realy help cuz I either needed the 'sa'
password or I don't know how to impliment the solution
based on my limited knowledge of SQL. I need to do a
final backup to move all the info to a new machine with
SQL 2000. I am really stuck (and screwed) if I can't get
that info off cuz we just entered in a few hundred more
employees so using an old backup isn't going to save me.
Help would be greatly appreciated
Hi,
It seems the Login ID you are using is defaulted to the database you have
deleted. That is the reason you are getting the error.
How to change the SA Password,
1. Login to Query analyzer using Windows Authentication
2. Execute the beloe command
sp_password null,'passwordsa',sa
(now the password for sa is passwordsa)
How to change the default database of your existing users, which gave error
1. Login to query analyzer as SA
2. execute below statement
sp_defaultdb <loginame>,<new database name>
3. After this you will be able to login using the old id itself.
Thanks
Hari
MCDBA
"Brendan" <anonymous@.discussions.microsoft.com> wrote in message
news:124bf01c41152$811230e0$a501280a@.phx.gbl...
> Understand I am not a DBA. I am just the guy who has to
> work with this stuff.
> We have client software that uses SQL 7 as a database.
> Winpak is a door access software made by Northern. It
> stores proxy card info, access levels, historys, etc. in
> a SQL 7 database. I know the databases are still there
> because Winpak connects and I can pull up info.
> This started because I noticed whoever set up the primary
> and archive databases pointed them both to the same file
> name. I backed up the primary and deleted it. Restored
> it with the full backup and made sure it was pointed to a
> different file name. Everything worked fine and still
> showed up in Enterprise Manager. I restarted the computer
> later and tried to go back into Enterprise Manager. Tried
> to expand 'SQL Server Group' to list all the databases and
> I got an error saying, "A connection could not be
> established to COMPUTER - Cannot open user default
> database '<ID>'. Using master database instead. Please
> verify SQL Server is running and check your SQL Server
> registration properties (by right-clicking on the COMPUTER
> node) and try again."
> My services were running just fine and when I checked my
> registration properties everything looked fine. Redid the
> registration properties with no luck. Using NT
> authentication and even making a new one. The other
> problem is that somebody set this thing up 3 years ago and
> I have no idea what the 'sa' password is and I have tried
> every simple password I can think of. I am checked out
> the knowledge database articles matching my error message
> and they didn't realy help cuz I either needed the 'sa'
> password or I don't know how to impliment the solution
> based on my limited knowledge of SQL. I need to do a
> final backup to move all the info to a new machine with
> SQL 2000. I am really stuck (and screwed) if I can't get
> that info off cuz we just entered in a few hundred more
> employees so using an old backup isn't going to save me.
> Help would be greatly appreciated
|||Thanks for the post. It more or less led me to a
solution. I tried using NT authentication with no
success. Realized the machine was running on the admin
account with no password. For giggles I made a new admin
account with a different user name and an actual password
and tried again. Low and behold it worked. I got into
query analyzer and changed the sa password. Problem
solved. Thanks again.

>--Original Message--
>Hi,
>It seems the Login ID you are using is defaulted to the
database you have
>deleted. That is the reason you are getting the error.
>How to change the SA Password,
>1. Login to Query analyzer using Windows Authentication
>2. Execute the beloe command
> sp_password null,'passwordsa',sa
>(now the password for sa is passwordsa)
>How to change the default database of your existing
users, which gave error
>1. Login to query analyzer as SA
>2. execute below statement
> sp_defaultdb <loginame>,<new database name>
>3. After this you will be able to login using the old id
itself.
>Thanks
>Hari
>MCDBA
>
>
>"Brendan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:124bf01c41152$811230e0$a501280a@.phx.gbl...
to
in
there
primary
file
Restored
to a
computer
Tried
and
Please
COMPUTER
my
the
and
tried
message
get
me.
>
>.
>

Can't connect to databases through Enterprise Manager

Understand I am not a DBA. I am just the guy who has to
work with this stuff.
We have client software that uses SQL 7 as a database.
Winpak is a door access software made by Northern. It
stores proxy card info, access levels, historys, etc. in
a SQL 7 database. I know the databases are still there
because Winpak connects and I can pull up info.
This started because I noticed whoever set up the primary
and archive databases pointed them both to the same file
name. I backed up the primary and deleted it. Restored
it with the full backup and made sure it was pointed to a
different file name. Everything worked fine and still
showed up in Enterprise Manager. I restarted the computer
later and tried to go back into Enterprise Manager. Tried
to expand 'SQL Server Group' to list all the databases and
I got an error saying, "A connection could not be
established to COMPUTER - Cannot open user default
database '<ID>'. Using master database instead. Please
verify SQL Server is running and check your SQL Server
registration properties (by right-clicking on the COMPUTER
node) and try again."
My services were running just fine and when I checked my
registration properties everything looked fine. Redid the
registration properties with no luck. Using NT
authentication and even making a new one. The other
problem is that somebody set this thing up 3 years ago and
I have no idea what the 'sa' password is and I have tried
every simple password I can think of. I am checked out
the knowledge database articles matching my error message
and they didn't realy help cuz I either needed the 'sa'
password or I don't know how to impliment the solution
based on my limited knowledge of SQL. I need to do a
final backup to move all the info to a new machine with
SQL 2000. I am really stuck (and screwed) if I can't get
that info off cuz we just entered in a few hundred more
employees so using an old backup isn't going to save me.
Help would be greatly appreciatedHi,
It seems the Login ID you are using is defaulted to the database you have
deleted. That is the reason you are getting the error.
How to change the SA Password,
1. Login to Query analyzer using Windows Authentication
2. Execute the beloe command
sp_password null,'passwordsa',sa
(now the password for sa is passwordsa)
How to change the default database of your existing users, which gave error
1. Login to query analyzer as SA
2. execute below statement
sp_defaultdb <loginame>,<new database name>
3. After this you will be able to login using the old id itself.
Thanks
Hari
MCDBA
"Brendan" <anonymous@.discussions.microsoft.com> wrote in message
news:124bf01c41152$811230e0$a501280a@.phx
.gbl...
> Understand I am not a DBA. I am just the guy who has to
> work with this stuff.
> We have client software that uses SQL 7 as a database.
> Winpak is a door access software made by Northern. It
> stores proxy card info, access levels, historys, etc. in
> a SQL 7 database. I know the databases are still there
> because Winpak connects and I can pull up info.
> This started because I noticed whoever set up the primary
> and archive databases pointed them both to the same file
> name. I backed up the primary and deleted it. Restored
> it with the full backup and made sure it was pointed to a
> different file name. Everything worked fine and still
> showed up in Enterprise Manager. I restarted the computer
> later and tried to go back into Enterprise Manager. Tried
> to expand 'SQL Server Group' to list all the databases and
> I got an error saying, "A connection could not be
> established to COMPUTER - Cannot open user default
> database '<ID>'. Using master database instead. Please
> verify SQL Server is running and check your SQL Server
> registration properties (by right-clicking on the COMPUTER
> node) and try again."
> My services were running just fine and when I checked my
> registration properties everything looked fine. Redid the
> registration properties with no luck. Using NT
> authentication and even making a new one. The other
> problem is that somebody set this thing up 3 years ago and
> I have no idea what the 'sa' password is and I have tried
> every simple password I can think of. I am checked out
> the knowledge database articles matching my error message
> and they didn't realy help cuz I either needed the 'sa'
> password or I don't know how to impliment the solution
> based on my limited knowledge of SQL. I need to do a
> final backup to move all the info to a new machine with
> SQL 2000. I am really stuck (and screwed) if I can't get
> that info off cuz we just entered in a few hundred more
> employees so using an old backup isn't going to save me.
> Help would be greatly appreciated|||Thanks for the post. It more or less led me to a
solution. I tried using NT authentication with no
success. Realized the machine was running on the admin
account with no password. For giggles I made a new admin
account with a different user name and an actual password
and tried again. Low and behold it worked. I got into
query analyzer and changed the sa password. Problem
solved. Thanks again.

>--Original Message--
>Hi,
>It seems the Login ID you are using is defaulted to the
database you have
>deleted. That is the reason you are getting the error.
>How to change the SA Password,
>1. Login to Query analyzer using Windows Authentication
>2. Execute the beloe command
> sp_password null,'passwordsa',sa
>(now the password for sa is passwordsa)
>How to change the default database of your existing
users, which gave error
>1. Login to query analyzer as SA
>2. execute below statement
> sp_defaultdb <loginame>,<new database name>
>3. After this you will be able to login using the old id
itself.
>Thanks
>Hari
>MCDBA
>
>
>"Brendan" <anonymous@.discussions.microsoft.com> wrote in
message
> news:124bf01c41152$811230e0$a501280a@.phx
.gbl...
to
in
there
primary
file
Restored
to a
computer
Tried
and
Please
COMPUTER
my
the
and
tried
message
get
me.
>
>.
>