Sql Cannot connect: procedures

Showing posts with label procedures. Show all posts

Sunday, March 25, 2012

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 drop stored proc

I use sp_rename to rename several stored procedures.
sp_rename 'MyDB.dbo.spRPTKCTicketCount', ''MyDB.dbo.spRPTKCTicketCount_obsol
ete'
The message was:The object was renamed to 'MyDB.dbo.spRPTKCTicketCount'_obso
lete'.
Caution: Changing any part of an object name could break scripts and stored
procedures.
But now, I cannot drop the stored procedure because it was stored in sysobje
cts as 'MyDB.dbo.spRPTKCTicketCount'_obsolete'
So when I use drop 'MyDB.dbo.spRPTKCTicketCount'_obsolete' it return an erro
r saying that
"Server: Msg 166, Level 15, State 1, Line 1
'DROP PROCEDURE' does not allow specifying the database name as a prefix to
the object name"
and if I take the Database name out of it, it says that it cannot find the i
t in the system catalogs.
Please help...Hi Jorge
Can you try putting the name in square brackets?
DROP PROCEDURE [MyDB.dbo.spRPTKCTicketCount_obsolete]
If that doesn't work, see if you can change the name again, to something
simpler.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jorge" <anonymous@.discussions.microsoft.com> wrote in message
news:6A32D11A-2EFE-48BA-B61F-F1004EB1F446@.microsoft.com...
> I use sp_rename to rename several stored procedures.
> sp_rename 'MyDB.dbo.spRPTKCTicketCount',
''MyDB.dbo. spRPTKCTicketCount_obsolete'ed">
> The message was:The object was renamed to
'MyDB.dbo.spRPTKCTicketCount'_obsolete'.
> Caution: Changing any part of an object name could break scripts and
stored procedures.
> But now, I cannot drop the stored procedure because it was stored in
sysobjects as 'MyDB.dbo. spRPTKCTicketCount'_obsolete'red">
> So when I use drop 'MyDB.dbo.spRPTKCTicketCount'_obsolete' it return an
error saying that
> "Server: Msg 166, Level 15, State 1, Line 1
> 'DROP PROCEDURE' does not allow specifying the database name as a prefix
to the object name"
> and if I take the Database name out of it, it says that it cannot find the
it in the system catalogs.
> Please help...
>|||Thank you...the brackets seem to work.
Actually, I'm impressed and surprised. I never thought that the author of Sq
l Server book would respond to me. It's an honor.
Jorge Chirinos
jorge_chirinos@.hotmail.com

Cannot drop database (hanging in loading and marked for replication)

Hi
I have restored a database to the same instance as it was backup, but as a
copy of the original database (I need a couple of Stored Procedures), but
while restoring the database is to hanging with (Loading).
I've tried to drop the database but it says that it cannot be dropped
because it is marked for replication, I've then tried to drop the
replication on the database but it can't, it says that the database cannot
be opened because it is in the middle of a restore.
What to do? I'm pretty stuck...
I've running SQL Server 2000 on a windows 2003 server SP1, the database is
less than 100MB in size.
-KimI was having the same problem restoring my database (without replication
issue) and it's stuck loading for an hour, so stoped SQL Server and removed
that datbase mdf and log files..
Then restarted my server and run mentanance task (optmizing & fix intgerity)
on master database..
Then restore my database from the backup
it finished in less than a minute.
"Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
news:43f6ec88$1@.news.carlbro.dk...
> Hi
> I have restored a database to the same instance as it was backup, but as a
> copy of the original database (I need a couple of Stored Procedures), but
> while restoring the database is to hanging with (Loading).
> I've tried to drop the database but it says that it cannot be dropped
> because it is marked for replication, I've then tried to drop the
> replication on the database but it can't, it says that the database cannot
> be opened because it is in the middle of a restore.
> What to do? I'm pretty stuck...
> I've running SQL Server 2000 on a windows 2003 server SP1, the database is
> less than 100MB in size.
> -Kim
>|||Hi,
I've removed the psysical files as suggested and runned the maintenance task
"check database integrity" on master (couldn't find optimize + fix
integrity).
This hasn't removed the entry from the enterprise manager... should I make
a new restore and then remove the entry ? (I don't need to restore it
anymore)
-KIm
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:ummPctJNGHA.532@.TK2MSFTNGP15.phx.gbl...
>I was having the same problem restoring my database (without replication
>issue) and it's stuck loading for an hour, so stoped SQL Server and removed
>that datbase mdf and log files..
> Then restarted my server and run mentanance task (optmizing & fix
> intgerity) on master database..
> Then restore my database from the backup
> it finished in less than a minute.
> "Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
> news:43f6ec88$1@.news.carlbro.dk...
>|||"Islamegy":[vbcol=seagreen]
[vbcol=seagreen]
Hi again,
I've removed the psysical files as suggested and runned the maintenance task
"check database integrity" on master (couldn't find optimize + fix
integrity).
This hasn't removed the entry from the enterprise manager... should I make
a new restore and then remove the entry ? (I don't need to restore it
anymore)
-KIm|||(sorry if this message is posted more than once, I've had trouble submitting
my mail to my newsserver)
"Islamegy":[vbcol=seagreen]
[vbcol=seagreen]
Hi again,
I've removed the psysical files as suggested and runned the maintenance task
"check database integrity" on master (couldn't find optimize + fix
integrity).
This hasn't removed the entry from the enterprise manager... should I make
a new restore and then remove the entry ? (I don't need to restore it
anymore)
-KIm

