Problem in Instead of Delete Trigger

Hi,
I have Instead of Delete Trigger in my Table. When i delete any row
from the table it shows the No of Rows affected ex:- 1 Row affected
But when i look into the table the Deleted Row is still Present.
My Question is If there is Instead of Delete Trigger then is it not
possible to delete row from Original table. and if i want to delete the
row using Instead of trigger what i have to do?
If anybody knows the solution Please let me know?
Thanks,
Vinoth
It really depends on what are you doing in the instead of delete trigger. Do
you actually delete the row in the trigger? Please post sample code from the
trigger if you need more help (desired result and sample data/table would be
helpful...)
MC
<vinoth@.gsdindia.com> wrote in message
news:1132308519.761801.276040@.g49g2000cwa.googlegr oups.com...
> Hi,
> I have Instead of Delete Trigger in my Table. When i delete any row
> from the table it shows the No of Rows affected ex:- 1 Row affected
> But when i look into the table the Deleted Row is still Present.
> My Question is If there is Instead of Delete Trigger then is it not
> possible to delete row from Original table. and if i want to delete the
> row using Instead of trigger what i have to do?
> If anybody knows the solution Please let me know?
>
> Thanks,
> Vinoth
>
|||Hi,
if I understand you correct you're deleteing a record and your trigger is
firing reporting the number of rows affected ? So far so good. If this
was
a normal "After" trigger the record would be deleted in the table. An
instead of trigger is different. The delete actually take place in the
table. You have to do that by yourself.
So in your trigger code you could do something like this :
Delete from table where id=(Select id from deleted)
But why are you using an instead of trigger and not an after trigger ?
Normally you use instead of triggers when you want to handle the dml
action
yourself for instance in partitioned views.
Regards
Bobby Henningsen
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:eHrFsqC7FHA.476@.TK2MSFTNGP15.phx.gbl...
> It really depends on what are you doing in the instead of delete
trigger.
> Do you actually delete the row in the trigger? Please post sample code
> from the trigger if you need more help (desired result and sample
> data/table would be helpful...)
>
> MC
>
> <vinoth@.gsdindia.com> wrote in message
> news:1132308519.761801.276040@.g49g2000cwa.googlegr oups.com...
>
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at f? 711 spam-mails.
Betalende brugere f?r ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk

Problem in Instead of Delete Trigger

Hi,
I have Instead of Delete Trigger in my Table. When i delete any row
from the table it shows the No of Rows affected ex:- 1 Row affected
But when i look into the table the Deleted Row is still Present.
My Question is If there is Instead of Delete Trigger then is it not
possible to delete row from Original table. and if i want to delete the
row using Instead of trigger what i have to do?
If anybody knows the solution Please let me know?
Thanks,
VinothIt really depends on what are you doing in the instead of delete trigger. Do
you actually delete the row in the trigger? Please post sample code from the
trigger if you need more help (desired result and sample data/table would be
helpful...)
MC
<vinoth@.gsdindia.com> wrote in message
news:1132308519.761801.276040@.g49g2000cwa.googlegroups.com...
> Hi,
> I have Instead of Delete Trigger in my Table. When i delete any row
> from the table it shows the No of Rows affected ex:- 1 Row affected
> But when i look into the table the Deleted Row is still Present.
> My Question is If there is Instead of Delete Trigger then is it not
> possible to delete row from Original table. and if i want to delete the
> row using Instead of trigger what i have to do?
> If anybody knows the solution Please let me know?
>
> Thanks,
> Vinoth
>|||Hi,
if I understand you correct you're deleteing a record and your trigger is
firing reporting the number of rows affected ' So far so good. If this
was
a normal "After" trigger the record would be deleted in the table. An
instead of trigger is different. The delete actually take place in the
table. You have to do that by yourself.
So in your trigger code you could do something like this :
Delete from table where id=(Select id from deleted)
But why are you using an instead of trigger and not an after trigger ?
Normally you use instead of triggers when you want to handle the dml
action
yourself for instance in partitioned views.
Regards :)
Bobby Henningsen
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:eHrFsqC7FHA.476@.TK2MSFTNGP15.phx.gbl...
> It really depends on what are you doing in the instead of delete
trigger.
> Do you actually delete the row in the trigger? Please post sample code
> from the trigger if you need more help (desired result and sample
> data/table would be helpful...)
>
> MC
>
> <vinoth@.gsdindia.com> wrote in message
> news:1132308519.761801.276040@.g49g2000cwa.googlegroups.com...
>> Hi,
>> I have Instead of Delete Trigger in my Table. When i delete any row
>> from the table it shows the No of Rows affected ex:- 1 Row affected
>> But when i look into the table the Deleted Row is still Present.
>> My Question is If there is Instead of Delete Trigger then is it not
>> possible to delete row from Original table. and if i want to delete the
>> row using Instead of trigger what i have to do?
>> If anybody knows the solution Please let me know?
>>
>> Thanks,
>> Vinoth
>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 711 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dksql

