I have the situation where I need to attach and detach from an installed
MSDE database and I was hoping I'd be able to do it using the SQLConnect
and SQLDisconnect functions from the ODBC API.

However, if I run a test case where I connect to the datasource and then
execute a query and detach - I actually am not detached from the
physical database.

I can verify this by attempting to attach in another application such as
Enterprise Manager. Am I misunderstanding how to do this properly?

Is there a better way to accomplish what I need from within either MSDE
or ODBC specifically?

Thanks,
John

Look into the sp_attachdb and sp_detachdb stored procedures.

The ODBC API just connects to the database by connecting to SQL Server, not
by loading the database files. If you want to detach the database, you have
to tell SQL Server to detach the database (using sp_detachdb).

Let me know if that isn't what you meant (by attach/detach)...

Brannon

"JohnD" <john_nospam@tech-testing.com> wrote in message
news:jgo0d.3$MF6.2@newssvr22.news.prodigy.com...
> I have the situation where I need to attach and detach from an installed
> MSDE database and I was hoping I'd be able to do it using the SQLConnect
> and SQLDisconnect functions from the ODBC API.
>
> However, if I run a test case where I connect to the datasource and then
> execute a query and detach - I actually am not detached from the
> physical database.
>
> I can verify this by attempting to attach in another application such as
> Enterprise Manager. Am I misunderstanding how to do this properly?
>
> Is there a better way to accomplish what I need from within either MSDE
> or ODBC specifically?
>
> Thanks,
> John

Brannon Jones wrote:
> Look into the sp_attachdb and sp_detachdb stored procedures.
>
> The ODBC API just connects to the database by connecting to SQL Server, not
> by loading the database files. If you want to detach the database, you have
> to tell SQL Server to detach the database (using sp_detachdb).
>
> Let me know if that isn't what you meant (by attach/detach)...

But in order to perform DML statements on the database, do I need to do
anything other than SQLConnect in ODBC? Do I specifically need to
execute the sp_attach/sp_detach procedures for basic DML operations?

Thanks,
John

I think there's some term confusion here. It sounds like you're just talking
about connecting to a SQL Server but are calling it attaching. Brannon
thinks you're talking about attaching and detaching a database from SQL
Server because those are the terms you used. :-) The two stored procedures
that Brannon mentioned are for attaching a database to a SQL Server, not
making a user connection to a SQL Server.

After you connect, run a query, and disconnect your ODBC application, you
then connect via Enterprise Manager and see that your ODBC client is still
connected? Is it possible that connection pooling is enabled and that's
what you're seeing? I'm not all that familiar with ODBC, but it is capable
of caching connections for reuse in order to improve connection speed.

--
Sincerely,
Stephen Dybing

This posting is provided "AS IS" with no warranties, and confers no rights.

"JohnD" <john_nospam@tech-testing.com> wrote in message
news:uTD1d.1729$xM6.571@newssvr22.news.prodigy.com ...
> Brannon Jones wrote:
>> Look into the sp_attachdb and sp_detachdb stored procedures.
>>
>> The ODBC API just connects to the database by connecting to SQL Server,
>> not
>> by loading the database files. If you want to detach the database, you
>> have
>> to tell SQL Server to detach the database (using sp_detachdb).
>>
>> Let me know if that isn't what you meant (by attach/detach)...
>
> But in order to perform DML statements on the database, do I need to do
> anything other than SQLConnect in ODBC? Do I specifically need to execute
> the sp_attach/sp_detach procedures for basic DML operations?
>
> Thanks,
> John

Yes, I wasn't sure if he meant attaching/detaching or
connecting/disconnecting, so I went with the term he used =).

Stephen is correct, ODBC will keep a connection open for a certain
(configurable) amount of time, even after you call SQLDisconnect(). This is
to improve performance for applications that connect and disconnect often.

I also wasn't sure about the attach/detach because I thought I remembered
something about MSDE not attaching databases by default. If that is the
case, then I don't know if MSDE would detach the database after the last
ODBC connection automatically.

John- if you could elaborate more on what you mean we could provide better
help.

Brannon

