killing a thread

Hey all,
Just wondering if there is any way to kill a thread within an sqlerver process. The thread we are trying to kill is a rollback statement that has been running for a very long time.
Any ideas ?
Thanks in advance,
KilkaC4 or nitroglycerin ?

I can't think of any reason to ever kill a rollback, other than to shutdown the server so that recovery can do the rollback faster when it has exclusive use of the database. Anything that prevents a rollback from completing essentially permanently corrupts the database.

-PatP|||Is it a result of a previous kill?|||yeah. It's the result of a previous kill.

Currently, I get this error message when I try and kill the spid.

SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

The rollback has been running for a couple hours now. Does anyone know if there is any way to see what it is rolling back ?

I'm going to bounce the server and see what happens.|||ok, so it appears the rollback was just hanging there. A bounce was all that was required. I'm still not sure if it's possible to tell what the rollback is working on. I think it would usefull to know. The reason I ask is because the spid is the result of another app working on the surface and it's quite difficult to tell what the app was doing at the time...

Cheers,
-Kilka|||You sure you're not Darkwing Duck?|||The rollback in 9 out of 10 will take longer (in many cases much longer) than the original transaction. Rollback is impossible to kill with a KILL command. The only way you can undo what KILL does is by bouncing the server, which you've already done. When the database gets recovered during the recovery process, the original rollback attempts to dismiss any originating transaction (basically ignoring the altered data pages recorded in the transaction log) and just moves on with what was actually committed and recorded in the trx log..|||Maybe there's some undocumented way of doing it? I'm in the situation right now that I'm waiting for a rollback that will take hours, and I just want to re-create the database from an old backup anyway. However, there are production databases on the same server so I can't shut down anything outside the particular database.sql

Kill Session statement does not work

I logged in through query anayzer as system admin and then logged in a second session from a different user ID. From the sa window I issued "sp_who" and found the session id of 55 for the second logged in session. I then issued "KILL 55". When I reissued
"sp_who" it showed the session id was gone, but when I opened the user query analyzer window, I was still allowed to issue SELECT statements. Shouldnt the user session window close or leave some type of message to show that the session was logged out and
the window is no longer active?
> Shouldnt the user session window close or leave some type of message
> to show that the session was logged out and the window is no longer
active?
The actual behavior is that Query Analyzer will try to reestablish the
connection when you try to execute a query against a closed connection. You
can see this with a Profiler trace. Whether or not QA should notify the
user when this occurs is debatable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:AA7FD110-6FD0-44D5-A011-EE320063ABC8@.microsoft.com...
> I logged in through query anayzer as system admin and then logged in a
second session from a different user ID. From the sa window I issued
"sp_who" and found the session id of 55 for the second logged in session. I
then issued "KILL 55". When I reissued "sp_who" it showed the session id was
gone, but when I opened the user query analyzer window, I was still allowed
to issue SELECT statements. Shouldnt the user session window close or leave
some type of message to show that the session was logged out and the window
is no longer active?

Kill Session statement does not work

I logged in through query anayzer as system admin and then logged in a secon
d session from a different user ID. From the sa window I issued "sp_who" and
found the session id of 55 for the second logged in session. I then issued
"KILL 55". When I reissued
"sp_who" it showed the session id was gone, but when I opened the user query
analyzer window, I was still allowed to issue SELECT statements. Shouldnt t
he user session window close or leave some type of message to show that the
session was logged out and
the window is no longer active?Hi,
Query analyzer will establish back the connection to SQL server on issuing
any SQL / DML statements.
Thanks
Hari
MCDBA
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:AA7FD110-6FD0-44D5-A011-EE320063ABC8@.microsoft.com...
> I logged in through query anayzer as system admin and then logged in a
second session from a different user ID. From the sa window I issued
"sp_who" and found the session id of 55 for the second logged in session. I
then issued "KILL 55". When I reissued "sp_who" it showed the session id was
gone, but when I opened the user query analyzer window, I was still allowed
to issue SELECT statements. Shouldnt the user session window close or leave
some type of message to show that the session was logged out and the window
is no longer active?|||> Shouldnt the user session window close or leave some type of message
> to show that the session was logged out and the window is no longer
active?
The actual behavior is that Query Analyzer will try to reestablish the
connection when you try to execute a query against a closed connection. You
can see this with a Profiler trace. Whether or not QA should notify the
user when this occurs is debatable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:AA7FD110-6FD0-44D5-A011-EE320063ABC8@.microsoft.com...
> I logged in through query anayzer as system admin and then logged in a
second session from a different user ID. From the sa window I issued
"sp_who" and found the session id of 55 for the second logged in session. I
then issued "KILL 55". When I reissued "sp_who" it showed the session id was
gone, but when I opened the user query analyzer window, I was still allowed
to issue SELECT statements. Shouldnt the user session window close or leave
some type of message to show that the session was logged out and the window
is no longer active?

