Can't connect as SA through ODBC manager...why?

Hi All,
I can connect to my MSDE server by logging in as SA from the command line
but not through the ODBC manager utility in windows. I need to setup system
DSNs for a few applications but can't because they require I use SQL
authentication. When I reach that step in the ODBC manager I type in SA and
the password and hit next I get a 18456 "login failed for sa" error. My
server is in mixed mode (I checked the registry), the domain admins group has
login privilages to MSDE, I have no firewall active on any NICs, and none of
my passwords contain a semicolon. I have no clue what I'm missing here. Can
some one point me in the right direction or link me to a good article on how
to setup system dsns through the command line?
Thanks,
Prikly Pete Peterson
MSDE can only be used as the source of the data. It's not licensed to host
Reporting Services (as we discuss in our Reporting Services
book --www.sqlreportingservices.net). Chapter 2 (freely downloadable from
the site) explains why.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"ComfortablySAD" <ComfortablySAD@.discussions.microsoft.com> wrote in message
news:32E686BF-63A6-4DFF-9A7D-A00E8B6CAE09@.microsoft.com...
> Hi All,
> I can connect to my MSDE server by logging in as SA from the command line
> but not through the ODBC manager utility in windows. I need to setup
> system
> DSNs for a few applications but can't because they require I use SQL
> authentication. When I reach that step in the ODBC manager I type in SA
> and
> the password and hit next I get a 18456 "login failed for sa" error. My
> server is in mixed mode (I checked the registry), the domain admins group
> has
> login privilages to MSDE, I have no firewall active on any NICs, and none
> of
> my passwords contain a semicolon. I have no clue what I'm missing here.
> Can
> some one point me in the right direction or link me to a good article on
> how
> to setup system dsns through the command line?
> Thanks,
> Prikly Pete Peterson
sql

can't backup - urgent

Hi,
When I do the backup and click on the add button, an error come
out:
Microsoft SQL-DMO (ODBC SQLSTATE: 42000)
Error 0: Cannot load the DLL xpstar.dll , or one of the DLLs it references.
Reason:998 (invalid access to memory location)
Please help. I cannot do the backup now. Thanks.
Allen,
Have you tried running the backup from Query Analyzer instead of Enterprise
Manager, using the BACKUP DATABASE syntax? I don't know what that error
means, but don't let the UI's failure to respond stop you...
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:eGZiOvbqEHA.2856@.TK2MSFTNGP10.phx.gbl...
> Hi,
> When I do the backup and click on the add button, an error come
> out:
> Microsoft SQL-DMO (ODBC SQLSTATE: 42000)
> Error 0: Cannot load the DLL xpstar.dll , or one of the DLLs it
references.
> Reason:998 (invalid access to memory location)
>
> Please help. I cannot do the backup now. Thanks.
>
|||Hi,
Should be MDAC issue. But as an immediate solution execute the backup
database command from query analyzer for backup.
Backup database <dbname> to disk='c:\backup\dbname.bak' with init,stats=10
After the backup, First update the SQL server by SP3a (latest service pack)
and try backup from Enterprise manager.
If you still have issues try updating latest MDAC
Thanks
Hari
MCDBA
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:eGZiOvbqEHA.2856@.TK2MSFTNGP10.phx.gbl...
> Hi,
> When I do the backup and click on the add button, an error come
> out:
> Microsoft SQL-DMO (ODBC SQLSTATE: 42000)
> Error 0: Cannot load the DLL xpstar.dll , or one of the DLLs it
> references. Reason:998 (invalid access to memory location)
>
> Please help. I cannot do the backup now. Thanks.
>
|||Hari and Adam,
This SQL server has already been applied SP3a long time ago.
Last week we got no problem to do the daily backup. But it suddenly happened
this morning. Any clue. I will try the query analyzer now. Thanks.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OfRez2bqEHA.1272@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Should be MDAC issue. But as an immediate solution execute the backup
> database command from query analyzer for backup.
> Backup database <dbname> to disk='c:\backup\dbname.bak' with init,stats=10
> After the backup, First update the SQL server by SP3a (latest service
> pack) and try backup from Enterprise manager.
> If you still have issues try updating latest MDAC
> Thanks
> Hari
> MCDBA
>
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:eGZiOvbqEHA.2856@.TK2MSFTNGP10.phx.gbl...
>
|||> This SQL server has already been applied SP3a long time ago.
So some other program with an improper install program or version DLL checking might mess thing up
for the SQL Server. This is why Hari suggested to *reinstall* sp 3, so it might sort things out for
you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:OhY6AGcqEHA.3592@.TK2MSFTNGP14.phx.gbl...
> Hari and Adam,
> This SQL server has already been applied SP3a long time ago. Last week we got no
> problem to do the daily backup. But it suddenly happened this morning. Any clue. I will try the
> query analyzer now. Thanks.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OfRez2bqEHA.1272@.TK2MSFTNGP09.phx.gbl...
>
|||I see. Will this re-apply cause any impact or conflict? Do I need to reboot
the server after? THanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OBI6zYdqEHA.3172@.TK2MSFTNGP10.phx.gbl...
> So some other program with an improper install program or version DLL
> checking might mess thing up for the SQL Server. This is why Hari
> suggested to *reinstall* sp 3, so it might sort things out for you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:OhY6AGcqEHA.3592@.TK2MSFTNGP14.phx.gbl...
>

