One of our critical databases that is part of a log shipping-like set up is
corrupt. This happened while doing a full DEb restore. DB was showing status
'Loading/Suspect' for about 5 hours. However, restore job has failed, and
nothing is attempting to restore it. I am unable to get get any information
of the DB via Query Analyzer or Enterprise Manager.
I need to drop this DB and recreate it, but I am unable to do any
drop/delete operation on this DB as well.
About an hour ago, I stoped the SQL Server, and deleted the .mdf and .ldf
files from the physical location. When I start the SQL Server back up, the DB
is still visible, and I am not able to do anything with it.
When I try to drop the database I get following error.
"Cannot drop database 'Tara' because it is being used for replication." We
don't have any replicaiton set up going on at all.
IF I do sp_helpDB it gives me following message
No permission to access database 'Tara'. I am logged on as sa.
Anyone knows what is going on hear?
Thank you
BlueFish
use master
go
exec sp_removedbreplication 'Tara'
go
drop database 'Tara'
Geoff N. Hiten
Microsoft SQL Server MVP
"bluefish" <bluefish@.discussions.microsoft.com> wrote in message
news:CB38AC55-B701-4CA0-88A1-3A2706D8933B@.microsoft.com...
> One of our critical databases that is part of a log shipping-like set up
> is
> corrupt. This happened while doing a full DEb restore. DB was showing
> status
> 'Loading/Suspect' for about 5 hours. However, restore job has failed,
> and
> nothing is attempting to restore it. I am unable to get get any
> information
> of the DB via Query Analyzer or Enterprise Manager.
> I need to drop this DB and recreate it, but I am unable to do any
> drop/delete operation on this DB as well.
> About an hour ago, I stoped the SQL Server, and deleted the .mdf and .ldf
> files from the physical location. When I start the SQL Server back up, the
> DB
> is still visible, and I am not able to do anything with it.
> When I try to drop the database I get following error.
> "Cannot drop database 'Tara' because it is being used for replication." We
> don't have any replicaiton set up going on at all.
>
> IF I do sp_helpDB it gives me following message
> No permission to access database 'Tara'. I am logged on as sa.
>
> Anyone knows what is going on hear?
> Thank you
> BlueFish
>
>
sql
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Cannot deploy
Hello, I attempted to deploy my reports to a remote web server where RS
is installed. The two RS databases exist, as do the two virtuals.
Anyone seen the error below or have any procedure for diagnosing it?
Thanks,
-KJ
************************** error follows ***************
TITLE: Microsoft Report Designer
--
A connection could not be made to the report server
http://10.12.48.97/ReportServer.
--
ADDITIONAL INFORMATION:
Client found response content type of 'text/html; charset=utf-8', but
expected 'text/xml'.
The request failed with the error message:
--
<html>
<head>
<title>
SQL Server Reporting Services
</title><meta name="Generator" content="Microsoft SQL Server
Reporting Services 9.00.2047.00" />
<meta name="HTTP Status" content="500" />
<meta name="ProductLocaleID" content="9" />
<meta name="CountryLocaleID" content="1033" />
<style>
BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt;
COLOR:black}
H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt}
LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt;
DISPLAY:inline}
.ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt;
COLOR:gray}
A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC;
TEXT-DECORATION:none}
A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#FF3300;
TEXT-DECORATION:underline}
A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC;
TEXT-DECORATION:none}
A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; color:#FF3300;
TEXT-DECORATION:underline}
</style>
</head><body bgcolor="white">
<h1>
Reporting Services Error<hr width="100%" size="1" color="silver" />
</h1><ul>
<li>The report server has encountered a configuration error. See the
report server log files for more information.
(rsServerConfigurationError) </li><ul>
<li>For more information about this error navigate to the report
server on the local server machine, or enable remote errors</li>
</ul><hr width="100%" size="1" color="silver" /><span
class="ProductInfo">SQL Server Reporting Services</span>
</ul>
</body>
--. (Microsoft.ReportingServices.Designer)
--
BUTTONS:
OK
--Cancel this request; after much ado, I got it up and running.
n_o_s_p_a__m@.mail.com wrote:
> Hello, I attempted to deploy my reports to a remote web server where RS
> is installed. The two RS databases exist, as do the two virtuals.
> Anyone seen the error below or have any procedure for diagnosing it?
> Thanks,
> -KJ
> ************************** error follows ***************
> TITLE: Microsoft Report Designer
> --
> A connection could not be made to the report server
> http://10.12.48.97/ReportServer.
> --
> ADDITIONAL INFORMATION:
> Client found response content type of 'text/html; charset=utf-8', but
> expected 'text/xml'.
> The request failed with the error message:
> --
> <html>
> <head>
> <title>
> SQL Server Reporting Services
> </title><meta name="Generator" content="Microsoft SQL Server
> Reporting Services 9.00.2047.00" />
> <meta name="HTTP Status" content="500" />
> <meta name="ProductLocaleID" content="9" />
> <meta name="CountryLocaleID" content="1033" />
> <style>
> BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt;
> COLOR:black}
> H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt}
> LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt;
> DISPLAY:inline}
> .ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt;
> COLOR:gray}
> A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC;
> TEXT-DECORATION:none}
> A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#FF3300;
> TEXT-DECORATION:underline}
> A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC;
> TEXT-DECORATION:none}
> A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; color:#FF3300;
> TEXT-DECORATION:underline}
> </style>
> </head><body bgcolor="white">
> <h1>
> Reporting Services Error<hr width="100%" size="1" color="silver" />
> </h1><ul>
> <li>The report server has encountered a configuration error. See the
> report server log files for more information.
> (rsServerConfigurationError) </li><ul>
> <li>For more information about this error navigate to the report
> server on the local server machine, or enable remote errors</li>
> </ul><hr width="100%" size="1" color="silver" /><span
> class="ProductInfo">SQL Server Reporting Services</span>
> </ul>
> </body>
> --. (Microsoft.ReportingServices.Designer)
> --
> BUTTONS:
> OK
> --
is installed. The two RS databases exist, as do the two virtuals.
Anyone seen the error below or have any procedure for diagnosing it?
Thanks,
-KJ
************************** error follows ***************
TITLE: Microsoft Report Designer
--
A connection could not be made to the report server
http://10.12.48.97/ReportServer.
--
ADDITIONAL INFORMATION:
Client found response content type of 'text/html; charset=utf-8', but
expected 'text/xml'.
The request failed with the error message:
--
<html>
<head>
<title>
SQL Server Reporting Services
</title><meta name="Generator" content="Microsoft SQL Server
Reporting Services 9.00.2047.00" />
<meta name="HTTP Status" content="500" />
<meta name="ProductLocaleID" content="9" />
<meta name="CountryLocaleID" content="1033" />
<style>
BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt;
COLOR:black}
H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt}
LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt;
DISPLAY:inline}
.ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt;
COLOR:gray}
A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC;
TEXT-DECORATION:none}
A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#FF3300;
TEXT-DECORATION:underline}
A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC;
TEXT-DECORATION:none}
A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; color:#FF3300;
TEXT-DECORATION:underline}
</style>
</head><body bgcolor="white">
<h1>
Reporting Services Error<hr width="100%" size="1" color="silver" />
</h1><ul>
<li>The report server has encountered a configuration error. See the
report server log files for more information.
(rsServerConfigurationError) </li><ul>
<li>For more information about this error navigate to the report
server on the local server machine, or enable remote errors</li>
</ul><hr width="100%" size="1" color="silver" /><span
class="ProductInfo">SQL Server Reporting Services</span>
</ul>
</body>
--. (Microsoft.ReportingServices.Designer)
--
BUTTONS:
OK
--Cancel this request; after much ado, I got it up and running.
n_o_s_p_a__m@.mail.com wrote:
> Hello, I attempted to deploy my reports to a remote web server where RS
> is installed. The two RS databases exist, as do the two virtuals.
> Anyone seen the error below or have any procedure for diagnosing it?
> Thanks,
> -KJ
> ************************** error follows ***************
> TITLE: Microsoft Report Designer
> --
> A connection could not be made to the report server
> http://10.12.48.97/ReportServer.
> --
> ADDITIONAL INFORMATION:
> Client found response content type of 'text/html; charset=utf-8', but
> expected 'text/xml'.
> The request failed with the error message:
> --
> <html>
> <head>
> <title>
> SQL Server Reporting Services
> </title><meta name="Generator" content="Microsoft SQL Server
> Reporting Services 9.00.2047.00" />
> <meta name="HTTP Status" content="500" />
> <meta name="ProductLocaleID" content="9" />
> <meta name="CountryLocaleID" content="1033" />
> <style>
> BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt;
> COLOR:black}
> H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt}
> LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt;
> DISPLAY:inline}
> .ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt;
> COLOR:gray}
> A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC;
> TEXT-DECORATION:none}
> A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#FF3300;
> TEXT-DECORATION:underline}
> A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC;
> TEXT-DECORATION:none}
> A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; color:#FF3300;
> TEXT-DECORATION:underline}
> </style>
> </head><body bgcolor="white">
> <h1>
> Reporting Services Error<hr width="100%" size="1" color="silver" />
> </h1><ul>
> <li>The report server has encountered a configuration error. See the
> report server log files for more information.
> (rsServerConfigurationError) </li><ul>
> <li>For more information about this error navigate to the report
> server on the local server machine, or enable remote errors</li>
> </ul><hr width="100%" size="1" color="silver" /><span
> class="ProductInfo">SQL Server Reporting Services</span>
> </ul>
> </body>
> --. (Microsoft.ReportingServices.Designer)
> --
> BUTTONS:
> OK
> --
Monday, March 19, 2012
Cannot create new database
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
Thanx
SeanDL wrote:
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following
> error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the
> operation later.
> Fair enough , but i have triple checked there are no connections or
> locks on model database.
> Please help ?
> Thanx
Run "sp_helpdb model" and make sure the status is reporting the correct
information.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Could you run the following query and see if you get any rows? You should
get no rows returned, if nobody is accessing the model database:
SELECT spid
FROM master..sysprocesses
WHERE dbid = DB_ID('model')
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
Thanx
|||Hi,
If Vyas query returns '0' records and if you still have issues then try
restarting the SQL Server service and try creating
the database. This is really some thing new if there is no connection to
Model database.
Do post the reply once the problem is solved.
Thanks
Hari
SQL Server MVP
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the operation
> later.
> Fair enough , but i have triple checked there are no connections or locks
> on
> model database.
> Please help ?
> Thanx
|||Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx
|||Yes the status is ok.
Nothing in sysprocesses or syslocks for model.
Can one detach the model database (as there are no processes on it) and
re-attach a mdel database from another server (as they are identical) while
sql server is running. It is a system database so i have a feeling it can
only be done in single user mode ? Or at least i know this is the case for
master database, not sure about model as it is not used unless a new db is
created ?
|||Please ignore the question on model database , as i have found out it can
only be done done with trace flag 3608 and sql must be restarted.
Thanx
|||If it is production and critical, then you might want to open a case with
Microsoft PSS.
As a temporary measure, could you create a new database on another server,
back it up and restore it onto the production server?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:51A651AC-BE30-42F8-B8BA-13F3F0D5E43D@.microsoft.com...
Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try
to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx
|||Marayana, this what i ended up doing.
Made the database on another server, detached and re-attached on the problem
server.
Thanx
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
Thanx
SeanDL wrote:
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following
> error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the
> operation later.
> Fair enough , but i have triple checked there are no connections or
> locks on model database.
> Please help ?
> Thanx
Run "sp_helpdb model" and make sure the status is reporting the correct
information.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Could you run the following query and see if you get any rows? You should
get no rows returned, if nobody is accessing the model database:
SELECT spid
FROM master..sysprocesses
WHERE dbid = DB_ID('model')
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
Thanx
|||Hi,
If Vyas query returns '0' records and if you still have issues then try
restarting the SQL Server service and try creating
the database. This is really some thing new if there is no connection to
Model database.
Do post the reply once the problem is solved.
Thanks
Hari
SQL Server MVP
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the operation
> later.
> Fair enough , but i have triple checked there are no connections or locks
> on
> model database.
> Please help ?
> Thanx
|||Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx
|||Yes the status is ok.
Nothing in sysprocesses or syslocks for model.
Can one detach the model database (as there are no processes on it) and
re-attach a mdel database from another server (as they are identical) while
sql server is running. It is a system database so i have a feeling it can
only be done in single user mode ? Or at least i know this is the case for
master database, not sure about model as it is not used unless a new db is
created ?
|||Please ignore the question on model database , as i have found out it can
only be done done with trace flag 3608 and sql must be restarted.
Thanx
|||If it is production and critical, then you might want to open a case with
Microsoft PSS.
As a temporary measure, could you create a new database on another server,
back it up and restore it onto the production server?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:51A651AC-BE30-42F8-B8BA-13F3F0D5E43D@.microsoft.com...
Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try
to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx
|||Marayana, this what i ended up doing.
Made the database on another server, detached and re-attached on the problem
server.
Thanx
Cannot create new database
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
ThanxSeanDL wrote:
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following
> error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the
> operation later.
> Fair enough , but i have triple checked there are no connections or
> locks on model database.
> Please help ?
> Thanx
Run "sp_helpdb model" and make sure the status is reporting the correct
information.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Could you run the following query and see if you get any rows? You should
get no rows returned, if nobody is accessing the model database:
SELECT spid
FROM master..sysprocesses
WHERE dbid = DB_ID('model')
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
Thanx|||Hi,
If Vyas query returns '0' records and if you still have issues then try
restarting the SQL Server service and try creating
the database. This is really some thing new if there is no connection to
Model database.
Do post the reply once the problem is solved.
Thanks
Hari
SQL Server MVP
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the operation
> later.
> Fair enough , but i have triple checked there are no connections or locks
> on
> model database.
> Please help ?
> Thanx|||Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try t
o
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx|||Yes the status is ok.
Nothing in sysprocesses or syslocks for model.
Can one detach the model database (as there are no processes on it) and
re-attach a mdel database from another server (as they are identical) while
sql server is running. It is a system database so i have a feeling it can
only be done in single user mode ? Or at least i know this is the case for
master database, not sure about model as it is not used unless a new db is
created ?|||Please ignore the question on model database , as i have found out it can
only be done done with trace flag 3608 and sql must be restarted.
Thanx|||If it is production and critical, then you might want to open a case with
Microsoft PSS.
As a temporary measure, could you create a new database on another server,
back it up and restore it onto the production server?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:51A651AC-BE30-42F8-B8BA-13F3F0D5E43D@.microsoft.com...
Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try
to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx|||Marayana, this what i ended up doing.
Made the database on another server, detached and re-attached on the problem
server.
Thanx
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
ThanxSeanDL wrote:
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following
> error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the
> operation later.
> Fair enough , but i have triple checked there are no connections or
> locks on model database.
> Please help ?
> Thanx
Run "sp_helpdb model" and make sure the status is reporting the correct
information.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Could you run the following query and see if you get any rows? You should
get no rows returned, if nobody is accessing the model database:
SELECT spid
FROM master..sysprocesses
WHERE dbid = DB_ID('model')
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
Thanx|||Hi,
If Vyas query returns '0' records and if you still have issues then try
restarting the SQL Server service and try creating
the database. This is really some thing new if there is no connection to
Model database.
Do post the reply once the problem is solved.
Thanks
Hari
SQL Server MVP
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the operation
> later.
> Fair enough , but i have triple checked there are no connections or locks
> on
> model database.
> Please help ?
> Thanx|||Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try t
o
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx|||Yes the status is ok.
Nothing in sysprocesses or syslocks for model.
Can one detach the model database (as there are no processes on it) and
re-attach a mdel database from another server (as they are identical) while
sql server is running. It is a system database so i have a feeling it can
only be done in single user mode ? Or at least i know this is the case for
master database, not sure about model as it is not used unless a new db is
created ?|||Please ignore the question on model database , as i have found out it can
only be done done with trace flag 3608 and sql must be restarted.
Thanx|||If it is production and critical, then you might want to open a case with
Microsoft PSS.
As a temporary measure, could you create a new database on another server,
back it up and restore it onto the production server?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:51A651AC-BE30-42F8-B8BA-13F3F0D5E43D@.microsoft.com...
Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try
to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx|||Marayana, this what i ended up doing.
Made the database on another server, detached and re-attached on the problem
server.
Thanx
Cannot create new database
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
ThanxSeanDL wrote:
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following
> error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the
> operation later.
> Fair enough , but i have triple checked there are no connections or
> locks on model database.
> Please help ?
> Thanx
Run "sp_helpdb model" and make sure the status is reporting the correct
information.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Could you run the following query and see if you get any rows? You should
get no rows returned, if nobody is accessing the model database:
SELECT spid
FROM master..sysprocesses
WHERE dbid = DB_ID('model')
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
Thanx|||Hi,
If Vyas query returns '0' records and if you still have issues then try
restarting the SQL Server service and try creating
the database. This is really some thing new if there is no connection to
Model database.
Do post the reply once the problem is solved.
Thanks
Hari
SQL Server MVP
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the operation
> later.
> Fair enough , but i have triple checked there are no connections or locks
> on
> model database.
> Please help ?
> Thanx|||Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx|||Yes the status is ok.
Nothing in sysprocesses or syslocks for model.
Can one detach the model database (as there are no processes on it) and
re-attach a mdel database from another server (as they are identical) while
sql server is running. It is a system database so i have a feeling it can
only be done in single user mode ? Or at least i know this is the case for
master database, not sure about model as it is not used unless a new db is
created ?|||Please ignore the question on model database , as i have found out it can
only be done done with trace flag 3608 and sql must be restarted.
Thanx|||If it is production and critical, then you might want to open a case with
Microsoft PSS.
As a temporary measure, could you create a new database on another server,
back it up and restore it onto the production server?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:51A651AC-BE30-42F8-B8BA-13F3F0D5E43D@.microsoft.com...
Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try
to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx|||Marayana, this what i ended up doing.
Made the database on another server, detached and re-attached on the problem
server.
Thanx
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
ThanxSeanDL wrote:
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following
> error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the
> operation later.
> Fair enough , but i have triple checked there are no connections or
> locks on model database.
> Please help ?
> Thanx
Run "sp_helpdb model" and make sure the status is reporting the correct
information.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Could you run the following query and see if you get any rows? You should
get no rows returned, if nobody is accessing the model database:
SELECT spid
FROM master..sysprocesses
WHERE dbid = DB_ID('model')
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
Hi There
Ok weird one.
Cannot create any new databases on Sql Server, i get the following error.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'MODEL'. Retry the operation
later.
Fair enough , but i have triple checked there are no connections or locks on
model database.
Please help ?
Thanx|||Hi,
If Vyas query returns '0' records and if you still have issues then try
restarting the SQL Server service and try creating
the database. This is really some thing new if there is no connection to
Model database.
Do post the reply once the problem is solved.
Thanks
Hari
SQL Server MVP
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:CF746EEF-DD80-417F-AB55-3B2E2FC751C0@.microsoft.com...
> Hi There
> Ok weird one.
> Cannot create any new databases on Sql Server, i get the following error.
> Msg 1807, Level 16, State 3, Line 1
> Could not obtain exclusive lock on database 'MODEL'. Retry the operation
> later.
> Fair enough , but i have triple checked there are no connections or locks
> on
> model database.
> Please help ?
> Thanx|||Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx|||Yes the status is ok.
Nothing in sysprocesses or syslocks for model.
Can one detach the model database (as there are no processes on it) and
re-attach a mdel database from another server (as they are identical) while
sql server is running. It is a system database so i have a feeling it can
only be done in single user mode ? Or at least i know this is the case for
master database, not sure about model as it is not used unless a new db is
created ?|||Please ignore the question on model database , as i have found out it can
only be done done with trace flag 3608 and sql must be restarted.
Thanx|||If it is production and critical, then you might want to open a case with
Microsoft PSS.
As a temporary measure, could you create a new database on another server,
back it up and restore it onto the production server?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"SeanDL" <SeanDL@.discussions.microsoft.com> wrote in message
news:51A651AC-BE30-42F8-B8BA-13F3F0D5E43D@.microsoft.com...
Yes i have checked all of this, status of model is online and ok.
Nothing in sysprocesses and syslocks for model.
I am running it as sysadmin, cannot create database from any database that
is it does not matter if i am in master, msdb, userdatabase etc when i try
to
create it.
This is a production database, i dont want to shut it down unless i know it
will fix the problem, server was rebooted Friday anyway.
Rather urgent please help?
Thanx|||Marayana, this what i ended up doing.
Made the database on another server, detached and re-attached on the problem
server.
Thanx
Sunday, March 11, 2012
Cannot create databases (and other issues) from Management Studio
I seem to be having a number of problems using managment studio (MS) against my locally installed SQL 2005 Standard Edition. E.g. when I create a default database using MS it says...
TITLE: Microsoft SQL Server Management Studio
----------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
----------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
----------
The server could not load DCOM. (Microsoft SQL Server, Error: 7404)
For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=7404&LinkId=20476
However, the hyperlinks are less than helpful. I also get problems doing various maintance work from MS. I don't think its a database engine problem 'cause I can do all the work via TSQL DDL but the interface is so much easier to use (or should be). BTW it's running on XP Pro (firewall problem?).
From the Event Log...What does it mean?
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17102
Date: 06/02/2006
Time: 20:12:06
User: N/A
Computer: TITAN
Description:
Failed to initialize Distributed COM (CoInitializeEx returned 80010119). Heterogeneous queries and remote procedure calls are disabled. Check the DCOM configuration using Component Services in Control Panel.
|||Finally discovered it was the nVidia (motherboard) firewall client, you have to uninstall it - I had a similar problem with IE7
Cannot create ADO connection to DB anymore.
This is a semi-crosspost - tried it on comp.databases.ms-sqlserver, but haven't
made any progress. Meanwhile, I'm getting desperate...Client wants some
changes to an app and I can't get the thing running.
I guess my primary quesion has become: "How do I confirm that
Provider=SQLOLEDB.1 us installed and functioning on my system?"
--
I rebuilt my machine a few weeks back - re-installed the developer version...
The app in question can see all it's tables in the SQL back end via ODBC and
everything looks normal via Enterprise Manager, but I can no longer create
an ADO Connection.
Source code is unchanged, what's changed is the PC rebuild and consequent
re-installatin of SQL Server developer version.
I suspect it's something to do with the provider (at least that's the only thing
I can see in the .Connect string that's not verifiably correct...)
The error looks like this after I trap and format it:
---
12/30/03 21:34:30v0.71 Userid: UPQC on SAG
Proc: basADO: ADO_ConnectionCreate
-2147467259: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
not exist or access denied.
Errors encountered when trying to connect:
'''[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied.
--Connect String:
Provider=SQLOLEDB.1;SERVER=SAG;DATABASE=TRETS;UID=Trets;PWD=trets
---
Can anybody see anything obvious to look for?
I'm thinking I need to know how to verify/validate the presence of provider
"SQLOLEDB.1"... But how?
--
PeteCresswell
--
PeteCresswellPete,
Yes, it looks like SQLOLEDB version 1 is no longer installed on that box.
Is there a particular reason why you need version 1?
If not, use what's known as the "VersionIndependentProgID",
simply: SQLOLEDB and this will use the latest version installed on the
machine.
James Hokes
"(Pete Cresswell)" <x@.y.z> wrote in message
news:dum6vv4e67q257m22kemdttda3h7pjmub2@.4ax.com...
> This is a semi-crosspost - tried it on comp.databases.ms-sqlserver, but
haven't
> made any progress. Meanwhile, I'm getting desperate...Client wants some
> changes to an app and I can't get the thing running.
> I guess my primary quesion has become: "How do I confirm that
> Provider=SQLOLEDB.1 us installed and functioning on my system?"
>
> --
> I rebuilt my machine a few weeks back - re-installed the developer
version...
> The app in question can see all it's tables in the SQL back end via ODBC
and
> everything looks normal via Enterprise Manager, but I can no longer create
> an ADO Connection.
> Source code is unchanged, what's changed is the PC rebuild and consequent
> re-installatin of SQL Server developer version.
> I suspect it's something to do with the provider (at least that's the only
thing
> I can see in the .Connect string that's not verifiably correct...)
> The error looks like this after I trap and format it:
> ---
> 12/30/03 21:34:30v0.71 Userid: UPQC on SAG
> Proc: basADO: ADO_ConnectionCreate
> -2147467259: [DBNETLIB][ConnectionOpen (Connect()).]SQL
Server does
> not exist or access denied.
> Errors encountered when trying to connect:
> '''[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist
or
> access denied.
> --Connect String:
> Provider=SQLOLEDB.1;SERVER=SAG;DATABASE=TRETS;UID=Trets;PWD=trets
> ---
>
> Can anybody see anything obvious to look for?
> I'm thinking I need to know how to verify/validate the presence of
provider
> "SQLOLEDB.1"... But how?
> --
> PeteCresswell
> --
> PeteCresswell|||RE/
>Yes, it looks like SQLOLEDB version 1 is no longer installed on that box.
>Is there a particular reason why you need version 1?
>If not, use what's known as the "VersionIndependentProgID",
>simply: SQLOLEDB and this will use the latest version installed on the
>machine.
Excellant suggestin in it's own right - I've been doing a lot of this stuff by
rote and didn't think about versioning...
Removed the version, same error - which suggests to me that there's no provider
of that name on the box.
Any idea how to install one? I'm guessing it's a download from the MS Web site
or my MSDN DVD set...but am a little leery of just "try this...try that..." for
fear of hosing the installation completely.
--
PeteCresswell|||Pete,
You might want to download microsoft's MDAC Checker. It does sound like
something's hosed - most likely just need to install the latest MDAC. That
will tell you if you have any mis-matched or missing files.
Here's a URL that tells what to do:
http://support.microsoft.com/default.aspx?kbid=301202&product=mdac
James Hokes
"(Pete Cresswell)" <x@.y.z> wrote in message
news:sl79vvo56du1nhspao0uoasmqdohfvcjna@.4ax.com...
> RE/
> >Yes, it looks like SQLOLEDB version 1 is no longer installed on that box.
> >
> >Is there a particular reason why you need version 1?
> >If not, use what's known as the "VersionIndependentProgID",
> >simply: SQLOLEDB and this will use the latest version installed on the
> >machine.
> Excellant suggestin in it's own right - I've been doing a lot of this
stuff by
> rote and didn't think about versioning...
> Removed the version, same error - which suggests to me that there's no
provider
> of that name on the box.
> Any idea how to install one? I'm guessing it's a download from the MS Web
site
> or my MSDN DVD set...but am a little leery of just "try this...try
that..." for
> fear of hosing the installation completely.
> --
> PeteCresswell|||(Pete Cresswell) (x@.y.z) writes:
> The app in question can see all it's tables in the SQL back end via ODBC
> and everything looks normal via Enterprise Manager, but I can no longer
> create an ADO Connection.
> Source code is unchanged, what's changed is the PC rebuild and consequent
> re-installatin of SQL Server developer version.
> I suspect it's something to do with the provider (at least that's the
> only thing I can see in the .Connect string that's not verifiably
> correct...)
> The error looks like this after I trap and format it:
> ---
> 12/30/03 21:34:30v0.71 Userid: UPQC on SAG
> Proc: basADO: ADO_ConnectionCreate
> -2147467259: [DBNETLIB][ConnectionOpen (Connect()).]SQL
> Server does not exist or access denied.
> Errors encountered when trying to connect:
> '''[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist
> or access denied.
> --Connect String:
> Provider=SQLOLEDB.1;SERVER=SAG;DATABASE=TRETS;UID=Trets;PWD=trets
> ---
The idea that SQLOLEDB should be missing seems to be an incorrect path
to me. In that case you would get a different message. To see what,
try Provider=NISSE.
No, the problem is that the SQL Server SAG cannot be be found. What happens
if you try connect to SAG from Query Analyzer or OSQL?
You may have to add SAG as an alias in the Client Network Utility.
Another possibility is that you have not enabled the network library
that SAG is listening to. Again, this can be checked in the Client
Network Utility. And in the Server Netowrk Utility on SAG, if you have
access to that machine.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||RE/
>The idea that SQLOLEDB should be missing seems to be an incorrect path
>to me. In that case you would get a different message. To see what,
>try Provider=NISSE.
" Error# 3706: Provider cannot be found. It may not be properly installed."
Sounds like you're correct.
>No, the problem is that the SQL Server SAG cannot be be found. What happens
>if you try connect to SAG from Query Analyzer or OSQL?
Dunno what OSQL is, but no problems at all using Query Analyser to do a SELECT *
FROM tlkpVendor... or using Enterprise Manager to open the same table,
returning all records
>You may have to add SAG as an alias in the Client Network Utility.
*Seems* to already be there.
>Another possibility is that you have not enabled the network library
>that SAG is listening to. Again, this can be checked in the Client
>Network Utility. And in the Server Netowrk Utility on SAG, if you have
>access to that machine.
Could you elaborate on that one - seems like the last possibility.
The only enabled protocols I've got are Named Pipes and TCP/IP - but that's what
I'd expect. Both utilities you've mentioned show these two protocols.
--
PeteCresswell|||(Pete Cresswell) (x@.y.z) writes:
> Dunno what OSQL is, but no problems at all using Query Analyser to do a
> SELECT * FROM tlkpVendor... or using Enterprise Manager to open the
> same table, returning all records
Don't know what OSQL is? Hey, Pete, how long have you been working with
SQL Server? It must be over year by now, isn't it?
Sorry, couldn't resist. OSQL is a command-line utility for queries, a more
primitive Query Analyzer if you like. Mainly useful if you want run
prepared SQL scripts from batch files.
Anyway, it is mysterious that you can access the server from the tools,
but not from the application code. This means that we can forget about
the network protocols that I discussed in my previous posting.
Connectivity problems are not my best game, so I'm fairly clueless
myself. The one difference with regards to the tools is that the
tools connect through ODBC and not OLE DB. But I cannot see that it
should matter. You could try to replace "Provider=SQLOLEDB" with
"Driver={SQL Server}", this will change provider to MSDASQL, OLE DB
over ODBC. But this is a poorer alternative, so we don't want to use
that.
Hm, you could also try changing SERVER= into Data Source=, which I
believe is the proper name for OLE DB.
No fantastic tips - I'm just clutching at straws.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||RE/
>No fantastic tips - I'm just clutching at straws.
Thanks for trying - I need anything I can get.
Tried the various Provider/Driver combinations, still no luck.
Changed my connect string to use the SA id/pw and no change.
I'm starting to grasp at my own straw: ADO...maybe there's something amiss
with my ADO. If ODBC works and Enterprise Manager can connect, what's left?
--
PeteCresswell|||RE/
>straws
Oh yeah...I re-installed the MDAC. Put 2.8 over top of 2.6.
Everything else still works: ODBC, Enterprise Manager...
--
PeteCresswell
made any progress. Meanwhile, I'm getting desperate...Client wants some
changes to an app and I can't get the thing running.
I guess my primary quesion has become: "How do I confirm that
Provider=SQLOLEDB.1 us installed and functioning on my system?"
--
I rebuilt my machine a few weeks back - re-installed the developer version...
The app in question can see all it's tables in the SQL back end via ODBC and
everything looks normal via Enterprise Manager, but I can no longer create
an ADO Connection.
Source code is unchanged, what's changed is the PC rebuild and consequent
re-installatin of SQL Server developer version.
I suspect it's something to do with the provider (at least that's the only thing
I can see in the .Connect string that's not verifiably correct...)
The error looks like this after I trap and format it:
---
12/30/03 21:34:30v0.71 Userid: UPQC on SAG
Proc: basADO: ADO_ConnectionCreate
-2147467259: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
not exist or access denied.
Errors encountered when trying to connect:
'''[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied.
--Connect String:
Provider=SQLOLEDB.1;SERVER=SAG;DATABASE=TRETS;UID=Trets;PWD=trets
---
Can anybody see anything obvious to look for?
I'm thinking I need to know how to verify/validate the presence of provider
"SQLOLEDB.1"... But how?
--
PeteCresswell
--
PeteCresswellPete,
Yes, it looks like SQLOLEDB version 1 is no longer installed on that box.
Is there a particular reason why you need version 1?
If not, use what's known as the "VersionIndependentProgID",
simply: SQLOLEDB and this will use the latest version installed on the
machine.
James Hokes
"(Pete Cresswell)" <x@.y.z> wrote in message
news:dum6vv4e67q257m22kemdttda3h7pjmub2@.4ax.com...
> This is a semi-crosspost - tried it on comp.databases.ms-sqlserver, but
haven't
> made any progress. Meanwhile, I'm getting desperate...Client wants some
> changes to an app and I can't get the thing running.
> I guess my primary quesion has become: "How do I confirm that
> Provider=SQLOLEDB.1 us installed and functioning on my system?"
>
> --
> I rebuilt my machine a few weeks back - re-installed the developer
version...
> The app in question can see all it's tables in the SQL back end via ODBC
and
> everything looks normal via Enterprise Manager, but I can no longer create
> an ADO Connection.
> Source code is unchanged, what's changed is the PC rebuild and consequent
> re-installatin of SQL Server developer version.
> I suspect it's something to do with the provider (at least that's the only
thing
> I can see in the .Connect string that's not verifiably correct...)
> The error looks like this after I trap and format it:
> ---
> 12/30/03 21:34:30v0.71 Userid: UPQC on SAG
> Proc: basADO: ADO_ConnectionCreate
> -2147467259: [DBNETLIB][ConnectionOpen (Connect()).]SQL
Server does
> not exist or access denied.
> Errors encountered when trying to connect:
> '''[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist
or
> access denied.
> --Connect String:
> Provider=SQLOLEDB.1;SERVER=SAG;DATABASE=TRETS;UID=Trets;PWD=trets
> ---
>
> Can anybody see anything obvious to look for?
> I'm thinking I need to know how to verify/validate the presence of
provider
> "SQLOLEDB.1"... But how?
> --
> PeteCresswell
> --
> PeteCresswell|||RE/
>Yes, it looks like SQLOLEDB version 1 is no longer installed on that box.
>Is there a particular reason why you need version 1?
>If not, use what's known as the "VersionIndependentProgID",
>simply: SQLOLEDB and this will use the latest version installed on the
>machine.
Excellant suggestin in it's own right - I've been doing a lot of this stuff by
rote and didn't think about versioning...
Removed the version, same error - which suggests to me that there's no provider
of that name on the box.
Any idea how to install one? I'm guessing it's a download from the MS Web site
or my MSDN DVD set...but am a little leery of just "try this...try that..." for
fear of hosing the installation completely.
--
PeteCresswell|||Pete,
You might want to download microsoft's MDAC Checker. It does sound like
something's hosed - most likely just need to install the latest MDAC. That
will tell you if you have any mis-matched or missing files.
Here's a URL that tells what to do:
http://support.microsoft.com/default.aspx?kbid=301202&product=mdac
James Hokes
"(Pete Cresswell)" <x@.y.z> wrote in message
news:sl79vvo56du1nhspao0uoasmqdohfvcjna@.4ax.com...
> RE/
> >Yes, it looks like SQLOLEDB version 1 is no longer installed on that box.
> >
> >Is there a particular reason why you need version 1?
> >If not, use what's known as the "VersionIndependentProgID",
> >simply: SQLOLEDB and this will use the latest version installed on the
> >machine.
> Excellant suggestin in it's own right - I've been doing a lot of this
stuff by
> rote and didn't think about versioning...
> Removed the version, same error - which suggests to me that there's no
provider
> of that name on the box.
> Any idea how to install one? I'm guessing it's a download from the MS Web
site
> or my MSDN DVD set...but am a little leery of just "try this...try
that..." for
> fear of hosing the installation completely.
> --
> PeteCresswell|||(Pete Cresswell) (x@.y.z) writes:
> The app in question can see all it's tables in the SQL back end via ODBC
> and everything looks normal via Enterprise Manager, but I can no longer
> create an ADO Connection.
> Source code is unchanged, what's changed is the PC rebuild and consequent
> re-installatin of SQL Server developer version.
> I suspect it's something to do with the provider (at least that's the
> only thing I can see in the .Connect string that's not verifiably
> correct...)
> The error looks like this after I trap and format it:
> ---
> 12/30/03 21:34:30v0.71 Userid: UPQC on SAG
> Proc: basADO: ADO_ConnectionCreate
> -2147467259: [DBNETLIB][ConnectionOpen (Connect()).]SQL
> Server does not exist or access denied.
> Errors encountered when trying to connect:
> '''[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist
> or access denied.
> --Connect String:
> Provider=SQLOLEDB.1;SERVER=SAG;DATABASE=TRETS;UID=Trets;PWD=trets
> ---
The idea that SQLOLEDB should be missing seems to be an incorrect path
to me. In that case you would get a different message. To see what,
try Provider=NISSE.
No, the problem is that the SQL Server SAG cannot be be found. What happens
if you try connect to SAG from Query Analyzer or OSQL?
You may have to add SAG as an alias in the Client Network Utility.
Another possibility is that you have not enabled the network library
that SAG is listening to. Again, this can be checked in the Client
Network Utility. And in the Server Netowrk Utility on SAG, if you have
access to that machine.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||RE/
>The idea that SQLOLEDB should be missing seems to be an incorrect path
>to me. In that case you would get a different message. To see what,
>try Provider=NISSE.
" Error# 3706: Provider cannot be found. It may not be properly installed."
Sounds like you're correct.
>No, the problem is that the SQL Server SAG cannot be be found. What happens
>if you try connect to SAG from Query Analyzer or OSQL?
Dunno what OSQL is, but no problems at all using Query Analyser to do a SELECT *
FROM tlkpVendor... or using Enterprise Manager to open the same table,
returning all records
>You may have to add SAG as an alias in the Client Network Utility.
*Seems* to already be there.
>Another possibility is that you have not enabled the network library
>that SAG is listening to. Again, this can be checked in the Client
>Network Utility. And in the Server Netowrk Utility on SAG, if you have
>access to that machine.
Could you elaborate on that one - seems like the last possibility.
The only enabled protocols I've got are Named Pipes and TCP/IP - but that's what
I'd expect. Both utilities you've mentioned show these two protocols.
--
PeteCresswell|||(Pete Cresswell) (x@.y.z) writes:
> Dunno what OSQL is, but no problems at all using Query Analyser to do a
> SELECT * FROM tlkpVendor... or using Enterprise Manager to open the
> same table, returning all records
Don't know what OSQL is? Hey, Pete, how long have you been working with
SQL Server? It must be over year by now, isn't it?
Sorry, couldn't resist. OSQL is a command-line utility for queries, a more
primitive Query Analyzer if you like. Mainly useful if you want run
prepared SQL scripts from batch files.
Anyway, it is mysterious that you can access the server from the tools,
but not from the application code. This means that we can forget about
the network protocols that I discussed in my previous posting.
Connectivity problems are not my best game, so I'm fairly clueless
myself. The one difference with regards to the tools is that the
tools connect through ODBC and not OLE DB. But I cannot see that it
should matter. You could try to replace "Provider=SQLOLEDB" with
"Driver={SQL Server}", this will change provider to MSDASQL, OLE DB
over ODBC. But this is a poorer alternative, so we don't want to use
that.
Hm, you could also try changing SERVER= into Data Source=, which I
believe is the proper name for OLE DB.
No fantastic tips - I'm just clutching at straws.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||RE/
>No fantastic tips - I'm just clutching at straws.
Thanks for trying - I need anything I can get.
Tried the various Provider/Driver combinations, still no luck.
Changed my connect string to use the SA id/pw and no change.
I'm starting to grasp at my own straw: ADO...maybe there's something amiss
with my ADO. If ODBC works and Enterprise Manager can connect, what's left?
--
PeteCresswell|||RE/
>straws
Oh yeah...I re-installed the MDAC. Put 2.8 over top of 2.6.
Everything else still works: ODBC, Enterprise Manager...
--
PeteCresswell
Saturday, February 25, 2012
cannot connect to the database using sa or a new login
Hi,
I have problem connecting to any of the databases on the local machine using
the "sa" or any of the logins that I had created and gave database access. I
have SQL Server 2000 Developer Edition installed on XP Professional OS.
I am getting the following error:
Java.sql.SQLException: []Login failed for user ‘sa’. Reason: Not XXX
ociatied
with a trusted SQL Server connection
I have never seen this error message.
I have the same SQL Server 2000-Developer edition installed on this other XP
Professional system and there is no problem with connecting to any of its
local machine databases.
Please help!
Thank you,
--
MitraThe error indicates you are trying to access the server with
SQL logins but the server is configured for Windows
Authentication only. So try changing the Authentication mode
to SQL Server and Windows.
You can find this by right clicking on the server in
Enterprise Manager, select properties and go to the security
tab.
-Sue
On Wed, 17 Nov 2004 14:48:02 -0800, "mitra"
<mitra@.discussions.microsoft.com> wrote:
>Hi,
>I have problem connecting to any of the databases on the local machine usin
g
>the "sa" or any of the logins that I had created and gave database access.
I
>have SQL Server 2000 Developer Edition installed on XP Professional OS.
>I am getting the following error:
>Java.sql.SQLException: []Login failed for user sa. Reason: Not associ
atied
>with a trusted SQL Server connection
>I have never seen this error message.
>I have the same SQL Server 2000-Developer edition installed on this other X
P
>Professional system and there is no problem with connecting to any of its
>local machine databases.
>Please help!
>Thank you,|||Hi,
You change the authentication process from "Windows only" to "SQL+Windows"
with the following steps :
1- stop MSSQLServer service
2- edit the value
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\LoginMode to
assign it the value 2.
3- Restart the service
Note : the value LoginMode can be
1 for Windows authenticaton
2 for Mixed authentication
Rgds,
Chris
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:7A3CF1D3-892B-4054-8B72-FED592BE3FC7@.microsoft.com...
> Hi,
> I have problem connecting to any of the databases on the local machine
using
> the "sa" or any of the logins that I had created and gave database access.
I
> have SQL Server 2000 Developer Edition installed on XP Professional OS.
> I am getting the following error:
> Java.sql.SQLException: []Login failed for user 'sa'. Reason: Not
associatied
> with a trusted SQL Server connection
> I have never seen this error message.
> I have the same SQL Server 2000-Developer edition installed on this other
XP
> Professional system and there is no problem with connecting to any of its
> local machine databases.
> Please help!
> Thank you,
> --
> Mitra
I have problem connecting to any of the databases on the local machine using
the "sa" or any of the logins that I had created and gave database access. I
have SQL Server 2000 Developer Edition installed on XP Professional OS.
I am getting the following error:
Java.sql.SQLException: []Login failed for user ‘sa’. Reason: Not XXX
ociatied
with a trusted SQL Server connection
I have never seen this error message.
I have the same SQL Server 2000-Developer edition installed on this other XP
Professional system and there is no problem with connecting to any of its
local machine databases.
Please help!
Thank you,
--
MitraThe error indicates you are trying to access the server with
SQL logins but the server is configured for Windows
Authentication only. So try changing the Authentication mode
to SQL Server and Windows.
You can find this by right clicking on the server in
Enterprise Manager, select properties and go to the security
tab.
-Sue
On Wed, 17 Nov 2004 14:48:02 -0800, "mitra"
<mitra@.discussions.microsoft.com> wrote:
>Hi,
>I have problem connecting to any of the databases on the local machine usin
g
>the "sa" or any of the logins that I had created and gave database access.
I
>have SQL Server 2000 Developer Edition installed on XP Professional OS.
>I am getting the following error:
>Java.sql.SQLException: []Login failed for user sa. Reason: Not associ
atied
>with a trusted SQL Server connection
>I have never seen this error message.
>I have the same SQL Server 2000-Developer edition installed on this other X
P
>Professional system and there is no problem with connecting to any of its
>local machine databases.
>Please help!
>Thank you,|||Hi,
You change the authentication process from "Windows only" to "SQL+Windows"
with the following steps :
1- stop MSSQLServer service
2- edit the value
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\LoginMode to
assign it the value 2.
3- Restart the service
Note : the value LoginMode can be
1 for Windows authenticaton
2 for Mixed authentication
Rgds,
Chris
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:7A3CF1D3-892B-4054-8B72-FED592BE3FC7@.microsoft.com...
> Hi,
> I have problem connecting to any of the databases on the local machine
using
> the "sa" or any of the logins that I had created and gave database access.
I
> have SQL Server 2000 Developer Edition installed on XP Professional OS.
> I am getting the following error:
> Java.sql.SQLException: []Login failed for user 'sa'. Reason: Not
associatied
> with a trusted SQL Server connection
> I have never seen this error message.
> I have the same SQL Server 2000-Developer edition installed on this other
XP
> Professional system and there is no problem with connecting to any of its
> local machine databases.
> Please help!
> Thank you,
> --
> Mitra
Friday, February 10, 2012
Cannot connect studio to my SQL Server Express Database
I am brand-new to databases and SQL Server. I have installed express on my machine. I used SQL Server Management Studio Express to create a new database (per the tutorial). Now I would like to access the database from within Visual Studio 2005.
I went to the Server Explorer and selected Connect To Database. I selected Microsoft SQL Server as the Data Source and hit Continue. In the Server Name combobox, I select my computer. However, in the Connect To A Database box the Select or entre a database Name field does not show any databased. When I select "Test Connection" I get the following error.
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remove connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
In Server Management Studio, under the database's properties, I have the connections set to Allow Remote connections to this server.
What am I doing wrong?
Thanks,
John
Hi John,
SQL Express installs to an instance name so you need to specify the name of the computer and the name of the SQL instance in the VS connection dialog. If you didn't change anything during installation your instance will be named SQLEXPRESS, so the full name would be:
<your computer>\SQLEXPRESS
When working with an installation on the same computer as VS you can shortcut this name using either:
.\SQLEXPRESS or (local)\SQLEXPRESS
Both the dot (".") and "(local)" substitute for your computers name.
Regards,
Mike Wachal
SQL Express team
Mark the best posts as Answers!
|||I've got the same problem, I installed sql express in computer A, then I use computer B to connect the sql server by VS2005 but failed. it showed remote logon server not allowed. but I already configure the server allow remote logon!
is it fire wall blocked the connection? if yes, which port I should release it in firewall?
thanks very much
|||Hi Bnn,
In order to enable remote connections follow the instructions in the Microsoft Knowledge Base article:
914277 How to configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
This article includes information about configuring the fire wall. In the future, it would be a better idea to start a separate thread when you have a new question.
Regards,
Mike Wachal
SQL Express team
-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1
Cannot connect studio to my SQL Server Express Database
I am brand-new to databases and SQL Server. I have installed express on my machine. I used SQL Server Management Studio Express to create a new database (per the tutorial). Now I would like to access the database from within Visual Studio 2005.
I went to the Server Explorer and selected Connect To Database. I selected Microsoft SQL Server as the Data Source and hit Continue. In the Server Name combobox, I select my computer. However, in the Connect To A Database box the Select or entre a database Name field does not show any databased. When I select "Test Connection" I get the following error.
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remove connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
In Server Management Studio, under the database's properties, I have the connections set to Allow Remote connections to this server.
What am I doing wrong?
Thanks,
John
Hi John,
SQL Express installs to an instance name so you need to specify the name of the computer and the name of the SQL instance in the VS connection dialog. If you didn't change anything during installation your instance will be named SQLEXPRESS, so the full name would be:
<your computer>\SQLEXPRESS
When working with an installation on the same computer as VS you can shortcut this name using either:
.\SQLEXPRESS or (local)\SQLEXPRESS
Both the dot (".") and "(local)" substitute for your computers name.
Regards,
Mike Wachal
SQL Express team
Mark the best posts as Answers!
|||I've got the same problem, I installed sql express in computer A, then I use computer B to connect the sql server by VS2005 but failed. it showed remote logon server not allowed. but I already configure the server allow remote logon!
is it fire wall blocked the connection? if yes, which port I should release it in firewall?
thanks very much
|||
Hi Bnn,
In order to enable remote connections follow the instructions in the Microsoft Knowledge Base article:
914277 How to configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
This article includes information about configuring the fire wall. In the future, it would be a better idea to start a separate thread when you have a new question.
Regards,
Mike Wachal
SQL Express team
-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1
Subscribe to:
Posts (Atom)