Keyword seach (Not full Phrase) parameterise sql statement

hi,

i want to do search by keywords for e.g "John Smith". should search for "John" and "Smith"

it is easy to do it using dynamic sql statement.

but i am using parameters sql.

this is my sql

"select * from emp_tbl where fname like '%' + @.keyw + '%' or lname like '%' + @.keyw + '%' "

the above sql will search by full phrase

how can i make it search each word in the phrase.

aslo, i am searching for 70-551 exam. to upgrade my mcad to mcts.

can anybody help.

Hi,

The solution is not that difficult. Just do one thing before sending the parameter to the stored procedure. Just replace the white spaces with %, so your query will become something like this

"select * from emp_tbl where fname like '%' + John%Smith + '%' or lname like '%' + John%Smith + '%' "

I am sure it will work for you.

Thanks and best regards,

|||

Hi,

it is not working!!!!.

can anybody advice how to do.

"Dynamic SQL IS EASY. BECUASE I CAN JUST SPLIT THE STRING AND BUILD MY SQL ACCORDINGLY"

|||

Hi,

Also check in Sql Profiler if the values received to the sp are correct as it always works perfectly fine with me. Make sure that the values received by the sp does not contain whitespaces or any non required character.

Thanks and best regards,

|||

hi,

i think you have a records like that

1. John Smith

2. John William Smith

so, in that case it will work perfect. because the records has both the words "John" and "Smith" so the '%' in between will ignore the word "William". this is how it works.

but if you have a records like that

1. John Smith

2. John William

3. Smith Graham.

in this case it will not work.

i need to bring all the records that has the words "John" or "Smith" with a single keyword string "John Smith"

|||

Hi,

You were right, actually I misunderstood. In order to achive your task you have to manipulate your query in a way so that it will look somthing like this

Select * From tbl_User Where FirstName in ('John','Smith') or LastName in ('John','Smith')

If you think you can achive this task easily from your stored procedure well and good, otherwise you can send the whole query from your application and execute it from the stored procedure.

Hope now it will help you out.

Thanks and best regards,

|||

Hi,

your idea is nice. but i shall make my sql like this

Select * From tbl_User Where FirstName in ('%John%','%Smith%') or LastName in ('%John%','%Smith%')

because in need a like %

what you suggested i have to pass exact name

i will check and i will let you know.

thanks for your help

|||

Hi Hussain,

Well I have also tried this way but it didn't gave the required result but the query which I mentioned worked.

Thanks and best regards,

|||

Hey Hussain,

It seems like I have sorted out the problem write following query instead of with IN keyword.

Select * From tbl_User Where FirstName + ' ' + LastName LIKE '%John%Smith%'

Hope it will work with you as well. Happy Coding ;)

Thanks and best regards,

|||

Hi,

again your query will returns the rows like the following

John Smith

John William Smith

but it will not return rows that begin with

Smith

William John

William Smith

i am trying to query in one field suppose you have in the firstName Column the following values

1. John Smith

2. Smith

3. William Smith

4. John William

5. Smith Wiliam

6. XYZ John

7. hjkdfjhkjdfhkjfh smith ashdsjakdhjkh

so your query will not returns all the rows that has either John or Smith

anyway i have solved. and this is my solutions

this is the function i have created

Create

FUNCTION [dbo].[udf_SearchEachWord](@.Stringnvarchar(4000),@.Phrasenvarchar(400))

RETURNS

char(1)

AS

BEGINDECLARE @.INDEXINTDECLARE @.SLICEnvarchar(4000)DECLARE @.ITMES_TABLETABLE(ITEMSNVARCHAR(4000))DECLARE @.FOUNDchar(1)

SET @.FOUND='0'-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z-- ERO FIRST TIME IN LOOPSELECT @.INDEX= 1-- following line added 10/06/04 as null-- values cause issuesWHILE @.INDEX!=0BEGIN-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTERSELECT @.INDEX=CHARINDEX(' ',@.STRING)-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLEIF @.INDEX!=0SELECT @.SLICE=LEFT(@.STRING,@.INDEX- 1)ELSESELECT @.SLICE= @.STRING-- PUT THE ITEM INTO THE RESULTS SETINSERTINTO @.ITMES_TABLE(Items)VALUES(@.SLICE)-- CHOP THE ITEM REMOVED OFF THE MAIN STRINGSELECT @.STRING=RIGHT(@.STRING,LEN(@.STRING)- @.INDEX)-- BREAK OUT IF WE ARE DONEIFLEN(@.STRING)= 0BREAKEND