Problem in Instead of Delete Trigger

Hi,
I have Instead of Delete Trigger in my Table. When i delete any row
from the table it shows the No of Rows affected ex:- 1 Row affected
But when i look into the table the Deleted Row is still Present.
My Question is If there is Instead of Delete Trigger then is it not
possible to delete row from Original table. and if i want to delete the
row using Instead of trigger what i have to do?
If anybody knows the solution Please let me know?
Thanks,
VinothHi ,
You cannot delete rows from a table which has a instead of delete trigger
configured.
Though, We can put delete statement for the table inside instead of trigger
but we need to keep RECURSIVE_TRIGGERS database option set correctly. This D
B
option will not cause trigger to fire again.
But still I am not sure why do you want to delete row from a trigger which
has instead of delete trigger configured.
--
Vishal Khajuria
9886170165
IBM Bangalore
"vinoth@.gsdindia.com" wrote:

> Hi,
> I have Instead of Delete Trigger in my Table. When i delete any row
> from the table it shows the No of Rows affected ex:- 1 Row affected
> But when i look into the table the Deleted Row is still Present.
> My Question is If there is Instead of Delete Trigger then is it not
> possible to delete row from Original table. and if i want to delete the
> row using Instead of trigger what i have to do?
> If anybody knows the solution Please let me know?
>
> Thanks,
> Vinoth
>

Problem in Instead of Delete Trigger

Hi,
I have Instead of Delete Trigger in my Table. When i delete any row
from the table it shows the No of Rows affected ex:- 1 Row affected
But when i look into the table the Deleted Row is still Present.
My Question is If there is Instead of Delete Trigger then is it not
possible to delete row from Original table. and if i want to delete the
row using Instead of trigger what i have to do?
If anybody knows the solution Please let me know?
Thanks,
VinothIt really depends on what are you doing in the instead of delete trigger. Do
you actually delete the row in the trigger? Please post sample code from the
trigger if you need more help (desired result and sample data/table would be
helpful...)
MC
<vinoth@.gsdindia.com> wrote in message
news:1132308519.761801.276040@.g49g2000cwa.googlegroups.com...
> Hi,
> I have Instead of Delete Trigger in my Table. When i delete any row
> from the table it shows the No of Rows affected ex:- 1 Row affected
> But when i look into the table the Deleted Row is still Present.
> My Question is If there is Instead of Delete Trigger then is it not
> possible to delete row from Original table. and if i want to delete the
> row using Instead of trigger what i have to do?
> If anybody knows the solution Please let me know?
>
> Thanks,
> Vinoth
>|||Hi,
if I understand you correct you're deleteing a record and your trigger is
firing reporting the number of rows affected ' So far so good. If this
was
a normal "After" trigger the record would be deleted in the table. An
instead of trigger is different. The delete actually take place in the
table. You have to do that by yourself.
So in your trigger code you could do something like this :
Delete from table where id=(Select id from deleted)
But why are you using an instead of trigger and not an after trigger ?
Normally you use instead of triggers when you want to handle the dml
action
yourself for instance in partitioned views.
Regards
Bobby Henningsen
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:eHrFsqC7FHA.476@.TK2MSFTNGP15.phx.gbl...
> It really depends on what are you doing in the instead of delete
trigger.
> Do you actually delete the row in the trigger? Please post sample code
> from the trigger if you need more help (desired result and sample
> data/table would be helpful...)
>
> MC
>
> <vinoth@.gsdindia.com> wrote in message
> news:1132308519.761801.276040@.g49g2000cwa.googlegroups.com...
>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at f? 711 spam-mails.
Betalende brugere f?r ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk

Problem in Creating Trigger Sql Server 2000