Cannot drop database (hanging in loading and marked for replication)

Hi
I have restored a database to the same instance as it was backup, but as a
copy of the original database (I need a couple of Stored Procedures), but
while restoring the database is to hanging with (Loading).
I've tried to drop the database but it says that it cannot be dropped
because it is marked for replication, I've then tried to drop the
replication on the database but it can't, it says that the database cannot
be opened because it is in the middle of a restore.
What to do? I'm pretty stuck...
I've running SQL Server 2000 on a windows 2003 server SP1, the database is
less than 100MB in size.
-KimI was having the same problem restoring my database (without replication
issue) and it's stuck loading for an hour, so stoped SQL Server and removed
that datbase mdf and log files..
Then restarted my server and run mentanance task (optmizing & fix intgerity)
on master database..
Then restore my database from the backup
it finished in less than a minute.
"Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
news:43f6ec88$1@.news.carlbro.dk...
> Hi
> I have restored a database to the same instance as it was backup, but as a
> copy of the original database (I need a couple of Stored Procedures), but
> while restoring the database is to hanging with (Loading).
> I've tried to drop the database but it says that it cannot be dropped
> because it is marked for replication, I've then tried to drop the
> replication on the database but it can't, it says that the database cannot
> be opened because it is in the middle of a restore.
> What to do? I'm pretty stuck...
> I've running SQL Server 2000 on a windows 2003 server SP1, the database is
> less than 100MB in size.
> -Kim
>|||Hi,
I've removed the psysical files as suggested and runned the maintenance task
"check database integrity" on master (couldn't find optimize + fix
integrity).
This hasn't removed the entry from the enterprise manager... should I make
a new restore and then remove the entry ? (I don't need to restore it
anymore)
-KIm
"Islamegy®" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:ummPctJNGHA.532@.TK2MSFTNGP15.phx.gbl...
>I was having the same problem restoring my database (without replication
>issue) and it's stuck loading for an hour, so stoped SQL Server and removed
>that datbase mdf and log files..
> Then restarted my server and run mentanance task (optmizing & fix
> intgerity) on master database..
> Then restore my database from the backup
> it finished in less than a minute.
> "Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
> news:43f6ec88$1@.news.carlbro.dk...
>> Hi
>> I have restored a database to the same instance as it was backup, but as
>> a copy of the original database (I need a couple of Stored Procedures),
>> but while restoring the database is to hanging with (Loading).
>> I've tried to drop the database but it says that it cannot be dropped
>> because it is marked for replication, I've then tried to drop the
>> replication on the database but it can't, it says that the database
>> cannot be opened because it is in the middle of a restore.
>> What to do? I'm pretty stuck...
>> I've running SQL Server 2000 on a windows 2003 server SP1, the database
>> is less than 100MB in size.
>> -Kim
>|||"Islamegy®":
>>I was having the same problem restoring my database (without replication
>>issue) and it's stuck loading for an hour, so stoped SQL Server and
>>removed that datbase mdf and log files..
>> Then restarted my server and run mentanance task (optmizing & fix
>> intgerity) on master database..
>> Then restore my database from the backup
>> it finished in less than a minute.
>> "Kim":
>> I have restored a database to the same instance as it was backup, but as
>> a copy of the original database (I need a couple of Stored Procedures),
>> but while restoring the database is to hanging with (Loading).
>> I've tried to drop the database but it says that it cannot be dropped
>> because it is marked for replication, I've then tried to drop the
>> replication on the database but it can't, it says that the database
>> cannot be opened because it is in the middle of a restore.
>> What to do? I'm pretty stuck...
>> I've running SQL Server 2000 on a windows 2003 server SP1, the database
>> is less than 100MB in size.
>> -Kim
Hi again,
I've removed the psysical files as suggested and runned the maintenance task
"check database integrity" on master (couldn't find optimize + fix
integrity).
This hasn't removed the entry from the enterprise manager... should I make
a new restore and then remove the entry ? (I don't need to restore it
anymore)
-KIm