--================================================================================

SELECT @.INDEX= 1-- following line added 10/06/04 as null-- values cause issuesWHILE @.INDEX!=0BEGIN-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTERSELECT @.INDEX=CHARINDEX(' ',@.Phrase)-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLEIF @.INDEX!=0SELECT @.SLICE=LEFT(@.Phrase,@.INDEX- 1)ELSESELECT @.SLICE= @.Phrase-- PUT THE ITEM INTO THE RESULTS SET

IFEXISTS(SELECT ITEMSFROM @.ITMES_TABLEWHERE ITEMSlike'%'+ @.SLICE+'%')beginSET @.FOUND='1'breakend

-- CHOP THE ITEM REMOVED OFF THE MAIN STRINGSELECT @.Phrase=RIGHT(@.Phrase,LEN(@.Phrase)- @.INDEX)-- BREAK OUT IF WE ARE DONEIFLEN(@.Phrase)= 0BREAKEND

RETURN @.FOUND

END

and this is how i am using it

select

au_fnamefrom authorswhere DBO.udf_SearchEachWord(au_fname,'John Smith')='1'

and this is the results

au_fname

-------

william john

smith john

john smith

smith

william smith

smith william

john william smith

john

(8 row(s) affected)

Key Lock

Hi,
I tried the following statement with two QA.
Use Northwind
Begin Tran
Update customers set country = 'Mexicos' where country = 'Mexico'
-- without commit/rollback here
I open another QA with
Select * from customers
-- of course it is now showing anything since it is being blocked
However when I key in sp_lock
it is showing KEY lock. My question is the "country" column is not a
primary key, not a clustered/non clustered index, how can it be a Key lock
with exclusive lock?
I understand the exclusive lock part, but i have no idea why the Key lock
occurs?
Thanks
EdThis is a row lock, most likely based on the table's primary key. Even if
not used to locate rows, the PK can still be used to acquire row locks.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:48A7B914-4237-4E61-8F73-72A24D3B59B2@.microsoft.com...
> Hi,
> I tried the following statement with two QA.
> Use Northwind
> Begin Tran
> Update customers set country = 'Mexicos' where country = 'Mexico'
> -- without commit/rollback here
> I open another QA with
> Select * from customers
> -- of course it is now showing anything since it is being blocked
> However when I key in sp_lock
> it is showing KEY lock. My question is the "country" column is not a
> primary key, not a clustered/non clustered index, how can it be a Key lock
> with exclusive lock?
> I understand the exclusive lock part, but i have no idea why the Key lock
> occurs?
> Thanks
> Ed
>
>|||Hi Ed
SQL Server doesn't lock individual columns, the minimum it can lock is a
row. The country column was used to determine which row, but once that row
is accessed, that whole row is locked.
If the table has a clustered index, the data rows are actually the leaf
level of the clustered index. Locking a row is then really locking an index
key. In fact, you will never see a row lock from sp_lock, indicated as RID,
for a table with a clustered index. It will always show KEY lock. But for
all practical purposes, it's the same thing.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:48A7B914-4237-4E61-8F73-72A24D3B59B2@.microsoft.com...
> Hi,
> I tried the following statement with two QA.
> Use Northwind
> Begin Tran
> Update customers set country = 'Mexicos' where country = 'Mexico'
> -- without commit/rollback here
> I open another QA with
> Select * from customers
> -- of course it is now showing anything since it is being blocked
> However when I key in sp_lock
> it is showing KEY lock. My question is the "country" column is not a
> primary key, not a clustered/non clustered index, how can it be a Key lock
> with exclusive lock?
> I understand the exclusive lock part, but i have no idea why the Key lock
> occurs?
> Thanks
> Ed
>
>|||thanks for the answer.
I am still not sure -- I created a nonclustered index on "country" columan
and issue the following statement
Select * from customers where country <> 'Mexico'
it still locks the select statement.
Why? or I have to say select * from customers where country <> 'Mexico' and
customerid = 'ALFKI' in order to show the result and avoid honoring the
exclusive lock?
Ed
"Kalen Delaney" wrote:

