sp_detach_db from VB6 code failure  
Author Message
Random





PostPosted: Thu Dec 09 08:11:41 CST 2004 Top

SQL Server Developer >> sp_detach_db from VB6 code failure

Hi,

I am trying to detach a database. Sometimes it works, usually after a cold
boot but then it suddenly stops working. I get an error -2147217900 which is
very general. I'm starting a new connection, a new command object and then
calling a stored proc. I have also tried this with using a command text in
the program which is basically what my stored proc uses (cmd2.CommandText =
"EXEC sp_detach_db 'ttsend', 'true'"). Sometimes when I go into query
analyzer and detach then attach the same database, the vb code will work for
one or two times after that.

Any help will be greatly appreciated since I've already been banging my head
against a wall for days.

Thanks

SQL Server241  
 
 
Jacco





PostPosted: Thu Dec 09 08:11:41 CST 2004 Top

SQL Server Developer >> sp_detach_db from VB6 code failure Probably there is still another connection to the database, so you can't
detach it. Disconnect all the other connections first with:
ALTER DATABASE ttsend SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE ttsend SET MULTI_USER
Then detach the database with sp_detach_db

--
Jacco Schalkwijk
SQL Server MVP




> Hi,
>
> I am trying to detach a database. Sometimes it works, usually after a cold
> boot but then it suddenly stops working. I get an error -2147217900 which
> is
> very general. I'm starting a new connection, a new command object and then
> calling a stored proc. I have also tried this with using a command text in
> the program which is basically what my stored proc uses (cmd2.CommandText
> =
> "EXEC sp_detach_db 'ttsend', 'true'"). Sometimes when I go into query
> analyzer and detach then attach the same database, the vb code will work
> for
> one or two times after that.
>
> Any help will be greatly appreciated since I've already been banging my
> head
> against a wall for days.
>
> Thanks
>
>


 
 
E





PostPosted: Thu Dec 09 09:47:55 CST 2004 Top

SQL Server Developer >> sp_detach_db from VB6 code failure This isn't working either. I do have more information though. I'm testing on
my local server but this also happens with a connection to the sql server. I
am executing a stored proc and I'm connected to a different database. That
stored proc from the other database executes, it puts records into ttsend. I
then close that connection and open a new connection to the other database
(not ttsend), new command, etc. The operation works fine; it takes about a
minute or two. Ttsend is detached, copied somewhere else then re-attached.
The program closes the connection and does all necessary clean up without
leaving the same screen in the program. Then when I try the same thing, the
first stored proc operation takes about a second and then I get the message
that I can't detach.

The only thing that helps is if I stop my sql server and then re-start. Then
I can perform the exact same operation without leaving the program or the
point at which it bombed.

Thanks again,

Ellie





> Probably there is still another connection to the database, so you can't
> detach it. Disconnect all the other connections first with:
> ALTER DATABASE ttsend SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> ALTER DATABASE ttsend SET MULTI_USER
> Then detach the database with sp_detach_db
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
>


> > Hi,
> >
> > I am trying to detach a database. Sometimes it works, usually after a
cold
> > boot but then it suddenly stops working. I get an error -2147217900
which
> > is
> > very general. I'm starting a new connection, a new command object and
then
> > calling a stored proc. I have also tried this with using a command text
in
> > the program which is basically what my stored proc uses
(cmd2.CommandText
> > =
> > "EXEC sp_detach_db 'ttsend', 'true'"). Sometimes when I go into query
> > analyzer and detach then attach the same database, the vb code will work
> > for
> > one or two times after that.
> >
> > Any help will be greatly appreciated since I've already been banging my
> > head
> > against a wall for days.
> >
> > Thanks
> >
> >
>
>


 
 
E





PostPosted: Thu Dec 09 10:38:18 CST 2004 Top

SQL Server Developer >> sp_detach_db from VB6 code failure I'm not sure yet, but this might work. I didn't have the multi user set
correctly but I'm still doing tests. Any other insights would still be
helpful.

Thanks



