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 question about varchar parameters

I've created a stored procedure that takes a varchar(10) as a parameter. However calling this stored procedure from an ASP page, with a string of greater length, generates an error. However this does not happen in Query Analyzer (it simply truncates the string to 10 characters). I was under the previous impression that this truncation was implicit, but now it seems that it is not. Can someone please give me a quick overview of how to work around this issue (is there an SQL setting I can flip on). I know I could pre-truncate every value in my page, but that seems like a design nightmare (seeing as how I would need to know the size of every varchar parameter in every stored procedure old and new, also I'd like to be able to simply increase the size of the data field in the table, at a later point, without having to match it up in every stored procedure and ASP page ).

P.S. I am using SQL Sever 2000

How did you call the stored procedure from your code? I use SqlConnection and SqlCommand to call the sp with a Parameter, it succeeded even I input a string with length greater then the length defined for the stored procedure parameter, as what happened in Query Analyzer.

So I guess your exception came from ASP .NET, not SQL. Did you call the stored procedure using OleDbCommand and specify the length for the parameter on the application side?

A query runs 1 times slower from a .NET application the from Query

Just a guess.
It might be the delay in creating and opening the connection.
Why don't you log the current time just before calling the SP and after it
and find the time difference. That can narrow down on what the issue is.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Boaz Ben-Porat" wrote:

> Computer: 3.4 Ghz CPU, 1 GB RAM, 2003 Server
> database : MS SqlServer 2000 Enterprise. ~ 10 GB database file. Largest
> table in the database contains 11,000,000 records.
> Framework: .NET 2.0
> I try to run a query against the database, selecting aggregated data from
> views based on the large table.
> When executed from the Query Analizer, it takes 13 seconds.
> When executed from a .NET application, it takes 140 seconds.
> The database is well tuned (or else the query analizer would go slowly), s
o
> I can't find the reason for this difference.
> Any suggestion ?
> TIA
> Boaz Ben-Porat
> Milestone Systems
>
>Thanks for a quick answer.
The time I refer to is after the connection is opened.
the relevant code:
DbDataReader dr = null;
try
{
// This method opens a connection, if not allready opened
Connect();
// dbCommand is an input parameter of type DbCommand. It contains the SQL
statement
dbCommand.Connection = _connection;
DateTime t1 = DateTime.Now;
dr = dbCommand.ExecuteReader();
DateTime t2 = DateTime.Now;
TimeSpan ts = t2 - t1;
int milli = (int)ts.TotalMilliseconds; // milli contains the execution time
of dbCommand.ExecuteReader();
Boaz Ben-Porat
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:49A213DD-227B-4602-81ED-5ADF4E32687E@.microsoft.com...
> Just a guess.
> It might be the delay in creating and opening the connection.
> Why don't you log the current time just before calling the SP and after it
> and find the time difference. That can narrow down on what the issue is.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Boaz Ben-Porat" wrote:
>