key violation, general sql error, connectin busy with another hstmt

We are getting this error periodically in a large app we are converting
from Access to SQL Server 2000. It uses BDE and ODBC for data access
and TTable/TQuery as well as TwwTable/TwwQuery components (from
woll2woll) under Delphi 6. It appears to happen when we are executing
TQuery.Open.
From what I've read, this is caused by ODBC not completing a result set
before processing another request. Which might be caused by having
queries run in multiple threads, but this is not the case."Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:%23OgCxf7FGHA.1396@.TK2MSFTNGP11.phx.gbl...
> We are getting this error periodically in a large app we are
> converting
> from Access to SQL Server 2000. It uses BDE and ODBC for data
> access
> and TTable/TQuery as well as TwwTable/TwwQuery components (from
> woll2woll) under Delphi 6. It appears to happen when we are
> executing
> TQuery.Open.
> From what I've read, this is caused by ODBC not completing a
> result set
> before processing another request. Which might be caused by
> having
> queries run in multiple threads, but this is not the case.
Basically, you can't have two open (SELECT) statements on the
same connection. For example,
Open query 1
Get some info from a record
Use that to set params in query 2
Open query 2 -- This will fail
This assumes both statements are attached to the same hDBC. It
is a result of using a client-side cursor for the hStmt's.
You'll have to use server-side cursors for the statements to make
this work. See MSDN.
This is one way to force a server-side cursor:
SQLSetStmtAttr( m_hStmt, SQL_ATTR_CURSOR_SCROLLABLE,
(SQLPOINTER) SQL_SCROLLABLE, 0 );
Good luck,
- Arnie|||So you can never have 2 open queries? I do that all the time, in one
thread. Maybe I'm not understanding. For example, this code works
fine, which creates 101 open queries:
--
procedure TForm1.ManyQueriesClick(Sender: TObject);
var qs : array[0..100] of TQuery ;
var q : Tquery;
var i : Integer;
begin
for i := 0 to 100 do begin
qs[i] := TQuery.Create(self);
q := qs[i];
q.databasename := 'WM';
q.SQL.Clear;
q.SQL.Add('SELECT * FROM CUSTOMERFIELDS');
statusbar1.SimpleText := 'Query ' + IntToStr(i);
q.active := true;
end;
for i := 0 to 100 do
qs[i].Free;
end;|||Arnie wrote:

> "Doug Stephens" <dougstephens@.rogers.com> wrote in message
> news:%23OgCxf7FGHA.1396@.TK2MSFTNGP11.phx.gbl...
> Basically, you can't have two open (SELECT) statements on the same
> connection. For example,
> Open query 1
> Get some info from a record
> Use that to set params in query 2
> Open query 2 -- This will fail
> This assumes both statements are attached to the same hDBC. It is a
> result of using a client-side cursor for the hStmt's. You'll have to
> use server-side cursors for the statements to make this work. See
> MSDN.
> This is one way to force a server-side cursor:
> SQLSetStmtAttr( m_hStmt, SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER)
> SQL_SCROLLABLE, 0 );
>
> Good luck,
> - Arnie
How can I run SQLSetStmtAttr from Delphi?|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:uVMztRHGGHA.2012@.TK2MSFTNGP14.phx.gbl...
> How can I run SQLSetStmtAttr from Delphi?
You can't. It's an ODBC statement. The equivalent in Delphi
would be CursorLocation := clUseServer for TADO components.
Sorry, but I've totally forgotten about the BDE. It may use
server-side cursors by default.
The problem occurs in ODBC when nesting queries that use the same
connection handle. Open q1, read some field values, use these to
set params in q2 and then open q2. This last open will cause the
error. In theory, SQL Server 2005 has 'fixed' this 'feature',
though I haven't tried it yet.
- Arnie|||I found a way to call this function in ODBC32.DLL from Delphi but what
is the hstmt?|||We have same problem with 2005.|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:e3UtnUqGGHA.2000@.TK2MSFTNGP15.phx.gbl...
> We have same problem with 2005.
What DB objects are you using with 2005?
- Arnie|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:OfWhHUqGGHA.2000@.TK2MSFTNGP15.phx.gbl...
>I found a way to call this function in ODBC32.DLL from Delphi
>but what
> is the hstmt?
As far as I know, you'd have to be using ODBC directly rather
than the BDE. hStmt is the ODBC statement handle.
- Arnie|||Uh, tables and indexes. No stored procs. Is that what you mean?

key violation, general sql error, connectin busy with another hstmt

We are getting this error periodically in a large app we are converting
from Access to SQL Server 2000. It uses BDE and ODBC for data access
and TTable/TQuery as well as TwwTable/TwwQuery components (from
woll2woll) under Delphi 6. It appears to happen when we are executing
TQuery.Open.
From what I've read, this is caused by ODBC not completing a result set
before processing another request. Which might be caused by having
queries run in multiple threads, but this is not the case.
"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:%23OgCxf7FGHA.1396@.TK2MSFTNGP11.phx.gbl...
> We are getting this error periodically in a large app we are
> converting
> from Access to SQL Server 2000. It uses BDE and ODBC for data
> access
> and TTable/TQuery as well as TwwTable/TwwQuery components (from
> woll2woll) under Delphi 6. It appears to happen when we are
> executing
> TQuery.Open.
> From what I've read, this is caused by ODBC not completing a
> result set
> before processing another request. Which might be caused by
> having
> queries run in multiple threads, but this is not the case.
Basically, you can't have two open (SELECT) statements on the
same connection. For example,
Open query 1
Get some info from a record
Use that to set params in query 2
Open query 2 -- This will fail
This assumes both statements are attached to the same hDBC. It
is a result of using a client-side cursor for the hStmt's.
You'll have to use server-side cursors for the statements to make
this work. See MSDN.
This is one way to force a server-side cursor:
SQLSetStmtAttr( m_hStmt, SQL_ATTR_CURSOR_SCROLLABLE,
(SQLPOINTER) SQL_SCROLLABLE, 0 );
Good luck,
- Arnie
|||So you can never have 2 open queries? I do that all the time, in one
thread. Maybe I'm not understanding. For example, this code works
fine, which creates 101 open queries:
procedure TForm1.ManyQueriesClick(Sender: TObject);
var qs : array[0..100] of TQuery ;
var q : Tquery;
var i : Integer;
begin
for i := 0 to 100 do begin
qs[i] := TQuery.Create(self);
q := qs[i];
q.databasename := 'WM';
q.SQL.Clear;
q.SQL.Add('SELECT * FROM CUSTOMERFIELDS');
statusbar1.SimpleText := 'Query ' + IntToStr(i);
q.active := true;
end;
for i := 0 to 100 do
qs[i].Free;
end;
|||Arnie wrote:

