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