Cannot drop the distribution database 'distribution' because it is currently in use.

All,
I use stored procedures from C#.net to configure and remove
replication.
Whenever, i remove replication i try to first check if any process for
distribution DB (using sp_who2) is remaining in the SQL process space
and kill it.
The possible processes, i knew of using the distribution DB are:
1. SQL .NET data client provider
2. MSSQL EM
3. SQL Query Analyzer
I also checked that no cleanup processes are using it.
But still i get the error "Cannot drop the distribution database
'distribution' because it is currently in use."
Can anyone help in finding out who else is using the Distribution DB?
Please reply asap!
Thanks in advance,
Lavanya
It could be the distribution cleanup agent, the log reader, the distribition
agent or the merge agent. For a user database you would make it single-user
with yourself as the only user then drop it: Alter database distribution set
single_user with rollback immediate; use tempdb; drop database distribution.
For the distributor I'd run sp_dropdistributor followed by
sp_dropdistributiondb rather than the "drop database".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Try this select * From sys.sysprocesses where dbid=db_id('distribution')
in sql 2005 in sql 2000, try this
select * From sysprocesses where dbid=db_id('distribution')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lavanya" <lavanyah@.gmail.com> wrote in message
news:1168258285.713911.183170@.s80g2000cwa.googlegr oups.com...
> All,
> I use stored procedures from C#.net to configure and remove
> replication.
> Whenever, i remove replication i try to first check if any process for
> distribution DB (using sp_who2) is remaining in the SQL process space
> and kill it.
> The possible processes, i knew of using the distribution DB are:
> 1. SQL .NET data client provider
> 2. MSSQL EM
> 3. SQL Query Analyzer
> I also checked that no cleanup processes are using it.
> But still i get the error "Cannot drop the distribution database
> 'distribution' because it is currently in use."
> Can anyone help in finding out who else is using the Distribution DB?
> Please reply asap!
> Thanks in advance,
> Lavanya
>

Cannot create INSTANCENAME