"Stephen Dybing [MSFT]" <stephd@online.microsoft.com> wrote in message
news:ut3asInmEHA.704@TK2MSFTNGP09.phx.gbl...
> I think there's some term confusion here. It sounds like you're just
talking
> about connecting to a SQL Server but are calling it attaching. Brannon
> thinks you're talking about attaching and detaching a database from SQL
> Server because those are the terms you used. :-) The two stored procedures
> that Brannon mentioned are for attaching a database to a SQL Server, not
> making a user connection to a SQL Server.
>
> After you connect, run a query, and disconnect your ODBC application, you
> then connect via Enterprise Manager and see that your ODBC client is still
> connected? Is it possible that connection pooling is enabled and that's
> what you're seeing? I'm not all that familiar with ODBC, but it is capable
> of caching connections for reuse in order to improve connection speed.
>
> --
> Sincerely,
> Stephen Dybing
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "JohnD" <john_nospam@tech-testing.com> wrote in message
> news:uTD1d.1729$xM6.571@newssvr22.news.prodigy.com ...
> > Brannon Jones wrote:
> >> Look into the sp_attachdb and sp_detachdb stored procedures.
> >>
> >> The ODBC API just connects to the database by connecting to SQL Server,
> >> not
> >> by loading the database files. If you want to detach the database, you
> >> have
> >> to tell SQL Server to detach the database (using sp_detachdb).
> >>
> >> Let me know if that isn't what you meant (by attach/detach)...
> >
> > But in order to perform DML statements on the database, do I need to do
> > anything other than SQLConnect in ODBC? Do I specifically need to
execute
> > the sp_attach/sp_detach procedures for basic DML operations?
> >
> > Thanks,
> > John
>
>

Brannon Jones wrote:
> John- if you could elaborate more on what you mean we could provide better
> help.

Brannon,

Actually having descriptions by both you and Stephen referring to the
differences between connecting/disconnecting and attaching/detaching
both from an ODBC and an MSDE standpoint has cleared my confusion.

I'm not new to using ODBC, but unfortunately new to MSDE and the concept
of attaching/detaching databases and confusing that with connecting and
disconnecting - operations which are obviously different. That's what I
was confused about.

Thanks for the clariifcation as I think I understand it now.

John

Perhaps you're thinking of the autoclose option that's set by default in
MSDE. When nobody is connected to a database, it (not the server) is
shutdown and it's resources are freed.

--
Sincerely,
Stephen Dybing

This posting is provided "AS IS" with no warranties, and confers no rights.

"Brannon Jones" <brannonjNOSPAM@gmail.com> wrote in message
news:eYx$lKpmEHA.3712@TK2MSFTNGP15.phx.gbl...
> Yes, I wasn't sure if he meant attaching/detaching or
> connecting/disconnecting, so I went with the term he used =).
>
> Stephen is correct, ODBC will keep a connection open for a certain
> (configurable) amount of time, even after you call SQLDisconnect(). This
> is
> to improve performance for applications that connect and disconnect often.
>
> I also wasn't sure about the attach/detach because I thought I remembered
> something about MSDE not attaching databases by default. If that is the
> case, then I don't know if MSDE would detach the database after the last
> ODBC connection automatically.
>
> John- if you could elaborate more on what you mean we could provide better
> help.
>
> Brannon
>
> "Stephen Dybing [MSFT]" <stephd@online.microsoft.com> wrote in message
> news:ut3asInmEHA.704@TK2MSFTNGP09.phx.gbl...
>> I think there's some term confusion here. It sounds like you're just
> talking
>> about connecting to a SQL Server but are calling it attaching. Brannon
>> thinks you're talking about attaching and detaching a database from SQL
>> Server because those are the terms you used. :-) The two stored
>> procedures
>> that Brannon mentioned are for attaching a database to a SQL Server, not
>> making a user connection to a SQL Server.
>>
>> After you connect, run a query, and disconnect your ODBC application, you
>> then connect via Enterprise Manager and see that your ODBC client is
>> still
>> connected? Is it possible that connection pooling is enabled and that's
>> what you're seeing? I'm not all that familiar with ODBC, but it is
>> capable
>> of caching connections for reuse in order to improve connection speed.
>>
>> --
>> Sincerely,
>> Stephen Dybing
>>
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "JohnD" <john_nospam@tech-testing.com> wrote in message
>> news:uTD1d.1729$xM6.571@newssvr22.news.prodigy.com ...
>> > Brannon Jones wrote:
>> >> Look into the sp_attachdb and sp_detachdb stored procedures.
>> >>
>> >> The ODBC API just connects to the database by connecting to SQL
>> >> Server,
>> >> not
>> >> by loading the database files. If you want to detach the database,
>> >> you
>> >> have
>> >> to tell SQL Server to detach the database (using sp_detachdb).
>> >>
>> >> Let me know if that isn't what you meant (by attach/detach)...
>> >
>> > But in order to perform DML statements on the database, do I need to do
>> > anything other than SQLConnect in ODBC? Do I specifically need to
> execute
>> > the sp_attach/sp_detach procedures for basic DML operations?
>> >
>> > Thanks,
>> > John
>>
>>
>