> Hi Ed
> SQL Server doesn't lock individual columns, the minimum it can lock is a
> row. The country column was used to determine which row, but once that row
> is accessed, that whole row is locked.
> If the table has a clustered index, the data rows are actually the leaf
> level of the clustered index. Locking a row is then really locking an inde
x
> key. In fact, you will never see a row lock from sp_lock, indicated as RID
,
> for a table with a clustered index. It will always show KEY lock. But for
> all practical purposes, it's the same thing.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:48A7B914-4237-4E61-8F73-72A24D3B59B2@.microsoft.com...
>
>|||Ed
I'm not quite sure what you're asking here.
No matter what index SQL Server uses to find the row, it will still have to
lock the row that it is updating.
Unless you tell SQL Server to ignore locks when you run the select, the
SELECT in another connection will block. You can tell SQL Server to ignore
exclusive locks by using the NOLOCK hint.
Select * from customers with (nolock)
Be very careful with this hint. It will allow you to read uncommitted data,
and if the connection that is doing the update gets rolled back, the data
that you read will be completely invalid.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:98877444-30E4-49FC-AA22-911D5B03CB0E@.microsoft.com...
> thanks for the answer.
> I am still not sure -- I created a nonclustered index on "country" columan
> and issue the following statement
> Select * from customers where country <> 'Mexico'
> it still locks the select statement.
> Why? or I have to say select * from customers where country <> 'Mexico'
> and
> customerid = 'ALFKI' in order to show the result and avoid honoring the
> exclusive lock?
> Ed
> "Kalen Delaney" wrote:
>
>

Key Exists?

What's the best SQL statement to use to detect if a Key Exists in a
particular table?
I had been using SQLDMO within a VB app to access possible keys in the table
and then find if one matches what I'm looking for:
For X = 1 To SQLDMOConnection.Databases(UCase(DatabaseName)).Tables(TableNam
e)
.Keys.Count
If Trim(UCase(KeyName)) = UCase(Trim(SQLDMOConnection.Databases(UCase
(DatabaseName)).Tables(TableName).Keys(X).Name)) Then
KeyExists = True
Exit For
End If
Next X
I've decided not to do this, and instead use SQL statements to get the
information.
So I need some way of traversing keys on a table and see the names and find
a
match to thename I'm looking for.
How's the best way to do this?Okay. I've got some of what I need.
I know that I can use OBJECTPROPERTY(OBJECT_ID('tablename.fieldname'),
'IsPrimaryKey') to find out if a field is a key. Can I specify table/field
in the OBJECT_ID call?
Also, before I do this, I'd like to check the table to see if it has a
primary key.
So...
OBJECTPROPERTY(OBJECT_ID('tablename'),'T
ableHasPrimaryKey')
Now those are elements of what I need.
What are the full statements to make it work?
E. coli Happens.|||It would sure be nice if someone could take the pieces and put them together
into a sql statement or statements that I can use.
Les Stockton wrote:
>Okay. I've got some of what I need.
>I know that I can use OBJECTPROPERTY(OBJECT_ID('tablename.fieldname'),
>'IsPrimaryKey') to find out if a field is a key. Can I specify table/field
>in the OBJECT_ID call?
>Also, before I do this, I'd like to check the table to see if it has a
>primary key.
>So...
> OBJECTPROPERTY(OBJECT_ID('tablename'),'T
ableHasPrimaryKey')
>Now those are elements of what I need.
>What are the full statements to make it work?
>
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||This would list all the tables in the current database that have a primary
key, and the name of the primary key on the table.
SELECT s1.[name] AS "Table", s2.[name] AS "Key"
FROM sysobjects s1 INNER JOIN sysobjects s2
ON s2.[parent_obj]=s1.[id]
WHERE s2.[xtype]='PK'
You could add a WHERE clause to look at a specific table and an aggregate
COUNT to get a 0 or 1 returned from the statement.
SELECT COUNT(*)
FROM sysobjects s1 INNER JOIN sysobjects s2
ON s2.[parent_obj]=s1.[id]
WHERE s2.[xtype]='PK' AND s1.[name]='table_name'
Returns 1 if table_name has a primary key, and zero if it doesn't.
"HockeyFan" wrote:

> What's the best SQL statement to use to detect if a Key Exists in a
> particular table?
> I had been using SQLDMO within a VB app to access possible keys in the tab
le
> and then find if one matches what I'm looking for:
> For X = 1 To SQLDMOConnection.Databases(UCase(DatabaseName)).Tables(TableN
ame)
> ..Keys.Count
> If Trim(UCase(KeyName)) = UCase(Trim(SQLDMOConnection.Databases(UCase
> (DatabaseName)).Tables(TableName).Keys(X).Name)) Then
> KeyExists = True
> Exit For
> End If
> Next X
> I've decided not to do this, and instead use SQL statements to get the
> information.
> So I need some way of traversing keys on a table and see the names and fin
d a
> match to thename I'm looking for.
> How's the best way to do this?
>|||I did.
How much of my post did you read?
Les Stockton via webservertalk.com wrote:
> It would sure be nice if someone could take the pieces and put them togeth
er
> into a sql statement or statements that I can use.
> Les Stockton wrote:
>
>

Key Exists?

What's the best SQL statement to use to detect if a Key Exists in a
particular table?Try:
declare @.key_col ...
if exists(select * from t1 where key_col = @.key_col)
print 'exists'
else
print 'no exist'
go
AMB
"Les Stockton" wrote:

> What's the best SQL statement to use to detect if a Key Exists in a
> particular table?
>|||what do you mean?
that a key value exists?
SELECT * FROM yourtable WHERE key='value'
or that the table has a primary key?
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where
TABLE_NAME = 'yourtable' and CONSTRAINT_TYPE = 'PRIMARY KEY')
print 'has primary key'
ELSE
print 'no primary key'
Les Stockton wrote:
> What's the best SQL statement to use to detect if a Key Exists in a
> particular table?
>|||More in knowing there is a primary key and is it named a certain name?
I have some code in VB that I inherited. It uses the SQLDMO to access the
database to do this:
For X = 1 To
SQLDMOConnection.Databases(UCase(DatabaseName)).Tables(TableName).Keys.Count
If Trim(UCase(KeyName)) =
UCase(Trim(SQLDMOConnection.Databases(UCase(DatabaseName)).Tables(TableName)
.Keys(X).Name)) Then
KeyExists = True
Exit For
End If
Next X
What I want to do, is to not use the SQLDMO, but instead, using SQL directly
to see if a key by a certain name exists.
"Trey Walpole" wrote:

> what do you mean?
> that a key value exists?
> SELECT * FROM yourtable WHERE key='value'
> or that the table has a primary key?
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where
> TABLE_NAME = 'yourtable' and CONSTRAINT_TYPE = 'PRIMARY KEY')
> print 'has primary key'
> ELSE
> print 'no primary key'
>
> Les Stockton wrote:
>|||In SQL-DMO, the key object has the name mapped to a contraint name. In t-SQL
the equivalent can be extracted using the metadata function OBJECTPROPERTY.
See the arguments, IsPrimaryKey and TableHasPrimaryKey in SQL Server Books
Online.
Anith