SVR: W2KADV SP4
MSDE is already running supporting Veritas Backup exec and another
application.
Attempting to configure WSUS.
Need to create a new INSTANCENAME=WSUS
Command line setup SAPWD="oursapwd" INSTANCENAME="WSUS"
What I get is this a blackened screen with "Windows 2000 Setup" in the upper
right hand corner. In the center of the screen is a blank menu box entitled
"Usage"
ERRORLOG: entries
2005-07-06 15:33:40.19 server Microsoft SQL Server 2000 - 8.00.818
(Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
2005-07-06 15:33:40.19 server Copyright (C) 1988-2002 Microsoft
Corporation.
2005-07-06 15:33:40.20 server All rights reserved.
2005-07-06 15:33:40.20 server Server Process ID is 9384.
2005-07-06 15:33:40.20 server Logging SQL Server messages in file
'C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG'.
2005-07-06 15:33:40.20 server SQL Server is starting at priority class
'normal'(4 CPUs detected).
2005-07-06 15:33:40.31 server SQL Server configured for thread mode
processing.
2005-07-06 15:33:40.31 server Using dynamic lock allocation. [500] Lock
Blocks, [1000] Lock Owner Blocks.
2005-07-06 15:33:40.34 spid3 Starting up database 'master'.
2005-07-06 15:33:40.45 spid5 Starting up database 'model'.
2005-07-06 15:33:40.45 server Using 'SSNETLIB.DLL' version '8.0.818'.
2005-07-06 15:33:40.47 spid3 Server name is: "Left Blank".
2005-07-06 15:33:40.47 spid3 Skipping startup of clean database id 4
2005-07-06 15:33:40.47 spid3 Skipping startup of clean database id 5
2005-07-06 15:33:40.47 spid3 Starting up database 'SUSDB'.
2005-07-06 15:33:40.53 server SQL server listening on Shared Memory.
2005-07-06 15:33:40.53 server SQL Server is ready for client connections
2005-07-06 15:33:40.56 spid5 Clearing tempdb database.
2005-07-06 15:33:40.56 spid3 3 transactions rolled forward in database
'SUSDB' (6).
2005-07-06 15:33:40.58 spid3 0 transactions rolled back in database
'SUSDB' (6).
2005-07-06 15:33:40.58 spid3 Recovery is checkpointing database 'SUSDB'
(6)
2005-07-06 15:33:40.76 spid5 Starting up database 'tempdb'.
2005-07-06 15:33:40.80 spid3 Recovery complete.
2005-07-06 15:33:40.80 spid3 SQL global counter collection task is
created.
2005-07-06 15:33:43.64 spid51 Starting up database 'SUSDB'.
2005-07-06 15:48:59.22 spid52 Starting up database 'msdb'.
2005-07-06 15:48:59.44 spid52 Using 'xpsqlbot.dll' version '2000.80.760'
to execute extended stored procedure 'xp_qv'.
hi,
Lokton wrote:
> SVR: W2KADV SP4
> MSDE is already running supporting Veritas Backup exec and another
> application.
> Attempting to configure WSUS.
> Need to create a new INSTANCENAME=WSUS
> Command line setup SAPWD="oursapwd" INSTANCENAME="WSUS"
> What I get is this a blackened screen with "Windows 2000 Setup" in
> the upper right hand corner. In the center of the screen is a blank
> menu box entitled "Usage"
> ERRORLOG: entries
this is the error log of the default MSDE instance probably used by Veritas
and other sw...
if you are using the new sp4 ugraded MSDE installation package, please try
omitting the quotes surrounding "oursapwd" and "WSUS"
then please add the
/L*v "c:\msdelog.txt"
parameter to your call to setup.exe boostrap installer, in order to allow
verbose logging of the installation process.. should error(s) be raised a
RETURN VALUE 3
will be written to the log, and about 10/15 lines before each entry some
(sometime criptic) description of the problem will be reported...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Cannot create a valid ODBC DSN to SQL Server Express

Hello All,

I've just spent the better part of a day trying to configure SQL Express to allow me to create an ODBC connection. When I do create one via Control Panel/Admin/ODBC it tells me that its timing out on the connection (localhost) and the problem is probably that SQL Server Express comes configured NOT to allow remote connections. I've enabled TCP/IP and named pipes, allowed remote connections, enabled the SQL Browser, and just about everything I;ve read in the docs and forums.

Am I missing the obvious?

Kind Regards,

Pat.

Pat,

I guess you are using (localhost) as the target server which is invalid. Either you can use (local)\sqlexpress or localhost\sqlexpress to connect to local sql express instance, a name instance.

Thanks,

Nan

|||

Nan,

You are bang on! Thank you very much.

I've created a test user database, but when i attempt to "get external data/link tables" in access, I can't see my database, only all the system tables.

Any thoughts?

Kind Regards,

Pat.

|||

Did you specify your database name while creating DSN?

It's under 'Change the default dabase to' checkbox.

|||

Thank you, gavrilenko_s - that did it.

To both you and Nan, if in Dublin, send me an email - there are pints of guinness woith your names on them.

appreciatively,

Pat Backowski

Consider this thread closed and solved - well done, lads!

|||Holy Telemundo, Batman! If I hadn't found this thread, I was seriously considering just using MySQL for my app. I've talked to a few guys who have had this problem too. You may want to include a hint about this solution in the next revision of the connectivity tools so people don't go wasting so much time on what could have been a simple solution.

Cannot create a valid ODBC DSN to SQL Server Express

Hello All,

I've just spent the better part of a day trying to configure SQL Express to allow me to create an ODBC connection. When I do create one via Control Panel/Admin/ODBC it tells me that its timing out on the connection (localhost) and the problem is probably that SQL Server Express comes configured NOT to allow remote connections. I've enabled TCP/IP and named pipes, allowed remote connections, enabled the SQL Browser, and just about everything I;ve read in the docs and forums.

Am I missing the obvious?

Kind Regards,

Pat.

Pat,

I guess you are using (localhost) as the target server which is invalid. Either you can use (local)\sqlexpress or localhost\sqlexpress to connect to local sql express instance, a name instance.

Thanks,

Nan

|||

Nan,

You are bang on! Thank you very much.

I've created a test user database, but when i attempt to "get external data/link tables" in access, I can't see my database, only all the system tables.

Any thoughts?

Kind Regards,

Pat.

|||

Did you specify your database name while creating DSN?

It's under 'Change the default dabase to' checkbox.

|||

Thank you, gavrilenko_s - that did it.

To both you and Nan, if in Dublin, send me an email - there are pints of guinness woith your names on them.

appreciatively,

Pat Backowski

Consider this thread closed and solved - well done, lads!

|||Holy Telemundo, Batman! If I hadn't found this thread, I was seriously considering just using MySQL for my app. I've talked to a few guys who have had this problem too. You may want to include a hint about this solution in the next revision of the connectivity tools so people don't go wasting so much time on what could have been a simple solution.

Cannot connect to database

I have SQL Server 2000 Developers Edition install at work and home
machines., I configure as Windows Authentication.
I am using the CASE Tools DBDesigner to connect to SQL Server.
However, I got probelm in connecting to SQL SErver 2000 at home, the
scenario is below:
1)I create a Windows user in Windows 2000 Professional by Control Panel
2)Go to SQL Server Enterprise Manager
3)Goto Security, create Logins
4)Goto Northwind database
5)Add Database User from Users folder
The problem is it works at work machine, but when I do the same at home
machine, I got different things.
At home machine, I got the new user has domain name prefix, it does not
appear in my work machine.
Now, at home machine, I tried to connect from DBDesigner 4, I got the error
something like the login is not associated with trusted login.
Why it was happened like this ?Alan
Try to switch Authentication to SQL Server and Windows.
Connect to SQL Server as 'sa'
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:e%23AlYYoTEHA.1548@.TK2MSFTNGP11.phx.gbl...
> I have SQL Server 2000 Developers Edition install at work and home
> machines., I configure as Windows Authentication.
> I am using the CASE Tools DBDesigner to connect to SQL Server.
> However, I got probelm in connecting to SQL SErver 2000 at home, the
> scenario is below:
> 1)I create a Windows user in Windows 2000 Professional by Control Panel
> 2)Go to SQL Server Enterprise Manager
> 3)Goto Security, create Logins
> 4)Goto Northwind database
> 5)Add Database User from Users folder
> The problem is it works at work machine, but when I do the same at home
> machine, I got different things.
> At home machine, I got the new user has domain name prefix, it does not
> appear in my work machine.
> Now, at home machine, I tried to connect from DBDesigner 4, I got the
error
> something like the login is not associated with trusted login.
> Why it was happened like this ?
>|||What is the exact error you are getting?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Sorry for late reply.
Now I need to list my details of my work machine SQL Server Developer
editions within Enterprise Manager:
- Use Windows authentication
- I add one login eg. 'bbb', password with 'bbb' from Security|Logins
I have NOT added user in Northwind database yet.
Now leave Enterprise Manager.
Goto DBDesigner 4, select the option reverse engineering, select the
connection to Northwind database, type in 'bbb' with password 'bbb'.
Magic is coming out: DBDesigner can reengineer the logical database
structure for me.
This is so far so good in my working place machine.
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:W4ql$9uTEHA.304@.cpmsftngxa10.phx.gbl...
> What is the exact error you are getting?
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