can't backup - urgent

Hi,
When I do the backup and click on the add button, an error come
out:
Microsoft SQL-DMO (ODBC SQLSTATE: 42000)
Error 0: Cannot load the DLL xpstar.dll , or one of the DLLs it references.
Reason:998 (invalid access to memory location)
Please help. I cannot do the backup now. Thanks.Allen,
Have you tried running the backup from Query Analyzer instead of Enterprise
Manager, using the BACKUP DATABASE syntax? I don't know what that error
means, but don't let the UI's failure to respond stop you...
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:eGZiOvbqEHA.2856@.TK2MSFTNGP10.phx.gbl...
> Hi,
> When I do the backup and click on the add button, an error come
> out:
> Microsoft SQL-DMO (ODBC SQLSTATE: 42000)
> Error 0: Cannot load the DLL xpstar.dll , or one of the DLLs it
references.
> Reason:998 (invalid access to memory location)
>
> Please help. I cannot do the backup now. Thanks.
>|||Hi,
Should be MDAC issue. But as an immediate solution execute the backup
database command from query analyzer for backup.
Backup database <dbname> to disk='c:\backup\dbname.bak' with init,stats=10
After the backup, First update the SQL server by SP3a (latest service pack)
and try backup from Enterprise manager.
If you still have issues try updating latest MDAC
Thanks
Hari
MCDBA
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:eGZiOvbqEHA.2856@.TK2MSFTNGP10.phx.gbl...
> Hi,
> When I do the backup and click on the add button, an error come
> out:
> Microsoft SQL-DMO (ODBC SQLSTATE: 42000)
> Error 0: Cannot load the DLL xpstar.dll , or one of the DLLs it
> references. Reason:998 (invalid access to memory location)
>
> Please help. I cannot do the backup now. Thanks.
>|||Hari and Adam,
This SQL server has already been applied SP3a long time ago.
Last week we got no problem to do the daily backup. But it suddenly happened
this morning. Any clue. I will try the query analyzer now. Thanks.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OfRez2bqEHA.1272@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Should be MDAC issue. But as an immediate solution execute the backup
> database command from query analyzer for backup.
> Backup database <dbname> to disk='c:\backup\dbname.bak' with init,stats=10
> After the backup, First update the SQL server by SP3a (latest service
> pack) and try backup from Enterprise manager.
> If you still have issues try updating latest MDAC
> Thanks
> Hari
> MCDBA
>
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:eGZiOvbqEHA.2856@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> When I do the backup and click on the add button, an error come
>> out:
>> Microsoft SQL-DMO (ODBC SQLSTATE: 42000)
>> Error 0: Cannot load the DLL xpstar.dll , or one of the DLLs it
>> references. Reason:998 (invalid access to memory location)
>>
>> Please help. I cannot do the backup now. Thanks.
>|||> This SQL server has already been applied SP3a long time ago.
So some other program with an improper install program or version DLL checking might mess thing up
for the SQL Server. This is why Hari suggested to *reinstall* sp 3, so it might sort things out for
you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:OhY6AGcqEHA.3592@.TK2MSFTNGP14.phx.gbl...
> Hari and Adam,
> This SQL server has already been applied SP3a long time ago. Last week we got no
> problem to do the daily backup. But it suddenly happened this morning. Any clue. I will try the
> query analyzer now. Thanks.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OfRez2bqEHA.1272@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> Should be MDAC issue. But as an immediate solution execute the backup database command from
>> query analyzer for backup.
>> Backup database <dbname> to disk='c:\backup\dbname.bak' with init,stats=10
>> After the backup, First update the SQL server by SP3a (latest service pack) and try backup from
>> Enterprise manager.
>> If you still have issues try updating latest MDAC
>> Thanks
>> Hari
>> MCDBA
>>
>>
>> ""Allen Iverson"" <no_spam@.bk.com> wrote in message news:eGZiOvbqEHA.2856@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> When I do the backup and click on the add button, an error come out:
>> Microsoft SQL-DMO (ODBC SQLSTATE: 42000)
>> Error 0: Cannot load the DLL xpstar.dll , or one of the DLLs it references. Reason:998 (invalid
>> access to memory location)
>>
>> Please help. I cannot do the backup now. Thanks.
>>
>|||I see. Will this re-apply cause any impact or conflict? Do I need to reboot
the server after? THanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OBI6zYdqEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> This SQL server has already been applied SP3a long time ago.
> So some other program with an improper install program or version DLL
> checking might mess thing up for the SQL Server. This is why Hari
> suggested to *reinstall* sp 3, so it might sort things out for you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:OhY6AGcqEHA.3592@.TK2MSFTNGP14.phx.gbl...
>> Hari and Adam,
>> This SQL server has already been applied SP3a long time ago.
>> Last week we got no problem to do the daily backup. But it suddenly
>> happened this morning. Any clue. I will try the query analyzer now.
>> Thanks.
>>
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:OfRez2bqEHA.1272@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> Should be MDAC issue. But as an immediate solution execute the backup
>> database command from query analyzer for backup.
>> Backup database <dbname> to disk='c:\backup\dbname.bak' with
>> init,stats=10
>> After the backup, First update the SQL server by SP3a (latest service
>> pack) and try backup from Enterprise manager.
>> If you still have issues try updating latest MDAC
>> Thanks
>> Hari
>> MCDBA
>>
>>
>> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
>> news:eGZiOvbqEHA.2856@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> When I do the backup and click on the add button, an error
>> come out:
>> Microsoft SQL-DMO (ODBC SQLSTATE: 42000)
>> Error 0: Cannot load the DLL xpstar.dll , or one of the DLLs it
>> references. Reason:998 (invalid access to memory location)
>>
>> Please help. I cannot do the backup now. Thanks.
>>
>>
>sql

