Aborting CALL to stored procedure

Hello

I am calling a stored procedure in a MSDE/SQLServer DB form within my
Visual C++ 6.0 program along the lines
CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
With
DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
It all works sweet as, but it can take a while and I want to let the
user abort it.
Everything I've tried ends in tears.Hi

You can issue a KILL command on the SQL Server which will terminate the
process. To do this you are going to need a separate thread. More
information in books online.

John

"Mike Brown" <browna@.beer.com> wrote in message
news:ea197978.0406302059.3f4b8524@.posting.google.c om...
> Hello
> I am calling a stored procedure in a MSDE/SQLServer DB form within my
> Visual C++ 6.0 program along the lines
> CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
> With
> DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
> It all works sweet as, but it can take a while and I want to let the
> user abort it.
> Everything I've tried ends in tears.|||I have the command running in a separate thread.
I dont want to kill the server, just the CALL. I have tried killing
the thread and using .Abort(), and most other things I can think of,
but everything results in my program crashing.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<AKPEc.694$t8.6278387@.news-text.cableinet.net>...
> Hi
> You can issue a KILL command on the SQL Server which will terminate the
> process. To do this you are going to need a separate thread. More
> information in books online.
> John
> "Mike Brown" <browna@.beer.com> wrote in message
> news:ea197978.0406302059.3f4b8524@.posting.google.c om...
> > Hello
> > I am calling a stored procedure in a MSDE/SQLServer DB form within my
> > Visual C++ 6.0 program along the lines
> > CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
> > With
> > DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
> > It all works sweet as, but it can take a while and I want to let the
> > user abort it.
> > Everything I've tried ends in tears.|||Hi

I am not sure what you mean by killing the server. Look up the KILL command
in books online.
Killing your thread should not result in the program crashing, but may leave
an orphaned process on the SQL server.

John

"Mike Brown" <browna@.beer.com> wrote in message
news:ea197978.0407011050.4a44f3c5@.posting.google.c om...
> I have the command running in a separate thread.
> I dont want to kill the server, just the CALL. I have tried killing
> the thread and using .Abort(), and most other things I can think of,
> but everything results in my program crashing.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<AKPEc.694$t8.6278387@.news-text.cableinet.net>...
> > Hi
> > You can issue a KILL command on the SQL Server which will terminate the
> > process. To do this you are going to need a separate thread. More
> > information in books online.
> > John
> > "Mike Brown" <browna@.beer.com> wrote in message
> > news:ea197978.0406302059.3f4b8524@.posting.google.c om...
> > > Hello
> > > > I am calling a stored procedure in a MSDE/SQLServer DB form within my
> > > Visual C++ 6.0 program along the lines
> > > CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
> > > With
> > > DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
> > > It all works sweet as, but it can take a while and I want to let the
> > > user abort it.
> > > Everything I've tried ends in tears.|||John Bell (jbellnewsposts@.hotmail.com) writes:
> I am not sure what you mean by killing the server. Look up the KILL
> command in books online.

And Books Online says:

KILL permissions default to the members of the sysadmin and processadmin
fixed database roles, and are not transferable.

And Mike wants to give his users away to cancel their running commands.

And killing the entire connection would be a huge overkill anyway, when
all you want to do is to cancel the current batch.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Mike Brown (browna@.beer.com) writes:
> I am calling a stored procedure in a MSDE/SQLServer DB form within my
> Visual C++ 6.0 program along the lines
> CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
> With
> DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
> It all works sweet as, but it can take a while and I want to let the
> user abort it.
> Everything I've tried ends in tears.

You don't say much of what you have tried. Then again, I will have to
admit that I have no experience of OLE DB Consumer templates, although
I've recently started to program against SQLOLEDB.

But I can't see but that to do this, you need to use asynchrounous
execution. The MDAC Books Online says:

Consumers that want to asynchronously open a rowset set the
DBPROPVAL_ASYNCH_INITIALIZE bit in the DBPROP_ROWSET_ASYNCH property.
When setting this bit prior to calling ICommand::Execute,
IOpenRowset::OpenRowset, IDBSchemaRowset::GetRowset,
IRowPosition::GetRowset, IColumnsRowset::GetColumnsRowset,
IMultipleResults::GetResult, ISourcesRowset::GetSourcesRowset, or any
other method that returns a rowset, riid must be set to
IID_IDBAsynchStatus, IID_IConnectionPointContainer, or IID_IUnknown.
...
To cancel creation of the rowset, the consumer can call
IDBAsynchStatus::Abort or can simply release all interfaces on the
rowset. Once the rowset's reference count goes to zero, any
asynchronous processing is canceled and the rowset is released. Calling
IDBAsynchStatus::Abort still requires releasing the interface.

If you don't do it asynchrounously... I guess you could start to
release things from another thread, but I'm not surprised if it ends
in tears...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||John was referring to the T-SQL 'KILL' command, not the unix kill command.

"Mike Brown" <browna@.beer.com> wrote in message
news:ea197978.0407011050.4a44f3c5@.posting.google.c om...
> I have the command running in a separate thread.
> I dont want to kill the server, just the CALL. I have tried killing
> the thread and using .Abort(), and most other things I can think of,
> but everything results in my program crashing.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<AKPEc.694$t8.6278387@.news-text.cableinet.net>...
> > Hi
> > You can issue a KILL command on the SQL Server which will terminate the
> > process. To do this you are going to need a separate thread. More
> > information in books online.