Key column information is insufficient ....II

This is the case... I would like to learn the statement that make the
relation between these tables.
Why? Cos these are separated in two different databases and if a user
make an update in a table from database X these changes must to be
applied in the other table in the another database:

The tables are :

Principal Database Name : Server Information 2004
Table Name : Clients
Fields : ID_Client, Client

Secondary Database Name : Index2003
Table Name : Contratos
Fields : ID_Con, ID_Client, Client

I need to write a Trigger for Update the table Contratos everytime a
user change the values in Clients.

Im using the follow Trigger :

CREATE TRIGGER UPDate_Clients ON dbo.Clients
FOR UPDATE
AS
update Contratos
set Client = inserted.Client
from Clients
inner join inserted on Clients.Client = inserted.Client

When I update the register the follow message in the application raise :

"Key column information is insufficient or incorrect. Too many rows were
affected by update."

If somebody can help me THANKS A LOT OF...

Leonardo Almeida

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Leonardo Almeida" <leonardoalmeida2004@.yahoo.com.br> wrote in message
news:3f674b6d$0$62079$75868355@.news.frii.net...
>
> This is the case... I would like to learn the statement that make the
> relation between these tables.
> Why? Cos these are separated in two different databases and if a user
> make an update in a table from database X these changes must to be
> applied in the other table in the another database:
> The tables are :
> Principal Database Name : Server Information 2004
> Table Name : Clients
> Fields : ID_Client, Client
> Secondary Database Name : Index2003
> Table Name : Contratos
> Fields : ID_Con, ID_Client, Client
> I need to write a Trigger for Update the table Contratos everytime a
> user change the values in Clients.
> Im using the follow Trigger :
> CREATE TRIGGER UPDate_Clients ON dbo.Clients
> FOR UPDATE
> AS
> update Contratos
> set Client = inserted.Client
> from Clients
> inner join inserted on Clients.Client = inserted.Client
> When I update the register the follow message in the application raise :
> "Key column information is insufficient or incorrect. Too many rows were
> affected by update."
> If somebody can help me THANKS A LOT OF...
> Leonardo Almeida
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

See my reply to your previous post.

Simon|||Leonardo Almeida (leonardoalmeida2004@.yahoo.com.br) writes:
> I need to write a Trigger for Update the table Contratos everytime a
> user change the values in Clients.
> Im using the follow Trigger :
> CREATE TRIGGER UPDate_Clients ON dbo.Clients
> FOR UPDATE
> AS
> update Contratos
> set Client = inserted.Client
> from Clients
> inner join inserted on Clients.Client = inserted.Client
> When I update the register the follow message in the application raise :
> "Key column information is insufficient or incorrect. Too many rows were
> affected by update."