Friends,
I have problem to creating a trigger.
In a trigger, i have check, if both inserted and deleted table has
row, then Check containt in both tables are same or not like this
Select Count(*) from inserted i , deleted d where i.cola = d.cola
and i.colb = d.colb
I have done it in some dynamically way, which can work for my all
tables, As code follows.
########
My Problem is, when following trigger execute, its return inserted
and deleted table does not exits.
#######
Update tm_empmastR1
Set empstatus = 'A'
Alter Trigger tr_tm_empmastR1 on tm_empmastR1
For Insert, Update, Delete
As
Declare @.ColName as VarChar(255),
@.Cmd as VarChar(8000)
Declare TCur Cursor For
Select SC.name
From sysobjects SO
Inner join syscolumns SC On SC.id = SO.id
And SO.name = 'tm_empmastR1'
order by colid
Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
Open TCur
Fetch Next From TCur Into @.ColName
While (@.@.Fetch_Status = 0)
Begin
Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
Fetch Next From TCur Into @.ColName
End
Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
Print (@.Cmd)
Exec (@.Cmd)
Close TCur
Deallocate TCur
#####
Although return Sql is runing perfectly.
#####
Thanks in advance for any reply.
Rahul Verma
FutureSoftDynamically executed SQL has its own context and doesn't have access to these tables. I encourage
you to skip the dynamic SQL if at all possible. If not, you can have the outer code populate a temp
table and then the inner (dynamic) code can access that temp table.
I would use the system tables/catalog views to generate the trigger code, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rahul" <verma.career@.gmail.com> wrote in message
news:1172035114.793535.273980@.v33g2000cwv.googlegroups.com...
> Friends,
> I have problem to creating a trigger.
> In a trigger, i have check, if both inserted and deleted table has
> row, then Check containt in both tables are same or not like this
> Select Count(*) from inserted i , deleted d where i.cola = d.cola
> and i.colb = d.colb
> I have done it in some dynamically way, which can work for my all
> tables, As code follows.
> ########
> My Problem is, when following trigger execute, its return inserted
> and deleted table does not exits.
> #######
>
> Update tm_empmastR1
> Set empstatus = 'A'
> Alter Trigger tr_tm_empmastR1 on tm_empmastR1
> For Insert, Update, Delete
> As
> Declare @.ColName as VarChar(255),
> @.Cmd as VarChar(8000)
> Declare TCur Cursor For
> Select SC.name
> From sysobjects SO
> Inner join syscolumns SC On SC.id = SO.id
> And SO.name = 'tm_empmastR1'
> order by colid
> Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
> Open TCur
> Fetch Next From TCur Into @.ColName
> While (@.@.Fetch_Status = 0)
> Begin
> Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
> Fetch Next From TCur Into @.ColName
> End
> Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
> Print (@.Cmd)
> Exec (@.Cmd)
> Close TCur
> Deallocate TCur
>
> #####
> Although return Sql is runing perfectly.
> #####
>
> Thanks in advance for any reply.
> Rahul Verma
> FutureSoft
>|||Hi Rahul
"Rahul" wrote:
> Friends,
> I have problem to creating a trigger.
> In a trigger, i have check, if both inserted and deleted table has
> row, then Check containt in both tables are same or not like this
> Select Count(*) from inserted i , deleted d where i.cola = d.cola
> and i.colb = d.colb
> I have done it in some dynamically way, which can work for my all
> tables, As code follows.
> ########
> My Problem is, when following trigger execute, its return inserted
> and deleted table does not exits.
> #######
>
> Update tm_empmastR1
> Set empstatus = 'A'
> Alter Trigger tr_tm_empmastR1 on tm_empmastR1
> For Insert, Update, Delete
> As
> Declare @.ColName as VarChar(255),
> @.Cmd as VarChar(8000)
> Declare TCur Cursor For
> Select SC.name
> From sysobjects SO
> Inner join syscolumns SC On SC.id = SO.id
> And SO.name = 'tm_empmastR1'
> order by colid
> Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
> Open TCur
> Fetch Next From TCur Into @.ColName
> While (@.@.Fetch_Status = 0)
> Begin
> Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
> Fetch Next From TCur Into @.ColName
> End
> Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
> Print (@.Cmd)
> Exec (@.Cmd)
> Close TCur
> Deallocate TCur
>
> #####
> Although return Sql is runing perfectly.
> #####
>
> Thanks in advance for any reply.
> Rahul Verma
> FutureSoft
>
The dynamic SQL is in a different scope to the trigger, therefore the it
doesn't know about the inserted and deleted tables.
You should check @.@.ROWCOUNT as the first statement in the trigger to make
sure that there has been some changes.
All the cursor is doing is allowing you to avoid having to change the
trigger when your tables structure changes, on a mature system it will be
redundant and could potentially impact performance and could even cause a
bottleneck.
John|||On Feb 21, 1:17 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Rahul
>
>
> "Rahul" wrote:
> > Friends,
> > I have problem to creating a trigger.
> > In a trigger, i have check, if both inserted and deleted table has
> > row, then Check containt in both tables are same or not like this
> > Select Count(*) from inserted i , deleted d where i.cola = d.cola
> > and i.colb = d.colb
> > I have done it in some dynamically way, which can work for my all
> > tables, As code follows.
> > ########
> > My Problem is, when following trigger execute, its return inserted
> > and deleted table does not exits.
> > #######
> > Update tm_empmastR1
> > Set empstatus = 'A'
> > Alter Trigger tr_tm_empmastR1 on tm_empmastR1
> > For Insert, Update, Delete
> > As
> > Declare @.ColName as VarChar(255),
> > @.Cmd as VarChar(8000)
> > Declare TCur Cursor For
> > Select SC.name
> > From sysobjects SO
> > Inner join syscolumns SC On SC.id = SO.id
> > And SO.name = 'tm_empmastR1'
> > order by colid
> > Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
> > Open TCur
> > Fetch Next From TCur Into @.ColName
> > While (@.@.Fetch_Status = 0)
> > Begin
> > Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
> > Fetch Next From TCur Into @.ColName
> > End
> > Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
> > Print (@.Cmd)
> > Exec (@.Cmd)
> > Close TCur
> > Deallocate TCur
> > #####
> > Although return Sql is runing perfectly.
> > #####
> > Thanks in advance for any reply.
> > Rahul Verma
> > FutureSoft
> The dynamic SQL is in a different scope to the trigger, therefore the it
> doesn't know about the inserted and deleted tables.
> You should check @.@.ROWCOUNT as the first statement in the trigger to make
> sure that there has been some changes.
> All the cursor is doing is allowing you to avoid having to change the
> trigger when your tables structure changes, on a mature system it will be
> redundant and could potentially impact performance and could even cause a
> bottleneck.
> John- Hide quoted text -
> - Show quoted text -
Hi All
Thanks for reply.
Tibor Karaszi
i am not getting your idea,
trigger is called when we call update/insert/delete command
automatically and just after these statements.
So, we have not enough time to create and use a temp table.
If is it possible or any other idea, please mail me, Because we
require to create this type of modification with 100'th triggers, As i
am creating log.
Jhon,
This is right, if table structure change, cursor may be fail, that's
why i am using sysobject and syscolumns tables to getting all columns.
And when i use print statement, the return value of print statement
is absolutely correct and its working.
The @.@.rowcount only return no of rows in last query, its ok but
before this statement, i have to match all these fields in both table.
####
Actually my logic is
if both inserted and deleted tables contain row (ie row is updated)
and changes are actually made (ie changes must be done in any field in
a table) then insert this row in a log file with a particular flag.
else if only inserted table contain row then it insert with different
flag
same as with deleted.
####
if you have any idea to done it, please reply me
i have more than 1000 tables.
Thanks
Rahul|||Hi Rahul
"Rahul" wrote:
> On Feb 21, 1:17 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi Rahul
> >
> >
> >
> >
> >
> > "Rahul" wrote:
> > > Friends,
> > > I have problem to creating a trigger.
> > > In a trigger, i have check, if both inserted and deleted table has
> > > row, then Check containt in both tables are same or not like this
> >
> > > Select Count(*) from inserted i , deleted d where i.cola = d.cola
> > > and i.colb = d.colb
> >
> > > I have done it in some dynamically way, which can work for my all
> > > tables, As code follows.
> >
> > > ########
> > > My Problem is, when following trigger execute, its return inserted
> > > and deleted table does not exits.
> > > #######
> >
> > > Update tm_empmastR1
> > > Set empstatus = 'A'
> >
> > > Alter Trigger tr_tm_empmastR1 on tm_empmastR1
> > > For Insert, Update, Delete
> > > As
> >
> > > Declare @.ColName as VarChar(255),
> > > @.Cmd as VarChar(8000)
> >
> > > Declare TCur Cursor For
> > > Select SC.name
> > > From sysobjects SO
> > > Inner join syscolumns SC On SC.id = SO.id
> > > And SO.name = 'tm_empmastR1'
> > > order by colid
> >
> > > Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
> > > Open TCur
> > > Fetch Next From TCur Into @.ColName
> > > While (@.@.Fetch_Status = 0)
> > > Begin
> > > Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
> > > Fetch Next From TCur Into @.ColName
> > > End
> >
> > > Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
> > > Print (@.Cmd)
> > > Exec (@.Cmd)
> >
> > > Close TCur
> > > Deallocate TCur
> >
> > > #####
> > > Although return Sql is runing perfectly.
> > > #####
> >
> > > Thanks in advance for any reply.
> >
> > > Rahul Verma
> > > FutureSoft
> >
> > The dynamic SQL is in a different scope to the trigger, therefore the it
> > doesn't know about the inserted and deleted tables.
> >
> > You should check @.@.ROWCOUNT as the first statement in the trigger to make
> > sure that there has been some changes.
> >
> > All the cursor is doing is allowing you to avoid having to change the
> > trigger when your tables structure changes, on a mature system it will be
> > redundant and could potentially impact performance and could even cause a
> > bottleneck.
> >
> > John- Hide quoted text -
> >
> > - Show quoted text -
>
> Hi All
> Thanks for reply.
> Tibor Karaszi
> i am not getting your idea,
> trigger is called when we call update/insert/delete command
> automatically and just after these statements.
> So, we have not enough time to create and use a temp table.
> If is it possible or any other idea, please mail me, Because we
> require to create this type of modification with 100'th triggers, As i
> am creating log.
>
> Jhon,
> This is right, if table structure change, cursor may be fail, that's
> why i am using sysobject and syscolumns tables to getting all columns.
> And when i use print statement, the return value of print statement
> is absolutely correct and its working.
> The @.@.rowcount only return no of rows in last query, its ok but
> before this statement, i have to match all these fields in both table.
> ####
> Actually my logic is
> if both inserted and deleted tables contain row (ie row is updated)
> and changes are actually made (ie changes must be done in any field in
> a table) then insert this row in a log file with a particular flag.
> else if only inserted table contain row then it insert with different
> flag
> same as with deleted.
> ####
> if you have any idea to done it, please reply me
> i have more than 1000 tables.
> Thanks
> Rahul
I think you have misunderstood both replies. I believe that Tibor is
suggesting that you generate your create trigger statements using the cursors
and you will end up with "hardcoded" columns for each table.
The checking of @.@.ROWCOUNT at the very begining of the trigger will mean
that you don't execute the code when not rows are changed, this will cut out
the unnecessary actions of the trigger.
Alter Trigger tr_tm_empmastR1 on tm_empmastR1 For Insert, Update, Delete
As
IF @.@.ROWCOUNT = 0 RETURN
...
John

