problem with MS sql server 2000 database table

[arial]

Hi you all experts,

Here, I am running into strenge situation. I have ms sql server 2000 database and one of the table is acting strenge. I have trigger placed on this table to move data to the other table witin same database after validating the data.

data goes to the first table through my smart phone using sync process.

well at some point of time that table look likes locking up and no data can make it the table. after i delete data from the first table it starts working again.

Can any one help me firgure it our why it is happening or what should I do to prevent this?

Please it is very urgent and I am having no luck on solving this.

so, please please help.

Thank You,

[ck9663]

Check your trigger. It might be recursing. Also, you don't want to execute a full table update or full table scan command on your trigger.

What's the size of your table?

-- CK

[arial]

Thank CK. I will check on my triggers. well at the moment my table has about 85 datafields.
and data i have is my test data about 2000 but it will grow once i solved this problem and launch this app.
Thank you,

[arial]

i can't figured it out so, here i am providing my trigger as well store procedure.

this is my trigger which is on table dbo.wrk

create trigger trigger_qcheck
on [dbo].[Q_CHECK_WORK]
for insert
as
exec [dbo].[SDFD_CheckAndLoad]
 

once records gets into dbo.wrk this trigger fires up and moves data to dbo.glass by executing store procedure and here is the store procedure:

CREATE PROCEDURE SDFD_CheckAndLoad
 
AS
 
BEGIN
 
            DECLARE @Id varchar(19)
 
            DECLARE @CBComplete varchar(1)
 
            DECLARE @PalmName varchar(20)
 
            DECLARE c5 CURSOR FOR
 
              SELECT [ID], CB_COMPLET, PALMNAME FROM dbo.Q_CHECK_WORK
 
              WHERE [ID] NOT IN (SELECT [ID] FROM dbo.Q_CHECK_GLASS)
 
            OPEN c5
 
            FETCH c5 INTO @Id, @CBComplete, @PalmName
 
 
 
print @id
 
print @CBComplete
 
print @PalmName
 
            WHILE @@FETCH_STATUS = 0
 
               BEGIN
 
              PRINT 'INSIDE WHILE'
 
                        IF @CBComplete='T'
 
                         BEGIN
 
                                    PRINT 'COMPARE'
 
                                    INSERT dbo.Q_CHECK_GLASS SELECT * FROM dbo.Q_CHECK_WORK WHERE [ID]=@Id
 
                                    /*delete from dbo.Q_CHECK_WORK WHERE [ID]=@Id*/
 
                                    PRINT 'AFTER INSERT'
 
                         END
 
                        ELSE
 
                                    INSERT dbo.Q_CHECK_FILTER values(@Id, @PalmName)
 
 
 
 
 
            FETCH NEXT FROM c5 INTO  @Id, @CBComplete, @PalmName
 
            END
 
            CLOSE c5
 
            DEALLOCATE c5
 
END
 
GO
 
 

Please help on solving this problem.

Thank You,

[ck9663]

First, consider not using a cursor.

Second, the query

SELECT [ID], CB_COMPLET, PALMNAME FROM dbo.Q_CHECK_WORK
WHERE [ID] NOT IN (SELECT [ID] FROM dbo.Q_CHECK_GLASS)

is still a full table scan. Consider using the logical inserted tables.

Read here for more info.

Happy coding!

-- CK