Tuesday, March 20, 2012

Cannot Debug SQL 2005 Stored Procedures in VS 2005

I am having trouble in consistently debugging T-SQL stored procedures in SQL
2005 using VS 2005 Pro. I have enabled debugging on the connection in the
Server Explorer and enable CLR Integration in the Surface Area Configuration
tool. At one point I could step into the sproc but now I cannot. What has
gone wrong. How can I be able to consistently debug T-SQL sprocs?
Any input would be appreciated."=?Utf-8?B? RWFnbGVSZWRASGlnaEZseWluZ0JpcmRzLmNvbQ==
?="
<EagleRedHighFlyingBirdscom@.discussions.microsoft.com> wrote in
news:2EC86A08-79A4-44CC-ABDF-72DAE46D1BF0@.microsoft.com:

> I am having trouble in consistently debugging T-SQL stored procedures
> in SQL 2005 using VS 2005 Pro. I have enabled debugging on the
> connection in the Server Explorer and enable CLR Integration in the
> Surface Area Configuration tool. At one point I could step into the
> sproc but now I cannot. What has gone wrong. How can I be able to
> consistently debug T-SQL sprocs?
>
Hmm, in order to debug T-SQL proc's you should not have to do any
enabling at all.
1. Open VS 2005
2. Open Server Explorer
3. Create a connection to the database the proc is in
4. Expand the database and drill down to Stored Procedures, expand.
5. Right click on the proc and choose "Step Into Stored Procedure".
6. Debug
What happens for you when you try the above?
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||The debugger starts but when I either step into or step to the next statemen
t
in the proc the proc runs to completion. I have set a breakpoint in the pro
c
only to have it disabled when debugging starts.
"EagleRed@.HighFlyingBirds.com" wrote:

> I am having trouble in consistently debugging T-SQL stored procedures in S
QL
> 2005 using VS 2005 Pro. I have enabled debugging on the connection in the
> Server Explorer and enable CLR Integration in the Surface Area Configurati
on
> tool. At one point I could step into the sproc but now I cannot. What ha
s
> gone wrong. How can I be able to consistently debug T-SQL sprocs?
> Any input would be appreciated.|||Here's some additional information. The disabled breakpoint displays a
tooltip that says the breakpoint will not be hit because the object
containing the breakpoint has not been loaded. What's going on? I have bee
n
able to debug and hit breakpoints, but I cannot do it consistently and right
now it simply isn't working.
"Niels Berglund" wrote:

> "=?Utf-8?B? RWFnbGVSZWRASGlnaEZseWluZ0JpcmRzLmNvbQ==
?="
> <EagleRedHighFlyingBirdscom@.discussions.microsoft.com> wrote in
> news:2EC86A08-79A4-44CC-ABDF-72DAE46D1BF0@.microsoft.com:
>
> Hmm, in order to debug T-SQL proc's you should not have to do any
> enabling at all.
> 1. Open VS 2005
> 2. Open Server Explorer
> 3. Create a connection to the database the proc is in
> 4. Expand the database and drill down to Stored Procedures, expand.
> 5. Right click on the proc and choose "Step Into Stored Procedure".
> 6. Debug
> What happens for you when you try the above?
> Niels
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>

Sunday, March 25, 2012

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

Cannot drop stored proc

Cannot drop database (hanging in loading and marked for replication)

Cannot drop database (hanging in loading and marked for replication)

Cannot drop database (hanging in loading and marked for replication)

Tuesday, March 20, 2012

Cannot Debug SQL 2005 Stored Procedures in VS 2005

Sql Cannot connect

Blog Archive

About Me