problem in connecting to server after restore of master

Hi all,
even though sysdatabases contains a row with [name] = 'master' I get
the follwoing error message when sending commands to the db engine:
Msg 911, Level 16, State 1, Server BEQV288H, Line 1
Could not locate entry in sysdatabases for database 'master'. No entry
found
with that name. Make sure that the name is entered correctly.
Msg 2812, Level 16, State 62, Server BEQV288H, Line 1
Could not find stored procedure 'sp_attach_db'.
[DBNETLIB]ConnectionCheckForData (CheckforData()).
[DBNETLIB]General network error. Check your network documentation.
What can I do?
Thanks a lot in advance
DanielHi
Did you follow the below rules?
1. Stop MSSQLServer and SQLServerAgent services.
2. From a command prompt, enter this command:
sqlservr.exe -m
3. Run Enterprise Manager to restore the master database from the backup
SQL Server 2005
C:\> sqlcmd
1> RESTORE DATABASE master FROM DISK = 'c:\foldername\master.bak';
2> GO<danielsanberger@.googlemail.com> wrote in message
news:1159957373.692230.161640@.m73g2000cwd.googlegroups.com...
> Hi all,
> even though sysdatabases contains a row with [name] = 'master' I get
> the follwoing error message when sending commands to the db engine:
> Msg 911, Level 16, State 1, Server BEQV288H, Line 1
> Could not locate entry in sysdatabases for database 'master'. No entry
> found
> with that name. Make sure that the name is entered correctly.
> Msg 2812, Level 16, State 62, Server BEQV288H, Line 1
> Could not find stored procedure 'sp_attach_db'.
> [DBNETLIB]ConnectionCheckForData (CheckforData()).
> [DBNETLIB]General network error. Check your network documentation.
> What can I do?
> Thanks a lot in advance
> Daniel
>|||Hi Uri,
yes I did, though it is SQL Server 2000. Then I started in sqlservr -c
-f -T3608 mode cause the master db comes from a different system
environment and I have to change some system entries in sysaltfiles and
detach some databases.
I tried this before in Windows Server 2003 environment and now I try to
do it in Windows Server 2000 environment.
Greetings
Daniel
Uri Dimant schrieb:
> Hi
> Did you follow the below rules?
> 1. Stop MSSQLServer and SQLServerAgent services.
> 2. From a command prompt, enter this command:
> sqlservr.exe -m
> 3. Run Enterprise Manager to restore the master database from the backup
>
> SQL Server 2005
> C:\> sqlcmd
> 1> RESTORE DATABASE master FROM DISK = 'c:\foldername\master.bak';
> 2> GO<danielsanberger@.googlemail.com> wrote in message
> news:1159957373.692230.161640@.m73g2000cwd.googlegroups.com...
> > Hi all,
> >
> > even though sysdatabases contains a row with [name] = 'master' I get
> > the follwoing error message when sending commands to the db engine:
> >
> > Msg 911, Level 16, State 1, Server BEQV288H, Line 1
> > Could not locate entry in sysdatabases for database 'master'. No entry
> > found
> > with that name. Make sure that the name is entered correctly.
> > Msg 2812, Level 16, State 62, Server BEQV288H, Line 1
> > Could not find stored procedure 'sp_attach_db'.
> > [DBNETLIB]ConnectionCheckForData (CheckforData()).
> > [DBNETLIB]General network error. Check your network documentation.
> >
> > What can I do?
> >
> > Thanks a lot in advance
> > Daniel
> >