Cannot connect to database

I have SQL Server 2000 Developers Edition install at work and home
machines., I configure as Windows Authentication.
I am using the CASE Tools DBDesigner to connect to SQL Server.
However, I got probelm in connecting to SQL SErver 2000 at home, the
scenario is below:
1)I create a Windows user in Windows 2000 Professional by Control Panel
2)Go to SQL Server Enterprise Manager
3)Goto Security, create Logins
4)Goto Northwind database
5)Add Database User from Users folder
The problem is it works at work machine, but when I do the same at home
machine, I got different things.
At home machine, I got the new user has domain name prefix, it does not
appear in my work machine.
Now, at home machine, I tried to connect from DBDesigner 4, I got the error
something like the login is not associated with trusted login.
Why it was happened like this ?Alan
Try to switch Authentication to SQL Server and Windows.
Connect to SQL Server as 'sa'
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:e%23AlYYoTEHA.1548@.TK2MSFTNGP11.phx.gbl...
> I have SQL Server 2000 Developers Edition install at work and home
> machines., I configure as Windows Authentication.
> I am using the CASE Tools DBDesigner to connect to SQL Server.
> However, I got probelm in connecting to SQL SErver 2000 at home, the
> scenario is below:
> 1)I create a Windows user in Windows 2000 Professional by Control Panel
> 2)Go to SQL Server Enterprise Manager
> 3)Goto Security, create Logins
> 4)Goto Northwind database
> 5)Add Database User from Users folder
> The problem is it works at work machine, but when I do the same at home
> machine, I got different things.
> At home machine, I got the new user has domain name prefix, it does not
> appear in my work machine.
> Now, at home machine, I tried to connect from DBDesigner 4, I got the
error
> something like the login is not associated with trusted login.
> Why it was happened like this ?
>|||What is the exact error you are getting?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Sorry for late reply.
Now I need to list my details of my work machine SQL Server Developer
editions within Enterprise Manager:
- Use Windows authentication
- I add one login eg. 'bbb', password with 'bbb' from Security|Logins
I have NOT added user in Northwind database yet.
Now leave Enterprise Manager.
Goto DBDesigner 4, select the option reverse engineering, select the
connection to Northwind database, type in 'bbb' with password 'bbb'.
Magic is coming out: DBDesigner can reengineer the logical database
structure for me.
This is so far so good in my working place machine.
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:W4ql$9uTEHA.304@.cpmsftngxa10.phx.gbl...
> What is the exact error you are getting?
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

