curser problem

Question

curser problem

navie22

SSC-Addicted

Points: 494
More actions
July 1, 2014 at 5:17 pm

#97212

my code is having problem. at first time, it runs fine, but when second time, when their is no data in schema ABC tables, it still make change to original schema tables. here is code. I think, it is bypassing step2 or 3.
DECLARE TCursor CURSOR
FOR
--- (1) get table names from [dbo].[Tablelist]
SELECT [TableName] from [dbo].[Tablelist] where SchemaName = 'abc' --have 5 schemas, but only need 3
DECLARE @TableName varchar(255)
OPEN TCursor
FETCH NEXT FROM TCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Stmt3 nvarchar(max)
--(2) check if jobhistory table have new date and batchjob status is success.which is on server1
SET @Stmt3 = ('select tablename FROM .[dbo].[JobHistory] where tableName = '''+ (@TableName) + '''and FLSLoadedDate > (getdate() -1) and JobBatchStatus <>''fail') ---(2)
BEGIN
---(3) also need to check if table has records inserted. which is on server2
SET @Stmt3 = ('If (Exists (Select * From abc.' +@tablename + '))' )
BEGIN
--(4) if records are in schema abc, flip original schema. otherwise leave original schema as it is.
SET @Stmt3 = 'ALTER SCHEMA abc1 TRANSFER Original.' +@tablename
EXEC (@Stmt3)
SET @Stmt3 = 'ALTER SCHEMA Original TRANSFER abc.'+@tablename
EXEC (@Stmt3)
SET @Stmt3 = 'ALTER SCHEMA abc TRANSFER abc1.' +@tablename
EXEC (@Stmt3)
SET @Stmt3 = 'TRUNCATE TABLE abc.' +@tablename
EXEC (@Stmt3)
END
FETCH NEXT FROM TCursor INTO @TableName
END
END
CLOSE TCursor
DEALLOCATE TCursor
thanks,
Navie

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

That's because you're not executing steps 2 and 3.

Step 3 will fail because you only have a condition. I'm not sure what you are trying to do with step 2, but I corrected step 3.

DECLARE TCursor CURSOR

FOR

--- (1) get table names from [dbo].[Tablelist]

SELECT [TableName]

FROM [dbo].[Tablelist]

WHERE SchemaName = 'abc' --have 5 schemas, but only need 3

DECLARE @TableName VARCHAR(255)

OPEN TCursor

FETCH NEXT

FROM TCursor

INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @Stmt3 NVARCHAR(max)

--(2) check if jobhistory table have new date and batchjob status is success.which is on server1

SET @Stmt3 = ('select tablename FROM .[dbo].[JobHistory] where tableName = ''' + (@TableName) + '''and FLSLoadedDate > (getdate() -1) and JobBatchStatus <>''fail') ---(2)

BEGIN

---(3) also need to check if table has records inserted. which is on server2

SET @Stmt3 = 'If (Exists (Select * From abc.' + @tablename + '))' + CHAR(13)

+ 'BEGIN'

+ 'ALTER SCHEMA abc1 TRANSFER Original.' + @tablename + CHAR(13)

+ 'ALTER SCHEMA Original TRANSFER abc.' + @tablename + CHAR(13)

+ 'ALTER SCHEMA abc TRANSFER abc1.' + @tablename + CHAR(13)

+ 'TRUNCATE TABLE abc.' + @tablename + CHAR(13)

+ 'END'

PRINT @Stmt3

EXEC (@Stmt3)

FETCH NEXT

FROM TCursor

INTO @TableName

END

CLOSE TCursor

DEALLOCATE TCursor

Hopefully you can identify what's wrong and be able to fix it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You shouldn't cross post, it will only fragment the responses.

No more answers here, all should refer to: http://www.sqlservercentral.com/Forums/Topic1588259-3412-1.aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

navie22 SSC-Addicted Points: 494 More actions · Answer 3

Thank you very much. now, I can fix step 2 as well. it helped me to see the problem. when I write this script, I new that step 2 and 3 are not working. but I wasn't sure how to fix them.

thanks,

Navie