> "Doug Stephens" <dougstephens@.rogers.com> wrote in message
> news:%23OgCxf7FGHA.1396@.TK2MSFTNGP11.phx.gbl...
> Basically, you can't have two open (SELECT) statements on the same
> connection. For example,
> Open query 1
> Get some info from a record
> Use that to set params in query 2
> Open query 2 -- This will fail
> This assumes both statements are attached to the same hDBC. It is a
> result of using a client-side cursor for the hStmt's. You'll have to
> use server-side cursors for the statements to make this work. See
> MSDN.
> This is one way to force a server-side cursor:
> SQLSetStmtAttr( m_hStmt, SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER)
> SQL_SCROLLABLE, 0 );
>
> Good luck,
> - Arnie
How can I run SQLSetStmtAttr from Delphi?
|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:uVMztRHGGHA.2012@.TK2MSFTNGP14.phx.gbl...
> How can I run SQLSetStmtAttr from Delphi?
You can't. It's an ODBC statement. The equivalent in Delphi
would be CursorLocation := clUseServer for TADO components.
Sorry, but I've totally forgotten about the BDE. It may use
server-side cursors by default.
The problem occurs in ODBC when nesting queries that use the same
connection handle. Open q1, read some field values, use these to
set params in q2 and then open q2. This last open will cause the
error. In theory, SQL Server 2005 has 'fixed' this 'feature',
though I haven't tried it yet.
- Arnie
|||I found a way to call this function in ODBC32.DLL from Delphi but what
is the hstmt?
|||We have same problem with 2005.
|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:e3UtnUqGGHA.2000@.TK2MSFTNGP15.phx.gbl...
> We have same problem with 2005.
What DB objects are you using with 2005?
- Arnie
|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:OfWhHUqGGHA.2000@.TK2MSFTNGP15.phx.gbl...
>I found a way to call this function in ODBC32.DLL from Delphi
>but what
> is the hstmt?
As far as I know, you'd have to be using ODBC directly rather
than the BDE. hStmt is the ODBC statement handle.
- Arnie
|||Uh, tables and indexes. No stored procs. Is that what you mean?

Key Violation during synchronization

Dear Friends

I restored same database in Publisher & subscriber.If I want to apply Transactional replication I have to apply initial snapshot.then I am getting key-violation problems during initialization .Some times Primary key table will be dropped before foreign key table.sometimes it won't able to drop some indexes.This I am getting if I am going for all tables of database.But here i need all tables.If I am going for selective table I don't have any problem.For avoiding this problem I tried all the options in table article option in publication wizard.But some na some key violation I am getting always.So please give me some better suggestion

Thanks in Advance

Filson

hi,

though i haven't done this

how about having a snapshot replication for the table that

encounter the key violation?

When your transaction replication encounters a key violation

run the snapshot replication.

this will work if there is no updating from the subscriber.

works like a multiple publication -multiple subscriber topology

regards,

|||

Sounds like you are trying to initialize a subscription with a backup. You may want to check the following document in book online to see if it helps.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/75c8c1f8-60bc-44a8-944b-d18d1f6bda11.htm

Gary

|||

Gary I won't able to find this document in net.Can you give me it as a link.

Thanks

Filson

|||

You can access the article on web from here.

http://msdn2.microsoft.com/en-us/library/ms151705.aspx

FYI, the previous link I post is for SQL Server Book Online.

Cheers,

Gary

Key Violation during synchronization

Dear Friends

I restored same database in Publisher & subscriber.If I want to apply Transactional replication I have to apply initial snapshot.then I am getting key-violation problems during initialization .Some times Primary key table will be dropped before foreign key table.sometimes it won't able to drop some indexes.This I am getting if I am going for all tables of database.But here i need all tables.If I am going for selective table I don't have any problem.For avoiding this problem I tried all the options in table article option in publication wizard.But some na some key violation I am getting always.So please give me some better suggestion

Thanks in Advance

Filson

hi,

though i haven't done this

how about having a snapshot replication for the table that

encounter the key violation?

When your transaction replication encounters a key violation

run the snapshot replication.

this will work if there is no updating from the subscriber.

works like a multiple publication -multiple subscriber topology

regards,

|||

Sounds like you are trying to initialize a subscription with a backup. You may want to check the following document in book online to see if it helps.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/75c8c1f8-60bc-44a8-944b-d18d1f6bda11.htm

Gary

|||

Gary I won't able to find this document in net.Can you give me it as a link.

Thanks

Filson

|||

You can access the article on web from here.

http://msdn2.microsoft.com/en-us/library/ms151705.aspx

FYI, the previous link I post is for SQL Server Book Online.

Cheers,

Gary