Include a SET NOCOUNT ON first in the trigger. If that does not help,
remove the trigger and run the update again. I would expect in such
case that you get the error anyway. Which would indicate that the error
is in the client code which you did not show us.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||What code are you talking about?

In the client code I use Delphi + ADO

ADOTable1.Open;
ADOTable1.Edit;

Now edit the Client registrer

Post the register with the command:

ADOTable1.Post;

The message arise again...
and each table has a primary key...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Leonardo Almeida (leonardoalmeida2004@.yahoo.com.br) writes:
> What code are you talking about?

The SET NOCOUNT ON command should be added to your trigger.

> In the client code I use Delphi + ADO
> ADOTable1.Open;
> ADOTable1.Edit;
> Now edit the Client registrer
> Post the register with the command:
> ADOTable1.Post;
> The message arise again...
> and each table has a primary key...

There is no .Post method in ADO, so I conclude that this is something
Delphi-specific, and I don't know Delphi.

If SET NOCOUNT ON did not help, I can only suggest to use the Profiler
to see what is going on behind the covers.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Leonardo,

Your trigger doesn't look correct to me! Why are you using Clients
table in your trigger? In order to update Contratos table using new
values in Clients, you need to use inserted table not Clients.
Something like this:

create trigger Update_Clients on Clients
for update
as
update Contratos
set Client = inserted.Client
from Contratos join inserted
on Contratos.ID_Client = inserted.ID_Client

In your current trigger, you are dealing with 3 tables (Contratos,
Clients & inserted) without joining them correctly. So when you try to
update Client field of Contratos table, it finds more than one value
in inserted table which causes that problem.
I hope this one works fine. I didn't test it...

Good Luck,
Shervin

Leonardo Almeida <leonardoalmeida2004@.yahoo.com.br> wrote in message news:<3f674b6d$0$62079$75868355@.news.frii.net>...
> This is the case... I would like to learn the statement that make the
> relation between these tables.
> Why? Cos these are separated in two different databases and if a user
> make an update in a table from database X these changes must to be
> applied in the other table in the another database:
> The tables are :
> Principal Database Name : Server Information 2004
> Table Name : Clients
> Fields : ID_Client, Client
> Secondary Database Name : Index2003
> Table Name : Contratos
> Fields : ID_Con, ID_Client, Client
> I need to write a Trigger for Update the table Contratos everytime a
> user change the values in Clients.
> Im using the follow Trigger :
> CREATE TRIGGER UPDate_Clients ON dbo.Clients
> FOR UPDATE
> AS
> update Contratos
> set Client = inserted.Client
> from Clients
> inner join inserted on Clients.Client = inserted.Client
> When I update the register the follow message in the application raise :
> "Key column information is insufficient or incorrect. Too many rows were
> affected by update."
> If somebody can help me THANKS A LOT OF...
> Leonardo Almeida
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Key column information is insufficient ....

This is the case... I would like to learn the statement that make the
relation between these tables.
Why? Cos these are separated in two different databases and if a user
make an update in a table from database X these changes must to be
applied in the other table in the another database:

The tables are :

Principal Database Name : Server Information 2004
Table Name : Clients
Fields : ID_Client, Client

Secondary Database Name : Index2003
Table Name : Contratos
Fields : ID_Con, ID_Client, Client

I need to write a Trigger for Update the table Contratos everytime a
user change the values in Clients.

Im using the follow Trigger :

CREATE TRIGGER UPDate_Clients ON dbo.Clients
FOR UPDATE
AS
update Contratos
set Client = inserted.Client
from Clients
inner join inserted on Clients.Client = inserted.Client

When I update the register the follow message in the application raise :

"Key column information is insufficient or incorrect. Too many rows were
affected by update."

If somebody can help me THANKS A LOT OF...

Leonardo Almeida

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hey Leonardo;

I noticed that there are no replies...I am getting exactly the same
error but cannot find a solution, nor a workaround. What did you
finally wind up doing? Please reply to my email directly rtodd@.metro.ca

I have similar triggers, they all work when I update my tables directly
in SQL Server. When I move to my ACCESS ADP and perform the same
updates directly in the same table, I get the same error message.

If anyone can help us....please do !

--
Posted via http://dbforums.com

Keep one connection open for log