> This isn't working either. I do have more information though. I'm testing
on
> my local server but this also happens with a connection to the sql server.
I
> am executing a stored proc and I'm connected to a different database. That
> stored proc from the other database executes, it puts records into ttsend.
I
> then close that connection and open a new connection to the other database
> (not ttsend), new command, etc. The operation works fine; it takes about a
> minute or two. Ttsend is detached, copied somewhere else then re-attached.
> The program closes the connection and does all necessary clean up without
> leaving the same screen in the program. Then when I try the same thing,
the
> first stored proc operation takes about a second and then I get the
message
> that I can't detach.
>
> The only thing that helps is if I stop my sql server and then re-start.
Then
> I can perform the exact same operation without leaving the program or the
> point at which it bombed.
>
> Thanks again,
>
> Ellie
>
>
>

wrote

> > Probably there is still another connection to the database, so you can't
> > detach it. Disconnect all the other connections first with:
> > ALTER DATABASE ttsend SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> > ALTER DATABASE ttsend SET MULTI_USER
> > Then detach the database with sp_detach_db
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >


> > > Hi,
> > >
> > > I am trying to detach a database. Sometimes it works, usually after a
> cold
> > > boot but then it suddenly stops working. I get an error -2147217900
> which
> > > is
> > > very general. I'm starting a new connection, a new command object and
> then
> > > calling a stored proc. I have also tried this with using a command
text
> in
> > > the program which is basically what my stored proc uses
> (cmd2.CommandText
> > > =
> > > "EXEC sp_detach_db 'ttsend', 'true'"). Sometimes when I go into query
> > > analyzer and detach then attach the same database, the vb code will
work
> > > for
> > > one or two times after that.
> > >
> > > Any help will be greatly appreciated since I've already been banging
my
> > > head
> > > against a wall for days.
> > >
> > > Thanks
> > >
> > >
> >
> >
>
>


 
 
E





PostPosted: Thu Dec 09 10:50:28 CST 2004 Top

SQL Server Developer >> sp_detach_db from VB6 code failure Well I guess this last post a premature and it really isn't work. Sorry



> I'm not sure yet, but this might work. I didn't have the multi user set
> correctly but I'm still doing tests. Any other insights would still be
> helpful.
>
> Thanks
>


> > This isn't working either. I do have more information though. I'm
testing
> on
> > my local server but this also happens with a connection to the sql
server.
> I
> > am executing a stored proc and I'm connected to a different database.
That
> > stored proc from the other database executes, it puts records into
ttsend.
> I
> > then close that connection and open a new connection to the other
database
> > (not ttsend), new command, etc. The operation works fine; it takes about
a
> > minute or two. Ttsend is detached, copied somewhere else then
re-attached.
> > The program closes the connection and does all necessary clean up
without
> > leaving the same screen in the program. Then when I try the same thing,
> the
> > first stored proc operation takes about a second and then I get the
> message
> > that I can't detach.
> >
> > The only thing that helps is if I stop my sql server and then re-start.
> Then
> > I can perform the exact same operation without leaving the program or
the
> > point at which it bombed.
> >
> > Thanks again,
> >
> > Ellie
> >
> >
> >

> wrote

> > > Probably there is still another connection to the database, so you
can't
> > > detach it. Disconnect all the other connections first with:
> > > ALTER DATABASE ttsend SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> > > ALTER DATABASE ttsend SET MULTI_USER
> > > Then detach the database with sp_detach_db
> > >
> > > --
> > > Jacco Schalkwijk
> > > SQL Server MVP
> > >
> > >


> > > > Hi,
> > > >
> > > > I am trying to detach a database. Sometimes it works, usually after
a
> > cold
> > > > boot but then it suddenly stops working. I get an error -2147217900
> > which
> > > > is
> > > > very general. I'm starting a new connection, a new command object
and
> > then
> > > > calling a stored proc. I have also tried this with using a command
> text
> > in
> > > > the program which is basically what my stored proc uses
> > (cmd2.CommandText
> > > > =
> > > > "EXEC sp_detach_db 'ttsend', 'true'"). Sometimes when I go into
query
> > > > analyzer and detach then attach the same database, the vb code will
> work
> > > > for
> > > > one or two times after that.
> > > >
> > > > Any help will be greatly appreciated since I've already been banging
> my
> > > > head
> > > > against a wall for days.
> > > >
> > > > Thanks
> > > >
> > > >
> > >
> > >
> >
> >
>
>


 
 
AlejandroMesa





PostPosted: Thu Dec 09 11:15:06 CST 2004 Top

SQL Server Developer >> sp_detach_db from VB6 code failure Sullivan,

From where are you getting the error, from the err object or from the
connection errors collection?. Try to find more information about the error.

