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.

a stored procedure in C# creates a file (the file cant be deleted)

hi,
I wrote a dll in c#. It creates a bitmap file. the dll is appended to sql
server 2005 as an assembly. when I call that stored procedure it creates a
bitmap file on my local disk. the problem is, that the file cant be deleted.
I get error saying that the file is being used by another person or
application. but it isnt!. moreover, when I create an exe file and launch it,
everything works ok. then, the exe creates the bitmap which can be easy
deleted.
how to check what application is using the bitmap (a resource)? what is the
difference in creating the bitmap by launching a stored sql procedure than by
launching an exe application?
Hello Chris,
FileMon from WinTernals comes to mind. See http://www.winternals.com/
Chances are that the SQL Server is holding a lock on that file because you
didn't close the file handle on it. Can you post the bit of code that writes
the bitmap to the file?
Thanks,
Kent Tegels
DevelopMentor

a stored procedure in C# creates a file (the file cant be deleted)

hi,
I wrote a dll in c#. It creates a bitmap file. the dll is appended to sql
server 2005 as an assembly. when I call that stored procedure it creates a
bitmap file on my local disk. the problem is, that the file cant be deleted.
I get error saying that the file is being used by another person or
application. but it isnt!. moreover, when I create an exe file and launch it
,
everything works ok. then, the exe creates the bitmap which can be easy
deleted.
how to check what application is using the bitmap (a resource)? what is the
difference in creating the bitmap by launching a stored sql procedure than b
y
launching an exe application?Hello Chris,
FileMon from WinTernals comes to mind. See http://www.winternals.com/
Chances are that the SQL Server is holding a lock on that file because you
didn't close the file handle on it. Can you post the bit of code that writes
the bitmap to the file?
Thanks,
Kent Tegels
DevelopMentor

a stored procedure in C# creates a file (the file cant be deleted)

hi,
I wrote a dll in c#. It creates a bitmap file. the dll is appended to sql
server 2005 as an assembly. when I call that stored procedure it creates a
bitmap file on my local disk. the problem is, that the file cant be deleted.
I get error saying that the file is being used by another person or
application. but it isnt!. moreover, when I create an exe file and launch it,
everything works ok. then, the exe creates the bitmap which can be easy
deleted.
how to check what application is using the bitmap (a resource)? what is the
difference in creating the bitmap by launching a stored sql procedure than by
launching an exe application?Hello Chris,
FileMon from WinTernals comes to mind. See http://www.winternals.com/
Chances are that the SQL Server is holding a lock on that file because you
didn't close the file handle on it. Can you post the bit of code that writes
the bitmap to the file?
Thanks,
Kent Tegels
DevelopMentor

A problem with SQLColumns call

Hello,
let us consider the following problem with SQLColumns call. Fist of all here goes a small quote from MSDN on SQLColumns input string parameters:

CatalogName [Input]
Catalog name. If a driver supports catalogs for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string ("") denotes those tables that do not have catalogs.

I read it as I can call SQLColumns the following ways:
SQLColumns(hstmt_, (SQLCHAR *)"",SQL_NTS, ...
or
SQLColumns(hstmt_, (SQLCHAR *)"",0, ...

but none of this calls works. The only working way is

SQLColumns(hstmt_, 0,0, ...

Please, note that all calls work for non-Microsoft ODBC drivers I have tried.

So, I believe there is a bug either in MS ODBC driver implementation or in MSDN.

--
Michael Kochetkov

Unfortunately, some implementation details are driver-specific. For the Microsoft ODBC drivers, when you specify the name as "", SQLColumns looks for all tables with that name. Since no tables have a name of "", it returns no results.

If you specify 'NULL' for a table name, it will look for ALL tables.

You may need to wrap your queries based on Driver Name, such as

if(<< MS Driver >>)

SQLColumns(hstmt, NULL, 0, ...)

else

SQLColumns(hstmt, "", SQL_NTS, ...)

|||

> Unfortunately, some implementation details are driver-specific.
I have just wanted to point out that there is a bug: either in MS implementation or MSDN. Let us get back to the CatalogName parameter description. I believe the following statement "an empty string ("") denotes those tables that do not have catalogs." shall be replaced with something like this: as far as MS ODBC driver does distinguish between SQL Nulls and empty strings ("") you cannot use empty string to denote that tables do not have catalogs and are to use 0 that stands for SQL Null value inside.

--
Michael Kochetkov

P.S. Edited: tables have --> tables do not have