I am using SQL 7. If I open one connection for long, I notice that as I keep
running more SQL statement my I/O and CPU Usage keep growing. Even though I
am done with the connection not running any statement I still see those
resouces being used.
Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
Please help.
Hi
Look up 'Connection Pooling' in BOL.
The MDAC driver keeps the connection open by default 120 seconds, and if
another query, to the same saver, using the same credentials comes along on
the same client machine., it just re-uses the existing connection.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Astros" <Astros@.discussions.microsoft.com> wrote in message
news:64E68C08-BACE-44A0-9301-2476AA954CD3@.microsoft.com...
> I am using SQL 7. If I open one connection for long, I notice that as I
keep
> running more SQL statement my I/O and CPU Usage keep growing. Even though
I
> am done with the connection not running any statement I still see those
> resouces being used.
> Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
> Please help.
|||I do not agree with you at all. For SQL 7 it is not true. I saw connections
stays there as long as I do not discoonect.
You have not answered any thing of my question. My bad luck is no body else
going to answer this question.
"Astros" wrote:

> I am using SQL 7. If I open one connection for long, I notice that as I keep
> running more SQL statement my I/O and CPU Usage keep growing. Even though I
> am done with the connection not running any statement I still see those
> resouces being used.
> Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
> Please help.
|||Hi
Well, then give us more information. Give us outputs of sp_who2 over
intervals and run profiler at the same time to see what is being submitted
to SQL Server. You might find that there are requests being submitted. There
is no known bug where counters increase themselves for no reason.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Astros" <Astros@.discussions.microsoft.com> wrote in message
news:52DB2055-CAE1-42CE-A106-C55E1422D918@.microsoft.com...
> I do not agree with you at all. For SQL 7 it is not true. I saw
connections
> stays there as long as I do not discoonect.
> You have not answered any thing of my question. My bad luck is no body
else[vbcol=seagreen]
> going to answer this question.
> "Astros" wrote:
keep[vbcol=seagreen]
though I[vbcol=seagreen]
|||Hi Mike,
I don't mean to be rude the other day. My application using one connection
and doing same kind of activities again and again. Several hundred time doing
same insert for a different record. It is not cursor as far as SQL server
concern. From within the application it is repeating.
Another situation is: I open query analyser and start using it for different
type of select or updat etc. Using same connection. I see that counter for
I/O and CPU keep increasing. Not necessarily I am using more resouce
consuming SQL but I never see those resouceses being released. Unless I killl
the connection.
Aziz
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Well, then give us more information. Give us outputs of sp_who2 over
> intervals and run profiler at the same time to see what is being submitted
> to SQL Server. You might find that there are requests being submitted. There
> is no known bug where counters increase themselves for no reason.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Astros" <Astros@.discussions.microsoft.com> wrote in message
> news:52DB2055-CAE1-42CE-A106-C55E1422D918@.microsoft.com...
> connections
> else
> keep
> though I
>
>

Keep one connection open for log

I am using SQL 7. If I open one connection for long, I notice that as I keep
running more SQL statement my I/O and CPU Usage keep growing. Even though I
am done with the connection not running any statement I still see those
resouces being used.
Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
Please help.Hi
Look up 'Connection Pooling' in BOL.
The MDAC driver keeps the connection open by default 120 seconds, and if
another query, to the same saver, using the same credentials comes along on
the same client machine., it just re-uses the existing connection.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Astros" <Astros@.discussions.microsoft.com> wrote in message
news:64E68C08-BACE-44A0-9301-2476AA954CD3@.microsoft.com...
> I am using SQL 7. If I open one connection for long, I notice that as I
keep
> running more SQL statement my I/O and CPU Usage keep growing. Even though
I
> am done with the connection not running any statement I still see those
> resouces being used.
> Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
> Please help.|||I do not agree with you at all. For SQL 7 it is not true. I saw connections
stays there as long as I do not discoonect.
You have not answered any thing of my question. My bad luck is no body else
going to answer this question.
"Astros" wrote:
> I am using SQL 7. If I open one connection for long, I notice that as I keep
> running more SQL statement my I/O and CPU Usage keep growing. Even though I
> am done with the connection not running any statement I still see those
> resouces being used.
> Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
> Please help.|||Hi
Well, then give us more information. Give us outputs of sp_who2 over
intervals and run profiler at the same time to see what is being submitted
to SQL Server. You might find that there are requests being submitted. There
is no known bug where counters increase themselves for no reason.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Astros" <Astros@.discussions.microsoft.com> wrote in message
news:52DB2055-CAE1-42CE-A106-C55E1422D918@.microsoft.com...
> I do not agree with you at all. For SQL 7 it is not true. I saw
connections
> stays there as long as I do not discoonect.
> You have not answered any thing of my question. My bad luck is no body
else
> going to answer this question.
> "Astros" wrote:
> > I am using SQL 7. If I open one connection for long, I notice that as I
keep
> > running more SQL statement my I/O and CPU Usage keep growing. Even
though I
> > am done with the connection not running any statement I still see those
> > resouces being used.
> >
> > Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
> >
> > Please help.|||Hi Mike,
I don't mean to be rude the other day. My application using one connection
and doing same kind of activities again and again. Several hundred time doing
same insert for a different record. It is not cursor as far as SQL server
concern. From within the application it is repeating.
Another situation is: I open query analyser and start using it for different
type of select or updat etc. Using same connection. I see that counter for
I/O and CPU keep increasing. Not necessarily I am using more resouce
consuming SQL but I never see those resouceses being released. Unless I killl
the connection.
Aziz
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Well, then give us more information. Give us outputs of sp_who2 over
> intervals and run profiler at the same time to see what is being submitted
> to SQL Server. You might find that there are requests being submitted. There
> is no known bug where counters increase themselves for no reason.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Astros" <Astros@.discussions.microsoft.com> wrote in message
> news:52DB2055-CAE1-42CE-A106-C55E1422D918@.microsoft.com...
> > I do not agree with you at all. For SQL 7 it is not true. I saw
> connections
> > stays there as long as I do not discoonect.
> >
> > You have not answered any thing of my question. My bad luck is no body
> else
> > going to answer this question.
> >
> > "Astros" wrote:
> >
> > > I am using SQL 7. If I open one connection for long, I notice that as I
> keep
> > > running more SQL statement my I/O and CPU Usage keep growing. Even
> though I
> > > am done with the connection not running any statement I still see those
> > > resouces being used.
> > >
> > > Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
> > >
> > > Please help.
>
>