> calling a stored proc. I have also tried this with using a command text in
> the program which is basically what my stored proc uses (cmd2.CommandText =
> "EXEC sp_detach_db 'ttsend', 'true'").

You should set just the name of the sp and fill the parameters collection.
This way you will have acces to the stored procedure return value.

...
with cmd2
.commandtext = "dbo.sp_detach_db"
.commandtype = adCmdStoredProc

.parameters.append .createparameter("ReturnValue", adInteger,
adParamReturnValue)

128, strDbName)

10, "true")

set .activeconnection = oConn

.execute
end

if cmd2.parameters("ReturnValue").value <> 0 then
' there was an error, take care of it
end if
...



AMB




> Hi,
>
> I am trying to detach a database. Sometimes it works, usually after a cold
> boot but then it suddenly stops working. I get an error -2147217900 which is
> very general. I'm starting a new connection, a new command object and then
> calling a stored proc. I have also tried this with using a command text in
> the program which is basically what my stored proc uses (cmd2.CommandText =
> "EXEC sp_detach_db 'ttsend', 'true'"). Sometimes when I go into query
> analyzer and detach then attach the same database, the vb code will work for
> one or two times after that.
>
> Any help will be greatly appreciated since I've already been banging my head
> against a wall for days.
>
> Thanks
>
>
>
 
 
E





PostPosted: Thu Dec 09 11:42:07 CST 2004 Top

SQL Server Developer >> sp_detach_db from VB6 code failure I'm getting that code from the error object. Is there somewhere else that I
can get it. It's a very general error.




> Sullivan,
>
> From where are you getting the error, from the err object or from the
> connection errors collection?. Try to find more information about the
error.
>
> > calling a stored proc. I have also tried this with using a command text
in
> > the program which is basically what my stored proc uses
(cmd2.CommandText =
> > "EXEC sp_detach_db 'ttsend', 'true'").
>
> You should set just the name of the sp and fill the parameters collection.
> This way you will have acces to the stored procedure return value.
>
> ...
> with cmd2
> .commandtext = "dbo.sp_detach_db"
> .commandtype = adCmdStoredProc
>
> .parameters.append .createparameter("ReturnValue", adInteger,
> adParamReturnValue)

> 128, strDbName)

adParamInput,
> 10, "true")
>
> set .activeconnection = oConn
>
> .execute
> end
>
> if cmd2.parameters("ReturnValue").value <> 0 then
> ' there was an error, take care of it
> end if
> ...
>
>
>
> AMB
>
>

>
> > Hi,
> >
> > I am trying to detach a database. Sometimes it works, usually after a
cold
> > boot but then it suddenly stops working. I get an error -2147217900
which is
> > very general. I'm starting a new connection, a new command object and
then
> > calling a stored proc. I have also tried this with using a command text
in
> > the program which is basically what my stored proc uses
(cmd2.CommandText =
> > "EXEC sp_detach_db 'ttsend', 'true'"). Sometimes when I go into query
> > analyzer and detach then attach the same database, the vb code will work
for
> > one or two times after that.
> >
> > Any help will be greatly appreciated since I've already been banging my
head
> > against a wall for days.
> >
> > Thanks
> >
> >
> >


 
 
E





PostPosted: Thu Dec 09 13:15:28 CST 2004 Top

SQL Server Developer >> sp_detach_db from VB6 code failure Is there a way to programmatically stop the sql server for the client
machine and then restart it?
That's really not fixing the problem but I do need to get this resolved.

Thanks



> Probably there is still another connection to the database, so you can't
> detach it. Disconnect all the other connections first with:
> ALTER DATABASE ttsend SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> ALTER DATABASE ttsend SET MULTI_USER
> Then detach the database with sp_detach_db
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
>


> > Hi,
> >
> > I am trying to detach a database. Sometimes it works, usually after a
cold
> > boot but then it suddenly stops working. I get an error -2147217900
which
> > is
> > very general. I'm starting a new connection, a new command object and
then
> > calling a stored proc. I have also tried this with using a command text
in
> > the program which is basically what my stored proc uses
(cmd2.CommandText
> > =
> > "EXEC sp_detach_db 'ttsend', 'true'"). Sometimes when I go into query
> > analyzer and detach then attach the same database, the vb code will work
> > for
> > one or two times after that.
> >
> > Any help will be greatly appreciated since I've already been banging my
> > head
> > against a wall for days.
> >
> > Thanks
> >
> >
>
>