Cannot connect to database

I have SQL Server 2000 Developers Edition install at work and home
machines., I configure as Windows Authentication.
I am using the CASE Tools DBDesigner to connect to SQL Server.
However, I got probelm in connecting to SQL SErver 2000 at home, the
scenario is below:
1)I create a Windows user in Windows 2000 Professional by Control Panel
2)Go to SQL Server Enterprise Manager
3)Goto Security, create Logins
4)Goto Northwind database
5)Add Database User from Users folder
The problem is it works at work machine, but when I do the same at home
machine, I got different things.
At home machine, I got the new user has domain name prefix, it does not
appear in my work machine.
Now, at home machine, I tried to connect from DBDesigner 4, I got the error
something like the login is not associated with trusted login.
Why it was happened like this ?
Alan
Try to switch Authentication to SQL Server and Windows.
Connect to SQL Server as 'sa'
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:e%23AlYYoTEHA.1548@.TK2MSFTNGP11.phx.gbl...
> I have SQL Server 2000 Developers Edition install at work and home
> machines., I configure as Windows Authentication.
> I am using the CASE Tools DBDesigner to connect to SQL Server.
> However, I got probelm in connecting to SQL SErver 2000 at home, the
> scenario is below:
> 1)I create a Windows user in Windows 2000 Professional by Control Panel
> 2)Go to SQL Server Enterprise Manager
> 3)Goto Security, create Logins
> 4)Goto Northwind database
> 5)Add Database User from Users folder
> The problem is it works at work machine, but when I do the same at home
> machine, I got different things.
> At home machine, I got the new user has domain name prefix, it does not
> appear in my work machine.
> Now, at home machine, I tried to connect from DBDesigner 4, I got the
error
> something like the login is not associated with trusted login.
> Why it was happened like this ?
>
|||What is the exact error you are getting?
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Sorry for late reply.
Now I need to list my details of my work machine SQL Server Developer
editions within Enterprise Manager:
- Use Windows authentication
- I add one login eg. 'bbb', password with 'bbb' from Security|Logins
I have NOT added user in Northwind database yet.
Now leave Enterprise Manager.
Goto DBDesigner 4, select the option reverse engineering, select the
connection to Northwind database, type in 'bbb' with password 'bbb'.
Magic is coming out: DBDesigner can reengineer the logical database
structure for me.
This is so far so good in my working place machine.
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:W4ql$9uTEHA.304@.cpmsftngxa10.phx.gbl...
> What is the exact error you are getting?
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>