Keep one connection open for log

I am using SQL 7. If I open one connection for long, I notice that as I keep
running more SQL statement my I/O and CPU Usage keep growing. Even though I
am done with the connection not running any statement I still see those
resouces being used.
Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
Please help.Hi
Look up 'Connection Pooling' in BOL.
The MDAC driver keeps the connection open by default 120 seconds, and if
another query, to the same saver, using the same credentials comes along on
the same client machine., it just re-uses the existing connection.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Astros" <Astros@.discussions.microsoft.com> wrote in message
news:64E68C08-BACE-44A0-9301-2476AA954CD3@.microsoft.com...
> I am using SQL 7. If I open one connection for long, I notice that as I
keep
> running more SQL statement my I/O and CPU Usage keep growing. Even though
I
> am done with the connection not running any statement I still see those
> resouces being used.
> Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
> Please help.|||I do not agree with you at all. For SQL 7 it is not true. I saw connections
stays there as long as I do not discoonect.
You have not answered any thing of my question. My bad luck is no body else
going to answer this question.
"Astros" wrote:

> I am using SQL 7. If I open one connection for long, I notice that as I ke
ep
> running more SQL statement my I/O and CPU Usage keep growing. Even though
I
> am done with the connection not running any statement I still see those
> resouces being used.
> Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
> Please help.|||Hi
Well, then give us more information. Give us outputs of sp_who2 over
intervals and run profiler at the same time to see what is being submitted
to SQL Server. You might find that there are requests being submitted. There
is no known bug where counters increase themselves for no reason.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Astros" <Astros@.discussions.microsoft.com> wrote in message
news:52DB2055-CAE1-42CE-A106-C55E1422D918@.microsoft.com...
> I do not agree with you at all. For SQL 7 it is not true. I saw
connections
> stays there as long as I do not discoonect.
> You have not answered any thing of my question. My bad luck is no body
else[vbcol=seagreen]
> going to answer this question.
> "Astros" wrote:
>
keep[vbcol=seagreen]
though I[vbcol=seagreen]|||Hi Mike,
I don't mean to be rude the other day. My application using one connection
and doing same kind of activities again and again. Several hundred time doin
g
same insert for a different record. It is not cursor as far as SQL server
concern. From within the application it is repeating.
Another situation is: I open query analyser and start using it for different
type of select or updat etc. Using same connection. I see that counter for
I/O and CPU keep increasing. Not necessarily I am using more resouce
consuming SQL but I never see those resouceses being released. Unless I kill
l
the connection.
Aziz
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Well, then give us more information. Give us outputs of sp_who2 over
> intervals and run profiler at the same time to see what is being submitted
> to SQL Server. You might find that there are requests being submitted. The
re
> is no known bug where counters increase themselves for no reason.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Astros" <Astros@.discussions.microsoft.com> wrote in message
> news:52DB2055-CAE1-42CE-A106-C55E1422D918@.microsoft.com...
> connections
> else
> keep
> though I
>
>