Problem finding values with aggregate functions

Hi all!

In a statement I want to find the IDENTITY-column value for a row that
has the smallest value. I have tried this, but for the result i also
want to know the row_id for each. Can this be solved in a neat way,
without using temporary tables?

CREATE TABLE some_table
(
row_id INTEGER
NOT NULL
IDENTITY(1,1)
PRIMARY KEY,

row_value integer,
row_name varchar(30)
)
GO
/* DROP TABLE some_table */

insert into some_table (row_name, row_value) VALUES ('Alice', 0)
insert into some_table (row_name, row_value) VALUES ('Alice', 1)
insert into some_table (row_name, row_value) VALUES ('Alice', 2)
insert into some_table (row_name, row_value) VALUES ('Alice', 3)
insert into some_table (row_name, row_value) VALUES ('Bob', 2)
insert into some_table (row_name, row_value) VALUES ('Bob', 3)
insert into some_table (row_name, row_value) VALUES ('Bob', 5)
insert into some_table (row_name, row_value) VALUES ('Celine', 4)
insert into some_table (row_name, row_value) VALUES ('Celine', 5)
insert into some_table (row_name, row_value) VALUES ('Celine', 6)

select min(row_value), row_name from some_table group by row_nameJon wrote:
> Hi all!
> In a statement I want to find the IDENTITY-column value for a row
that
> has the smallest value. I have tried this, but for the result i also
> want to know the row_id for each. Can this be solved in a neat way,
> without using temporary tables?
> CREATE TABLE some_table
> (
> row_id INTEGER
> NOT NULL
> IDENTITY(1,1)
> PRIMARY KEY,
> row_value integer,
> row_name varchar(30)
> )
> GO
> /* DROP TABLE some_table */
> insert into some_table (row_name, row_value) VALUES ('Alice', 0)
> insert into some_table (row_name, row_value) VALUES ('Alice', 1)
> insert into some_table (row_name, row_value) VALUES ('Alice', 2)
> insert into some_table (row_name, row_value) VALUES ('Alice', 3)
> insert into some_table (row_name, row_value) VALUES ('Bob', 2)
> insert into some_table (row_name, row_value) VALUES ('Bob', 3)
> insert into some_table (row_name, row_value) VALUES ('Bob', 5)
> insert into some_table (row_name, row_value) VALUES ('Celine', 4)
> insert into some_table (row_name, row_value) VALUES ('Celine', 5)
> insert into some_table (row_name, row_value) VALUES ('Celine', 6)
> select min(row_value), row_name from some_table group by row_name