cannot run DTS Package from SQL Query Analyzer

I attemped to run dts package to transform data from AS400/DB2 by using Client Access ODBC ( Ms Windows 2000 Server, Ms SQL 2K, Client Access Driver 4.5)

but the result of testing has messed me up

because when i used this command via SQL Query Analyzer

exec master..xp_cmdshell 'DTSRun /S "MySVR" /U "sa" /N "dtsTEST" /G "{482209FD-0891-11D8-868C-00E0299B4BCF}" /A "G1":"8"="200310" /W "0"'

but it doesn't work

I wonder why.. because it can be executed in MS-Dos prompt successfully.
if I type

DTSRun /S "MySVR" /U "sa" /N "dtsTEST" /G "{482209FD-0891-11D8-868C-00E0299B4BCF}" /A "G1":"8"="200310" /W "0"

in the command line.

If somebody know please give me some advice (urgently!!).What is the error?

When run from dos it will be run on your client m/c. When from query analyser on the server using the sql server service account (probably)

Try logging on to the server as the sql server service account and running it in the designer and see what happens.|||I didn't run dos-command on Client M/C, I tested on Server M/C itself
and it success.

But when I tried to run by query analyzer, it takes much time (> 20 min and nothing happens) for executing and there was no error show on the screen. so I had to kill the process because it seemed not work .
And this process I tested on Server M/C also, by using Sql Admin (sa).

How could I solve this problem?|||It can be run properly in DTS Designer, but cannot run in Query analyzer.|||Does it have any user interaction - have you left in a debug msgbox?

I meant the account that the sql server service uses to logon - see services.
Maybe it needs access to something that this account doesn't have permission on.|||I checked already that the log on user-id has full permission, and the sign on user name and password for AS400 has been specify in DSN that use via client access express driver by setting the sign on information as 'use windows user name and password, no promping'.

If this problem is related to the security of sql server, why it can be run in DTS Designer(which has no pop up message box for user interface) with no popup error message and the result of transformation is correct.|||Because dts is a client application.
When you run it from query analyser the client is the server which gives it a different environment.

If you have already run it from the sql server service account then that limits the things that can be wrong.
Try turning on logging to see if any steps are run and what the error is.|||why i can run DTS Package in query analyzer if i change the source of DTS to be MsAccess file, on the server m/c not client. run in command line, no problem also.

why i cannot run in query analyzer if i specified source DSN as AS400/DB2, but run properly in command line by using same script.

Cannot restore database

RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.
Seems like someone has a connection to the database. Make sure that the connection from where you do the
restore isn't in the database. Check who is in the database using sp_who or sp_who2.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
> RESTORE DATABASE upperbridge
> FROM DISK = C:\...........
> WITH REPLACE
> gets the error
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be
> obtained because the database is in use.
> This is on my test SQL Server 2000 on my intranet with no other users. Both
> Enterprise Manager and Query Analyser are not running.
> Please advise.
>
>
|||Run sp_who or sp_who2 and see who is connected to it.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.
|||Are you sure that the QA session you're using to restore the database isn't
the one that's using the database? Use sp_who2 to see who's using it.
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.
|||Hi Roger,
Execute the below script from master to restore the database (Execute all
the lines in a together from query analyzer - Replace dbname , filename ,
directory and
logical name based on ur requirement)
use master
go
alter database <dbname> set single_user with rollback immediate
go
restore database <dbname> from disk = 'C:\filename.bak' with recovery,
move 'logical_mdf_name' to 'c:\mssql\data\filename.mdf',
move 'logical_ldf_name' to 'c:\mssql\data\filename.ldf'
Thanks
Hari
MCDBA
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
> RESTORE DATABASE upperbridge
> FROM DISK = C:\...........
> WITH REPLACE
> gets the error
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not
be
> obtained because the database is in use.
> This is on my test SQL Server 2000 on my intranet with no other users.
Both
> Enterprise Manager and Query Analyser are not running.
> Please advise.
>
>

Cannot restore database

RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.Run sp_who or sp_who2 and see who is connected to it.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.|||Seems like someone has a connection to the database. Make sure that the connection from where you do the
restore isn't in the database. Check who is in the database using sp_who or sp_who2.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
> RESTORE DATABASE upperbridge
> FROM DISK = C:\...........
> WITH REPLACE
> gets the error
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be
> obtained because the database is in use.
> This is on my test SQL Server 2000 on my intranet with no other users. Both
> Enterprise Manager and Query Analyser are not running.
> Please advise.
>
>|||Are you sure that the QA session you're using to restore the database isn't
the one that's using the database? Use sp_who2 to see who's using it.
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.|||Hi Roger,
Execute the below script from master to restore the database (Execute all
the lines in a together from query analyzer - Replace dbname , filename ,
directory and
logical name based on ur requirement)
use master
go
alter database <dbname> set single_user with rollback immediate
go
restore database <dbname> from disk = 'C:\filename.bak' with recovery,
move 'logical_mdf_name' to 'c:\mssql\data\filename.mdf',
move 'logical_ldf_name' to 'c:\mssql\data\filename.ldf'
Thanks
Hari
MCDBA
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
> RESTORE DATABASE upperbridge
> FROM DISK = C:\...........
> WITH REPLACE
> gets the error
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not
be
> obtained because the database is in use.
> This is on my test SQL Server 2000 on my intranet with no other users.
Both
> Enterprise Manager and Query Analyser are not running.
> Please advise.
>
>

Cannot restore database

RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access
could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.Seems like someone has a connection to the database. Make sure that the conn
ection from where you do the
restore isn't in the database. Check who is in the database using sp_who or
sp_who2.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
> RESTORE DATABASE upperbridge
> FROM DISK = C:\...........
> WITH REPLACE
> gets the error
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive acces
s could not be
> obtained because the database is in use.
> This is on my test SQL Server 2000 on my intranet with no other users. Bo
th
> Enterprise Manager and Query Analyser are not running.
> Please advise.
>
>|||Run sp_who or sp_who2 and see who is connected to it.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access
could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.|||Are you sure that the QA session you're using to restore the database isn't
the one that's using the database? Use sp_who2 to see who's using it.
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access
could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.|||Hi Roger,
Execute the below script from master to restore the database (Execute all
the lines in a together from query analyzer - Replace dbname , filename ,
directory and
logical name based on ur requirement)
use master
go
alter database <dbname> set single_user with rollback immediate
go
restore database <dbname> from disk = 'C:\filename.bak' with recovery,
move 'logical_mdf_name' to 'c:\mssql\data\filename.mdf',
move 'logical_ldf_name' to 'c:\mssql\data\filename.ldf'
Thanks
Hari
MCDBA
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
> RESTORE DATABASE upperbridge
> FROM DISK = C:\...........
> WITH REPLACE
> gets the error
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could
not
be
> obtained because the database is in use.
> This is on my test SQL Server 2000 on my intranet with no other users.
Both
> Enterprise Manager and Query Analyser are not running.
> Please advise.
>
>