*Assuming* that row_name/row_value combinations are unique, then it
would be:

select row_id,row_value,row_name from some_table t1 inner join (select
min(row_value) as row_value, row_name from some_table group by
row_name) t2 on t1.row_value = t2.row_value and t1.row_name =
t2.row_name

*Is* my assumption correct? If not, then there's some additional
grouping on the outer query, and a decision to be made on which row_id
to return (e.g. Min())|||Jon (jonsjostedt@.hotmail.com) writes:
> In a statement I want to find the IDENTITY-column value for a row that
> has the smallest value. I have tried this, but for the result i also
> want to know the row_id for each. Can this be solved in a neat way,
> without using temporary tables?

Yes:

select s.row_id, x.min_value, x.row_name
from some_table s
join (select min_value = min(row_value), row_name
from some_table
group by row_name) as x on x.min_value = s.row_value
and x.row_name = s.row_name

What you see there is a *derived table*. A derived is sort of a temp
table within the query, but it is never materialized. In fact, the
optimizer may recast the computation order as long as this does not
affect the result.

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

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

Problem due to locking in MSSQLServer 2000

Insert or update statements seems to be locking entire
page or table rather than locking
the corresponding row to be inserted or updated.
lets assume the table with 3 rows.
scenario 1)
In transaction A, I'm updating the 3'rd row. and in
another transaction B I'm reading
row 1.
Transaction B seems to be waiting for transaction A to
finish before returning the select results.
Scenario 2)
In transaction A, I'm inserting new row (4'th row) and in
another transaction B I'm reading
row 1.
here as well trasaction B does not return the row 1 unless
transaction A is complete.
Select operation is blocked due to insert.
Ideally in both the scenarios , read operation should have
returned the results without waiting
for update/insert to finish. As the read is being done on
different rows than that of being updated
or inserted.
I have tried both the insert/update as well as select
queries with all the possible locking hints
such as ROWLOCK, READCOMMITED, UPDLOCK etc...
The only way select query returns the row without blocking
is by using the NOLOCK locking hint. But then this is
not the proper solution as it gives us the dirty read.
Please suggest me any solution or workaround for above
issue.Adding to Tony's comments (the DDL/DML will be useful), you might find the
problem is that it's the index page that's locked during the update/insert
which is blocking the select. How many rows are in the table we're
discussing?
Depending on the WHERE clause and the optimizer query plans, sometimes row
locks are NOT released until the entire statement has finished, and
sometimes these can be escalated to table locks. See
http://www.hanlincrest.com/SQLServerLockEscalation.htm for a description.
I couldn't quite figure it out from your note, but is it the same process
attempting to execute two separate transactions on two SQL Server sessions
and thus blocking itself? If this is the case it would be wise to rethink it
so only one transaction is active from the process ar once.
Kind Regards, Howard
"Sangram Deshmukh" <sangram@.savvion.com> wrote in message
news:0c2e01c34ad9$841875a0$a301280a@.phx.gbl...
> Insert or update statements seems to be locking entire
> page or table rather than locking
> the corresponding row to be inserted or updated.
> lets assume the table with 3 rows.
> scenario 1)
> In transaction A, I'm updating the 3'rd row. and in
> another transaction B I'm reading
> row 1.
> Transaction B seems to be waiting for transaction A to
> finish before returning the select results.
>
> Scenario 2)
> In transaction A, I'm inserting new row (4'th row) and in
> another transaction B I'm reading
> row 1.
> here as well trasaction B does not return the row 1 unless
> transaction A is complete.
> Select operation is blocked due to insert.
>
> Ideally in both the scenarios , read operation should have
> returned the results without waiting
> for update/insert to finish. As the read is being done on
> different rows than that of being updated
> or inserted.
> I have tried both the insert/update as well as select
> queries with all the possible locking hints
> such as ROWLOCK, READCOMMITED, UPDLOCK etc...
> The only way select query returns the row without blocking
> is by using the NOLOCK locking hint. But then this is
> not the proper solution as it gives us the dirty read.
> Please suggest me any solution or workaround for above
> issue.